Clasificación (ranking) ordinal con desempate aleatorio

En mi post anterior sobre clasificación (ranking) con Excel y con Power Query mostré un método de desempate basado en un "sorteo", es decir, asignar a cada miembro de la lista un número aleatorio y determinar la posición en caso de empate de acuerdo a ese número.

Como vimos, si usamos la función Number.Random de Power Query todas las filas de la tabla reciben el mismo número.

Buscando en la Internet dí con esta discusión donde se muestran algunas soluciones (aunque la única que funcionó con mi ejemplo fue a basada en List.Random).

Debo confesar que no entiendo cabalmente la solución propuesta que muestro en el post. Esto me produjo un sentimiento de incomodidad que me llevó a buscar mi propia solución.

Recordemos el problema a solucionar:

• tenemos un a lista de calificaciones de alumno;
• queremos ordenarla por calificación, de mayor a menor;
• en caso de "empate" (dos o más alumnos con la misma calificación), realizamos un sorteo para determinar la posición relativa de los "empatados";
• el sorteo lo hacemos calculando un número aleatorio para cada alumno. Luego ordenamos los empates de acuerdo al número aleatorio que recibió cada alumno.

La solución que desarrollé esta basada en la función Number.Random y en un "truco": combinar la consulta consigo misma.

Estos son los pasos aplicados partiendo de esta tabla de datos:

Ranking con Excel y con Power Query

Un docente me consulta sobre como ordenar una lista de alumnos de acuerdo a sus calificaciones. El problema del docente era cómo determinar la posición (ranking) de cada alumno en caso de empate (es decir, dos o más alumnos con la misma calificación).

Según Wikipedia hay cinco métodos de calcular la posición de un elemento en una lista (ranking)

• Ordinal simple (1,2,3,4): cada elemento recibe un número de clasificación secuencial sin tomar en cuenta "empates".
• Competencia estándar (1,2,2,4): los elementos que "empatan" reciben el mismo número de clasificación, y luego se deja un hueco en los números de clasificación.
• Competencia modificada (1,3,3,4): la clasificación se hace dejando los huecos en los números de clasificación antes de los conjuntos de elementos de igual rango (en lugar de después de ellos como en la clasificación estándar de la competencia).
• Clasificación Densa (1,2,2,3): los artículos que se "empatan" reciben el mismo número de clasificación, y los siguientes artículos reciben el número de clasificación inmediatamente posterior. 
• Clasificación fraccionada (1,2.5,2.5,4): los artículos que "empatan" reciben el mismo número de clasificación, que es la media de lo que tendrían en las clasificaciones ordinales. 

En un próximo post volveré sobre el tema de los métodos de ranking. En este post me centraré en el método ordinal simple y algunas variaciones. Digamos que ésta sea la lista de alumnos:

Cuando Excel se equivoca al ordenar datos

Hay muchos motivos por los cuales se pueden producir desajustes al ordenar una tabla en Excel. Cuando nos topamos con este problema, nuestra primera reacción es "¡hay un bug en Excel!". Pero los que "hemos fatigado los arduos senderos de Excel por años" (parafraseando al inmortal Borges), sabemos que en casi todos los casos la culpa recae sobre el usuario.

Este artículo de Microsoft contiene una lista de errores que pueden causar desajustes al ordenar datos.

Una vez dicho todo esto, veamos el siguiente caso

En esta tabla, los valores de la columna Porcentaje se obtienen de la tabla en la hoja "clientes", usando la función INDICE combinada con COINCIDIR.

Si observamos la fórmula en la columna "Porcentaje" veremos que Excel incluye la referencia a la hoja "ventas" a pesar de ser esta la hoja activa.

Esto se debe a que al construir la fórmula comenzamos en la hoja activa (Ventas), pasamos a la hoja "clientes" para señalar el rango de la matriz de búsqueda de INDICE y luego volvemos a "Ventas" para completar la fórmula.

Esta referencia a la hoja donde se encuentra la fórmula parece superflua pero inocua. Ahora veamos que pasa al ordenar la tabla por tipo de clientes. Como referencia recordemos que las ventas netas del Cliente 1 son 259,508

Las ventas neto del Cliente 1 ahora son 274,773!

Si nos fijamos en las fórmulas en la tabla ordenada por Tipo, veremos que, a pesar de que las referencias a la columna Tipo son relativas (por ejemplo, ventas!C3 para el Cliente 1), al ordenar la tabla éstas actúan como referencias absolutas.
En la imagen anterior podemos ver que el Cliente 10 se encuentra en la fila 3, pero la formula en la celda E3 se refiere a la celda C12, que era la fila del cliente antes de ordenar la tabla.

Para solucionar o evitar este problema lo que hacemos es eliminar la referencia a la hoja activa ("ventas" en nuestro caso) en las fórmulas. Nuestra fórmula ahora se verá así

=INDICE(clientes!$B$2:$B$4,COINCIDIR(C3,clientes!$A$2:$A$4,0))

Ahora, al ordenar los datos por tipo, no se producirá el desajuste