¿Cómo crear una lista dinámica de los 10 o n mejores en Excel?
En muchos proyectos y procesos empresariales, es común necesitar clasificar individuos, organizaciones, productos u otras entidades según su rendimiento o valores numéricos. Una "lista de los mejores" sirve para destacar las entradas con mejor desempeño, como los estudiantes con las mejores calificaciones, los mejores vendedores o los departamentos con mayores ingresos. Por ejemplo, podrías tener una tabla de calificaciones de estudiantes y querer extraer dinámicamente a los 10 mejores para premios, análisis o monitoreo de resultados educativos, como se muestra en la captura de pantalla a continuación. Crear una lista dinámica de los 10 o n mejores en Excel te permite ver los resultados actualizados automáticamente a medida que cambian tus datos, ahorrando tiempo y reduciendo errores de clasificación manual. Esta guía introduce varias soluciones prácticas, incluidas fórmulas, tablas dinámicas y macros VBA, para ayudarte a construir una lista dinámica de los 10 o n mejores para satisfacer diversas necesidades de análisis de datos de manera eficiente.
Crear una lista dinámica de los 10 mejores en Excel
- Fórmulas para crear una lista dinámica de los 10 mejores
- Fórmulas para crear una lista dinámica de los 10 mejores con criterios
Crear una lista dinámica de los 10 mejores en Office 365
- Fórmula para crear una lista dinámica de los 10 mejores
- Fórmula para crear una lista dinámica de los 10 mejores con criterios
Crear una lista dinámica de los 10 mejores con una Tabla Dinámica
Crear una lista dinámica de los 10 mejores en Excel
En Excel 2019 y versiones anteriores, crear una lista dinámica de los 10 (o n) mejores implica combinar fórmulas para extraer simultáneamente los valores más altos y sus nombres o IDs asociados. Esta solución es ampliamente utilizada y adecuada para situaciones en las que deseas que la lista se actualice automáticamente cuando cambian tus datos. Las siguientes operaciones describen cómo lograr esto utilizando fórmulas clásicas de Excel. Estas fórmulas ofrecen flexibilidad y no requieren complementos especiales de Excel, pero el proceso de configuración es un poco más complejo en comparación con algunas funciones modernas de matrices dinámicas.
Fórmulas para crear una lista dinámica de los 10 mejores
1. Para comenzar, necesitas extraer los 10 valores más altos de tu rango de valores. Ingresa la siguiente fórmula en una celda en blanco (por ejemplo, celda G2). Después de ingresar la fórmula, arrastra el controlador de relleno hacia abajo para generar tu lista dinámica de los 10 valores principales. Ver captura de pantalla:
2. Luego, para mostrar los nombres (o IDs) correspondientes a esos valores más altos, ingresa la siguiente fórmula en la celda F2. Esta es una fórmula de matriz, así que después de escribirla, presiona Ctrl + Mayús + Enter para confirmar. Esta fórmula encuentra los nombres correspondientes a los valores más altos que acabas de extraer:
- A2:A20 es el rango del cual extraer los nombres;
- B2:B20 es el rango de puntuaciones o valores;
- G2 es el valor más alto de la fórmula anterior;
- B1 es el encabezado de la lista de valores y se utiliza para compensar en los cálculos de FILA.
Esta fórmula vincula dinámicamente los valores más altos con sus nombres. Si tu rango de valores contiene duplicados, CONTAR.SI asegura que cada nombre coincidente aparezca solo una vez con su puntuación.
3. Después de extraer el primer resultado, selecciona la fórmula en la celda F2 y arrastra el controlador de relleno hacia abajo para copiar la fórmula en tantas filas como sea necesario. Esto expandirá tus resultados para mostrar dinámicamente los nombres de todas las entradas principales, coincidiendo con esas puntuaciones. Ver captura de pantalla:
Descubre la Magia de Excel con Kutools AI
- Ejecución Inteligente: Realiza operaciones en celdas, analiza datos y crea gráficos, todo impulsado por comandos simples.
- Fórmulas Personalizadas: Genera fórmulas adaptadas para optimizar tus flujos de trabajo.
- Codificación VBA: Escribe e implementa código VBA sin esfuerzo.
- Interpretación de Fórmulas: Comprende fórmulas complejas con facilidad.
- Traducción de Texto: Supera las barreras del idioma dentro de tus hojas de cálculo.
Fórmulas para crear una lista dinámica de los 10 mejores con criterios
En algunas tareas de análisis, puede que necesites una lista de los mejores que solo muestre entradas que cumplan ciertos criterios, como limitar los resultados principales a un grupo, equipo o categoría específica. Por ejemplo, puedes querer identificar las 10 mejores calificaciones solo de "Clase 1" de una hoja de datos total que contiene calificaciones de múltiples clases. Aquí te mostramos cómo usar fórmulas para este escenario:
1. Comienza extrayendo los 10 valores más altos que cumplan con tu criterio especificado (por ejemplo, "Clase 1") del conjunto de datos. Ingresa esta fórmula en la celda objetivo (por ejemplo, J2):
2. Después de ingresar la fórmula, presiona Ctrl + Mayús + Enter para confirmarla como una fórmula de matriz y luego arrastra hacia abajo el controlador de relleno para llenar otras celdas. La fórmula devolverá los 10 valores más altos que coincidan con tu condición elegida (por ejemplo, todas las puntuaciones de "Clase 1").
3. Para listar los nombres correspondientes a estos valores principales bajo tus criterios, copia y pega la fórmula a continuación en la celda I2 y presiona Ctrl + Mayús + Enter como una fórmula de matriz. Luego, rellena hacia abajo según sea necesario para generar la lista completa de nombres.
Asegúrate de ajustar los rangos en las fórmulas para que coincidan con la configuración real de tus datos. Ten en cuenta que usar rangos de datos grandes con fórmulas de matriz puede ralentizar el rendimiento. Si aparecen valores duplicados en tu top 10, la fórmula manejará apropiadamente las puntuaciones repetidas y dará varios nombres de estudiantes si sus calificaciones son iguales.
Crear una lista dinámica de los 10 mejores en Office 365
Si bien las versiones anteriores de Excel requieren combinar varias funciones con fórmulas de matriz, Office 365 (y Excel 2021) introduce funciones de matriz dinámica como INDICE, ORDENAR, SECUENCIA y FILTRAR que simplifican enormemente el flujo de trabajo. Estas funciones facilitan la construcción de listas dinámicas de los 10 mejores, reducen errores y son especialmente útiles para tablas que crecen o cambian con frecuencia. Si trabajas en un entorno con datos constantemente actualizados, estas funciones pueden agilizar tu análisis y permitir decisiones comerciales más rápidas.
Fórmula para crear una lista dinámica de los 10 mejores
Para extraer y mostrar una lista dinámica de los 10 mejores usando Office 365, ingresa la fórmula a continuación en tu celda de salida deseada. Todo lo que necesitas hacer es ajustar los rangos y números según tus necesidades, y la fórmula mostrará automáticamente los últimos resultados de los 10 mejores cada vez que cambien tus datos.
Simplemente presiona la tecla Intro. La lista completa de los 10 mejores aparece instantáneamente y permanecerá dinámica, por lo que cualquier dato adicional o puntuaciones modificadas se reflejarán inmediatamente en tu clasificación.
Función ORDENAR:
=ORDENAR(matriz, [índice_orden], [orden], [por_col])
- matriz: El rango que deseas ordenar.
- [índice_orden]: Número de la columna por la que ordenar. Para una tabla típica de calificaciones, esta suele ser la segunda columna.
- [orden]: Usa 1 para orden ascendente o -1 para descendente. Para obtener las mejores puntuaciones, usa -1.
- [por_col]: Si ordenar por columnas (VERDADERO) o por filas (FALSO u omitido).
Por ejemplo: ORDENAR(A2:B20,2,-1) ordena A2:B20 por la segunda columna en orden descendente.
Función SECUENCIA:
=SECUENCIA(filas, [columnas], [inicio], [paso])
- filas: Número de filas a devolver, por ejemplo, 10 para una lista de los 10 mejores.
- [columnas]: (Opcional) Número de columnas a devolver.
- [inicio]: (Opcional) Valor inicial.
- [paso]: (Opcional) Valor para incrementar.
SECUENCIA(10) genera los números del 1 al 10, permitiendo que INDICE seleccione los 10 mejores resultados ordenados.
Combinando estos, =INDICE(ORDENAR(A2:B20,2,-1),SECUENCIA(10),{1,2}) te da una lista dinámica de los 10 mejores en dos columnas.
Fórmula para crear una lista dinámica de los 10 mejores con criterios
Si necesitas extraer los 10 mejores para un grupo particular, como "Clase 1", estas funciones avanzadas de Office 365 pueden crear una lista de los N mejores mientras incluyen solo aquellas filas que cumplen con tus criterios. Coloca la fórmula a continuación en la ubicación deseada y ajusta los rangos y la celda de criterios según sea necesario:
Después de ingresar la fórmula, simplemente presiona la tecla Intro. La lista de los 10 mejores filtrada y clasificada solo para el criterio especificado se muestra de inmediato, actualizándose cada vez que modifiques tus datos o criterio.
Función FILTRAR:
=FILTRAR(matriz, incluir, [si_vacío])
- matriz: El rango de celdas a filtrar.
- incluir: La condición (por ejemplo, igual a una clase dada) para inclusión.
- [si_vacío]: (Opcional) Qué mostrar si no hay resultados que cumplan con los criterios.
=FILTRAR(A2:C25,B2:B25=F2) devuelve solo aquellas filas donde la columna B coincide con el valor en F2.
Crear una lista dinámica de los 10 mejores con una Tabla Dinámica
Tabla Dinámica: Mostrar automáticamente los N mejores resultados de forma interactiva
Una forma alternativa de construir una lista dinámica de los N mejores es mediante el uso de la función de Tabla Dinámica de Excel. Este método es especialmente adecuado para grandes conjuntos de datos, análisis interactivo (como cambiar rápidamente el número de elementos principales o aplicar filtros), o cuando deseas evitar fórmulas complejas. Las tablas dinámicas son fáciles de usar y se actualizan automáticamente cuando cambian los datos, lo que las hace ideales para cuadros de mando o informes compartidos con otros.
Para crear una lista dinámica de los N mejores usando una tabla dinámica:
- Haz clic en cualquier lugar dentro de tu tabla de datos, luego ve a Insertar > Tabla Dinámica.
- En el cuadro de diálogo de la tabla dinámica, elige dónde deseas colocar la tabla dinámica y haz clic en Aceptar.
- Arrastra tu campo "Nombre" (u otro identificador similar) al área de Filas.
- Arrastra tu campo "Puntuación" (o columna de valores) al área de Valores. Normalmente, se predeterminará a "Suma de" o "Recuento de" – para las listas principales, generalmente quieres "Suma" o "Máximo". Cambia el cálculo del campo de valor si es necesario haciendo clic derecho y eligiendo Resumir Valor Por.
- Ordena la columna "Puntuación" en orden descendente haciendo clic derecho en un valor y seleccionando Ordenar > Ordenar de mayor a menor.
- Para limitar a los N mejores resultados, haz clic en la flecha desplegable en Etiquetas de Fila, selecciona Filtros de Valor > Los 10 Mejores..., establece el número (por ejemplo, Top 10) y el campo por el que filtrar, luego haz clic en Aceptar.
Tu tabla dinámica ahora muestra los 10 mejores dinámicos (o cualquier N que especifiques). Para cambiar el Top N, simplemente revisita la configuración del filtro. Si cambian tus datos, actualiza la tabla dinámica para reflejar instantáneamente las clasificaciones.
Entre los beneficios de este enfoque se incluye la configuración rápida, la fácil ordenación y el ajuste interactivo. Sin embargo, las tablas dinámicas no pueden agregar automáticamente filas correspondientes de otras columnas a menos que estén incluidas en el área de Filas o Valores. Los usuarios avanzados pueden personalizar aún más los informes agrupando, creando segmentadores o incorporando el filtro de los N mejores en cuadros de mando.
Crear una lista dinámica de los 10 mejores usando VBA
Macro VBA: Generar y actualizar automáticamente una lista de los N mejores
Usar una macro VBA es adecuado para usuarios que manejan datos extensos o que se actualizan con frecuencia, donde se requiere automatizar la extracción y actualización de una lista dinámica de los N mejores. Las macros son ideales para reducir tareas repetitivas y garantizar consistencia. Puedes crear una rutina que ordene tus datos y copie solo las N primeras filas a una ubicación específica cada vez que se ejecute.
Para usar una macro VBA para crear una lista dinámica de los N mejores, sigue estos pasos:
- Haz clic en Desarrollador > Visual Basic para abrir el editor VBA. (Si no ves la pestaña Desarrollador, ve a Archivo > Opciones > Personalizar Cinta y habilita "Desarrollador".)
- En la ventana VBA, haz clic en Insertar > Módulo para agregar un nuevo módulo.
- Pega el siguiente código VBA en el módulo:
Sub ExtractTopNList()
'Updated by Extendoffice 2025/7/24
Dim DataRange As Range
Dim OutputRange As Range
Dim N As Integer
Dim ws As Worksheet, tempWS As Worksheet
Dim xTitleId As String
Dim LastCol As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set ws = ActiveSheet
Set DataRange = Application.InputBox("Select the full data range to analyze (including headers)", xTitleId, ws.UsedRange.Address, Type:=8)
Set OutputRange = Application.InputBox("Select the top-left cell of the output area", xTitleId, "", Type:=8)
N = Application.InputBox("How many top items to extract? (Enter a positive integer)", xTitleId, 10, Type:=1)
If DataRange Is Nothing Or OutputRange Is Nothing Or N < 1 Then Exit Sub
' Create a temporary worksheet to avoid sorting original data
Set tempWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
DataRange.Copy tempWS.Range("A1")
' Determine last column for sorting key
LastCol = DataRange.Columns.Count
' Sort in temporary sheet
tempWS.UsedRange.Sort Key1:=tempWS.Cells(1, LastCol), Order1:=xlDescending, Header:=xlYes
' Copy headers and top N rows to output
tempWS.Rows(1).Copy Destination:=OutputRange
tempWS.Range("A2").Resize(N, LastCol).Copy Destination:=OutputRange.Offset(1, 0)
' Optional: Delete temporary sheet
Application.DisplayAlerts = False
tempWS.Delete
Application.DisplayAlerts = True
Application.CutCopyMode = False
End Sub4. Para ejecutar la macro, asegúrate de que tus datos estén dispuestos correctamente en una tabla con encabezados. Presiona F5 o haz clic en el 
botón en el editor VBA. Se te pedirá que:
- Selecciona tu rango de datos (incluyendo encabezados para una ordenación correcta).
- Selecciona la celda de salida para pegar los resultados.
- Ingresa el número N (por ejemplo, 10 para los 10 mejores).
La macro copiará las N primeras entradas (incluyendo encabezados) a la ubicación que especificaste.
Es recomendable usar esto en una copia de seguridad o copia de tu libro de trabajo al probar por primera vez. Si ocurren errores (como seleccionar un rango incorrecto), vuelve a ejecutar y asegúrate de que tus rangos y disposición de datos sean correctos.
Esta solución es ideal para automatizar tareas de informes repetitivas, crear cuadros de mando o actualizar rápidamente informes de los N mejores sin fórmulas manuales ni ordenación. Puedes personalizar aún más el script VBA para lógica de clasificación más compleja, como ordenar por una columna específica o exportar resultados a otro libro de trabajo.
Solución de problemas: Si la macro no funciona como se espera, verifica que tu tabla de datos incluya encabezados adecuados, corrige los tipos de datos para evitar problemas de ordenación y asegúrate de que las referencias de celda estén seleccionadas correctamente en cada indicación. Siempre guarda tu trabajo antes de ejecutar macros para evitar cambios accidentales en los datos.
En resumen, Excel admite varios métodos para generar y mantener una lista dinámica de los N mejores, desde fórmulas tradicionales hasta poderosas funciones de Office 365, tablas dinámicas para análisis interactivo y macros VBA para automatización avanzada. Elige el método que mejor se adapte a tu flujo de trabajo y escala de datos. Usar fórmulas es efectivo para la mayoría de los análisis manuales, las funciones de Office 365 proporcionan la mayor simplicidad y potencia, las tablas dinámicas son excelentes para resúmenes rápidos y flexibles, y VBA es especialmente útil para automatizar grandes tareas repetitivas de clasificación. Siempre verifica la integridad de tus fórmulas o código, y adapta las referencias de celda para coincidir con cualquier cambio en tu estructura de datos a medida que evolucione tu proyecto.
Las mejores herramientas de productividad para Office
Mejora tu dominio de Excel con Kutools para Excel y experimenta una eficiencia sin precedentes. Kutools para Excel ofrece más de300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haz clic aquí para obtener la función que más necesitas...
Office Tab incorpora la interfaz de pestañas en Office y facilita mucho tu trabajo
- Habilita la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abre y crea varios documentos en nuevas pestañas de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumenta tu productividad en un50% y reduce cientos de clics de ratón cada día!
Todos los complementos de Kutools. Un solo instalador
El paquete Kutools for Office agrupa complementos para Excel, Word, Outlook y PowerPoint junto con Office Tab Pro, ideal para equipos que trabajan en varias aplicaciones de Office.
- Suite todo en uno: complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un solo instalador, una licencia: configuración en minutos (compatible con MSI)
- Mejor juntos: productividad optimizada en todas las aplicaciones de Office
- Prueba completa de30 días: sin registro ni tarjeta de crédito
- La mejor relación calidad-precio: ahorra en comparación con la compra individual de complementos