Подстановочные символы в Excel — полное руководство

Что такое подстановочные символы

Подстановочные символы (wildcards) — короткая запись для обозначения неизвестных символов в строках. Это удобно, когда нужно искать, фильтровать или сопоставлять значения без полного соответствия.

Кратко:

• • (звёздочка) — заменяет любое количество символов (включая 0).
• ? (вопрос) — заменяет ровно один символ.
• ~ (тильда) — экранирует следующий символ, превращая подстановочный символ в обычный символ.

Определение: подстановочный символ — символ-маска, который позволяет задавать шаблон для поиска текста.

Где работают подстановочные символы

Подстановочные символы применимы в нескольких местах Excel:

• Фильтры таблиц и автосортировка (поиск в списке фильтра).
• Диалог «Найти и заменить» (Ctrl + F / Ctrl + H).
• Некоторые функции: AVERAGEIF, COUNTIF, SUMIF, VLOOKUP, SEARCH, MATCH и аналогичные. В русском интерфейсе функции называются СРЗНАЧЕСЛИ, СЧЁТЕСЛИ, СУММЕСЛИ, ВПР, ПОИСК, ПОИСКПОЗ соответственно.
• Новые функции (например, XLOOKUP) тоже поддерживают шаблоны в аргументе поиска в большинстве реализаций.

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

Основные правила и примеры

• Пример с : шаблон en найдёт любые строки, оканчивающиеся на «en», например “Women”, “Men” и “Open”.
• Пример с в начале: Ra найдёт “Raid”, “Rabbit”, “Race”.
• mi найдёт любые строки, содержащие последовательность “mi”: “Family”, “Salami” и т.д.
• Пример с ?: S?nd найдёт “Sand” и “Send”. Шаблон ???? — любые 4 символа.
• Пример с ~: чтобы найти в тексте символ , используйте ~. Тогда “Student~“ найдёт строку, содержащую буквальный символ “Student“, а не все строки, начинающиеся с “Student”.

Как фильтровать данные с подстановочными символами

Пошагово (пример: список элементов таблицы Менделеева):

1. Выделите заголовок столбца (A1) и протяните выделение до последней строки с данными (A11).
2. Нажмите Ctrl + Shift + L — появятся стрелки фильтра в заголовках.
3. Нажмите стрелку рядом с заголовком, чтобы открыть меню фильтра.
4. В поле поиска введите *ium.
5. Нажмите Enter — Excel отфильтрует строки, оканчивающиеся на “ium”.

Результат показан ниже.

Чтобы отфильтровать строки с длиной имени ровно 4 символа:

1. Откройте меню фильтра (стрелка рядом с заголовком).
2. В поле поиска введите ???? (четыре вопроса).
3. Нажмите Enter — останутся только названия из 4 букв.

Чтобы сбросить фильтр:

1. Откройте меню фильтра.
2. В списке под полем поиска отметьте Select All (Выделить всё) или снимите галочки фильтра.
3. Нажмите Enter.

Совет: при работе с международными наборами данных имейте в виду пробелы и невидимые символы — используйте TRIM (СЖПРОБЕЛЫ) перед фильтрацией.

Поиск с подстановочными символами через «Найти и заменить»

Диалог Найти (Ctrl + F) поддерживает шаблоны. Пример: найти элементы, начинающиеся и заканчивающиеся на “N”:

1. Нажмите Ctrl + F.
2. В поле “Find what” введите N*N.
3. Нажмите “Find All” — Excel покажет все ячейки, где строка начинается на N и заканчивается на N.

Более точный пример — какие элементы начинаются и заканчиваются на N и имеют 4 буквы:

1. Ctrl + F.
2. Введите N??N.
3. Find All — результат отобразится в списке.

Подсказка: если вы используете локализованную версию Excel, заголовки окна будут на вашем языке, но шаблон вводится одинаково.

Подстановочные символы в формулах — практические примеры

В Excel не все функции принимают шаблоны, но следующие функции поддерживают подстановочные символы:

• AVERAGEIF (СРЗНАЧЕСЛИ)
• COUNTIF (СЧЁТЕСЛИ)
• SUMIF (СУММЕСЛИ)
• VLOOKUP (ВПР)
• SEARCH (ПОИСК)
• MATCH (ПОИСКПОЗ)

Примеры из исходного листа с автомобилями:

1. Посчитать автомобили с двигателями “12” в конце конфигурации (в колонке B):

=COUNTIF(B2:B11, "*12")

Логика: *12 находит любые строки, которые заканчиваются на “12” — например “V12” или “Flat12”.

2. Посчитать автомобили, где в названии встречается “GT”:

=COUNTIF(A2:A11, "*GT*")

Логика: GT найдёт любые строки, содержащие подряд символы “GT”.

3. Посчитать автомобили с четырёхбуквенными моделями (если модель — последние 4 символа):

=COUNTIF(A2:A11, "* ????")

Здесь астриск указывает, что перед моделью может быть любое содержимое (название бренда), а четыре вопроса гарантируют, что модель содержит ровно четыре символа.

Обратите внимание: если модель идёт в отдельном столбце, шаблон можно упростить: =COUNTIF(столбец, “????”).

Дополнительно: в русской версии формулы будут выглядеть так:

=СЧЁТЕСЛИ(B2:B11; "*12")
=СЧЁТЕСЛИ(A2:A11; "*GT*")
=СЧЁТЕСЛИ(A2:A11; "????")

Когда подстановочные символы не подходят (контрпример)

• Поиск по регулярным выражениям. Подстановочные символы — это простые маски; они не заменят регулярные выражения (регексы) для сложного парсинга.
• Чувствительность к регистру. Большинство функций Excel не различают регистр при поиске текста; если вам нужен чувствительный к регистру поиск, придётся использовать дополнительные приёмы (например, вспомогательные столбцы с EXACT/СОВПАД для сравнения).
• Поиск в числах или датах. Подстановочные символы применимы к тексту. Для числовых и датированных полей лучше использовать числовые условия или функции типа YEAR, MONTH, BETWEEN.
• Совпадения, зависящие от разделителей (например, поиск «Ann» должен находить только отдельное слово “Ann”, а не часть слова “Annette”). В таких случаях добавляйте границы слова через пробелы, знаки препинания или используйте вспомогательные формулы.

Альтернативные подходы

• FILTER (ФИЛЬТР) — в Excel 365 и более новых версиях можно применять динамические массивы и функции FILTER вместе с SEARCH/ПОИСК для гибких совпадений.
• REGEX (в Google Sheets либо через надстройки) — если нужен регекс, используйте инструменты, которые поддерживают регулярные выражения.
• Power Query — если требуется сложная очистка и фильтрация данных на этапе загрузки, Power Query позволяет применять шаги с более точными правилами.

Пример использования FILTER + SEARCH:

=FILTER(A2:A100, ISNUMBER(SEARCH("GT", A2:A100)))

Этот подход возвращает все строки, где есть подстрока “GT”.

Ментальные модели и эвристики

• “Звёздочка = «где угодно», вопрос = «ровно один»”. Запомните простую ассоциацию: * = много, ? = один.
• Начинайте с простого шаблона и уточняйте: сначала substring, затем ? для длины.
• Если не получается — подумайте, не скрыта ли нужная информация пробелами или невидимыми символами.
• Экранирование: если хотите найти реальные символы ‘*’ или ‘?’, ставьте перед ними ‘~’.

Чек-листы по ролям

Аналитик:

• Убедиться, что столбцы с текстом очищены от ведущих/завершающих пробелов (TRIM).
• Использовать вспомогательные столбцы для нормализации регистра и формата.
• Протестировать шаблоны на репрезентативной выборке.

Оператор данных (data entry):

• При вводе данных использовать единый формат (например, “V12” а не “V 12”).
• Документировать стандарты, чтобы последующие фильтры работали корректно.

Power user / разработчик отчётов:

• Использовать формулы с шаблонами внутри вычисляемых колонок, чтобы упростить сводные таблицы.
• Рассмотреть замену COUNTIF на динамические FILTER+COUNTA в Excel 365 для более гибкой логики.

Методология: как спроектировать фильтр с подстановочными символами

1. Сформулируйте цель поиска — что именно хотите получить (например, все элементы, содержащие “ium”).
2. Проверьте чистоту данных (TRIM, удаление неалфавитных символов).
3. Подберите минимально возможный шаблон (чтобы не захватить лишнего).
4. Протестируйте шаблон на нескольких примерах.
5. Если результат неверный, уточните шаблон или примените дополнительные условия.

Пошаговое руководство по отладке

• Нет результатов: убедитесь, что диапазон выбран правильно и вы не ищете в числовом столбце.
• Появляются лишние строки: уточняйте шаблон (добавляйте пробелы, границы слова или конкретные префиксы/суффиксы).
• Нужны точные совпадения слова: используйте шаблон со пробелами или объединяйте функции (например, поиск с проверкой начала и конца строки).

Диаграмма принятия решения

flowchart TD
  A[Нужно искать текст?] --> B{Требуется сложный шаблон}
  B -- Да --> C[Использовать Power Query или регекс]
  B -- Нет --> D{Excel 365 или старше}
  D -- 365 --> E[Функция FILTER + SEARCH / XLOOKUP]
  D -- старше --> F[COUNTIF / VLOOKUP / FIND]
  E --> G[Применить шаблон с * и ?]
  F --> G
  C --> H[Подготовить данные и применить регекс-решение]

Советы по производительности

• COUNTIF и SUMIF быстро работают для больших таблиц, но сложные формулы с массивами и SEARCH на сотнях тысяч строк могут замедлить лист.
• При регулярных операциях по фильтрации лучше подготовить предвычисленные колонки с флагами (булевыми значениями) и фильтровать по ним.
• Если данные часто обновляются, рассмотрите Power Query для применения фильтров на этапе загрузки.

Совместимость и советы по миграции

• Подстановочные символы поддерживаются в большинстве версий Excel на Windows и Mac в функциях, перечисленных выше.
• Если вы делитесь книгой с пользователями в разных локалях, учтите локализованные имена функций (например, СЧЁТЕСЛИ вместо COUNTIF). Формулы, записанные в английской версии, при открытии в русской версии автоматически переводятся Excel.
• В файлах CSV подстановочные символы не применимы — это чистый текстовый формат; применяйте маски уже в Excel после импорта.

Краткий глоссарий

• Подстановочный символ — символ-маска для поиска текста.
• Экранирование — способ трактовать подстановочный символ как обычный символ с помощью тильды ~.
• Шаблон — строка с подстановочными символами, которую Excel использует для сопоставления.

Часто встречающиеся ошибки и как их исправлять

• Ошибка: шаблон не ищет требуемые строки. Причина: лишние пробелы или неверный диапазон. Решение: примените TRIM и проверьте диапазон.
• Ошибка: требуется чувствительность к регистру. Решение: используйте дополнительные формулы для сравнения с учётом регистра.
• Ошибка: ожидалось «только слово», а найдено внутри другого слова. Решение: добавьте границы (пробелы, знаки препинания) или используйте дополнительные проверки.

Когда стоит изучать дальше

Если ваши задачи по текстовой очистке и сопоставлению усложняются — изучите Power Query, регулярные выражения (в системах, где они доступны), а также новые динамические функции Excel (FILTER, UNIQUE, XLOOKUP). Они дают более гибкие и масштабируемые подходы по сравнению с простыми шаблонами.

Итог

Подстановочные символы — простой и мощный инструмент для поиска, фильтрации и подсчёта совпадений в Excel. Начните с базовых паттернов (*, ?, ~), попробуйте их в фильтрах и COUNTIF/СЧЁТЕСЛИ, затем переходите к более сложным комбинациям и альтернативам (FILTER, Power Query), если потребуется более точная или производительная логика.

Важное: перед массовыми операциями всегда работайте с копией данных или используйте вспомогательные столбцы — это поможет быстро откатить изменения и избежать потерь.

Ключевые примеры формул (для быстрого копирования):

=COUNTIF(B2:B11, "*12")
=COUNTIF(A2:A11, "*GT*")
=COUNTIF(A2:A11, "????")
=FILTER(A2:A100, ISNUMBER(SEARCH("GT", A2:A100)))

Спасибо за внимание — применяйте шаблоны с осторожностью и тестируйте их на примерах перед использованием в отчётах.