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

Anúncios

In Excel We Trust

Marcado com: , , , , , , , ,
Publicado em Nível: Avançado
12 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,.

  8. algum pode me ajudar?

    eu criei uma tabela …. da seguinte forma

    uma tabela principal que da os resultados normais
    outra varias tabelinhas de soma …..

    queria pegtar o resultado X das tabelinhas tipo casa H10 …
    e quando a pessoa colocar numero 1 em baixo aparecer o resultado da casa H10 …se colocar 2 aparecer o resultado da casa F10 se colocar 3 aparecer o resultado da casa G10 eassim vai …puxando os resultados das tabelinhas para a tabela principal

    sem usar macro tem como?

Deixe um comentário

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
  • 2,069,536

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

Junte-se a 1.534 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: