Подстановочные символы в Excel

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

Подстановочные символы (wildcards) — это специальные символы, которые заменяют один или несколько символов при поиске или фильтрации текста в Excel. Коротко:

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

1‑строчная дефиниция: Подстановочные символы — быстрый способ задать шаблон поиска вместо точного совпадения.

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

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

• “*en” — найдёт любую строку, оканчивающуюся на “en” (например: Women, Men).
• “Ra*” — найдёт строки, начинающиеся с “Ra” (Raid, Rabbit, Race).
• “mi“ — найдёт любые строки, содержащие “mi” (Family, Salami).
• “S?nd” — найдёт Sand и Send (вместо ? может быть любой одиночный символ).
• “????” — любая строка ровно из четырёх символов.
• “Student~“ — найдёт текст “Student“ (тильда экранирует астериск).

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

Пример: у нас список первых 10 химических элементов. Покажем, как отфильтровать элементы, оканчивающиеся на “ium”, и как отобразить только те, у которых четырёхбуквенные названия.

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

Для четырёхбуквенных названий:

1. Откройте меню фильтра у заголовка столбца.
2. В поле поиска введите “????” и нажмите Enter — останутся только строки из четырёх символов.

Чтобы снять фильтр и вернуть все строки:

1. Откройте меню фильтра у заголовка.
2. Снимите/поставьте галочку “Выбрать всё” (Select All) под полем поиска и нажмите Enter.

Примечание: в локализациях Excel подписи в интерфейсе могут меняться (например, “Select All” / “Выбрать всё”), но логика работы поля поиска и подстановок постоянна.

Как искать данные с подстановочными символами (Найти и заменить)

Инструмент “Найти и заменить” поддерживает подстановочные символы и позволяет быстро просканировать лист или книгу.

Пример: найти элементы, которые начинаются и заканчиваются на букву N.

1. Нажмите Ctrl+F, чтобы открыть окно “Найти и заменить”.
2. В поле “Найти” введите “N*N”.
3. Нажмите “Найти все” — вы увидите список всех ячеек, соответствующих шаблону.

Чтобы сузить поиск до элементов, начинающихся и заканчивающихся на N и имеющих ровно 4 символа, используйте “N??N” и нажмите “Найти все”.

Полезно: кнопка “Параметры” в этом окне открывает дополнительные настройки поиска (диапазон, соответствие регистру, поиск формул/значений).

Функции Excel, которые поддерживают подстановочные символы

Не все функции распознают подстановки, но следующие — да:

• AVERAGEIF
• COUNTIF
• SUMIF
• VLOOKUP
• SEARCH
• MATCH

Примеры использования в формулах (сохранены исходные формулы):

Пример 1. Подсчёт ячеек, в которых в столбце B указана конфигурация двигателя с “12” в конце:

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

Пример 2. Подсчёт моделей, содержащих “GT”:

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

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

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

Полезная шпаргалка (Cheat sheet)

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

• Формулы, не перечисленные выше, часто игнорируют подстановки. Если функция явно не поддерживает шаблоны, результаты будут неверны.
• Подстановки не дают контроля над сложными регулярными выражениями (регекс). Для сложных шаблонов потребуется Power Query, VBA или внешняя обработка.
• В числовых или дата-полях подстановочные символы бесполезны: Excel сначала интерпретирует значение как число/дату.
• При больших объёмах данных надёжность и производительность могут быть ниже по сравнению с специализированными инструментами (Power Query, БД).

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

• Power Query: лучше для сложной фильтрации, нормализации и трансформаций — позволяет писать выражения M и применять более сложную логику.
• VBA / макросы: подходят для автоматизации повторяющихся задач с шаблонами и экранированием.
• Регулярные выражения (через VBA или внешние инструменты): когда нужны гибкие паттерны, которые невозможно выразить через *, ?.
• Функции современного Excel (FILTER, XLOOKUP): в сочетании с логическими выражениями дают гибкость, но синтаксис фильтрации отличается.

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

• Если нужен «любой набор символов» — используйте “*”.
• Если нужен «точно один символ» — используйте “?”.
• Если нужно искать сами символы “*” или “?” — поставьте перед ними “~”.
• Для проверки шаблонов вручную сначала попробуйте окно “Найти” — быстрый способ проверить, как маска работает на реальных данных.

Пошаговый план (SOP) для применения подстановок в рабочем листе

1. Определите цель: фильтрация, поиск, подсчёт или замена.
2. Оцените тип данных: текстовые строки vs числа/даты.
3. Подберите шаблон (*, ?, ~) и протестируйте в окне “Найти” (Ctrl+F).
4. Если результат верный — примените фильтр или формулу (например, COUNTIF).
5. Для сложной логики подготовьте Power Query или макрос.
6. Протестируйте на выборке и добавьте критерии приёмки (ниже).

Критерии приёмки

• Результат поиска/фильтрации совпадает с эталонным набором строк.
• Фильтр не исключает допустимые строки и не включает недопустимые.
• Формулы возвращают ожидаемые числовые значения при трёх тестовых наборах (пустые, граничные, типичные).

Тестовые случаи (наборы для проверки)

1. Базовый: строки с явно совпадающими шаблонами (например, “Helium”, “Sodium”).
2. Пограничный: пустые строки, строки с пробелами, строки с экранируемыми символами (*, ?).
3. Негативный: числовые значения и даты в том же столбце — должны быть игнорированы.

Ролевые чек-листы

Аналитик:

• Убедиться, что столбец — текстовый.
• Протестировать шаблон через Ctrl+F.
• Документировать шаблон для повторного использования.

QA:

• Проверить граничные значения и экранирование.
• Сверить результаты с контрольной выборкой.

Data steward:

• Убедиться, что фильтрация не удаляет критичные данные.
• Согласовать шаблоны с политиками наименований.

Дерево решений для быстрого выбора шаблона

flowchart TD
  A[Нужно искать/фильтровать текст?] -->|Нет| B[Используйте числовые/дата фильтры]
  A -->|Да| C[Нужен произвольный фрагмент?]
  C -->|Да| D[Используйте '*fragment*' ]
  C -->|Нет| E[Нужен поиск начала/конца строки?]
  E -->|Начало| F['start*']
  E -->|Конец| G['*end']
  A --> H[Нужно точное число символов?]
  H -->|Да| I[Используйте '?' для каждого символа]
  H -->|Нет| J[Используйте комбинации * и ?]

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

• Подстановки поддерживаются в настольных версиях Excel (Windows и Mac) и в большинстве случаев в Excel Online. Синтаксис подстановок одинаковый, но некоторые новые функции (например, динамические массивы) могут вести себя иначе в разных версиях.
• Если вы переносите логику в Power Query, обратите внимание, что там используются собственные функции для поиска и сравнения текста — нужно переконструировать выражения.

Краткое резюме

Подстановочные символы — лёгкий и быстрый инструмент для поиска, фильтрации и подсчёта по шаблонам в Excel. Для простых задач используйте *, ?, ~; для сложных — комбинируйте с формулами, Power Query или макросами.

Важно: тестируйте шаблоны на реальных данных и документируйте используемые маски, чтобы коллеги могли повторить логику.

Если вы хотите, могу предложить готовые шаблоны для конкретных сценариев (например, очистка каталогов, поиск по номерам деталей, фильтрация почтовых адресов).