Calendario del Campeonato Mundial de Futbol en Excel

Los interesados en seguir las instancias del Campeonato Mundial de Futbol, pueden descargar un excelente cuaderno Excel con un calendario activo del campeonato.

Este calendario fue desarrollado por la gente de Excely.com en una hoja de cálculo, funciona solamente con fórmulas, sin macros. Se puede descargar gratuitamente aquí.

La hoja tiene un botón que permite "traducir" las leyendas a 20 idiomas! La hoja es ideal para armar pronósticos.

Felicitaciones a la gente de Excely.com

Ahhh! También pueden leer mi entrada sobre ordenar una tabla de acuerdo a varios criterios usando las funciones JERARQUIA y SUMAPRODUCTO. El ejemplo se basa en una tabla de posiciones.

Categorías: Varios_

Calculos instantaneos de valores en un rango seleccionado en MS Excel

Como ya he comentado en alguna oportunidad, este blog resume en gran parte, consultas de mis compañeros de trabajo que utilizan Excel para sus tareas cotidianas.
Uno de mis "tips" favoritos, y que pocos de mis compañeros conocen, es el uso de esta funcionalidad:

Al apretar el botón derecho del mouse, cuando este apunta a la barra de situación (inmediatamente por debajo de las pestañas de la hoja, si no ha alguna barra de por medio), se abre este menú

Estas funciones dan el resultado correspondiente a todo rango que tengamos seleccionado en el momento de abrir el menú.

Por ejemplo, si la función activa es SUMA, y tenemos seleccionado un rango de números enteros de 1 a 15, veremos en la barra inferior el resultado "120"

Si la función seleccionada es PROMEDIO, veremos el resultado "8".


Esta funcionalidad también funciona con rangos no contiguos



De la misma manera podemos utilizar la funciones MAX, MIN, Cuenta (CONTARA) y Cuenta.Num (CONTAR) .

Esta funcionalidad es muy útil para realizar cálculos "instant", sin necesidad de anotar fórmulas en la hoja.

Categorías: Varios_

Technorati Tags: Excel

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