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.

Você pode se interessar por: Como usar o PROCV no Excel

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/

6 Comentários

  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?

  2. Nathan disse:

    Na fórmula aninhada SE($C$5:$C$11=$G5 em vez de $G5 deveria ser $C5

  3. Heloisa disse:

    Bom dia, o que está nas células da coluna G? Por que, pelo que eu vi, aí você tem as colunas C, D e E.

  4. Elaine Christine Rodrigues disse:

    Olá Marcos! Já consegui aprender muito com suas planilhas! Será que vc poderia me ajudar um pouco mais!?
    Preciso incluir uma condição na fórmula abaixo, que verifique se na célula G5 estiver como “CAN” (cancelada) ou “FAT” (faturada), caso seja “CAN” a célula J5 não deve retornar nada, caso contrário fazer o que o restante da fórmula pede. Não sei como incluir mais uma função SE.

    {=SEERRO(ÍNDICE($E$5:$E$11;MENOR(SE($C$5:$C$11=$H5;LIN($E$5:$E$11)-LIN($E$5)+1);CONT.SE($H$5:H5;H5)));””)}

    Se puder me ajudar ficarei muito grata!

  5. Ingrid disse:

    Olá! Poderia me ajudar? Tentei refazer esta mesma planilha para entender melhor a fórmula, e não consigo com que a primeria fórmula puxe os 3 itens, além do mais não entendi qual é o valor em “G5”.

  6. Ivan Varella disse:

    Prezado Marcos,

    Estou com um problema no Excel, que talvez seja o problema de muita gente e também um desafio! Fiz uma planilha para uma pequena loja de roupas,
    Talvez sirva pra muita gente a sua solução.

    Eis o Problema: (Tudo hipotético)

    1 – Fiz uma venda = 500,00 (tudo em linha um para facilitar o entendimento)
    A pessoa pode pagar de 1, 2 ou 3 vezes. Vou chamar essa coluna de Nº de Parcelas (até 3)

    2- Nessa coluna uma pessoa pode escolher se quer pagar de 1, 2 ou 3 vezes.
    3- Fiz outras 3 colunas:

    a) Coluna 1 = 2% de acréscimo
    b) Coluna 2 = 4% de acréscimo
    c) Coluna 3 = 6% de acréscimo

    4) Se a pessoa escolheu pagar em 30 dias, o valor de venda é multiplicado por 2% que esta fixado no topo
    da coluna que representa 2%

    5) Se a pessoa escolheu pagar em 60 dias, o valor de venda é multiplicado por 4% que esta fixado no topo
    da coluna que representa 4%

    6) Se a pessoa escolheu pagar em 90 dias, o valor de venda é multiplicado por 6% que esta fixado no topo
    da coluna que representa 6%

    A questão é: quero que quando a pessoa digite (digamos 2 na Coluna “Nº de parcelas”) o resultado saia
    na coluna correspondente ou seja, coluna de 4% e dê o resultado na coluna e célula em linha da coluna 4.

    Ex.: Vendi R$ 500,00. A pessoa escolheu pagar de duas vezes. O Valor de juros que será aplicado é de 4%.
    Nesse caso, o juros seria de R$ 20,00 e seria alocado somente nessa coluna de 4%.
    Se a venda fosse dividida em 3 pagamentos, o resultado dos juros cairia na coluna equivalente 3 parcelas
    na mesma linha.Portanto, este é o problema que os versados em Excel, podem resolvê-lo com facilidade.
    Penso que a solução está em escolher a formula certa para adequar a solução. Ache que a função matricial
    combinada com outras funções poderá fazer com que a tabela funcione perfeitamente! Gostaria que aceitasse este desafio!

    Por email, posso enviar a estrutura já montada da tabela!

    Agradeço a atenção e deixo o meu email: navicvarella@gmail.com – Ivan Varella

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.