performance - otra - funcion buscarv excel 2013

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

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?

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.

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

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

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