Продвинутые техники использования ПРОСМОТРX (XLOOKUP)

В 2020 году Microsoft выпустила обновление, добавляющее в Microsoft Excel 365 новую функцию ПРОСМОТРХ (XLOOKUP), пришедшую на замену классическим функциям поиска и подстановки типа ВПР (VLOOKUP), ГПР (HLOOKUP) и связке функций ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH). В отличие от предшественников, новая функция имеет простой и понятный синтаксис, умеет искать как по вертикали, так и по горизонтали, а также может искать снизу вверх, а не сверху вниз. Базовый синтаксис и возможности функции ПРОСМОТРX я уже подробно разбирал в этой статье с сопутствующим видеоуроком.

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

Базовый синтаксис

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

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

Также есть три дополнительных необязательных аргументы, которые нам здесь пока не нужны, но могут где-то вам пригодиться:

• что выводить, если ничего не нашли;
• режим сопоставления (точно, приблизительно с округлением в меньшую/большую сторону; с использованием символов подстановки типа "звёздочка" или "вопросительный знак"; с использованием регулярных выражений);
• режим (направление) поиска (сверху вниз или снизу вверх - для поиска первого или последнего найденного совпадения, если их несколько).

Множественные условия

Если необходимо найти и вывести совпадение не по одному, а по нескольким условиям, то можно использовать следующую конструкцию:

Здесь каждое условие в скобках проверяет по очереди все ячейки в заданных столбцах и возвращает в качестве результата массив из значений ИСТИНА (TRUE) или ЛОЖЬ (FALSE), соответственно. При параллельном перемножении этих массивов друг на друга, Excel автоматически преобразует ИСТИНУ в 1, а ЛОЖЬ в 0, так что на выходе мы получаем массив с единичками только там, где все условия выполняются. Невыполнение хотя бы одного из них даст нам 0 как один из множителей, что автоматом обнулит итоговый результат проверки тоже.

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

Искомое значение как массив

Ещё одна принципиально важная особенность новой функции ПРОСМОТРХ состоит в том, что в качестве первого её аргумента (что ищем) можно задавать не одну, а сразу несколько ячеек. Функция будет автоматически их перебирать, искать по каждой совпадение, а затем вернёт в качестве результата динамический массив с найденными результатами того же размера:

Единственное исключение - эта техника не применима к динамическим "умным" таблицам, т.к. массив из нескольких найденных значений формируется в каждой строке "умной" таблицы, так что результаты начинают накладываться друг на друга, что приводит к ошибке #ПЕРЕНОС (#SPILL): 

В этом случае лучше первым аргументом ПРОСМОТРX указывать всё же ссылку на одну ячейку в текущей строке (об этом сигнализирует символ @ перед именем столбца) - тогда проблем не будет:

=ПРОСМОТРX([@Код]; $B$2:$B$20; $C$2:$C$20)

Результаты как массив

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

Например, если мы хотим найти продажи пончо сразу во всех городах, то это легко реализовать, указав в качестве возвращаемого массива все столбцы-города, а не один, как раньше:

Само собой, получившийся на выходе динамический массив можно завернуть снаружи в требуемую агрегирующую функцию типа СУММ, СРЗНАЧ, МИН, МАКС и т.д. - тогда мы легко сможем вычислить, например, итог по всей строке с выбранным товаром:

=СУММ(ПРОСМОТРX(J3;A2:A15;C2:G15))

Двумерный поиск

Из предыдущего пункта следует ещё одна важная идея - поскольку функция ПРОСМОТРХ может возвращать, в общем случае, не одну ячейку, а массив, а также отлично работает и по горизонтали, то мы можем вложить одну функцию ПРОСМОТРХ в другую и реализовать, таким образом, двумерный поиск - по строке и по столбцу одновременно:

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

В старых версиях Excel для реализации подобной логики приходилось использовать более громоздкую конструкцию с функцией ИНДЕКС (INDEX) и вложенными в неё двумя функциями ПОИСКПОЗ (MATCH) для поиска номера строки и номера столбца извлекаемой ячейки:

=ИНДЕКС(C2:G15; ПОИСКПОЗ(J4;A2:A15;0); ПОИСКПОЗ(K4;C1:G1;0))

ПРОСМОТРX внутри СУММЕСЛИ

Ещё одна интересная техника их той же оперы - это использование функции ПРОСМОТРХ внутри функций выборочного подсчёта типа СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ и т.д. Это позволит динамически формировать диапазон суммирования, выбирая нужный столбец "на лету":

Здесь функция ПРОСМОТРX сначала находит столбец с данными по Тамбову, а затем функция СУММЕСЛИМН (SUMIFS) подсчитывает в нём сумму продаж по синим товарам.

Поиск в нескольких таблицах (листах)

Если таблица, в которой вы ищете совпадение, у вас не монолитная, а разбита на несколько подтаблиц (в т.ч. и по разным листам), то перед использованием функции ПРОСМОТРX эти фрагменты нужно сначала собрать в единое целое. В последних версиях Microsoft Excel это проще всего сделать с помощью новых функций вертикальной и горизонтальной склейки диапазонов - ВСТОЛБИК (VSTACK) и ГСТОЛБИК (HSTACK).

Представим, например, что исходные таблицы у нас разнесены на три листа: Лист1, Лист2 и Лист3:

Тогда формула для глобального поиска по всем листам может выглядеть так:

Если листов много, то проще будет использовать трёхмерные ссылки, чтобы задать сразу весь диапазон листов от первого до последнего и не прописывать ссылку на каждый лист в отдельности (только диапазон тогда нужно задать с запасом, чтобы в него точно уместились данные с любого листа):

Само собой, предполагается, что все таблицы в этом сценарии имеют одинаковую структуру по столбцам (но любое количество строк). Если столбцы в исходных таблицах могут располагаться в разном порядке, то задача сильно усложняется и требует либо более сложных формул, либо использования Power Query.

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

Ну, а если вы пока работаете на старых версиях Excel до 2021 года, где её ещё нет, то тоже не отчаивайтесь - ВПР и её аналоги никто не отменял :)

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

• Наследник ВПР - новая функция ПРОСМОТРX (XLOOKUP)
• Склейка диапазонов новыми функциями ВСТОЛБИК (VSTACK) и ГСТОЛБИК (HSTACK)
• Динамические массивы в Excel