Прочтение публикации Упрощаем бинарный поиск в 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 (русский аналог — ПОИСКПОЗ).
Спасибо за внимание!
ссылка на оригинал статьи https://habrahabr.ru/post/314302/
Добавить комментарий