Подсчёт определённых слов в диапазоне ячеек Excel

В этой статье рассказывается, как с помощью формулы подсчитать количество вхождений конкретного слова в заданном диапазоне ячеек Excel.

Как подсчитать количество определённых слов в заданном диапазоне ячеек Excel?

Универсальная формула

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))

Аргументы

Как использовать эту формулу?

Допустим, вы хотите подсчитать, сколько раз встречается слово «tool» в диапазоне B5:B8. Выполните следующие действия.

Выберите пустую ячейку, введите в неё приведённую ниже формулу и нажмите клавишу Enter.

Советы:Вы можете сразу скопировать и вставить формулу в выбранную ячейку.

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

Примечания:

• 1. Пожалуйста, замените диапазон B5:B8 и слово «tool» на нужные вам значения.
• 2. Если слово не найдено, формула вернёт 0;
• 3. Формула учитывает регистр символов;
• 4. Чтобы подсчитать конкретное слово в диапазоне ячеек без учёта регистра, используйте функцию UPPER внутри SUBSTITUTE, как показано в следующей формуле:
• =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(UPPER(B5:B8),UPPER("tool"),"")))/LEN("tool"))
• 

Как работает эта формула?

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

• 1. LEN(B5:B8): функция LEN вычисляет общее количество Фильтр по длине строки символов в каждой ячейке диапазона B5:B8 и возвращает массив чисел {47;23;23;15};
• 2. LEN(SUBSTITUTE(B5:B8,"tool","")): сначала функция SUBSTITUTE удаляет слово «tool» из каждой ячейки диапазона B5:B8, а затем функция LEN определяет длину получившегося текста в каждой ячейке. В результате возвращается массив чисел {35;19;19;15};
• 3. (LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")): теперь эта часть формулы преобразуется в {47;23;23;15}–{35;19;19;15}. После вычитания получаем результат {12;4;4;0};
• 4. LEN("tool"): здесь функция LEN вычисляет длину слова «tool» и возвращает результат 4;
• 5. LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"): как описано выше, эта часть преобразуется в {12;4;4;0}/4. Деление каждого элемента массива на 4 даёт результат {3;1;1;0};
• 6. SUMPRODUCT({3;1;1;0}): функция SUMPRODUCT суммирует числа в массиве и возвращает общее количество вхождений слова «tool» в диапазоне B5:B8.

Связанные функции

Связанные формулы