Busca aproximada Excel – Índice Corresp

Neste artigo você verá como fazer uma busca aproximada no Excel. Utilizaremos para isso Matriz as funções Mínimo, SE, Índice, Corresp, ABS e Seerro.

Esta semana me pediram na empresa para que eu realizasse a comparação de duas listas de dados e retornasse os valores mais próximos entre eles.

As duas listas possuem três colunas, uma com nome, outra com valor a ser comparado e uma outra a preencher com o valor mais próximo na Lista 2 quando for aquele mesmo nome na coluna 1.

Temos então o seguinte problema: “Como retornar o valor mais próximo quando for o José Sarney?”

Para resolver o problema temos a seguinte resposta: “Retornando o valor no qual o nome for José Sarney e a diferença absoluta entre o valor da lista 1 e da lista 2 for mínimo.”

Com esta resposta podemos pensar por partes na solução deste problema.

Primeiro fiz uma coluna separada com uma função que retorna apenas a menor diferença entre dois valores:

=MÍNIMO(SE(B10=’Lista 2′!$B$10:$B$19;ABS(‘Lista 1’!C10-‘Lista 2’!$C$10:$C$19))), para aplicá-la pressione CTRL+SHIFT+ENTER, pois esta função é matricial.

Na primeira parte SE(B10=’Lista 2′!$B$10:$B$19 verificamos se o valor na lista 1 é o mesmo na lista 2, se for igual ele retorna ABS(‘Lista 1’!C10-‘Lista 2’!$C$10:$C$19)) uma lista com a diferença absoluta entre a célula de valor da lista 1 e os valores da lista 2.

A diferença absoluta faz com que a diferença retornada seja sempre positiva, fazendo com que possamos encontrar a menor diferença quando aplicamos á esta fórmula a função MÍNIMO.

Esta fórmula já retorna a menor diferença entre as duas listas, veja:

Perceba que é sempre retornada a menor diferença, como por exemplo José Sarney na lista 1 com o valor 30 e na lista 2 possui o valor 38, retornando a diferença 8.

Veja que a diferença para José Sarney : 5 é 0, pois na lista 2 existe um José Sarney com o valor 5 também, e ABS(5-5) = 0.

Desta forma temos apenas a diferença entre as listas, para resolver então o problema e retornar o valor correto poderíamos fazer a mesma fórmula na lista 2 e unir os dados do nome e do valor e fazer um procv para retornar o valor exato.

Usamos para isso uma coluna auxiliar aonde concatenamos o nome do presidente com o resultado da função que vimos agora em seguida.

Em seguida usamos uma simples fórmula de PROCV para retornar os valores da Lista 2 com a menor diferença.

Fórmula de concatenação do nome com a diferença: =B10&”-“&E10.

Fórmula de busca aproximada com PROCV para retornar o valor conforme a coluna auxiliar montada =SEERRO(PROCV(F10;’Lista 2′!A:C;3;0);””).

Até aqui já resolvemos o problema, mas podemos criar uma nova fórmula matricial que retorne o valor sem a coluna auxiliar:

=SEERRO(ÍNDICE(‘Lista 2’!$C$10:$C$19;CORRESP(MÍNIMO(SE(B10=’Lista 2′!$B$10:$B$19;ABS(‘Lista 1’!C10-‘Lista 2’!$C$10:$C$19)));SE(B10=’Lista 2′!$B$10:$B$19;ABS(‘Lista 1’!C10-‘Lista 2’!$C$10:$C$19);”X”);0);1);””) aplicar com o CTRL+SHIFT+ENTER.

Na parte aonde temos (MÍNIMO(SE(B10=’Lista 2′!$B$10:$B$19;ABS(‘Lista 1’!C10-‘Lista 2’!$C$10:$C$19)));SE(B10=’Lista 2′!$B$10:$B$19;ABS(‘Lista 1’!C10-‘Lista 2’!$C$10:$C$19);”X”);0)

A parte da fórmula em vermelho já foi desenvolvida na primeira parte do artigo, na segunda parte: ;SE(B10=’Lista 2′!$B$10:$B$19;ABS(‘Lista 1’!C10-‘Lista 2’!$C$10:$C$19);”X”);0), temos a criação da lista aonde o nome seja igual e trazemos uma lista aonde preencheremos com valores ou X de acordo com a lista. Retornando desta forma para a lista em José Sarney: {“X”;”X”;”X”;8;25;22;”X”;”X”;”X”;”X”}.

Assim na primeira parte da fórmula aonde temos o valor mínimo, é pesquisado o valor 8 dentro desta lista utilizando a função CORRESP e retornando a posição do menor valor, no caso a posição 4, referente ao valor “8”.

O X é necessário para que ele complete a lista com valores caso não localize, senão a lista ficará incompleta e não retornará a posição correta.

Caso queira ver a implementação passo-a-passo da fórmula de busca aproximada veja o vídeo no início deste artigo, ou faça o download do arquivo diretamente abaixo preenchendo seu nome e e-mail para se inscrever em nossa newsletter gratuita e receber semanalmente dicas de Excel e VBA.

Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: