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

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

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

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

Аргументы

Замечания

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример

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

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

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

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