Загальна формула
=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)
Резюме
Якщо вам потрібно підрахувати, скільки разів конкретне слово (або будь-яка підрядок) з’являється всередині комірки, ви можете скористатися формулою, яка використовує ЗАМІННИК і ЛІН. У загальній формі формули, наведеної вище, "текст" представляє клітинку, яка містить текст, а "слово" - слово або підрядок, що враховується. У прикладі ми використовуємо таку формулу:
=(LEN(B4)-LEN(SUBSTITUTE(B4,C4,"")))/LEN(C4)
Пояснення
B4 - це клітинка, в якій ми підраховуємо слова, а C4 містить підрядок (слово або будь-яку підрядок), яку ви підраховуєте.
SUBSTITUTE видаляє підрядок з оригінального тексту, а LEN обчислює довжину тексту без підрядка. Потім це число віднімається від довжини оригінального тексту. Результат - кількість символів, видалених ЗАМІННИКОМ.
Нарешті, кількість видалених символів ділиться на довжину підрядка. Отже, якщо підрядок має 5 символів, а після видалення з оригінального тексту не вистачає 10 символів, ми знаємо, що підрядок з’явився двічі в оригінальному тексті.
Випадок обробки
ЗАМІНА - це чутлива до регістру функція, тому вона буде відповідати регістру при запуску заміни. Якщо вам потрібно підрахувати як вживання великих, так і малих літер слова чи підрядка, використовуйте функцію UPPER всередині SUBSTITUTE, щоб перетворити текст у верхній регістр перед виконанням заміни:
=(LEN(B4)-LEN(SUBSTITUTE(UPPER(B4),UPPER(C4),"")))/LEN(C4)
Оскільки ця формула перетворює підрядок і текст у верхній регістр перед виконанням заміни, вона в будь-якому випадку однаково добре працюватиме з текстом.
Проблеми з обробкою
Підрахунок слів у Excel досить складно, оскільки Excel не підтримує регулярні вирази. Як результат, важко націлити слова, які ви хочете точно порахувати, ігноруючи підрядки та інші часткові збіги (тобто знайти "лисиця", але не "лисиця"). Знаки пунктуації та регістру роблять цю проблему досить складною.
Одним з обхідних шляхів є використання іншої формули в допоміжному стовпці для "нормалізації тексту" як першого кроку. Потім за допомогою формули на цій сторінці підрахуйте слова, загорнуті в пробіли, щоб отримати точний підрахунок (тобто ви можете шукати "лисиця" в нормалізованому тексті.
Примітка: цей підхід настільки хороший, як нормалізований текст, який ви можете створити, і, можливо, вам доведеться багато разів коригувати нормувальну формулу, щоб отримати потрібний результат.
