Procv com mais de um resultado matricial

Gráfico Heat Map – Gráfico de calor no Excel
19 de maio de 2018
Abrir arquivo texto grande com power query capa
Abrir arquivo com mais de um milhão de linhas no Excel
1 de junho de 2018

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 1

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.

Procv com mais de um resultado 2

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.

Procv com mais de um resultado 3

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.

Procv com mais de um resultado 3

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.

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/
Cursos

Curso Excel Básico – 1 ano de acesso

R$99,00

COMPRAR
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR

1 Comentário

  1. COELHO disse:

    complicando mais um pouco…

    Digamos que eu faça tudo isso e queira que ele filtre por descrição e apareça somente (jaquetas) como fazer?

Deixe uma resposta

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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.