Este tutorial explica como usar a função CORRESP no Excel com exemplos de fórmulas, e como deixa-las mais dinâmicas trabalhando em paralelo com as funções É.NÃO.DISP, EXATO, SE, PROCV e PROCH.
No Microsoft Excel, existem muitas funções diferentes de pesquisa ou referência que podem nos ajudar a encontrar determinado valor em um intervalo de células. O CORRESP é uma dessas funções que, basicamente identifica uma posição relativa de um item em um intervalo de células.
Função CORRESP do Excel – sintaxe e uso
A função CORRESP no Excel procura um valor especificado em um intervalo de células e retorna a posição relativa desse valor.
A sintaxe para a função CORRESP é a seguinte:
= CORRESP(valor_procurado;matriz_procurada;[tipo_correspondência])
Sendo:
- valor_procurado (obrigatório) – É o valor que se deseja encontrar. Pode ser um valor numérico, de texto ou lógico, bem como uma referência de célula.
- matriz_procurada (obrigatório) – É o intervalo de células para pesquisa.
- tipo_correspondência (opcional) – Define o tipo de correspondência, e pode ser um destes valores: 1, 0, -1.
O argumento tipo_correspondência definido como 0 (zero) retorna apenas a correspondência exata, enquanto os outros dois tipos permitem uma correspondência aproximada.
- 1 (ou omitido) – Encontra o maior valor na matriz que seja menor ou igual ao valor de pesquisa. Esta correspondência requer a classificação da matriz de pesquisa em ordem crescente, do menor para o maior ou de A a Z.
- 0 – Encontra o primeiro valor na matriz exatamente igual ao valor da pesquisa. Nenhuma classificação é necessária.
- -1 – Encontra o menor valor na matriz que seja maior ou igual ao valor da pesquisa. A matriz de pesquisa deve ser classificada em ordem decrescente, do maior para o menor ou de Z para A.
Para entender melhor a função CORRESP, vamos criar uma fórmula simples com base nesses dados: Nomes dos alunos na coluna A e suas pontuações nos exames na coluna B, classificados do maior para o menor. Para descobrir onde se encontra um aluno específico na tabela (por exemplo, Laura), deve ser usado a seguinte fórmula:
= CORRESP("Laura";A2:A8;0)Opcionalmente, podemos colocar o valor da pesquisa em alguma célula (E1 neste exemplo) e referenciar essa célula no primeiro argumento da função:
= CORRESP(E1;A2:A8;0)
Como é possível ver na imagem acima, os nomes dos alunos são inseridos em uma ordem arbitrária e, portanto, definimos o terceiro argumento da função como 0 (correspondência exata), porque apenas esse tipo de correspondência não requer classificação de valores na matriz de pesquisa. Tecnicamente, a fórmula CORRESP retorna a posição relativa de Laura no intervalo que está sendo pesquisado. Porém, como as pontuações são classificadas do maior para o menor, também nos diz que Laura tem a 5ª melhor pontuação entre todos os alunos.
4 coisas que você deve saber sobre a função CORRESP no Excel
Como acabamos de ver, não é difícil usar a função CORRESP no Excel. No entanto, assim como quase todas as funções do Excel, a função CORRESP apresenta algumas especialidades que devo apresentar:
1 – A função CORRESP retorna a posição relativa do valor de pesquisa na matriz, não o valor em si.
2 – A função CORRESP não diferencia letras maiúsculas de minúsculas, o que significa que não faz distinção entre caracteres minúsculos e maiúsculos ao lidar com valores de texto.
3 – Se a matriz de pesquisa contiver várias ocorrências do valor da pesquisa, a posição do primeiro valor será retornada.
4 – Se o valor da pesquisa não for encontrado na matriz de pesquisa, o erro # N/A será retornado como resposta.
Como usar a função CORRESP no Excel – exemplo de fórmulas
Agora que já conhecemos a finalidade básica da função CORRESP, vamos discutir mais alguns exemplos de fórmulas que vão além do básico.
Fórmula CORRESP com caracteres curinga
Como muitas outras funções do Excel, o CORRESP entende os seguintes caracteres curinga:
- Ponto de interrogação (?) – Substitui qualquer caractere único
- Asterisco (*) – Substitui qualquer sequência de caracteres
Dica. Os curingas só podem ser usados em fórmulas CORRESP com o tipo de correspondência definido como 0.
Uma fórmula CORRESP com caracteres curinga é útil em situações em que se deseja corresponder não a sequência de texto inteira, mas apenas alguns caracteres ou parte da sequência. Para ilustrar o ponto, considere o seguinte exemplo:
Supondo que tenhamos uma lista de revendedores regionais e seus números de vendas no mês passado. Desejamos encontrar uma posição relativa de um determinado revendedor na lista (classificada pelos valores de Vendas em ordem decrescente). Porém, não conseguimos nos lembrar exatamente do nome dele, embora a gente se lembre dos primeiros caracteres.
Supondo que os nomes dos revendedores estejam no intervalo A2: A11 e a gente esteja procurando o nome que começa com “Car”. A fórmula é a seguinte:
= CORRESP("Car *";A2:A11;0)Para tornar a fórmula de correspondência mais versátil, podemos digitar o valor da pesquisa em alguma célula (E1 neste exemplo) e concatenar essa célula com o caractere curinga, dessa forma:
= CORRESP(E1 & "*"; A2:A11;0)
Conforme mostrado na figura abaixo, a fórmula retorna 2, que é a posição de “Carlos”:
Para substituir apenas um caractere no valor da pesquisa, use o operador curinga “?”:
= CORRESP("Ca?la";A2:A11;0)A fórmula acima corresponderá ao nome “Camila” e executará novamente sua posição relativa, que é 6.
Fórmula CORRESP que diferencia maiúsculas de minúsculas
Conforme mencionado no início deste tutorial, a função CORRESP do Excel não distingue caracteres maiúsculos e minúsculos. Para criar uma fórmula de correspondência sensível a letras maiúsculas e minúsculas, use o CORRESP em combinação com a função EXATO que compara células iguais, incluindo caracteres.
A fórmula genérica que diferencia maiúsculas de minúsculas no Excel é esta apresentada abaixo:
= CORRESP(VERDADEIRO;EXATO(matriz de pesquisa;valor da pesquisa);0)
A fórmula funciona com a seguinte lógica:
- A função EXATO compara o valor da pesquisa com cada elemento da matriz de pesquisa. Se as células comparadas forem exatamente iguais, a função retornará VERDADEIRO. Caso contrário retorna FALSO.
- Em seguida, a função CORRESP compara o primeiro argumento “VERDADEIRO” com cada valor na matriz indicado pela função EXATO e retorna a posição da primeira correspondência.
Lembre-se de que é uma fórmula de matriz que requer pressionar as teclas Ctrl + Shift + Enter para ser concluída corretamente.
Supondo que seu valor de pesquisa esteja na célula E1 e a matriz de pesquisa seja A2: A9, a fórmula é a seguinte:
= CORRESP(VERDADEIRO;EXATO(A2:A9;E1);0)
A imagem abaixo mostra a fórmula CORRESP que diferencia maiúsculas de minúsculas no Excel:
Compare 2 colunas diferentes (É.NÃO.DISP / CORRESP)
A verificação de duas listas de correspondências diferentes é uma das tarefas mais comuns no Excel e pode ser feita de várias maneiras. Usar as fórmulas É.NÃO.DISP / CORRESP é uma delas:
= SE(É.NÃO.DISP(CORRESP(1º valor na Lista1; Lista2; 0));"Não está na Lista 1";"")
Para qualquer valor da lista 2 que não esteja presente na lista 1, a fórmula retornará a mensagem “Não está na Lista 1”. Entenda como:
- A função CORRESP procura um valor da Lista 1 na Lista 2. Se um valor for encontrado, ele retornará sua posição relativa, caso contrário, retorna o erro # N/A como resposta.
- A função É.NÃO.DISP no Excel verifica se há erros de N/D (que significa “não disponível”). Se um determinado valor for um erro # N / A, a função retornará VERDADEIRO. Caso contrário retornará FALSO. No caso, VERDADEIRO significa que um valor da Lista 1 não foi encontrado na Lista 2 (o erro i # N/A é retornado pela função CORRESP).
- Como pode ser muito confuso usar VERDADEIRO para valores que não aparecem na Lista 1, você utiliza a função SE para exibir a mensagem “Não está na Lista 1” ou o texto que desejar.
Supondo que seja necessário comparar valores da coluna B com valores da coluna A, a fórmula assume a seguinte forma (onde B2 é a célula de referência):
= SE(É.NÃO.DISP(CORRESP(B2;A:A;0));"Não está na Lista 1";"")
Como vimos, a função CORRESP no Excel não faz distinção entre maiúsculas e minúsculas. Para distinguir o caso dos caracteres, incorpore a função EXATO no segundo argumento e lembre-se de pressionar as teclas Ctrl + Shift + Enter para concluir esta fórmula de matriz:
= SE(É.NÃO.DISP(CORRESP(VERDADEIRO;EXATO(A:A;B2);0));"Não está na Lista 1";"")
Na figura a seguir temos as duas fórmulas em ação:
Funções PROCV e CORRESP no Excel
Este exemplo supõe que o usuário já tenha algum conhecimento básico da Função PROCV.
Uma das maiores desvantagens da função PROCV no Excel é que, ele simplesmente para de funcionar após inserir ou excluir uma coluna em uma tabela de pesquisa. Isso acontece porque o PROCV obtém um valor correspondente com base no número da coluna de retorno (número do índice). Como o número do índice é “codificado” na fórmula, o Excel não pode ajustá-lo quando uma nova coluna é adicionada ou excluída da tabela.
Entretanto, a função CORRESP do Excel lida com uma posição relativa de um valor de pesquisa, o que o torna um ajuste perfeito para o argumento núm_índice_coluna do PROCV. Em outras palavras, em vez de especificar a coluna de retorno como um número específico, usamos o CORRESP para obter a posição atual dessa coluna.
Para facilitar a compreensão, vamos usar a tabela com as pontuações dos exames dos alunos novamente (semelhante à que usamos no início do tutorial), mas desta vez vamos buscar a pontuação real e não sua posição relativa.
Supondo que o valor da pesquisa esteja na célula F1, a matriz da tabela é $A$ 1: $C$2 (se planeja copiar a fórmula para outras células, é importante bloquear o intervalo usando Referências Absolutas de Células). Temos assim, a seguinte fórmula:
= PROCV(F1;$A$1:$C$8;3;FALSO)
O terceiro argumento (núm_índice_coluna) está definido como 3, porque a pontuação matemática que queremos extrair está na terceira coluna da tabela. Note na figura abaixo, como fórmula PROCV funciona bem:
Mas esta fórmula funciona bem, somente até que se insira ou exclua uma (s) coluna (s):
Neste caso, o Excel retorna o erro # REF! porque, como o argumento núm_índice_coluna é definido pelo usuário como 3, o Excel entende que ele deve buscar o valor correspondente a terceira coluna. Como agora existem apenas 2 colunas na matriz da tabela, o Excel (por meio da função PROCV) não é capaz de buscar o resultado esperado.
Felizmente, para impedir que essas coisas aconteçam, podemos tornar a fórmula acima mais dinâmica incluindo a função CORRESP:
= CORRESP(E2;A1:C1;0)
Onde:
- E2 é o valor da pesquisa, exatamente igual ao nome da coluna de retorno, ou seja, a coluna da qual se deseja extrair um valor (Pontuação Matemática neste exemplo).
- A1:C1 é a matriz de pesquisa que contém os cabeçalhos da tabela.
Agora, inclua a função CORRESP no argumento núm_índice_coluna da fórmula do PROCV, dessa maneira:
= PROCV(F1;$A$1:$C$8;CORRESP(E2;$A$1:$C$1;0);FALSO)
Note que a fórmula acima funciona bem, independente de quantas colunas se deseja adicionar ou excluir:
Logo, todas as referências de células foram bloqueadas para que a fórmula funcione corretamente, mesmo que qualquer pessoa as movam para outro local da planilha. Observe a diferença na figura abaixo após a exclusão de uma coluna:
Funções PROCH e CORRESP no Excel
Semelhante ao exemplo anterior, podemos usar a função CORRESP do Excel para melhorar as fórmulas PROCH.
Contudo, o princípio é o mesmo que no caso do PROCV: usamos a função CORRESP para obter a posição relativa da coluna de retorno e, em seguida fornece esse número ao argumento nú,_índice_lin da fórmula PROCH.
Supondo que, o valor da pesquisa esteja na célula B5, a matriz da tabela seja B1: H3, o nome da linha de retorno (valor da pesquisa para CORRESP) esteja na célula A6 e os cabeçalhos da linha sejam A1: A3, a fórmula completa é a seguinte:
= PROCH(B5;B1:H3;CORRESP(A6;A1:A3;0);FALSO)
Como acabamos de ver, a combinação de PROCH / PROCV & CORRESP é certamente uma melhoria em relação às fórmulas regulares de PROCH e PROCV. No entanto, a função CORRESP não elimina todas as suas limitações. Em particular, uma fórmula do PROCV CORRESP ainda não pode obter uma resposta com dados de referência a esquerda do valor procurado, e o PROCH CORRESP falha ao pesquisar em qualquer linha que não seja a mais alta.
Para superar as limitações acima (e algumas outras), considere usar uma combinação de ÍNDICE CORRESP, que fornece uma maneira poderosa de pesquisa no Excel superior ao PROCV e PROCH em muitos aspectos. Alguns exemplos mais detalhados podem ser encontrados em ÍNDICE & CORRESP no Excel – uma melhor alternativa ao PROCV.
Definitivamente, é assim que se utiliza fórmulas de correspondência no Excel. Logo, acredito que felizmente os exemplos discutidos neste tutorial poderão ser úteis em seu trabalho.
Agradeço a leitura e espero vê-lo aqui no blog na próxima semana.
Conclusão
Neste artigo vimos como trabalhar com a função CORRESP no Excel e, como ela pode ser útil em aplicação feitas por outras funções do Excel, com o objetivo de tornar fórmulas mais dinâmicas e práticas. Vimas aqui as opções mais usadas, mas existem outras maneiras de atingir o mesmo objetivo que você pode ler em Dicas Excel, mas este artigo se encaixa bem na categoria Excel Intermediário.
Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado.
O site deste pessoal aqui foi referência para este artigo. O site é muitoooo bacana com várias dicas legais para Excel, mas infelizmente o conteúdo está em inglês, o que não deixa de ser uma boa referência para quem conseguir acompanhar. Eu recomendo.
Portanto, continue lendo mais sobre Excel avançado aqui.
