Как можно использовать подстановочные знаки в Microsoft Excel для уточнения поиска

Подстановочные знаки в Microsoft Excel позволяют искать частичные совпадения, расширять фильтры и создавать формулы, ссылающиеся на ячейки, содержащие определенные строки. Они представляют собой неуказанные символы, помогающие находить текстовые значения с «нечеткими» совпадениями.

Групповые символы: звездочка (*) и вопросительный знак (?)

В Excel есть два подстановочных знака, и знание их назначения имеет решающее значение для понимания того, как подстановочные знаки работают в целом.

Подстановочный знак «звездочка»: любое количество символов

Первый из двух подстановочных знаков в Microsoft Excel — звездочка, которая представляет любое количество символов, включая отсутствие символов.

Например:

• *OK* соответствует любым ячейкам, содержащим «OK», с любым количеством символов до или после (включая отсутствие символов).
• OK* соответствует любым ячейкам, начинающимся с «OK», с любым количеством символов (включая отсутствие символов) после них, но без символов до них.
• *OK соответствует любым ячейкам, заканчивающимся на «OK», с любым количеством символов (включая отсутствие символов) перед ними.

Подстановочный знак вопроса: любой одиночный символ

Вторым подстановочным знаком в Microsoft Excel является вопросительный знак, который заменяет любой отдельный символ.

Например:

• ?OK? соответствует любым ячейкам, которые содержат один символ перед «OK» и один символ после.
• OK? соответствует любым ячейкам, которые содержат один символ после «OK», но ничего до него.
• ?OK соответствует любым ячейкам, которые содержат один символ перед «OK», но не содержат ничего после.

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

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

Например,

• ??OK* соответствует любым ячейкам, которые начинаются с двух символов, а затем содержат «OK», с любым количеством символов в конце (включая отсутствие таковых).
• *OK? соответствует любым ячейкам, которые начинаются с любого количества символов (включая отсутствие таковых), затем содержат «OK» и заканчиваются еще одним символом.
• ?OK* соответствует любым ячейкам, которые начинаются с одного символа, затем содержат «OK» и имеют любое количество символов (включая ни одного) после этого.

Отмена джокеров: Тильда (~)

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

Например:

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

Использование подстановочных знаков в поиске

Одним из наиболее распространенных вариантов использования подстановочных знаков в Microsoft Excel является поиск символов в рабочей книге и, при необходимости, замена их альтернативными вариантами.

Найти строки символов

В этом списке кодов продуктов буквы в начале обозначают место производства продукта (AUS — Австралия, UK — Соединенное Королевство, USA — Соединенные Штаты и CAN — Канада), первая цифра обозначает соответствующий отдел магазина (1 — одежда, 2 — товары для дома, 3 — спортивные товары и 4 — садовые товары), а коды с буквой в конце обозначают только национальную продажу (A) или только международную продажу (B).

Допустим, вы хотите найти все товары для дома, предназначенные только для продажи на национальном уровне.

Нажмите Ctrl+F, чтобы открыть вкладку «Найти» диалогового окна «Найти и заменить», и в поле «Найти» введите:

*2??А

в котором

• Звездочка и цифра 2 сообщают Excel, что нужно искать ячейки, начинающиеся с любого количества символов, за которыми следует цифра 2. Здесь необходимо использовать звездочку, поскольку некоторые страны представлены двумя буквами, а другие — тремя.
• Два вопросительных знака, за которыми следует буква A, сообщают Excel, что остальная часть строки должна состоять из двух символов и буквы A.

Затем, нажав «Найти все», вы увидите список в нижней части диалогового окна «Найти и заменить», в котором будут показаны все результаты, соответствующие этим критериям.

Щелкните любой из результатов в диалоговом окне «Найти и заменить», чтобы перейти к соответствующей ячейке в электронной таблице.

Теперь предположим, что ваша цель — вернуть коды для всех товаров одежды, которые не ограничены только национальными или международными продажами (другими словами, коды, которые содержат страну и трехзначное число, начинающееся с 1, но не заканчивающиеся буквой).

В поле «Найти» введите:

*1??

Однако при нажатии кнопки «Найти все» в результатах поиска появляется товар, заканчивающийся на букву, что говорит о том, что в критериях чего-то не хватает.

На самом деле это произошло потому, что ошибочного кода содержит подстроку, которую вы ввели для запроса. Таким образом, чтобы исключить их и вернуть только те коды, которые полностью сопоставьте свой запрос, нажмите «Параметры», установите флажок «Сопоставить все содержимое ячейки» и снова нажмите «Найти все».

На этот раз отображаются только желаемые результаты.

Найти и заменить строки символов

В этом списке Excel показаны любимые футболисты всех времен респондентов. Однако имя Марадоны было написано тремя разными способами, и вы хотите исправить неправильные.

Сначала нажмите Ctrl+H, чтобы открыть вкладку Replace диалогового окна Find And Replace. Затем в поле Find What введите:

Мар*

поскольку все неправильные варианты написания начинаются с этих трех букв, и это единственное имя в списке, которое начинается с этой последовательности символов.

Затем в поле «Заменить на» введите правильное написание:

Марадона

и нажмите «Заменить все» для подтверждения исправлений.

Теперь все случаи имени этого игрока написаны правильно и единообразно.

Использование подстановочных знаков в формулах

Помимо использования подстановочных знаков в диалоговом окне «Найти и заменить» в Excel, вы можете использовать их в аргументах различных функций, таких как XLOOKUP для создания частичного совпадения, COUNTIFS для подсчета количества ячеек, соответствующих нескольким критериям, и многих других.

В этом примере вы хотите использовать функцию СУММЕСЛИ для расчета общей стоимости товаров, произведенных в Великобритании.

Один из способов сделать это — ввести:

=СУММЕСЛИ(A2:A18;»UK*;B2:B18)

в пустую ячейку, где

• SUMIF складывает значения на основе заданных критериев,
• A2: A18 — это диапазон, содержащий значения, подлежащие тестированию,
• «Великобритания*» сообщает Excel, что нужно найти все ячейки в диапазоне от A2 до A18, начинающиеся с «UK», за которыми следует любое количество символов, и
• B2: B18 — это массив для суммирования.

Подстановочные знаки и текстовые значения в формулах Excel всегда должны быть заключены в кавычки.

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

Вместо этого введите критерии (в данном случае «UK») в отдельной ячейке. Затем укажите ссылку на эту ячейку в формуле, используя символ амперсанда (&) для отделения ссылки на ячейку от подстановочного знака, заключенного в кавычки:

=СУММЕСЛИ(A2:A18,Д2&»*»,B2:B18)

Теперь, когда вы меняете код страны, общая сумма обновляется.

Используйте инструмент проверки данных Excel для создания раскрывающегося списка стран.

Использование подстановочных знаков в фильтрах

Еще одно применение подстановочных знаков в Microsoft Excel — фильтры.

Независимо от того, работаете ли вы с отформатированной таблицей Excel или неотформатированным диапазоном, выберите любую ячейку в массиве и нажмите Ctrl+Shift+L, чтобы добавить кнопки фильтра.

Теперь предположим, что вы хотите отфильтровать коды продуктов, включив только те, которые содержат букву A на конце.

Для этого нажмите кнопку фильтра Product_Code и в поле поиска введите:

*A

для включения всех кодов, заканчивающихся на букву A. Поскольку подстановочный знак звездочки помещается перед буквой A в поиске, он не выберет никакие коды продуктов, содержащие A в начале (например, AUS458) или в середине (например, USA320B).

После предварительного просмотра результатов во всплывающем окне фильтра нажмите «ОК» и увидите, как ваши данные будут уточнены в соответствии с вашими критериями.

Очки для заметок

Прежде чем приступить к использованию подстановочных знаков в таблице Excel, уделите немного времени прочтению следующих заключительных указаний:

• При использовании подстановочных знаков в формулах вы столкнетесь с препятствием, если попытаетесь найти цифру в диапазоне чисел. Это связано с тем, что подстановочные знаки автоматически преобразуют числа в текст. Однако поиск цифр в диапазоне чисел с помощью инструмента Excel «Найти и заменить» или фильтров будет работать нормально, как и поиск чисел в ячейках, которые также содержат текстовые значения.
• Хотя многие функции поддерживают использование подстановочных знаков, некоторые, включая функцию IF, этого не делают.
• Добавление подстановочных знаков в формулы делает их более сложными, потенциально увеличивая риск ошибок, если вы не будете особенно осторожны. Всегда помните о необходимости вставлять подстановочные знаки в кавычки в формулах!

Помимо использования подстановочных знаков для расширения возможностей поиска в Excel, вы также можете использовать их для поиска и замены текста в Microsoft Word. Главное различие между ними заключается в том, что наряду со звездочкой и вопросительным знаком Word также позволяет использовать квадратные скобки ([ ]) в качестве подстановочных знаков для соответствия более чем одному элементу.