Cómo usar la función Rank y SUMPRODUCT en Excel con COUNTIF
Clasificar elementos en orden ascendente o descendente mediante la combinación de las funciones COUNTIF, RANK.EQ y SUMPRODUCT es relativamente simple. Puede ordenar los elementos con o sin duplicación y mantener el control para omitir o no omitir ningún rango en la secuencia.
Al asignar los mismos rangos a múltiples entradas idénticas, la función RANK.EQ omite números en la secuencia general. Para evitar este salto, utilice la función SUMPRODUCT con la función COUNTIF. Esta combinación clasifica los valores duplicados sin omitir los números.
Diferentes funciones de rango en Excel
Antes de echar un vistazo más de cerca a su implementación, revisemos brevemente sus otros dos formularios que se encuentran en Excel; RANK.AVG y RANK.
En versiones anteriores de Excel, solo estaba disponible la función RANK. Sin embargo, desde Excel 2010, se han agregado dos funciones de rango adicionales, a saber, RANK.AVG y RANK.EQ. Exploremos sus diferencias, ya que probablemente las verá todas en Excel.
Función RANK.EQ: RANK.EQ es una versión nueva y mejorada de la función RANK y es compatible con las últimas versiones de Excel. Sin embargo, ambas funciones son las mismas y tienen el mismo propósito. Independientemente de lo que use, obtendrá los mismos resultados.
Función RANK: La función RANK, por otro lado, está disponible para compatibilidad con Excel 2007 y versiones anteriores y puede eliminarse en cualquiera de las nuevas versiones de Excel. Hoy en día, todo el mundo usa las versiones de Excel más recientes, así que siempre use la función RANK.EQ cuando tenga que compartir hojas con otros.
RANGO. AVG: cuando cualquier secuencia de rango contiene los duplicados, la función RANK.AVG asigna un rango promedio a cada conjunto de duplicados. Por lo tanto, tiene el mismo propósito de clasificar elementos, pero su objetivo principal es detectar duplicados.
Como este artículo trata sobre el cálculo de las clasificaciones para la lista de elementos, veamos cómo la función RANK.EQ los clasificará. Además, verá su uso en combinación con la función CONTAR.SI.
Antes de implementar la fórmula, veamos brevemente la sintaxis de la función RANK.EQ.
Una descripción general de la función de clasificación de Excel
Entonces, hay tres argumentos en la función RANK.EQ; número, ref y orden. Los dos primeros argumentos son obligatorios, mientras que el tercero es opcional. Para ejecutar RANK.EQ en una matriz de números, siempre debe especificar los dos primeros argumentos.
Echemos un vistazo a la función de cada argumento:
- Número: este argumento se refiere al número cuyo rango desea conocer.
- Ref: el argumento Ref se refiere a una matriz de listas de números en la secuencia. Tenga en cuenta que la referencia ignora cualquier valor no numérico que se encuentre en la matriz.
- Orden: a pesar de ser un argumento opcional, puede especificar la función RANK para clasificar elementos en orden descendente o ascendente eligiendo 0 o 1 en este argumento. De forma predeterminada, si lo omite, clasificará los elementos en orden descendente.
Ejemplo de función de rango de Excel
Entonces, ahora que comprende los argumentos de la función RANK.EQ, pongámosla en práctica en un conjunto de datos para ver cómo funciona.
Como ejemplo, observe los datos de muestra que contienen porcentajes de diez estudiantes. Puede utilizar la función Rank.EQ para calcular rápidamente los rangos en función de los porcentajes de estos estudiantes. Esto elimina la necesidad de calcularlos manualmente.
Sin embargo, omite números cuando se trata de duplicados, por lo que es posible que deba utilizar otras funciones que se describen más adelante en este artículo. A continuación se muestra la lista de estudiantes con sus porcentajes finales.
El primer argumento es el número que desea clasificar, que es el porcentaje de James Walker y otros estudiantes en la fila de este ejemplo. Entonces, seleccione la celda B2 como primer argumento.
El rango de referencia, en este caso, será de las celdas B2 a B11, y elijamos el orden ascendente en el argumento de orden. A continuación puede ver la implementación de la función RANK.EQ. La fórmula se ve así:
=RANK.EQ(B2,$B$2:$B$11,0)
Al presionar Enter, se ejecutará la fórmula y obtendrás el rango de James Walker.
En este ejemplo, James Walker ocupa el 5 º en la clase sobre la base de su porcentaje, lo que significa su porcentaje es 5º más alto en comparación con el resto de los números en una matriz.
Uso de la función RANK en combinación con la función COUNTIF
Es posible utilizar la función RANK.EQ combinada con la función COUNTIF para detener la omisión de números, pero también ignorará la duplicación de rango. Aunque ningún número se repetirá dos veces, dos estudiantes que tengan el mismo número pueden obtener diferentes rangos.
Para entender esto mejor, veamos cómo funciona RANK.EQ junto con COUNTIF. La fórmula se ve así:
=RANK.EQ(B2,$B$2:$B$11,0)+COUNTIF($B$2:B2,B2)-1La implementación de esta fórmula resolverá el problema de saltarse números.
No hay duplicación en los rangos anteriores. Pero, James Walker y Gillian Tillman, quienes se suponía que tenían el mismo rango, ahora están clasificados de manera diferente.
Por lo tanto, el uso de RANK.EQ con COUNTIF ha resuelto la mitad del problema, pero no genera el resultado deseado.
Uso de la función SUMPRODUCT con la función COUNTIF
Para clasificar a los estudiantes en una lista asignando los mismos rangos para porcentajes equivalentes sin omitir ningún número, puede usar la función SUMPRODUCT con COUNTIF.
Eche un vistazo a la fórmula a continuación:
La fórmula puede parecer complicada, pero es la mejor manera de clasificar los elementos correctamente. De esta manera, puede lograr los resultados deseados con duplicación de rango y sin saltos de números.
Mientras crea el resultado para sus estudiantes, puede usar directamente la fórmula SUMPRODUCT como reemplazo de la función RANK. Para cálculos de clasificación no duplicados, puede usar la función RANK.EQ sola o con la función COUNTIF.
Cambio de orden del resultado final
En la pestaña Datos , haga clic en el grupo Ordenar y filtrar y elija un orden ascendente para ordenar las clasificaciones.
Compare los resultados en tres filas una al lado de la otra para comprender mejor cómo funciona cada método de clasificación de elementos.
Clasifique elementos fácilmente con las funciones RANK.EQ y SUMPRODUCT
A continuación se explica cómo puede utilizar las funciones RANK y SUMPRODUCT en combinación con la función COUNTIF para clasificar elementos, con o sin duplicaciones. También puede decidir si se salta algún número de la secuencia o no.
Dependiendo de la situación, puede usar una variedad de métodos para clasificar los números de acuerdo con un conjunto de criterios dados. Este es un ejemplo de cómo las funciones de Excel pueden ayudarlo a ahorrar mucho tiempo y esfuerzo en los cálculos manuales. Hay mucho más por explorar para diseñar hojas de cálculo de Excel de aspecto profesional.