Загальна формула
=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 підсумовує всі елементи масиву, щоб отримати загальну кількість випадків підрядка в діапазоні комірок.
Ігнорування справи
ЗАМІНА - це чутлива до регістру функція, тому вона буде відповідати регістру при запуску заміни. Якщо вам потрібно підрахувати як вживання великих, так і малих літер слова чи підрядка, використовуйте функцію UPPER всередині SUBSTITUTE, щоб перетворити текст у верхній регістр перед виконанням заміни:
=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE((UPPER(rng)),UPPER(txt),"")))/LEN(txt))
