Cómo calcular el Top 10 con fórmulas en Excel

Las listas de los 10 principales son una forma común de mostrar información, especialmente en paneles e informes resumidos. Es fácil crear un top 10 en Excel cuando se trabaja con datos ordenados; simplemente enlace el celular a los 10 elementos principales de la lista… ¡fácil! También es relativamente sencillo cuando se utiliza Autofiltro, Tablas y Tablas dinámicas, ya que es una configuración de filtro predeterminada dentro de estas funciones. Sin embargo, al crear un top 10 con fórmulas en un conjunto de datos no ordenados, las cosas se vuelven un poco complicadas.

Pero las fórmulas son el método más flexible para generar informes en Excel. Por lo tanto, en esta publicación te mostraré exactamente cómo calcular una lista de los 10 mejores. A través de estos métodos, no estás restringido a un top 10; Puedes crear un top 5, un top 8 o cualquier número que elijas.

Desde la introducción de los cálculos de matrices dinámicas en Excel, tenemos un método nuevo y más sencillo para crear la lista de los 10 principales. Por lo tanto, comenzamos analizando las matrices dinámicas. Sin embargo, no se preocupe si tiene Excel 2019 o anterior, también cubriremos una solución para eso.

Tabla de contenido

• Funciones de matriz dinámica
• Combinación de funciones ÍNDICE / ORDENAR / SECUENCIA (Excel 2021 y Excel 365)
• Combinación de funciones SELECCIONAR / SELECCIONAR / ORDENAR / SECUENCIA (solo Excel 365)
• 10 últimos
• Matrices dinámicas con criterios.
• Funciones tradicionales
• Usando la función GRANDE
• Encontrar las etiquetas para el top 10
• Encontrar las etiquetas cuando los 10 valores principales no son únicos
• 10 valores inferiores
• Agregar criterios
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre de archivo: 0015 Top 10 usando fórmulas.xlsx

Tener acceso

Nota: Si no tiene una versión de Excel habilitada para Dynamic Array, las pestañas que contienen esos ejemplos mostrarán errores.

Nota: La publicación del blog se actualizó para incluir las últimas fórmulas de matriz dinámica; Estos no están incluidos en el vídeo.

Mira el vídeo en YouTube

Funciones de matriz dinámica

Si tiene una versión de Excel habilitada para matrices dinámicas (Excel 365 y Excel 2021), es una excelente noticia, ya que no necesita depender de fórmulas complicadas. En cambio, las matrices dinámicas lo facilitan.

Combinación de funciones ÍNDICE / ORDENAR / SECUENCIA (Excel 2021 y Excel 365)

Comience mirando la pestaña Top 10 – DA en el archivo de ejemplo.

En nuestro archivo de ejemplo, hay 26 clientes, con sus correspondientes ubicaciones y valores.

En la celda F4 la fórmula es:

=INDEX(SORT(A2:C27,3,-1),SEQUENCE(10),{1,3})

¡Y eso es! Es una solución sencilla. No es necesario utilizar fórmulas complejas, no es necesario presionar Ctrl+Shift+Enter y no es necesario copiar fórmulas.

Profundicemos un poco más en esto para comprender cómo funciona.

CLASIFICAR

SORT tiene cuatro argumentos:

=SORT(array, [sort_index], [sort_order], [by_col])

• matriz: el rango de celdas o matriz de valores que se ordenarán.
• [sort_index]: la enésima columna o fila a la que aplicar la clasificación. Por ejemplo, para ordenar por la tercera columna, el índice de clasificación sería 3.
• [sort_order]: 1 = ordenar en orden ascendente; -1 = ordenar en orden descendente (si se excluye, el parámetro predeterminado es 1).
• [by_col]: VERDADERO = ordenar por columnas; FALSO = ordenar por filas (si se excluye, el parámetro predeterminado es FALSO).

En nuestra fórmula, utilizamos la siguiente CLASIFICACIÓN.

SORT(A2:C27,3,-1)

Esto ordena las celdas A2 a C27 ​​​​en la tercera columna en orden descendente.

Descubra más sobre la función ORDENAR en este artículo: Función ORDENAR en Excel .

SECUENCIA

La función SECUENCIA tiene cuatro argumentos:

=SEQUENCE(rows, [columns], [start], [step])

• filas: el número de filas a devolver
• [columnas]: el número de columnas que se devolverán. Si se excluye, devuelve una sola columna.
• [inicio]: El primer número de la secuencia. Si se omite, comienza en 1.
• [paso]: La cantidad para incrementar cada valor posterior. Si se excluye, cada incremento es 1.

En nuestro ejemplo, la función SECUENCIA crea una lista del 1 al 10. Sólo necesitamos el primer argumento; Podemos usar las opciones predeterminadas para los argumentos restantes.

SEQUENCE(10)

Esta fórmula se evalúa como una matriz {1;2;3;4;5;6;7;8;9;10}

Descubra más sobre la función SECUENCIA en este artículo: Función SECUENCIA en Excel .

ÍNDICE

Usemos ORDENAR y SECUENCIAR en una función ÍNDICE tradicional:

=INDEX(SORT(A2:C27,3,-1),SEQUENCE(10),{1,3})

La fórmula calcula los primeros 10 resultados de SORT y devuelve las columnas 1 y 3.

Eso fue tan fácil… cierto.

Combinación de funciones SELECCIONAR / SELECCIONAR / ORDENAR / SECUENCIA (solo Excel 365)

Si tiene Excel 365, tenemos acceso a fórmulas matrices aún más dinámicas. Dos de ellos son CHOOSECOLS y CHOOSEROWS, que podemos usar en lugar de la función ÍNDICE.

La fórmula en la celda K4 es:

=CHOOSEROWS(CHOOSECOLS(SORT(A2:C27,3,-1),1,3),SEQUENCE(10))

La fórmula puede ser más larga que la alternativa INDEX pero es más flexible. Echemos un vistazo a las funciones CHOOSEROWS y CHOOSECOLS.

SELECCIONAR y SELECCIONAR

CHOOSEROWS y CHOOSECOLS devuelven solo las filas o columnas especificadas de una matriz. Tienen una sintaxis similar y solo tienen 3 argumentos:

=CHOOSEROWS(array, row_num1, [row_num2],…)

=CHOOSECOLS(array, col_num1, [col_num2],…)

• matriz: la matriz que contiene las filas o columnas que se devolverán
• row_num1 / col_num1: La primera fila/columna que se devolverá
• [row_num2] / [col_num2]: números de fila/columna adicionales que se devolverán

En nuestra fórmula utilizamos lo siguiente:

CHOOSECOLS(SORT(A2:C27,3,-1),1,3)

Esto toma los valores ordenados de las celdas A2:C27 (ordenados por la columna 3 en orden descendente) y solo devuelve las columnas 1 y 3.

A continuación, envolvemos el resultado dentro de una función CHOOSEROWS.

=CHOOSEROWS(CHOOSECOLS(SORT(A2:C27,3,-1),1,3),SEQUENCE(10))

Esto toma el resultado anterior de CHOOSECOLS y devuelve solo las primeras 10 filas.

10 últimos

Para obtener los 10 últimos, solo necesitamos cambiar el tercer argumento de la función ORDENAR de -1 a 1. Mire las celdas F18 y K18 en el archivo de ejemplo para ver esto en acción.

Matrices dinámicas con criterios.

Incluso si tenemos criterios de selección para aplicar, sigue siendo sencillo con las matrices dinámicas. La celda J4 contiene el nombre de la ciudad; Londres o Birmingham. Queremos que el top 10 solo incluya clientes de esa ciudad.

Mire la pestaña Top 10 – DA con criterios en el archivo de ejemplo.

La fórmula en la celda F4 es:

=INDEX(SORT(FILTER(A2:C27,B2:B27=J4),3,-1),SEQUENCE(10),{1,3})

La única diferencia con el ejemplo anterior es que estamos usando la función FILTRO para incluir los elementos coincidentes, antes de introducirlos en la función ORDENAR.

FILTRAR

FILTRO tiene tres argumentos:

=FILTER(array, include, [if_empty])

• matriz: el rango de celdas o matriz de valores para filtrar.
• Incluye: Una matriz de resultados VERDADEROS/FALDOS, donde los valores VERDADEROS se conservan en el resultado.
• [if_empty]: el valor que se mostrará si no se devuelven filas.

En nuestro ejemplo, la función FILTRO es:

FILTER(A2:C27,B2:B27=J4)

Devuelve las celdas A2 a C27, pero solo donde los valores correspondientes de B2 a B27 son iguales a la ciudad seleccionada en la celda J4.

Descubra más sobre la función FILTRO en este artículo: Función FILTRO en Excel .

Los 10 últimos con criterios

Mire el archivo de ejemplo (celdas E17:H27) para ver cómo se calculan los 10 últimos con criterios.

CHOOSECOLS / CHOOSEROWS / ORDENAR / SECUENCIA combinación con criterios

En el archivo de ejemplo, también encontrará la combinación CHOOSECOLS / CHOOSEROWS / ORDENAR / SECUENCIA en la celda M18.

=CHOOSEROWS(CHOOSECOLS(SORT(FILTER(A2:C27,B2:B27=J4),3,1),1,3),SEQUENCE(10))

Esta combinación de funciones detalladas anteriormente se puede utilizar solo con Excel 365.

Trabajar con múltiples criterios

La clave para trabajar con múltiples criterios es la función FILTRO.

Mire el Top 10: DA con múltiples pestañas en el archivo de ejemplo.

Se ha agregado un criterio adicional para incluir solo artículos con un valor igual a la ciudad o menos de 100.

La fórmula en la celda F4 es:

=INDEX(SORT(FILTER(A2:C27,(B2:B27=J4)+(C2:C27J6)),3,-1),SEQUENCE(10),{1,3})

Los criterios de filtro están resaltados arriba. Podemos utilizar este enfoque para agregar tantos criterios de filtro como queramos.

Con la función FILTRO, el símbolo más ( + ) crea lógica O; para crear la lógica AND, usamos el asterisco ( * ). Esto se muestra en el siguiente ejemplo.

=INDEX(SORT(FILTER(A2:C27,(B2:B27=J4)*(C2:C27J6)),3,-1),SEQUENCE(10),{1,3})

Funciones tradicionales

Bien, ahora pasemos a ver las funciones tradicionales (es decir, matrices no dinámicas). Usando estos, obtener los 10 valores principales no es un problema. Pero el desafío es conseguir los nombres/etiquetas que se relacionan con esos diez valores.

Comience mirando la pestaña Top 10 – no DA del archivo de ejemplo.

Usando la función GRANDE

En nuestro archivo de ejemplo, hay 26 clientes, con sus valores.

Usamos la función GRANDE para crear un top 10 de estos clientes (sin ordenar la lista). La celda G4 contiene esta fórmula:

=LARGE($C$2:$C$27,ROW(F4)-ROW(F$3))

GRANDE tiene dos argumentos:

=LARGE(range, k)

• rango: El rango de datos que se analizarán.
• k: El enésimo ^elemento que se encuentra.

En nuestro ejemplo, el rango de datos a analizar son las celdas C2 a C27.

El valor k se calcula como el número de fila menos el número de fila de la fila del encabezado. Esto siempre calcula la posición relativa de la fila en un rango de celdas. Entonces, por ejemplo, la primera fila de datos se calcula como 1, la segunda fila se calcula como 2, y así sucesivamente.

ROW(F4)-ROW(F$3)

La fórmula de la celda G4 se copia para mostrar los 10 valores principales.

Encontrar las etiquetas para el top 10

Tenemos los valores, así que ahora sólo necesitamos calcular el nombre del cliente para ese valor.

No podemos usar BUSCARV ya que el nombre del cliente está a la izquierda del valor de búsqueda. Entonces, en su lugar, usaremos la combinación de fórmula ÍNDICE/COINCIDENCIA.

Nota: Dado que XLOOKUP se lanzó después de las matrices dinámicas, lo he excluido como opción.

La celda F4, en nuestro ejemplo, contiene la siguiente fórmula.

=INDEX($A$2:$A$27,MATCH(G4,$C$2:$C$27,0))

Si esta fórmula se copiara en las celdas F5:F13, nuestra hoja de trabajo se mostraría de la siguiente manera:

¿Puedes ver el problema? Nuestros 10 valores principales no son únicos; Hay 3 valores de 80 (consulte la captura de pantalla anterior). Un cálculo básico de ÍNDICE/COINCIDENCIA solo devuelve el primer valor; por lo tanto, encuentra el nombre Alfa 3 veces. Sin embargo, Echo y Tango , que también tienen 80, no figuran en la lista. Esto es claramente un error, entonces, ¿cómo lo solucionamos?

Encontrar las etiquetas cuando los 10 valores principales no son únicos

Para resolver el problema de encontrar etiquetas con valores no únicos, recurrimos a una fórmula matricial avanzada. La fórmula en la celda F4 debería ser:

{=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G4,ROW($C$2:$C$27)-ROW($C$1)),COUNTIF($G$4:G4,G4)))}

¡¡¡Vaya!!! ¡Esa es una fórmula grande y complicada!

Nota: Este es un tipo especial de fórmula conocida como fórmula matricial. No coloque las llaves ( { } ) al principio o al final cuando escriba la fórmula en la barra de fórmulas; cuando presiona Ctrl+Shift+Enter, Excel agrega las llaves por sí solo. Al presionar Ctrl+Shift+Enter le permite a Excel saber que es una fórmula matricial.

¡RECORDAR! – Si regresa a una fórmula matricial para editarla, debe presionar Ctrl + Shift + Enter para volver a ingresar la fórmula.

Esta fórmula se comporta como ÍNDICE/COINCIDENCIA, pero devuelve el primer, segundo, tercero, cuarto... o enésimo valor. Profundicemos un poco más para entender cómo funciona.

Sección 1 – Función SI

En medio de la fórmula encontramos una función SI.

IF($C$2:$C$27=G4,ROW($C$2:$C$27)-ROW($C$1))

En inglés, esto dice:

Si C2 = G4, devuelva el recuento de filas entre C2 y C1.

Como se trata de una fórmula matricial, pasa automáticamente a la siguiente fila y vuelve a calcular

Si C3 = G4, devuelva el recuento de filas entre C3 y C1.

Y sigue adelante.

Si C4 = G4, devuelva el recuento de filas entre C4 y C1.

Esto incluye todas las celdas desde C2 hasta C27.

Para la fórmula en F4, la función SI se calcula como:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

El séptimo elemento de la lista coincide con la celda G4; por tanto, el único valor que no es FALSO es 7.

Sección 2: funciones PEQUEÑA y CONTAR.SI

Si introducimos el resultado anterior en la función PEQUEÑA, sería el siguiente:

SMALL({FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;7;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},COUNTIF($G$4:G4,G4))

PEQUEÑO encuentra el enésimo ^valor más pequeño. Funciona de manera similar a GRANDE, pero para el valor más pequeño.

PEQUEÑO sólo tiene dos argumentos:

=SMALL(range,k)

• rango: El rango de datos que se analizarán.
• k: El enésimo ^elemento que se encuentra.

En este contexto, CONTAR.SI calcula cuántas instancias del valor ya han aparecido entre los 10 primeros.

Para la primera fila, solo hay 1 elemento entre los 10 primeros con una puntuación de 120, por lo que CONTAR.SI se calculará como 1.

El PEQUEÑO encontrará el primer valor más pequeño, que es 7 , ya que todos los demás resultados son FALDOS.

Finalmente, lo envuelve en la función ÍNDICE para encontrar el séptimo valor en la tabla fuente.

=INDEX($A$2:$A$27,7)

Esto se calcula en la celda A8, que en nuestro ejemplo es Golf .

Todo el funcionamiento anterior fue para mostrar cómo funciona la fórmula. Ahora podemos copiar la fórmula completa en las celdas F5:F13.

Pruebas de valores duplicados

Probemos nuestra lógica de valores duplicados con la celda F11. Hay 3 valores entre los 10 primeros, los cuales son 80; se encuentran en G10, G11 y G12. La etiqueta en F11 debe ser Echo , ya que es el segundo valor de 80.

La fórmula en F11 es:

{=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G11,ROW($C$2:$C$27)-ROW($C$1)),COUNTIF($G$4:G11,G11)))}

La parte SI de la función se calcula como:

{1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

Las filas 1, 5 y 20 de la tabla de origen coinciden todas con el valor de 80.

Por lo tanto, CONTAR.SI se calcula como 2, lo cual tiene sentido, ya que es el segundo valor coincidente que estamos buscando.

SMALL({1;FALSE;FALSE;FALSE;5;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;20;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},2)

Como el segundo número más pequeño es 5, la función ÍNDICE devuelve el quinto valor en la lista de origen, que es Echo . Por tanto, ha calculado el valor correcto.

10 valores inferiores

Si queremos los 10 valores inferiores, el único cambio es que la fórmula para obtener los valores usa PEQUEÑO, en lugar de GRANDE.

Mire el archivo de ejemplo. La fórmula en la celda G18 es:

=SMALL($C$2:$C$27,ROW(F18)-ROW($F$17))

La única diferencia entre la celda G18 y la celda G4 es el uso de la función PEQUEÑA.

La fórmula en la celda F18 es la misma que vimos arriba antes (pero apuntando a celdas diferentes).

=INDEX($A$2:$A$27,SMALL(IF($C$2:$C$27=G18,ROW($C$2:$C$27)-ROW($C$1)),COUNTIF($G$18:G18,G18)))

Agregar criterios

Ahora es el momento de agregar criterios de selección. En nuestro archivo de ejemplo, mire la pestaña Top 10: no DA con criterios .

Como vimos antes, la celda J4 contiene el nombre de la ciudad; Londres o Birmingham. Queremos devolver el top 10 solo para la ciudad seleccionada.

La celda G4 tiene la siguiente función:

{=LARGE(IF($B$2:$B$27=$J$4,$C$2:$C$27),ROW(F4)-ROW(F$3))}

Esta es otra fórmula matricial. Recuerde, no ingrese las llaves, pero presione Ctrl+Shift+Enter nuevamente.

Esto usa la misma lógica que ya hemos visto. La función SI verifica las celdas B2:B27; si el valor coincide con la celda J4, se devuelve el valor en C2:C27.

Cuando la ciudad es Londres, el IF calcula de la siguiente manera:

{80;FALSE;FALSE;FALSE;80;100;120;FALSE;95;FALSE;FALSE;55;FALSE;FALSE;-35;30;FALSE;FALSE;FALSE;80;FALSE;-20;75;-15;FALSE;FALSE}

Al utilizar la función GRANDE, solo los valores de Londres se devuelven al top 10. Todos los valores que no son de Londres se calculan como FALSO.

Hacemos un ajuste similar a la fórmula que calcula el nombre del cliente (la sección agregada está en negrita)

={INDEX($A$2:$A$27,SMALL(IF(($C$2:$C$27=G4)*($B$2:$B$27=$J$4),ROW($C$2:$C$27)-ROW($C$1)),COUNTIF(G4:$G$4,G4)))}

Esta es una fórmula matricial. No ingrese las llaves. Presione Ctrl+Shift+Enter nuevamente.

Trabajar con múltiples criterios

Mire el Top 10: no DA con pestañas múltiples en el archivo de ejemplo.

Se ha agregado un criterio adicional para incluir solo artículos con un valor igual a la ciudad O menos de 100.

La clave para esto son los argumentos contenidos dentro de la función SI.

La fórmula en la celda G4 es:

{=LARGE(IF(($B$2:$B$27=$J$4)+($C$2:$C$27$J$6),$C$2:$C$27),ROW(F4)-ROW(F$3))}

La fórmula en la celda F4 es:

{=INDEX($A$2:$A$27,SMALL(IF(($C$2:$C$27=G4)*(($B$2:$B$27=$J$4)+($C$2:$C$27$J$6)),ROW($C$2:$C$27)-ROW($C$1)),COUNTIF(G4:$G$4,G4)))}

Estas son fórmulas matriciales, así que recuerde presionar Ctrl+Shift+Enter.

Los criterios de filtro están resaltados arriba. Podemos utilizar este enfoque para agregar tantos criterios de filtro adicionales como queramos.

Con Excel, el símbolo más ( + ) crea una lógica O y un asterisco ( * ) crea una lógica Y. Esto se muestra en las siguientes fórmulas:

{=LARGE(IF(($B$2:$B$27=$J$4)*($C$2:$C$27$J$6),$C$2:$C$27),ROW(F4)-ROW(F$3))}

{=INDEX($A$2:$A$27,SMALL(IF(($C$2:$C$27=G4)*(($B$2:$B$27=$J$4)*($C$2:$C$27$J$6)),ROW($C$2:$C$27)-ROW($C$1)),COUNTIF(G4:$G$4,G4)))}

¡Guau! Eso se estaba poniendo complicado.

Conclusión

Una vez que conozcas las técnicas y funciones, calcular un top 10 usando fórmulas en Excel no está tan mal.

Esta publicación demuestra lo buenas que son las matrices dinámicas; Ya no necesitamos depender de fórmulas matrices complejas.

Otros recursos que pueden resultarle útiles:

• Introducción a las matrices dinámicas.
• Comprender las fórmulas matrices básicas para Excel tradicional.

---