performance - otra - funcion buscarv excel 2013

¿Cómo optimizar vlookup para un alto conteo de búsquedas?(alternativas a VLOOKUP)

Question

Estoy buscando alternativas a vlookup, con un mejor desempeño dentro del contexto de interés.

El contexto es el siguiente:

• Tengo un conjunto de datos de {key; data} que es grande (~ 100''000 registros)
• Quiero realizar muchas operaciones VLOOKUP en el conjunto de datos (el uso típico es reordenar todo el conjunto de datos)
• Mi conjunto de datos no tiene claves duplicadas
• Estoy buscando solo coincidencias exactas (el último argumento para VLOOKUP es FALSE )

Un esquema para explicar:

Hoja de referencia: ( "sheet1" )

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

Hoja de búsqueda:

 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

En mi Core i7 M 620 @ 2.67 GHz, esto se computa en ~ 10 minutos

¿Hay alternativas a VLOOKUP con un mejor rendimiento en este contexto?

Answer 1

Cambie a Excel 2013 y use el Modelo de datos. Allí puede definir una columna con claves de ID únicas en ambas tablas y enlazar esas dos tablas con relación en Tabla dinámica. Si es absolutamente necesario, puede usar Getpivotdata () para completar la primera tabla. Tenía una tabla de ~ 250K filas haciendo vlookup en la tabla similar ~ 250K filas. Dejó Excel calculando después de una hora. Con Data Model tomó menos de 10 segundos.

Answer 2

Consideré las siguientes alternativas:

• VLOOKUP array-formula
• PARTIDO / ÍNDICE
• VBA (usando un diccionario)

El rendimiento comparado es:

• Fórmula simple VLOOKUP: ~ 10 minutos
• VLOOKUP array-formula: ~ 10 minutos (índice de rendimiento 1: 1)
• PARTIDO / ÍNDICE: ~ 2 minutos (índice de rendimiento de 5: 1)
• VBA (usando un diccionario): ~ 6 segundos (índice de rendimiento 100: 1)

Usando la misma hoja de referencia

1) Hoja de búsqueda: (versión de la fórmula 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) Hoja de búsqueda: (coincidencia + versión de índice)

 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) Hoja de búsqueda: (versión 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 : Por alguna razón (externa interna), vbalookup no puede devolver más de 65536 datos a la vez. Así que tuve que dividir la fórmula de la matriz en dos.

y el código VBA asociado:

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 requiere una referencia a Microsoft Scripting Runtime que debe agregarse manualmente (Herramientas-> Menú Referencias en la ventana de Excel VBA)

Conclusión

En este contexto, VBA utilizando un diccionario es 100 veces más rápido que usar VLOOKUP y 20 veces más rápido que MATCH / INDEX

Answer 3

Corrección de valor: compruebe si hay una celda en blanco al compilar el diccionario. Si la celda está en blanco, salga.

Answer 4

También es posible que desee considerar el uso del método "doble Vlookup" (no es mi idea, se ve en otro lugar). Lo probé en 100.000 valores de búsqueda en la hoja 2 (ordenados aleatoriamente) con un conjunto de datos idéntico al que describió en la hoja 1, y lo sincronicé por poco menos de 4 segundos. El código también es un poco más simple.

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