Procv com mais de um resultado matricial
Neste artigo é demonstrado como fazer um PROCV que retorne mais de um resultado utilizando matricial. Veja mais em https://www.guiadoexcel.com.br/avancado/
O PROCV é uma das funções de pesquisa e referência do Excel para localizar informações de uma lista em outra. Por exemplo buscar o nome de um cliente baseado no seu código.
Esta técnica foi disponibilizada pelo amigo Felipe Matsudo Garcia ao qual agradecemos pela valiosa contribuição.
Para realizar a busca com mais de um resultado temos um exemplo que será resolvido de duas formas diferentes, uma com coluna auxiliar e outra sem a coluna auxiliar.
Siga abaixo o exemplo e a explicação de cada uma. O download está no final do artigo e no começo dele você tem também o vídeo explicando a criação e utilização detalhada das fórmulas.
Problema
Temos uma lista aonde o código do produto se repete, mas não a sua descrição. O objetivo é retornar todos os produtos pelo seu código.
Procv com mais de um resultado com coluna auxiliar
A primeira solução consiste em utilizarmos uma lista auxiliar para indicar qual o “número” do item. Por exemplo, o primeiro item 3023 seria o item 1, o segundo 3023 o 2 e o terceiro o 3.
Desta forma é utilizada esta coluna como auxiliar para definir qual dos itens deve ser retornado naquela situação.
Criando a coluna auxiliar
Ao lado da descrição foi criada uma coluna com a fórmula: =CONT.SE($C$5:C5;C5).
A função CONT.SE faz uma contagem de células por um determinado critério, no nosso caso o critério é o código do produto.
$C$5:C5: Intervalo de contagem, aonde serão contados os itens conforme o critério. Perceba que o primeiro é travado em $C$5, isso foi feito para que ao arrastar o início do intervalo não seja alterado, mas o final dele sim, aumentando a lista na medida em que cresce.
Assim, a contagem será somente dos itens acima daquela linha que sejam daquele código, criando um sequencial único para cada produto.
C5: É o critério que será utilizado, no caso o código do produto.
Veja que a medida em que a função foi arrastada para baixo o intervalo de contagem foi alterado.
Após a criação da coluna auxiliar já podemos partir para a fórmula.
A fórmula utilizada é uma fórmula matricial, ou seja, ela atua de forma diferente de uma fórmula padrão do Excel, em outro artigo iremos falar mais a respeito e dar uma introdução clara sobre matriciais e algumas outras aplicações, por enquanto vamos focar na solução.
A fórmula utilizada foi: =SEERRO(ÍNDICE($D$5:$D$11;MENOR(SE($C$5:$C$11=$G5;LIN($D$5:$D$11)-LIN($D$5)+1);$E5));””) aplicar a função com CTRL+SHIFT+ENTER.
Abaixo a explicação das partes que compõe esta fórmula:
ÍNDICE($D$5:$D$11 : A função ÍNDICE é uma função de busca parecida com a função PROCV. Veja detalhes de como utilizá-la neste artigo: Índice Corresp Excel – Como utilizar Índice Corresp no Excel
No caso desta primeira parte temos a seleção do que desejamos que seja retornado, no caso a descrição dos produtos que se encontra neste intervalo.
MENOR(SE($C$5:$C$11=$G5;LIN($D$5:$D$11)-LIN($D$5)+1) : Esta é a parte matricial da função. A lógica é a seguinte, SE o valor em C5:C11 for igual ao valor em G5, o sistema retorna o número da linha correspondente.
Veja que o resultado deste cálculo é FALSO, FALSO, 3,4,5, FALSO, FALSO. Os valores 3,4,5 são referentes ás linhas aonde o código do produto foi encontrado, e o FALSO é aonde não foi encontrado, estes são descartados.
;$E5)) : Este parâmetro está ligado com a função MENOR, e aponta para a lista auxiliar, retornando o 1,2, 3, etc. Conforme o número que estiver na lista.
No caso deste item, se for o 1 ele retornaria 3, se for 2 ele retornará o 4 e se for o 3 o 5, conforme temos na nossa lista retornada pelo SE.
=SEERRO( : Esta função retorna um valor padrão caso seja identificado um erro no retorno da função, no caso desta está com “” no retorno.
Observação: Use o CTRL+SHIFT+ENTER para que a função seja lida como matricial.
Procv com mais de um resultado sem coluna auxiliar
Nesta outra solução não foi usada a temos uma função praticamente igual, mas com a diferença de não utilizar a coluna auxiliar.
A fórmula utilizada é:
=SEERRO(ÍNDICE($D$5:$D$11;MENOR(SE($C$5:$C$11=$G5;LIN($D$5:$D$11)-LIN($D$5)+1);CONT.SE($G$5:G5;G5)));””) aplicar a função com CTRL+SHIFT+ENTER.
Vamos replicar abaixo as pares que são semelhantes e também vamos explicar a parte do CONT.SE no final.
Abaixo a explicação das partes que compõe esta fórmula:
ÍNDICE($D$5:$D$11 : A função ÍNDICE é uma função de busca parecida com a função PROCV. Veja detalhes de como utilizá-la neste artigo: Índice Corresp Excel – Como utilizar Índice Corresp no Excel
No caso desta primeira parte temos a seleção do que desejamos que seja retornado, no caso a descrição dos produtos que se encontra neste intervalo.
MENOR(SE($C$5:$C$11=$G5;LIN($D$5:$D$11)-LIN($D$5)+1) : Esta é a parte matricial da função. A lógica é a seguinte, SE o valor em C5:C11 for igual ao valor em G5, o sistema retorna o número da linha correspondente.
Veja que o resultado deste cálculo é FALSO, FALSO, 3,4,5, FALSO, FALSO. Os valores 3,4,5 são referentes ás linhas aonde o código do produto foi encontrado, e o FALSO é aonde não foi encontrado, estes são descartados.
;$E5)) : Este parâmetro está ligado com a função MENOR, e aponta para a lista auxiliar, retornando o 1,2, 3, etc. Conforme o número que estiver na lista.
No caso deste item, se for o 1 ele retornaria 3, se for 2 ele retornará o 4 e se for o 3 o 5, conforme temos na nossa lista retornada pelo SE.
CONT.SE($G$5:G5;G5) : Nesta última parte aonde teríamos o E5 que se referiria á coluna auxiliar, nós temos a própria fórmula da coluna auxiliar.
Desta forma o CONT.SE continua funcionando da mesma forma que na coluna auxiliar e o retorno é sempre o número sequencial até aquele ponto, que é a contagem de códigos semelhantes até aquela linha.
=SEERRO( : Esta função retorna um valor padrão caso seja identificado um erro no retorno da função, no caso desta está com “” no retorno.
Observação: Use o CTRL+SHIFT+ENTER para que a função seja lida como matricial.
Baixe a planilhaVocê pode se interessar por: Como usar o PROCV no Excel
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:
