#ExcelPedroWave

🔝Select the language of this blog post in the Google box at the top left.

En el artículo anterior regalé una plantilla para celebrar que este blog ha superado el millón de visitas, ¡que contento estoy!, que puedes descargar en este enlace:

¡¡¡ Un millón de visitas conseguidas !!!

En este artículo explicaré cómo generar un millón de filas ordenadas, con una macro VBA, y cómo buscar rápida y eficazmente en el millón de filas, usando las funciones: BUSCARV y BUSCARX.

Esta imagen representa cuántas veces es más rápida una búsqueda con doble BUSCARV, sobre datos ordenados, en comparación con la búsqueda estándar con una función BUSCARV, sobre datos desordenados, dependiendo del número de filas en las que buscar.

Según esa gráfica la búsqueda eficiente es 3.600 veces más rápida para un millón de filas. Una búsqueda lenta, que tarda 6 minutos en encontrar un valor, se puede convertir en una búsqueda rápida, que tarda una décima de segundo en buscar lo mismo. La imagen anterior la he copiado del primer enlace de más abajo.

Las funciones BUSCARV y BUSCARX se traducen al inglés como VLOOKUP y XLOOKUP, respectivamente.

Búsqueda de antecedentes

Para informarme de qué fórmulas y funciones son más rápidas y eficaces he leído muchos artículos como los siguientes:

dailydoseofexcel.com - How Much Faster Is The Double-VLOOKUP Trick?

dailydoseofexcel.com - VLOOKUP: V Is For Volatile

fastexcel - Developing Faster Lookups – Part 1 - Using Excel’s functions efficiently

fastexcel - Developing Faster Lookups – Part 2 – How to build a faster VBA Lookup

MrExcel.com YouTube - Faster VLOOKUP - Podcast 2031

professor-excel.com - Performance of XLOOKUP: How fast is the new XLOOKUP vs. VLOOKUP?

blogs.itpro.es - Doble BUSCARV calcula más rápido en Excel

elsabiodeexcel - Cómo hacer que BUSCARV vaya más rápido

En estos artículos se explica por qué es más rápida la búsqueda aproximada sobre datos ordenados que la búsqueda exacta sobre datos ordenados o desordenados.

Tipos de búsquedas en Excel

Todas las versiones de Excel incorporan la función BUSCARV, que ha mejorado en prestaciones y funcionalidad con la función BUSCARX de las versiones recientes de Excel para Microsoft 365, Excel Web, Excel Android, etc. 

A) Búsqueda exacta con la función BUSCARV:

=BUSCARV(Rango_Valor;Rango_DATOS;2;FALSO)

Busca exactamente (argumento 4º a FALSO) el valor del argumento 1º en la primera columna de la matriz de datos (no hace falta que esté ordenada) del argumento 2º y devuelve el valor de la columna del argumento 3º en la matriz de datos. 

Es la búsqueda más usual y extendida pero nada rápida y eficiente para buscar entre miles de filas.

B) Búsqueda aproximada con doble BUSCARV sobre datos ordenados.

=SI(BUSCARV(Rango_Valor;Rango_DATOS_A;1;VERDADERO)=Rango_Valor; 

BUSCARV(Rango_Valor;Rango_DATOS;2;VERDADERO);NOD())

El argumento 4º a VERDADERO hace que la búsqueda sea aproximada y mucho más rápida que la búsqueda exacta, que tiene que comprobar cada fila. La búsqueda aproximada funciona sólo si los datos donde buscar están ordenados.

Con la primera búsqueda aproximada con BUSCARV se comprueba si se encuentra el valor buscado, en caso afirmativo hace una segunda búsqueda que devuelve el valor buscado, en caso negativo devuelve el error #N/D (No Disponible) con la función NOD().

C) Búsqueda exacta con la función BUSCARX

=BUSCARX(Rango_Valor;Rango_DATOS_A;Rango_DATOS_B)

Esta es la forma sencilla de llamar a esta función. El argumento 1º es el valor a buscar, el argumento 2º es la columna donde buscar (matriz buscada) y el argumento 3º es la columna donde está el valor a devolver (matriz devuelta).

Como no se han especificado mas argumentos, por defecto el modo de coincidencia (argumento 5º) es de coincidencia exacta, y el modo de búsqueda (argumento 6º) es empezando por el primer elemento.

Los tiempos de búsqueda exacta son lentos tanto con la función BUSCARV como con BUSCARX.

D) Búsqueda binaria con la función BUSCARX sobre datos ordenados

=BUSCARX(Rango_Valor;Rango_DATOS_A;Rango_DATOS_B;;;2)

El argumento 1º es el valor a buscar, el argumento 2º es la columna donde buscar (matriz buscada) y el argumento 3º es la columna donde está el valor a devolver (matriz devuelta).

Como no se especifica el argumento 5º, la búsqueda sigue siendo con coincidencia exacta, pero el argumento 6º es un 2, lo que indica que el modo de búsqueda es binario con la matriz buscada ordenada en orden ascendente.

La búsqueda binaria es muy rápida y ya no hace falta llamar dos veces a la función, como en el caso de BUSCARV, porque BUSCARX tiene el argumento 5º para definir una búsqueda con coincidencia exacta. Internamente hace una búsqueda binaria exacta extremadamente rápida y eficaz. 

Si tienes Excel 365 o Excel Web o Excel Android, acostúmbrate a usar esta función BUSCARX con estos argumentos:

=BUSCARX(valor_buscado; matriz_buscada; matriz_devuelta; [si_no_se_encuentra]; [modo_de_coincidencia]; [modo_de_búsqueda])

Siendo [modo_de_búsqueda] el último argumento con valor:

2: para realizar una búsqueda binaria que se base en que matriz_buscada se ordene en orden ascendente.

-2: para realizar una búsqueda binaria que se base en que matriz_buscada se ordene en orden descendente.

Cuesta menos tiempo ordenar los datos que buscar sobre datos desordenados con lo que, antes de realizar búsquedas del tipo B) o D), es mucho mejor ordenar previamente los datos. Si lo datos están desordenados, no se podrá buscar con coincidencia aproximada o binaria, habrá que seguir usando búsquedas del tipo A) o C) con búsqueda exacta, mucho más lentas e ineficaces.

En muchas ocasiones es más interesante usar la combinación de funciones INDICE y COINCIDIR que BUSCARV, pero este tema da para otro artículo.

Plantilla que busca entre un millón de filas

Descarga la plantilla desde este enlace:

¡¡¡ Un millón de visitas conseguidas !!!

• En la hoja ‘HISTORIA’ se han introducido datos exactos de las visitas mensuales desde que se creó el blog hasta que alcanzó el millón de visitas, con dos gráficos: uno de visitas acumuladas y otro de visitas mensuales.

• En la hoja ‘DATOS’ se rellena un millón de filas con el número de visita y la fecha ficticia de la visita, para que cuadre con las visitas mensuales.​ El relleno se hace con una macro que se explica más adelante.

• En la hoja ‘BUSCAR’ se rellenan mil filas con el número de visita y la fecha de la visita, con una fórmula con 4 tipos de búsqueda. El relleno se hace con una macro que se explica más adelante.

Con la función ELEGIR se llama a la fórmula con el tipo de búsqueda elegido:

=ELEGIR(Rango_NumFormula; BUSCARV([@Visitas];Rango_DATOS;2;FALSO); SI(BUSCARV([@Visitas];Rango_DATOS_A;1;VERDADERO)=[@Visitas];BUSCARV([@Visitas];Rango_DATOS;2;VERDADERO);NOD()); BUSCARX([@Visitas];Rango_DATOS_A;Rango_DATOS_B); BUSCARX([@Visitas];Rango_DATOS_A;Rango_DATOS_B;;;2))

Siendo Rango_NumFormula un número del 1 al 4 con el tipo de búsqueda. Cada uno de estos tipos de búsqueda ya los he descrito anteriormente en este artículo, por lo que no me extiendo más. 

• En la hoja ‘VISITAS’ se elige uno de los 4 tipos de búsqueda y se obtiene el tiempo de la búsqueda. El tipo de búsqueda más frecuente, para un usuario de nivel intermedio de Excel, es con BUSCARV con coincidencia exacta, ¡el más lento!​

Es mucho más rápido BUSCARV con coincidencia aproximada, para lo que los datos a buscar tienen que estar ordenados.​ En Excel 365 se puede buscar con la nueva función BUSCARX. Sigue siendo lenta al buscar con coincidencia exacta.​ La búsqueda binaria es muy rápida con la función BUSCARX, para lo que los datos de búsqueda tienen que estar ordenados, como es el caso.​

Macros para rellenar el millón de visitas

Para probar las búsquedas en un millón de filas he creado unas macros VBA que rellenan una tabla con el número de visita de 1 a un millón y con la fecha de la visita. Están en el módulo: MóduloVisitas

La plantilla descargada pesa poco, unos 60 KB, pero cuando se rellena con un millón de visitas, su tamaño crece considerablemente, hasta unos 20 MB, unas 333 veces más, por lo que es mejor rellenar el millón de filas por primera vez después de descargar la plantilla.

Al hacer clic en el botón de arriba, etiquetado como "Rellena un millón de visitas", se ejecuta la macro Rellenar_Visitas, que rellena un millón de visitas aleatorias y ordenadas para cada mes.

Lo primero que hace esa macro es comprobar que es la primera vez que se ha hecho clic en ese botón y no hará nada las sucesivas veces que se presione el botón.

Luego ejecuta la macro Rellenar_Millón que es realmente la encargada de rellenar un millón de visitas ficticias ordenadas, comenzando por el primer día de la primera visita al blog, el 10 de marzo de 2010, y terminando el día que se superó el millón de visitas, que fue el 17 de abril de 2021. Desde que publiqué el primer artículo pasaron más de 11 años en alcanzar el millón de visitas. A ver si el siguiente millón cuesta menos tiempo...

Con el algoritmo implementado para insertar las fechas de las visitas ficticias, se consigue que la suma del número de visitas de cada mes sea real.

Este es el algoritmo de generación de fechas aleatorias ordenadas:

' Calcula aleatoriamente la siguiente fecha de visita iItera = 0 Do dtAleatoria = Round(dtFecha + Rnd / Int(lVisitasDía / 2), 5) iItera = iItera + 1 Loop While Int(dtAleatoria) <> Int(dtFecha) And iItera < 10 If iItera < 10 Then dtFecha = dtAleatoria End If

Es importante resaltar que la fecha aleatoria, calculada con la variable dtAleatoria, se redondea a 5 decimales.

Si no se hiciera este redondeo ocurriría el problema detectado mientras programaba este algoritmo, lo que me sirvió de inspiración para publicar el siguiente artículo, en el que explico detalladamente un error en el motor de cálculo de Excel con los formatos numéricos de fecha:

Fechas desbordadas aleatoriamente en Excel

Además de rellenar un millón de visitas, se rellenan mil búsquedas de visitas con este código:

' Rellena mil búsquedas Sheets("BUSCAR").Range("A3:B3").AutoFill _ Destination:=Sheets("BUSCAR").Range("A3:B1001"), Type:=xlFillDefault

Con estas mil fórmulas se consigue que el recálculo de los tipos de búsqueda ineficaces sea más lento, como ocurre en tantas ocasiones si se usa la búsqueda inadecuada, que no da problemas con pocas filas pero que, cuando crecen las filas de las tablas, se vuelve tremendamente lenta, y todo el mundo se extraña que, yendo tan bien antes, ahora sea insufrible el recálculo de la misma plantilla.

¿Te ha pasado alguna vez tener que esperar minutos u horas a que acabe el recálculo de Excel?

¡A mí muchas veces con plantillas diseñadas por otros que he tenido que corregir para eliminar las búsquedas ineficaces!

Macros para calcular los tiempos de recálculo

Interesa calcular el tiempo que se tarda en buscar con cada uno de los 4 tipos de búsqueda, para lo que he creado el módulo MóduloTiempos, al que le he añadido las macros copiadas del siguiente enlace, que explica cómo medir el tiempo de cálculo:

Excel performance: Improving calculation performance

En la hoja 'VISITAS' se escucha al siguiente evento cuando cambia el tipo de búsqueda ("Rango_Tipo_Fórmula") o el número de la visita ("Rango_Valor"):

Private Sub Worksheet_Change(ByVal Target As Range) ' ' Worksheet Change Event ' If Target.Address = Range("Rango_Valor").Address Or _ Target.Address = Range("Rango_Tipo_Fórmula").Address Then ' Espera 1 segundo a recalcular todo Esperar_Recalcular End If End Sub

La macro Esperar_Recalcular espera 1 segundo antes de lanzar la macro Recalcular_Todo que llama a su vez a la macro: FullcalcTimer que es la que recalcula todas las fórmulas del libro abierto, mide el tiempo de recálculo en milisegundos, y lo muestra en la hoja 'VISITAS'.

Ya sabes que puedas comentar un poco más abajo las dudas o aclaraciones que necesites sobre las fórmulas o las macros explicadas en este artículo.