Fórmula do Excel: Verificar se a célula contém todas as várias coisas
Suponha que no Excel, há uma lista de valores na coluna E, e você deseja verificar se as células na coluna B contêm todos os valores da coluna E, retornando VERDADEIRO ou FALSO conforme mostrado na captura de tela abaixo. Neste tutorial, fornecemos uma fórmula para resolver essa tarefa.
Fórmula genérica:
=SOMARPRODUTO(--ÉNÚM(PROCURAR(coisas;texto)))=CONT.VALORES(coisas) |
Argumentos
Coisas: a lista de valores que você deseja usar para verificar se o argumento texto contém. |
Texto: a célula ou string de texto que você deseja verificar se contém o argumento coisas. |
Valor de retorno:
Esta fórmula retorna um valor lógico, FALSO: a célula não contém todas as coisas; VERDADEIRO: a célula contém todas as coisas.
Como esta fórmula funciona
Por exemplo, na coluna B, há uma lista de strings de texto que você deseja verificar se contêm todos os valores no intervalo E3:E5, use a fórmula abaixo.
=SOMARPRODUTO(--ÉNÚM(PROCURAR($E$3:$E$5;B3)))=CONT.VALORES($E$3:$E$5) |
Pressione Enter tecla, arraste a alça de preenchimento sobre as células que você deseja verificar. FALSO indica que a célula não contém todos os valores no intervalo E3:E5, enquanto VERDADEIRO indica que a célula relativa contém todos os valores.
Explicação
Função PROCURAR: a função PROCURAR retornará a localização do primeiro caractere da string de texto dentro de outra. Se a função PROCURAR encontrar o texto, ela retorna a posição, caso contrário, retorna o erro #VALOR!. Por exemplo, aqui a fórmula PROCURAR($E$3:$E$5;B4) procurará cada valor do intervalo E3:E5 na célula B4 e retornará a localização de cada string de texto na célula B4. Ela retornará um resultado de matriz como este: {1;7;12}
Função ÉNÚM: a função ÉNÚM verifica se um valor é um número e retorna VERDADEIRO ou FALSO. Aqui ÉNÚM(PROCURAR($E$3:$E$5;B4)) retornará o resultado da matriz como {true;true;true} pois a função PROCURAR encontra 3 números.
--ÉNÚM(PROCURAR($E$3:$E$5;B4)) converterá o valor VERDADEIRO para 1 e o valor FALSO para 0, então esta fórmula altera o resultado da matriz para {1;1;1}.
Função SOMARPRODUTO: é usada para multiplicar intervalos ou somar matrizes juntas e retorna a soma dos produtos. O SOMARPRODUTO(--ÉNÚM(PROCURAR($E$3:$E$5;B4))) retorna 1+1+1=3.
Função CONT.VALORES: retorna o número de células não vazias. CONT.VALORES($E$3:$E$5) retorna 3, então o resultado de SOMARPRODUTO(--ÉNÚM(PROCURAR($E$3:$E$5;B4))) é igual a 3, a fórmula inteira retornará VERDADEIRO, caso contrário, retornará FALSO.
Observações:
A fórmula =SOMARPRODUTO(--ÉNÚM(PROCURAR(coisas;texto)))=CONT.VALORES(coisas) não está fazendo uma verificação exata. Veja a captura de tela:
Arquivo de Exemplo
Clique para baixar o arquivo de exemplo
Fórmulas Relacionadas
- Contar células iguais a
Com a função CONT.SE, você pode contar células que são iguais ou não contêm um valor especificado. - Contar células iguais a x ou y
Às vezes, você pode querer contar o número de células que atendem a um de dois critérios, nesse caso, você pode usar a função CONT.SE. - Contar células iguais a x e y
Este artigo apresenta a fórmula para contar células que, ao mesmo tempo, correspondem a dois critérios. - Contar células diferentes de
Este artigo discute como usar a função CONT.SE para contar o número de células que não são iguais a um valor específico.
As Melhores Ferramentas de Produtividade para o Office
Kutools para Excel - Ajuda Você a Se Destacar na Multidão
Kutools para Excel Conta com Mais de 300 Funcionalidades, Garantindo Que O Que Você Precisa Está Apenas Um Clique de Distância...
Office Tab - Ative a Leitura e Edição com Guias 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.