Neste artigo, aprenderemos como procurar valores ausentes na tabela usando a fórmula no Excel.
Por exemplo, temos uma lista de valores e precisamos de confirmação sobre a localização do valor na lista. Para isso, estaremos considerando a situação e sugeriremos algumas fórmulas para fazer o mesmo. Estaremos usando operação de função diferente dependendo da facilidade do table_array.
A seguinte função estará em uso. Portanto, conheça um pouco sobre eles antes de usá-los.
- Função IF
- Função CONT.SE
- Função ISNA
- Função VLOOKUP
- Função MATCH
Vamos construir uma fórmula a partir disso. Em primeiro lugar, o valor de pesquisa é pesquisado na coluna particular da matriz da tabela. Em seguida, os valores correspondentes nos darão a confirmação usando a função IF. A função IF retorna a confirmação usando os valores "Is there" e "Missing".
Primeiro Método: Uso da função CONT.SE e IF
Use a fórmula genérica
| = IF (CONT.SE (lista, valor_célula), "Existe", "Ausente") |
Explicação:
- A função COUNTIF mantém a contagem de cell_value na lista e retorna o número para a função IF.
- A função IF considera 0 como FALSO e qualquer outro número inteiro diferente de 0 como VERDADEIRO.
- A função IF retorna "Existe" como valor se for verdadeiro e "ausente" como valor se for falso.
Exemplo:
Vamos fazer isso usando a fórmula do exemplo.
Aqui temos uma tabela e precisamos extrair informações dela.
Aqui, precisamos da confirmação pela lista de ID. Portanto, usamos o intervalo nomeado para a lista de IDs. EU IRIA intervalo nomeado para C2: C14.
Então, usaremos a fórmula para obter o valor total
| = SE (CONT.SE (ID, G4), "Existe", "Ausente") |
Explicação para a fórmula:
- A função CONT.SE mantém a contagem de ID 900 na lista e retorna a contagem para a função IF.
- A função IF considera 0 como FALSO e qualquer outro número inteiro diferente de 0 como VERDADEIRO
- A função IF retorna "Existe" como Valor se verdadeiro e "Ausente" como valor se Falso.
Aqui, as matrizes de argumento para a função são fornecidas como referência de célula.
Como você pode ver, a fórmula retorna os valores para o número de ID 807 e 953. Mas retorna Ausente para o número de ID 900.
Segundo Método: Uso da função ISNA & VLOOKUP.
Sintaxe da fórmula:
| = IF (ISNA (VLOOKUP (cell_value, list, 1, 0)), "Missing", "Is There") |
Explicação para a fórmula:
- A função VLOOKUP procura o valor da célula na 1ª coluna da lista table_array. A função retorna o valor se encontrado ou então retorna o erro # N / A.
- A função ISNA captura o erro # N / A e retorna TRUE se o erro # N / A existir ou então retorna FALSE.
- A função IF retorna "Existe" como valor se for FALSO e "ausente" como valor se for VERDADEIRO.
Como você pode ver no instantâneo acima. A fórmula retorna "Existe" para os IDs 807 e 953 correspondentes. Mas retorna "Ausente" para os IDs 900 não correspondentes.
Terceiro Método: Uso da função ISNA & MATCH.
Sintaxe da fórmula:
| = IF (ISNA (MATCH (cell_value, list, 0)), "Missing", "Is There") |
Explicação para a fórmula:
- A função MATCH procura o valor da célula na lista table_array. A função retorna o valor se encontrado ou então retorna o erro # N / A.
- A função ISNA captura o erro # N / A e retorna TRUE se o erro # N / A existir ou então retorna FALSE.
- A função IF retorna "Existe" como valor se for FALSO e "ausente" como valor se for VERDADEIRO.
Como você pode ver no instantâneo acima. A fórmula retorna "Existe" para os IDs 807 e 953 correspondentes. Mas retorna "Ausente" para os IDs 900 não correspondentes.
Os 3 exemplos acima explicados para encontrar os valores ausentes na lista no Excel. Todas as três fórmulas funcionam bem, mas há alguns pontos a serem observados.
Notas:
- A função VLOOKUP não olha para a esquerda em table_array.
- A função CONT.SE suporta curingas ( * , ? ) que ajuda a extrair valores com frases.
- Os valores não numéricos devem ser fornecidos entre aspas duplas ("valor") ou use referência_célula…
- Veja a lista inteira, pois a função retorna valores onde quer que corresponda.
- O argumento da matriz para a função pode ser fornecido como referência de célula ou intervalos nomeados.
- Você pode personalizar essas fórmulas conforme necessário usando outra função do Excel.
- A função retorna a soma dos valores que satisfazem todas as condições.
Espero que você tenha entendido como Encontrar valores ausentes no Excel. Explore mais artigos sobre fórmulas de funções do Excel aqui. Sinta-se à vontade para expressar sua dúvida ou feedback sobre o artigo acima. Nós iremos atendê-lo.
INDEX-MATCH em Excel
VLOOKUP Valores Múltiplos
VLOOKUP com Dynamic Col Index
Correspondência parcial com a função VLOOKUP
Vlookup por data no Excel
17 coisas sobre VLOOKUP do Excel
Artigos populares
50 Atalho do Excel para aumentar sua produtividade
Editar uma lista suspensa
Referência absoluta no Excel
Se com formatação condicional
Se com curingas
Vlookup por data