0095-Simulando PROCV com múltiplos retornos

Nós já vimos que o PROCV tem a limitação de retornar apenas a primeira ocorrência de um valor numa referência pesquisada.

Vimos também uma abordagem utilizando UDFs para retornar múltiplos valores coincidentes.

É possível também fazê-lo utilizando fórmulas matriciais.

Este é o exemplo que utilizaremos:

Iremos pesquisar quais os valores de vendas correspondentes ao vendedor A.

Vamos inserir na célula E2 o valor que será pesquisado.

Agora vamos às etapas da construção da fórmula matricial. A cada etapa será incorporada a fórmula obtida na etapa anterior.

1. Precisamos determinar em que posições do intervalo A2:A10 ocorrem valores iguais ao valor inserido em E2. Selecionando o intervalo J2:J10, vamos inserir a fórmula matricial
=($A$2:$A$10=$E$2).
O resultado é uma matriz de valores VERDADEIRO e FALSO com base no resultado da comparação

2. Um vez sabendo quais os valores que correspondem ao valor pesquisado, vamos determinar em que linhas estes valores ocorrem. No intervalo K2:K10, vamos inserir a fórmula matricial
=($A$2:$A$10=$E$2)*LIN($A$2:$A$10).
O resultado é uma matriz com os valores das linhas em que ocorreram as coincidências com o valor pesquisado. Quando multiplicamos duas matrizes VERDADEIRO se comporta como o valor um e FALSO como o valor zero.

Vamos agora ordenar as linhas para que os valores que desejamos apareçam de forma sequencial. Precisamos determinar a 1ª menor linha em que ocorreu a coincidência, depois a 2ª menor linha e assim por diante. Temos um pequeno problema, se aplicarmos a fórmula =MENOR(($A$2:$A$10=$E$2)*LIN($A$2:$A$10);1) iremos obter o valor zero, já que a matriz que calculamos em 2 deverá conter zeros em todas as linhas nas quais não foi encontrado o valor pesquisado. Mas podemos contornar a situação se determinamos quantos são os valores zero e começarmos a buscar o menor valor na posição equivalente ao número de zeros mais um. E em seguida acrescentar um unidade sequencialmente até obter todos os valores diferentes de zero.

3. Para determinar o número de zeros, vamos inserir no intervalo L2:L10 a fórmula matricial:
=SOMARPRODUTO(N(($A$2:$A$10=$E$2)*LIN($A$2:$A$10)=0)).

4. Agora iremos criar o sequencial de valores que vai se iniciar em Nº de Zeros + 1 e será incrementado um valor a cada linha. A melhor forma de fazê-lo é utilizar a função LIN() que retorna o número da linha da célula. Como estamos num intervalo vertical, cada célula será uma unidade maior do que a célula localizada imediatamente acima. Como iniciamos o intervalo na célula da linha 2 e queremos iniciar a contagem a partir do valor 1, vamos corrigir esta defasagem subtraindo uma unidade do valor de LIN(). Inseriremos então em M2:M10 a fórmula matricial

=SOMARPRODUTO(N(($A$2:$A$10=$E$2)*LIN($A$2:$A$10)=0))+LIN()-1

5. Podemos agora retornar a série dos menores valores, colocando em N2:N10 a fórmula.

=MENOR(($A$2:$A$10=$E$2)*LIN($A$2:$A$10);
SOMARPRODUTO(N(($A$2:$A$10=$E$2)*LIN($A$2:$A$10)=0))+LIN()-1)

Neste ponto a fórmula apresenta o valor de erro #NÚM! nas células em que não foi possível localizar um valor correspondente na série dos menores valores. Vamos deixar o tratamento deste erro para a conclusão da fórmula

6. Agora que temos as linhas em que ocorrem as coincidências devidamente ordenadas, vamos utilizar a função ÍNDICE para retornar os valores correspondentes da coluna B, que contém os valores de vendas.

=ÍNDICE($B$1:$B$10;MENOR(($A$2:$A$10=$E$2)*LIN($A$2:$A$10);
SOMARPRODUTO(N(($A$2:$A$10=$E$2)*LIN($A$2:$A$10)=0))+LIN()-1))

Retornamos então os múltiplos valores da coluna B que correspondem a um valor pesquisado na coluna A. Temos um último detalhe para resolver que é a exibição das mensagens de erro. Vamos inserir em H2:H10 a fórmula:

=SEERRO(ÍNDICE($B$1:$B$10;MENOR(($A$2:$A$10=E2)*LIN($A$2:$A$10);
SOMARPRODUTO(N((($A$2:$A$10=E2)*LIN($A$2:$A$10))=0))+LIN()-1));””)

OBS: Se você trabalha com uma versão do Excel anterior à 2007 utilize esta variante da fórmula.

=SE(ÉERROS(ÍNDICE($B$1:$B$10;MENOR(($A$2:$A$10=E2)*LIN($A$2:$A$10);
SOMARPRODUTO(N((($A$2:$A$10=E2)*LIN($A$2:$A$10))=0))+LIN()-1)));””;
ÍNDICE($B$1:$B$10;MENOR(($A$2:$A$10=E2)*LIN($A$2:$A$10);
SOMARPRODUTO(N((($A$2:$A$10=E2)*LIN($A$2:$A$10))=0))+LIN()-1)))

Veja neste link a fórmula final e os passos para obtenção: Múltiplos Retornos

In Excel We Trust

Marcado com: , , , , , , , ,
Publicado em Nível: Avançado
10 comentários em “0095-Simulando PROCV com múltiplos retornos
  1. Wellington disse:

    Não entendi muito bem os exemplos de fórmulas matriciais. Vou dar uma procurada no seu blog sobre isso.

  2. Miguel Rodrigues disse:

    Achei muito util este artigo, mas será possivel na mesma formula ter também os multiplos retornos para vários vendedores?
    Ou melhor, tal como no exemplo apresentado que apareça imediatamente a seguir, e na vertical com uma linha de intervalo outro ou outroa vendedores, estou tentando há duas semanas e não estou conseguindo.
    estou com dificuldade na criação do algoritmo de referenciação das linhas.

    • Bom dia, Miguel.
      A forma mais simples que enxergo é que no trecho ($A$2:$A$10=$E$2) da fórmula, utilize a referência absoluta apenas para a linha ($A$2:$A$10=E$2).
      Em seguida copie a fórmula para as colunas adjacentes e coloque em F2, G2, etc os nomes dos vendedores cujos resultados deseja pesquisar.
      Podemos também transpor os resultados de forma a colocar os nomes a serem pesquisados numa mesma coluna e retornar os resultados em linha.
      Abraços!

  3. Marcilio disse:

    Olá amigo, excelente post.
    Estava realmente precisando desta informação. Contudo, já tentei diversas vezes e baixei sua planilha, mas até agora não identifiquei porque raios não funciona o passo 1.

    Veja só, copiei as colunas A e B do exemplo de 2 a 10, com os mesmos valores. Quando digito, =($A$2:$A$10=$E$2) (seja matricial ou comum) o resultado é sempre verdadeiro (matricial) ou sempre falso (comum) quando arrasto de J2 a J10!

    Mesmo eu copiando o exemplo para a mesma pasta da sua planilha, também não funciona. Tem alguma ideia do que possa ser? Abs.

  4. Talita disse:

    Boa Tarde,

    Muito útil essas informações. Desde já agradeço.

    Mas estou tentando fazer este passo:
    2. Um vez sabendo quais os valores que correspondem ao valor pesquisado, vamos determinar em que linhas estes valores ocorrem. No intervalo K2:K10, vamos inserir a fórmula matricial
    =($A$2:$A$10=$E$2)*LIN($A$2:$A$10).
    O resultado é uma matriz com os valores das linhas em que ocorreram as coincidências com o valor pesquisado. Quando multiplicamos duas matrizes VERDADEIRO se comporta como o valor um e FALSO como o valor zero.

    É só me retorna valores iguais a 2.

    O que fazer?

    Att,
    Talita Cappelari

  5. Edson disse:

    Esse tipo de fórmula matricial é muito boa, mas tenho uma dúvida se houvesse uma mesma tabela da coluna A e B na coluna D e E, qual seria solução para a formula busca o mesmo resultado

  6. elizandro disse:

    como fazer o resultado final aparecer em ordem decrescente…

  7. minha planilha ficou muito lenta, demora a processar tipo uns 10 minutos, usei essa formula em 350 linha…e dora muito a calcular como pode me ajudar a resolver esse problema? ate tentei usar o PROVN por macro mas continua lento do mesmo jeito,.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 1,964,110

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.476 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: