Как найти и посчитать данные, если ячейка содержит определённый текст
Чтобы проверить, содержит ли ячейка нужный текст или число, и автоматически посчитать такие случаи, используйте функцию СЧЁТЕСЛИ с символом звёздочки *. Базовая формула выглядит так: =СЧЁТЕСЛИ(диапазон; "*текст*"). Звёздочки означают, что искомое слово может находиться в любом месте строки (в начале, середине или конце). Это решение работает во всех версиях Excel, включая 365 и онлайн-версию.
Принцип работы подстановочных знаков
Главный секрет условия «содержит» — использование подстановочных знаков (wildcards). Excel понимает два основных символа для поиска по маске:
- Звёздочка (
*) — заменяет любое количество любых символов."*Москва*"— найдёт «Москва», «Заказ Москва», «Москва-Сити»."А*"— найдёт всё, что начинается на «А»."*ок"— найдёт всё, что заканчивается на «ок».
- Вопросительный знак (
?) — заменяет ровно один любой символ."К?т"— найдёт «Кот», «Кит», но не «Крот».
Регистр букв не имеет значения. Формула "*москва*" найдёт ячейки со словами «МОСКВА», «Москва» и «москва» одинаково успешно.
Синтаксис основных функций для поиска
Для решения задач анализа данных чаще всего используются две функции: одна для подсчёта количества, другая для суммирования значений.
1. Подсчёт количества совпадений (СЧЁТЕСЛИ)
Используется, когда нужно узнать, сколько раз встречается определённое значение.
Формула:
=СЧЁТЕСЛИ(A2:A100; "*слово*")
A2:A100— диапазон ячеек, где производится поиск."*слово*"— критерий поиска. Кавычки обязательны, если критерий содержит текст или знаки операций.
2. Суммирование по условию (СУММЕСЛИ)
Используется, когда нужно сложить числа из соседнего столбца, соответствующие найденному тексту.
Формула:
=СУММЕСЛИ(A2:A100; "*слово*"; B2:B100)
A2:A100— диапазон, где ищем текст."*слово*"— условие поиска.B2:B100— диапазон ячеек с числами, которые нужно просуммировать.
Практический пример: анализ продаж по городам
Представим таблицу заказов, где в столбце A указаны города отправки, а в столбце B — суммы заказов.
| A (Город) | B (Сумма) | |
|---|---|---|
| 1 | Москва | 5 000 |
| 2 | Санкт-Петербург | 3 000 |
| 3 | Заказ доставлен в Москву | 2 500 |
| 4 | Новосибирск | 4 000 |
Задача 1: Посчитать количество заказов, связанных с Москвой. Даже если в ячейке написано просто «Москва» или длинная фраза «Заказ доставлен в Москву», нам нужны все варианты. Формула в любой свободной ячейке:
=СЧЁТЕСЛИ(A2:A4; "*Москва*")
Результат: 2 (ячейки A1 и A3). Без звёздочек формула нашла бы только точное совпадение (ячейку A1).
Задача 2: Узнать общую сумму заказов по Москве. Используем функцию суммирования с тем же условием. Формула:
=СУММЕСЛИ(A2:A4; "*Москва*"; B2:B4)
Результат: 7 500 (5000 + 2500).
Если критерий поиска записан в отдельной ячейке (например, E1 содержит слово «Москва»), формулу можно записать так: =СЧЁТЕСЛИ(A2:A100; "*"&E1&"*"). Знак & склеивает звёздочки со значением из ячейки.
Работа с исключениями и сложными условиями
Иногда требуется посчитать всё, кроме определённого значения, или использовать несколько условий одновременно.
Исключение текста (НЕ содержит)
Чтобы посчитать ячейки, которые не содержат определённое слово, используйте оператор «не равно» <> вместе со звёздочками.
=СЧЁТЕСЛИ(A2:A100; "<>*Отмена*")
Эта формула подсчитает все строки, где нет слова «Отмена».
Несколько условий (ИЛИ)
Функция СЧЁТЕСЛИ поддерживает только одно условие. Чтобы посчитать сумму строк, содержащих «Москва» ИЛИ «Подольск», нужно сложить результаты двух функций:
=СЧЁТЕСЛИ(A2:A100; "*Москва*") + СЧЁТЕСЛИ(A2:A100; "*Подольск*")
Несколько условий (И)
Если нужно, чтобы ячейка содержала сразу два слова (например, «Москва» И «Опт»), используется функция СЧЁТЕСЛИМН:
=СЧЁТЕСЛИМН(A2:A100; "*Москва*"; A2:A100; "*Опт*")
Частые ошибки и способы их устранения
При работе с текстовыми условиями новички часто сталкиваются с типичными проблемами.
Ошибка 1: Забыты кавычки.
Неправильно: =СЧЁТЕСЛИ(A1:A10; *текст*)
Правильно: =СЧЁТЕСЛИ(A1:A10; "*текст*")
Текстовые критерии всегда должны быть в двойных кавычках.
Ошибка 2: Лишние пробелы.
Если в ячейке написано " Москва " (с пробелами), а вы ищете "Москва", формула сработает. Но если вы ищете точное совпадение без звёздочек, пробелы помешают. Используйте функцию СЖПРОБЕЛЫ (TRIM) для очистки данных перед анализом.
Ошибка 3: Поиск самого знака звёздочки.
Если вам нужно найти именно символ * или ? в тексте, поставьте перед ним тильду ~.
Пример: "~*" найдёт ячейки, содержащие реальную звёздочку.
Сравнение методов поиска данных
| Метод | Когда использовать | Преимущества |
|---|---|---|
| СЧЁТЕСЛИ / СУММЕСЛИ | Для автоматических отчётов и сводных данных | Результат обновляется при изменении данных, легко копируется |
| Фильтр | Для разового визуального просмотра | Не требует написания формул, наглядно скрывает лишнее |
| Условное форматирование | Для визуального выделения цветом | Позволяет быстро увидеть нужные строки в большой таблице |
| Power Query | Для обработки миллионов строк | Работает быстрее формул на огромных массивах данных |
Ответы на популярные вопросы (FAQ)
Можно ли использовать условие «содержит» для чисел?
Да. Например, чтобы найти числа, содержащие цифру 5 (например, 15, 50, 5), можно использовать текст: =СЧЁТЕСЛИ(A1:A100; "*5*"). Однако для числовых диапазонов (больше/меньше) лучше использовать операторы: ">100".
Работает ли это в Google Таблицах?
Да, синтаксис функций COUNTIF (СЧЁТЕСЛИ) и SUMIF (СУММЕСЛИ) в Google Таблицах полностью идентичен Excel, включая поддержку звёздочек.
Как сделать поиск чувствительным к регистру?
Стандартные функции СЧЁТЕСЛИ игнорируют регистр. Для чувствительного поиска потребуется комбинация функций СУММПРОИЗВ и НАЙТИ, либо использование макросов VBA, но в 95% случаев стандартного поиска достаточно.