Обща формула
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Обобщение
Ако трябва да преброите колко пъти дадена дума (или който и да е подниз) се появява в клетка, можете да използвате формула, която използва ЗАМЕСТИТЕЛ и ЛЕН. В общата форма на формулата по-горе „текст“ представлява клетка, която съдържа текст, а „дума“ представлява думата или подниза, които се броят. В примера използваме тази формула:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Обяснение
B4 е клетката, в която броим думи, а C4 съдържа подниза (дума или произволен подниз), който броите.
SUBSTITUTE премахва подниза от оригиналния текст и LEN изчислява дължината на текста без подниза. След това това число се изважда от дължината на оригиналния текст. Резултатът е броят на знаците, които са били премахнати от ЗАМЕСТИТЕЛ.
И накрая, броят на премахнатите символи се разделя на дължината на подниза. Така че, ако поднизът е с дължина 5 знака и липсват 10 знака, след като е бил премахнат от оригиналния текст, знаем, че поднизът се е появил два пъти в оригиналния текст.
Калъф за боравене
SUBSTITUTE е чувствителна към главни и малки букви функция, така че ще съответства на регистъра при изпълнение на заместване. Ако трябва да преброите както малки, така и малки букви на думата или подниза, използвайте функцията UPPER в SUBSTITUTE, за да конвертирате текста в главни букви преди да изпълните заместването:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Тъй като тази формула преобразува подниза и текста в главни букви, преди да извърши заместването, тя ще работи еднакво добре с текст във всеки случай.
Работа с проблеми
Преброяването на думи в Excel е сложно, защото Excel не поддържа регулярни изрази. В резултат на това е трудно да се насочите към думите, които искате да преброите точно, като същевременно игнорирате поднизовете и други частични съвпадения (т.е. намерете „лисица“, но не и „лисици“). Пунктуацията и вариациите на регистъра правят този проблем доста предизвикателен.
Едно решение е да се използва друга формула в помощна колона за "нормализиране на текста" като първа стъпка. След това използвайте формулата на тази страница, за да преброите думи, увити в интервали, за да получите точен брой (т.е. можете да потърсите „лисица“ в нормализирания текст.
Забележка: този подход е толкова добър, колкото нормализираният текст, който можете да създадете, и може да се наложи да коригирате нормализиращата формула много пъти, за да получите резултата, от който се нуждаете.
