Обща формула
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Обобщение
За да преброите броя на конкретни думи или ключови думи, които се появяват в дадена клетка, можете да използвате формула, базирана на функциите SEARCH, ISNUMBER и SUMPRODUCT. В показания пример формулата в C5 е:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
където "ключови думи" е наименуваният диапазон E5: E9.
Обяснение
Забележка: ако ключова дума се появи повече от веднъж в дадена клетка, тя ще бъде отчетена само веднъж. С други думи, формулата отчита само случаи на различни ключови думи.
Ядрото на тази формула е ISNUMBER + SEARCH подход за намиране на текст в клетка, който е обяснен по-подробно тук. В този случай ние търсим във всяка клетка всички думи в посочения диапазон „ключови думи“ (E5: E9). Правим това, като предаваме диапазона в SEARCH като аргумент find_text. Тъй като предаваме масив от 5 елемента:
("green";"orange";"white";"blue";"pink")
в резултат получаваме масив от 5 елемента:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Числата съответстват на съвпадения, а #VALUE! грешка означава, че не е намерено съвпадение. В този случай, тъй като не ни интересува къде е намерен текстът в клетката, използваме ISNUMBER, за да преобразуваме масива в TRUE и FALSE стойности:
(FALSE;FALSE;TRUE;FALSE;TRUE)
И двойното отрицателно (-), за да ги промените в 1s и нули:
(0;0;1;0;1)
След това функцията SUMPRODUCT просто връща сумата от масива, 2 в този случай.
Работа с празни ключови думи
Ако диапазонът от ключови думи съдържа празни клетки, формулата няма да работи правилно, тъй като функцията SEARCH връща нула при търсене на празен низ (""). За да филтрирате празни клетки в обхвата на ключовите думи, можете да използвате вариацията по-долу:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Забележка: тази версия е формула на масив и трябва да се въведе с control + shift + enter.
