Функция ПРОСМОТРX - наследник ВПР

В мае 2019 года руководитель команды разработчиков Microsoft Excel Joe McDaid анонсировал выход новой функции, которая должна прийти на замену легендарной ВПР (VLOOKUP). Новая функция получила сочное английское название XLOOKUP и не очень внятное русское ПРОСМОТРX (причем последняя буква тут именно английская "икс", а не русская "ха" - забавно).

Полгода Microsoft тренировалась на кошках тестировала эту функцию на своих сотрудниках и добровольцах-инсайдерах и, наконец, в январе 2020 года было объявлено, что XLOOKUP готова к использованию и будет в ближайшее время разослана с обновлениями всем подписчикам Office 365.

Давайте разберёмся, в чем её преимущества перед классической ВПР (VLOOKUP), и как она может нам помочь в повседневной работе с данными в Microsoft Excel.

Старый добрый ВПР

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

Старый добрый ВПР

На всякий случай, напомню:

  • Первый аргумент здесь - искомое значение ("гречка" из H4).
  • Второй - область поиска, причем обязательно начиная со столбца, где хранятся искомые данные, т.е. с товара, а не с артикула.
  • Третий - порядковый номер столбца в таблице, из которого мы хотим извлечь нужное нам значение (цена в четвертом столбце).
  • Последний аргумент отвечает за режим поиска: 0 - точный поиск, 1 - поиск ближайшего наименьшего значения (для чисел). Причем 0 не подразумевается по умолчанию - нужно вводить его явно.

Привычно, знакомо и делается многими на автомате, не приходя в сознание. ОК.

Теперь посмотрим как то же самое можно вычислить с помощью новой функции ПРОСМОТРX (XLOOKUP).

Синтаксис ПРОСМОТРX (XLOOKUP)

Сначала, для порядка, давайте озвучим официальный синтаксис. У нашей новой функции 6 аргументов:

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

Выглядит немного громоздко, но последние три аргумента [в квадратных скобках] не являются обязательными (мы разберёмся с ними чуть позже). Так что, на самом деле, всё проще:

ПРОСМОТРX или XLOOKUP

  • Первый аргумент (искомое_значение) - что мы ищем ("гречка" из ячейки H4)
  • Второй аргумент (просматриваемый_массив) - диапазон ячеек, где мы ищем (столбец Товар в прайс-листе).
  • Третий аргумент (возвращаемый_массив) - диапазон, откуда хотим получить результаты (столбец Цена в прайс-листе).
Если сравнивать с ВПР, то стоит отметить, что:

  • По умолчанию используется точный поиск, т.е. не нужно это явно прописывать как в ВПР (последний нолик).
  • Не нужно отсчитывать и задавать номер столбца (третий аргумент ВПР). В больших таблицах это бывает непросто (особенно с учетом наличия скрытых столбцов).
  • Из предыдущего пункта автоматом следует, что вставка/удаление столбцов в прайс не ломают формулу (как было бы с ВПР).
  • Нет проблемы "левого ВПР", когда нужно извлечь значение левее просматриваемого столбца (например, артикул в нашем случае) - просматриваемый и возвращаемый массивы в ПРОСМОТРX могут располагаться как угодно (даже на разных листах, в общем случае!)
  • В общем и целом синтаксис гораздо проще и понятнее, чем у ВПР.

Также приятно, что ПРОСМОТРX отлично работает и в горизонтальном варианте без каких-либо доработок:

В горизонтальном варианте вместо ГПР

Раньше для этого нужно было использовать уже функцию ГПР (HLOOKUP) вместо ВПР (VLOOKUP).

Перехват ошибок #Н/Д

Если искомое значение отсутствует в списке, то функция ПРОСМОТРX, как и ВПР, выдаёт знакомую ошибку #Н/Д (#N/A):

Ошибка #Н/Д

Раньше для перехвата таких ошибок и замены их на что-нибудь более осмысленное применяли вложнную конструкцию из функций ЕСЛИОШИБКА (IFERROR) и ВПР (VLOOKUP). Теперь же можно сделать всё "на лету", используя 4-й аргумент [если_ничего_не_найдено] нашей новой функции :

Перехват ошибок

Удобно.

Приблизительный поиск

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

В старой ВПР за это отвечал последний аргумент [интервальный_просмотр] - если задать его равным 1, то ВПР переходила в режим поиска ближайшего наименьшего значения. В ПРОСМОТРХ за этот функционал отвечает 5-й аргумент [режим_сопоставления]:

Режим сопоставления для приблизительного поиска

Он может работать по четырём различным сценариям:

  • 0 - точный поиск (это режим по-умолчанию)
  • -1 - поиск предыдущего, т.е. ближайшего наименьшего значения (для 29 шт. товара это будет скидка 5%)
  • 1 - поиск следующего, т.е. ближайшего наибольшего (для 29 шт. товара это будет уже 10% скидки)
  • 2 - неточный поиск текста с использованием подстановочных символов

Если с первыми тремя вариантами тут всё более-менее понятно, то последний стоит прокомментировать дополнительно. Имеется ввиду ситуация, когда мы ищем значение, где помимо букв и цифр использованы подстановочные символы * (звёздочка = любое количество любых символов) и ? (вопросительный знак = один любой символ).

На практике это может использоваться, например, так:

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

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

Функция ВПР, кстати говоря, всегда умела такое "из коробки", так что особого преимущества у ПРОСМОТРX здесь нет. Но важен другой нюанс: функция ВПР при включенном приблизительном поиске (последний аргумент =1) строго требовала сортировки искомой таблицы по возрастанию. Новая функция прекрасно ищет ближайшее наибольшее или наименьшее и в неотсортированном списке.

Направление поиска

Если в таблице есть не одно, а несколько совпадений с искомым значением, то функция ВПР всегда выдает первое, т.к. ведёт поиск исключительно сверху-вниз. ПРОСМОТРX может искать и в обратном направлении (снизу-вверх) - за это отвечает последний 6-й её аргумент [режим_поиска]:

Направление поиска

Благодаря ему, поиск первого и (главное!) последнего совпадения больше не представляет сложности - различие будет только в значении этого аргумента:

Поиск первого и последнего совпадения

Раньше для поиска последнего совпадения приходилось неслабо шаманить с формулами массива и несколькими вложенными функциями типа ИНДЕКС, НАИБОЛЬШИЙ и т.п.

Резюме

Если вы дочитали до этого места, то выводы, я думаю, уже сделали сами :) На мой взгляд, у Microsoft получилось создать очень достойного наследника легендарной функции ВПР, добавив мощи и красоты и сохранив, при этом, простоту и наглядность использования.

Минус же пока только в том, что эта функция в ближайшее время появится только у подписчиков Office 365. Пользователи standalone-версий Excel 2013, 2016, 2019 эту функцию не получат, пока не обновятся до следующей версии Office (когда она выйдет). Но, рано или поздно, эта замечательная функция появится у большинства пользователей - вот тогда заживём! :)

Ссылки по теме




22.01.2020 08:00:10
Николай, большое спасибо за статью, очень интересно!!!
Столько глобальных изменений в последнее время в Excel.
Про функцию ВПР вы точно отметили об использовании не приходя в сознание :-D .
27.01.2020 14:41:57
Новость супер. Огорчает только вот это "Пользователи standalone-версий Excel 2013, 2016, 2019 эту функцию не получат, пока не обновятся до следующей версии Office". Дома то я что хочу то ворочу, захотела новый офис - пожалуйста. А на работе... ток если будет новая закупка и там уже будет установлена версия новее имеющейся (на моем конкретно 2010) и будет надобность сменить системник. Иного пути получить новый софт нет :(
27.01.2020 18:46:09
Ура!!! Я тоже дождался! - сегодня, наконец, пришло обновление
ПРОСМОТРX рулит!
29.01.2020 20:11:27
Где вы получили такое обновление? использую последнюю версию 2019 и у меня нет этой функции??
29.01.2020 22:48:12
У меня 365.
Причём, на одну учётку обновление пришло, а на другую из того же пакета пока нет.
01.02.2020 07:29:00
Они порциями раскатывают. Сегодня глава отдела Excel в MS написал, что к 1 февраля разослали уже всем, у кого ежемесячный канал обновлений Monthly Channel.
04.02.2020 09:51:14
Подскажите, возможно ли как то самостоятельно установить данную функцию на Excel 2016 ?
08.02.2020 09:43:27
Для владельцев обычных версий Office эта функция недоступна. Придется ждать, когда MS выпустят следующую версию, например 2020 или 2021 или ...
02.02.2020 18:08:27
Николай, добрый день!

Сегодня порадовали обновлением, наконец и у меня есть ПРОСМОТРХ.
Вероятно Вы уже знаете способ применить её для сбора значений с разных листов
в одной книге?
Подскажите если не затруднит!?
10.02.2020 21:51:30
Николай, спасибо!
Как всегда кратко и доходчиво, остаётся дождаться обновления.
Рискну поинтересоваться ещё об одной функции,
ПОИСКПОЗХ, чем она интересна?
02.03.2020 05:05:05
Открыл я значит сегодня файл где массово использовал формулу просмотрx. А там ошибки вместо формулы.
Попробовал набрать формулу в новой ячейки, а её нет.
:D
=_xlfn.XLOOKUP([@[C2_WBS_Spool]];Таблица2[Зона-линия-узел];Таблица2[№ письма];"";)
Sub-Contractor ErectionПисьмо передачиАКТ передачи
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
VNZM#ИМЯ?#ИМЯ?
03.03.2020 07:22:14
Обновление вам не откатили, случайно? :)
01.04.2020 16:05:48
У меня тоже самое на Win7
Тех.Поддержка сказала, что это связано с прекращением поддержки Win7. Посоветовали перейти на Win8/10

Я такую штуку сегодня обнаружил. Хотя 23 марта пользовался.
Пропали и остальные обновления - дин.диапазоны, сорт и т.д.
30.04.2020 00:57:13
У меня тоже нету на Excel 2016 32-bit.

Примечание. В настоящее время эта функция доступна подписчикам Microsoft 365 на ежемесячном канале. Он будет доступен подписчикам Microsoft 365 на полугодовом канале, начинающемся в июле 2020 года. Для получения дополнительной информации о том, как функции распространяются на подписчиков Microsoft 365, см. Когда я получу новейшие функции для Microsoft 365.

Взято отсюда: https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
17.06.2020 23:06:59
а как найти второе совпадение или третье?
14.09.2020 05:57:26
Office 2019, почему-то нет функции "ПРОСМОТРX", есть только "ПРОСМОТР" и всё.
Обновил до конца.
Наверх