Ordenando rangos por jerarquía con Excel – Reseña

La función JERARQUIA de Excel calcula la posición relativa de un valor dentro de una lista. En notas anteriores hemos mostrado como usar esta función, sus limitaciones y algunas formas de superarlas.
En la nota Construir con Excel una tabla con las 10 primeras posiciones mostramos como superar el problema el "empate". Allí dimos el ejemplo de esta lista de calificaciones de alumnos



En la columna C usamos la función JERARQUIA para determinar la posición de cada alumno de acuerdo a la calificación. Allí vemos que Perla y Cristina, que tienen la misma calificación, obtienen el mismo número de rango (1) y Carlos recibe el número 3. Para solucionar esta situación usamos la función JERARQUIA combinada con CONTAR.SI para "desempatar" entre alumnos con la misma calificación. La fórmula en la columna D es

=JERARQUIA(B2,puntaje)+CONTAR.SI($B$2:B2,B2)-1


Un lector me pregunta como hacer para que el alumno Carlos, que tiene la segunda mejor nota, aparezca con el número 2 ya que ocupa el segundo lugar y no con el número tres. Es decir, que cada alumno aparezca con el numero de posición que ocupa.
Para lograr esto tendremos que usar una fórmula desarrollada por Tushar Mehta. En nuestra tabla hemos agregado la columna D, que muestra el resultado deseado



La fórmula en la columna D es

={SUMA(1/SI($C$2:$C$25<C2,CONTAR.SI($C$2:C25,$C$2:$C$25),9.999999999E+307))+1}

Esta es una fórmula matricial. . Primero la introducimos la fórmula en la celda D2 pulsando CTRL+MAYUSCULAS +ENTER simultáneamente. Luego la copiamos al esto del rango (hasta D25 e nuestro ejemplo).
Algunos detalles a tomar en cuenta en esta fórmula:

# - La fórmula usa la columna C como columna auxiliar. Esta columna contiene la posición de cada alumno calculada con la función JERARQUIA.

# - La expresión CONTAR.SI($C$2:C25,$C$2:$C$25) calcula cuantas veces aparece un determinado valor en el rango, sólo si no se trata de la primera posición. Para determinar si el valor evaluado no es el de la primera posición, usamos la expresión $C$2:$C$25<C4.

# - La segunda parte la función SI, 9.999999999E+307 es un número expresado en forma exponencial y es el mayor número que Excel puede aceptar. Esto es necesario para evitar la división por cero cuando evaluamos el valor del primer puesto. La operación 1/9.999999999E+307 da como resultado 0.

Para ver como funciona esta fórmula, lo mejor es usar el botón Evaluar de la barra de auditoría de fórmulas.



Por ejemplo, seleccionamos la celda D5 y apretamos el botón Evaluar fórmula



Apretamos el botón Evaluar del diálogo y vemos la expresión que va a ser evaluada



Al volver a apretar Evaluar vemos el resultado de la operación



En nuestro caso, se genera una matriz con 4 resultados VERDADERO y el resto FALSO.
En el próximo paso, Evaluar nos muestra la matriz generada por CONTAR.SI



Ahora vemos que el resultado de CONTAR.SI es la matriz {2,2,1,1,1,9,99..9E+07…}



Al dividir los miembros de esta matriz por 1 obtenemos



Y al sumar la nueva matriz, nos da



Es decir, 4, la posición del valor en la lista.

Technorati Tags: MS Excel