Excel: identifica a coluna com um valor específico

Question

Eu tenho um arquivo excel com uma tabela do seguinte formato, há cerca de 50 colunas

A B C D E id
1 0 0 0 0  1
0 1 0 0 0  2
0 0 0 0 1  3

É garantido que uma linha terá exatamente 1 valor que é '1', outras serão 0's

Como posso transformar o acima para um formato:

val  id
 A    1
 B    2
 E    3

    

Answer 1

Assumindo que a tabela que você deu está em A1: F4 (com cabeçalhos na linha 1), primeiro insira esta fórmula em H1:

= SUM (A2: E4)

Em seguida, insira essa ** fórmula de matriz **** em I1:

= SE (LINHAS ($ 1: 1) > $ H $ 1, "", ÍNDICE ($ A $ 1: $ E $ 1, PEQUENO (SE ($ A $ 2: $ E $ 4, COLUNA ($ A $ 2: $ E $ 4) -MIN (COLUNA ($ A $ 2: $ E $ 4)) + 1), LINHAS ($ 1: 1))))

Copie essa fórmula (embora não a de H1 - isso é uma exceção) até você começar a obter os resultados em branco.

A cláusula IF inicial é projetada de modo que, nas linhas em que a fórmula é copiada além do número esperado de devoluções (dada pela fórmula em H1), um espaço em branco é retornado. Isso funciona desde ROWS ($ 1: 1) (que é igual a 1), quando copiado torna-se, sucessivamente, ROWS ($ 1: 2) (= 2), ROWS ($ 1: 3) (= 3), etc, etc .

A construção IF dentro da função SMALL gera uma matriz de valores que consiste nos números de coluna relativos para todos os casos em que há um 1 em algum lugar nessa coluna. A função SMALL, que tem como parâmetro k a construção de ROWS ($ 1: 1) como acima, retorna, em linhas sucessivas, o primeiro valor, o segundo, etc., etc.

Estes índices da coluna são então passados para o ÍNDICE para fornecer a entrada correspondente dos cabeçalhos.

Em seguida, insira esta fórmula (não matriz) em J1:

= IF (I1="", "", ÍNDICE ($ F $ 2: $ F $ 4, MATCH (1, ÍNDICE ($ A $ 2: $ E $ 4, MATCH (I1, $ A $ 1: $ E $ 1) , 0)), 0)))

Copie conforme necessário.

Atenciosamente

** As fórmulas das matrizes não são inseridas da mesma maneira que as fórmulas 'padrão'. Em vez de pressionar apenas ENTER, você primeiro pressiona CTRL e SHIFT, e só então pressiona ENTER. Se você fez isso corretamente, você notará que o Excel coloca as chaves {} ao redor da fórmula (embora não tente inseri-las manualmente).