Подтвердить что ты не робот

Как оптимизировать vlookup для высокого количества поиска? (альтернативы VLOOKUP)

Я ищу альтернативы vlookup, с улучшенной производительностью в контексте интереса.

Контекст следующий:

  • У меня есть набор данных {key; data}, который является большим (~ 100 000 записей)
  • Я хочу выполнить много операций VLOOKUP в наборе данных (типичное использование - переупорядочить весь набор данных)
  • В моем наборе данных нет дубликатов ключей
  • Я ищу только точные соответствия (последний аргумент VLOOKUP is FALSE)

Схема для объяснения:

Справочный лист: ("sheet1")

        A           B
     1
     2  key1        data1
     3  key2        data2
     4  key3        data3
   ...  ...         ...
 99999  key99998    data99998
100000  key99999    data99999
100001  key100000   data100000
100002

Лист поиска:

        A           B
     1
     2  key51359    =VLOOKUP(A2;sheet1!$A$2:$B$100001;2;FALSE)
     3  key41232    =VLOOKUP(A3;sheet1!$A$2:$B$100001;2;FALSE)
     4  key10102    =VLOOKUP(A3;sheet1!$A$2:$B$100001;2;FALSE)
   ...  ...         ...
 99999  key4153     =VLOOKUP(A99999;sheet1!$A$2:$B$100001;2;FALSE)
100000  key12818    =VLOOKUP(A100000;sheet1!$A$2:$B$100001;2;FALSE)
100001  key35032    =VLOOKUP(A100001;sheet1!$A$2:$B$100001;2;FALSE)
100002

На моем Core i7 M 620 @2,67 ГГц это вычисляется через ~ 10 минут

Существуют ли альтернативы VLOOKUP с лучшей производительностью в этом контексте?

4b9b3361

ОТВЕТЫ

Ответ 1

Я рассмотрел следующие альтернативы:

  • Формула-формула VLOOKUP
  • MATCH/INDEX
  • VBA (с использованием словаря)

Сравниваемая производительность:

  • VLOOKUP простая формула: ~ 10 минут
  • VLOOKUP array-formula: ~ 10 минут (индекс производительности 1:1)
  • MATCH/INDEX: ~ 2 минуты (индекс производительности 5: 1)
  • VBA (с использованием словаря): ~ 6 секунд (индекс производительности 100: 1)

Используя тот же справочный лист

1) Лист поиска: (версия формулы vlookup array)

         A          B
     1
     2   key51359    {=VLOOKUP(A2:A10001;sheet1!$A$2:$B$100001;2;FALSE)}
     3   key41232    formula in B2
     4   key10102    ... extends to
   ...   ...         ... 
 99999   key4153     ... cell B100001
100000   key12818    ... (select whole range, and press
100001   key35032    ... CTRL+SHIFT+ENTER to make it an array formula)
100002

2) Лист поиска: (совпадение + индексная версия)

         A           B                                       C
      1
      2  key51359    =MATCH(A2;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B2)
      3  key41232    =MATCH(A3;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B3)
      4  key10102    =MATCH(A4;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B4)
    ...  ...         ...                                     ...
  99999  key4153     =MATCH(A99999;sheet1!$A$2:$A$100001;)   =INDEX(sheet1!$B$2:$B$100001;B99999)
 100000  key12818    =MATCH(A100000;sheet1!$A$2:$A$100001;)  =INDEX(sheet1!$B$2:$B$100001;B100000)
 100001  key35032    =MATCH(A100001;sheet1!$A$2:$A$100001;)  =INDEX(sheet1!$B$2:$B$100001;B100001)
 100002

3) Лист поиска: (версия vbalookup)

       A          B
     1
     2  key51359    {=vbalookup(A2:A50001;sheet1!$A$2:$B$100001;2)}
     3  key41232    formula in B2
     4  key10102    ... extends to
   ...  ...         ...
 50000  key91021    ... 
 50001  key42       ... cell B50001
 50002  key21873    {=vbalookup(A50002:A100001;sheet1!$A$2:$B$100001;2)}
 50003  key31415    formula in B50001 extends to
   ...  ...         ...
 99999  key4153     ... cell B100001
100000  key12818    ... (select whole range, and press
100001  key35032    ... CTRL+SHIFT+ENTER to make it an array formula)
100002

NB. Для некоторой (внешней внутренней) причины vbalookup не может вернуть более 65536 данных за раз. Поэтому мне пришлось разделить формулу массива на две части.

и связанный код VBA:

Function vbalookup(lookupRange As Range, refRange As Range, dataCol As Long) As Variant
  Dim dict As New Scripting.Dictionary
  Dim myRow As Range
  Dim I As Long, J As Long
  Dim vResults() As Variant

  ' 1. Build a dictionnary
  For Each myRow In refRange.Columns(1).Cells
    ' Append A : B to dictionnary
    dict.Add myRow.Value, myRow.Offset(0, dataCol - 1).Value
  Next myRow

  ' 2. Use it over all lookup data
  ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
  For I = 1 To lookupRange.Rows.Count
    For J = 1 To lookupRange.Columns.Count
      If dict.Exists(lookupRange.Cells(I, J).Value) Then
        vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
      End If
    Next J
  Next I

  vbalookup = vResults
End Function

NB: Scripting.Dictionary требуется ссылка на Microsoft Scripting Runtime, которая должна быть добавлено вручную (меню "Сервис- > Список литературы" в окне Excel VBA)

Заключение:

В этом контексте VBA, использующий словарь, в 100 раз быстрее, чем использование VLOOKUP и 20 раз быстрее, чем MATCH/INDEX

Ответ 2

Вы также можете рассмотреть возможность использования метода "двойной Vlookup" (не моя идея - см. в другом месте). Я тестировал его на 100 000 значений поиска на листе 2 (случайным образом отсортированным) с идентичным набором данных, как тот, который вы описали на листе 1, и приурочил его чуть менее 4 секунд. Код также немного проще.

Sub FastestVlookup()

    With Sheet2.Range("B1:B100000")
        .FormulaR1C1 = _
        "=IF(VLOOKUP(RC1,Sheet1!R1C1:R100000C1,1)=RC1,VLOOKUP(RC1,Sheet1!R1C1:R100000C2,2),""N/A"")"
        .Value = .Value
    End With

End Sub

Ответ 3

Переключитесь на Excel 2013 и используйте модель данных. Там вы можете определить столбец с уникальными идентификационными ключами в обеих таблицах и связать эти две таблицы с отношением в сводной таблице. Затем, если необходимо, вы можете использовать Getpivotdata() для заполнения первой таблицы. У меня была таблица строк ~ 250 тыс. Строк, которая делала vlookup в таблице ~ 250K строк. Остановил Excel, вычисляя его через час. В модели данных потребовалось менее 10 секунд.