Підрахуйте конкретні слова в діапазоні в Excel

У цій статті пояснюється формула для підрахунку кількості випадків, коли певне слово з’являється в діапазоні комірок у Excel.

Як підрахувати конкретні слова в діапазоні комірок у Excel?

Загальна формула

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

Аргументи

Як користуватися цією формулою?

Припустимо, ви хочете порахувати кількість слова "інструмент" у діапазоні B5: B8, ви можете отримати його наступним чином.

Виділіть порожню клітинку, введіть в неї формулу нижче, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ

Порада: Ви можете безпосередньо скопіювати та вставити формулу у вибрану комірку.

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

Примітки:

• 1. Будь ласка, змініть діапазон B5: B8 та слово «інструмент» відповідно до власних потреб;
• 2. Якщо слова не знайдено, воно поверне 0;
• 3. Формула враховує регістр;
• 4. Якщо ви хочете порахувати конкретне слово в діапазоні комірок і проігнорувати регістр, будь ласка, використовуйте функцію ВЕРХНЯ всередині ЗАМІНИ, як показано нижче:
• =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","")): Тут функція ЗАМІНИ видаляє конкретне слово «інструмент» з кожної комірки в діапазоні B5: B8, а потім функція LEN обчислює довжину для кожної комірки в діапазоні B5: B8 без конкретного слова «інструмент». Він повертає номери масивів як {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 тут обчислює довжину для «інструменту» і повертає результат 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 підсумовує числа в масиві та повертає загальну кількість "інструменту" в діапазоні B5: B8.

Супутні функції

Пов’язані формули