Fórmula genérica
(=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0))
Resumo
Para testar uma célula para ver se contém certas palavras, mas não outras, você pode usar uma fórmula de matriz baseada nas funções COUNT e SEARCH, agrupadas na função AND.
No exemplo mostrado, a fórmula em C5 é:
(=AND(COUNT(SEARCH(inc,B5))>0,COUNT(SEARCH(exc,B5))=0))
Esta fórmula retorna TRUE quando B5 contém qualquer uma das palavras no intervalo nomeado inc e nenhuma das palavras no intervalo nomeado exc .
Esta é uma fórmula de matriz e deve ser inserida com Control + Shift + Enter.
Explicação
Esta fórmula depende da função AND para testar duas condições ao mesmo tempo:
- Contagem de palavras do intervalo nomeado inc é> 0
- Contagem de palavras do intervalo nomeado exc é = 0
Se ambas as condições forem TRUE, a fórmula retornará TRUE. Se qualquer uma das condições for FALSE, a fórmula retornará FALSE.
O teste para várias palavras é feito usando a função SEARCH com a ajuda de COUNT.
Quando SEARCH recebe mais de um item para procurar, ele retorna uma matriz de resultados, um por item. Quando uma correspondência é encontrada, SEARCH retorna a posição da correspondência. Quando nenhuma correspondência é encontrada, SEARCH retorna o erro #VALUE. A função COUNT obtém uma contagem de números na matriz retornada por SEARCH. COUNT ignora erros por padrão.
No exemplo mostrado, a fórmula é resolvida em etapas como esta
=AND(COUNT((1;11;#VALUE!))>0,COUNT((#VALUE!;#VALUE!))=0) =AND(2>0,0=0) =AND(TRUE,TRUE) =TRUE
Com valores embutidos em código
Não há exigência de que você use um intervalo para sua lista de itens. Se você está procurando apenas um pequeno número de coisas, pode usar uma lista no formato de matriz, que é chamada de constante de matriz. Por exemplo, para testar vermelho, azul ou verde, mas excluir rosa e laranja, você pode usar:
=AND(COUNT(SEARCH(("red","blue","green"),B5))>0,COUNT(SEARCH(("orange","pink"),B5))=0)
Esta versão não requer a sintaxe da fórmula de matriz control + shift + enter.
