As Top 11 Alternativas ao PROCV (atualizado em 2022!) – Excel e Google Planilhas
Este tutorial vai demonstrar as melhores alternativas ao PROCV no Excel e no Google Planilhas.
1. Função PROCX
Se você tiver uma versão atualizada do Excel, a função PROCX é provavelmente a melhor alternativa à função PROCV.
PROCX: Pesquisa à Esquerda
A matriz de pesquisa e a matriz de retorno da função PROCX são argumentos separados. Isso traz as seguintes vantagens:
- Não há necessidade de ajustar a fórmula quando as colunas são inseridas ou excluídas.
- Não há necessidade de inserir intervalos/matrizes de várias colunas quando as colunas de pesquisa e de retorno não são adjacentes. Isso reduz a quantidade de dados a serem processados.
- Capacidade de realizar pesquisas à esquerda (veja o exemplo):
=PROCX(F3;D3:D7;C3:C7)
PROCX: Retorno de mais de uma Coluna
Um recurso da função PROCX é a possibilidade de retornar mais de uma coluna.
=PROCX(F3;B3:B7;C3:D7)
Observação: A matriz de retorno (colunas) deve ser contígua.
PROCX: Tratamento do Erro #N/D
Ao contrário da função PROCV, onde precisamos usar as funções SEERRO ou SENÃODISP para tratar o erro #N/D, a função PROCX tem um manipulador de erro #N/D integrado, que é o 4º argumento (se_não_encontrada).
=PROCX(E3;B3:B7;C3:C7;"Não encontrado!")
PROCX: Modo de correspondência
Outro recurso excelente da função PROCX é a possibilidade de selecionar o modo_correspondência (5º argumento): 0 – Correspondência exata, -1 – Correspondência exata ou próximo item com valor menor, 1 – Correspondência exata ou próximo item com valor maior e 2 – Correspondência de caractere curinga.
=PROCX(E3;B3:B7;C3:C7;"Sem desconto!";-1)
Observação: Ao contrário das correspondências aproximadas das funções PROCV, PROCH e CORRESP, em que precisamos classificar os dados, o modo_correspondência da função PROCX, por padrão, não exige a classificação dos dados (veja o exemplo a seguir).
PROCX: Modo de pesquisa
O exemplo com o argumento modo_correspondência da seção anterior não exigiu um conjunto de dados classificado porque o valor padrão do modo_pesquisa (6º argumento) é uma pesquisa linear (do primeiro ao último). Agora, podemos selecionar o processo de pesquisa da nossa busca (ou seja, pesquisa linear e pesquisa binária), e uma delas é a opção “Pesquisa do último-ao-primeiro” (ou seja, -1), que nos permite retornar facilmente esta correspondência.
=PROCX(E3;B3:B7;C3:C7;;;-1)
PROCX: Correspondência Binária Exata e Matrizes Dinâmicas
A atualização mais importante da função PROCV ou mesmo da fórmula ÍNDICE-CORRESP é a possibilidade de realizar uma correspondência exata com um processo de pesquisa binária. Agora podemos utilizar a velocidade da pesquisa binária sem sacrificar a correspondência exata, bastando definir o último argumento, modo_pesquisa, como 2 (ordem ascendente) ou -2 (ordem descendente).
Vejamos um cenário de pesquisa de um milhão de dados:
=PROCX(C3:C1000002;B3:B1000002;B3:B1000002;;;2)
Observação: Por padrão, o modo_pesquisa é 1, que é a procura a partir do primeiro valor.
Com a pesquisa binária, podemos realizar essa pesquisa em menos de 5 segundos. Outro aspecto é que podemos converter a função PROCX em uma fórmula de matriz dinâmica, inserindo uma entrada de matriz no valor da pesquisa (por exemplo, C3:C1000002).
2. ÍNDICE-CORRESP
As fórmulas ÍNDICE-CORRESP são a alternativa mais conhecida para a função PROCV nas versões anteriores do Excel.
ÍNDICE-CORRESP: Pesquisa à esquerda
Assim como na função PROCX, a coluna de pesquisa e a coluna de retorno são separadas nas fórmulas ÍNDICE-CORRESP. A coluna de busca é inserida na função CORRESP, que também executa o processo de pesquisa, enquanto a coluna de retorno é inserida na função ÍNDICE, que retorna o valor que corresponde ao resultado da função CORRESP. Esse arranjo oferece os mesmos benefícios mencionados na função PROCX: ajuste automático para inserções e exclusões de colunas, fórmula mais rápida e eficiente devido à menor quantidade de entradas de matriz de colunas, e capacidade de realizar pesquisas à esquerda (veja o exemplo abaixo).
=ÍNDICE(C3:C7;CORRESP(F3;D3:D7;0))
Vamos examinar a fórmula:
Função CORRESP
Vamos começar encontrando a coordenada relativa da linha do valor de pesquisa (por exemplo, ID do estudante) da matriz de pesquisa (por exemplo, D3:D7) usando a função CORRESP.
=CORRESP(F3;D3:D7;0)
Observação: O tipo_correspondência (3º argumento) da função CORRESP define o modo de correspondência. Zero significa correspondência exata, enquanto 1 e -1 são correspondências aproximadas.
Função ÍNDICE
Quando tivermos a coordenada da linha, poderemos usá-la para retornar um valor da matriz de retorno correspondente (por exemplo, C3:C7) usando a função ÍNDICE.
=ÍNDICE(C3:C7;H3)
Observação: A função ÍNDICE retorna um valor de uma matriz com as coordenadas relativas de linha e coluna (para matriz 2D).
A combinação das duas fórmulas resulta em nossa fórmula original:
=ÍNDICE(C3:C7;CORRESP(F3;D3:D7;0))ÍNDICE-CORRESP: Matriz de Pesquisa Horizontal
Outro exemplo que mostra a flexibilidade das fórmulas ÍNDICE-CORRESP é quando a matriz de pesquisa e a matriz de retorno estão em orientações opostas:
=INDEX(B3:B7,MATCH(D7,E2:I2,0))
3. Função PROCH
Agora que estamos falando de orientações horizontais, há também uma função de pesquisa criada para pesquisas horizontais: a função PROCH.
=PROCH(I3;C2:G3;2;FALSO)
Observação: A função PROCH funciona da mesma forma que a função PROCV, mas na orientação oposta. Ela procura a correspondência exata na primeira linha e retorna o valor correspondente a partir de um determinado núm_índice_lin.
4. DESLOC-CORRESP: Referência Dinâmica de Coluna
Outra alternativa ao PROCV são as fórmulas DESLOC-CORRESP, que funciona de forma semelhante à fórmula ÍNDICE-CORRESP.
Vamos examinar um cenário de pesquisa com referência de coluna dinâmica e ver como o DESLOC difere ligeiramente do ÍNDICE-CORRESP.
=DESLOC(B2;CORRESP(F3;B3:B7;0);CORRESP(G3;C2:D2;0))
Vamos examinar a fórmula:
Coordenadas de Linha e Coluna
Primeiro, precisamos determinar as coordenadas relativas de linha e coluna usando a função CORRESP.
Função DESLOC
Em vez de usar a função ÍNDICE para retornar um valor, nós a substituiremos pela função DESLOC. Por padrão, a função DESLOC retorna um intervalo definindo as coordenadas relativas de linha e coluna de um intervalo de referência (por exemplo, B2).
=DESLOC(B2;I3;J3)
Observação: A função DESLOC é uma função volátil, o que significa que ela sempre será recalculada quando a planilha for recalculada. Dependendo do cenário, e isso pode afetar a velocidade de sua planilha.
A combinação de todas as funções produz nossa fórmula original:
=DESLOC(B2;CORRESP(F3;B3:B7;0);CORRESP(G3;C2:D2;0))5. INDIRETO-ENDEREÇO-CORRESP: Referência de Coluna Dinâmica
Outra alternativa flexível ao PROCV são as fórmulas INDIRETO-ENDEREÇO-CORRESP. Vamos aplicar ao cenário de referência de coluna dinâmica:
=INDIRETO(ENDEREÇO(CORRESP(F3;B1:B7;0);CORRESP(G3;A2:D2;0)))
Vamos examinar a fórmula:
Coordenada da Linha
Nessa fórmula devemos determinar a coordenada de linha da própria célula, diferentemente das fórmulas ÍNDICE e DESLOC, em que usamos coordenadas relativas.
=CORRESP(F3;B1:B7;0)
Coordenada da Coluna
Em seguida, também precisamos determinar a coordenada da coluna da célula.
=CORRESP(G3;A2:D2;0)
Função ENDEREÇO
Em seguida, usamos as coordenadas de linha e coluna para retornar uma referência de célula em formato de texto usando a função ENDEREÇO. Por padrão, a referência de célula de texto estará no formato absoluto (por exemplo, $C$4).
=ENDEREÇO(I3;J3)
Função INDIRETO
Por último, convertemos a referência de célula de texto em uma referência de célula real usando a função INDIRETO.
=INDIRETO(K3)
Observação: A função INDIRETO é uma função volátil, o que significa que ela sempre será recalculada quando a planilha for recalculada. Dependendo do cenário, e isso pode afetar a velocidade de sua planilha.
A combinação de todas as funções resulta em nossa fórmula original:
=INDIRETO(ENDEREÇO(CORRESP(F3;B1:B7;0);CORRESP(G3;A2:D2;0)))6. Função PROC: Última Correspondência
Se houver entradas duplicadas na matriz de pesquisa, é difícil para a função PROCV obter a última correspondência. A função PROCX é a melhor solução, mas se essa não for uma opção, a função PROC é a melhor alternativa.
=PROC(2;1/(B3:B7=E3);C3:C7)
Vamos examinar a fórmula:
Condição de Pesquisa
Primeiro, vamos verificar os valores da coluna de pesquisa (por exemplo, B3:B7) em relação ao valor de pesquisa (por exemplo, E3).
=B3=$E$3
Recíproco de Valores Booleanos
Em seguida, tomamos o recíproco dos valores booleanos, em que TRUE é 1 e FALSE é 0.
=1/G3
A matriz recíproca é usada como a matriz de pesquisa para a função PROC, que ignora os erros. Portanto, tecnicamente, temos uma matriz de 1s.
Função PROC
A função PROC só pode fazer uma correspondência aproximada com a suposição de que os dados estão classificados em ordem crescente, o que significa que a função PROC encontrará o maior valor da matriz de pesquisa que seja menor ou igual ao valor de pesquisa.
Usamos 2 como valor de pesquisa para aproveitar a correspondência aproximada na matriz de 1s. Se houver duplicatas, como no exemplo, a posição da última instância será retornada.
=PROC(2;H3:H7;C3:C7)
A combinação de todas as fórmulas resulta em nossa fórmula original:
=PROC(2;1/(B3:B7=E3);C3:C7)7. Função FILTRO: Pesquisar Todas as Duplicatas
Se quisermos procurar todas as duplicatas, as versões mais recentes do Excel oferecem uma alternativa melhor: a função FILTRO. Ela é simples e não requer muitas etapas, como adicionar colunas auxiliares.
=FILTRO(C3:C7;B3:B7=E2)
Observação: O primeiro argumento é a matriz (por exemplo, C3:C7) que queremos filtrar e o segundo argumento é o critério de filtragem (por exemplo, B3:B7=E2).
8. FILTRO-ÍNDICE: Procurar a Enésima Correspondência
Em vez de retornar todas as duplicatas, podemos selecionar a enésima correspondência usando as fórmulas FILTRO-ÍNDICE. Essa é uma alternativa mais conveniente ao método de ID exclusivo do PROCV (consulte o artigo Valores duplicados de PROCV).
=ÍNDICE(FILTRO(C3:C7;B3:B7=E3);F3)
Vamos examinar a fórmula:
Função FILTRO
Primeiro, vamos retornar todas as duplicatas usando a função FILTRO. Usamos a matriz de pesquisa = valor de pesquisa como condição de filtro.
=FILTER(C3:C7,B3:B7=E3)
Função ÍNDICE e Enésima Correspondência
Em seguida, retornamos a enésima correspondência do resultado da função FILTRO usando a função ÍNDICE.
=ÍNDICE(H3:H5;F3)
A combinação de todas as funções resulta em nossa fórmula original:
=ÍNDICE(FILTRO(C3:C7;B3:B7=E3);F3)9. SOMARPRODUTO: Pesquisa de Números
Se estivermos pesquisando apenas números, podemos usar a função SOMARPRODUTO como alternativa à função PROCV. Uma vantagem da função SOMARPRODUTO é a conveniência de aplicar vários critérios. Para usuários do Excel sem acesso às novas fórmulas de matriz, não será necessário usar Ctrl+Shift+Enter para processar as matrizes. Vejamos o exemplo abaixo:
=SOMARPRODUTO((B3:B7=F3)*(C3:C7=G3)*D3:D7)
Vamos examinar a fórmula:
Condição 1
Primeiro, vamos aplicar as condições apropriadas às colunas correspondentes. Aqui está a primeira condição:
=B3=$E$3
Condição 2
Aqui está a segunda condição:
=C3=$G$3
Matriz E
Em seguida, verificamos se ambas as condições são satisfeitas multiplicando as duas matrizes booleanas (VERDADEIRO = 1 e FALSO = 0).
=I3*J3
Observação: A multiplicação de matrizes booleanas é equivalente à função E. Se ambas as condições forem satisfeitas, o resultado será 1. Se uma das condições for FALSA, o produto será 0.
Matriz de Retorno
Se não houver duplicatas, a lista conterá um valor de 1 e os demais serão 0s. Convertemos essa matriz na matriz de retorno multiplicando a matriz de retorno a ela.
=K3*D3
Função SOMARPRODUTO
A função SOMARPRODUTO executa uma multiplicação de matriz e obtém a soma da matriz do produto. Como há apenas um valor maior que 0 e os demais são 0, a soma retornará o valor que estamos procurando.
=SOMARPRODUTO(L3:L7)
Observação: Multiplicamos as matrizes antes da função SOMARPRODUTO para converter as matrizes booleanas em números. A função SOMARPRODUTO só executa cálculos com números e exclui outros tipos de dados (por exemplo booleanos ou cadeias de caracteres).
A combinação de todas as fórmulas resulta em nossa fórmula original:
=SOMARPRODUTO((B3:B7=F3)*(C3:C7=G3)*D3:D7)10. Função SOMASES: Pesquisa de Números
Em vez da função SOMARPRODUTO, também podemos usar a função SOMASES para realizar pesquisas de vários critérios em números. Ela é mais simples e conveniente de usar em comparação com a função SOMARPRODUTO, mas, diferentemente da função SOMARPRODUTO, não aceita entrada de matriz (como os resultados de matriz de outras funções, por exemplo). Os intervalos intervalo_soma e intervalo_critérios são estritamente intervalos.
=SOMASES(D3:D7;B3:B7;F3;C3:C7;G3)
Observação: O primeiro argumento é o intervalo_soma, que é a matriz que será somada. Os argumentos seguintes são pares de intervalo_critérios e critério onde é feita a verificação da condição para que o valor seja somado ou não.
11. Planilhas Google: Função QUERY
Com exceção da função PROCX, que não existe no Planilhas Google, todas as funções mencionadas anteriormente estão disponíveis e funcionam da mesma forma no Google Planilhas.
Há uma alternativa mais poderosa que podemos usar no Planilhas Google: a função QUERY.
=QUERY(B3:C7;"SELECT C WHERE B='"&E3&"'")
Observação: O segundo argumento da função QUERY é a sintaxe da consulta (formato de texto) que pode nos ajudar a realizar manipulações de dados, como pesquisas, classificação, filtragem e formatação.
Vamos examinar a fórmula:
SELECT
A cláusula SELECT filtra a coluna que queremos retornar (por exemplo, Coluna C).
=QUERY(B3:C7;"SELECT C")
WHERE
A cláusula WHERE filtra a linha. Como o valor de pesquisa é um texto, precisamos colocá-lo entre aspas simples.
Observação: Se removermos a cláusula SELECT, todas as colunas serão retornadas.
A combinação das duas cláusulas resulta em nossa fórmula original:
=QUERY(B3:C7;"SELECT C WHERE B='"&E3&"'")
