Formuła ogólna
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Podsumowanie
Aby policzyć liczbę określonych słów lub słów kluczowych, które pojawiają się w danej komórce, możesz użyć formuły opartej na funkcjach WYSZUKAJ, NUMER i SUMA. W przedstawionym przykładzie formuła w C5 to:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
gdzie „słowa kluczowe” to nazwany zakres E5: E9.
Wyjaśnienie
Uwaga: jeśli słowo kluczowe występuje więcej niż raz w danej komórce, zostanie policzone tylko raz. Innymi słowy, formuła liczy tylko wystąpienia różnych słów kluczowych.
Rdzeniem tej formuły jest podejście ISNUMBER + SEARCH do znajdowania tekstu w komórce, które zostało wyjaśnione bardziej szczegółowo tutaj. W tym przypadku szukamy w każdej komórce wszystkich słów z nazwanego zakresu „słowa kluczowe” (E5: E9). Robimy to, przekazując zakres do funkcji SEARCH jako argument find_text. Ponieważ przekazujemy tablicę 5 elementów:
("green";"orange";"white";"blue";"pink")
w wyniku otrzymujemy tablicę 5 pozycji:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Liczby odpowiadają dopasowaniom, a #ARG! błąd oznacza, że nie znaleziono żadnego dopasowania. W tym przypadku, ponieważ nie obchodzi nas, gdzie tekst został znaleziony w komórce, używamy ISNUMBER, aby przekonwertować tablicę na wartości TRUE i FALSE:
(FALSE;FALSE;TRUE;FALSE;TRUE)
I podwójnie ujemne (-), aby zmienić je na jedynki i zera:
(0;0;1;0;1)
Funkcja SUMPRODUCT zwraca następnie po prostu sumę tablicy, w tym przypadku 2.
Obsługa pustych słów kluczowych
Jeśli zakres słów kluczowych zawiera puste komórki, formuła nie będzie działać poprawnie, ponieważ funkcja SEARCH zwraca zero podczas wyszukiwania pustego ciągu („”). Aby odfiltrować puste komórki w zakresie słów kluczowych, możesz użyć poniższej odmiany:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Uwaga: ta wersja jest formułą tablicową i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
