Encontrar valores em falta
Há situações em que precisa de comparar duas listas para verificar se um valor da lista A existe na lista B no Excel. Por exemplo, tem uma lista de produtos e pretende verificar se os produtos da sua lista existem na lista de produtos fornecida pelo seu fornecedor. Para realizar esta tarefa, indicamos abaixo três métodos; sinta-se à vontade para escolher o que preferir.
Encontrar valores em falta com CORRESP, É.N/D e SE
Encontrar valores em falta com PROCV, É.N/D e SE
Encontrar valores em falta com CONTAR.SE e SE
Encontrar valores em falta com CORRESP, É.N/D e SE
Para verificar se todos os produtos da sua lista existem na lista do seu fornecedor, conforme mostrado na imagem acima, comece por utilizar a função CORRESP para obter a posição de um produto da sua lista (valor da lista A) na lista do fornecedor (lista B). A função CORRESP devolve o erro #N/D sempre que um produto não é encontrado. Em seguida, introduza esse resultado na função É.N/D para converter os erros #N/D em VERDADEIRO — indicando que esses produtos estão em falta. Por fim, a função SE devolverá o resultado pretendido.
Sintaxe genérica
=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")
√ Nota: Pode substituir «Em falta» e «Encontrado» por quaisquer valores que desejar.
- lookup_value: O valor que a função CORRESP utiliza para encontrar a sua posição, caso exista no lookup_range, ou devolve o erro #N/D se não for encontrado. Neste caso, refere-se aos produtos da sua lista.
- lookup_range: O intervalo de células a comparar com o lookup_value. Aqui refere-se à lista de produtos do fornecedor.
Para verificar se todos os produtos da sua lista existem na lista do seu fornecedor, copie ou introduza a fórmula seguinte na célula H6 e prima Enterpara obter o resultado:
=SE(É.N/D(CORRESP()))30002,$B$6:$B$10,0)),«Em falta»,«Encontrado»)
Ou, utilize Uma referência de célula para tornar a fórmula dinâmica:
=SE(É.NÃO.DISP(PROCURAR()))G6;$B$6:$B$10;0));«Em falta»;«Encontrado»)
√ Nota: Os sinais de dólar ($) acima indicam referências absolutas, o que significa que o intervalo_procurana fórmula não será alterado ao mover ou copiar a fórmula para outras células. Contudo, não foram adicionados sinais de dólar ao valor_procuraporque pretende que este seja dinâmico. Após introduzir 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 utilizamos a fórmula abaixo como exemplo:
=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")
- MATCH(G8,$B$6:$B$10,0): O tipo_de_correspondência 0 obriga a função CORRESP a devolver um valor numérico que indica a posição da primeira correspondência de 3004 — o valor na célula G8 — no vetor $B$6:$B$10. Contudo, neste caso, a função CORRESP não conseguiu encontrar o valor no vetor de procura, pelo que devolve o erro #N/D.
- É.N/D()MATCH(G8,$B$6:$B$10,0))=É.N/D()#N/D):A função É.N/D verifica se um valor corresponde ao erro «#N/D». Se sim, devolve VERDADEIRO; caso contrário — ou seja, se o valor for qualquer coisa diferente desse erro — devolve FALSO. Assim, esta fórmula É.N/D irá devolver VERDADEIRO.
- SE()É.N/D()CORRESP(G8,$B$6:$B$10,0)),«Em falta»,«Encontrado») = SE(VERDADEIRO,«Em falta»,«Encontrado»): A função SE devolverá «Em falta» se a comparação efetuada pelas funções É.N/D e CORRESP for VERDADEIRA; caso contrário, devolverá «Encontrado». Assim, a fórmula irá devolver Em falta.
Encontrar valores em falta com PROCV, É.NÃO.DISP e SE
Para verificar se todos os produtos da sua lista existem na lista do seu fornecedor, pode substituir a função PROCURAR acima por PROCV, já que esta funciona de forma semelhante, devolvendo o erro #N/D sempre que um valor não for encontrado noutra lista — ou seja, estiver em falta.
Sintaxe genérica
=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")
√ Nota: Pode substituir «Em falta» e «Encontrado» por quaisquer valores que desejar.
- lookup_value: O valor que a função PROCV utiliza para localizar a sua posição — caso exista no lookup_range — ou devolver o erro #N/D se não for encontrado. Aqui refere-se aos produtos da sua lista.
- lookup_range:O intervalo de células a comparar com o lookup_value. Aqui refere-se à lista de produtos do fornecedor.
Para verificar se todos os produtos da sua lista existem na lista do seu fornecedor, copie ou introduza a fórmula abaixo na célula H6 e prima Enterpara obter o resultado:
=SE(É.NÃO.DISP(VLOOKUP()))30002;$B$6:$B$10;1;FALSO));«Em falta»;«Encontrado»)
Ou, utilize Uma referência de célula para tornar a fórmula dinâmica:
=SE(É.NÃO.DISP(VLOOKUP()))G6;$B$6:$B$10;1;FALSO));«Em falta»;«Encontrado»)
√ Nota: Os sinais de dólar ($) acima indicam referências absolutas, o que significa que o intervalo_procurana fórmula não será alterado ao mover ou copiar a fórmula para outras células. Contudo, não foram adicionados sinais de dólar ao valor_procuraporque pretende que este seja dinâmico. Após introduzir 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 utilizamos a fórmula abaixo como exemplo:
=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")
- VLOOKUP(G8,$B$6:$B$10,1,FALSE): O valor_procurado_na_matriz FALSO obriga a função PROCV a procurar e devolver o valor que corresponda exatamente a 3004, o valor na célula G8. Se o lookup_value 3004 existir na 1ª coluna do vetor $B$6:$B$10, a função PROCV irá devolver esse valor; caso contrário, devolverá o valor de erro #N/D. Aqui, 3004 não existe no vetor, logo o resultado será #N/D.
- É.N/D()VLOOKUP(G8,$B$6:$B$10,1,FALSE))=É.N/D()#N/D):A função É.N/D verifica se um valor corresponde ao erro «#N/D». Se for esse o caso, devolve VERDADEIRO; caso contrário — ou seja, se o valor for qualquer coisa que não seja o erro «#N/D» — devolve FALSO. Assim, esta fórmula É.N/D irá devolver VERDADEIRO.
- SE()É.N/D()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),«Em falta»,«Encontrado») = SE(VERDADEIRO,«Em falta»,«Encontrado»): A função SE devolve «Em falta» se a comparação realizada pelas funções É.N/D e PROCV for VERDADEIRA; caso contrário, devolve «Encontrado». Assim, a fórmula irá devolver Em falta.
Encontrar valores em falta com CONTAR.SE e SE
Para verificar se todos os produtos da sua lista existem na lista do seu fornecedor, pode usar uma fórmula mais simples com as funções CONTAR.SE e SE. A fórmula tira partido do facto de o Excel considerar qualquer número diferente de zero (0) como VERDADEIRO. Assim, se um valor existir noutra lista, a função CONTAR.SE devolve o número de ocorrências desse valor, e a função SE interpreta esse resultado como VERDADEIRO; caso contrário, se o valor não existir na lista, a função CONTAR.SE devolve 0, que a função SE interpreta como FALSO.
Sintaxe genérica
=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")
√ Nota: Pode alterar «Encontrado», «Em falta» para quaisquer valores conforme necessário.
- lookup_range:O intervalo de células a comparar com o lookup_value. Aqui refere-se à lista de produtos do fornecedor.
- lookup_value: O valor que a função CONTAR.SE utiliza para devolver o número de ocorrências na lookup_range. Neste caso, refere-se aos produtos da sua lista.
Para verificar se todos os produtos da sua lista existem na lista do seu fornecedor, copie ou introduza a fórmula abaixo na célula H6 e prima Enterpara obter o resultado:
=SE(CONTAR.SE())$B$6:$B$10;30002);«Encontrado»;«Em falta»)
Ou, utilize Uma referência de célula para tornar a fórmula dinâmica:
=SE(CONTAR.SE())$B$6:$B$10;G6);«Encontrado»;«Em falta»)
√ Nota: Os sinais de dólar ($) acima indicam referências absolutas, o que significa que o intervalo_procurana fórmula não será alterado ao mover ou copiar a fórmula para outras células. Contudo, não foram adicionados sinais de dólar ao valor_procuraPorque pretende que este seja dinâmico. Após introduzir a fórmula, arraste a alça de preenchimento para baixo e aplique-a automaticamente às células seguintes.
Explicação da fórmula
Aqui utilizamos a fórmula abaixo como exemplo:
=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")
- COUNTIF($B$6:$B$10,G8): A função CONTAR.SE conta quantas vezes 3004, o valor na célula G8, aparece no intervalo $B$6:$B$10. Como 3004 não existe nesse intervalo, o resultado será 0.
- SE()COUNTIF($B$6:$B$10,G8),«Encontrado»,«Em falta») = SE(0,«Encontrado»,«Em falta»): A função SE avalia 0 como FALSO. Assim, a fórmula devolve Em falta, o valor correspondente ao caso em que o primeiro argumento é avaliado como FALSO.
Funções relacionadas
A função SE é uma das funções mais simples e úteis na Pasta de Trabalho do Excel. Realiza um teste lógico básico que, consoante o resultado da comparação, devolve um valor se for VERDADEIRO ou outro valor se for FALSO.
A função CORRESP do Excel procura um valor específico num intervalo de células e devolve a sua posição relativa.
A função PROCV do Excel procura um valor ao encontrar uma correspondência na primeira coluna de uma tabela e devolve o valor correspondente de uma coluna especificada, na mesma linha.
A função CONTAR.SE é uma função estatística do Excel usada para contar o número de células que atendem a um determinado critério. Suporta operadores lógicos (como > e <) e os caracteres universais (? e *) para correspondências parciais.
Fórmulas relacionadas
Procurar um valor que contenha texto específico com carateres universais
Para encontrar a primeira correspondência que contenha uma determinada cadeia de texto num intervalo no Excel, utilize as funções ÍNDICE e CORRESP, combinadas com carateres universais – o asterisco (*) e o ponto de interrogação (?).
Correspondência parcial com PROCV
Há ocasiões em que precisa que o Excel recupere dados com base em informações parciais. Para resolver esse desafio, utilize a fórmula PROCV combinada com carateres universais — o asterisco (*) e o ponto de interrogação (?).
Correspondência aproximada com ÍNDICE e CORRESP
Há ocasiões em que precisamos de encontrar correspondências aproximadas no Excel — seja para avaliar o desempenho dos colaboradores, classificar as notas dos alunos ou calcular portes com base no peso. Neste tutorial, mostramos como utilizar as funções ÍNDICE e CORRESP para obter exatamente os resultados que procura.
Procurar o valor mais próximo com vários critérios
Em alguns casos, poderá necessitar de procurar o valor mais próximo ou aproximado com base em mais do que um critério. Com a combinação das funções ÍNDICE, CORRESP e SE, pode realizar esta tarefa rapidamente no Excel.
As Melhores Ferramentas de Produtividade para o Office
Kutools for Excel – Ajuda-o a destacar-se da multidão
Kutools for Excel Oferece mais de 300 funcionalidades,garantindo que tudo o que precisa está a apenas um clique…
Office Tab – Ative a leitura e edição com separadores no Microsoft Office (incluindo o Excel)
- Um segundo para alternar entre dezenas de documentos abertos!
- Reduza centenas de cliques diários e diga adeus à tendinite do rato.
- Aumente a sua produtividade em 50 % ao visualizar e editar vários documentos simultaneamente.
- Traz uma navegação eficiente com separadores ao Office (incluindo o Excel), tal como no Chrome, Edge e Firefox.