Examine esta figura:
Suponha que você queira produzir um relatório a partir disso, como se você filtrasse na região. Ou seja, se você filtrar pelo Norte, verá:
Mas e se você quisesse uma versão baseada em fórmula da mesma coisa?
Aqui está o resultado que você está procurando nas colunas I: K:
Claramente, é o mesmo relatório, mas não há itens filtrados aqui. Se você quiser um novo relatório no Leste, seria bom simplesmente alterar o valor em G1 para o Leste:
Veja como isso é feito. Em primeiro lugar, isso não é feito usando VLOOKUP. Então eu menti sobre o título dessa técnica!
A coluna F não foi mostrada antes e pode ser ocultada (ou movida para outro lugar para não interferir no relatório).
O que é mostrado na coluna F são os números das linhas de onde G1 é encontrado na coluna A; ou seja, quais linhas contêm o valor “Norte”? Esta técnica envolve o uso da célula acima, assim que deve começar em, pelo menos, linha 2. Ele corresponde ao valor “Norte” contra a coluna A, mas em vez de toda a coluna, utilize uma função de deslocamento: OFFSET($A$1,F1,0,1000,1).
Como F1 é 0, isso OFFSET(A1,0,0,1000,1)é A1: A1000. (O 1000 é arbitrário, mas grande o suficiente para fazer o trabalho - você pode torná-lo qualquer outro número).
O valor 2 em F2 é onde está o primeiro “Norte”. Você também deseja adicionar de volta o valor de F1 no final, mas isso é zero, até agora.
A “mágica” ganha vida na célula F3. Você já sabe que o primeiro norte é encontrado na linha 2. Portanto, você deseja começar a pesquisar duas linhas abaixo de A1. Você pode fazer isso especificando 2 como o segundo argumento da função OFFSET.
A fórmula na F3 apontará automaticamente para o 2 que foi calculado na célula F2: Quando você copia a fórmula para baixo, você vai ver =OFFSET($A$1,F2,0,1000,1)o que é OFFSET($A$1,2,0,1000,1)que é A3: A1000. Então você está comparando o Norte com este novo intervalo e ele encontra o Norte na terceira célula deste novo intervalo, então o MATCH dá 3.
Adicionando de volta o valor da célula acima, F2, você verá o 3 mais o 2, ou 5, que é a linha que contém o segundo Norte.
Esta fórmula é preenchida o suficiente para obter todos os valores.
Isso fornecerá os números das linhas onde todos os registros do Norte são encontrados.
Como você traduz esses números de linha para os resultados nas colunas I a K? Tudo é feito com uma única fórmula. Digite esta fórmula em I2: =IFERROR(INDEX(A:A,$F2),””). Copie à direita e depois copie para baixo.
Por que usar IFERROR? Onde está o erro? Observe a célula F6 - ela contém # N / A (é por isso que você deseja ocultar a coluna F) porque não há mais Norte após a linha 15. Portanto, se a coluna F for um erro, retorne um espaço em branco. Caso contrário, pegue o valor da coluna A (e quando preenchido à direita, B e C).
O $ F2 é uma referência absoluta para a coluna F, portanto, o preenchimento à direita ainda se refere à coluna F.
Este artigo convidado é do Excel MVP Bob Umlas. É uma de suas técnicas favoritas de seu livro, Excel Outside the Box.
Excel fora da caixa »
