Как оптимизировать vlookup для большого количества запросов? (альтернативы VLOOKUP)
Я ищу альтернативы vlookup, с улучшенной производительностью в контексте интересов.
Контекст следующий:
- У меня есть набор данных {ключ; данные}, который является большим (~ 100'000 записей)
- Я хочу выполнить много операций VLOOKUP с набором данных (обычно используется для переупорядочения всего набора данных)
- Мой набор данных не имеет дубликатов ключей
- Я ищу только точные совпадения (последний аргумент
VLOOKUPявляется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 с лучшей производительностью в этом контексте?
4 ответа
Я рассмотрел следующие варианты:
- VLOOKUP массив-формула
- МАТЧ / ИНДЕКС
- VBA (используя словарь)
Сравненная производительность:
- VLOOKUP простая формула: ~ 10 минут
- Формула массива VLOOKUP: ~ 10 минут (индекс производительности 1: 1)
- МАТЧ / ИНДЕКС: ~ 2 минуты (индекс производительности 5: 1)
- VBA (с использованием словаря): ~ 6 секунд (индекс производительности 100: 1)
Используя тот же справочный лист
1) Лист поиска: (версия формулы массива vlookup)
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.
Вы также можете рассмотреть возможность использования метода "double 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
Переключитесь на Excel 2013 и используйте модель данных. Там вы можете определить столбец с уникальными ключами идентификаторов в обеих таблицах и связать эти две таблицы со связями в сводной таблице. Тогда, если это абсолютно необходимо, вы можете использовать Getpivotdata() для заполнения первой таблицы. У меня была таблица строк ~250K, выполняющая vlookup в аналогичной таблице строк ~250K. Перестал Excel вычислять через час. С моделью данных это заняло менее 10 секунд.
Исправление значения: проверьте наличие пустой ячейки при построении словаря. Если ячейка пуста, выйдите из.