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

Быстрые ссылки

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

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

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

Знак звездочки: любое количество символов

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

Например:

Значение для проверки

Критерий: *OK*

Критерий: OK*

Критерий: *OK

OK

Совпадение

Совпадение

Совпадение

Оклахома

Совпадение

Совпадение

Нет совпадения

Шук

Совпадение

Нет совпадения

Совпадение

Смотреть

Совпадение

Нет совпадения

Нет совпадения

Смотрим

Совпадение

Нет совпадения

Нет совпадения

Вопросительный знак: любой один символ

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

Например:

Значение для проверки

Критерий: ?OK?

Критерий: OK*

Критерий: ?OK

Шутка

Совпадение

Нет совпадения

Нет совпадения

Ока

Нет совпадения

Совпадение

Нет совпадения

Вок

Нет совпадения

Нет совпадения

Совпадение

Смешивание вопросительного знака и звездочки

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

Например:

Значение для проверки

Критерий: ??OK*

Критерий: *OK?

Критерий: ?OK*

Взял

Совпадение

Нет совпадения

Нет совпадения

Книги

Совпадение

Совпадение

Нет совпадения

Страшный

Нет совпадения

Совпадение

Нет совпадения

Жуткий

Нет совпадения

Совпадение

Совпадение

Шутки

Нет совпадения

Нет совпадения

Совпадение

Отмена подстановочных знаков: тильда (~)

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

Например:

Значение для проверки

Критерий: *~?

Критерий: *~?*

Критерий: *~*?

Ужин?

Совпадение

Совпадение

Нет совпадения

???к

Нет совпадения

Совпадение

Нет совпадения

D?*y

Нет совпадения

Совпадение

Совпадение

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

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

Как найти и заменить текст и числа в Excel

Инструмент «Найти и Заменить» — это мощная и часто недооценённая функция Excel.

Поиск строк текста

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

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

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

где

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

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

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

В поле поиска введите:

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

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

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

Поиск и замена строк текста

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

Сначала нажмите Ctrl H, чтобы открыть вкладку «Замена» в диалоговом окне «Найти и Заменить». Затем в поле поиска введите:

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

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

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

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

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

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

В этом примере вы хотите использовать функцию SUMIF, чтобы вычислить общую стоимость продуктов из Великобритании.

Как использовать SUMIF в Microsoft Excel

Хотите складывать числа, но только если они соответствуют вашим условиям? Используйте SUMIF!

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

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

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

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

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

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

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

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

Другой способ применения подстановочных знаков в Microsoft Excel — это фильтры.

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

Microsoft Excel предлагает мощные возможности сортировки и фильтрации. Вот как их использовать в вашей таблице.

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

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

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

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

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

Важные моменты

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

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

Если вам понравилась эта статья, подпишитесь, чтобы не пропустить еще много полезных статей!

Вы также можете найти наши материалы в:

• Telegram: https://t.me/gergenshin
• Youtube: https://www.youtube.com/@gergenshin
• Яндекс Дзен: https://dzen.ru/gergen
• Официальный сайт: https://www-genshin.ru