В Excel довольно широко представлены функции поиска – ВПР, ГПР, ПОИСКПОЗ в связке с ИНДЕКС, ПРОСМОТР, и некоторые другие. Все они относятся к категории функций Ссылки и массивы. При этом штатно они ищут первое встретившееся в диапазоне значение, отвечающее условиям поиска. Как было бы замечательно, если бы функция ВПР имела пятый аргумент, позволяющий выбрать направление поиска: с начала диапазона или с конца! Но увы, такой возможности нет. Ранее я описал изящный трюк, позволяющий с помощью функции ПРОСМОТР решить такую задачу. А недавно обнаружил довольно простую пользовательскую функцию, которую немного доработал и назвал ВПР2.
Рис. 1. Окно Аргументы функции ВПР2
Скачать заметку в формате Word или pdf, примеры в формате zip (хостинг не позволяет напрямую загрузить файл Excel с поддержкой макросов)
Синтаксис функции ВПР2 (рис. 1):
ВПР2(SearchValue; Table; ResultColumnNum),
где SearchValue – искомое значение; Table – таблица поиска, поиск происходит в первом столбце таблицы; ResultColumnNum – номер столбца таблицы, из которого возвращается значение. Обратите внимание, что в отличие от обычной ВПР, здесь нет четвертого аргумента Интервальный_просмотр. ВПР2 ищет только точное совпадение, и если его не находит, возвращает ошибку #Н/Д (нет данных).
Код функции
1 2 3 4 5 6 7 8 9 |
Function ВПР2(SearchValue As Variant, Table As Range, ResultColumnNum As Integer) Dim i As Long ВПР2 = CVErr(xlErrNA) For i = 1 To Table.Rows.Count If Table.Cells(i, 1) = SearchValue Then ВПР2 = Table.Cells(i, ResultColumnNum) End If Next i End Function |
Пояснения. В строке 1 определены три аргумента функции, которые передаются в VBA из Excel. Поскольку тип данных самой функции не определен, он будет установлен по умолчанию, как Variant. И это полезно, так как мы не знаем, какие данные будут возвращаться функцией ВПР2: числовые или текстовые.
В строке 2 определяется внутренняя (для кода VBA) переменная i. В Excel ей соответствует номер строки диапазона. Т.е., она может принимать целые значения от 1 до 1 млн. Поэтому лучше присвоить ей тип данных Long. Integer не справится, если строк в таблице Table будет более 32 000 (приблизительно).
В строке 3 задается значение ВПР2 по умолчанию. Т.е., если ни одного совпадения в цикле For … Next и проверке If не случится, функция ВПР2 вернет в Excel значение, определяемое константой xlErrNA или #Н/Д.
В строке 4 задается диапазон перебора i для цикла: от 1 до числа строк в таблице Table.
В строке 5 проверяется условие: если значение в ячейке текущей строки столбца 1 таблицы равно искомому – SearchValue – то значение ВПР2 меняется на значение из той же строки столбца номер ResultColumnNum.
Работа функции
Рис. 2. В ячейке F2 функция ВПР2 ищет строку «a» в диапазоне А2:А11, и возвращает значение из столбца В2:В11 той строки, в которой «a» встречается последний раз – В6. Для сравнения, обычная ВПР возвращает значение из второго столбца для первого встретившегося «a» – В2.
Если сделать перебор с конца массива в начало, до первого совпадения, то функция будет работать быстрее
Согласен. Например, так:
Отличное решение!
В продолжение, для поиска по маске, как в обычном ВПР, можно использовать Like + два варианта: с и без учета регистра букв.