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