Formuła ogólna
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Podsumowanie
Aby przetestować komórkę w celu sprawdzenia, czy zawiera jeden z wielu ciągów, możesz użyć formuły opartej na funkcjach WYSZUKAJ, NUMER i SUMA. Wzór w C5, skopiowany w dół, to:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
gdzie rzeczy to nazwany zakres E5: E9.
Wyjaśnienie
Chcemy przetestować każdą komórkę w B5: B11, aby sprawdzić, czy zawiera którykolwiek z ciągów z nazwanego zakresu rzeczy (E5: E9). Formuła, której używamy w C5, skopiowana w dół, to:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Ta formuła jest oparta na formule (wyjaśnionej tutaj), która sprawdza komórkę pod kątem pojedynczego podciągu. Jeśli komórka zawiera podciąg, formuła zwraca wartość PRAWDA. Jeśli nie, formuła zwraca FALSE:
ISNUMBER(SEARCH(things,B5))
Jednak w tym przypadku podajemy SEARCH listę ciągów. Ponieważ w rzeczach jest 5 ciągów , funkcja SEARCH zwraca 5 wyników w tablicy takiej jak ta:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Kiedy SEARCH znajduje ciąg, zwraca pozycję tego ciągu. Jeśli SZUKAJ nie znajdzie ciągu, zwraca #ARG! błąd. Ponieważ „żółty” pojawia się jako pierwsze słowo w B5, widzimy 1. Ponieważ pozostałe ciągi nie zostały znalezione, pozostałe 4 elementy są błędami.
Ta tablica jest zwracana bezpośrednio do funkcji ISNUMBER. ISNUMBER następnie zwraca tablicę wartości PRAWDA / FAŁSZ:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Jeśli w tablicy mamy choćby jedną wartość TRUE, wiemy, że komórka zawiera co najmniej jeden z ciągów, których szukamy. Najłatwiejszym sposobem sprawdzenia PRAWDA jest dodanie wszystkich wartości do siebie. Możemy to zrobić za pomocą SUMPRODUCT, ale najpierw musimy wymusić wartości TRUE / FALSE na 1 i 0 za pomocą podwójnego ujemnego (-) w następujący sposób:
--ISNUMBER(SEARCH(things,B5))
Daje to nową tablicę zawierającą tylko 1 i 0:
(1;0;0;0;0)
dostarczane bezpośrednio do SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Mając tylko jedną tablicę do przetworzenia, SUMPRODUCT dodaje elementy do tablicy i zwraca wynik. Każdy wynik niezerowy oznacza, że mamy „trafienie”, więc dodajemy> 0, aby wymusić ostateczny wynik PRAWDA lub FAŁSZ:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Z listą zakodowaną na stałe
Nie ma potrzeby używania zakresu jako listy ciągów do wyszukania. Możesz także użyć stałej tablicowej. Na przykład, aby sprawdzić „czerwony”, „niebieski” i „zielony”, możesz użyć następującego wzoru:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Zapobieganie fałszywym dopasowaniom
Jednym z problemów z tym podejściem jest to, że możesz otrzymać fałszywe dopasowania z podciągów, które pojawiają się wewnątrz dłuższych słów. 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 dopasowuje „zawiera”.
Aby szybko się zorientować, 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ę ze znakami interpunkcyjnymi.
Jeśli potrzebujesz dokładniejszego 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 używasz wzoru z tej strony w tekście wynikowym.
