Simulación de lotería en Excel

La lotería no es una cacería de suerte,

es una cacería de perdedores.

Con una regularidad envidiable (y más a menudo últimamente), me escriben personas pidiéndome ayuda en varios cálculos relacionados con las loterías. Alguien quiere implementar su algoritmo secreto para seleccionar los números ganadores en Excel, alguien quiere encontrar patrones en los números que han caído en sorteos anteriores, alguien quiere atrapar a los organizadores de la lotería en un juego deshonesto.

В этой статье мне хотелось бы ответить на часть этих вопросов. Бл, в Excel дò рения таких задач достаточно инст comt.

Tarea 1. Probabilidad de ganar

Tomemos como ejemplo la clásica lotería Stoloto 6 de 45. De acuerdo con las reglas, solo aquellos que adivinaron los 10 números de 6 reciben un súper premio (45 millones de rublos o más si el saldo del fondo de premios se ha acumulado de sorteos anteriores). Si acertó 5, recibirá 150 mil rublos, si 4 – 1500 rublos. , si 3 números de 6, entonces 150 rublos, si 2 números, devolverá 50 rublos gastados en el boleto. Adivina solo uno o ninguno: obtén solo endorfinas del proceso del juego.

La probabilidad matemática de ganar se puede calcular fácilmente usando la función estándar NÚMERO PEINE (COMBINAR), que está disponible en Microsoft Excel para tal caso. Esta función calcula el número de combinaciones de N números de M. Entonces, para nuestra lotería "6 de 45" sería:

=ЧИСЛКОМБ(45;6)

… que es igual a 8, el número total de todas las combinaciones posibles en esta lotería.

Si desea calcular la probabilidad de una ganancia parcial (2-5 números de 6), primero deberá calcular el número de tales opciones, que es igual al producto del número de combinaciones de números adivinados de 6 por el número de números no adivinados de los restantes (45-6) = 39 números. Luego, dividimos el número total de todas las combinaciones posibles (8) por el número de ganancias recibidas para cada opción, y obtenemos las probabilidades de ganar para cada caso:

Por cierto, la probabilidad, por ejemplo, de morir en un accidente aéreo en Nuestro País se estima en alrededor de 1 en un millón. Y la probabilidad de ganar en un casino a la ruleta, apostando todo a un número es de 1 a 37.

Si todo lo anterior no lo detuvo y todavía está listo para seguir jugando, continúe.

Tarea 2. Frecuencia de ocurrencia de cada número

Para empezar, determinemos con qué frecuencia salen ciertos números. En una lotería ideal, dado un intervalo de tiempo suficientemente grande para el análisis, todas las bolas deberían tener la misma probabilidad de estar en la muestra ganadora. En realidad, las características de diseño del tambor de lotería y la forma del peso de las bolas pueden distorsionar esta imagen y, para algunas bolas, la probabilidad de que se caigan puede ser mayor o menor que para otras. Probemos esta hipótesis en la práctica.

Tomemos, por ejemplo, datos sobre todos los 2020 sorteos de lotería de 21 que tuvieron lugar en 6-45 del sitio web de su organizador Stoloto, diseñado en forma de una tabla tan "inteligente", conveniente para el análisis, con el nombre tabArchive Circulación. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для ана:

Para calcular la frecuencia de aparición de cada número, utilice la función COUNTIF (CONTAR.SI) y agregarle una función TEXTO (TEXTO)para agregar ceros a la izquierda y asteriscos antes y después de números de un solo dígito, de modo que COUNTIF busque la aparición de un número en cualquier lugar de la combinación en la columna B. Además, para mayor claridad, crearemos un gráfico por resultados y ordenaremos las frecuencias en orden descendente:

En promedio, cualquier bola debería caer 1459 sorteos * 6 bolas / 45 números = 194,53 veces (esto es exactamente lo que se llama en las estadísticas matemáticas matemáticas), но хорошо видно, что некоeccion числа (27, 32, 11 ...) выыадали заметн momento (+18%), ан м м. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. ли с regalo

Tarea 3. ¿Qué números no se han sorteado durante mucho tiempo?

Otra estrategia se basa en la idea de que con un número suficientemente grande de sorteos, tarde o temprano, todos los números disponibles del 1 al 45 deberían caer. Entonces, si algunos números no han aparecido entre los ganadores durante mucho tiempo ("bolas frías"), entonces es lógico intentar apostar por ellos en el futuro. 

Мжно лко найeccion ific давно не выпада adaptón MÁS EXPUESTO (JUEGO). Buscará de arriba a abajo (es decir, de las ejecuciones nuevas a las antiguas) para buscar cada número y dar el número de serie de la ejecución (contando desde el final del año hasta el principio) donde se arrojó este número por última vez:

Задача 4. Генератор случайных чисел

Otra estrategia de juego se basa en eliminar el factor psicológico a la hora de adivinar números. Cuando un jugador elige números al hacer su apuesta, inconscientemente lo hace no del todo racionalmente. Según las estadísticas, por ejemplo, los números del 1 al 31 se eligen un 70% más que el resto (fechas favoritas), el 13 se elige con menos frecuencia (maldita docena), los números que contienen el siete “afortunado” se eligen con más frecuencia, etc. Pero estamos jugando contra una máquina (tambor de lotería) en la que todos los números son iguales, por lo que tiene sentido elegirlos con la misma imparcialidad matemática para igualar nuestras posibilidades. Para hacer esto, necesitamos crear un generador de números aleatorios y, lo más importante, que no se repiten en Excel:

Para hacer esto:

1. Vamos a crear una tabla "inteligente" llamada generador de mesa, donde la primera columna serán nuestros números del 1 al 45.
2. En la segunda columna, ingrese el peso de cada número (lo necesitaremos un poco más adelante). Si todos los números son igualmente valiosos para nosotros y queremos elegirlos con la misma probabilidad, entonces el peso se puede establecer igual a 1 en todas partes.
3. En la tercera columna usamos la función SLCHIS (ALEATORIO), добавив к нему вес из предыдущего столбц. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) se generará un nuevo conjunto de 45 números aleatorios, teniendo en cuenta el peso de cada uno de ellos.
4. Agreguemos una cuarta columna, donde usando la función RANK (RANGO) вычислим ранг (позицию в топе) для каждого из чисел.

Ahora queda hacer una selección de los primeros seis números por rango 6 usando la función MÁS EXPUESTO (JUEGO):

При нажатии на клавишу F9 las fórmulas en la hoja de Excel se volverán a calcular y cada vez obtendremos un nuevo conjunto de 6 números en celdas verdes. Además, los números para los que se fijó una mayor ponderación en la columna B recibirán una clasificación proporcionalmente más alta y, por lo tanto, aparecerán con mayor frecuencia en los resultados de nuestra muestra aleatoria. Si el peso de todos los números se establece en el mismo, todos ellos serán seleccionados con la misma probabilidad. De esta forma obtenemos un generador de números aleatorios justo e imparcial de 6 de 45, pero con la capacidad de hacer ajustes a la aleatoriedad de la distribución si es necesario.

Si decidimos jugar en cada sorteo no con uno, sino, por ejemplo, con dos boletos a la vez, en cada uno de los cuales elegiremos números que no se repiten, entonces simplemente podemos agregar líneas adicionales desde la parte inferior hasta el rango verde, agregando 6, 12, 18, etc. al rango. d. respectivamente:

Tarea 5. Simulador de Lotería en Excel

Como apoteosis de todo este tema, creemos un simulador de lotería completo en Excel, donde puede probar cualquier estrategia y comparar los resultados (en la teoría de la optimización, algo similar también se llama el método de Monte Carlo, pero será más simple para nosotros).

Para que todo se parezca lo más posible a la realidad, imagina por un momento que es el 1 de enero de 2022 y tenemos por delante los sorteos de este año, en los que tenemos pensado jugar. Ingresé los números reales caídos en la tabla tablTiraži2022, separando los números extraídos adicionalmente entre sí en columnas separadas para facilitar los cálculos posteriores:

En una hoja aparte juega cree un espacio en blanco para modelar en forma de una tabla "inteligente" con el nombre pestañaIgra el siguiente formulario:

Aquí:

• En las celdas amarillas de arriba, estableceremos para la macro la cantidad de sorteos en 2022 en los que queremos participar (1-82) y la cantidad de boletos que jugamos en cada sorteo.
• Los datos de las primeras 11 columnas (AJ) serán copiados por la macro del sorteo de 2022.
• Datos para las próximas seis columnas (KP) que la macro tomará de la hoja Generador, donde hemos implementado un generador de números aleatorios (ver problema 4 arriba).
• En la columna Q, contamos el número de coincidencias entre los números caídos y los generados usando la función SUMPRODUCT (SUMAPRODUCTO).
• В столбце r вычислеuf финанансо plata.
• En la última columna S, consideramos el resultado general de todo el juego como un total acumulativo para ver la dinámica del proceso.

Y para revivir toda esta estructura, necesitamos una pequeña macro. en la pestaña revelador (Desarrollador) elige un equipo Visual Basic o usa el atajo de teclado otro+F11. Luego agregue un nuevo módulo vacío a través del menú Insertar – Módulo e ingrese el siguiente código allí:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576"). Eliminar 'чищаеíf с regalo (1, 1. .PasteSpecial Paste:=xlPasteValues ​​i = i + 2022 Siguiente b Siguiente t End Sub  

Queda por ingresar los parámetros iniciales deseados en las celdas amarillas y ejecutar la macro a través de Desarrollador – Macros (Desarrollador — Macros) o atajo de teclado otro+F8.

Para mayor claridad, también puede crear un diagrama para la última columna con un total acumulativo, que refleje el cambio en el saldo de dinero durante el juego:

Comparación de diferentes estrategias

Ahora, utilizando el simulador creado, puede probar cualquier estrategia de juego en sorteos reales en 2022 y ver los resultados que traería. Si juega 1 boleto en cada sorteo, entonces la imagen general de la "ciruela" se ve así:

Aquí:

• Generador es un juego donde en cada sorteo elegimos números aleatorios creados por nuestro generador (con el mismo peso).
• Favoritos es un juego en el que en cada sorteo usamos los mismos números, los que más veces han caído en los sorteos de los últimos dos años (27, 32, 11, 14, 34, 40).
• Los forasteros – lo mismo, pero usamos los números desplegables más raros (12, 18, 26, 10, 21, 6).
• Frío – en todos los sorteos usamos números que no han caído en mucho tiempo (35, 5, 39, 11, 6, 29).

Como puedes ver, no hay gran diferencia, pero el generador de números aleatorios se comporta un poco mejor que las otras “estrategias”.

Es posible que desee instalar varios billetes de banco en una tirada, ya que puede utilizar varias variantes de este tipo (inglés). а для этого несколько игроков объединяются в группу).

Jugando en cada sorteo con un boleto con números generados aleatoriamente (con el mismo peso):

Jugando 10 boletos en cada sorteo con números generados aleatoriamente (con el mismo peso):

Jugando 100 boletos en cada sorteo con números aleatorios (con el mismo peso):

Los comentarios, como dicen, son superfluos: un drenaje de depósito es inevitable en todos los casos 🙂