Encontrar valores ausentes
Há casos em que você precisa comparar duas listas para verificar se um valor da lista A existe na lista B no Excel. Por exemplo, você tem uma lista de produtos e deseja verificar se os produtos da sua lista estão presentes na lista de produtos fornecida pelo seu fornecedor. Para realizar essa tarefa, listamos abaixo três maneiras; fique à vontade para escolher a que preferir.
Encontrar valores ausentes com CORRESP, É.NÃO.DISP e SE
Encontrar valores ausentes com PROCV, É.NÃO.DISP e SE
Encontrar valores ausentes com CONT.SE e SE
Encontrar valores ausentes com CORRESP, É.NÃO.DISP e SE
Para descobrir se todos os produtos da sua lista estão presentes na lista do seu fornecedor, conforme mostrado na captura de tela acima, você pode primeiro usar a função CORRESP para recuperar a posição de um produto da sua lista (valor da lista A) na lista do fornecedor (lista B). A função CORRESP retornará o erro #N/D quando um produto não for encontrado. Em seguida, você pode passar o resultado para a função É.NÃO.DISP para converter os erros #N/D em VERDADEIROS, indicando que esses produtos estão ausentes. A função SE então retornará o resultado esperado.
Sintaxe genérica
=SE(É.NÃO.DISP(CORRESP("valor_procura",intervalo_procura,0)),"Ausente","Encontrado")
√ Nota: Você pode alterar "Ausente", "Encontrado" para quaisquer valores que precisar.
- valor_procura: O valor que a função CORRESP usará para recuperar sua posição, se existir no intervalo_procura, ou o erro #N/D caso contrário. Aqui refere-se aos produtos da sua lista.
- intervalo_procura: O intervalo de células a ser comparado com o valor_procura. Aqui refere-se à lista de produtos do fornecedor.
Para descobrir se todos os produtos da sua lista estão presentes na lista do seu fornecedor, copie ou insira a fórmula abaixo na célula H6 e pressione Enter para obter o resultado:
=SE(É.NÃO.DISP(CORRESP(30002,$B$6:$B$10,0)),"Ausente","Encontrado")
Ou, use uma referência de célula para tornar a fórmula dinâmica:
=SE(É.NÃO.DISP(CORRESP(G6,$B$6:$B$10,0)),"Ausente","Encontrado")
√ Nota: Os sinais de cifrão ($) acima indicam referências absolutas, o que significa que o intervalo_procura na fórmula não mudará quando você mover ou copiar a fórmula para outras células. No entanto, nenhum sinal de cifrão foi adicionado ao valor_procura, pois você deseja que ele seja dinâmico. Após inserir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células abaixo.
Explicação da fórmula
Aqui usamos a fórmula abaixo como exemplo:
=SE(É.NÃO.DISP(CORRESP(G8,$B$6:$B$10,0)),"Ausente","Encontrado")
- CORRESP(G8,$B$6:$B$10,0): O tipo_correspondência 0 força a função CORRESP a retornar um valor numérico que indica a posição da primeira correspondência de 3004, o valor na célula G8, no intervalo $B$6:$B$10. No entanto, neste caso, a função CORRESP não encontrou o valor no intervalo de busca, então ela retornará o erro #N/D.
- É.NÃO.DISP(CORRESP(G8,$B$6:$B$10,0)) = É.NÃO.DISP(#N/D): A função É.NÃO.DISP verifica se um valor é o erro “#N/D” ou não. Se sim, a função retornará VERDADEIRO; se o valor for qualquer coisa diferente do erro “#N/D”, ela retornará FALSO. Assim, esta fórmula É.NÃO.DISP retornará VERDADEIRO.
- SE(É.NÃO.DISP(CORRESP(G8,$B$6:$B$10,0)),"Ausente","Encontrado") = SE(VERDADEIRO,"Ausente","Encontrado"): A função SE retornará Ausente se a comparação feita por É.NÃO.DISP e CORRESP for VERDADEIRA, caso contrário, retornará Encontrado. Assim, a fórmula retornará Ausente.
Encontrar valores ausentes com PROCV, É.NÃO.DISP e SE
Para descobrir se todos os produtos da sua lista estão presentes na lista do seu fornecedor, você pode substituir a função CORRESP acima pela função PROCV, já que ela funciona da mesma forma que a função CORRESP, ou seja, retornará o erro #N/D se o valor não existir em outra lista, ou podemos dizer que está ausente.
Sintaxe genérica
=SE(É.NÃO.DISP(PROCV("valor_procura",intervalo_procura,1,FALSO)),"Ausente","Encontrado")
√ Nota: Você pode alterar "Ausente", "Encontrado" para quaisquer valores que precisar.
- valor_procura: O valor que a função PROCV usará para recuperar sua posição, se existir no intervalo_procura, ou o erro #N/D caso contrário. Aqui refere-se aos produtos da sua lista.
- intervalo_procura: O intervalo de células a ser comparado com o valor_procura. Aqui refere-se à lista de produtos do fornecedor.
Para descobrir se todos os produtos da sua lista estão presentes na lista do seu fornecedor, copie ou insira a fórmula abaixo na célula H6 e pressione Enter para obter o resultado:
=SE(É.NÃO.DISP(PROCV(30002,$B$6:$B$10,1,FALSO)),"Ausente","Encontrado")
Ou, use uma referência de célula para tornar a fórmula dinâmica:
=SE(É.NÃO.DISP(PROCV(G6,$B$6:$B$10,1,FALSO)),"Ausente","Encontrado")
√ Nota: Os sinais de cifrão ($) acima indicam referências absolutas, o que significa que o intervalo_procura na fórmula não mudará quando você mover ou copiar a fórmula para outras células. No entanto, nenhum sinal de cifrão foi adicionado ao valor_procura, pois você deseja que ele seja dinâmico. Após inserir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células abaixo.
Explicação da fórmula
Aqui usamos a fórmula abaixo como exemplo:
=SE(É.NÃO.DISP(PROCV(G8,$B$6:$B$10,1,FALSO)),"Ausente","Encontrado")
- PROCV(G8,$B$6:$B$10,1,FALSO): O argumento procura_exata FALSO força a função PROCV a procurar e retornar o valor que corresponde exatamente a 3004, o valor na célula G8. Se o valor_procura 3004 existir na primeira coluna do intervalo $B$6:$B$10, a função PROCV retornará esse valor; caso contrário, retornará o valor de erro #N/D. Aqui, 3004 não existe no intervalo, então o resultado seria #N/D.
- É.NÃO.DISP(PROCV(G8,$B$6:$B$10,1,FALSO)) = É.NÃO.DISP(#N/D): A função É.NÃO.DISP verifica se um valor é o erro “#N/D” ou não. Se sim, a função retornará VERDADEIRO; se o valor for qualquer coisa diferente do erro “#N/D”, ela retornará FALSO. Assim, esta fórmula É.NÃO.DISP retornará VERDADEIRO.
- SE(É.NÃO.DISP(PROCV(G8,$B$6:$B$10,1,FALSO)),"Ausente","Encontrado") = SE(VERDADEIRO,"Ausente","Encontrado"): A função SE retornará Ausente se a comparação feita por É.NÃO.DISP e PROCV for VERDADEIRA, caso contrário, retornará Encontrado. Assim, a fórmula retornará Ausente.
Encontrar valores ausentes com CONT.SE e SE
Para descobrir se todos os produtos da sua lista estão presentes na lista do seu fornecedor, você pode usar uma fórmula mais simples com as funções CONT.SE e SE. A fórmula aproveita o fato de que o Excel avaliará qualquer número diferente de zero (0) como VERDADEIRO. Então, se um valor existir em outra lista, a função CONT.SE retornará a contagem de suas ocorrências nessa lista, e a função SE interpretará o número como VERDADEIRO; se o valor não existir na lista, a função CONT.SE retornará 0, e a função SE interpretará isso como FALSO.
Sintaxe genérica
=SE(CONT.SE("intervalo_procura",valor_procura),"Encontrado","Ausente")
√ Nota: Você pode alterar "Encontrado", "Ausente" para quaisquer valores que precisar.
- intervalo_procura: O intervalo de células a ser comparado com o valor_procura. Aqui refere-se à lista de produtos do fornecedor.
- valor_procura: O valor que a função CONT.SE usará para retornar o número de suas ocorrências no intervalo_procura. Aqui refere-se aos produtos da sua lista.
Para descobrir se todos os produtos da sua lista estão presentes na lista do seu fornecedor, copie ou insira a fórmula abaixo na célula H6 e pressione Enter para obter o resultado:
=SE(CONT.SE($B$6:$B$10,30002),"Encontrado","Ausente")
Ou, use uma referência de célula para tornar a fórmula dinâmica:
=SE(CONT.SE($B$6:$B$10,G6),"Encontrado","Ausente")
√ Nota: Os sinais de cifrão ($) acima indicam referências absolutas, o que significa que o intervalo_procura na fórmula não mudará quando você mover ou copiar a fórmula para outras células. No entanto, nenhum sinal de cifrão foi adicionado ao valor_procura, pois você deseja que ele seja dinâmico. Após inserir a fórmula, arraste a alça de preenchimento para baixo para aplicar a fórmula às células abaixo.
Explicação da fórmula
Aqui usamos a fórmula abaixo como exemplo:
=SE(CONT.SE($B$6:$B$10,G8),"Encontrado","Ausente")
- CONT.SE($B$6:$B$10,G8): A função CONT.SE conta quantas vezes 3004, o valor na célula G8, aparece no intervalo $B$6:$B$10. Obviamente, 3004 não existe no intervalo, então o resultado seria 0.
- SE(CONT.SE($B$6:$B$10,G8),"Encontrado","Ausente") = SE(0,"Encontrado","Ausente"): A função SE avaliará 0 como FALSO. Assim, a fórmula retornará Ausente, o valor a ser retornado quando o primeiro argumento for avaliado como FALSO.
Funções relacionadas
A função SE é uma das funções mais simples e úteis em uma planilha do Excel. Ela realiza um teste lógico simples dependendo do resultado da comparação e retorna um valor se o resultado for VERDADEIRO ou outro valor se o resultado for FALSO.
A função CORRESP do Excel pesquisa um valor específico em um intervalo de células e retorna a posição relativa desse valor.
A função PROCV do Excel pesquisa um valor correspondendo na primeira coluna de uma tabela e retorna o valor correspondente de uma determinada coluna na mesma linha.
A função CONT.SE é uma função estatística no Excel que é usada para contar o número de células que atendem a um critério. Ela suporta operadores lógicos (<>, =, >, e <) e caracteres curinga (? e *) para correspondência parcial.
Fórmulas relacionadas
Pesquisar um valor contendo texto específico com curingas
Para encontrar a primeira correspondência que contém uma determinada string de texto em um intervalo no Excel, você pode usar uma fórmula ÍNDICE e CORRESP com caracteres curinga - o asterisco (*) e o ponto de interrogação (?).
Correspondência parcial com PROCV
Há momentos em que você precisa que o Excel recupere dados com base em informações parciais. Para resolver o problema, você pode usar uma fórmula PROCV junto com caracteres curinga - o asterisco (*) e o ponto de interrogação (?).
Correspondência aproximada com ÍNDICE e CORRESP
Há momentos em que precisamos encontrar correspondências aproximadas no Excel para avaliar o desempenho dos funcionários, classificar as notas dos alunos, calcular o frete com base no peso, etc. Neste tutorial, falaremos sobre como usar as funções ÍNDICE e CORRESP para recuperar os resultados necessários.
Pesquisar valor de correspondência mais próxima com múltiplos critérios
Em alguns casos, você pode precisar pesquisar o valor de correspondência mais próxima ou aproximada com base em mais de um critério. Com a combinação das funções ÍNDICE, CORRESP e SE, você pode fazer isso rapidamente no Excel.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
O Kutools para Excel Oferece Mais de 300 Recursos, Garantindo que O Que Você Precisa Está Apenas a Um Clique de Distância...
Office Tab - Habilite a Leitura e Edição com Abas no Microsoft Office (inclui Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduz centenas de cliques de mouse para você todos os dias, diga adeus à mão do mouse.
- Aumenta sua produtividade em 50% ao visualizar e editar vários documentos.
- Traz Guias Eficientes para o Office (inclui Excel), Assim Como Chrome, Edge e Firefox.