Обща формула
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))
Обобщение
За да преброите колко пъти дадена дума (или който и да е подниз) се появява в диапазон от клетки, можете да използвате формула, базирана на функциите ЗАМЕСТИТЕЛ, ЛЕН и СУМПРОДУКТ. В показания пример формулата в C11 е:
=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,C2,"")))/LEN(C2))
Забележка: Формулата на тази страница отчита обстоятелствата на една дума в диапазон. Например, ако клетка съдържа два екземпляра на дума, това ще допринесе 2 за общия брой. Ако просто искате да преброите клетки, които съдържат конкретна дума, вижте тази проста формула, базирана на функцията COUNTIF.
Обяснение
В общата версия на формулата rng представлява диапазона, който трябва да се провери, а txt е думата или поднизът, който трябва да се брои.
В показания пример B5: B8 е обхватът, който трябва да се провери, а C2 съдържа текста (дума или подниз) за преброяване.
За всяка клетка в диапазона SUBSTITUTE премахва подниза от оригиналния текст и LEN изчислява дължината на текста без подниза. След това това число се изважда от дължината на оригиналния текст. Резултатът е броят на знаците, които са били премахнати от ЗАМЕСТИТЕЛ.
След това броят на премахнатите символи се разделя на дължината на подниза. Така че, ако поднизът или думата са с дължина 5 знака и липсват 10 знака, след като са били премахнати от оригиналния текст, знаем, че поднизът / думата се е появил два пъти в оригиналния текст.
Тъй като горното изчисление е увито във функцията SUMPRODUCT, резултатът е масив, който съдържа число за всяка клетка в диапазона. Тези числа представляват броя на повторенията на подниза във всяка клетка. За този пример масивът изглежда така: (1; 1; 0; 1)
И накрая, SUMPRODUCT сумира всички елементи в масива, за да получи общите случаи на подниз в диапазона от клетки.
Пренебрегване на случая
SUBSTITUTE е чувствителна към главни и малки букви функция, така че ще съответства на регистъра при изпълнение на заместване. Ако трябва да преброите както малки, така и малки букви на думата или подниза, използвайте функцията UPPER в SUBSTITUTE, за да конвертирате текста в главни букви преди да изпълните заместването:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
