Усовершенствуем функцию ВПР в 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 (русский аналог — ПОИСКПОЗ).


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

Поделиться публикацией
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама
Комментарии 13
  • 0
    впр не требует отсортированного множества
    • +1
      Если 4-й аргумент имеет значение ИСТИНА и значения в первом столбце таблицы подстановки не отсортированы по возрастанию, — это может привести к непредвиденным результатам. (При использовании значения ЛОЖЬ сортировка не требуется). //files3.vunivere.ru/workbase/00/02/24/11/images/image023.jpg
    • +2
      Ничего так не улучшает ВПР, как подключённый к excel SQL.
      • 0
        а еще лучше подключенный R к Excel,
        хотя лучше вообще все в R подобное делать
        • 0
          А есть способ? Может подскажете?
          А то когда надо много работать с большими объемами данных в Excel (прихоть работодателя), использую Python , но с удовольствием бы перешел на R.
          • 0
            для интеграции есть адд-он к Excel: RExcel — http://rcom.univie.ac.at/download.html#RExcel.

            А если все делать в R, то есть пакеты и для открытия файлов xls, и сохранения результатов в них же.
            • 0
              readxl+dplyr
              • 0
                но с удовольствием бы перешел на R.

                просто интересно, можете обосновать Ваше удовольствие? т.к. наталкиваюсь уже не в первый раз на упоминание о неудобстве (конечно субъективном) работы пользователей с питоном
                • 0
                  Никакого неудобства. Очень люблю питон и с удовольствием на нем пишу. Но то, как на R можно обрабатывать ту же статистику, это просто песня :)
              • 0
                А расскажите подробнее, вот я хочу обрабатывать словарь (текстовый файл размером около 2 ГБ), поможет в этом R?
                Искать и находить в нем по ключам от 1 до 200.000 значений одновременно, и возвращать для каждого ключа значения.
            • 0
              Ээээ, кхм…
              Это хорошо, что сделали типа ВПР, но через ПОИСКПОЗ, хотя в первоначальной статье именно про это в коментариях и говорилось.
              Однако…
              Функции пита ВПР или ПОИСКПОЗ прменяются в 2-х видах задач:
              1. Разовая обработка больших массивов данных в экселе. Как оказала практика, если вы через ОЛЕ выгружаете данные эксель, то лучше выгружать сразу в Аксес. Для статитстической обработки очень больших данных это намного практичнее и быстрее.
              Более того, если выгружать в эксель и обрабатывать в нем, то нюансы функции ВПР не так важны, как и скорость — работа разовая, можно 1- сек подождать.

              2. Рабочие файлы, в которых ежедневно, ежеднелельно и ежемесячно ведется работа, обрабатываемые данные могут заносится туда выгрузками или вручную (т.е. никаких сортировок).
              И функции типа ВПР, ПОИСПОЗ или СУММПРОИЗВ применяются в массовых масштабах для создания реляционной базы.
              Данные измеряются не 200 тыс строк, а на порядок меньше, зато указанные функции используются по 5-15 раз в каждой строке, итого они вызываются под 1 млн раз.
              Это я к чему? При такое количестве вызоов функций любое использование ВБА подвешивает программу на минуты и часы. По этой простой причине примеются исключительно встроенные фукнции, т.к. скрипты катасрофически тормознутые.
              Если у вас 100 вызовов ВБА — это нормально, если 100 тыс. — это беда.
              Причем тормозит именно сам механизм ВБа, даже если вы там внутри вызываете встроенные функции.
              • 0
                А еще есть INDEX MATCH.
                https://www.deskbright.com/excel/using-index-match/
                • 0
                  Index-match сработает неправильно, если захочется найти точное значение в отсортированном массиве. Именно эта ситуация как раз и оптимизируется…

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.