PROCV com múltiplos critérios

Embora seja uma das funções mais utilizadas no Excel, e um verdadeiro divisor de águas entre quem a conhece e quem ainda não teve esse… prazer, poucos a dominam por completo ou sabem contornar suas limitações.

Neste artigo você vai aprender a como fazer um “PROCV” com múltiplos critérios. PROCV entre aspas pois você não verá apenas como obter esse resultado com o PROCV, mas também com a união das funções ÍNDICE + CORRESP e também com a função SOMARPRODUTO.

Saiba porque ÍNDICE + CORRESP é superior ao PROCV, clique aqui

Sugiro que leia todo o artigo ou assista o video abaixo por completo para entender todas as vantagens e desvantagens de cada um desses 3 métodos, e os diferentes pontos de atenção no uso de cada um. O vídeo e o texto cobrem os mesmos pontos, fique à vontade para escolher ler ou assistir.

---

Vídeo de PROCV com múltiplos critérios

Coloque o vídeo em tela cheia para assistir normalmente

---

Download do arquivo utilizado nos exemplos do vídeo e do texto

[sociallocker id=”30968″]

Download “PROCV com múltiplos critérios”

PROCV com múltiplos critérios.xlsx – Baixado 4987 vezes – 22,35 KB

---

Exemplo Prático de PROCV com múltiplos critérios

Para ambas as 3 funções, usaremos o mesmo exemplo de dados, para facilitar o entendimento e diferenciação de cada uma.

Na imagem abaixo, temos à esquerda uma tabela com nomes de vendedores e quantidade vendida de cada um, de acordo com o região de venda (norte e sul) e o turno (manhã e noite). Cada vendedor se repete 4 vezes na lista, com combinações de turno e região diferentes.

À direita temos um campo de pesquisa já preenchido com:
Vendedor = Rafael
Região = Norte
Turno = Noite

E logo abaixo, um espaço para utilizarmos as 3 diferentes fórmulas que montaremos aqui.

Assim, o que queremos buscar é apenas a quantidade vendida pelo Rafael na região norte no turno da noite. Ou seja, um “PROCV” com 3 critérios de busca simultâneos.

---

Função Matricial – IMPORTANTE PARA QUE TUDO FUNCIONE

As fórmulas que montaremos aqui para as função PROCV e ÍNDICE + CORRESP, são funções matriciais, e só irão funcionar se após escrever a fórmula, ao invés de pressionar apenas a tecla ENTER, você pressionar, ao mesmo tempo, as teclas CTRL + SHIFT + ENTER, você notará que na barra de fórmulas a sua fórmula aparecerá entre chaves {Sua_fórmula} indicando que é uma fórmula matricial.

Para saber um pouco mais sobre fórmulas matriciais clique aqui

A função SOMARPRODUTO já é uma função matricial por natureza, não é necessário finalizá-la com CTRL + SHIFT + ENTER.

---

PROCV – Como utilizar

Essa é a função que utilizaremos na célula J7 da imagem do exemplo prático.

=PROCV(H4&I4&J4;B3:E14&C3:F14&D3:G14;4;FALSO)

Lembre de finalizar com CTRL + SHIFT + ENTER.

Caso fossemos fazer um PROCV simples, para buscar apenas pela primeira referência do vendedor Rafael, nossa fórmula seria:

=PROCV(H4;B3:E14;4;FALSO)

Onde temos apenas um valor_procurado e uma matriz_tabela dentro do PROCV. Como neste caso temos 3, o que fizemos foi concatenar (unir com &) os 3 valor_procurado e as 3 matriz_tabela.

Note que cada matriz_tabela inicia na coluna onde está o dado que se procura.

B3:E14 = Coluna B = Vendedor = Rafael
C3:F14 = Coluna C = Região = Norte
D3:G14 = Coluna D = Turno = Noite

Você deve estar se perguntando porque somente a primeira matriz_tabela termina na coluna E, onde estão os dados de venda, enquanto as demais terminam depois da tabela de dados buscada.

Isso acontece pois todas as matriz_tabela utilizadas na função devem ter o mesmo tamanho em linhas e colunas. Caso contrário obteremos um erro de fórmula.

Já o argumento núm_índice_coluna, que indica a quantas colunas o dado desejado está da primeira coluna selecionada na matriz_tabela, deve conter apenas um número, e levar em conta apenas a primeira matriz_tabela. Neste exemplo, 4, pois os dados de venda estão na coluna E, a 4 colunas de B na matriz_tabela B3:E14.

---

PROCV – Vantagens

1. Função mais conhecida pela público em geral.
   Para a maioria significa não ter que necessariamente aprender uma nova função (Isso não faz sentido pra mim).

---

PROCV – Desvantagens

1. Função matricial forçada
   Dependendo do tamanho da tabela de dados, a função pode se tornar extremamente pesada, lenta e inviável.
2. Busca dados de colunas que não interessam
   Como vimos logo acima as 3 matriz_tabela precisam ter, obrigatoriamente, o mesmo tamanho, o que significa que 2 delas terminam em colunas vazias. Porém essas colunas também são buscadas no resultado do PROCV (assista ao vídeo para mais detalhes).
   

---

ÍNDICE + CORRESP – Como utilizar

Essa é a função que utilizaremos na célula J8 da imagem do exemplo prático.

=ÍNDICE(E3:E14;CORRESP(H4&I4&J4;B3:B14&C3:C14&D3:D14;0))

Lembre de finalizar com CTRL + SHIFT + ENTER.

O uso diferente do que estamos acostumados, está na função CORRESP somente, utilizada dentro da função ÍNDICE. Este uso diferenciado é semelhante ao uso que fizemos no PROCV acima, concatenamos (unir com &) os argumentos valor_procurado e matriz_procurada da função CORRESP.

Caso quiséssemos encontrar apenas a primeira referência do vendedor Rafael, nossa função seria:

=ÍNDICE(E3:E14;CORRESP(H4;B3:B14;0))

E assim como no PROCV, temos então 1 valor_procurado e uma matriz_procurada para cada critério de busca.

B3:B14 = Coluna B = Vendedor = Rafael
C3:C14 = Coluna C = Região = Norte
D3:D14 = Coluna D = Turno = Noite

---

ÍNDICE + CORRESP – Vantagens

1. Superior ao PROCV
   De visualização mais clara e sem a desvantagem de buscar colunas desnecessárias.

---

ÍNDICE + CORRESP – Desvantagens

1. Função matricial forçada
   Dependendo do tamanho da tabela de dados, a função pode se tornar extremamente pesada, lenta e inviável.

---

SOMARPRODUTO – Como utilizar

Essa é a função que utilizaremos na célula J9 da imagem do exemplo prático.

=SOMARPRODUTO((B3:B14=H4)*(C3:C14=I4)*(D3:D14=J4)*(E3:E14))

Essa já é bem diferente de qualquer uso mais comum que damos a função SOMARPRODUTO, além dela por si só já não ser uma função muito usual para a maioria.

Calculando média ponderada com SOMARPRODUTO clique aqui

Note que cada matriz é separada da outra por parenteses, e então multiplicada umas as outras.

Sabendo que a função SOMARPRODUTO é uma função matricial por natureza, assim, os seguinte trechos da fórmula:

• (B3:B14=H4)
• (C3:C14=I4)
• (D3:D14=J4)

Resultarão em 3 matrizes de VERDADEIRO e FALSO, abaixo (Atente para a cor verde mais adiante você entenderá):

• {VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;FALSO}
• {FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;VERDADEIRO}
• {FALSO;FALSO;VERDADEIRO;FALSO;VERDADEIRO;VERDADEIRO;FALSO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;VERDADEIRO}

VERDADEIRO aparece sempre que algum dos itens contidos no intervalo analisado for igual a célula comparada. Ou seja, sempre que uma célula do intervalo B3:B14 for igual a célula H4, obteremos VERDADEIRO, caso contrário obteremos FALSO.

VERDADEIRO equivale ao número 1, e FALSO equivale ao número 0. Assim ao multiplicar uma matriz com a outra obtemos uma nova matriz de 0 e 1. Assim, a matriz resultante da multiplicação de (B3:B14=H4)(C3:C14=I4)(D3:D14=J4) é {0;0;0;0;0;0;0;1;0;0;0;0} (olha a cor verde ali de novo).

Note que apenas o 8º elemento da matriz resultante é 1, e todos os demais é 0. Isso acontece pois só o 8º elemento das 3 matrizes iniciais é VERDADEIRO, ou seja 1. E 1 x 1 x 1 = 1. Enquanto que qualquer coisa multiplicada por 0 é 0.

Por fim, essa matriz de 0 e 1 é multiplicada pela matriz que contém os números das vendas. Como apenas um deles contém o número 1, apenas o 8º elemento da matriz com os números das vendas aparecerá no resultado final, e todos os demais serão ignorados, pois são multiplicados por 0.

---

SOMARPRODUTO – Vantagens

1. Função matricial natural
   Ao contrário das demais, esta não irá forçar ou pesar o arquivo, pois ela já é naturalmente uma função matricial.
2. Supera PROCV e ÍNDICE + CORRESP
   A fórmula é limpa e de fácil visualização, não utiliza colunas desnecessárias e também não tem problemas de performance dependendo do tamanho da tabela de dados.

---

SOMARPRODUTO – Pontos de atenção (não tem desvantagem)

1. Resultado inesperado (Sugiro assistir ao vídeo para melhor explicação visual)
   Como visto na explicação do funcionamento da fórmula, várias matrizes são multiplicadas umas as outras. Obtivemos os resultado final esperado pois apenas um mesmo elemento, em todas as matrizes resultou VERDADEIRO.
   Caso utilizássemos apenas 2 critérios, mas o mesmo exemplo usado aqui. Ou seja, ignorar o turno e buscar apenas por Rafael e Norte, o SOMARPRODUTO nos retornaria o resultado da soma das vendas efetuadas pelo Rafael na região Norte, que são duas, uma no turno da noite e outra no turno da manhã.
   
   Isso acontece pois agora a multiplicação das matrizes (B3:B14=H4)*(C3:C14=I4) resultará na matriz {0;0;0;1;0;0;0;1;0;0;0;0} pois tanto o 4º quando o 8º elemento das matrizes originais é VERDADEIRO.Por isso, um ponto de atenção, não uma desvantagem, pois dependendo do que se deseja, essa pode ser mais um ponto positivo para o SOMARPRODUTO.

Espero ter ajudado. Qualquer dúvida deixe seu comentário logo abaixo.

---

Acompanhe o Função Excel