Fórmula do Excel: Verificar se uma célula contém um de entre vários valores, mas exclui outros valores

Imagine que tem duas listas de valores e pretende verificar se a célula B3 contém algum dos valores no intervalo E3:E5, mas, ao mesmo tempo, não contém nenhum dos valores no intervalo F3:F4, conforme ilustrado na imagem seguinte. Este tutorial fornece uma fórmula para resolver rapidamente esta tarefa no Excel e explica os seus argumentos.

Fórmula genérica:

Argumentos

Valor de retorno:

A fórmula devolve 1 ou 0: devolve 1 quando a célula contém um dos valores a incluir e não contém nenhum dos valores a excluir; caso contrário, devolve 0. Nesta fórmula, os valores 1 e 0 são interpretados como os equivalentes lógicos VERDADEIRO e FALSO.

Como funciona esta fórmula

Imagine que pretende verificar se a célula B3 contém um dos valores no intervalo E3:E5, mas simultaneamente excluir os valores no intervalo F3:F4; utilize a fórmula seguinte

Prima Enter para obter o resultado da verificação.

Explicação

Parte 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) verifica se a célula contém valores em E3:E5

Função PROCURAR: a função PROCURAR devolve a posição do primeiro carácter de uma cadeia de texto dentro de outra. Se encontrar correspondência, devolve a posição relativa; caso contrário, devolve o erro #VALOR!. Por exemplo, a fórmula SEARCH($E$3:$E$5,B3) procura cada valor do intervalo E3:E5 na célula B3 e devolve a localização de cada uma dessas cadeias de texto em B3, apresentando um resultado em matriz como este:{1;7;12}.

Função É.NÚM: a função É.NÚM devolve VERDADEIRO quando uma célula contém um número. Assim, ISNUMBER(SEARCH($E$3:$E$5,B3)) devolve um resultado em matriz como {VERDADEIRO;VERDADEIRO;VERDADEIRO}, já que a função PROCURAR encontra três números.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) converte o valor VERDADEIRO em 1 e o valor FALSO em 0, transformando assim o resultado da fórmula numa matriz {1;1;1}.

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

Por fim, compare a fórmula à esquerda SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) com 0: se o resultado da fórmula for superior a 0, o valor devolvido será VERDADEIRO; caso contrário, será FALSO. Neste caso, devolve VERDADEIRO.

Parte 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) verifica se a célula não contém valores em F3:F4

A fórmula SEARCH($F$3:$F$4,B3) procura cada valor do intervalo F3:F4 na célula B3 e devolve a posição de cada cadeia de texto encontrada. O resultado é devolvido sob a forma de uma matriz, como esta: {#VALOR!;#VALOR!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) irá devolver um resultado em matriz como {FALSO;FALSO}, uma vez que a função PROCURAR não encontra nenhum número.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) converte o valor VERDADEIRO em 1 e o valor FALSO em 0, transformando assim o resultado da fórmula numa matriz {0;0}.

Função SOMAPRODUTO: multiplica intervalos ou matrizes e devolve a soma dos produtos. A fórmula SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) devolve 0+0=0.

Por fim, compare a fórmula à esquerda SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) com 0: se o resultado da fórmula for igual a 0, o valor devolvido será VERDADEIRO; caso contrário, será FALSO. Neste caso, devolve VERDADEIRO.

Parte 3: Multiplicar as duas fórmulas

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>,0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

Nesta fórmula, 1 e 0 são interpretados como os valores lógicos VERDADEIRO e FALSO.

Ficheiro de exemplo

Clique para descarregar o ficheiro de exemplo

Fórmulas relacionadas

• Verificar se uma célula contém um texto específico
  Para verificar se uma célula contém alguns textos no intervalo A, mas não contém os textos no intervalo B, pode utilizar uma fórmula matricial que combina as funções CONTAR, PROCURAR e E no Excel
• Verificar se uma célula contém um de entre vários elementos
  Este tutorial apresenta uma fórmula para verificar se uma célula contém um de vários valores no Excel, explicando os seus argumentos e como funciona.
• Verificar se uma célula contém um dos elementos
  Imagine que, no Excel, tem uma lista de valores na coluna E e pretende verificar se as células da coluna B contêm algum dos valores dessa coluna, devolvendo VERDADEIRO ou FALSO.
• Verificar se uma célula contém um número
  Por vezes, poderá precisar de verificar se uma célula contém caracteres numéricos. Este tutorial apresenta uma fórmula que devolve VERDADEIRO se a célula contiver um número e FALSO caso contrário.