PROCV com dados repetidos
A função PROCV (clique aqui para conhecer mais) nos permite buscar um dado correspondente a outro de forma bastante rápida e simples. É sem dúvida uma das funções mais utilizadas no Excel, porém, possui algumas limitações, e uma delas é que o PROCV busca sempre a primeira referência do valor informado, ou seja, se você está buscando pela palavra abelha em uma lista onde esta palavra aparece mais de uma vez, esta função encontrará sempre somente a primeira referência.
Para contornar este problema precisamos utilizar uma fórmula matricial (clique aqui para conhecer mais).
Vídeo Explicativo
Com preguiça de ler? Assista ao vídeo. (O exemplo utilizado no vídeo é diferente do utilizado aqui). Caso contrário, é só descer mais um pouco e continuar a leitura.
Coloque o vídeo em tela cheia para assistir normalmente
Decidiu continuar lendo?! Tudo bem, vamos lá!
Devo dizer, porém, que embora o título deste artigo e seu primeiro parágrafo falem sobre a função PROCV, não utilizaremos nada dela aqui, apenas falei dela para facilitar o entendimento do que será feito. O que utilizaremos mesmo será a função ÍNDICE.
(Caso este artigo não te ajude, temos outro que também cuida de dados repetidos, clique aqui para acessar)
Usando função matricial para valores repetidos
A tabela abaixo possui alguns dados fictícios de produtos e seus preços em diferentes estados do Brasil.
Caso quiséssemos buscar os dados do produto celular utilizando o PROCV teríamos:
E não importa que fizer, sempre somente o primeiro resultado será buscado. Por mais que se tente arrastar a função para as células de baixo, ou qualquer coisa parecida.
Para conseguir os dados teremos de utilizar uma fórmula matricial. Para este exemplo a fórmula seria conforme a imagem abaixo.
=SEERRO(ÍNDICE($B$3:$D$13;MENOR(SE($B$3:$B$13=$F$3;LIN($B$3:$B$13)-LIN($B$2));LIN(1:1));3);"")
Não sabe para que servem os $ na fórmula? Clique aqui e conheça
Pode parecer um pouco complexa de cara, mas vamos entender cada pedaço desta fórmula e para que serve.
Funções SE e LIN
A função SE compara se dois valores são iguais ou não, sendo verdadeiro, o Excel executará um ação, sendo falso, executará outra ação diferente.
Já a função LIN indica em qual linha uma referência está.
No caso do exemplo, a função SE investiga se o nome do produto procurado é igual a um dos dados presentes no intervalo B3:B13 (isso é feito para cada uma das células do intervalo por se tratar de uma fórmula matricial).
Sempre que um dos testes da função SE der VERDADEIRO, é retornado o número da linha da célula do intervalo B3:B13 que foi igual ao valor procurado, subtraído do número da linha do cabeçalho da tabela. Esta subtração é feita pois o intervalo selecionado pode começar em qualquer linha da planilha, depende de como cada um montar os dados, assim quando subtrair o número da linha encontrada pelo número da linha do cabeçalho da tabela, sempre teremos o número da linha em que o dado se encontra dentro do intervalo selecionado. No nosso exemplo, a primeira vez que a palavra celular aparece é na 3º linha da planilha, porém na 1º linha da tabela, como o cabeçalho está na linha 2 da planilha, 3 – 2 = 1.
Função MENOR
A função MENOR retorna o enésimo menor valor encontrado em um conjunto de dados. Este “enésimo” valor é definido pela segunda função LIN que aparece.
A primeira parte da função MENOR que preenchemos com a função SE e a 1º função LIN nos retorna uma matriz com as linhas que possuem o valor procurado. Conforme a imagem.
Note que o produto celular aparece nas linhas 1, 4 e 8 da tabela (não da planilha), as outras linhas onde não é encontrada aparece FALSO.
Aí entra a 2º função LIN, que indica o enésimo menor valor, ou seja, o enésimo menor número de linha encontrado. Como esta segunda função inicia na primeira linha da planilha e depois vai descendo, primeiro dirá a função MENOR que deverá buscar o 1º menor valor contido na matriz (imagem anterior), para este caso, será o número 1. Quando a fórmula for arrastada para baixo, a função LIN passará a informar à função MENOR que deverá buscar o 2º menor valor contido na matriz, neste caso será o número 4. Assim por diante.
Função ÍNDICE
Você pode conhecer mais sobre esta função clicando aqui mas em linhas gerais, ela retorna um dado de uma matriz qualquer, dada a linha e coluna em que este dado se encontra.
No exemplo que estamos trabalhando é o resultado da função MENOR e todas as demais que a compõem que dirão a função ÍNDICE qual linha da tabela está o dado desejado.
Já a coluna será definida manualmente, onde para buscar os dados de Estado utilizamos o número 2, por estar na segunda coluna da tabela, e o número 3 para os dados de Preço. Nada impediria de utilizar alguma outra função para isso, mas neste caso não vale a pena o trabalho.
Função SEERRO
Esta função não participa ativamente do cálculo para buscar os dados repetidos, serve apenas por uma questão estética.
Como pôde notar, deixei algumas linhas em branco no quadro Dados encontrados.
Fiz isso intencionalmente, acreditando que nenhum dos dados procurados se repetisse mais de 7 vezes na base de dados. Assim, quando ultrapassamos o número de vezes que o dado procurado se repete na base nossa fórmula começa a dar erro, por não encontrar mais estes dados. No caso do celular o 2º LIN indicará à função MENOR que devera buscar o 4º menor valor na matriz, porém, o produto celular só se repete 3 vezes na base, logo não existe o 4º menor número, o que causa o erro na fórmula.
A função SEERRO envolve toda a fórmula e caso o resultado seja um erro, é escrito “” que para o Excel significa vazio.
IMPORTANTE – PARA QUE TUDO FUNCIONE
Como dito, o que foi ensinado aqui se trata de uma fórmula matricial e é necessário informar ao Excel isso, pois ele não identifica isso automaticamente. Ao terminar de escrever sua fórmula ao invés de apertar apenas tecla ENTER para concluir, aperte CTRL + SHIFT + ENTER. Só assim a fórmula irá funcionar. Depois arraste (não copie, arraste) a fórmula para baixo para buscar os demais dados. Se você fizer diferente disso, não irá funcionar, você notará que na barra de fórmulas a sua fórmula aparecerá entre chaves {Sua_fórmula} indicando que é uma fórmula matricial. Para saber um pouco mais de fórmulas matriciais clique aqui.
Espero ter ajudado. Qualquer dúvida deixe seu comentário logo abaixo.
(Caso este artigo não te ajude, temos outros que também cuida de dados repetidos, clique aqui para acessar)
Acompanhe o Função Excel
Tenho uma dúvida em uma questão parecida. Meu Excel não tá aceitando esses valores e está mandando sempre o primeiro valor.
Me ajudou muito, resolveu o meu problema de forma fácil. Muito obrigada!!!
Ajudou para resolver a 1º posição do meu ranking; a partir da 2º posição do ranking, o nome do jogador não apareceu e na 4º e 5º posição, aparecem os primeiros jogadores com a mesma pontuação, mas não consegui que aparecesse o nome de ambos. Na vdd, não está aparecendo nenhum nome.
de todo modo, muito obrigado pela aula. Aprendi bastante com esse exercício!!!!!!
VLW!