Использование подстановочных знаков в ВПР (VLOOKUP) и ПРОСМОТРХ (XLOOKUP) в Excel
Функции ВПР (VLOOKUP) и ПРОСМОТРХ (XLOOKUP) — мощные инструменты для поиска данных в Excel. В этой статье мы рассмотрим, как использовать подстановочные знаки для поиска неточных совпадений и как организовать поиск по нескольким таблицам, что особенно полезно при работе с нестандартизированными данными.
Поиск с подстановочными знаками
Подстановочные знаки, такие как звёздочка (*), позволяют находить значения, даже если вы не знаете точную форму записи. Например, если в таблице могут встречаться варианты "Apple", "Apple Computer" или "Apple Computer Inc", можно использовать шаблон "Apple*".
Синтаксис для ВПР (VLOOKUP)
=ВПР("Apple*", Table, 2, ЛОЖЬ) (=VLOOKUP("Apple*", Table, 2, FALSE))Эта формула найдёт первую запись в диапазоне поиска, которая начинается с "Apple".
Синтаксис для ПРОСМОТРХ (XLOOKUP)
В ПРОСМОТРХ (XLOOKUP) необходимо явно указать режим поиска с подстановочными знаками, используя аргумент match_mode.
=ПРОСМОТРХ("Apple*", A, B, , 2) (=XLOOKUP("Apple*", A, B, , 2))Здесь 2 в пятом аргументе указывает на поиск с подстановочными знаками.
Поиск в нескольких таблицах
Часто данные могут быть распределены по нескольким таблицам. Например, нужно сначала искать в Table1, а если совпадение не найдено — в Table2.
Комбинация ВПР (VLOOKUP) и ЕСНД (IFNA)
Функция ЕСНД (IFNA) позволяет задать альтернативное действие при ошибке #Ч (N)/A.
=ЕСНД(ВПР(А2, ТАБКД1, 3, ЛОЖЬ), ВПР(А2, ТАБКД2, 3, ЛОЖЬ)) (=IFNA(VLOOKUP(A2, Table1, 3, FALSE), VLOOKUP(A2, Table2, 3, FALSE)))Если первый ВПР (VLOOKUP) возвращает ошибку, выполняется второй.
Решение с ПРОСМОТРХ (XLOOKUP)
В ПРОСМОТРХ (XLOOKUP) аргумент для обработки ошибки #Ч (N)/A встроен прямо в функцию.
=ПРОСМОТРХ(А2, A, B, ПРОСМОТРХ(А2, C, D)) (=XLOOKUP(A2, A, B, XLOOKUP(A2, C, D)))Если первый поиск не даёт результата, выполняется вложенный ПРОСМОТРХ (XLOOKUP).
Практические советы
- Используйте подстановочные знаки для работы с данными, которые могут иметь вариации в написании (например, названия компаний или товаров).
- Комбинируйте
ЕСНД (IFNA)с функциями поиска для создания отказоустойчивых формул. - Помните, что
ПРОСМОТРХ (XLOOKUP)более гибок и имеет встроенную обработку ошибок, что упрощает формулы.
Освоение этих приёмов значительно повысит эффективность вашей работы с данными в Excel, особенно при анализе больших и неструктурированных наборов информации.