Функция ПРОСМОТР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(искомое_значение; просматриваемый_массив; возвращаемый_массив; [если_ничего_не_найдено]; [режим_сопоставления]; [режим_поиска])
Выглядит немного громоздко, но последние три аргумента [в квадратных скобках] не являются обязательными (мы разберёмся с ними чуть позже). Так что, на самом деле, всё проще:
- Первый аргумент (искомое_значение) - что мы ищем ("гречка" из ячейки 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 (когда она выйдет). Но, рано или поздно, эта замечательная функция появится у большинства пользователей - вот тогда заживём! :)
Ссылки по теме
- Как использовать функцию ВПР (VLOOKUP) для поиска и подстановки значений
- Левый ВПР
- Связка функций ИНДЕКС и ПОИСКПОЗ как аналог ВПР
Столько глобальных изменений в последнее время в Excel.
Про функцию ВПР вы точно отметили об использовании не приходя в сознание
ПРОСМОТРX рулит!
Причём, на одну учётку обновление пришло, а на другую из того же пакета пока нет.
Сегодня порадовали обновлением, наконец и у меня есть ПРОСМОТРХ.
Вероятно Вы уже знаете способ применить её для сбора значений с разных листов
в одной книге?
Подскажите если не затруднит!?
У меня такая же проблема...
Как всегда кратко и доходчиво, остаётся дождаться обновления.
Рискну поинтересоваться ещё об одной функции,
ПОИСКПОЗХ, чем она интересна?
Попробовал набрать формулу в новой ячейки, а её нет.
=_xlfn.XLOOKUP([@[C2_WBS_Spool]];Таблица2[Зона-линия-узел];Таблица2[№ письма];"";)
Тех.Поддержка сказала, что это связано с прекращением поддержки Win7. Посоветовали перейти на Win8/10
Я такую штуку сегодня обнаружил. Хотя 23 марта пользовался.
Пропали и остальные обновления - дин.диапазоны, сорт и т.д.
Примечание. В настоящее время эта функция доступна подписчикам Microsoft 365 на ежемесячном канале. Он будет доступен подписчикам Microsoft 365 на полугодовом канале, начинающемся в июле 2020 года. Для получения дополнительной информации о том, как функции распространяются на подписчиков Microsoft 365, см. Когда я получу новейшие функции для Microsoft 365.
Взято отсюда:
Обновил до конца.
Все новые фичи после выхода, только для подписчиков Office 365
PS: 10 месяц ждал, ЖЕСТЬ!!!
Если можно, то у меня к Вам вопрос-просьба: не могли бы Вы в очередной обновке Plex (если, конечно же, Вы планируете обновление) написать/встроить функцию ПросмотрХ - ну уж очень хочется. Умел бы сам писать udf или (как там правильно?) пользовательские функции давно бы написал, но "не цыган я, не цыган"©:D
Присоединяюсь к просьбе, было бы здорово, если бы формула появилась в Plex. Установили MS Office 2019, к сожалению, формулы не оказалось.(((
=ЕСЛИ(ПРОСМОТРX(...)=0;"";ПРОСМОТРX(...))
Уважаемый Гуру.
Подскажите может не там ищу.
Вопрос в следующем: как осуществить выборку из массива по определённому набору условий, включающему в себя просмотр значений по условию подстановочных символов (звёздочка = любое количество любых символов) и ? (вопросительный знак = один любой символ). Результат - массив, соответствующий всем (нескольким) требованиям.
Если нагляднее из Вашего примера:
=Выборка([Товар];[Упаковка]="паке*";[Страна]="Росси?"
результат массив из двух значений {Морковь,Гречка} (для последующих вычислений)
либо второй вариант (как с обычными диапазонами)
([Упаковка="паке*"*([Страна]="Росси?"или
{(c4:c18="паке*"*(d4:d18="Росси?"*1} результат массив (диапазон) в 18 значений = все 0, кроме 1-го и 10-го там 1
с символами подстановки не работает
Очень буду признателен за помощь, нужна именно формула, без макроса. Его я принципиально не вставляю в книгу, хотя там кода VBA всего 5 строк
файл примера в этой
Как на последнем изображении функция останавливается когда она находит значение в данном случае стоимости отгрузки 813 500.
И не имеет значение ячейка пустая или нет.
Вопрос в том как сделать так чтобы она не останавливалась. Чтобы функция пропускала пустые ячейки.
можно на примерах в умной таблице?
Что быстрее, ПРОСМОТРX() или ФИЛЬТР()?
если просто открыть файл то - ошибка имя?
если открываю файл через открытую новую книгу - открыть и выбрать этот файл - все работает...но сохраняя даже под другим именем и в другие места не приводит к тому что напрямую открывая все работает... так же ошибка имя?
=_xlfn.XLOOKUP([@Наименование];Таблица1[Наименование];Таблица1[Цена])
Либо это старая версия Excel, либо могли откатиться какие-то обновления, содержащие эту функцию.
=ЕСЛИ(ПРОСМОТРX(...)=0;"";ПРОСМОТРX(...))
Не лучшее решение: требует написания функции 2 раза. Но рабочее.
Может быть кто-то нашёл более короткий вариант обхода этой «особенности»?
Подскажите пожалуйста как "зафиксировать столбец" с данными при протягивании функции по столбцам при использовании имен столбцов?
У меня в первом столбце таблицы куда подтягиваю данные ИНН, 2-5 столбцы разные данные из другой таблицы.
При "протягивании вправо" @ИНН меняется на навзание следкющего столбца
XLOOKUP([@ИНН]
Это можно сделать формулой, ПРОСМОТРХ ?
Через ВПР и ИНДЕКС и ПОИСКПОЗ у меня ничего не вышло.
Заранее благодарен!
Только вот такой вопрос возник, как правильно написать формулу, если у имеется два критерия поиска?
Как это реализовать?
в одной книге?
Подскажите, если не затруднит, способ применить её для сбора значений с разных листов
в одной книге?