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

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

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

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

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

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

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

Код функции

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

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.

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

Аноним 23.07.2019 в 21:14

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

Baguzin 23.07.2019 в 21:41

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

Function ВПР3(SearchValue As Variant, _
   Table As Range, ResultColumnNum As Integer)
Dim i As Long
ВПР3 = CVErr(xlErrNA)
For i = Table.Rows.Count To 1 Step -1
    If Table.Cells(i, 1) = SearchValue Then
        ВПР3 = Table.Cells(i, ResultColumnNum)
        Exit Function
    End If
Next i
End Function

Function ВПР3(SearchValue As Variant, _

Table As Range, ResultColumnNum As Integer)

ВПР3 = CVErr(xlErrNA)

For i = Table.Rows.Count To 1 Step -1

ВПР3 = Table.Cells(i, ResultColumnNum)

Exit Function

Александр Хван 25.07.2019 в 13:11

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

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

Function ВПР3(SearchValue As Variant, _
   Table As Range, ResultColumnNum As Integer)
Dim i As Long
ВПР3 = CVErr(xlErrNA)
For i = Table.Rows.Count To 1 Step -1
    'If Table.Cells(i, 1) Like SearchValue Then _
           'с учетом регистра
    If UCase(Table.Cells(i, 1)) Like UCase(SearchValue) _
           Then 'без учета регистра
        ВПР3 = Table.Cells(i, ResultColumnNum)
        Exit Function
    End If
Next i
End Function

'If Table.Cells(i, 1) Like SearchValue Then _

'с учетом регистра

If UCase(Table.Cells(i, 1)) Like UCase(SearchValue) _

Then 'без учета регистра

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

Код функции

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

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

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