El tutorial explica las especificidades de la función Rank en Excel y muestra cómo clasificar en Excel según varios criterios, clasificar los datos por grupo, calcular el rango percentil y más. Cuando necesites determinar la posición relativa de un número en una lista de números, la forma más sencilla es ordenar los números en orden ascendente o descendente. Si por alguna razón no es posible ordenar, una fórmula de rango en Excel es la herramienta perfecta para hacer el trabajo.
Función de RANGO de Excel
La función RANK en Excel devuelve el orden (o rango) de un valor numérico en comparación con otros valores en la misma lista. En otras palabras, te dice qué valor es el más alto, el segundo más alto, entre otros.
Quizás te puede interesar: Función ELEGIR De Excel. Fórmulas Y Ejemplos
En una lista ordenada, el rango de un cierto número sería la posición. La función Rango en Excel puede determinar el rango comenzando con el valor más grande (como si estuviera ordenado en orden descendente) o el valor más pequeño (como si estuviera ordenado en orden ascendente). La sintaxis de la función RANK de Excel es la siguiente:
RANK (número, ref, [orden])
Dónde:
- Número (obligatorio): el valor cuya clasificación te gustaría encontrar.
- Ref (obligatorio): una lista de valores numéricos con los que clasificar. Puede proporcionarse como una matriz de números o como una referencia a la lista de números.
- Orden (opcional): un número que especifica cómo clasificar los valores:
- Si es 0 o se omite, los valores se clasifican en orden descendente, es decir, de mayor a menor.
- Si es 1 o cualquier otro valor distinto de cero, los valores se clasifican en orden ascendente, es decir, de menor a mayor.
Función RANK.EQ en Excel
RANK.EQ es una versión mejorada de la función RANK, introducida en Excel 2010. Tiene la misma sintaxis que RANK y funciona con la misma lógica: si varios valores se clasifican por igual, el rango más alto se asigna a todos esos valores. (EQ significa «igual»).
RANK.EQ (número, ref, [orden])
En Excel 2007 y versiones inferiores, siempre debes utilizar la función RANK. En Excel 2010, Excel 2013 y Excel 2016, puedes elegir RANK o RANK.EQ. Sin embargo, sería aconsejable utilizar RANK.EQ porque RANK puede suspenderse en cualquier momento.
Función RANGO.AVG de Excel
RANK.AVG es otra función para encontrar el rango en Excel que está disponible solo en Excel 2010, Excel 2013, Excel 2016 y versiones posteriores. Tiene la misma sintaxis que las otras dos funciones:
RANK.AVG (número, ref, [orden])
La diferencia es que si más de un número tiene el mismo rango, se devuelve el rango promedio (AVG significa «promedio»).
4 cosas que debe saber sobre RANK en Excel
Estas son algunas de las cosas a tomar en cuenta acerca de la función Rank en Excel:
- Cualquier fórmula de rango en Excel funciona solo para valores numéricos: números positivos y negativos, ceros, valores de fecha y hora. Los valores no numéricos del argumento ref se ignoran.
- Todas las funciones de RANGO devuelven el mismo rango para valores duplicados y omiten la clasificación posterior, como se muestra en el ejemplo siguiente.
- En Excel 2010 y versiones posteriores, la función RANK se reemplazó con RANK.EQ y RANK.AVG. Para compatibilidad con versiones anteriores, RANK todavía funciona en todas las versiones de Excel, pero es posible que no esté disponible en el futuro.
- Si el número no se encuentra dentro de la referencia, cualquier función de rango de Excel devolvería el error # N / A.
Fórmula básica de rango de Excel (de mayor a menor)
Para obtener más información sobre la clasificación con la función Rank en Excel, echa un vistazo a esta captura de pantalla:
Las tres fórmulas clasifican los números en la columna B en orden descendente (se omite el argumento de orden):
En todas las versiones de Excel 2003 – 2016:
=RANK($B2,$B$2:$B$7)
En Excel 2010 – 2016:
=RANK.EQ($B2,$B$2:$B$7)
=RANK.AVG($B2,$B$2:$B$7)
La diferencia está en cómo estas fórmulas procesan valores duplicados. Como puedes ver, la misma puntuación aparece dos veces, en las celdas B5 y B6, lo que afecta la clasificación posterior:
- Las fórmulas RANK y RANK.EQ dan un rango de 2 a ambas puntuaciones duplicadas. La siguiente puntuación más alta (Daniela) ocupa el cuarto lugar. A nadie se le da un rango de 3.
- La fórmula RANK.AVG asigna un rango diferente a cada duplicado detrás de escena (2 y 3 en este ejemplo) y devuelve el promedio de esos rangos (2.5). Nuevamente, el tercer rango no se asigna a nadie.
Cómo usar RANK en Excel – ejemplos de fórmulas
El camino hacia la excelencia, dicen, está pavimentado con práctica. Entonces, para aprender mejor cómo usar la función RANK en Excel, sola o en combinación con otras funciones, busquemos soluciones para algunas tareas de la vida real.
Cómo clasificar en Excel de menor a mayor
Como se muestra en el ejemplo anterior, para clasificar los números de mayor a menor, usa una de las fórmulas de rango de Excel con el argumento de orden establecido en 0 u omitido (predeterminado).
Para tener un número clasificado frente a otros números ordenados en orden ascendente, coloca 1 o cualquier otro valor distinto de cero en el tercer argumento opcional. Por ejemplo, para clasificar los tiempos de sprint de 100 metros de los estudiantes, puedes usar cualquiera de las fórmulas siguientes:
=RANK(B2,$B$2:$B$7,1)
=RANK.EQ(B2,$B$2:$B$7,1)
Presta atención a que bloqueamos el rango en el argumento ref mediante el uso de referencias de celda absolutas, de modo que no cambie cuando copiemos la fórmula en la columna.
Como resultado, el valor más bajo (tiempo más rápido) ocupa el primer lugar y el valor más grande (tiempo más lento) obtiene el rango más bajo de 6. Los tiempos iguales (B2 y B7) reciben el mismo rango.
Cómo clasificar datos en Excel de forma única
Como se señaló anteriormente, todas las funciones de rango de Excel devuelven el mismo rango para elementos de igual valor. Si eso no es lo que deseas usa una de las siguientes fórmulas para resolver situaciones de desempate y otorga un rango único a cada número.
Clasificación única de mayor a menor
Para clasificar los puntajes de matemáticas estudiantes únicamente en orden descendente, usa esta fórmula:
=RANK.EQ(B2,$B$2:$B$7)+COUNTIF($B$2:B2,B2)-1
Clasificación única de menor a mayor
Para clasificar los resultados de la carrera de 100 metros en orden ascendente sin duplicados, usa esta fórmula:
=RANK.EQ(B2,$B$2:$B$7,1) + COUNTIF($B$2:B2,B2)-1
Cómo funcionan estas fórmulas
Como habrás notado, la única diferencia entre las dos fórmulas es el argumento de orden de la función RANK.EQ: se omite para clasificar valores descendentes, 1 para clasificar ascendentes. En ambas fórmulas, es la función COUNTIF con el uso inteligente de referencias de celda relativas y absolutas lo que hace el truco.
En resumen, usa COUNTIF para averiguar cuántas ocurrencias del número que se clasifica hay en las celdas anteriores, incluida la celda del número. En la fila superior donde ingresa la fórmula, el rango consta de una sola celda ($ B $ 2: B2). Pero debido a que bloquea solo la primera referencia ($ B $ 2), la última referencia relativa (B2) cambia según la fila donde se copia la fórmula.
Por lo tanto, para la fila 7, el rango se expande a $ B $ 2: B7 y el valor en B7 se compara con cada una de las celdas anteriores. En consecuencia, para todas las primeras apariciones, COUNTIF devuelve 1; y resta 1 al final de la fórmula para restaurar el rango original.
Para las segundas ocurrencias, COUNTIF devuelve 2. Al restar 1, incrementa el rango en 1 punto, evitando así duplicados. Si hay 3 apariciones del mismo valor, COUNTIF () – 1 agregaría 2 a tu clasificación, y así sucesivamente.
Solución alternativa para romper los lazos de RANGO de Excel
Otra forma de clasificar los números en Excel de forma única es sumando dos funciones COUNTIF:
- La primera función determina cuántos valores son mayores o menores que el número que se clasificará, dependiendo de si estás clasificando descendente o ascendente, respectivamente.
- La segunda función (con el «rango en expansión» $B $2:B2 como en el ejemplo anterior) obtienes el número de valores igual al número.
Por ejemplo, para clasificar los números únicamente de mayor a menor, usaría esta fórmula:
=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIF($B$2:B2,B2)
Como se muestra en la captura de pantalla a continuación, el desempate se resuelve con éxito y se asigna un rango único a cada estudiante:
Clasificación en Excel basada en múltiples criterios
El ejemplo anterior ha demostrado dos soluciones de trabajo para una situación de desempate de RANGO de Excel. Sin embargo, puede parecer injusto que números iguales se clasifiquen de manera diferente basándose únicamente en su posición en la lista.
Para mejorar tu clasificación, es posible que desees agregar un criterio más para ser considerado en caso de empate. En nuestro conjunto de datos de muestra, agreguemos puntajes totales en la columna C y calculemos el rango de la siguiente manera:
- Primero, clasifica con puntaje matemático (criterio principal).
- Cuando haya un empate, rómpelo con la puntuación total (criterio secundario)
Para hacerlo, usaremos una fórmula RANK / RANK.EQ regular para encontrar el rango, y la función COUNTIF para romper un empate:
=RANK.EQ($B2,$B$2:$B$7)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)
En comparación con el ejemplo anterior, esta fórmula rango es más objetiva: Timoteo está en el puesto 2 nd porque su puntuación total es superior a la de Julia:
Cómo funciona esta fórmula
La parte RANK de la fórmula es obvia y la función COUNTIFS hace lo siguiente:
- El primer par criterio_rango / criterio ($ B $ 2: $ B $ 7, $ B2) cuenta las apariciones del valor que estás clasificando. Ten en cuenta que arreglamos el rango con referencias absolutas, pero no bloqueamos la fila de criterios ($ B2) para que la fórmula verifique el valor en cada fila individualmente.
- El segundo par de rango_criterio / criterio ($ C $ 2: $ C $ 7, «>» & $ C2) averigua cuántos puntajes totales son mayores que el puntaje total del valor que se clasifica.
Debido a que COUNTIFS funciona con la lógica AND, es decir, cuenta solo las celdas que cumplen con todas las condiciones especificadas, devuelve 0 para Timothy, ya que ningún otro estudiante con el mismo puntaje en matemáticas tiene un puntaje total más alto. Por lo tanto, el rango de Timothy devuelto por RANK.EQ no cambia.
Para Julia, la función COUNTIF devuelve 1 porque un estudiante con el mismo puntaje en matemáticas tiene un total más alto, por lo que su número de rango se incrementa en 1. Si un estudiante más obtuvo el mismo puntaje en matemáticas y un puntaje total más bajo que el de Timothy y Julia, su rango se incrementará en 2, y así sucesivamente.
Soluciones alternativas para clasificar números con múltiples criterios
En lugar de la función RANK o RANK.EQ, puedes usar COUNTIF para verificar los criterios principales y COUNTIFS o SUMPRODUCT para resolver un desempate:
=COUNTIF($B$2:$B$7,»>»&$B2)+COUNTIFS($B$2:$B$7,$B2,$C$2:$C$7,»>»&$C2)+1
=COUNTIF($B$2:$B$7,»>»&B2)+SUMPRODUCT(–($C$2:$C$7=C2),–($B$2:$B$7>B2))+1
El resultado de estas fórmulas es exactamente el mismo que se muestra arriba.
Cómo calcular el rango percentil en Excel
En estadística, un percentil es el valor por debajo del cual cae un cierto porcentaje de valores en un conjunto de datos dado. Por ejemplo, si el 70% de los estudiantes son iguales o inferiores a la puntuación de su examen, su rango percentil es 70.
Para obtener el rango percentil en Excel, usa la función RANK o RANK.EQ con un argumento de orden distinto de cero para clasificar los números de menor a mayor y luego dividir la clasificación por el recuento de números. Entonces, la fórmula genérica de rango percentil de Excel es la siguiente:
RANK.EQ (topmost_cell , rango , 1) / COUNT (rango)
Para calcular el rango percentil de los estudiantes, la fórmula toma la siguiente forma:
=RANK.EQ(B2,$B$2:$B$7,1)/COUNT($B$2:$B$7)
Para que los resultados se muestren correctamente, asegúrate de establecer el formato de porcentaje en las celdas de fórmula:
Tal vez quieras saber: Cómo Sumar Por Categoría En Excel
Cómo clasificar números en celdas no adyacentes
En situaciones en las que necesites clasificar celdas no contiguas, proporciona esas celdas directamente en el argumento ref de tu fórmula de rango de Excel en forma de unión de referencia, bloqueando las referencias con el signo $. Por ejemplo:
=RANK(B2,($B$2,$B$4,$B$6))
Para evitar errores en celdas no clasificadas, ajusta RANK en la función IFERROR, así:
=IFERROR(RANK(B2,($B$2,$B$4,$B$6)),»»)
Ten en cuenta que a un número duplicado también se le asigna un rango, aunque la celda B5 no está incluida en la fórmula:
Si necesitas clasificar varias celdas no contiguas, la fórmula anterior puede resultar demasiado larga. En este caso, una solución más elegante sería definir un rango con nombre y hacer referencia a ese nombre en la fórmula:
=IFERROR(RANK(B2,range),»»)
Cómo clasificar en Excel por grupo
Al trabajar con entradas organizadas en algún tipo de estructura de datos, los datos pueden pertenecer a varios grupos y es posible que desees clasificar los números dentro de cada grupo individualmente. La función RANK de Excel no puede resolver este desafío, por lo que vamos a utilizar una fórmula SUMPRODUCT más compleja:
Clasifica por grupo en orden descendente:
=SUMPRODUCT((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1
Clasifica por grupo en orden ascendente:
=SUMPRODUCT((A2=$A$2:$A$7)*(C2>$C$2:$C$7))+1
Dónde:
- A2: A7 son grupos asignados a números.
- C2: C7 son números para clasificar.
En este ejemplo, usamos la primera fórmula para clasificar los números en cada grupo de mayor a menor:
Cómo funciona esta fórmula
Básicamente, la fórmula evalúa 2 condiciones:
- Primero, verifica el grupo (A2 = $ A $ 2: $ A $ 7). Esta parte devuelve una matriz de VERDADERO y FALSO en función de si un elemento de rango pertenece al mismo grupo que A2.
- En segundo lugar, verifica la puntuación. Para clasificar los valores de mayor a menor (orden descendente), usa la condición (C2 <$ C $ 2: $ C $ 11), que devuelve VERDADERO para las celdas mayores o iguales a C2, FALSO en caso contrario.
Dado que en términos de Microsoft Excel, VERDADERO = 1 y FALSO = 0, multiplicar las dos matrices da una matriz de 1 y 0, donde 1 se devuelve solo para las filas en las que se cumplen ambas condiciones. Luego, SUMPRODUCT suma los elementos de la matriz de 1 y 0, por lo que devuelve 0 para el número más grande de cada grupo. Y agrega 1 al resultado para comenzar a clasificar con 1.
La fórmula que clasifica los números dentro de los grupos de menor a mayor (orden ascendente) funciona con la misma lógica. La diferencia es que SUMPRODUCTO devuelve 0 para el número más pequeño en un grupo en particular, ya que ningún número en ese grupo cumple la 2ª condición (C2> $ C $ 2: $ C $ 7). Nuevamente, reemplaza el rango cero con el primer rango agregando 1 al resultado de la fórmula.
En lugar de SUMPRODUCT, puedes usar la función SUM para sumar los elementos de la matriz. Pero esto requeriría usar una fórmula de matriz, completada a través de Ctrl + Mayús + Entrar. Por ejemplo:
=SUM((A2=$A$2:$A$7)*(C2<$C$2:$C$7))+1
Cómo clasificar números positivos y negativos por separado
Si tu lista de números contiene valores tanto positivos como negativos, la función RANK en Excel los clasificará a todos en poco tiempo. Pero, ¿y si quisieras tener números positivos y negativos clasificados por separado? Con números en las celdas A2 a A10, usa una de las siguientes fórmulas para obtener una clasificación individual para los valores positivos y negativos:
Clasifica números positivos descendentes:
=IF($A2>0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)
Clasifica los números positivos en forma ascendente:
=IF($A2>0,COUNTIF($A$2:$A$10,»>0″)-COUNTIF($A$2:$A$10,»>»&$A2),»»)
Clasificar números negativos descendentes:
=IF($A2<0,COUNTIF($A$2:$A$10,»<0″)-COUNTIF($A$2:$A$10,»<«&$A2),»»)
Clasifica los números negativos en forma ascendente:
=IF($A2<0,COUNTIF($A$2:$A$10,»<«&$A2)+1,»»)
Los resultados se verán algo similar a esto:
Cómo funcionan estas fórmulas
Para empezar, analicemos la fórmula que clasifica los números positivos en orden descendente:
- En la prueba lógica de la función IF, verifica si el número es mayor que cero.
- Si el número es mayor que 0, la función CONTAR.SI devuelve el recuento de valores más altos que el número que se clasifica.
En este ejemplo, A2 contiene el segundo número positivo más alto, para el cual COUNTIF devuelve 1, lo que significa que solo hay un número mayor que él. Para comenzar nuestra clasificación con 1, no 0, agregamos 1 al resultado de la fórmula, por lo que devuelve una clasificación de 2 para A2.
Si el número es mayor que 0, la fórmula devuelve una cadena vacía («»). La fórmula que clasifica los números positivos en orden ascendente funciona de manera un poco diferente: Si el número es mayor que 0, el primer COUNTIF obtiene el recuento total de números positivos en el conjunto de datos y el segundo COUNTIF averigua cuántos valores son más altos que ese número.
Luego, resta el último del primero y obtiene el rango deseado. En este ejemplo, hay 5 valores positivos, 1 de los cuales es mayor que A2. Entonces, resta 1 de 5, obteniendo así un rango de 4 para A2. Las fórmulas para clasificar números negativos se basan en una lógica similar.
Nota: todas las fórmulas anteriores ignoran los valores cero porque 0 no pertenece ni al conjunto de números positivos ni al conjunto de números negativos. Para incluir ceros en tu clasificación, reemplaza> 0 y <0 con> = 0 y <= 0, respectivamente, donde la lógica de la fórmula lo requiera. Por ejemplo, para clasificar números positivos y ceros de mayor a menor, utiliza esta fórmula:
=IF($A2>=0,COUNTIF($A$2:$A$10,»>»&A2)+1,»»)
Cómo clasificar datos en Excel ignorando valores cero
Como ya sabes, una fórmula de RANGO es Excel maneja todos los números: positivo, negativo y ceros. Pero en algunos casos, solo queremos clasificar las celdas con datos que ignoran los valores 0. Puedes encontrar algunas posibles soluciones para esta tarea, pero la fórmula de Excel RANK IF es la más universal:
Números de rango descendente ignorando el cero:
=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10), RANK($B2,$B$2:$B$10)-COUNTIF($B$2:$B$10,0)))
Números de rango ascendentes ignorando el cero:
=IF($B2=0,»»,IF($B2>0,RANK($B2,$B$2:$B$10,1) – COUNTIF($B$2:$B$10,0), RANK($B2,$B$2:$B$10,1)))
Donde B2: B10 es el rango de números que se clasificarán. Lo mejor de esta fórmula es que funciona maravillosamente tanto para números positivos como negativos, dejando los valores cero fuera de la clasificación:
Cómo funciona esta fórmula
A primera vista, la fórmula puede parecer un poco complicada. Mirando más de cerca, la lógica es muy simple. Así es como la fórmula RANK IF de Excel clasifica los números de mayor a menor ignorando los ceros:
- El primer IF comprueba si el número es 0 y, si lo es, devuelve una cadena vacía:
SI ($ B2 = 0, «»,…)
- Si el número no es cero, el segundo IF comprueba si es mayor que 0, y si lo es, una función RANK / RANK.EQ normal calcula el rango:
SI ($ B2> 0, RANK ($ B2, $ B $ 2: $ B $ 10),…)
- Si el número es menor que 0, ajusta la clasificación por el recuento de cero. En este ejemplo, hay 4 números positivos y 2 ceros. Entonces, para el número negativo más grande en B10, una función de Rango en Excel devolvería 7. Pero omitimos ceros y, por lo tanto, necesitamos ajustar el rango en 2 puntos. Para esto, restamos el número de ceros del rango:
RANGO ($ B2, $ B $ 2: $ B $ 10) -COUNTIF ($ B $ 2: $ B $ 10,0))
¡Sí, es así de fácil! La fórmula para clasificar números de menor a mayor ignorando ceros funciona de manera similar, y podría ser un buen ejercicio mental para deducir su lógica.
Cómo calcular el rango en Excel por valor absoluto
Cuando se trata de una lista de valores positivos y negativos, puede ser necesario clasificar los números por sus valores absolutos ignorando el signo. La tarea se puede cumplir con una de las fórmulas siguientes, en el corazón de la cual se encuentra la función ABS que devuelve el valor absoluto de un número:
Rango ABS descendente:
=SUMPRODUCT((ABS(A2)<=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>»»))+1
Rango ABS ascendente:
=SUMPRODUCT((ABS(A2)>=ABS(A$2:A$7)) * (A$2:A$7<>»»)) – SUMPRODUCT((ABS(A2)=ABS($A$2:$A$7)) * (A$2:A$7<>»»))+1
Como resultado, los números negativos se clasifican como si fueran números positivos:
Cómo obtener N valores más grandes o más pequeños
Si desea obtener un número N real de los valores más grandes o más pequeños en lugar de su clasificación, use la función LARGE o SMALL, respectivamente. Por ejemplo, podemos obtener los 3 mejores puntajes de los estudiantes con esta fórmula:
=LARGE($B$2:$B$7, $D3)
Donde B2: B7 es la lista de puntuaciones y D3 es el rango deseado. Además, puedes recuperar los nombres de los estudiantes mediante la fórmula de PARTIDA DE ÍNDICE (siempre que no haya puntajes duplicados en los 3 primeros):
=INDEX($A$2:$A$7,MATCH(E3,$B$2:$B$7,0))
Del mismo modo, puedes utilizar la función SMALL para extraer los 3 valores inferiores:
=SMALL($B$2:$B$7, $D3)
Echa un vistazo a: Cómo Combinar Excel Con Word: Importar Datos De Excel A Word
Pensamientos finales
Así es como se clasifica utilizando la Función Rank en Excel. ¡Gracias por leer y esperamos verte en nuestro blog la próxima semana! Hay una gran cantidad de tutoriales disponibles para aclarar dudas relacionadas con el área de la informática. Volviendo al tema, hay muchas formas alternativas de calcular el rango en Excel, cada una con sus particularidades. Tú decides cómo quieres trabajar.
