[Из песочницы] Усовершенствуем функцию ВПР в Excel

Прочтение публикации Упрощаем бинарный поиск в Excel сподвигло на дополнительное усовершенствование функции ВПР по сравнению с приведенным в статье.

Что не было учтено, и что хотелось бы добавить:

1. Универсальность, т.е. возможность вызывать функцию как для отсортированного массива, так и для неотсортированного.

2. Исключить необходимость два раза вызывать функцию бинарного поиска (ВПР).

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

4. Сделать защиту от дурака — проверять передаваемые аргументы на корректность.

В связи с этим целесообразно отделить указание на то, каким образом отсортирован массив (по возрастанию, по убыванию или не отсортирован) от желаемого результата (нужен точный результат или достаточно приближенного). Вот получившийся код с комментариями.

' VPR - улучшенная версия функций ВПР и ГПР (VLookup & HLookup)
' key - искомое значение (индекс)
' a - массив для поиска индекса
' b - массив такой же формы, для выдачи значения
' Ordered - указывает тип упорядочения массива: 1 - по возрастанию, 0 - не упорядочен, -1 - упорядочен по убыванию
' NotStrict - указывает, нужно точное или приблизительное значение: False - точное, True - достаточно приблизительного.
' Если массив не упорядочен (Ordered = 0), то всегда возвращается точное значение

Function VPR(key As Variant, ByRef a As Range, ByRef b As Range, Optional Ordered As Integer = 0, Optional NotStrict As Boolean = False) As Variant

    ' проверяем корректность аргументов a и b - должны быть линейными, из одной области и 
    ' с одинаковым ненулевым количеством элементов

    If (b.Areas.Count <> 1) Or ((b.Columns.Count > 1) And (b.Rows.Count > 1)) Then
        VPR = CDbl("")
        Exit Function
    End If

    If (a.Areas.Count <> 1) Or ((a.Columns.Count > 1) And (a.Rows.Count > 1)) Then
        VPR = CDbl("")
        Exit Function
    End If

    If (a.Count <> b.Count) Or (a.Count < 1) Then
        VPR = CDbl("")
        Exit Function
    End If

    If Ordered = 0 Then
        NotStrict = False
    End If

    Dim index As Long
    index = Application.WorksheetFunction.Match(key, a, Ordered)

    If (Not NotStrict) And (a(index).value <> key) Then
        VPR = CDbl("")
    Else
        VPR = b(index).value
    End If
End Function

Дополнительные плюшки

1. Возможность поиска в массиве, отсортированном по убыванию (Ordered = -1).
2. Функция позволяет делать поиск если любой (или оба) из аргументов a и b являются горизонтальными рядами (т.е. обобщает и функцию горизонтального просмотра ГПР).

Комментарии

1. Вызов CDbl("") нужен для генерации ошибки (выдача #ЗНАЧ#).
2. Используется не ВПР, а функция Match (русский аналог — ПОИСКПОЗ).

Спасибо за внимание!

Let's block ads! (Why?)