Olá pessoal seja bem vindo ao meu novo artigo! Neste artigo irei demonstrar como podemos realizar a busca de vários modos diferentes, utilizando um critério, dois critérios ou múltiplos critérios, demonstrado suas aplicações e seus desempenhos operacionais. Vamos a todas as fórmulas e suas restrições.
1° Busca Unidimensional - 1 Critério de Busca
Serão listadas abaixo várias técnicas para buscar um valor dentro de uma tabela e trazendo o valor associado, neste caso abaixo, estaremos procurando como critério de busca o maior valor "240" na coluna do Total (Coluna H) e deverá retornar o nome do Item que obteve o maior valor, neste caso o "Item J".
Lembre-se, quando você estiver desenvolvendo/construindo uma planilha deverá sempre estar à frente de quem a deseja, pois, a técnica que você utilizará tera que funcionar para 20 linhas como para 200.000 linhas ou para o limite máximo do Excel que é 1.048.576 linhas, sempre verifique se sua estratégia não está deixando "pesado" o Excel, causando lentidão e descrédito a quem for utilizar. Também verifique se sua planilha está contemplando as alterações dos dados tanto das colunas (alteração do Mês 1 para Janeiro), linhas (alteração do Item A para Produto X), na inserção ou remoção das colunas e linhas, deixando o resultado dinâmico e flexível para qualquer usuário, sem a necessidade de manutenção constante.
1.1 PROC | LOOKUP
Esta fórmula é muito interessante devido a sua simplicidade, onde é selecionado o seu critério de busca na primeira parte da fórmula, na segunda parte deverá selecionar a coluna onde se encontra o critério, na terceira parte deverá selecionar a coluna de resposta. A mesma fórmula funciona a busca em linhas.
Segue a fórmula na célula L7
=PROC(L5;H3:H12;B3:B12)ou em inglês
=LOOKUP(L5;H3:H12;B3:B12)1.2 DESLOC + CORRESP | OFFSET + MATCH
Nesta estratégia se utiliza o DESLOC, que é uma fórmula muito especial e interessante, junto com o CORRESP irá retornar a "correspondência" da linha, associando ao deslocamento de linhas com a fórmula DESLOC. Note que você não necessita selecionar o intervalo da resposta, o que faz dessa fórmula a mais especial de todo o Excel.
Segue a fórmula na célula L8
=DESLOC(B2;CORRESP(L5;H3:H12;0);0)ou em inglês
=OFFSET(B2;MATCH(L5;H3:H12;0);0)1.3 ÍNDICE + CORRESP | INDEX + MATCH
Esta combinação de fórmula é a mais difundida quando se deseja buscar um intervalo a esquerda. Também funciona a direita e é muito recomendável que você selecione a coluna inteira para que contemple todas as linhas do Excel. O interessante deste método é que você poderá adicionar colunas e linhas adicionais que a fórmula não irá se "perder".
Segue a fórmula na célula L9
=ÍNDICE(B:B;CORRESP(L5;H:H;0);0)ou em inglês
=INDEX(B:B;MATCH(L5;H:H;0);0)1.4 ÍNDICE + SOMA + LIN | INDEX + SUM + ROW
Nesta combinação, o interessante é a sua utilização de linhas com a multiplicação do critério associado ao ÍNDICE inicial. A fórmula do ÍNDICE depois da fórmula de SOMA, faz com que a conversão de um intervalo matricial em intervalo dinâmico, não necessitando da ativação do Ctrl+Shit+Enter conforme descrito nos artigos anteriores (Modelos Dinâmicos e Estáticos no Excel).
Segue a fórmula na célula L10
=ÍNDICE(B3:B12;SOMA(ÍNDICE((H3:H12=L5)*(LIN(H3:H12)-LIN(H2));0);0))ou em inglês
=INDEX(B3:B12;SUM(INDEX((H3:H12=L5)*(ROW(H3:H12)-ROW(H2));0);0))1.5 ÍNDICE + SOMARPRODUTO + LIN | INDEX + SUMPRODUCT + ROW
Esta estratégia tem o mesmo princípio do método 1.4, utilizando a fórmula do SOMARPRODUTO, realiza a conversão de um intervalo matricial em um intervalo dinâmico. Ao utilizar a fórmula do SOMARPRODUTO, deixará a sua planilha com um alto processamento e baixo desempenho computacional.
Segue a fórmula na célula L11
=ÍNDICE(B3:B12;SOMARPRODUTO(--(H3:H12=L5)*(LIN(B3:B12)-LIN(B3)+1));0)ou em inglês
=INDEX(B3:B12;SUMPRODUCT(--(H3:H12=L5)*(ROW(B3:B12)-ROW(B3)+1));0)1.6 INDIRETO + CORRESP | INDIRECT + MATCH
Nesta combinação de fórmulas, a utilização do INDIRETO se torna interessante, onde o resultado é concatenado para o texto "B12" e a fórmula do INDIRETO converte na posição da célula B12, que será a resposta. O problema de utilizar esta fórmula é o alto processamento resultando em um baixo desempenho computacional. Recomendo sempre utilizar ela quando se tem o vínculo com o nome das guias do Excel.
Segue a fórmula na célula L12
=INDIRETO("B"&CORRESP(L5;H:H;0))ou em inglês
=INDIRECT("B"&MATCH(L5;H:H;0))1.7 PROCV + ESCOLHER | VLOOKUP + CHOOSE
Esta estratégia já foi demonstrada em artigos anteriores (Os 8 PROCV's), onde a combinação do PROCV com o ESCOLHER, resultará com o pior desempenho de todas as demonstradas anteriormente. Não é recomendável utilizar essa estratégia em arquivos de grande porte no Excel.
Segue a fórmula na célula L13
=PROCV(L5;ESCOLHER({1\2};H3:H12;B3:B12);2;0)ou em inglês
=VLOOKUP(L5;CHOOSE({1\2};H3:H12;B3:B12);2;0)2° Busca Bidimensional - Dois Critérios de Busca
Será demonstrado as técnicas quando se necessita utilizar dois critérios de busca, neste caso será o "Mês 2" e o "Item D", com a combinação dos dois critérios, o resultado desejado será o "17".
Abaixo será descrito os prós e contras de todos esses métodos, mas já antecipando que a pior de todas elas e gostaria de que fosse retirado de todos os cursos de Excel, é a combinação do PROCV+PROCH, abaixo poderá verificar e validar todas essas combinações.
2.1 DESLOC + CORRESP | OFFSET + MATCH
Quando estiver realizando uma busca com dois critérios, esta técnica é a mais recomendada entre todas as demonstradas abaixo. Onde a principal vantagem é de não selecionar o intervalo da resposta, sendo que os critérios sempre estarão vinculados, adicionado ou removendo linhas e colunas, sendo que este método irá se ajustar automaticamente.
Segue a fórmula na célula P8
=DESLOC($A$1;CORRESP($P$6;$B:$B;0)-1;CORRESP($P$5;$2:$2;0)-1)ou em inglês
=OFFSET($A$1;MATCH($P$6;$B:$B;0)-1;MATCH($P$5;$2:$2;0)-1)2.2 ÍNDICE + CORRESP | INDEX + MATCH
Esta busca é interessante devido a utilização dos CORRESP's, mas sempre deverá selecionar o intervalo desejado.
Segue a fórmula na célula P9
=ÍNDICE(B2:H12;CORRESP(P6;B2:B12;0);CORRESP(P5;B2:H2;0))ou em inglês
=INDEX(B2:H12;MATCH(P6;B2:B12;0);MATCH(P5;B2:H2;0))2.3 INDIRETO + ENDEREÇO | INDIRECT + ADDRESS
Este método ele é bastante eficiente, pois não é necessário selecionar o intervalo da resposta, mas com a utilização da fórmula do INDIRETO, que tem um alto processamento, perdemos em eficiência computacional.
Segue a fórmula na célula P10
=INDIRETO(ENDEREÇO(CORRESP(P6;B:B;0);CORRESP(P5;$2:$2;0)))ou em inglês
=INDIRECT(ADDRESS(MATCH(P6;B:B;0);MATCH(P5;$2:$2;0)))2.4 SOMA + ÍNDICE | SUM + INDEX
Este método, que considero o mais simples de todos, ele transforma o intervalo matricial em dinâmico, mas tem o problema da seleção do intervalo da resposta. Outro ponto negativo deste método, só será válido para resultados numéricos, devido as operações matemáticas.
Segue a fórmula na célula P11
=SOMA(ÍNDICE((B3:B12=P6)*(C2:H2=$P$5)*(C3:H12);0))ou em inglês
=SUM(INDEX((B3:B12=P6)*(C2:H2=$P$5)*(C3:H12);0))2.5 SOMARPRODUTO | SUMPRODUCT
Esta técnica é muito eficiente quando se deseja procurar um valor numérico ou somar intervalos, quando existe em seus critérios valores duplicados, mas esta somente será válido para valores numéricos e deverá selecionar o intervalo da resposta, onde a considero de baixa desempenho computacional.
Segue a fórmula na célula P12
=SOMARPRODUTO((C3:H12)*($C$2:$H$2=$P$5)*($B$3:$B$12=$P$6))ou em inglês
=SUMPRODUCT((C3:H12)*($C$2:$H$2=$P$5)*($B$3:$B$12=$P$6))2.6 AGREGAR | AGGREGATE
Esta técnica somente poderá resultar valores númericos, pois o número 14 significa o Maior entre os valores encontrados, quando é feito a multiplicação entre os critérios ficará uma sequência de zeros e um número um, {0\0\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0;0\17\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0;0\0\0\0\0\0}, retornando o maior valor encontrado. Nesta técnica, você deverá selecionar o intervalo da resposta.
Segue a fórmula na célula P13
=AGREGAR(14;6;(C3:H12)*(C2:H2=P5)*(B3:B12=P6);1)ou em inglês
=AGGREGATE(14;6;(C3:H12)*(C2:H2=P5)*(B3:B12=P6);1)2.7 PROCV + PROCV | VLOOKUP + VLOOKUP
Esta técnica criei para desenvolver a lógica não linear na resolução de problemas, como pode ser visto, deverá criar um intervalo auxiliar ou fixar dentro da fórmula. Há vários problemas ao utilizar esta técnica, se adicionar uma coluna entre os meses, não irá funcionar. Se alterar as referências dos Meses, do "Mês 1" para "Jan", a mesma não atualizar automaticamente, caso tenha alterações constantes este método ficará inviável no dia a dia. Esta técnica já foi apresentada em artigos anteriores (Os 8 PROCV's).
Segue a fórmula na célula P14
=PROCV(P6;$B$3:$H$12;PROCV(P5;{"Mês 1"\2;"Mês 2"\3;"Mês 3"\4;"Mês 4"\5;"Mês 5"\6};2;0);0)ou em inglês
=VLOOKUP(P6;$B$3:$H$12;VLOOKUP(P5;{"Mês 1"\2;"Mês 2"\3;"Mês 3"\4;"Mês 4"\5;"Mês 5"\6};2;0);0)2.8 PROCV + PROCH | VLOOKUP + HLOOKUP
Esta técnica é a mais ensinada nos cursos de Excel para a busca matricial, deixei-a por último, pois não a recomendo a utilização desta combinação, onde deverá ser adicionada uma linha adicional contendo o número de colunas do PROCV, se adicionar/remover alguma linha ou alguma coluna deverá sempre fazer manutenção da fórmula, com isso entrando em descrédito o trabalho. Ela foi demonstrada para evitar ao máximo a sua utilização.
Segue a fórmula na célula P15
=PROCV(P6;$B$3:$H$12;PROCH(P5;{"Mês 1"\"Mês 2"\"Mês 3"\"Mês 4"\"Mês 5"\"Total";2\3\4\5\6\7};2;0);0)ou em inglês
=VLOOKUP(P6;$B$3:$H$12;HLOOKUP(P5;{"Mês 1"\"Mês 2"\"Mês 3"\"Mês 4"\"Mês 5"\"Total";2\3\4\5\6\7};2;0);0)3° Busca Multidimensional
Quando é necessário a utilização de 3 ou mais critérios, deverá se utilizar de outras metodologias para buscar os valores desejados. Que seguem abaixo
Para esse tipo de busca não é recomendável a adição de uma coluna, criando uma chave única (concatenada), pois necessitará sempre de manutenção quando for necessária a adição de uma nova linha ou de um novo registro.
3.1 ÍNDICE + CORRESP | INDEX + MATCH - 1° MÉTODO
Esta técnica é muito interessante, onde combina a fórmula do ÍNDICE associado ao CORRESP onde ele irá retornar a primeira correspondência de um intervalo de busca {0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;1}, neste caso ele retornou a posição do primeiro "1". Onde caso tenha mais de um registro, irá retornar sempre a primeira correspondência.
Segue a fórmula na célula N2
=ÍNDICE((F1:F19);CORRESP(1;ÍNDICE((A1:A19=H2)*(B1:B19=I2)*(C1:C19=J2)*(D1:D19=K2)*(E1:E19=L2);0);0))ou em inglês
=INDEX((F1:F19);MATCH(1;INDEX((A1:A19=H2)*(B1:B19=I2)*(C1:C19=J2)*(D1:D19=K2)*(E1:E19=L2);0);0))3.2 ÍNDICE + CORRESP | INDEX + MATCH - 2° MÉTODO
Esta estratégia foi apresentada pelo grande Mestre Eng. MVP Felipe Gualberto, onde mostrou esta solução para o problema, fiz uma adaptação para não necessitar da ativação do Control+Shift+Enter (CSE), adicionando a fórmula do ÍNDICE no meio para evitar a ativação do CSE .
Segue a fórmula na célula N3
=ÍNDICE(F2:F19;CORRESP(H2&"-"&I2&"-"&J2&"-"&K2&"-"&L2;ÍNDICE(A2:A19&"-"&B2:B19&"-"&C2:C19&"-"&D2:D19&"-"&E2:E19;0); 0))ou em inglês
=INDEX(F2:F19;MATCH(H2&"-"&I2&"-"&J2&"-"&K2&"-"&L2;INDEX(A2:A19&"-"&B2:B19&"-"&C2:C19&"-"&D2:D19&"-"&E2:E19;0); 0))3.2 ÍNDICE + SOMARPRODUTO | INDEX + SUMPRODUCT
Esta estratégia também foi apresentada pelo grande Mestre Eng. MVP Felipe Gualberto, onde mostrou uma outra solução ao mesmo problema.
Segue a fórmula na célula N4
=ÍNDICE(F2:F19;SOMARPRODUTO(CORRESP(H2&"-"&I2&"-"&J2&"-"&K2&"-"&L2;A2:A19&"-"&B2:B19&"-"&C2:C19&"-"&D2:D19&"-"&E2:E19;0)))ou em inglês
=INDEX(F2:F19;SUMPRODUCT(MATCH(H2&"-"&I2&"-"&J2&"-"&K2&"-"&L2;A2:A19&"-"&B2:B19&"-"&C2:C19&"-"&D2:D19&"-"&E2:E19;0)))4° Buscas Especiais
Esta seção merece um novo artigo, mas irei colocar somente duas aplicações bastante úteis quando está procurando um texto dentro de um intervalo.
Vamos as fórmulas
4.1 PROC + PROCURAR | LOOKUP + SEARCH
Esta estratégia foi apresentada pelo grande Mestre de Excel Mike Girvin, onde existe excelentes postagens de Excel em seu canal no Youtube.
Segue a fórmula na célula F2
=PROC(9999;PROCURAR(D2;$B$1:$B$6);A1:A6)ou em inglês
=LOOKUP(9999;SEARCH(D2;$B$1:$B$6);A1:A6)4.2 DESLOC + CORRESP | OFFSET + MATCH
Esta fórmula de busca foi apresentado pelo grande Mestre e amigo Eng. Dinis Fernandes, obrigado meu amigo, onde a mesma busca com os caracteres "*", onde no Excel significa qualquer letra ou número, adicionado antes e depois da palavra chave a ser procurada.
Segue a fórmula na célula F3
=DESLOC($A$1;CORRESP("*"&D2&"*"; B1:B6;0)-1;0)ou em inglês
=OFFSET($A$1;MATCH("*"&D2&"*"; B1:B6;0)-1;0)Espero que tenha gostado desse artigo!
Caso tenha alguma sugestão de outra técnica, poste aqui nos comentários.
Se gostou, curta e compartilhe este artigo para que todos saibam o que é possível fazer dentro do Excel.
E ja conhece o meu novo curso online de Excel?
Abraços a todos e até o próximo artigo!
Fabio BALDINI
Frase do dia:"Quando conhecer alguém melhor do que você, foque seu pensamento em se tornar igual. Quando conhecer alguém pior do que você, examine a si próprio" Autor: Confúcio
OBS: Quero agradecer imensamente ao Grande Mestre e Amigão Alessandro Trovato pelas eternas revisões deste artigo para que sempre fique esmero. Obrigadão meu amigo!
