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

В этой статье объясняется формула для подсчета количества раз, когда определенное слово появляется в диапазоне ячеек в Excel.

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

Общая формула

=СУММПРОИЗВ((ДЛСТР(диап)-ДЛСТР(ПОДСТАВИТЬ(диап,текст,"")))/ДЛСТР(текст))

Аргументы

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

Предположим, вы хотите подсчитать количество слова «tool» в диапазоне B5:B8, вы можете сделать это следующим образом.

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

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

=СУММПРОИЗВ((ДЛСТР(B5:B8)-ДЛСТР(ПОДСТАВИТЬ(B5:B8,"tool","")))/ДЛСТР("tool"))

Примечания:

• 1. Пожалуйста, измените диапазон B5:B8 и слово «tool» в соответствии с вашими потребностями;
• 2. Если слово не найдено, результат будет 0;
• 3. Формула чувствительна к регистру;
• 4. Если вы хотите подсчитать определенное слово в диапазоне ячеек, игнорируя регистр, используйте функцию ПРОПИСН внутри функции ПОДСТАВИТЬ, как показано в формуле ниже:
• =СУММПРОИЗВ((ДЛСТР(B5:B8)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(B5:B8),ПРОПИСН("tool"),"")))/ДЛСТР("tool"))
• 

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

=СУММПРОИЗВ((ДЛСТР(B5:B8)-ДЛСТР(ПОДСТАВИТЬ(B5:B8,"tool","")))/ДЛСТР("tool"))

• 1. ДЛСТР(B5:B8): Функция ДЛСТР вычисляет общую длину строки для каждой ячейки в диапазоне B5:B8, она вернет массив чисел {47;23;23;15};
• 2. ДЛСТР(ПОДСТАВИТЬ(B5:B8,"tool","")): Здесь функция ПОДСТАВИТЬ удаляет конкретное слово «tool» из каждой ячейки в диапазоне B5:B8, а затем функция ДЛСТР вычисляет длину для каждой ячейки в диапазоне B5:B8 без конкретного слова «tool». Она возвращает массив чисел как {35;19;19;15};
• 3. (ДЛСТР(B5:B8)-ДЛСТР(ПОДСТАВИТЬ(B5:B8,"tool","")): Теперь эта часть формулы превращается в {47;23;23;15}-{35;19;19;15}. После вычитания результат равен {12;4;4;0};
• 4. ДЛСТР("tool"): Функция ДЛСТР здесь вычисляет длину для «tool» и возвращает результат 4;
• 5. (ДЛСТР(B5:B8)-ДЛСТР(ПОДСТАВИТЬ(B5:B8,"tool","")))/ДЛСТР("tool"): Как упоминалось выше, эта часть будет отображаться как {12;4;4;0}/4. Разделив каждое число массива на 4, получаем результат {3;1;1;0};
• 6. СУММПРОИЗВ({3;1;1;0}): Функция СУММПРОИЗВ суммирует числа в массиве и возвращает общее количество «tool» в диапазоне B5:B8.

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

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