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

Ответ 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 секунд.