Bendroji formulė
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,A1)))
Santrauka
Norėdami suskaičiuoti konkrečių žodžių ar raktinių žodžių, rodomų tam tikroje langelyje, skaičių, galite naudoti formulę, pagrįstą funkcijomis PAIEŠKA, ISNUMBER ir SUMPRODUCT. Parodytame pavyzdyje formulė C5 yra:
=SUMPRODUCT(--ISNUMBER(SEARCH(keywords,B5)))
kur „raktiniai žodžiai“ yra įvardytas diapazonas E5: E9.
Paaiškinimas
Pastaba: jei raktinis žodis tam tikrame langelyje pasirodo daugiau nei vieną kartą, jis bus skaičiuojamas tik vieną kartą. Kitaip tariant, formulėje skaičiuojami tik skirtingų raktinių žodžių atvejai.
Šios formulės esmė yra „ISNUMBER + SEARCH“ būdas rasti tekstą langelyje, kuris čia išsamiau paaiškintas. Šiuo atveju kiekvienoje langelyje ieškome visų žodžių, nurodytų diapazone „raktiniai žodžiai“ (E5: E9). Mes tai darome perduodami diapazoną į SEARCH kaip argumentą „find_text“. Kadangi mes perduodame 5 elementų masyvą:
("green";"orange";"white";"blue";"pink")
gauname 5 elementų masyvą:
(#VALUE!;#VALUE!;1;#VALUE!;14)
Skaičiai atitinka rungtynes, o #VALUE! klaida reiškia, kad nerasta atitikties. Tokiu atveju, nes mums nesvarbu, kur tekstas rastas langelyje, mes naudojame ISNUMBER, kad masyvą paverstume TRUE ir FALSE reikšmėmis:
(FALSE;FALSE;TRUE;FALSE;TRUE)
Dvigubas neigiamas (-), kad pakeistumėte juos į 1 ir nulius:
(0;0;1;0;1)
Tada funkcija SUMPRODUCT tiesiog grąžina masyvo sumą, šiuo atveju 2.
Tuščių raktinių žodžių tvarkymas
Jei raktinių žodžių diapazone yra tuščių langelių, formulė neveiks teisingai, nes ieškodama tuščios eilutės funkcija „SEARCH“ grąžina nulį. Norėdami filtruoti visas tuščias raktinių žodžių diapazono langelius, galite naudoti toliau pateiktą variantą:
(=SUMPRODUCT(--ISNUMBER(SEARCH(IF(keywords"",keywords),B5))))
Pastaba: ši versija yra masyvo formulė, kurią reikia įvesti su „Control“ + „Shift“ + „Enter“.
