Генеричка формула
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Резиме
Да бисте избројали колико се пута одређена реч (или било који подниз) појављује унутар низа ћелија, можете да користите формулу засновану на функцијама ЗАМЕНА, ЛЕН и СУМПРОДУЦТ. У приказаном примеру, формула у Ц11 је:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Напомена: Формула на овој страници броји речи у опсегу. На пример, ако ћелија садржи две инстанце речи, то ће допринети 2 укупном броју. Ако само желите да пребројите ћелије које садрже одређену реч, погледајте ову једноставну формулу засновану на функцији ЦОУНТИФ.
Објашњење
У генеричког верзији формуле, РНГ представља опсег да проверим, и ТКСТ је реч или субстринг да бројим.
У приказаном примеру, Б5: Б8 је опсег за проверу, а Ц2 садржи текст (реч или подниз) за бројање.
За сваку ћелију у опсегу, СУБСТИТУТЕ уклања подниз из оригиналног текста и ЛЕН израчунава дужину текста без подниза. Затим се овај број одузима од дужине оригиналног текста. Резултат је број знакова које је СУБСТИТУТЕ уклонио.
Затим се број уклоњених знакова дели са дужином подниза. Дакле, ако је подниз или реч дугачак 5 знакова, а недостаје 10 знакова након што је уклоњен из оригиналног текста, знамо да се подниз / реч појавила два пута у оригиналном тексту.
Будући да је горњи прорачун умотан у функцију СУМПРОДУЦТ, резултат је низ који садржи број за сваку ћелију у опсегу. Ови бројеви представљају број појављивања подниза у свакој ћелији. У овом примеру низ изгледа овако: (1; 1; 0; 1)
Коначно, СУМПРОДУЦТ сумира све ставке у низу да би се добило укупно појављивање подниза у опсегу ћелија.
Игнорисање случаја
ЗАМЕНА је функција осетљива на велика и мала слова, тако да ће се подударати са великим и малим словима приликом извођења замене. Ако требате да избројите и велика и мала слова речи или подниза, користите УППЕР функцију унутар СУБСТИТУТЕ да бисте претворили текст у велика слова пре него што извршите замену:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
