Левый ВПР
Необходимое предисловие
Если вы раньше не работали с функцией ВПР (VLOOKUP), то много потеряли очень рекомендую сначала почитать вот эту статью и посмотреть в ней видеоурок.
Проблема
Как многим известно, функция ВПР (VLOOKUP) может выдавать в качестве результата значения, которые находятся строго правее того столбца, где производится поиск. Ах, как было бы красиво, если бы третий аргумент этой функции (номер столбца, откуда выдаются значения) можно было бы задавать отрицательным, но - нет.
На практике же сплошь и рядом возникают ситуации, когда искать данные надо в столбце, который находится правее, а не левее столбца результатов, например:
Стоимость по коду заказа найти легко - обычный ВПР тут поможет на раз-два. А вот как найти название товара по коду? На тренингах этот вопрос я чаще всего слышу в формулировке "а как сделать левый ВПР"?
Давайте разберем несколько способов.
Способ 1. Лобовая атака
Если следовать принципу Оккама и не усложнять без надобности, то можно просто скопировать нужный столбец правее (или сделать его ссылками) и использовать обычный ВПР:
Дешево и сердито, но требует ручного допиливания таблицы. Кроме того, часто бывают случаи, когда таблицу нельзя менять: она защищена паролем, это корпоративный шаблон, таблица в общем доступе и т.д. Тогда нужен другой подход.
Способ 2. Виртуальная перестановка столбцов функцией ВЫБОР
Если переставить местами столбцы на листе нельзя, то это можно сделать виртуально, т.е. "на лету" прямо в самой формуле. Для этого нам потребуется функция ВЫБОР (CHOOSE). Основное ее предназначение – выбирать нужный элемент из списка по заданному номеру. Ее, например, можно использовать для замены номера дня недели на его текстовый аналог:
Ничего сверхъестественного, на первый взгляд, но тут есть пара хитрых моментов.
Во-первых, вместо текстовых названий выбираемых элементов списка ("пн", "вт" и т.д.) можно использовать адреса диапазонов. И тогда функция вернет ссылку на выбранный диапазон. Так, например, формула:
=ВЫБОР(2; A1:A10; D1:D10; B1:B10)
… выдаст на выходе ссылку на второй указанный диапазон (D1:D10).
Во-вторых, вместо простого одиночного номера извлекаемого элемента в первом аргументе функции ВЫБОР можно задать массив констант в фигурных скобках, например, так:
=ВЫБОР({1;2}; A1:A10; D1:D10; B1:B10)
Тогда на выходе мы получим два первых диапазона (A1:A10 и D1:D10), склеенных в единое целое.
И вот теперь все это можно вложить внутрь нашей ВПР, чтобы реализовать «левый поиск»:
От "классического ВПР" отличается, как видно, только тем, что диапазон задается склейкой двух столбцов Код заказа и Товар с помощью функции ВЫБОР. В остальном все привычно.
Минусы такого способа - это скорость (примерно в 5-7 раз медленнее обычного ВПР) и некоторая непривычность для коллег (а может это даже плюс!)
Способ 3. Связка функций ИНДЕКС и ПОИСКПОЗ
Если не держаться имеено за функцию ВПР, то можно использовать ее более мощный аналог - связку двух очень полезных функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH):
Функция ПОИСКПОЗ ищет заданное значение (С2, т.е. код нужного нам заказа) в одномерном диапазоне (столбце кодов в таблице C10:C25) и выдает в качестве результата порядковый номер ячейки, где нашла искомое - в нашем случае это будет число 4, т.к. код нужного нам заказа четвертый в таблице.
А затем в дело вступает функция ИНДЕКС, которая умеет извлекать данные из вертикального массива-столбца (названия товаров в B10:B25) по порядковому номеру (который предварительно нашла ПОИСКПОЗ). Таким образом, ИНДЕКС выдаст нам содержимое четвертой ячейки из столбца Товар, что и требовалось.
По сравнению с предыдущим способом, такой вариант гораздо быстрее пересчитывается (почти также быстро как обычный ВПР), что важно для больших таблиц.
Похожий пример (с видео) я разбирал ранее вот в этой статье. А уж про функцию ИНДЕКС можно говорить совсем долго :)
Способ 4. Функция СУММЕСЛИ(МН)
Если нужно извлечь из таблицы именно число (допустим, объем в литрах), то иногда проще использовать для реализации "левого ВПР" функцию выборочного суммирования СУММЕСЛИ (SUMIF) или ее старшую сестру - функцию СУММЕСЛИМН (SUMIFS):
Минусы такого подхода очевидны - он работает только для чисел и, при условии, что в столбце нет повторяющихся значений. Если есть дубликаты (несколько заказов с одинаковым кодом), то эта функция сложит все объемы, а не выдаст первый, как это сделала бы ВПР. Ну, и скорость у такого способа тоже не очень - примерно в 3-4 раза медленнее, чем обычный ВПР.
Большая статья про функции выборочного подсчета по одному или нескольким условиям есть тут.
Способ 5. Готовая макрофункция из PLEX
Если не пугает использование макросов, то можно использовать готовую пользовательскую функцию VLOOKUPS на Visual Basic, которая входит в состав последней версии моей надстройки PLEX для Microsoft Excel. По сравнению с обычной ВПР она умеет:
- искать по нескольким столбцам сразу (до 3)
- выдавать результаты из любого столбца (левее или правее - не важно)
- выдавать не только первое встретившееся значение, а нужное по порядку
- можно задать, что вывести, если ничего не найдено вместо ошибки #Н/Д
У такого способа два минуса: нужно сохранять файл с поддержкой макросов (XLSM) и скорость у любой макрофункции не очень высокая - на больших таблицах может ощутимо подтормаживать.
Но как один из вариантов - пойдет :)
Ссылки по теме
- Что такое функция ВПР (VLOOKUP) и как ее использовать для поиска и подстановки значений
- Как вывести сразу все значения, а не только первое (Мульти ВПР)
- Как реализовать двумерный поиск (ВПР 2D)
Научите: Как можно с помощью индекс и поискпозиции выбирать значения из первого столбца, когда ищешь во втором столбце, но при этом нужно во втором столбце найти значения с максимальной датой в четвертом?
На вскидку приходит функция наибольший, но куда ее вставить....)))
Видимо "Выбор" скрепляет диапазоны только по столбцам, а по строкам не может. Или какая-то другая причина. Может кто знает ?