Функция ВПР (VLOOKUP) скоро отпразднует юбилей 10 лет, что для временного цикла программного обеспечения сопоставимо с выходом на пенсию.
Перед разработчиками из Microsoft стояла задача подобрать достойную замену, и они не ударили в грязь лицом, выпустив функцию ПРОСМОТРX (XLOOKUP), которая представляет собой не только улучшенную версию ВПР, но ещё и замену ГПР, ИНДЕКС и ПОИСКПОЗ. Если хотите узнать о других полезных функциях Excel, то рекомендуем записаться на бесплатный онлайн-курс «Аналитика в Excel».
Пару слов про функцию ПРОСМОТРX
Прежде чем приступать к работе с функцией ПРОСМОТРХ, нужно учесть 3 момента:
1. Версию Excel: на данный момент, осень 2020 года, ПРОСМОТРX доступен только пользователям Office 365, хотя поговаривают, что эту брешь залатают в Standalone-версии Microsoft Office 2021.
2. Буква Х в названии функции – английская, поэтому велика вероятность ошибки при дотошном вводе; лучше всего использовать автозавершение ввода функций с помощью клавиши Tab.
3. Перебирать аргументы и диапазоны (не только в ПРОСМОТРХ, но и в других функциях) намного быстрее с зажатой клавишей Ctrl, чем с помощью ручного ввода точек с запятой.
Про атрибуты и возможности функции мы поговорим уже в боевых условиях, разбираясь с тем, что же всё-таки умеет делать ПРОСМОТРХ.
Левый ВПР? Я могу орудовать, где угодно!
Наверняка большинство из вас знает, что ВПР выдаёт ошибку, когда нужно забрать данные левее искомой ячейки. Для ПРОСМОТРХ это не проблема, ведь функция считает везде, где только заблагорассудится.
К примеру, на одном листе у нас имеется список сотрудников и годовые оклады, при этом нужно посчитать их бонусы, забрав желаемые данные со второго листа.
Загвоздка заключается в том, что столбец с процентами бонусов находится левее столбца с реквизитами сотрудников, по которым как раз и будет осуществляться поиск.
«Загвоздка?!» – удивляется ПРОСМОТРХ. «Да ещё чего!».
Специально используем «Мастер функций» комбинацией Shift+F3 (или кнопочка Fx слева от строки ввода формул), чтобы показать аргументы.
Искомое значение – это ячейка А2, то есть Gary Miller, с него мы начнём выводить бонусы.
=ПРОСМОТРX(A2;Лист2!$C$2:$C$26;Лист2!$B$2:$B$26;0;0)
Просматриваемый массив – это столбец с сотрудниками на втором листе, то есть где функция ищет искомое значение. Закрепляем абсолютными ссылками.
Возвращаемый массив – тот самый столбец бонусов, которые мы хотим достать. Тоже забираем его со второго листа, зафиксировав с помощью F4.
Если ничего не найдено – уникальный и необязательный аргумент, позволяющий задать любое значение при неудачных результатах поиска, например, пробел (“ “) или 0, можно пропустить.
Режим сопоставления – классическая указка Excel для поиска точных или приблизительных значения. В данном случае выбираем 0 – точный поиск.
Протягиваем результат в ячейке ниже и сразу обращаем внимание на 0 – это те сотрудники, которых ПРОСМОТРХ не обнаружил в списке.
Кстати, если изначально функция везде выдаёт вам нули, то нужно проверить формат ячеек и проставить там числовой
Чтобы посчитать годовой оклад, можно умножить наши результаты на показатели столбца В, то есть дописываем в формулу *B2:
=ПРОСМОТРX(A2;Лист2!$C$2:$C$26;Лист2!$B$2:$B$26;0;0)*B2
Протягиваем и получаем максимальный бонус сотрудников.
Иди домой, ГПР!
Раз мы сказали о том, что ПРОСМОТРХ умеет работать в любых направлениях, то и с заменой ГПР, которая считает по строкам, тоже проблем не будет.
Заберём аналогичные данные для бонуса по сотрудникам, как делали ранее, но теперь обратимся к горизонтально развёрнутой табличке на другом листе. Естественно, строка с бонусом находится выше строки с сотрудниками, что в случае с обычным ГПР нам принесло бы ошибку. Сейчас же мы действуем по накатанной схеме:
=ПРОСМОТРX(A2;Лист3!$B$3:$Z$3;Лист3!$B$2:$Z$2;0;0)
Указываем ячейку с сотрудником. Затем выбираем диапазон с сотрудниками, в котором ПРОСМОТРХ находит искомое значение. Фиксируем по нажатию F4.
Далее указываем строку с бонусами, тоже фиксируем через F4.
Если ничего не найдено, ставим 0.
Точность поиска – тоже 0, точный поиск.
Получаем идентичный результат, который мы можем с чистой совестью перемножить на годовое жалованье сотрудников. Добавляем к формуле *B2:
=ПРОСМОТРX(A2;Лист3!$B$3:$Z$3;Лист3!$B$2:$Z$2;0;0)*B2
В итоге мы должны получить те же самые значения, что и с прошлой таблицей. Тоже перемножаем их на годовой оклад, дабы узнать максимальный бонус.
Кажется, на нём были очки…
Представим, что необходимо достать все реквизиты сотрудника, но мы знаем лишь его фамилию (или какие-то другие отличительные знаки). Попробуем извлечь данные человека по фамилии Willard в отдельной ячейке.
Если мы начнём поиск с ячейки, содержащей одну только фамилию Willard, выберем столбец с сотрудниками в качестве искомого и возвращаемого массива, то… получим ошибку. В идеале наша формула должна выглядеть следующим образом, смотрите:
В первый аргумент, перед искомой ячейкой G2, дописываем “*”. Звёздочка – это служебный символ, которого мы сцепляем амперсандом (&) с ячейкой G2 и таким образом говорим программе, чтобы осуществлялся поиск не только Willard, но и всего остального содержимого ячейки до Willard. То есть первый аргумент у нас примет вид: “*”&G2
Второй аргумент – искомый массив, то есть столбец с именами: A2:A19
Третий аргумент – возвращаемый массив, то есть опять столбец с именами: A2:A19
Четвёртый аргумент – пропускаем
Пятый аргумент – крайне важный в нашем случае. Сейчас мы должны выбрать цифру 2, которая учитывает поиск по служебным символам, то есть обращает внимание на введённую звёздочку в первом аргументе
=ПРОСМОТРX("*"&G2;A2:A19;A2:A19;;2)
Вот мы и получили James Willard.
«Двойной ПРОСМОТРХ» или «Зависимые выпадающие списки»
Для создания зависимых выпадающих списков, то есть, когда по значению из одного списка открываются соответствующие значения другого, больше не нужно хитрых комбинаций с формулами. Хватит ПРОСМОТРХ и парочки списков.
Сперва добавим в нашу таблицу новый столбец под названием «Итоговый платёж», в котором будет осуществляться суммирование столбцов B и C.
Теперь добавим выпадающий список в ячейку G7.
Вкладка «Данные» – «Проверка данных». Тип данных – список.
В поле «Источник» указываем диапазон с нашими сотрудниками из столбца А. Жмём ОК.
Проверяем. Список работает.
Ниже, в ячейке G8, создаём второй выпадающий список, вот только в поле «Источник» указываем заголовки столбцов из шапки таблицы, то есть протягиваем строку от Годового оклада до Итогового платежа. Нажимаем ОК.
Тоже работает. Едем дальше.
Всё готово для встраивания двойного ПРОСМОТРХ.
Переходим в ячейку G9, хотя вы можете выбрать абсолютно любую ячейку.
Начинаем вводить формулу.
Мы ищем имя из выпадающего списка выше, то есть ячейка G7 – это первый аргумент.
Затем выбираем диапазон сотрудников – это второй аргумент.
В качестве возвращаемого массива выбираем все остальные столбцы, потому что нам понадобятся все данные. И здесь – внимание!
На место третьего аргумента первой функции ПРОСМОТРХ мы пишем ещё один ПРОСМОТРХ. Уже в нём указываем первым аргументом ячейку F8 (там перебираются заголовки столбцов).
Второй аргумент – это шапка таблицы.
Третий аргумент – уже обозначенный диапазон всех столбцов, кроме сотрудников (разумеется, без шапки).
Обойдёмся без поразительной точности и закрываем формулу.
Готово. Теперь, в зависимости от выбранного сотрудника в первом списке, переключаются его показатели в различных столбцах из второго. Шикарно!
Как насчёт подсветки выбираемых имён?
В качестве бонуса можете прикрутить сюда условное форматирование, чтобы оно подсвечивало строку по выбранному имени. Мы же смотрим отчётность, это было бы крайне удобно!
Если мы должны найти строку по определённому показателю, то, естественно, выбор падёт на функцию ПОИСКПОЗ. Искать мы будем позицию по имени сотрудника в ячейке F7 и подсвечивать ту строку таблицы, в которой это имя/фамилия находится. Получится очень эффектно.
Обводим всю нашу таблицу. Переходим на вкладку «Главная», потом «Условное форматирование», затем «Создать правило».
Выбираем «Использовать формулу для определения форматируемых ячеек».
Сперва зададим формат. Зальём каким-нибудь цветом. Окрасим шрифт. Должно выйти неплохо. Теперь переходим к прописыванию формулы.
=ПОИСКПОЗ($A1;$F$7;0)
Обратите внимание, что в первом аргументе (искомое значение) мы фиксируем только столбец, поскольку нам нужно, чтобы функция перемещалась только по строкам.
$F$7 – наша зафиксированная ячейка с именем из выпадающего списка.
0 – просматриваемый массив, то есть ПОИСКПОЗ находит первое значение, равное искомому.
Нажимаем ОК и ОК далее.
Переключаем имена и любуемся подсветкой.
ПРОСМОТРХ вместо задания условий
Возникла ситуация, когда следует начислить определённые бонусы в зависимости от жалования сотрудников. Отдельная маленькая табличка уже находится на листе. В одном столбце – ориентировочное жалование, в другом – бонусы в процентах.
Если зарплата равна 10 000 – никакого бонуса, 30 000 – 5% бонусом, 50 000 – 8% бонусом и так далее. Определим, кому какой бонус полагается с помощью ПРОСМОТРХ.
Создадим новый столбец «Новый бонус» на основном листе.
Пишем в первой ячейке нового столбца формулу:
Первым аргументом выбираем ячейку В2 из столбца с окладами сотрудников.
В качестве второго аргумента указываем столбец с жалованием из бонусной таблички с другого листа, то есть у нас будет Лист2!F13:$F$17.
Возвращаемый массив – уже бонусный проценты, то есть бонусный столбец из бонусной таблички с другого листа – Лист2!G13:$G$17.
Пропускаем аргумент, если мы ничего не находим, сейчас это необязательно.
И самое главное – пятый аргумент, который отвечает за точность поиска. Указываем там -1, то есть точное совпадение или следующее меньшее значение.
=ПРОСМОТРX(B2;Лист2!$F$13:$F$17;Лист2!$G$13:$G$17;;-1)
Теперь нужно проверить результаты.
Гари Миллеру досталось 10% бонусов. Идём на бонусную табличку и смотрим.
Его оклад составляет 60 000 – это 10%. Следующий оклад для бонусов уже 100 000 и 15% соответственно. Что сделал Excel: он нашёл 60 000 и затем отобрал следующее минимальное значение, то есть 10%.
Никаких условий и никаких подборов. Одна только функция ПРОСМОТРХ помогла нам решить целую вереницу задач.