Для поиска данных и переноса их из одной таблицы в другую чаще всего используют функцию ВПР. У этой функции есть «заменитель» - связка функций ИНДЕКС и ПОИСКПОЗ, которая лишена некоторых недостатков функции ВПР, но имеет свои собственные. В Excel 365 и начиная с Excel 2021 появилась функция ПРОСМОТРX (XLOOKUP), которая выполняет те же задачи по поиску данных, но имеет множество преимуществ перед ранее перечисленными функциями. Давайте на примерах рассмотрим работу с функцией ПРОСМОТРX.
Классический пример
Пожалуй, классикой применения функций поиска будет подтягивание цены из прайс-листа в чек.
Стандартное решение с функцией ВПР выглядит так - указываем значение, которое мы ищем (А3), затем диапазон, в котором это значение нужно найти и из которого нужно будет вернуть значение, соответствующее искомому (G3:I7), далее указывается номер столбца выделенного диапазона, из которого значение нужно будет вернуть и в последнем аргументе задается тип поиска - в большинстве случаев указывается ноль, так как ищется точное совпадение.
У многих пользователей, которые начинают применять ВПР, возникают затруднения с пониманием логики работы этой функции. В первую очередь неочевиден выбор диапазона, ведь он обязательно должен начинаться со столбца, в котором происходит поиск. Также этот диапазон включает в себя все промежуточные столбцы, даже если они не нужны. Ну а если формулу предполагается копировать в ячейки ниже, то обязательно нужно фиксировать диапазон. Про это часто забывают, что приводит к «сползанию» диапазона при копировании и неверным результатам.
Функция ПРОСМОТРX в этом отношении абсолютно логична и интуитивно понятна, хоть и содержит целых шесть аргументов.
Правда только первые три из них обязательны, остальные можно использовать лишь при необходимости. Давайте решим ту же задачу с помощью ПРОСМОТРX. Кстати, стоит сразу обратить внимание на то, что наименование функции именно «ПРОСМОТР-ИКС», то есть последняя буква это не русская «Х», а английская «X» (экс). Поэтому при наборе функции лучше задействовать автозаполнение, то есть после начала ее ввода выбрать из подсказки предложенный вариант и нажать на клавишу Tab.
В первом аргументе функции, как и в ВПР, указываем значение, которое будем искать. Далее указываем диапазон, в котором будет осуществляться поиск. В отличии от функции ВПР здесь указывается именно диапазон для поиска, то есть в нашем случае это лишь столбец с наименованием товаров. И последний обязательный аргумент - это диапазон, из которого значение нужно вернуть. Нам нужна цена, поэтому выбираем соответствующий столбец.
При этом столбец с возвращаемым значением может находиться справа или слева от диапазона поиска, что как раз невозможно задать в функции ВПР и для решения подобных задач ранее приходилось прибегать к связке функций ИНДЕКС и ПОИСКПОЗ.
По мне так все очень просто и логично и остается лишь удивляться, почему такой алгоритм сразу не был внедрен в функцию ВПР.
Если предполагается протягивать формулу на ячейки ниже, то также не забываем фиксировать диапазоны - здесь все также, как и в ВПР. Правда есть одно существенное отличие - поскольку функция ПРОСМОТРX появилась в версиях Эксель, в которых уже реализованы динамические массивы, то она их в полной мере поддерживает.
Динамические массивы
С помощью ПРОСМОТРX можно производить поиск не какого-то конкретного значения, а сразу массива значений в другом массиве. Для этого в качестве первого аргумента функции указываем нужный диапазон (A13:A16) и на выходе получаем массив соответствующих значений из указанного диапазона.
Соответственно в таком случае ссылки на диапазоны фиксировать не нужно.
При этом динамические диапазоны можно задействовать и для получения данных из исходной таблицы. Например, нужно по фамилии сотрудника подтянуть информацию о нем. Для этого в качестве диапазона задаем не один столбец, а все необходимые.
В результате подтянется вся строка из исходной таблицы.
Зачастую данные нужно получить в другом виде, например, подтянуть телефон, должность и оклад и именно в такой очередности. Для решения задачи можно задействовать функцию ВЫБОР. В качестве индекса укажем массив констант, а за ними закрепим соответствующие столбцы исходной таблицы.
Ну а если данные нужно вывести в виде карточки, то есть не строкой, а столбцом, то задействуем функцию транспонирования ТРАНСП, обернув ей ранее созданную формулу.
Вот так просто решаются задачи, в которых ранее нужно было задействовать довольно громоздкие формулы со множеством вложенных функций, а порой и с создавать вспомогательные столбцы.
Перехват ошибки
Теперь поговорим о необязательных аргументах функции ПРОСМОТРX.
Есть та же таблица с данными о сотрудниках и вторая, в которую нужно подтянуть оклады по фамилии.
Задействуем ПРОСМОТРX и динамические массивы. В итоге получаем две ячейки с ошибкой.
Произошло это потому, что указанных сотрудников нет в исходной таблице. В таких ситуациях ранее применялась функция ЕСЛИОШИБКА, которой оборачивали формулу и во втором ее аргументе указывали, какое значение нужно вывести в случае появления ошибки.
Сейчас же этого делать не нужно, так как эта опция уже есть в функции ПРОСМОТРX, а точнее за это отвечает ее четвертый аргумент. Указываем значение, которое появится в ячейке если ничего найдено не будет. Это может быть надпись, вроде «нет данных» или какое-то число, например, 0.
Режим сопоставления
Пятый аргумент функции является аналогом аргумента интервальный просмотр функции ВПР, вот только он может принимать пять значений.
По умолчанию используется значение "0" - точное совпадение. Оно используется в большинстве случаев и указывать его необязательно. А вот второе значение аргумента "-1" задействуется также, как и приблизительное совпадение в функции ВПР.
Например, необходимо рассчитать суммы к выплате с учетом бонусов. Бонус - это некоторая денежная сумма, зависящая от стажа сотрудника.
Такая задача решается и с помощью ВПР, но только таблица с бонусами в этом случае должна быть отсортирована по возрастанию. Для функции ПРОСМОТРX этот момент не принципиален, что я и подчеркнул, указав вразброс бонусы в соответствующей таблице.
Мы будем искать стаж сотрудника в соответствующем столбце бонусной таблицы, а затем вернем значение надбавки. Четвертый аргумент в данном случае необязателен, поэтому его пропущу. В качестве пятого аргумента укажем «-1», то есть в таблице будет искаться либо точное значение стажа, либо ближайшее меньшее значение. По аналогии можно искать и ближайшее большее, возможно в некоторых ситуациях это будет полезным.
В таблице с бонусами указана строчка с нулевым стажем, но можем обойтись и без нее. Тогда в расчете появится ошибка - Н/Д (нет данных). Воспользуемся четвертрым аргументом функции, чтобы заменить ошибку на 0 и получим тот же результат.
И есть еще один режим сопоставления под кодом "2". Этот режим имеет несколько иную логику. В нем учитываются постановочные знаки.
Подстановочные знаки — это символы, которые могут заменять собой другие символы. Наиболее часто используется символ звездочки (*), замещающий собой любое количество знаков подряд.
Вернемся к рассмотренному ранее примеру. Есть заявка и прайс-лист. Товар в заявке указан иначе, нежели в прайс-листе.
Вот в таких ситуациях и придут на помощь символы подстановки. Нам нужно в тексте найти слово, указанное в заявке, то есть данное слово может быть окружено любым количеством других символов.
Поскольку символ подстановки является текстовым, то он указывается в кавычках, а сцепить его с основным словом можно с помощью символа амперсанд (&).
Если сейчас нажать Enter, то функция вернет ошибку. Чтобы знаки подстановки учитывались, необходимо активировать соответствующий режим.
Режим поиска
И последний аргумент функции ПРОСМОТРX позволяет задать режим поиска.
Функция ВПР ищет лишь первое совпадение, а в ряде случаев нужно найти последнее. Например, в таблице заказов необходимо определить сумму последнего заказа конкретного покупателя.
В случае с ВПР приходилось прибегать к различным ухищрениям, сейчас же достаточно задать режим поиска. Обычно задается один из двух - в первом случае возвращается первое найденное совпадение, во втором - последнее.
Поиск по двум столбцам
Ну а теперь давайте разберем примеры использования функции ПРОСМОТРX. Так в только что рассмотренной задаче мы искали последний заказ определенного покупателя, но часто нужно производить поиск по двум столбцам, то есть найти последний заказ определенного товара определенным покупателем. Функция ищет лишь одно значение, но решить задачу довольно просто и этот трюк используется и с другими функциями Эксель.
И заказчик, и наименование товара являются текстовыми значениями, поэтому если мы их объединим с помощью функции СЦЕПИТЬ или символа амперсанд, то получим одно значение, которое и сможем использовать в поиске. В приведенном примере искомое значение будет "МагнитСвекла".
При этом поиск мы должны производить также в сцепленных столбцах. В таком виде значения столбцов будут также попарно объединены.
Таким образом мы можем задать необходимое количество критериев поиска, например, добавить еще и город, чтобы поиск производился по трем столбцам.
Поиск в двух направлениях
Функция ВПР осуществляет поиск лишь в вертикальном направлении, для поиска по горизонтали приходится использовать функцию ГПР. В этом отношении функция ПРОСМОТРX универсальна и может осуществлять поиск значения как в столбце, так и в строке. По этой причине очень удобно использовать функцию ПРОСМОТРX для поиска в двух направлениях.
Рассмотрим довольно распространенную задачу. Есть данные по объектам за определенные месяцы. С помощью выпадающих списков можно выбрать интересующий месяц и объект и из таблицы должно подтянуться соответствующее значение.
Задействуем функцию ПРОСМОТРX. Сначала привычным образом будем искать объект в первом столбце.
Возвращаются значения всей строки, соответствующей выбранному объекту, то есть мы получили горизонтальный динамический массив, из которого нужно выбрать лишь одно значение, соответствующее выбранному месяцу. Поэтому обернем полученную формулу еще одной функцией ПРОСМОТРX, которая и будет производить поиск значения в полученном горизонтальном массиве. Нам нужно найти месяц в строке заголовка, а затем вернуть соответствующее значение из ранее полученного горизонтального массива.
Такая довольно простая формула позволит осуществлять поиск значения в двух направлениях - как по вертикали, так и по горизонтали.
Возвращение ссылки
И в заключение рассмотрим пример, в котором функция ПРОСМОТРX применяется абсолютно неочевидным способом. Дело в том, что функция ПРОСМОТРX возвращает не только искомое значение. Она также может возвращать адрес интересующей нас ячейки.
Чтобы в этом убедиться давайте обернем ранее созданную формулу функцией ЯЧЕЙКА, которая возвращает сведения об ячейке, на которую указана ссылка. Нам нужен адрес указанной ячейки, поэтому укажем его в первом аргументе функции, а вот ссылку на саму ячейку как раз возвращает функция ПРОСМОТРX.
Как видим формула вернула адрес ячейки, из которой ранее возвращалось значение. То есть помимо значения функция может возвращать и адрес найденной ячейки. Но как это можно использовать?
А давайте несколько изменим отчет. Нам нужно получать общую сумму по выбранному объекту за указанный интервал времени. То есть мы выбираем месяцы в списке и получаем сумму всех соответствующих значений.
Фактически нам нужно будет просуммировать диапазон значений, соответствующий выбранным месяцам и в этом как раз поможет функция ПРОСМОТРX.
Сначала упростим задачу и будем суммировать значения только для первого объекта, то есть пока не будем задействовать третий выпадающий список.
Для суммирования воспользуемся стандартной функцией СУММ. Ее аргументом должен быть диапазон значений, а его легко получить описанным ранее способом. То есть с помощью функции ПРОСМОТРX получаем адрес первой ячейки со значением, соответствующим указанному месяцу, затем с помощью двоеточия формируем диапазон и получаем вторую его ссылку с помощью второй функции ПРОСМОТРX.
В итоге выбирая месяцы из списка мы получаем сумму значений первой строки. В правильности расчетов поможет убедиться строка состояния. Выбираем тот же диапазон ячеек и получаем тот же результат.
Ну а теперь доработаем формулу, расширив ее на всю таблицу и добавив выбор объекта. Для этого вложим функции ПРОСМОТРX по ранее рассмотренному алгоритму. При этом вложенная функция ПРОСМОТРX будет одинакова в обоих вариантах, поэтому можем ее скопировать и вставить вместо ранее выбранного диапазона.
Теперь можно выбирать значения из всех трех выпадающих списков и получать данные по определенному объекту за выбранный временной интервал.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм