Загальна формула
=COUNTIF(range,"*txt*")
Резюме
Щоб підрахувати комірки, що містять певний текст, ви можете використовувати функцію COUNTIF із символом підстановки. У наведеному прикладі формула в E5 має вигляд:
=COUNTIF(B5:B15,"*a*")
Пояснення
Функція COUNTIF підраховує клітинки в діапазоні, які відповідають критеріям. Наприклад, для підрахунку кількості комірок у діапазоні, що містять "а", ви можете використовувати:
=COUNTIF(range,"a") // exact match
Однак зауважте, що це точний збіг. Для підрахунку клітинка повинна містити точно "a". Якщо комірка містить будь-які інші символи, вона не враховуватиметься.
Для показаної проблеми ми хочемо підрахувати комірки, які містять певний текст, тобто текст може знаходитися де завгодно в комірці. Для цього нам потрібно використовувати символ зірочки (*) як підстановочний знак. Наприклад, для підрахунку клітинок, які містять літеру "а", де б ми не використовували:
=COUNTIF(range,"*a*")
Зірочка у формулі означає "збіг будь-якої кількості символів, включаючи нуль", тому цей шаблон буде враховувати будь-яку клітинку, яка містить "a" у будь-якому положенні, з або без інших символів ліворуч або праворуч. Формули, використані у прикладі, мають однаковий шаблон:
=COUNTIF(B5:B15,"*a*") // contains "a" =COUNTIF(B5:B15,"*2*") // contains "2" =COUNTIF(B5:B15,"*-S*") // contains "-s" =COUNTIF(B5:B15,"*x*") // contains "x"
Примітка: COUNTIF не враховує регістр.
З посиланням на клітинку
Ви можете легко відрегулювати цю формулу, щоб використовувати вміст іншої комірки для критеріїв. Наприклад, якщо A1 містить текст, якому потрібно збігтися, ви можете використовувати:
=COUNTIF(range,"*"&A1&"*")
Підрахуйте конкретні цифри
Наведена вище формула COUNTIF не буде працювати, якщо ви шукаєте певне число, а комірки містять числові дані. Це тому, що підстановочний знак автоматично змушує COUNTIF шукати лише текст (тобто шукати "2", а не лише 2). Оскільки текстове значення ніколи не буде знайдено в істинному числі, COUNTIF поверне нуль.
Щоб підрахувати певне число в числовому вмісті, ви можете використовувати іншу формулу, засновану на функції SEARCH і функції ISNUMBER, наприклад:
=SUMPRODUCT(--(ISNUMBER(SEARCH(text,range))))
У цій формулі текст - це текст, який ви шукаєте, а діапазон - діапазон комірок, які ви хочете порахувати. Коли SEARCH знаходить збіг, він поверне число, а функція ISNUMBER перетворить число у значення TRUE або FALSE. Оскільки ми перевіряємо більше однієї комірки, результатом буде масив або значення TRUE і FALSE. Подвійний негатив використовується для перетворення значень TRUE і FALSE в 1s і 0s, а функція SUMPRODUCT повертає суму масиву як кінцевий результат.
ПОШУК автоматично обробляє всі числа як текст, тому знайде, наприклад, 1 у числі 201. Крім того, немає необхідності використовувати символи підстановки для позначення позиції, оскільки ПОШУК автоматично переглядатиме весь текст у комірці.
Версія з урахуванням регістру
Для підрахунку регістру, який враховує регістр, ви можете використовувати формулу на основі функції FIND:
=SUMPRODUCT(--(ISNUMBER(FIND(text,range))))
Тут використовується функція FIND замість SEARCH, оскільки FIND чутлива до регістру. В іншому формула працює як формула ISNUMBER + FIND, описана вище.
