Новые информационные технологии и программное обеспечение
  RSS    

20241004 240x200 d7cdff19a16e2826d9cff55196912690



Excel: надстройка для функции обратного ВПР, или как ВПР "сходить налево"

Любой пользователь, который работает с функцией ВПР (LOOKUP), через некоторое время понимает, что не очень удобно работать с функцией, которая производит поиск только справа от столбца, в котором находится искомое значение. Функции ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX) могут разрешить эту проблему, но в целом формула будет намного длиннее и труднее по сравнению с более простой ВПР. 

Пользовательская надстройка rLOOKUP работает как ВПР, но позволяет производить обратный поиск. Функция просто использует методы указательным и матч в VBA. Она предоставляется в Excel надстройка для простоты использования.

Скачать надстройку

Загрузите надстройку: .xlam файл (15 Кб).

Сохраните файл на компьютере и установить его в Excel, выбрав Файл -> Параметры -> Надстройки. Нажмите на кнопку Перейти ..., в открывшемся окне выберите Обзор. Выберите сохраненный файл и нажмите кнопку OK, чтобы завершить установку. После установки появится сообщение "Reverse_Lookup" в списке доступных надстроек и функция rLOOKUP становится доступной во всех книгах.

rLU

После установки надстройки функцию rLOOKUP можна найти Формулы -> Вставить функцию -> Категория: Определенные пользователем -> rLOOKUP. 

rLU1

Описание функции

Аргументы для функции rLOOKUP такие же, как и для функции ВПР, см. скриншоты ниже. Чтобы выполнить обратный поиск, просто введите отрицательное число в аргумент 'Col_Index_Num' (смотри примеры ниже). Формула будет Lookup в направлении влево.

rLU2

Одно из различий между этими двумя функциями, что если Интервальный_просмотр опущен в ВПР, то результата точно не будет. Однако обратное верно для rLOOKUP, по умолчанию эта функция возвращает точное значение. Опять же, это потому, что чаще всего пользователи хотят получить значение в результате поиска. Значения ошибок, возвращаемые функцией rLOOKUP аналогичны тем, которые возвращает ВПР. 

Пример функции

Следующие данные Excel показывает адреса офисов, имена сотрудников и должность.

RevLookup Data1

Для ответа на этот запрос "Кто работает офисе 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".

Другие примеры приведены ниже:

RevLookup Examples1

Не забудьте сначала установить надстройку, в противном случае будет возвращаться ошибка #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

Поделиться:

 

 

Оставьте свой комментарий!

Комментарии  

 
0 # AkrosKomfort 10.11.2020 01:20
ААААА!! Супункция! Почему раньше не знал????
Ответить
 
 
0 # SeoTop 14.11.2020 03:53
пригодилось!
Ответить
 

Добавить комментарий

 

Самое читаемое:

Быстрый поиск

Инструкции к программам

Инструкции к программам

Сайт "Новые Информационные Технологии" содержит лишь справочные данные из открытых источников. Мы НЕ Рекламируем и НЕ Рекомендуем покупать или использовать ВСЕ упомянутые на сайте программы, оборудование и технологии