Excel Multi questões de formatação condicional

Question

Eu preciso de uma fórmula que analise o texto em uma célula e a compare a outras três colunas e realce uma cor específica.

Veja o exemplo:

Dados

• A célula A2 contém "Este é o texto de amostra na célula"
• A célula B2 contém "amostra"
• A célula C2 contém "texto"
• A célula D2 contém "célula"

Fórmula desejada

1. Se A2 contiver B2 OU C2 OU D2, destaque amarelo
2. Se A2 contiver B2 E C2, OU contiver B2 E D2, OU contiver C2 E D2, realce azul
3. Se A2 contiver B2 E C2 E D2, destaque verde

O que eu tentei

• Eu tentei usar =SUM(COUNTIF(A2,"*"&B2&"*")) . Isso funciona como desejado para o nº 1, mas exige que eu crie regras de formatação separadas, uma para B2, C2 e D2. Por isso, não é muito elegante e não consegui descobrir a sintaxe adequada para fazer com que funcione como # 2.

• Para o número 3, tentei usar =SUM(COUNTIF(A2,"*"&B2:D2&"*")) , mas isso está retornando falsos positivos e exibindo quando apenas uma célula atende aos critérios.

• Eu também tentei =SUMPRODUCT(--(A2=things))>0 ( things = uma matriz nomeada) como detalhado aqui: link Mas isso não funciona, pois exige que eu crie nomes de lista exclusivos para cada linha.

• E também tentei =IF(OR(A2="sample",A2="text",A2="cell"),"True","False") e, em seguida, aplique um formato condicional para saídas True, mas isso não funciona, pois depende de valores codificados.

A fórmula deve ser capaz de usar valores dinâmicos (o número de célula A2, B2, C2, etc.), não pode depender de nenhum valor físico. Vou precisar usar essa fórmula para centenas de linhas, com valores diferentes conectados a cada célula. Portanto, a fórmula ainda deve funcionar da mesma maneira para as células A3, B3, C3 e D3 sem que eu precise alterar nada. Ele precisa ser uma cópia direta e colar na coluna que está sendo avaliada, neste exemplo, seria tudo na coluna A.

Eu sou novo aqui, então peço desculpas se isso foi respondido em outro lugar, não consigo encontrá-lo. Se assim for, por favor me direcione para a resposta!

    

Answer 1

Isso deve funcionar:

=SUMPRODUCT(-- ISNUMBER(SEARCH(B2:D2,$A$2)))

Isso é semelhante à sua terceira tentativa, mas -

• você não precisa usar um intervalo nomeado. Você pode usar apenas B2:D2 , que funcionará como você espera quando você copiá-lo para a próxima linha.
• você precisa verificar se a string contém os valores de teste, em vez de ser igual a eles.

SEARCH retorna o ponto inicial de uma cadeia dentro de outra, ISNUMBER converte isso para verdadeiro ou falso dependendo se a cadeia foi encontrada e -- converte o valor booleano para 0 ou 1. Passando uma faixa de valores para SEARCH significa que a saída é uma matriz de 0s e 1s, em vez de um único valor.

Usar SUMPRODUCT em vez de SUM permite adicionar esses valores sem ter que usar a notação de fórmula de matriz do Excel. O resultado final é um número de 0 a 3, dependendo de quantas das comparações foram bem-sucedidas, o que você pode testar para orientar a formatação condicional.

Observe que, se alguma das suas células de comparação estiver em branco, a comparação será bem-sucedida (já que todas as strings contêm a string vazia).

(Com base nas instruções em link )