Formuła ogólna
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Podsumowanie
Aby wyróżnić komórki zawierające jeden z wielu ciągów tekstowych, możesz użyć formuły opartej na funkcjach ISNUMBER i SEARCH oraz funkcji SUMPRODUCT. W przedstawionym przykładzie formatowanie warunkowe zastosowane do B4: B11 jest oparte na następującym wzorze:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Wyjaśnienie
Działając od wewnątrz, ta część formuły przeszukuje każdą komórkę w B4: B11 pod kątem wszystkich wartości z nazwanego zakresu „rzeczy”:
--ISNUMBER(SEARCH(things,B4)
Funkcja SEARCH zwraca pozycję wartości, jeśli zostanie znaleziona, oraz błąd #VALUE, jeśli nie zostanie znaleziona. W przypadku B4 wyniki powracają w następującej tablicy:
(8;#VALUE!;#VALUE!)
Funkcja ISNUMBER zmienia wszystkie wyniki na TRUE lub FALSE:
(TRUE;FALSE;FALSE)
Podwójny minus przed ISNUMBER wymusza TRUE / FALSE do 1/0:
(1;0;0)
Następnie funkcja SUMPRODUCT sumuje wyniki, które są testowane względem zera:
=SUMPRODUCT((1;0;0))>0
Każdy wynik niezerowy oznacza, że znaleziono co najmniej jedną wartość, więc formuła zwraca wartość PRAWDA, wyzwalając regułę.
Ignoruj puste rzeczy
Aby zignorować puste komórki w nazwanym zakresie „rzeczy”, możesz wypróbować zmodyfikowaną formułę, taką jak ta:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Działa to, o ile testowane wartości tekstowe nie zawierają ciągu „FALSE”. Jeśli tak, możesz rozszerzyć funkcję JEŻELI tak, aby zawierała wartość, jeśli fałsz nie występuje w tekście (np. „Zzzz”, „####” itp.)
Opcja uwzględniająca wielkość liter
SEARCH nie rozróżnia wielkości liter. Aby również sprawdzić wielkość liter, zamień SZUKAJ na ZNAJDŹ w następujący sposób:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Zapobieganie fałszywym dopasowaniom
Jednym z problemów z tym podejściem jest to, że możesz zobaczyć fałszywe dopasowania spowodowane podciągami, które pojawiają się w dłuższych słowach. Na przykład, jeśli spróbujesz dopasować „dr”, możesz również znaleźć „Andrea”, „napój”, „wytrawny” itp., Ponieważ „dr” pojawia się w tych słowach. Dzieje się tak, ponieważ SEARCH automatycznie wykonuje dopasowanie „zawiera”.
W celu częściowego ustalenia, możesz dodać spację wokół wyszukiwanych słów (np. „Dr” lub „dr”), aby uniknąć przechwytywania „dr” w innym słowie. Ale to się nie powiedzie, jeśli „dr” pojawi się jako pierwszy lub ostatni w komórce lub pojawi się obok interpunkcji. Można to częściowo rozwiązać, dodając przestrzeń również wokół oryginalnego tekstu. Aby dodać spację na początku i na końcu obu jednocześnie, możesz wypróbować następującą formułę:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Jednak nie rozwiąże to problemów spowodowanych interpunkcją.
Jeśli potrzebujesz bardziej kompletnego rozwiązania, jedną z opcji jest normalizacja tekstu najpierw w kolumnie pomocniczej, zwracając uwagę, aby dodać również spację początkową i końcową. Następnie możesz wyszukiwać całe słowa otoczone spacjami.
