Uso de la función JERARQUIA combinada con SUMAPRODUCTO - Ranking de listas con más de un criterio

A tono con el mundial de fútbol que se acerca, uno de mis lectores me pregunta como es posible organizar una tabla de posiciones usando MS Excel, de manera que si hay empate en la cantidad de puntos, la posición se defina de acuerdo a la cantidad de goles a favor. En caso de persistir la igualdad, la posición se definiría por mayor diferencia de goles. Todo esto utilizando solamente fórmulas!!
Ya hemos visto en una entrada anterior sobre el uso de la función JERARQUIA (RANK en inglés), para dar un número de orden a los miembros de una lista de acuerdo a su posición relativa. En una nota adicional sobre la función JERARQUIA (RANK) vimos que esta función tiene un problema: en caso de "empate" ambos miembros en la lista reciben el mismo número de orden. Este "problema" puede ser usado a nuestro favor para resolver la pregunta sobre la tabla de posiciones.

Supongamos esta tabla de un campeonato imaginario




Como podemos ver los equipos 4,5 y 6 comparten la cuarta posición; los equipos 7,8 y 9 comparten la quinta posición

Empezamos creando columnas auxiliares (K, L y M), para facilitar el cálculo.


En la columna auxiliar K anotamos la fórmula =JERARQUIA(J5,$J$4:$J$19) para calcular el número de orden de acuerdo a la cantidad de puntos de cada equipo.

En la columna L anotamos =SUMAPRODUCTO((J5=$J$4:$J$19)*(G5<$G$4:$G$19)) para generar un ranking entre los equipos de igual cantidad de puntos, de acuerdo a los goles a favor.
En la columna M anotamos =SUMAPRODUCTO((J5=$J$4:$J$19)*(I5<$I$4:$I$19)) para general el ranking entre los equipos con igual cantidad de puntos, de acuerdo a la diferencia de goles.
Finalmente, combinamos las tres fórmulas en esta "mega-fórmula" que anotamos en la columna A
=JERARQUIA(J5,$J$4:$J$19)+SUMAPRODUCTO((J5=$J$4:$J$19)*(G5<$G$4:$G$19))+SUMAPRODUCTO((K5=$K$4:$K$19)*(L5=$L$4:$L$19)*(M5>$M$4:$M$19))

Una vez obtenido el ranking de los equipos de acuerdo a los tres criterios (puntaje, goles a favor y diferencia de goles) podemos ordenar nuestra tabla utilizando el menú Ordenar de acuerdo a la columna del ranking (A).

Esta fórmula funciona de la siguiente manera:

- el primer miembro, la función JERARQUIA, calcula el número de orden de acuerdo al puntaje.

- El segundo miembro, la primera función SUMAPRODUCTO, calcula un número de orden interno sólo para los equipos con el mismo puntaje. Este orden interno comienza con el número cero. Si el puntaje del equipo evaluado es único, esta fórmula da cero.

- El tercer miembro, la segunda función SUMAPRODUCTO, calcula un número de orden interno sólo para los equipos que tienen el mismo puntaje y la misma cantidad de goles a favor. Si esto no se cumple, da como resultado cero.

Esta fórmula puede ser escrita en forma más compacta utilizando nombres en lugar de rangos explícitos.



Categorías: Funciones&Formulas_

Technorati Tags: JEARARQUIA, SUMAPRODUCTO

Convertir un Numero Decimal en Excel a formato hh:mm:ss

Algunos sistemas de asistencia utilizan notación decimal en los reportes que producen. Por ejemplo, el total de horas de un operario que trabajó de la 8:00 a las 17:30, aparece en el informe como 9.50. Otro operario que trabajó de la 8:00 a las 17:45, aparecerá en el informe 9.75.
Como con muchos otros sistemas de información, estos informes son exportados a Excel. Al sumar las horas trabajadas por los dos operarios, el total que aparecerá en Excel será 9.50 + 9.75 = 19.25.
Este resultado es incorrecto, ya que el total de horas trabajadas es 19 horas y 15 minutos,

Para convertir, las horas y minutos del formato decimal al formato hh:mm:ss en Microsoft Excel utilizamos la siguiente fórmula: ="Horas.minutos" en forma decimal/24
Al usar esta fórmula resultará un número de serie. Para mostrar el número de serie en el formato correspondiente, elige Número en el menú Formato (en el menú Formato, hace clic en Celdas y después, selecciona la ficha Número) y selecciona hh:mm:ss.



Si queremos convertir minutos en formato decimal a formato hh:mm:ss, aplicamos la siguiente fórmula: ="minutos en formato decimal"/1440.

Finalmente si queremos convertir segundos exhibidos en formato decimal a formato hh:mm:ss, usamos: ="segundos en formato decimal"/86400.

Los divisores (24, 1440, 86400), se deben a que Excel utiliza notación decimal efectuar cálculos de tiempo, como expliqué en la entrada fechas y tiempo en MS Excel. La unidad (1) representa un día completo, que equivale a 24 horas, a 1440 minutos y a 86400 segundos.




Categorías: Varios_



Technorati Tags: Cálculos de tiempo en Excel

Formato personalizado de números en Excel – Nota II

En la nota anterior sobre formatos de números personalizados en Excel, expliqué, brevemente, como crear formatos de números en Excel. En esta nota completaré la explicación mostrando algunos de los códigos con los cuales se crean los formatos.

Para crear formatos personalizados usamos el menú Formato de Celdas---Personalizada. En la ventanilla "Tipo" escribimos la combinación de códigos que crean el formato deseado. Estos códigos son los siguientes:

"General": el formato general de Excel

"#": muestra únicamente los dígitos significativos y no muestra los ceros sin valor.

"0" (cero): muestra los ceros sin valor si un número tiene menos dígitos que ceros en el formato

"?": agrega los espacios de los ceros sin valor a cada lado del separador decimal, para alinear los decimales con el ancho fijo del formato. También se puede utilizar ? para las fracciones que tengan un número de dígitos variable.

"%": porcentaje

[color]: determina el color del número (ver ejemplo en la entrada anterior). El color debe ser el primer elemento de la sección. Ejemplos de colores: [Negro], [Azul], [Aguamarina], [Verde], [Fucsia] [Rojo], [Blanco], [Amarillo]

"texto": para agregar texto inmediatamente después (o antes) del número, ponga el texto deseado entre comillas, como en el ejemplo de la entrada anterior.

También se pueden generar formatos condicionales que se aplicarán únicamente si coinciden con las condiciones que se hayan especificado. La condición se escribe entre corchetes y consta de un operador de comparación y un valor. Por ejemplo, el siguiente formato muestra los números iguales o inferiores a 100 en color rojo y los números superiores a 100 en color azul.

[Rojo][<=100];[Azul][>100]

En general es más eficiente utilizar el comando Formato Condicional en el menú Formato para formatos condicionales.

En la próxima nota mostraré los códigos que se utilizan para crear formatos de fechas y horas.

Categorías: Varios_

Technorati Tags: Formato personalizado de numeros