Функция ПРОСМОТР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.2022 02:08:10
Здравствуйте. Вы нашли решение по своему вопросу?
У меня такая же проблема...
10.02.2022 15:11:24
Здравствуйте, решение так и не нашёл. Но есть похожее решение с помощью UDF AllSumif от Priest
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", есть только "ПРОСМОТР" и всё.
Обновил до конца.
14.10.2020 13:07:50
Office 2019 вышел в 2018 году, функцию добавили январе 2020.
Все новые фичи после выхода, только для подписчиков Office 365
14.10.2020 13:10:51
Наконец то наши IT соизволили накатить нам Excel с версией 2002 (до этого стояла 1908), в которой появилась эта формула.

PS: 10 месяц ждал, ЖЕСТЬ!!!
15.10.2020 06:09:06
Excel 2011 версии билд, MS Office 2019 - до сих пор нет функции, разве они обновление не выпускали?
15.10.2020 17:53:12
У Office for Mac своя хронология обновлений. Там, вполне возможно, этой функции и не будет в ближайшем времени :(
23.10.2020 13:06:54
У мен подписка, но этой функции нет
03.04.2021 01:04:33
Николай, добрый день! Спасибо за очередное прекрасное обучающее видео!
Если можно, то у меня к Вам вопрос-просьба: не могли бы Вы в очередной обновке Plex (если, конечно же, Вы планируете обновление) написать/встроить функцию ПросмотрХ - ну уж очень хочется. Умел бы сам писать udf или (как там правильно?) пользовательские функции давно бы написал, но "не цыган я, не цыган"©:D
Добрый день!

Присоединяюсь к просьбе, было бы здорово, если бы  формула появилась в Plex. Установили MS Office 2019, к сожалению, формулы не оказалось.(((
10.06.2021 14:38:59
А разве вместо функции ВПР нельзя использовать функцию ПРОСМОТР, тем у кого нет ПРОСМОТРХ? Да, нет последних двух аргументов, но не так это и критично.
29.09.2021 16:49:16
Не-не, ПРОСМОТР - это совсем другая функция и к ПРОСМОТРХ она никакого отношения не имеет (зато имеет кучу ограничений).
20.07.2021 14:51:31
 Добрый день! Всё работает, только в случае если не найден заданный аргумент появляется ноль, вместо текста. Не подскажете, что не так в данном случае.)
10.11.2023 22:39:57
Тоже столкнулся с указанной особенностью. Помогло исправить написание функции с условием:

=ЕСЛИ(ПРОСМОТРX(...)=0;"";ПРОСМОТРX(...))
23.02.2022 12:38:12
Добрый день!
Уважаемый Гуру.

Подскажите может не там ищу.
Вопрос в следующем: как осуществить выборку из массива по определённому набору условий, включающему в себя просмотр значений по условию подстановочных символов (звёздочка = любое количество любых символов) и ? (вопросительный знак = один любой символ). Результат - массив, соответствующий всем (нескольким) требованиям.
Если нагляднее из Вашего примера:
=Выборка([Товар];[Упаковка]="паке*";[Страна]="Росси?"
результат массив из двух значений {Морковь,Гречка} (для последующих вычислений)

либо второй вариант (как с обычными диапазонами)
([Упаковка="паке*"*([Страна]="Росси?"или
{(c4:c18="паке*"*(d4:d18="Росси?"*1} результат массив (диапазон) в 18 значений = все 0, кроме 1-го и 10-го там 1
с символами подстановки не работает

Очень буду признателен за помощь, нужна именно формула, без макроса. Его я принципиально не вставляю в книгу, хотя там кода VBA всего 5 строк

файл примера в этой теме
15.05.2022 10:16:48
Доброго времени суток. Ищу ответа на свой вопрос уже больше недели касательно функции ПРОСМОТРХ.

Как на последнем изображении функция останавливается когда она находит значение в данном случае стоимости отгрузки 813 500.
И не имеет значение ячейка пустая или нет.

Вопрос в том как сделать так чтобы она не останавливалась. Чтобы функция пропускала пустые ячейки.


Ссылка с похожей проблемой только с функцией ВПР и файлом
14.06.2022 10:36:20
что-то у меня в "Умной таблице" просмотрх не работает, а ВПР работает
можно на примерах в умной таблице?
16.08.2022 15:27:54
Добрый день!
Что быстрее, ПРОСМОТРX() или ФИЛЬТР()?
09.09.2022 16:17:03
Microsoft Office LTSC профессиональный плюс 2021 - нет функции ПРОСМОТРX, а только ПРОСМОТР. И работает она коряво. Часть цен подтаскивает из умной таблицы часть нет. ВПР работает исправно. Наверное функция не доработана. После изучения в интернете, понял что таблица с данными должна быть отсортирована и тогда все работает
08.11.2022 18:23:18
ПРОСМОТР - это старая функция и к ПРОСМОТРХ никакого отношения не имеет.
Добрый день! Вопрос - сделал большую работу с применением функции ПросмотрХ, после большого перерыва - файл перестал считывать функцию...
если просто открыть файл то - ошибка имя?
если открываю файл через открытую новую книгу - открыть и выбрать этот файл - все работает...но сохраняя даже под другим именем и в другие места не приводит к тому что напрямую открывая все работает... так же ошибка имя?
=_xlfn.XLOOKUP([@Наименование];Таблица1[Наименование];Таблица1[Цена])
Шкаф Леон 3х ств
Прайс себестоимость
НаименованиеСтолбец1Ед.измКол-воЦенаСумма
ЛДСП 16 мм заказнойм26,3922#ИМЯ?#ИМЯ?
Отходы ЛДСП заказной10%м20,639220#ИМЯ?#ИМЯ?
ЛДСП 16 мм заказнойм22,2966#ИМЯ?#ИМЯ?
Отходы ЛДСП заказной10%м20,229660#ИМЯ?#ИМЯ?
ЛДВП 3,2 ммм22,637#ИМЯ?#ИМЯ?
Отходы ЛДВП15%м20,395550#ИМЯ?#ИМЯ?
Кромка ПВХ 0,4*19п.м.19,128#ИМЯ?#ИМЯ?
Кромка ПВХ 0,4*19п.м.15,54#ИМЯ?#ИМЯ?
Клей-расплав кромочный8гр91#ИМЯ?#ИМЯ?
Зеркало с фацетомшт0,46#ИМЯ?#ИМЯ?
Опора подпятникшт8#ИМЯ?#ИМЯ?
Флянецшт2#ИМЯ?#ИМЯ?
Труба хромп.м0,766#ИМЯ?#ИМЯ?
Шкантшт6#ИМЯ?#ИМЯ?
Шуруп 4*16шт110#ИМЯ?#ИМЯ?
Шуруп 4*30шт10#ИМЯ?#ИМЯ?
Направляющие полного выката 450 ммкомпл.2#ИМЯ?#ИМЯ?
Гвоздь 2*20шт80#ИМЯ?#ИМЯ?
Еврошурупшт32#ИМЯ?#ИМЯ?
Евроключшт1#ИМЯ?#ИМЯ?
Заглушка шт6#ИМЯ?#ИМЯ?
Петля накладнаяшт11#ИМЯ?#ИМЯ?
Полкодержатель D5шт16#ИМЯ?#ИМЯ?
Ручка С-18 160 ммшт5#ИМЯ?#ИМЯ?
Эксцентрикшт6#ИМЯ?#ИМЯ?
Пакет фасовочныйшт1#ИМЯ?#ИМЯ?
Упаковкашт1650,00650,00
Работаруб1650,00650,00
Прочие расходы5%руб#ИМЯ?
Итог#ИМЯ?
08.11.2022 18:22:50
Такое может быть, если на компьютере, где вы сейчас открыли файл, нет функции ПРОСМОТРХ.
Либо это старая версия Excel, либо могли откатиться какие-то обновления, содержащие эту функцию.
28.12.2022 08:55:54
Есть особенность у "просмотрх", если возвращаемое значение "пустая ячейка", т.е. 0, то функции типа "мин" не воспринимают этот "0"  как ноль. Если возвращаемое значение 0 (не пустая чейка), то нормально работают дальнейшие операции.
10.11.2023 22:34:41
Тоже столкнулся с этой особенностью. Помогло исправить «мнимый 0» следующее написание функции:

=ЕСЛИ(ПРОСМОТРX(...)=0;"";ПРОСМОТРX(...))

Не лучшее решение: требует написания функции 2 раза. Но рабочее.
Может быть кто-то нашёл более короткий вариант обхода этой «особенности»?
K M
01.02.2023 15:42:05
Добрый день!
Подскажите пожалуйста как "зафиксировать столбец" с данными  при протягивании функции по столбцам при использовании имен столбцов?

У меня в первом столбце таблицы куда подтягиваю данные ИНН, 2-5 столбцы разные данные из другой таблицы.
При "протягивании вправо"  @ИНН меняется на навзание следкющего столбца

XLOOKUP([@ИНН]
14.02.2023 09:44:17
Добрый день. Уважаемые, эксперты и гуру Excel, у меня задача над которой ломаю голову уже пару дней, и не могу уснуть спокойно). Нужно найти аналог магазина. Есть два списка с магазинами, первый список с магазинами к которым нужно подобрать аналог на основании двух текстовых  признаков (подформат и тип сезона) и бюджета магазина:
МагазинПодформатТип сезона Бюджет
ПотомственныйГородОбычный   4 734 951
АльтруизмДеревняСезонный   3 578 942
ПереливГородАнтисезон   1 951 438
КортасарДеревняОбычный   3 927 855
КомароваГородОбычный   2 911 082
НогинаДеревняОбычный 893 184
Это второй список с магазинами аналогами:
МагазинПодформатТип сезона Бюджет
БалансирГородОбычный   3 563 916
МаестатДеревняСезонный   3 335 729
АштонГородАнтисезон   2 083 801
ТопольГородОбычный   2 397 819
АтаманскийГородОбычный   4 181 757
КраснодонскийДеревняОбычный   2 520 496
МаслодельныйГородАнтисезон   3 056 046
ПроционДеревняОбычный   1 883 311
Ниже решение, которое я сделал руками:
МагазинПодформатТип сезона Бюджет Аналог:
ПотомственныйГородОбычный   4 734 951 Атаманский
АльтруизмДеревняСезонный   3 578 942 Маестат
ПереливГородАнтисезон   1 951 438 Маслодельный
КортасарДеревняОбычный   3 927 855 Краснодонский
КомароваГородОбычный   2 911 082 Тополь
НогинаДеревняОбычный 893 184 Процион

Это можно сделать формулой, ПРОСМОТРХ ?
Через ВПР и ИНДЕКС и ПОИСКПОЗ у меня ничего не вышло.


Заранее благодарен!
16.02.2023 15:26:18
Или может есть какая-нибудь другая формула, не через ПРОСМОТРХ ?
02.08.2023 21:36:36
Всем привет! У меня очередной сюрприз. В обычном файле без всяких макросов решил сравнить VLOOKUP, SUMIF, INDEX&MATCH и нашего главного героя XLOOKUP. Получилась ерунда следующего характера: при миксе кодов по которым идёт запрос, добавлении туда-же кодов без совпадений, плюс список 10.000 строк (!!!) только XLOOKUP посчитал все величины и всё замечательно нашёл в обе стороны (снизу вверх и сверху вниз) ОСТАЛЬНЫЕ ФОРМУЛЫ ПОСЧИТАЛИ ТОЛЬКО 1.000 строк, а остальное выдали нулями (!!!). Я естественно ставлю защиту от "нет данных", но НОЛЬ выдаёт 9.000 строк. Могу прислать файл-пример. Абсолютно не горит, просто интересно.
02.11.2023 12:36:32
Спасибо большое за статью, очень интересно.
Только вот такой вопрос возник, как правильно написать формулу, если у имеется два критерия поиска?
ТоварДатаЦена
Груша30.11.2331
Яблоко30.11.2325
Персик30.11.2317
Груша31.11.2333
Яблоко31.11.2322
Персик31.11.2319
Груша01.12.2332
Яблоко01.12.2325
Персик01.12.2318
Например мне надо чтоб формула выдала цену при условии что это яблоко проданное 01.12.23.
Как это реализовать?
21.01.2024 18:32:43
Подскажите если не затруднит, способ применить функцию ПРСОМОТРX для сбора значений с разных листов.
в одной книге?
21.01.2024 18:50:08
ПРОСМОТРХ.

Подскажите, если не затруднит, способ применить её для сбора значений с разных листов
в одной книге?
Наверх