Course
Fórmula de clasificación en Excel: Una guía completa con ejemplos
La clasificación te permite comparar valores e identificar patrones dentro de tus conjuntos de datos. Excel proporciona potentes herramientas como RANK()
,RANK.EQ()
, y RANK.AVG()
para facilitar la clasificación. Estas funciones son útiles en todas las situaciones, desde clasificar por puntos a los participantes en un torneo deportivo hasta analizar las tendencias del mercado basándose en los datos de las ventas clasificadas.
En este artículo, exploraremos las tres funciones de rango en Excel y te explicaremos cómo puedes utilizarlas con ejemplos prácticos. También nos sumergiremos en el uso de las funciones de rango con otras funciones de Excel y en el cálculo de rangos percentiles.
La respuesta rápida: Cómo calcular el rango en Excel
Para calcular los rangos en los conjuntos de datos que desees, utiliza la siguiente fórmula:
=RANK(number, ref, [order])
Comprender la función RANK() en Excel
Supongamos que has organizado una competición entre alumnos de una escuela y ahora quieres clasificar a miles de estudiantes en función de su rendimiento.
¿Pasarías horas clasificando datos? No, porque Excel puede ayudar. Entre sus muchas y potentes funciones está la de RANK()
, que te permite determinar la posición de alguien con un solo clic .
Para calcular el rango de un valor dentro de un intervalo de valores, introduce la fórmula:
RANK(number, ref, [order])
En la fórmula anterior:
number
: es el número que quieres clasificar.ref
: es el rango de números a tener en cuenta para la clasificación.order
: especifica el orden de clasificación. Para ascendente, utiliza "1" como valor; para descendente, utiliza "0" (por defecto).
Cómo utilizar la fórmula RANK() en Excel
Implementemos la función RANK()
en ejemplos reales:
Clasificar las notas de los alumnos
Calcular las notas sería un reto y llevaría mucho tiempo si tienes miles de alumnos. Supón que estás preparando los resultados finales de los alumnos y quieres clasificarlos en función de sus puntuaciones.
Para ello, en primer lugar, prepara tu conjunto de datos.
Conjunto de datos de ejemplo de los alumnos en Excel. Fuente: Imagen del autor.
Introduce la fórmula =RANK(B3,($B$3:$B$7))
en una celda y arrástrala hasta la última celda rellenada para obtener el rango.
Utilizar la función RANK() para calcular el rango de los alumnos. Fuente: Imagen del autor.
Ahora tienes el rango del alumno en un solo clic.
Pero, ¿te has dado cuenta de que dos alumnos con las mismas notas tienen el mismo rango de 4? El siguiente alumno, con 23 puntos, salta al puesto 6, saltándose el puesto 5. Esto significa que la fórmula asigna el mismo rango a las puntuaciones duplicadas.
Esto puede alterar el orden de las puntuaciones ajustadas. Pero puedes arreglar estos empates utilizando la función COUNTIF()
. Introduce la siguiente fórmula:
=RANK(B3,($B$3:$B$8)) + COUNTIF($B$3:B3, B3)-1
La funciónCOUNTIF()
ajusta el rango para los valores duplicados. Cuenta cuántas veces aparecen las notas del alumno actual por encima de él en la lista.
Desempatar utilizando la función COUNTIF() en Excel. Fuente: Imagen del autor.
Un valor de 28 aparece dos veces, y COUNTIF()
rastrea el recuento de apariciones. Este recuento de ocurrencias se utiliza para ajustar el rango. La fórmula añade el recuento de apariciones menos uno al rango original. Esto garantiza que cada valor duplicado reciba un rango único.
Clasificar las cifras de ventas en un conjunto de datos
Veamos otro ejemplo para mostrar cómo utilizar la función de Excel RANK()
en orden ascendente o descendente.
Si quieres evaluar el rendimiento de tu equipo de ventas, puedes clasificar sus cifras de ventas mensuales utilizando la función de Excel RANK()
de Excel.
Aquí tenemos un conjunto de datos con las cifras de ventas mensuales de cada vendedor. Utilizaremos la función RANK()
para clasificarlos en función de quién haya conseguido más ventas (de mayor a menor).
Lista de cifras de ventas para clasificar. Fuente: Imagen del autor.
Para clasificar las ventas en orden ascendente, aplicamos la fórmula =RANK(B2, ($B$2:$B$6), 1)
. Aquí, un valor de "1" para el parámetro order
especifica el orden ascendente de la clasificación.
Ordena los valores de forma ascendente. Fuente: Imagen del autor.
Una vez que hayas introducido la fórmula, simplemente mantén el cursor, arrastra la esquina de la celda hacia abajo y aplícala al resto de tus datos de ventas.
Del mismo modo, si quieres clasificar los valores en orden descendente, escribe "0" como valor del parámetro en la fórmula de la siguiente manera order
valor del parámetro en la fórmula, así =RANK(B2, ($B$2:$B$6), 0)
Puedes ver la diferencia en los resultados:
Ordena los valores de forma descendente. Fuente: Imagen del autor.
Puede que notes los signos de dólar en las referencias ($B$2:$B$6)
. Se llaman referencias absolutas. Garantizan que la fórmula se refiera siempre al mismo intervalo de datos de ventas, aunque la copies, para evitar errores.
Si no utilizas la referencia absoluta, como =RANK(A2, A2:A10)
el rango se ajustará a medida que arrastres hacia abajo:
- Fórmula en G3:
=RANK(G3,(G3:G7), 1)
- Fórmula en G4:
=RANK(G4,(G4:G8), 1)
Para evitar este problema, utiliza referencias absolutas pulsando "F4". Ahora la fórmula será: =RANK(G3,($G$3:$G$7), 1)
Cuando arrastres la fórmula anterior a la última celda rellenada, las referencias relativas cambiarán en función de la posición de cada celda. Puedes ver la diferencia en los resultados al utilizar referencias absolutas:
Diferencia entre los rangos con y sin referencia absoluta. Fuente: Imagen del autor.
Funciones de Excel de rango avanzado: RANK.EQ() y RANK.AVG()
Cuando te encuentras con valores duplicados en Excel, las cosas pueden complicarse. Ahí es donde prefiero utilizar las funciones RANK.EQ()
y RANK.AVG()
. Ambas son ligeramente diferentes de manejar. Veamos cómo funcionan.
La función de Excel RANK.EQ()
RANK.EQ()
funciona igual que la función normal RANK()
, pero con un nombre más preciso. Cuando hay duplicados, RANK.EQ()
asigna el mismo rango a todos ellos y simplemente se salta el rango o rangos siguientes.
Syntax: =RANK.EQ(number, ref, [order])
En la fórmula anterior:
number
: es el número que quieres clasificar.ref
: es el rango de números a tener en cuenta para la clasificación.order
: especifica el orden de clasificación. Para ascendente, utiliza "1" como valor; para descendente, utiliza "0" (por defecto).
Vamos a entender esta fórmula con un ejemplo: Tenía un conjunto de datos con las poblaciones de varios países, y quería clasificar estos países en función de sus poblaciones.
Así es como lo hice utilizando la RANK.EQ()
función
Utilizando la función RANK.EQ(). Fuente: Imagen del autor.
En la imagen anterior, el país con mayor población ocupa el puesto 1, y así sucesivamente. Ahora, verás cómo RANK.EQ()
maneja los lazos .
Como EEUU e Indonesia tienen poblaciones idénticas, comparten el mismo rango de 2. Esto deja claro cómo RANK.EQ()
asigna el mismo rango a los duplicados y se salta el rango siguiente en la secuencia.
La función de Excel RANK.AVG()
La funciónRANK.AVG()
adopta un enfoque diferente para tratar los empates en las clasificaciones. En lugar de asignar el mismo rango a todos los duplicados, calcula la media de los rangos que habrían recibido individualmente.
Syntax: RANK.AVG(number, ref, [order])
En la fórmula anterior:
number
: es el número que quieres clasificar.ref
: es el rango de números a tener en cuenta para la clasificación.order
: especifica el orden de clasificación. Para ascendente, utiliza "1" como valor; para descendente, utiliza "0" (por defecto).
Vamos a entenderlo con un ejemplo: Tenía un conjunto de datos de puntuaciones, así que apliqué la función RANK.AVG()
para ver cómo trataba las puntuaciones de empate.
Utilizando la función RANK.AVG(). Fuente: Imagen del autor.
Utilizando =RANK.AVG(D3,$D$3:$D$6)
, la puntuación 88, recibe un rango de 2,5. Esto significa que la función promedia los rangos que habría asignado individualmente.
Este enfoque mantiene una distribución más equilibrada de los rangos, especialmente en conjuntos de datos con frecuentes empates.
Combinar RANK() con otras funciones de Excel
Utilizar la funciónRANK()
con otras funciones de Excel es útil en algunos casos. Repasemos un par de casos de uso común.
Combinar RANK() y ROUNDUP() para categorizar datos
Así es como puedes combinar la función ROUNDUP()
con la función RANK()
con la función
Combinar las funciones RANK() y ROUNDUP() en Excel. Fuente. Imagen del autor.
He utilizado la fórmula =ROUNDUP(RANK(B2, $B$2:$B$7, 1)/3, 0)
, que calcula el rango del valor de la celda B2 dentro del rango B2 en orden ascendente. A continuación, divide este rango por tres y redondea el resultado al número entero más próximo.
Con la fórmula anterior, clasificarás eficazmente los datos en distintos grupos o niveles, lo que puede ayudar a identificar y analizar distintos niveles de rendimiento o a categorizar los datos para su posterior análisis.
Combinar RANK.EQ() y COUNTIFS() para desempatar
Hemos comentado algunas formas de tratar los empates utilizando la funciónRANK()
de Excel . Sin embargo, supongamos que quieres clasificar los productos únicamente en función del precio. Esto puede parecer arbitrario cuando varios artículos comparten el mismo coste.
Así pues, introduzcamos un criterio secundario para deshacer los empates y crear un sistema de clasificación más objetivo.
Piensa en una lista de clientes que hayan comprado auriculares. Queremos clasificarlos por su precio (criterio principal) y luego utilizar la valoración de los clientes (criterio secundario) para diferenciar en caso de empate.
Ejemplo de lista de clientes. Fuente: Imagen del autor
Para conseguirlo, combinaremos la función RANK.EQ()
con la funciónCOUNTIFS()
. He aquí cómo:
=RANK.EQ($B2,$B$2:$B$9) + COUNTIFS($B$2:$B$9, $B2, $C$2:$C$9, ">"& $C2)
Calcular el rango utilizando RANK.EQ y COUNTIFS Calcular el rango utilizando RANK.EQ() y COUNTIFS(). Fuente: Imagen del autor.
La sección COUNTIFS($B$2:$B$9,$B2)
cuenta las veces que aparece el precio actual en la lista. Utilizamos referencias absolutas para el rango, $B$2:$B$9
, para mantener la coherencia, pero mantenemos la celda criterio, $B2
, relativa para que la fórmula compruebe cada fila individualmente.
La segunda parte, $C$2:$C$9,">"&$C2
encuentra el número de valoraciones de clientes superiores a la valoración actual del auricular, C2.
COUNTIFS()
sigue la lógica "Y": Sólo cuenta las celdas que cumplen todas las condiciones. Si ningún otro auricular del mismo precio tiene una valoración superior, COUNTIFS()
devuelve 0. Por eso el rango determinado por RANK.EQ()
permanece inalterado.
Calcular el rango percentil en Excel
Como la función RANK()
ordena un valor numérico, puedes utilizar una fórmula de Excel de rango percentil para calcular el rango del valor dentro de un conjunto de datos como porcentaje del conjunto de datos total.
Excel proporciona dos funciones para calcular rangos de percentiles. Exploremos ambos.
La función de Excel PERCENTRANK.INC()
PERCENTRANK.INC() calcula el rango percentil de un valor, incluyendo los valores más pequeño y más grande del conjunto de datos.
Vamos a entender esta función con un ejemplo: Tenía un conjunto de datos que representaba las puntuaciones de los exámenes de 10 alumnos, y quería calcular el rango percentil de cada puntuación, incluyendo los valores más pequeño y más grande.
Así que utilicé la fórmula =PERCENTRANK.INC($A$2:$A$11, A2)
:
Calcula el rango utilizando la función PERCENTRANK.INC(). Fuente: Imagen del autor
En el ejemplo anterior, puedes ver que la funciónPERCENTRANK.INC()
incluye los valores más pequeños y más grandes del conjunto de datos. La puntuación 45 (valor más pequeño) tiene un rango percentil de 0, y la puntuación 90 (valor más grande) tiene un rango percentil de 1.
La función de Excel PERCENTRANK.EXC()
PERCENTRANK.EXC()
calcula el rango percentil de un valor, excluyendo los valores más pequeño y más grande del conjunto de datos.
Vamos a entender esta función con un ejemplo: He aplicado la función PERCENTRANK.EXC()
sobre el mismo conjunto de datos de estudiantes de la sección anterior:
Calcula el rango utilizando la función PERCENTRANK.EXC(). Fuente: Imagen del autor
En el ejemplo anterior, la funciónPERCENTRANK.EXC()
excluye los valores más pequeño y más grande del conjunto de datos. Esto significa que la puntuación 45 (valor más pequeño) no tiene un rango de percentil 0, y la puntuación 90 (valor más grande) no tiene un rango de percentil 1.
Errores comunes de la función RANK() de Excel y cómo evitarlos
Las funciones de Excel son prácticas y fáciles de usar para todo el trabajo empresarial. Pero a veces, incluso los usuarios más expertos pueden cometer algunos errores comunes. Por eso, comparto algunos de los errores más comunes que debes buscar y cómo puedes evitarlos:
Copiar y pegar fórmulas
Cuando copies tu fórmula de clasificación entre celdas, asegúrate de no utilizar accidentalmente referencias absolutas. Cada celda puede hacer referencia a un fragmento diferente de datos y alterar tus clasificaciones.
Para evitarlo, utiliza referencias absolutas, como $A$1:$A$10
cuando configures tus fórmulas para fijar el rango correcto en todas tus clasificaciones.
No gestionar duplicados
Supongamos que tienes dos vendedores empatados con el mayor número de ventas. La forma en que manejes los empates en tu clasificación puede suponer una gran diferencia: las funciones RANK()
y RANK.EQ()
tratan los empates de forma similar.
Por tanto, debes decidir si los valores empatados merecen el mismo o diferente rango o si sus rangos deben promediarse utilizando RANK.AVG()
. Luego, utiliza la función correcta según corresponda.
Pedido erróneo
Parece sencillo, pero si te olvidas de establecer el argumento order
en tu función puede ordenar los rankings en la dirección equivocada (ascendente o descendente).
Por eso siempre debes comprobar dos veces este argumento: Utiliza "0" (o déjalo en blanco) para el orden descendente y "1" para el ascendente. Asegúrate de que el orden se ajusta a cómo quieres clasificar tus datos.
Función fumble
Cuando trabajas con distintos conjuntos de datos, es muy importante elegir la función de clasificación correcta, porque proceder con una función de clasificación equivocada dará resultados indeseables.
Para evitar estos problemas, utiliza RANK.EQ()
para la clasificación estándar y RANK.AVG()
para situaciones en las que el manejo de los empates afecte al resultado, como el análisis estadístico.
Utilizar fórmulas obsoletas
A medida que tus datos crecen, debes actualizar las referencias de rango en tus fórmulas. Esto significa que los nuevos puntos de datos no se incluirán en tus clasificaciones, y puede que te frustres al no acertar la respuesta.
Por eso debes actualizar y comprobar regularmente los rangos de tus fórmulas. Esto garantiza que todos tus datos valiosos se tengan en cuenta para la clasificación.
Reflexiones finales
Ahora ya sabes cómo utilizar funciones como RANK()
, RANK.EQ()
, y RANK.AVG()
para tratar los empates y resolver diversos problemas de clasificación. Estas funciones ayudan en distintos escenarios, como analizar las notas de los alumnos u ordenar las cifras de ventas.
Si te interesa ampliar aún más tus conocimientos de Excel, te recomiendo encarecidamente que eches un vistazo a algunos cursos y recursos excelentes disponibles en Datacamp:
- Empieza con el curso Introducción a Excel para adquirir conocimientos básicos, y luego avanza hacia Análisis de datos en Excel y Fundamentos de Excel.
- Considera el curso Visualización de Datos en Excel para mejorar tu capacidad de visualizar datos.
- El curso Introducción a PowerQuery en Excel beneficia enormemente la manipulación de datos más avanzada.
- Sin embargo, para formalizar tus conocimientos de Excel, consulta esta guía completa sobre Cómo obtener una certificación de Microsoft Excel.
Preguntas frecuentes
¿Cuál es la diferencia entre RANK.EQ() y RANK.AVG()?
RANK.EQ()
asigna el mismo rango a los valores duplicados, mientras que RANK.AVG()
asigna el rango medio a los valores duplicados.
¿Puedes clasificar valores de texto con la fórmula RANK()?
No, la fórmulaRANK()
está diseñada para valores numéricos. Tendrías que utilizar funciones diferentes como RANK()
combinadas con otra lógica para los valores de texto.
¿Para qué sirve la función COUNTIF() para ajustar los rangos en caso de empate?
La función COUNTIF()
de Excel utiliza la fórmula RANK()
para contar las apariciones del valor hasta la celda actual y añade este recuento (menos uno) al rango original.
¿Cuál es el error en la fórmula RANK() en Excel?
Pueden producirse dos errores diferentes al utilizar la fórmulaRANK()
: #VALUE!
Error y #N/A
Error.
#VALUE!
RANK()
contienen valores no numéricos. Asegúrate de que todas las celdas del rango que se está clasificando y aquellas cuyo rango se calcula contienen datos numéricos.#N/A
Error: Este error se produce si la celda clasificada está fuera del rango especificado. Para evitar este error, comprueba que el valor clasificado está incluido en el rango seleccionado.
¡Aprende más sobre Excel y el análisis de datos con estos cursos!
Course
Data Preparation in Excel
Course
Data Visualization in Excel
blog
Las 25 mejores preguntas para entrevistas sobre Excel para todos los niveles
blog