Encontre o número da coluna no excel com base nos nomes dos cabeçalhos usando a fórmula

Question

Eu tenho uma tabela no Excel com o seguinte layout de linha de cabeçalho 2:

    A      B      C      D      E      F      G      H      I
1 qtr01, qtr02, qtr03, qtr04, qtr01, qtr02, qtr03, qtr04, qtr01
2 2013,  2013,  2013,  2013,  2012,  2012,  2012,  2012,  2015

Como faço para encontrar o número da coluna que corresponde a qtr02 e 2012 (ou seja, col número 6) usando uma fórmula?

Nota: as colunas e linhas são dinâmicas dentro de um intervalo. Por exemplo, a linha que contém informações "qtr" pode ser movida da linha 1 para 3 e os cabeçalhos que correspondem a qtr02 e 2012 podem ser deslocados para a coluna G da coluna F devido a uma inserção de um novo registro de coluna. Essa mudança dinâmica depende do arquivo de origem recebido.

    

Answer 1

Para obter o número da coluna, use

=MATCH(1, (1:1="qtr02")*(2:2=2012), 0)

Esta é uma "fórmula de matriz", então, depois de digitá-la, você deve pressionar Ctrl + Deslocar + Enter .

• 1:1 refere-se a toda a linha 1 . Se preferir, você pode usar A1:I1 para se referir apenas às células que possuem dados.
• (1:1="qtr02") procura qtr02 na linha 1 (ou as células designadas, conforme apropriado) e constrói uma matriz booleana dos resultados. Neste caso, o array é %código% porque a segunda e a sexta colunas contêm {FALSE, TRUE, FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE} .
• Da mesma forma, qtr02 cria uma matriz de onde os (2:2=2012) s estão na linha 2012 : ou seja, 2 .
• {FALSE, FALSE, FALSE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE} , claro, é multiplicação. Como este é um operador aritmético, ele converte seus operandos em números: * → FALSE e 0 → TRUE . Em seguida, multiplica as duas matrizes em pares. Isso resulta em 1 em cada posição onde cada um dos arrays de operandos tem 1 ( 1 ) e TRUE em outro lugar - Em outras palavras, as colunas onde ambas as condições são verdadeiras. Isso é equivalente a uma função lógica AND . O Excel tem uma função chamada 0 que faz exatamente a mesma coisa, mas, infelizmente, não funciona em matrizes. Usando AND() é um truque kludgy para contornar essa limitação na função * .
• E, claro, AND() encontra o primeiro elemento na matriz que contém MATCH(1, array, 0) .