Busca aproximada Excel – Índice Corresp

Entrevista João Benito Savastano
Entrevista com o MVP Excel João Benito Savastano
12 de fevereiro de 2017
Mapa de estoque dinâmico Excel Logística WMS
Mapa de Estoque Excel Logística WMS
28 de fevereiro de 2017

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.

Busca aproximada de valores com condições 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:

Busca aproximada de valores com condições 2

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.

Busca aproximada de valores com condições 3

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.

Busca aproximada de valores com condições 4

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.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.