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

Sobre adilsonsoledade

In Excel We Trust

‹ 0094-Modelo de planilha para cálculos financeiros

0096-Algumas Funções de Data / Hora ›

Marcado com: Array Formulas, Excel, Fórmulas Matriciais, Múltiplos retornos, Pesquisa, Planilhas, PROCV, Spreadsheet, VLOOKUP
Publicado em Nível: Avançado