Левый ВПР

153107 09.01.2017 Скачать пример

Необходимое предисловие

Если вы раньше не работали с функцией ВПР (VLOOKUP), то много потеряли очень рекомендую сначала почитать вот эту статью и посмотреть в ней видеоурок.

Проблема

Как многим известно, функция ВПР (VLOOKUP) может выдавать в качестве результата значения, которые находятся строго правее того столбца, где производится поиск. Ах, как было бы красиво, если бы третий аргумент этой функции (номер столбца, откуда выдаются значения) можно было бы задавать отрицательным, но - нет.

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

Проблема с левым ВПР

Стоимость по коду заказа найти легко - обычный ВПР тут поможет на раз-два. А вот как найти название товара по коду? На тренингах этот вопрос я чаще всего слышу в формулировке "а как сделать левый ВПР"?

Давайте разберем несколько способов.

Способ 1. Лобовая атака

Если следовать принципу Оккама и не усложнять без надобности, то можно просто скопировать нужный столбец правее (или сделать его ссылками) и использовать обычный ВПР:

Копируем столбец правее

Дешево и сердито, но требует ручного допиливания таблицы. Кроме того, часто бывают случаи, когда таблицу нельзя менять: она защищена паролем, это корпоративный шаблон, таблица в общем доступе и т.д. Тогда нужен другой подход.

Способ 2. Виртуальная перестановка столбцов функцией ВЫБОР

Если переставить местами столбцы на листе нельзя, то это можно сделать виртуально, т.е. "на лету" прямо в самой формуле. Для этого нам потребуется функция ВЫБОР (CHOOSE). Основное ее предназначение – выбирать нужный элемент из списка по заданному номеру. Ее, например, можно использовать для замены номера дня недели на его текстовый аналог:

Функция ВЫБОР

Ничего сверхъестественного, на первый взгляд, но тут есть пара хитрых моментов.

Во-первых, вместо текстовых названий выбираемых элементов списка ("пн", "вт" и т.д.) можно использовать адреса диапазонов. И тогда функция вернет ссылку на выбранный диапазон. Так, например, формула:

=ВЫБОР(2; A1:A10; D1:D10; B1:B10)

… выдаст на выходе ссылку на второй указанный диапазон (D1:D10).

Во-вторых, вместо простого одиночного номера извлекаемого элемента в первом аргументе функции ВЫБОР можно задать массив констант в фигурных скобках, например, так:

=ВЫБОР({1;2}A1:A10D1:D10B1: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)
  • выдавать результаты из любого столбца (левее или правее - не важно)
  • выдавать не только первое встретившееся значение, а нужное по порядку
  • можно задать, что вывести, если ничего не найдено вместо ошибки #Н/Д
VLOOKUPS

У такого способа два минуса: нужно сохранять файл с поддержкой макросов (XLSM) и скорость у любой макрофункции не очень высокая - на больших таблицах может ощутимо подтормаживать.

Но как один из вариантов - пойдет :)

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


10.01.2017 13:39:49
А почему не функция ПРОСМОТР? Он довольно объемная, но почему бы и нет, в случае, если заранее известно, в каком столбце искать ответ?
24.01.2017 17:21:48
Логично, правда, у ПРОСМОТР-а есть требование на сортировку по возрастанию вектора поиска.
26.01.2017 13:24:37
Ну, если об этом неудобстве помнить, вполне можно работать.
26.01.2017 13:11:15
Отличный способ. Спасибо.
Научите: Как можно с помощью индекс и поискпозиции выбирать значения из первого столбца, когда ищешь во втором столбце, но при этом нужно во втором столбце найти значения с максимальной датой в четвертом?
На вскидку приходит функция наибольший, но куда ее вставить....)))
12авпаввп23.01.17.
15бпакп20.01.17.
17свкапккап24.01.17.
19бкапкуап21.01.17.
ищемзначения из 1 столбца.
с17.
бздесь нужно чтобы нашлось 19, а не 15.
вот такая таблица
маршрутидпрефиксплатеж
запад2251458БР7652И16.01.2017
Кашира1252412БР7652И17.01.2017
С5-1253016БР7272И18.01.2017
С3-1254392БР7652И19.01.2017
Кашира1255031БР7249И20.01.2017
Кашира1257321БР7759И21.01.2017
СВ1-1257569БР7761И22.01.2017
Пушкино1259373БР7647И23.01.2017
Коломна1259591БР7315И24.01.2017
Кашира1260300БР7544И25.01.2017
вот так я ищу
префиксид
БР7652ИИНДЕКС(A2:D11;ПОИСКПОЗ(D20;C2:C11;0);ПОИСКПОЗ(E19;A1:D1;0))
но мне нужен результат с максимальной датой платежа а не первый по порядку

пример в файле
26.01.2017 13:12:02
блин, таблицы не вставились(((
20.12.2017 05:46:37
Пытаюсь с помощью второго способа (функции "ВЫБОР") реализовать не "левый ВПР", а "верхний ГПР". Вроде по аналогии всё должно работать, но не работает. Результат: #н/д.
Видимо "Выбор" скрепляет диапазоны только по столбцам, а по строкам не может. Или какая-то другая причина. Может кто знает ?
15.01.2018 00:39:23
Массив номеров на "вертикальный" заменили? ВЫБОР({1:2}; ...)
25.07.2019 11:38:21
Фукцию ВЫБОР можно использовать совместно с ВПР и если таблица широкая, ничего не надо переставлять справа на лево, но лень считать порядковый номер столбца (напрмер он будет 50-й по счёту) от крайнего левого столбца.
25.07.2019 11:40:09
Есть ли другой способ не считать порядковый номер столбца в таблице от крайнего левого столбца для функции ВПР?
Наверх