Пишем свой VLOOKUP для того, чтобы не зависеть от стандартного (Excel functions)

    Преамбула


    Как известно, знание функции VLOOKUP в MS EXCEL, достаточно чтобы в Москве стать средним аналитиком. Если человек знаком еще и с PIVOT или например знает как убрать дубликаты из списка — все двери в счастливый офисный мир перед ним настежь раскыты.

    У сообщества Хабрахабр, конечно, такие знания могут вызвать лишь улыбку умиления. Работать с данными (если вообще до этого снисходить) допустимо только на олдскульном ANSI T-SQL — 92.

    Но иногда суровая реальность заставляет сравнивать массивы данных. При этом, как правило, нет времени на перенос данных в СУБД, либо это просто нецелесообразно. Поэтому предлагаю уважаемому сообществу поделиться друг с другом своими «фишками» для удобной и быстрой обработки данных в EXCEL. Не пропадать же добру…

    Амбула, собственно


    Функция VLOOKUP (в русской версии — ВПР) действительно очень удобная и мощная помощница, когда нужно остыскать соответствия данных в разных таблицах. Работает она просто и надежно, как автомат Калашникова.

    Но вот незадача, иногда нужно в ходе сравнения уточнить какие-то дополнительные параметры. Например соответствий может быть очень много, и все они в общем-то верные, но некоторые все же «верней». То есть нужно в ходе сравнения провести дополнительную валидацию.

    В общем случае, такие дополнительные задачи могут быть какие угодно — получить контроль над столь ценной функцией, как VLOOKUP, может оказаться крайне полезным.

    Предлагаю вашему вниманию код, который выполняет такую двойную проверку. По сути это DOUBLE LOOKUP. Если выполняется соответствие для «базовой» колонки, то функция ищет параметр «уточняющей» колонки и в случае успеха выдает результат, по аналогии со стандартным VLOOKUP.

    Если же базовое соответствие найдено, но уточняющего нет, то для моих целей я поставил выдачу резутьтатом оповещения, но вы конечно теперь можете переделать функцию так, как нужно для вашего проекта.

    Его величество код


    Формат вызова функции:
    VLOOKUP2my
    ([Table] таблица, внутри которой ищем соответствия;
    SearchColumnNum колонка в [Table], в которой ищем "базовое" соответствие;
    SearchValue значение для поиска "базового" соответствия;
    ResultColumnNum колонка в [Table], из которой заберем результат в случае успеха поисков;
    N2 значение для поиска "уточняющего" соответствия;
    N2col колонка в [Table], в которой ищем "уточняющее" соответствие)

    Function VLOOKUP2my(Table As Range, SearchColumnNum As Integer, SearchValue As Variant, ResultColumnNum As Integer, N2 As Variant, N2col As Integer)
    Dim i As Long
    For i = 1 To Table.Rows.Count
    If UCase(Table.Cells(i, SearchColumnNum)) = UCase(SearchValue) Then
    If (UCase(Table.Cells(i, N2col)) = UCase(N2)) Then
    VLOOKUP2my = Table.Cells(i, ResultColumnNum)
    Exit For
    End If
    VLOOKUP2my = "Second option not exists"
    End If
    Next i
    End Function
    Share post

    Comments 19

      +2
      Амбула Фабула
      Заранее простите за занудство :)
        0
        Что-то мне подсказывает, что vba — куда старше, чем T-SQL. Так что это еще вопрос, что из них олдскульнее…
          0
          Дату рождения VBA отыскать не удалось. SQL аж с 1979 года ведет историю (с Вики). Но T-SQL конечно много позже появился. (Опять же дат в Вики нет)
            0
            VBA как известно появился в Excel 5.0, так что с 1993 года. T-SQL — я даже не догадываюсь когда появился, но в 1993 году насколько мне известно появился и MSSQL for WinNT. Вот только я не знаю, был ли в нем Transact-SQL… А сравнивал я с T-SQL лишь из-за его упоминания в статье.
          0
          На самом деле к ВПР(), а так же практически ко всему что в Экселе связано с поиском или выборками есть масса претензий :)
          Я в свое время написал аналог ВПР() за исключением:
          — не нужно чтобы значения в столбце поиска были отсортированы.
          — ищет только точное совпадение
          — возвращает не значение, а номер строки (соответствующая строчка закомментирована)
          — возвращает номер строки ПОСЛЕДНЕГО найденного совпадения.

          Function VPRL(SearchValue As Variant, Table As Range, SearchColumnNum As Integer, ResultColumnNum As Integer)
          Dim i As Integer
          For i = 1 To Table.Rows.Count
          If Table.Cells(i, SearchColumnNum).Value = SearchValue Then
          ' VPRL = Table.Cells(i, ResultColumnNum)
          VPRL = i
          End If
          Next i
          End Function
            0
            "— ищет только точное совпадение"
            Чтобы искало совпадения независимо от регистра я и поставил UCASE.

            Также известный баг — если ищешь среди цифровых значений а в одной из таблиц они представлены как «число в виде текста» — зеленый треугольничек — то ВПР ничего не найдет. Опять же лечится написанием данной функции, при сравнении использовал «If CStr(Table.Cells(i, SearchColumnNum)) = SearchValue Then» =>
            Заработало
              0
              ПОИСКПОЗ она же МАТCH?
                0
                Да нет, искать надо было в другом столбце, все эти истории с сортировкой для ПОИСКПОЗ тоже актуальны, на сколько я помню (у меня был неотсортированные данные), ну и ПОИСКПОЗ вроде бы тоже находит первый элемент, а мне надо было последний.
                0
                Да ну что вы такое новорите про сортировку… В функции ВПР есть последний параметр, который отвечает за «точность», при его значении=0 сортировка не нужна… Хотя стоит утрчнить версию Excel…
                  0
                  Да, интервальный_просмотр. Но как вы верно заметили он отвечает не за сортировку, там это просто побочный эффект.
                0
                >знает как убрать дубликаты из списка

                Я не знаю, научите.
                Стыдно сказать, но без сортировки и ручной правки не умею…
                  0
                  На этом ПК у меня 2007 офис, в нем есть просто кнопка, а в 2003 вроде как при специальной вставке есть опция? До понедельника проверить не смогу…
                    0
                    Data -> Remove duplicates
                    Либо пихнуть всё в сводную таблицу. Второй способ более кошерен, так как не страдают данные.
                    0
                    index + match и никаких проблем с сортировками или порядком столбцов
                    index + match + match для совсем продвинутых -> получаеться резиновый поиск по x,y
                      0
                      Как вариант, для работы с массивами данных, на мой субъективный взгляд, очень удобно пользовать Google Refine, очень мощно, удобно и свободно.
                        0
                        На тему «фишек» — недавно пришлось сравнивать много-много строк в разных таблицах по группам столбцов — пригодились хэшироваие, создавал в нужных таблицах столбец, куда записывал md5.

                        Исходный код модуля pastebin.com/AA7d7ewL
                        Там просто функция MD5(st as String) as String.
                          0
                          Дано не заходил сюда. Классная «фишка».
                          0
                          Также хочу здесь оставить код аналогичный стандартному ВПР

                          Function VLOOKUP2(SearchValue As Variant, Table As Range, ResultColumnNum As Integer)
                          Dim i As Long
                          SearchColumnNum = 1
                          Rem MsgBox (SearchValue)
                          For i = 1 To Table.Rows.Count
                          Rem MsgBox (Table.Cells(i, SearchColumnNum))
                          If UCase(RTrim(Table.Cells(i, SearchColumnNum))) = RTrim(UCase(SearchValue)) Then
                          VLOOKUP2 = Table.Cells(i, ResultColumnNum)
                          Exit For
                          End If
                          Next i
                          End Function
                            0
                            Также бывает полезен следующий несложный макрос, он обеспечивает защиту от зависаний при пересчете больших сегментов

                            Sub SelectionCalculate()
                            Set ss = Selection
                            For Each cl In Selection.Cells
                            cl.Select
                            cl.Calculate
                            Next cl
                            ss.Select
                            End Sub

                            Only users with full accounts can post comments. Log in, please.