Использование подстановочных знаков в ВПР / VLOOKUP и ПРОСМОТРХ (XLOOKUP) в Excel
Функции ВПР / VLOOKUP и ПРОСМОТРХ (XLOOKUP) — мощные инструменты для поиска данных в Excel. В этой статье мы рассмотрим, как использовать подстановочные знаки для поиска неточных совпадений и как организовать поиск по нескольким таблицам, что особенно полезно при работе с нестандартизированными данными.
ВПР доступна в Excel 2016 и новее; ПРОСМОТРХ — в Microsoft 365, Excel 2021/2024 и веб-версии, но не в Excel 2016/2019. Далее для ВПР используются именованные диапазоны Table, Table1 и Table2, которые нужно заранее создать.
Поиск с подстановочными знаками
Подстановочные знаки, такие как звёздочка (*), позволяют находить значения, даже если вы не знаете точную форму записи. Например, если в таблице могут встречаться варианты "Apple", "Apple Computer" или "Apple Computer Inc", можно использовать шаблон "Apple*".
Синтаксис для ВПР / VLOOKUP
=ВПР("Apple*"; Table; 2; ЛОЖЬ)=VLOOKUP("Apple*", Table, 2, FALSE)Эта формула найдёт первую запись в диапазоне поиска, которая начинается с "Apple".
Синтаксис для ПРОСМОТРХ (XLOOKUP)
В ПРОСМОТРХ (XLOOKUP) необходимо явно указать режим поиска с подстановочными знаками, используя аргумент match_mode.
=ПРОСМОТРХ("Apple*";A2:A100;B2:B100;"Не найдено";2)=XLOOKUP("Apple*",A2:A100,B2:B100,"Не найдено",2)Здесь 2 в пятом аргументе включает подстановочные знаки. Звёздочка означает любое число символов, вопросительный знак — один символ, тильда экранирует знак: ~* ищет буквальную звёздочку.
Поиск в нескольких таблицах
Часто данные могут быть распределены по нескольким таблицам. Например, нужно сначала искать в Table1, а если совпадение не найдено — в Table2.
Комбинация ВПР / VLOOKUP и ЕСНД / IFNA
Функция ЕСНД / IFNA позволяет задать альтернативное действие при ошибке #Н/Д (#N/A).
=ЕСНД(ВПР(A2; Table1; 3; ЛОЖЬ); ВПР(A2; Table2; 3; ЛОЖЬ))=IFNA(VLOOKUP(A2, Table1, 3, FALSE), VLOOKUP(A2, Table2, 3, FALSE))Второй ВПР используется только при #Н/Д в результате первого. ЕСНД не перехватывает остальные ошибки; #Н/Д в самой найденной ячейке тоже запустит запасной поиск.
Решение с ПРОСМОТРХ (XLOOKUP)
В ПРОСМОТРХ (XLOOKUP) аргумент если_не_найдено задаёт результат при отсутствии совпадения. Он не заменяет обработчик всех ошибок.
=ПРОСМОТРХ(A2;$F$2:$F$100;$G$2:$G$100;ПРОСМОТРХ(A2;$J$2:$J$100;$K$2:$K$100;"Не найдено"))=XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,XLOOKUP(A2,$J$2:$J$100,$K$2:$K$100,"Не найдено"))Если первый поиск не даёт результата, выполняется вложенный ПРОСМОТРХ (XLOOKUP).
Практические советы
- Используйте подстановочные знаки для работы с данными, которые могут иметь вариации в написании (например, названия компаний или товаров).
- Комбинируйте
ЕСНД / IFNAс функциями поиска для создания отказоустойчивых формул. - Помните, что
ПРОСМОТРХ (XLOOKUP)имеет отдельный аргумент для случая отсутствия совпадения, что упрощает формулы.
Освоение этих приёмов значительно повысит эффективность вашей работы с данными в Excel, особенно при анализе больших и неструктурированных наборов информации.