12 ключевых преимуществ функции ПРОСМОТРХ (XLOOKUP) в Excel

Функция ПРОСМОТРХ (XLOOKUP), представленная в Microsoft 365 в ноябре 2019 года, стала революционным обновлением для поиска данных в Excel. Разработанная Джо Макдейдом из команды Excel, она объединила лучшие черты ВПР / VLOOKUP и ИНДЕКС / INDEX/ПОИСКПОЗ / MATCH, предлагая более гибкий и надежный инструмент для работы с данными.

Если вам интересны Python в Excel, Copilot и AI-инструменты, материал поможет понять, как использовать их в реальных рабочих сценариях.

Синтаксис функции ПРОСМОТРХ (XLOOKUP)

Основной синтаксис функции выглядит следующим образом:

Русская версия:

=ПРОСМОТРХ(искомое_значение; массив_поиска; массив_возврата; [если_не_найдено]; [режим_сопоставления]; [режим_поиска])

English version:

=XLOOKUP(искомое_значение, массив_поиска, массив_возврата, [если_не_найдено], [режим_сопоставления], [режим_поиска])

12 преимуществ ПРОСМОТРХ (XLOOKUP) перед старыми функциями

Точное совпадение по умолчанию — больше не нужно указывать ЛОЖЬ или 0 в конце формулы.
Ссылка вместо номера столбца — третий аргумент является ссылкой на диапазон, а не числом, что делает формулу устойчивой к вставке или удалению столбцов.
Встроенная обработка ошибок — четвертый аргумент [если_не_найдено] позволяет сразу указать значение или текст при отсутствии совпадения.
Поиск слева от ключевого поля — ПРОСМОТРХ (XLOOKUP) легко возвращает значения из столбцов, расположенных левее столбца поиска.
Поиск ближайшего меньшего или большего значения без сортировки — используйте аргумент [режим_сопоставления] со значениями -1 или 1.
Замена ГПР / HLOOKUP — работает с горизонтальными массивами данных.
Поиск последнего совпадения — аргумент [режим_поиска] позволяет начать поиск с конца списка.
Подстановочные знаки отключены по умолчанию — для их использования нужно явно указать режим сопоставления 2.
Возврат нескольких значений в одной формуле — благодаря динамическим массивам можно вернуть целый диапазон (например, все 12 месяцев).
Одна формула для всего столбца — указав диапазон в качестве искомого_значения, можно выполнить массовый поиск.
Возврат ссылки на ячейку — если ПРОСМОТРХ (XLOOKUP) стоит рядом с оператором (например, двоеточием), он возвращает адрес ячейки, а не её значение.
Двунаправленный поиск — позволяет выполнять поиск и по строкам, и по столбцам, заменяя конструкцию ИНДЕКС(,ПОИСКПОЗ(),ПОИСКПОЗ()).

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

Рассмотрим несколько ключевых сценариев, где ПРОСМОТРХ (XLOOKUP) демонстрирует свою мощь.

Преимущество 1: Точное совпадение по умолчанию

99% формул ВПР / VLOOKUP заканчивались на ,ЛОЖЬ. В ПРОСМОТРХ (XLOOKUP) точное совпадение установлено по умолчанию, что упрощает запись.

Пример формулы XLOOKUP с поиском значения из ячейки A4 в диапазоне L8:L35 и возвратом цены из столбца N. — Пример формулы ПРОСМОТРХ (XLOOKUP) с поиском значения из ячейки A4 в диапазоне L8:L35 и возвратом цены из столбца Ч / N.

Преимущество 2: Устойчивость к изменению структуры таблицы

Поскольку третий аргумент — это ссылка (например, N8:N35), а не номер столбца, формула автоматически адаптируется при вставке новых столбцов.

Сравнение уязвимости VLOOKUP и устойчивости XLOOKUP к вставке столбцов в таблице. — Сравнение уязвимости ВПР / VLOOKUP и устойчивости ПРОСМОТРХ (XLOOKUP) к вставке столбцов в таблице.

Преимущество 3: Встроенная обработка ошибки #Ч / N/A

Четвертый аргумент позволяет элегантно обработать ситуацию, когда значение не найдено.

Русская версия:

=ПРОСМОТРХ(A2; $D$2:$D$100; $E$2:$E$100; "Не найдено")

English version:

=XLOOKUP(A2, $D$2:$D$100, $E$2:$E$100, "Не найдено")

Использование аргумента 'если не найдено' для замены ошибки #N/A на 0 или текст. — Использование аргумента 'если не найдено' для замены ошибки #Ч / N/A на 0 или текст.

Преимущество 5: Поиск без сортировки таблицы

Используйте [режим_сопоставления] для поиска ближайшего меньшего (-1) или большего (1) значения. Таблицу при этом сортировать не нужно.

Пример поиска подходящего транспортного средства по количеству пассажиров с использованием match_mode=1.

Преимущество 9 и 10: Динамические массивы и массовый поиск

Сила ПРОСМОТРХ (XLOOKUP) раскрывается в полной мере вместе с динамическими массивами.

Возврат диапазона: Одна формула может вернуть все 12 месяцев, если указать прямоугольный массив_возврата.
Массовый поиск: Указав вертикальный диапазон в качестве искомого_значения, вы получите результаты для всего списка одной формулой.

Одна формула XLOOKUP в ячейке C4 возвращает ставки для всего списка имен из B4:B14. — Одна формула ПРОСМОТРХ (XLOOKUP) в ячейке C4 возвращает ставки для всего списка имен из B4:B14.

Заключение

Функция ПРОСМОТРХ (XLOOKUP) — это современный, мощный и интуитивно понятный инструмент для поиска данных в Excel. Она не только устраняет основные недостатки ВПР / VLOOKUP и ГПР / HLOOKUP, но и открывает новые возможности благодаря интеграции с динамическими массивами. Освоение ПРОСМОТРХ (XLOOKUP) — обязательный шаг для любого, кто хочет эффективно работать с данными в современных версиях Excel 365.

Начните заменять свои старые формулы ВПР / VLOOKUP на ПРОСМОТРХ (XLOOKUP), чтобы сделать таблицы более надежными, читаемыми и функциональными.