В Excel нам часто приходится искать и фильтровать данные, содержащие определенные символы или шаблоны. И тут на помощь приходят подстановочные знаки!
Эти маленькие, но мощные инструменты позволяют значительно упростить поиск нужной информации в больших таблицах.
Преимущество использования подстановочных знаков в Excel проявляется при поиске и использовании любых текстовых функций. Когда мы не уверены в точности данных или знаем только частичный элемент данных, подстановочные знаки становятся гибкими и универсальными помощниками.
Что такое подстановочные знаки?
Подстановочные знаки – это специальные символы, которые используются для замены одного или нескольких символов при поиске или фильтрации данных.
В Excel есть три типа подстановочных знаков - это звёздочка (*), вопросительный знак (?) и тильда (~).
- Звёздочка (*) заменяет любое количество символов (включая ноль).
- Знак вопроса (?) заменяет один любой символ.
- Тильда (~) используется для удаления подстановочных символов, таких как звёздочка (*) и вопросительный знак (?) в Excel. Иными словами используется для поиска самих символов * и ?, чтобы Excel не воспринимал их как подстановочные знаки.
Как работают подстановочные знаки
Звездочка (*): это один из самых распространённых и часто используемых подстановочных символов в Excel.
- Ли* : соответствует словам, которые начинаются с букв «Ли» и заканчиваются любым количеством символов. Например, Литература, Лиса, Лимит.
- *Ли : соответствует словам, которые заканчиваются на «Ли» и начинаются с любого количества символов. Например, Модели, Ясли, Брокколи.
- *Ли* : соответствует словам, содержащим буквы «Ли». Начало и конец слов не имеют значения. Например, Магнолия, Малина.
Вопросительный знак (?): этот подстановочный знак обозначает любой отдельный символ в слове.
- Г?д : соответствует словам, которые состоят из трех букв, начинаются на «Г» и заканчиваются на «д». Например, Год и Гид.
- Г????: соответствует всем словам, которые начинаются на «Г» и содержат 5 букв.
Тильда (~): этот подстановочный знак используется для поиска подстановочных символов в тексте.
Поиск и замена с использованием подстановочного знака (?)
Предположим, мы попросили пользователей указать своего любимого блогера и многие из них указали одного и того же, но написание имени этого блогера отличается лишними знаками внутри ника:
В итоге мы получили 4-х разных блогеров, хотя имелся ввиду один. После имени "Дмитрий" должен стоять пробел, но вместо него стоят разные символы. Давайте воспользуемся подстановочным знаком (?) и заменим их пробелами:
- Выберите диапазон данных в ячейках A1:A4.
- Нажмите сочетание клавиш Ctrl + F, чтобы открыть окно «Найти и заменить».
- В диалоговом окне Найти: введите слово «Дмитрий?Масленников».
- Нажмите Найти все.
На экране отобразятся все ячейки, содержащие слово с указанным шаблоном.
Теперь нам нужно удалить ненужные символы между словами «Дмитрий» и «Масленников» и заменить их пробелом.
5. Нажмите на вкладку Заменить в том же окне Найти и заменить.
6. В поле Заменить на: введите слово Дмитрий Масленников. Это слово, которое нам нужно заменить.
Excel выполнил 4 замены, и теперь у нас есть общее значение «Дмитрий Масленников» во всех ячейках.
Аналогичным образом мы можем использовать подстановочный знак в функциях для замены значений в тексте.
Условное форматирование с использованием подстановочных символов
Подстановочные знаки можно использовать и в условном форматировании. Например, у нас есть список городов в Excel:
Из приведённого выше списка городов нам нужно выделить те, которые начинаются на букву «В».
- Выделите диапазон ячеек от A1 до A8.
- Перейдите на вкладку Главная и в разделе Условное форматирование нажмите Создать правило:
3. Выберите Использовать формулу для определения форматируемых ячеек в окне «Создание правила форматирования».
4. Введите следующую формулу в поле для формулы:
=ЕСЛИ(СЧЁТЕСЛИ(A1;"В*");ИСТИНА;ЛОЖЬ)
Функция СЧЁТЕСЛИ проверит, начинается ли значение в ячейке с буквы «В», а подстановочный символ «звёздочка» (*) будет соответствовать оставшемуся количеству символов.
Фильтрация значений с использованием подстановочных знаков
Подстановочные знаки можно использовать и для фильтрации данных. В предыдущем примере попробуем найти все города, в названиях которых есть буквы на «ро».
- Выделите первую ячейку Город и вставьте фильтр:
2. Нажмите на значок раскрывающегося списка в столбце Город, чтобы открыть список фильтров.
3. В поле поиска введите критерий фильтрации «*ро*».
4. Нажмите ОК, и перед вами появится список нужных городов (в нашем случае он один):
Аналогичным образом давайте применим подстановочный знак (?) в строке поиска.
На примере ниже мы задали критерий в виде 6 вопросительных знаков, а в ответ получили все записи, содержащие 6 букв:
Иной случай когда у нас, предположим, в списке городов есть города, отмеченные знаком *. Если при фильтрации в поисковой строке ввести только его - фильтр не справится с поиском, так как будет считывать его не так, как нам нужно:
Но стоит только добавить знак Тильда и поиск заработает:
Использование подстановочных знаков в формулах на примере ВПР
Следует учитывать, что не все функции поддерживают подстановочные знаки. К функциям, поддерживающим подстановочные знаки, относятся ВПР, ГПР, ЕСЛИ, СРЗНАЧЕСЛИ, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИ и СУММЕСЛИМН.
Предположим, у нас есть список клиентов и город, в котором они проживают. Нам необходимо найти город, в котором проживает клиент Иван, зная только его имя:
Для частичного совпадения мы будем использовать подстановочный знак (*).
=ВПР("*"&D2&"*";A1:B6;2;ЛОЖЬ)
Логическая конструкция “*”&D2&“*” будет искать слово «Иван» с любым количеством символов с обеих сторон.
Таким образом, мы можем использовать подстановочные знаки в формулах Excel.
Подписывайтесь, чтобы первыми получать самые интересные публикации! Ещё больше полезных советов и лайфхаков Excel – в моем Telegram-канале!
Для удобства - статья навигатор по каналу: Путеводитель по каналу