Traducir el blog
Buscar rápida y eficazmente en Excel
🔝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:
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?
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:
- 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:
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:
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:
Además de rellenar un millón de visitas, se rellenan mil búsquedas de visitas con este código:
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:
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"):
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.
Mi lista de blogs
-
Nueva Función TRIMRANGE y Nuevo Operador de Rango - Hoy vengo con novedades emocionantes en Excel: Microsoft ha liberado una nueva función llamada TRIMRANGE y ha introducido un nuevo […] The post Nueva Fun...Hace 1 día
-
Divisor propio mayor que la raíz cuadrada - Explorando por OEIS, encontré un tipo de números en https://oeis.org/A332269 y me ha apetecido desarrollar el tema mediante nuestras funciones en hoja ...Hace 1 día
-
PASAR DATOS A FILAS SEGÚN ELEMENTOS ENTRE GUIONES UTILIZANDO POWER QUERY - Hola a todos! Después del post anterior (aquí) en el que realizábamos una tarea utilizando VBA, me gustaría replicar el mismo trabajo pero usando Power …...Hace 3 días
-
3DFrame-py; with non-linear analysis - Exactly a year since the previous update I have revised the 3DFrame-py spreadsheet with the addition of non-linear analysis options. The new files can be d...Hace 4 días
-
Tiempo pasado, presente y futuro - 🔝*To translate this blog post to your language, select it in the top left Google box. * AVISOS que salvan vidas Haciendo caso de las posibles inund...Hace 5 días
-
Color, Conditions, and Copilot: How to save time using conditional formatting with Copilot in Excel - Hi everyone, this is part 11 in a series of posts to show you some of the things that are possible to do with Copilot in Excel. *What is conditional f...Hace 6 días
-
5 Ways to Calculate Hours Worked in Microsoft Excel - Wondering how to calculate hours worked in Excel? You’ve reached the best resource to learn from. So, tag along! Calculating time values in Microsoft Excel...Hace 1 semana
-
Insertar un objeto vinculado en Excel es fácil y práctico - Aprende a insertar un objeto en Excel. Ya puedes traer objeto vinculado en Excel de manera más avanzada que con los comandos Copiar y Pegar te permiten t...Hace 1 semana
-
Debra’s Excel News–October 2024 - New PIVOTBY function, get ready for Spreadsheet Day, and more, in this month’s Excel news. Visit my Excel website for more tips, tutorials and videos, and ...Hace 4 semanas
-
4 ejemplos de utilización de validación de datos con fórmulas - Ya sabes que la herramienta de Validación de datos es de gran utilidad para controlar y restringir la introducción de datos y así, asegurarte de…Hace 1 mes
-
Trucos de Excel: Referencias Relativas, Absolutas y Mixtas Explicadas ✨ [VIDEO] - ¡Hola a todos! Hoy hablaremos sobre un tema muy importante para quienes usan Excel: los tipos de referencia. Las referencias son fundamentales al momento d...Hace 1 mes
-
How To Predict Bearing Life With Excel - When you work in mechanical engineering, understanding the reliability and performance of bearings under various conditions is crucial. Bearings are the co...Hace 2 meses
-
How to calculate WEEKNUMBER in Month / Quarter / Year with Excel? - Let's say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month ...Hace 2 meses
-
Excel: Cálculo de Finiquito, Beneficios Sociales Bolivia - Es importante tener nociones mínimas acerca el cálculo del finiquito y Beneficios Sociales de un trabajador en Bolivia, ya sea para la verificación de nues...Hace 2 meses
-
-
Minutos de juego y puntos. El Espanyol, sus finales de partido y mis enfados - Pienso que el Espanyol este 2024 se está dejando muchos puntos al final de los partidos. Cuando el ... Leer más » The post Minutos de juego y puntos. El ...Hace 7 meses
-
TikTok’s search evolution - 2 in 5 Americans use TikTok as a search engine. Nearly 1 in 10 Gen Zers are more likely to rely on TikTok than Google as a search engine. More than half of...Hace 7 meses
-
MASTERCLASS Gratis – Gráfica de Gestión Proyectos en #EXCEL. - Aprende a crear un Gráfico de CURVA S, ideal para GESTIÓN DE PROYECTOS, porque te permite identificar como esta tu proyecto tanto en COSTOS como en TIEMP...Hace 8 meses
-
Interés compuesto con Excel - Este es un mapa mental de las distintas funciones Excel para calcular el valor del dinero en el tiempo aplicando la ley de capitalización compuesta. En ...Hace 9 meses
-
Demos cursos de Excel 2007, 2010, 2013, 2016, 365 - Puedes consultar las demostraciones de los siguientes capítulos de los cursos Excel. Demo cursos ExcelHace 9 meses
-
Unblocking and Enabling Macros - When Windows detects that a file has come from a computer other than the one you're using, it marks the file as coming from the web, and blocks the file....Hace 1 año
-
-
Navigating Outlook Favorites - I have these four favorites defined in Outlook: From the inbox, I could hit Shift+F6 to get into the Favorites area but sometimes I would end up in no man’...Hace 1 año
-
Office Scripts: Trabajando con Tablas - [image: Office Scripts: Trabajando con Tablas] Me he dado cuenta que últimamente solo escribo de lenguaje M (es mi pequeño vicio)... pero hay que liberar l...Hace 1 año
-
Progress on the Block Protocol - Since the 1990s, the web has been a publishing place for human-readable documents. Documents published on the web are in HTML. HTML has a little bit of… Re...Hace 1 año
-
Hello world! - [image: Hello world!] Welcome to WordPress. This is your first post. Edit or delete it, then start writing!Hace 2 años
-
Decálogo para realizar Trabajos de Fin de Grado (TFGs) y de Fin de Máster (TFMs) - 1.- Tanto TFGs como TFMs son un requisito para graduarse a la vez que una oportunidad para aprender. Así, el tiempo que se le dedica es muy variable. De...Hace 3 años
-
London Excel Meetup Workbooks - The workbooks used in my presentation on “Analytical and Interactive Dashboards in Excel” at the London Excel Meetup, September 3, 2020Hace 4 años
-
Cálculo de jornada que termina al día siguiente (Power Query) y despedida - [image: Cálculo de jornada que termina al día siguiente (Power Query) y despedida] Este blog se ha ocupado de cálculos de tiempo con bastante intensidad, c...Hace 4 años
-
Visualize parts and whole - combine clustered column and stacked column charts - *Inga: Disa what?* *Igor: -ppeared.* by The FrankensTeam ------------------------------ Really it was 3 years ago we posted our last article? *Freddy: Th...Hace 5 años
-
-
-
-
-
-
-
-
-
No Response to "Buscar rápida y eficazmente en Excel"
Leave A Reply
Indícame las erratas que encuentres y qué es lo que te gustaría ver en los próximos artículos.