Fórmula do Excel: Verificar se a célula contém todos os elementos de uma lista

Imagine que, no Excel, tem uma lista de valores na coluna E e pretende verificar se as células da coluna B contêm todos esses valores, devolvendo VERDADEIRO ou FALSO, tal como ilustrado na imagem seguinte. Este tutorial apresenta uma fórmula eficaz para resolver esta tarefa.

Fórmula genérica:

Argumentos

Valor de retorno:

Esta fórmula devolve um valor lógico: VERDADEIRO indica que a célula contém todos os elementos; FALSO indica que não os contém.

Como funciona esta fórmula

Por exemplo, se na coluna B existir uma lista de cadeias de texto cujo conteúdo pretende verificar quanto à presença de todos os valores no intervalo E3:E5, utilize a fórmula seguinte.

Prima Enter e arraste a alça de preenchimento pelas células que pretende verificar. FALSO indica que a célula não contém todos os valores do intervalo E3:E5, enquanto VERDADEIRO significa que a célula respetiva inclui todos os valores.

Explicação

Função PROCURAR: a função PROCURAR devolve a posição do primeiro caráter de uma cadeia de texto dentro de outra. Se encontrar o texto, devolve a respetiva posição; caso contrário, devolve o erro #VALOR!. Por exemplo, a fórmula SEARCH($E$3:$E$5,B4) procura cada valor do intervalo E3:E5 na célula B4 e devolve a posição de cada ocorrência na célula B4, resultando num conjunto como este: {1;7;12}

Função É.NÚM: verifica se um valor é numérico e devolve VERDADEIRO ou FALSO. Neste caso, ISNUMBER(SEARCH($E$3:$E$5,B4)) devolve um conjunto como {true;true;true}, pois a função PROCURAR encontrou três correspondências numéricas.

--ISNUMBER(SEARCH($E$3:$E$5,B4)) converte o valor VERDADEIRO em 1 e o valor FALSO em 0, transformando assim o conjunto resultante em {1;1;1}.

Função SOMARPRODUTO: multiplica intervalos ou matrizes e devolve a soma dos produtos. A fórmula SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) devolve 1+1+1=3.

Função CONTAR.VALORES: devolve o número de células não vazias. COUNTA($E$3:$E$5) devolve 3, pelo que o resultado de SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) é igual a 3; assim, toda a fórmula devolve VERDADEIRO ou FALSO.

Observações:

A fórmula =SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) não efetua uma verificação exata. Veja a imagem:

Ficheiro de exemplo

Clique para descarregar o ficheiro de exemplo

Fórmulas relacionadas

• Contar células iguais a
  Com a função CONTAR.SE, pode contar células que são iguais a um valor especificado ou que não o contêm.
• Contar células iguais a x ou y
  Por vezes, pode querer contar o número de células que cumprem um de dois critérios — nesse caso, utilize a função CONTAR.SE.
• Contar células iguais a x e y
  Este artigo apresenta a fórmula para contar células que correspondem simultaneamente a dois critérios.
• Contar células diferentes de
  Este artigo explica como utilizar a função CONTAR.SE para contar o número de células que não correspondem a um valor específico.