No, Excel no puede ayudarte a ganar la lotería

A lo largo de la vida de este blog he recibido no pocas consultas relacionadas con todo tipo de cálculos para acertar los números de la lotería. Digamoslo claramente: no, no se puede predecir el resultado de los sorteos de la lotería.
No sólo no se pueden predecir los resultados, sino que también las posibilidades ganar el primer premio son prácticamente inexistentes. Supongamos, por ejemplo, una loteria donde hay que acertar 6 de un conjunto de 49 números. Para calcular la probabilildad de acertar la cambinación ganadora, si n es el conjunto de 49 números que participan en el sorteo y k es cada una de las combinaciones posibles de 6 números, sin repeticiones, la expresión

n!
-----------------
k! * (n-k)!

donde el símbolo ! indica la operación factorial. Excel nos permite realizar el cálculo con facilidad usando la función COMBINAT(). La expresión =1/COMBINAT(n,k) nos da la probabilidad de que una combinación dada de seis números salga en el sorteo

Como puede apreciarse hay casi 14 millones de combinaciones posibles, es decir una probabilidad de 0.00000007 que nuestros números formen la combinación ganadora.

Sin embargo cada tanto alguién gana y se convierte enn un nuevo millonario. Por lo que seguiremos jugando y soñando qué hacer cuando la diosa fortuna golpee a nuestra puerta.
Y si bien Excel no nos puede ayudar a ganar la lotería, si puede ayudarnos a controlar si hemos obtenido algún premio.
Supongamos que nuestra lotería premia combinaciones de por lo menos 3 números ganadores. Supongamos también que siempre apostamos a la misma combinación y supongamos también que hemos jugado esta combinación durante diez sorteos y ahora queremos comprobar si hemos tenido suerte. Para hacerlo usaremos Formato Condicional y también SUMAPRODUCTO. La hoja Excel que aparece aquí abajo es interactiva y hay que desplazar la hoja hacia la derecha para ver las columnas K y L (también puede descargarse el archivo usando el icono en el ángulo inferior derecho).



Para señalar con un color de fondo los números sorteados que coinciden con "Mis_números" usamos format condicional con la opción Fórmula

Usamos ESNUMERO combinada con COINCIDIR para obtener resultado VERDADERO si el número evaluado coincide con alguno de los números del rango "Mis_numeros" lo hace que se aplique el formato.

En la columna L contamos la cantidad de aciertos logrados en cada sorteo con la fórmula

=SUMAPRODUCTO(--(E3:J3=Mis_numeros))

Usamos el doble guión para que Excel evalue los valores VERDADERO como 1 y los FALSO como 0.
La regla del formato condicional en la columna L es

Máximo, Mínimo y K.esimo condicionales en Excel

Excel no cuenta con funciones para calcular máximos o mínimos sujetos a condiciones. Por ejemplo, en esta lista de valores por departamentos

Para encontrar el valor máximo y el mínimo del Depto. 4 tenemos que usar funciones matriciales.
En la celda F3 ponemos la fórmula

=MAX((B3:B18=F2)*C3:C18) 

en forma matricial (se introduce apretando simultáneamente Ctrl-Mayúsculas-Enter), y en la celda F4 la fórmula matricial

=MIN(SI(B3:B18=F2,C3:C18))

La nueva función AGREGAR  (disponible a partir de la versión 2010 de Excel) nos permite encontrar máximos y mínimos, como también valores "k.ésimos" (segundo mayor, tercer menor, etc.) sin necesidad de usar fórmulas matriciales.



Para encontrar el valor máximo del Depto. 4 usamos esta fórmula
=AGREGAR(14,6,C3:C18/(B3:B18=$F$2),1)
y para el mínimo
=AGREGAR(15,6,C3:C18/(B3:B18=$F$2),1)
Veamos cómo funciona esta fórmula:

Como explicamos en la nota mecionada, AGREGAR cuenta con 19 operaciones siendo 14 K.ESIMO.MAYOR y la operación 15, K.ESIMO.MENOR.
El segundo argumento (Opciones = 6) indica que los errores serán ignorados.
La expresión C3:C18/(B3:B18=$F$2) en la fórmula crea una matriz que contiene errores DIV/0! como podemos ver en esta columna auxiliar

cuando la fila evaluada no corresponde al Depto. 4,que serán ignorados, dado que hemos establecido el argumento Opciones con el valor 6.
El argumento "k" funciona de la misma manera que en la función K.ESIMO.MAYOR. Al establecer 1, el resultado será el mayor de los valores.

Una alternativa a los elementos calculados de las tablas dinámicas

Cuando resumimos datos con tablas dinámicas podemos echar mano, entre otras, a dos herramientas valiosas: los campos y los elementos calculados.
Los elementos calculados tienen en ciertas situaciones un comportamiento un tanto enervante, por decirlo de alguna manera.

Veamos esta situación:

El año de cada venta aparece en el campo Año, por lo que si queremos calcular la diferencia entre ambos años para cada país, podemos hacerlo usando creando un elemento calculado,

Apretamos "Aceptar" y Excel crea un nuevo campo con la diferencia de los elementos del campo Año

Como mis observadores lectores habrán notado el reporte dinámico tiene un filtro que permite ver los resultados por vendedor. Elegimos la vendedora Anne Dodsworth y obtenemos este reporte

Podemos ver que también los países donde la compañera Anne no ha realizado ventas aparecen en el informe.

Una posible solución a este problema es crear un nuevo campo en el base de datos con las diferencias. La solución no es trivial e implica el uso intensivo de fórmulas, lo que puede afectar la eficiencia de nuestro modelo.

Podemos usar un pequeño truco para superar este problema. El primer paso es poner por segunda vez el campo "Venta total" en el área de los datos:

El próximo paso es seleccionar alguna de las celdas de la columna 2015 del campo Ventas total que acabamos de agregar y seleccionamos la opción "Diferencia de..." del menú "Mostrar valores como"

y luego elegimos como campo de base la el campo "Año"

El resultado es

Todo lo que nos queda por hacer es ocultar la columna D. Ahora el reporte de las ventas de Anne muestra sólo los países donde se realizaron ventas