Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

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

forest_paw 27.12.2025 609

Использование подстановочных знаков в ВПР / 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; ЛОЖЬ)
English version:
=VLOOKUP("Apple*", Table, 2, FALSE)

Эта формула найдёт первую запись в диапазоне поиска, которая начинается с "Apple".

Синтаксис для ПРОСМОТРХ (XLOOKUP)

В ПРОСМОТРХ (XLOOKUP) необходимо явно указать режим поиска с подстановочными знаками, используя аргумент match_mode.

Русская версия:
=ПРОСМОТРХ("Apple*";A2:A100;B2:B100;"Не найдено";2)
English version:
=XLOOKUP("Apple*",A2:A100,B2:B100,"Не найдено",2)

Здесь 2 в пятом аргументе включает подстановочные знаки. Звёздочка означает любое число символов, вопросительный знак — один символ, тильда экранирует знак: ~* ищет буквальную звёздочку.

Поиск в нескольких таблицах

Часто данные могут быть распределены по нескольким таблицам. Например, нужно сначала искать в Table1, а если совпадение не найдено — в Table2.

Комбинация ВПР / VLOOKUP и ЕСНД / IFNA

Функция ЕСНД / IFNA позволяет задать альтернативное действие при ошибке #Н/Д (#N/A).

Русская версия:
=ЕСНД(ВПР(A2; Table1; 3; ЛОЖЬ); ВПР(A2; Table2; 3; ЛОЖЬ))
English version:
=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;"Не найдено"))
English version:
=XLOOKUP(A2,$F$2:$F$100,$G$2:$G$100,XLOOKUP(A2,$J$2:$J$100,$K$2:$K$100,"Не найдено"))

Если первый поиск не даёт результата, выполняется вложенный ПРОСМОТРХ (XLOOKUP).

Практические советы

  1. Используйте подстановочные знаки для работы с данными, которые могут иметь вариации в написании (например, названия компаний или товаров).
  2. Комбинируйте ЕСНД / IFNA с функциями поиска для создания отказоустойчивых формул.
  3. Помните, что ПРОСМОТРХ (XLOOKUP) имеет отдельный аргумент для случая отсутствия совпадения, что упрощает формулы.

Освоение этих приёмов значительно повысит эффективность вашей работы с данными в Excel, особенно при анализе больших и неструктурированных наборов информации.

Поделиться:

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