Formuła ogólna
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,text)),0)))
Podsumowanie
Aby skategoryzować tekst za pomocą słów kluczowych z dopasowaniem „zawiera”, możesz użyć funkcji SZUKAJ z pomocą INDEKSU i PODAJ. W przedstawionym przykładzie formuła w C5 to:
(=INDEX(categories,MATCH(TRUE,ISNUMBER(SEARCH(keywords,B5)),0)))
gdzie słowa kluczowe to nazwany zakres E5: E14, a kategorie to nazwany zakres F5: F14.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
Zasadniczo jest to funkcja INDEKS i PODAJ.POZYCJĘ.
Wewnątrz funkcji PODAJ.POZYCJĘ używamy funkcji SZUKAJ do wyszukiwania komórek w kolumnie B dla każdego wymienionego słowa kluczowego w nazwanych słowach kluczowych zakresu (E5: E14):
SEARCH(keywords,B5)
Ponieważ szukamy wielu elementów (w nazwanych słowach kluczowych z zakresu ), otrzymamy wiele wyników w następujący sposób:
(#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;24;#VALUE!;#VALUE!;#VALUE!)
Wartość! błąd występuje, gdy SEARCH nie może znaleźć tekstu. Gdy funkcja SEARCH znajdzie dopasowanie, zwraca liczbę odpowiadającą pozycji tekstu w komórce.
Aby zmienić te wyniki na bardziej użyteczny format, używamy funkcji ISNUMBER, która konwertuje wszystkie wartości na TRUE / FALSE w następujący sposób:
(FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE)
Ta tablica trafia do funkcji PODAJ.POZYCJĘ jako tablica_wyszukiwania, z wartością_wyszukiwania ustawioną na PRAWDA. PODAJ.POZYCJĘ zwraca pozycję pierwszej wartości PRAWDA znalezionej w tablicy (w tym przypadku 7), która jest przekazywana do funkcji INDEKS jako argument nr_wiersza:
=INDEX(categories,7)
INDEKS zwraca siódmą pozycję z kategorii , „Auto”, jako wynik końcowy.
Dzięki XLOOKUP
Dzięki funkcji XLOOKUP tę formułę można nieco uprościć. XLOOKUP może wykorzystywać tę samą logikę, co w powyższej funkcji MATCH, więc równoważna formuła to:
=XLOOKUP(TRUE,ISNUMBER(SEARCH(keywords,B5)),categories)
XLOOKUP lokalizuje pierwszą wartość TRUE w tablicy i zwraca odpowiednią wartość z kategorii .
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, pojawi się ze znakami interpunkcyjnymi itp.
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 możesz wyszukiwać całe słowa otoczone spacjami.
