Любой пользователь, который работает с функцией ВПР (LOOKUP), через некоторое время понимает, что не очень удобно работать с функцией, которая производит поиск только справа от столбца, в котором находится искомое значение. Функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) могут разрешить эту проблему, но в целом формула будет намного длиннее и труднее по сравнению с более простой ВПР.
Пользовательская надстройка rLOOKUP работает как ВПР, но позволяет производить обратный поиск. Функция просто использует методы указательным и матч в VBA. Она предоставляется в Excel надстройка для простоты использования.
Скачать надстройку
Сохраните файл на компьютере и установить его в Excel, выбрав Файл -> Параметры -> Надстройки . Нажмите на кнопку Перейти... , в открывшемся окне выберите Обзор . Выберите сохраненный файл и нажмите кнопку OK, чтобы завершить установку. После установки появится сообщение "Reverse_Lookup" в списке доступных надстроек и функция rLOOKUP становится доступной во всех книгах.
Описание функции
Аргументы для функции rLOOKUP такие же, как и для функции ВПР, см. скриншоты ниже. Чтобы выполнить обратный поиск, просто введите отрицательное число в аргумент "Col_Index_Num" (смотри примеры ниже). Формула будет Lookup в направлении влево.
Одно из различий между этими двумя функциями, что если Интервальный_просмотр опущен в ВПР, то результата точно не будет. Однако обратное верно для rLOOKUP, по умолчанию эта функция возвращает точное значение. Опять же, это потому, что чаще всего пользователи хотят получить значение в результате поиска. Значения ошибок, возвращаемые функцией rLOOKUP аналогичны тем, которые возвращает ВПР.
Пример функции
Следующие данные Excel показывает адреса офисов, имена сотрудников и должность.
Для ответа на этот запрос "Кто работает офисе B43?"мы можем использовать функцию ВПР:
ВПР ("B43"; A1:C6;2; ЛОЖЬ) возвращает "Dilbert".
Тем не менее, если мы хотим знать, "Где находится офис Дилберта?", то ВПР не будет работать. Вот тут пригодится функция rLOOKUP:
rLOOKUP ("Dilbert"; A1:B6, -2).
Обратите внимание, в аргументах номер столбца указывается отрицательным число -2, что позволяет производить поиск в колонке слева.
Вообще, функция работает в обоих направлениях:
rLOOKUP ("B43";A1:C6;2) возвращает "Dilbert" и
rLOOKUP ("Dilbert"; A1:B6; -2) возвращает "B43".
Другие примеры приведены ниже:
Не забудьте сначала установить надстройку, в противном случае будет возвращаться ошибка #NAME? .
Как работает функция
Функция использует функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) для запуска обратного поиска.
Исходный код для функции смотрите ниже.
Function rLOOKUP(Lookup_value, Table_array As Range, Col_index_num As Integer, Optional Range_lookup As Boolean)
"
"Custom function that allows forward and reverse vertical lookups
"Works like VLookup for positive Col_index_num, and does reverse lookups for negative Col_index_num
"Unlike VLOOKUP, it looks for the exact value by default
"
"Nitin Mehta, www.Engineers-Excel.com
"
"
Dim Source_Col As Range, Dest_Col_num As Integer
"The Source Column is the Column where the Lookup_value is located. It is the leftmost column if
"Col_index_number is positive. If Col_index_number is negative, it is the rightmost column
Set Source_Col = Table_array.Columns(IIf(Col_index_num > 0, 1, Table_array.Columns.Count))
"Dest_col_num is the column number within the Table_array range from where we have to pick up the entry against the Lookup_value
Dest_Col_num = IIf(Col_index_num > 0, Col_index_num, Table_array.Columns.Count + Col_index_num + 1)
"Use MATCH and INDEX functions for the Lookup.
"Use of CVErr(xlErrRef) suggested by Brian Canes
rLOOKUP = IIf(Dest_Col_num < 1, CVErr(xlErrRef), Application.Index(Table_array, Application.Match(Lookup_value, Source_Col, Range_lookup), Dest_Col_num))
End Function
Оставьте свой комментарий!
Многие знают и часто пользуются. Но при этом у нее есть два значительных недостатка, например как сделать «Левый ВПР «. Я использую сам ВПР, только если нужно сделать что-то быстро. В файлах для «регулярного» использования я делаю конструкцию ИНДЕКС и ПОИСКПОЗ. Чем она лучше?
- При добавлении столбца в таблицу данных не нужно менять номер столбца в самой формуле (как в ВПР). Столбец подвинется автоматом
- Можно сделать ВПР наоборот, т.е. сделать выбор из таблицы справа-налево. В ВПР первый столбец всегда должен быть для поиска.
Как все это сделать, читаем ниже:)
Использовать буду пример из . Для того, чтобы лучше понять применение этой конструкции в сравнение с ВПР:
Как просто заполнить, если видишь формулу первый раз?
Сперва определитесь где таблица изначальная а где для получения данных! Поэтапно, что заполнять.
- Вставляем формулу в нужную область таблицы
- На место $G:$G поставьте те ячейки, в которых должно быть найдено значение и соответственно, должно появиться в результате. Мы ищем фамилию, значит в ищите столбец с фамилиями в начальной таблице.
- Замените $J:$J на в зависимости от которых должны вернуться значения в ячейку. Нам нужны Фамилии в зависимости от ТС — вставляем те ТС около которых должны появиться значения.
- Вместо $H:$H заполняем столбец с в котором нужно найти соответствующее значение. Т.е. мы ищем Фамилию по ТС, значит вставляем столбец с ТС в изначальной таблице.
Если вам интересно для чего в формуле знаки $ читайте
ИНДЕКС и ПОИСКПОЗ. Что это за функции?
ИНДЕКС и ПОИСКПОЗ — очень сильные функции, которые в комбинации с другими дают отличный результат.
ИНДЕКС(массив; номер_строки; номер_столбца)
Возвращает значение на пересечении указанной строки и столбца определенного диапазона. Т.е. изначально она работает с двумерными массивами.
Выберя массив данных J1:K4 и задав номер строки и столбца равным двум, мы получили соответствующее значение.
Посмотрев на изначальную формулу
ИНДЕКС($G:$G;ПОИСКПОЗ($J:$J;$H:$H;0);1)
Мы увидим, что вместо второго аргумента (номер строки) у нас формула ПОИСКПОЗ. Что тут она делает?
ПОИСКПОЗ — это поиск по значению. Функция ищет заданное значение в строке или в столбце и возвращает ее порядковый номер (от начала диапазона). Т.е. во втором аргументе функции ИНДЕКС мы находим нужный нам номер ТС, получаем его номер, например 2.
И уже в одномерном массиве $G:$G находим ячейку с номером строки = 2. Так это будет работать для каждой ячейки в столбце J.
Вот так вроде бы не очень сложная конструкция, но как я писал выше очень эффективная. Т.к. вам не придется постоянно менять номер искомого значения, как в ВПР и можно делать поиск как направо, так и на лево:)
Пишите комментарии, если вопросы остались.
Как всегда!
Поделитесь нашей статьей в ваших соцсетях:ВПР в Excel очень удобный и часто используемый инструмент для работы с таблицами как с базой данных и не только. Данная функция проста в освоении и очень функциональна при выполнении.
Благодаря гармоничному сочетанию простоты и функциональности ВПР пользователи активно ее используют в процессе работы с электронными таблицами. Но стоит отметить, что у данной функции достаточно много недостатков, которые ограничивают возможности. Поэтому ее иногда нужно использовать с другими функциями или вообще заменять более сложными. Для начала на готовом примере применения функции рассмотрим ее преимущества, а потом определим недостатки.
Как работает функция ВПР в Excel: пример
Функция ВПР предназначена для выборки данных из таблицы Excel по определенным критериям поиска. Например, если таблица состоит из двух колонок: «Наименование товара» и «Цена». Рядом находится другая таблица, которая будет искать в первой таблице по наименованию товара и получать значение соответствующей цены.
В поле «Исходное значение» вводим ссылку на ячейку под наименованием товара второй таблицы D3. В поле «Таблица» вводим диапазон всех значений первой таблицы A2:B7. В поле «Номер столбца» вводим значение 2, так как во втором столбце у нас находиться цена, которую мы хотим получить при поиске товара. И нажимаем ОК.
Теперь под заголовком столбца второй таблицы «Товар» введите наименования того товара по котором нам нужно узнать его цену. И нажмите Enter.
Функция позволяет нам быстро находить данные и получать по ним все необходимые значения из больших таблиц. Это похоже на работу с базами данных. Когда к базе создается запрос, а в ответ выводятся результаты, которые являются ответом на критерии запроса.
Функция ВПР в Excel и две таблицы
Немного усложним задание, изменив структуру и увеличив объем данных в таблице. Расширьте объем данных первой таблицы, добавив столбцы: «январь», «февраль», «март». Там запишем суммы продаж в первом квартале как показано на рисунке:
Как видите вторую таблицу так же нужно немного изменить, чтобы не потерять суть задачи.
Теперь нам нужно сделать выборку данных с помощью функции ВПР отдельно по товару и просуммировать продажи за первый квартал. Для этого переходим в ячейку H3 и после вызова функции заполняем ее аргументы следующим образом:
- Исходное значение: G3.
- Таблица: A2:E7. Диапазон нашей таблицы расширен.
- Номер столбца: {3;4;5}. Нам нужно с помощью функции обращаться одновременно к нескольким столбцам, поэтому значение данного аргумента будет взято в массив фигурными скобками. А номера столбцов следует перечислять через точку с запятой.
- Интервальный просмотр: ЛОЖЬ.
- Чтобы значения в выбранных столбцах суммировались, тогда всю функцию нужно поместить внутрь функции СУММ(). Вся формула в целом выглядит следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).
- После ввода данной формулы следует нажать комбинацию клавиш: CTRL+SHIFT+ENTER. Внимание! Если не нажать комбинацию этих клавиш формула будет работать ошибочно . В Excel иногда приходиться выполнять функции в массиве для этого нужно обязательно использовать клавиши: CTRL+SHIFT+ENTER при вводе функций. Тогда в строке формул все содержимое будет взято в фигурные скобки «{}», что свидетельствует о выполнении формулы в массиве.
Теперь вводите в ячейку G3 наименование товара, в ячейке H3 получаем сумму продаж в первом квартале по данному товару.
Происходит сравнение двух таблиц в Excel функцией ВПР и как только определяется совпадение запрашиваемых данных, сразу подставляется их значения для суммирования функцией СУММ. Весь процесс выполняется циклически благодаря массиву функций о чем свидетельствуют фигурные скобки в строке формул.
Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.
Стоит отметить, что главным недостатком функции ВПР является отсутствие возможности выбрать несколько одинаковых исходных значений в запросе.
Другими словами если в нашей таблице повторяются значения «груши», «яблока» мы не сможем просуммировать всех груш и яблок. Для этого нужно использовать функцию ПРОСМОТР(). Она очень похожа на ВПР но умеет хорошо работать с массивами в исходных значениях.
Функция ВПР в Excel позволяет данные из одной таблицы переставить в соответствующие ячейки второй. Ее английское наименование – VLOOKUP.
Очень удобная и часто используемая. Т.к. сопоставить вручную диапазоны с десятками тысяч наименований проблематично.
Как пользоваться функцией ВПР в Excel
Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.
Стоимость материалов – в прайс-листе. Это отдельная таблица.
Необходимо узнать стоимость материалов, поступивших на склад. Для этого нужно подставит цену из второй таблицы в первую. И посредством обычного умножения мы найдем искомое.
Алгоритм действий:
Нажимаем ОК. А затем «размножаем» функцию по всему столбцу: цепляем мышью правый нижний угол и тянем вниз. Получаем необходимый результат.
Теперь найти стоимость материалов не составит труда: количество * цену.
Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».
- Выделяем столбец со вставленными ценами.
- Правая кнопка мыши – «Копировать».
- Не снимая выделения, правая кнопка мыши – «Специальная вставка».
- Поставить галочку напротив «Значения». ОК.
Формула в ячейках исчезнет. Останутся только значения.
Быстрое сравнение двух таблиц с помощью ВПР
Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами.
Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу.
Функция ВПР в Excel с несколькими условиями
До сих пор мы предлагали для анализа только одно условие – наименование материала. На практике же нередко требуется сравнить несколько диапазонов с данными и выбрать значение по 2, 3-м и т.д. критериям.
Таблица для примера:
Предположим, нам нужно найти, по какой цене привезли гофрированный картон от ОАО «Восток». Нужно задать два условия для поиска по наименованию материала и по поставщику.
Дело осложняется тем, что от одного поставщика поступает несколько наименований.
Рассмотрим формулу детально:
- Что ищем.
- Где ищем.
- Какие данные берем.
Функция ВПР и выпадающий список
Допустим, какие-то данные у нас сделаны в виде раскрывающегося списка. В нашем примере – «Материалы». Необходимо настроить функцию так, чтобы при выборе наименования появлялась цена.
Сначала сделаем раскрывающийся список:
Теперь нужно сделать так, чтобы при выборе определенного материала в графе цена появлялась соответствующая цифра. Ставим курсор в ячейку Е9 (где должна будет появляться цена).
- Открываем «Мастер функций» и выбираем ВПР.
- Первый аргумент – «Искомое значение» - ячейка с выпадающим списком. Таблица – диапазон с названиями материалов и ценами. Столбец, соответственно, 2. Функция приобрела следующий вид: .
- Нажимаем ВВОД и наслаждаемся результатом.
Изменяем материал – меняется цена:
Так работает раскрывающийся список в Excel с функцией ВПР. Все происходит автоматически. В течение нескольких секунд. Все работает быстро и качественно. Нужно только разобраться с этой функцией.
Батьянов Денис на правах гостевого автора рассказывает в этом посте о том, как найти данные в одной таблице Excel и извлечь их в другую, а также открывает все секреты функции вертикального просмотра.
Использование функции СТОЛБЕЦ для указания колонки извлечения
Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов. При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная.
Создание составного ключа через &»|»&
Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.
Это моя первая статья для Лайфхакера. Если вам понравилось, то приглашаю вас посетить мой сайт , а также с удовольствием прочту в комментариях о ваших секретах использования функции ВПР и ей подобных. Спасибо. :)
