Поиск последнего вхождения на основе пользовательской функции ВПР2

Рубрика: 7. Полезняшки Excel

В Excel довольно широко представлены функции поиска – ВПР, ГПР, ПОИСКПОЗ в связке с ИНДЕКС, ПРОСМОТР, и некоторые другие. Все они относятся к категории функций Ссылки и массивы. При этом штатно они ищут первое встретившееся в диапазоне значение, отвечающее условиям поиска. Как было бы замечательно, если бы функция ВПР имела пятый аргумент, позволяющий выбрать направление поиска: с начала диапазона или с конца! Но увы, такой возможности нет. Ранее я описал изящный трюк, позволяющий с помощью функции ПРОСМОТР решить такую задачу. А недавно обнаружил довольно простую пользовательскую функцию, которую немного доработал и назвал ВПР2.

Ris. 1. Okno Argumenty funktsii VPR2

Рис. 1. Окно Аргументы функции ВПР2

Скачать заметку в формате Word или pdf, примеры в формате zip (хостинг не позволяет напрямую загрузить файл Excel с поддержкой макросов)

Синтаксис функции ВПР2 (рис. 1):

ВПР2(SearchValue; Table; ResultColumnNum),

где SearchValue – искомое значение; Table – таблица поиска, поиск происходит в первом столбце таблицы; ResultColumnNum – номер столбца таблицы, из которого возвращается значение. Обратите внимание, что в отличие от обычной ВПР, здесь нет четвертого аргумента Интервальный_просмотр. ВПР2 ищет только точное совпадение, и если его не находит, возвращает ошибку #Н/Д (нет данных).

Код функции

Пояснения. В строке 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.

Работа функции

Ris. 2. VPR2 ishhet stroku a v diapazone

Рис. 2. В ячейке F2 функция ВПР2 ищет строку «a» в диапазоне А2:А11, и возвращает значение из столбца В2:В11 той строки, в которой «a» встречается последний раз – В6. Для сравнения, обычная ВПР возвращает значение из второго столбца для первого встретившегося «a» – В2.

Комментарии: 3 комментария

Если сделать перебор с конца массива в начало, до первого совпадения, то функция будет работать быстрее

Согласен. Например, так:

Отличное решение!

В продолжение, для поиска по маске, как в обычном ВПР, можно использовать Like + два варианта: с и без учета регистра букв.


Прокомментировать