Excel - функции XLOOKUP, ВПР и функции ИНДЕКС / ПОИСКПОЗ

28 августа 2022 года Microsoft объявила о выпуске функции XLOOKUP. Функция XLOOKUP предназначена для замены функций ВПР и ГПР. Он также заменит комбинацию функций ИНДЕКС / ПОИСКПОЗ, которая ранее использовалась для выполнения более мощных ВПР.

Функция XLOOKUP постепенно выпускается для пользователей Office 365 (начиная с участников программы предварительной оценки Office 365). Таким образом, вы можете еще не увидеть новую функцию. Пользователи Office 2022 не получат XLOOKUP. Поэтому будьте осторожны при использовании функции XLOOKUP - убедитесь, что ваши конечные пользователи имеют доступ к новой функции.

Пример XLOOKUP

Синтаксис XLOOKUP

Синтаксис XLOOKUP:

XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])

Где:

• lookup_value - Что искать
• lookup_array - Где искать
• return_array - Что выводить
• [match_mode] - (НЕОБЯЗАТЕЛЬНО) Укажите тип соответствия для выполнения. По умолчанию установлено точное соответствие (все параметры см. В таблице ниже).
• [search_mode] - (НЕОБЯЗАТЕЛЬНО) Укажите тип и направление поиска. По умолчанию - First-To-Last (все параметры см. В таблице ниже)

XLOOKUP Match_Mode

0 - При точном совпадении будут найдены только точные совпадения

1 (-1) - Выполнит точное совпадение или найдет следующий по величине (наименьший) элемент.

2 - Соответствие подстановочных знаков позволяет использовать? или * символы подстановки для неточных совпадений.

XLOOKUP Search_Mode

1 - Поиск сверху вниз (или слева направо для горизонтального поиска)

-1 - Поиск снизу вверх (или справа налево для горизонтального поиска)

2 (-2) - Бинарный поиск по отсортированным данным. Если вы не знаете, что такое бинарный поиск, вам, вероятно, никогда не понадобится его выполнять.

Почему XLOOKUP лучше, чем VLOOKUP?

1. Функция ВПР требует, чтобы столбец подстановки был самым левым столбцом в наборе данных. Вы не можете «искать влево». Это было одним из основных преимуществ использования ИНДЕКС / ПОИСКПОЗ вместо ВПР. Однако XLOOKUP не имеет этого ограничения.
2. По умолчанию XLOOKUP соответствует точному совпадению. По умолчанию ВПР соответствует «приблизительному» совпадению, требуя, чтобы вы добавили аргумент «ложь» в конце ВПР для выполнения точного совпадения. Это было причиной бесчисленных ошибок в таблицах, когда пользователи непреднамеренно выполняли приблизительные совпадения.
3. Формулы ВПР не могут обрабатывать вставки или удаления столбцов. Если вы вставили или удалили столбец, вам нужно будет изменить порядковый номер столбца в ВПР. Это не проблема с функцией XLOOKUP.
4. XLOOKUP может выполнять горизонтальный или вертикальный поиск. XLOOKUP заменяет как VLOOKUP, так и HLOOKUP.
5. XLOOKUP имеет более точные приблизительные совпадения. Данные ВПР необходимо отсортировать от наименьшего к наибольшему. Однако XLOOKUP может выполнять поиск в любом направлении.
6. XLOOKUP требует ссылки на меньшее количество ячеек. ВПР требует ввода всего набора данных, но XLOOKUP требует только ссылки на соответствующие столбцы или строки. Ссылаясь на меньшее количество ячеек, XLOOKUP увеличит скорость вычислений электронной таблицы и потенциально приведет к меньшему количеству ошибок циклических ссылок.

Примеры XLOOKUP в VBA

XLOOKUP пока доступен только для инсайдерской программы Office 365, поэтому он еще не является общедоступным. Скоро он станет доступен.