Функция ПРОСМОТРХ (XLOOKUP)

Функция ПРОСМОТРХ (XLOOKUP) использует возможности динамических массивов и призвана заменить сразу функции ВПР и ГПР.

Описание функции ПРОСМОТРХ

Функция XLOOKUP анонсирована Microsoft практически через год после анонса динамических массивов которые Microsoft анонсировала 24 сентября 2018 года. Данная функция, как и возможность использования динамических массивов не должна быть доступна пользователям, купившим Office 2019 и, тем более, более ранним версиям.

Воспользоваться новыми возможностями смогут пользователи с подпиской Office 365, а в будущем пользователи Office 2021 (следующей версией, которая следует за Office 2019), если к тому времени Microsoft попросту не оставит только вариант с подпиской.

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

Синтаксис ПРОСМОТРХ

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

Аргументы

искомое_значениепросматриваемый_массиввозвращаемый_массиврежим_сопоставлениярежим_поиска
Обязательный. Значение, которое необходимо найти
Обязательный. Массив или диапазон для поиска
Обязательный. Массив или диапазон, который нужно вернуть
Необязательный. Используются следующие варианты для аргумента:

  • 0 — точное совпадение. Если ничего не найдено будет возвращена ошибка #N/А. Данный параметр используется по умолчанию.
  • -1 — точное совпадение. Если ничего не найдено, будет возвращено следующий меньший элемент.
  • 1 — точное совпадение. Если ничего не найдено, будет возвращено следующий более крупный элемент.
  • 2 — использование подстановочных знаков при поиске.
Необязательный. Используются следующие режимы поиска:

  • 1 — стандартный поиск с начала списка. Данный параметр используется по умолчанию.
  • -1 — обратный поиск с конца списка.
  • 2 — двоичный поиск (поиск по числам). Список будет должен будет отсортирован по возрастанию
  • -2 — двоичный поиск (поиск по числам). Список будет должен будет отсортирован по убыванию

Замечания

  • Функция ВПР по умолчанию, возвращала приблизительное совпадение, если пользователь не передал необязательный параметр «интервальный_просмотр». По заявлению Microsoft, непредвиденные результаты вводили в заблуждение неопытных пользователей, ведь для приблизительного соответствия массив должен быть отсортирован. Новая функция по умолчанию возвращает точное соответствие, это такая особенность работы и ее следует учитывать.
  • если в поле «режим_сопоставления» ввести значение 2, то в в поле «искомое_значение» можно будет использовать подстановочные знаки, где «?» — означает замену одного символа, а «*» — любое количество символов. На самом деле, по умолчанию передавать подстановочные знаки в ВПР можно, в новой функции для этого есть специальный переключатель. Скорее всего, это сделано по соображениям повышения производительности.
  • Размерность массивов, которые передаются в параметры «просматриваемый_массив» и «возвращаемый_массив» должна быть одинаковой, иначе функция вернет ошибку #ЗНАЧ!. В случае использование ВПР (ГПР) и поиск и возврат значения осуществлялся по сути из одного массива, поэтому не правильно указать размерность попросту было невозможно, здесь же на это следует обратить внимание.
  • ПРОСМОТРX как и ВПР, ищет первое значение в списке, если нужно найти все совпадения используйте функцию ФИЛЬТР

Основные преимущества перед ВПР (и ГПР)

Раз данная функция представлена как более совершенная замена такой популярной функции как ВПР (заодно и ГПР), будет правильно рассмотреть чем новая функция выгодно отличается от уже знакомых пользователям аналогов.

  • Поиск не только в первом столбце массива
  • Одним из популярных недостатков ВПР — это поиск значения исключительно в первом столбце диапазона. Решение в виде изменения источника данных таким образом, чтобы искомый столбец находился первым рассматривать не правильно.

    Есть решение в виде использования связки из функций ИНДЕКС и ПОИСКПОЗ. Единственным недостатком такого решения (если опустить вопрос производительности) является трудность в освоении для неподготовленного пользователя и относительная громоздкость решения. Громоздкость в последствии сможет негативно сказать на читаемости формул.

  • Передача в функцию бесполезной информации
  • В функциях ВПР и ГПР столбцы в котором осуществляется поиск и с которого возвращается результат должны быть составляющими одного массива. Это негативно сказываться на производительности ведь в формулу всегда нужно передавать кучу данных, которая попросту не используется.

    Справедливости ради, следует отметить, что Microsoft буквально за месяц перед анонсом новой функции произвела усовершенствование производительности алгоритмов ВПР (ГПР). Но это не отменяет того факта, что всегда нужно передавать «бесполезный» пласт информации, который, к тому же, может затруднить читаемость функций.

  • Жесткое закрепление возвращаемого массива
  • Возвращаемый массив в функцию должен передаваться числом, т.е. функция ВПР читается как «найти значение «А» в первом столбце и вернуть соответствующее ему значение из столбца «n», где n — это число, например, 3″.

    Для больших таблиц это просто не удобно для пользователя высчитывать номер столбца с которого нужно вернуть результат, например, если вместо 3 будет 23 или 33. Но еще большее неудобство возникает, если в исходный диапазон будет добавлен еще столбец, или удален существующий. Если новый/удаленный столбец находятся между первым и искомым столбцом, придется изменить формулу.

    В новой функции ПРОСМОТРX диапазон с которого возвращается результат указывается диапазоном и добавление/удаление столбцов никак не скажется на результатах вычисления формулы.

  • Возвращение результата не одного диапазона
  • Использую ВПР вы сможете найти, например, сотрудника и вернуть его зарплату, если нужно еще вернуть подразделение в котором он работает, придется написать еще одну формулу. С помощью ПРОСМОТРX можно вернуть сразу же двумерный массив.

    Опять таки, с в версиях Excel, с поддержкой динамических массивов, в ВПР тоже можно указать не просто номер столбца, а передать туда массив чисел и функция вернет несколько значений. Но это появилось с поддержкой динамических массивов.

  • Работа как с вертикальными так и горизонтальными массивами
  • Поскольку ПРОСМОТРX призвана заменить сразу 2 функции, то очевидно, что она должна работать с массивами любой ориентации.

    Напомним, что первая буква в функциях ВПР и ГПР и обозначала как должен быть организован массив «В» — вертикально, «Г» — горизонтально.

  • Возможность поиска в обратном порядке
  • В случае использования ВПР (ГПР) поиск всегда осуществлялся с начала списка и обойти это ограничение без переформатирования самого исходного диапазона было невозможно.

    Теперь же поиск можно произвести с «конца списка», например, если список отсортирован по показателям продаж по дням, можно сразу же найти и лучший, и худший результат по определенному сотруднику.

  • Больше возможностей в режимах сопоставления
  • В случае использования старых функций был вариант не точного поиска, если диапазон отсортирован и точного значения не находилось, то возвращалось максимальное значение, которое меньше искомого. В случае использование ПРОСМОТРX можно найти как значение меньше искомого, так и больше, если точного совпадения не будет.

    Более того, предварительная сортировка в случае не точно соответствия не требуется, функция корректно отработает в любом случае.

    Пример

    Видео работы функции

    Дополнительные материалы

    Microsoft представила замену ВПР, ГПР а также ПОИСКПОЗ

    Microsoft добавила динамические массивы в Excel и новые функции.

    Scroll Up