Simulación de lotería en Excel

A lotería non é unha caza de sorte,

é unha caza de perdedores.

Cunha regularidade envexable (e máis a miúdo ultimamente), a xente escríbeme pedíndome axuda en varios cálculos relacionados coas loterías. Alguén quere implementar o seu algoritmo secreto para seleccionar números gañadores en Excel, alguén quere atopar patróns nos números que caeron dos sorteos anteriores, alguén quere atrapar aos organizadores da lotería nun xogo deshonesto.

В этой статье мне хотелось бы ответить на часть этих вопросов. , В Excel для решения таких задач достаточ fas инстрементов, многие зхооххчччччччччччххххххххххххххххххххх ens

Tarefa 1. Probabilidade de gañar

Poñamos como exemplo a clásica lotería Stoloto 6 de 45. Segundo as regras, só os que adiviñaron os 10 números de 6 reciben un super premio (45 millóns de rublos ou máis se o saldo do fondo do premio acumulouse de sorteos anteriores). Se adiviñaches 5, recibirás 150 mil rublos, se 4 - 1500 rublos. , se 3 números de 6, entón 150 rublos, se 2 números - devolverás 50 rublos gastados no billete. Adiviña só unha ou ningunha: obtén só endorfinas do proceso do xogo.

A probabilidade matemática de gañar pódese calcular facilmente usando a función estándar NÚMERO COMB (COMBINAR), que está dispoñible en Microsoft Excel para tal caso. Esta función calcula o número de combinacións de N números de M. Polo tanto, para a nosa lotería "6 de 45" sería:

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

… que é igual a 8, o número total de todas as combinacións posibles nesta lotería.

Se queres calcular a probabilidade dunha vitoria parcial (2-5 números de 6), primeiro terás que calcular o número de tales opcións, que é igual ao produto do número de combinacións de números adiviñados. 6 polo número de números non adiviñados dos restantes (45-6) = 39 números. Despois dividimos o número total de todas as combinacións posibles (8) polo número de vitorias recibidas para cada opción e obtemos as probabilidades de gañar para cada caso:

Por certo, a probabilidade, por exemplo, de morrer nun accidente de avión no Noso País calcúlase en preto de 1 entre un millón. E a probabilidade de gañar nun casino na ruleta, apostando todo nun número é do 1 ao 37.

Se todo o anterior non te detivo e aínda estás preparado para seguir xogando, continúa.

Tarefa 2. Frecuencia de aparición de cada número

Para comezar, imos determinar con que frecuencia caen certos números. Nunha lotería ideal, dado un intervalo de tempo suficientemente grande para a análise, todas as bólas deberían ter a mesma probabilidade de estar na mostra gañadora. En realidade, as características do deseño do tambor de lotería e a forma do peso das bólas poden distorsionar esta imaxe, e para algunhas bólas a probabilidade de caer pode ser maior/menor que noutras. Comprobamos esta hipótese na práctica.

Tomemos, por exemplo, os datos de todo o 2020 dos 21 sorteos de lotería que tiveron lugar no 6-45 do sitio web do seu organizador Stoloto, deseñados en forma de táboa tan "intelixente", conveniente para a análise, co nome pestana Circulación de arquivos. Розыгрыши проходят два раза в день (no 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выблия занлдорка

Para calcular a frecuencia de aparición de cada número, utiliza a función COUNTIF (CONTAR SE) e engadirlle unha función TEXTO (TEXTO)para engadir ceros e asteriscos antes e despois aos números dun só díxito, de xeito que COUNTIF busque a aparición dun número en calquera lugar da combinación da columna B. Ademais, para maior claridade, elaboraremos un gráfico por resultados e ordenaremos as frecuencias. en orde descendente:

De media, calquera bóla debería caer 1459 empates * 6 bólas / 45 números = 194,53 veces (isto é exactamente o que se chama nas estatísticas) математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11 ...) ыыадали заметно чаще (+18%), аототототототототототототототототототототототототототототототототототот fla о 10%, Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша. либо ставить н те шары, что ыыадают чаще, лио наоорот - делать свку на vai, ыо ччччччччччччччччччччччччччч чы ыыы ы ы ы

Tarefa 3. Que números levan moito tempo sen debuxar?

Outra estratexia baséase na idea de que cun número suficientemente grande de empates, tarde ou cedo, cada número de todos os dispoñibles do 1 ao 45 debería caer. Polo tanto, se hai moito tempo que algúns números non aparecen entre os gañadores ("bolas frías"), entón é lóxico tentar apostar por eles no futuro. 

Можно леactor найти все давно не ыыадавшие номера, если отсортировать наш ахх жжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжжж и MÁIS EXPOÑA (Coincidir). Buscará de arriba a abaixo (é dicir, de tiradas novas a antigas) para buscar cada número e dará o número de serie da carreira (contando desde o final do ano ata o principio) onde se deixou caer este número por última vez:

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

Outra estratexia de xogo baséase en eliminar o factor psicolóxico á hora de adiviñar números. Cando un xogador escolle números facendo a súa aposta, inconscientemente fai isto de forma non totalmente racional. Segundo as estatísticas, por exemplo, os números do 1 ao 31 escóllense un 70% máis a miúdo que o resto (datas favoritas), o 13 elíxese con menos frecuencia (maldita ducia), os números que conteñen o sete "afortunados" son máis a miúdo elixidos, etc. Pero estamos xogando contra unha máquina (tambor de lotería) para a que todos os números son iguais, polo que ten sentido elixilos coa mesma imparcialidade matemática para igualar as nosas posibilidades. Para iso, necesitamos crear un xerador de números aleatorios e, o máis importante, que non se repiten en Excel:

Para facelo:

1. Imos crear unha táboa "intelixente" chamada xerador de táboas, onde a primeira columna serán os nosos números do 1 ao 45.
2. Na segunda columna, introduza o peso de cada número (necesitarémolo un pouco máis tarde). Se todos os números son igualmente valiosos para nós e queremos elixilos coa mesma probabilidade, entón o peso pódese establecer igual a 1 en todas partes.
3. Na terceira columna usamos a función SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предисло от XNUMX до XNUMX, добавив к нему вес из предисло от. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) xerarase un novo conxunto de 45 números aleatorios, tendo en conta o peso de cada un deles.
4. Imos engadir unha cuarta columna, onde usar a función RANK (CLASIFICACIÓN) вычислим ранг (позицию в топе) для каждого из чисел.

Agora queda facer unha selección dos seis primeiros números por rango 6 usando a función MÁIS EXPOÑA (Coincidir):

При нажатии на клавишу F9 recalcularanse as fórmulas da folla de Excel e cada vez obteremos un novo conxunto de 6 números en celas verdes. Ademais, os números para os que se estableceu un peso maior na columna B recibirán un rango proporcionalmente máis alto e, polo tanto, aparecerán con máis frecuencia nos resultados da nosa mostra aleatoria. Se o peso para todos os números se establece no mesmo, entón todos eles serán seleccionados coa mesma probabilidade. Deste xeito, obtemos un xerador de números aleatorios xusto e imparcial de 6 de 45, pero coa capacidade de facer axustes á aleatoriedade da distribución se é necesario.

Se decidimos xogar en cada sorteo non con un, senón, por exemplo, con dous boletos á vez, en cada un dos cales escolleremos números que non se repiten, entón podemos simplemente engadir liñas adicionais desde a parte inferior ata o rango verde, sumando 6, 12, 18, etc. ao rango. d. respectivamente:

Tarefa 5. Simulador de lotería en Excel

Como apoteose de todo este tema, creemos un simulador de lotería completo en Excel, onde podes probar calquera estratexia e comparar os resultados (na teoría da optimización, algo semellante tamén se chama método Monte Carlo, pero será máis sinxelo). para nós).

Para que todo sexa o máis próximo posible á realidade, imaxine por un momento que é o 1 de xaneiro de 2022 e temos por diante os sorteos deste ano, nos que pensamos xogar. Introducín os números reais perdidos na táboa tablTiraži2022, separando os números extraídos adicionalmente entre si en columnas separadas para facilitar os cálculos posteriores:

Nunha folla aparte Xogo crea un espazo en branco para modelar en forma de táboa "intelixente" co nome tabIgra a seguinte forma:

Aquí:

• Nas celas amarelas anteriores, fixaremos para a macro o número de sorteos en 2022 nos que queremos participar (1-82) e o número de entradas que xogamos en cada sorteo.
• Os datos das primeiras 11 columnas (AJ) serán copiados pola macro da folla de sorteo de 2022.
• Os datos das seguintes seis columnas (KP) tomará a macro da folla Xerador, onde implementamos un xerador de números aleatorios (ver problema 4 anterior).
• Na columna Q, contamos o número de coincidencias entre os números eliminados e os xerados mediante a función SUMPRODUCT (SUMAPRODUTO).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей зансовый результат) — В столбце
• Na última columna S, consideramos o resultado global de todo o xogo como un total acumulado para ver a dinámica do proceso.

E para revivir toda esta estrutura, necesitamos unha pequena macro. Na pestana revelador (Desenvolvedor) escolle un equipo Visual Basic ou use o atallo do teclado alt+F11. A continuación, engade un novo módulo baleiro a través do menú Inserir – Módulo e introduza alí o seguinte código:

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 'очищаем старые данные por t = 1 a Igames por b = 1 a itickets' копируеем ыигавшие ноера с с лens. (2022, 1).Copy Destination:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенерированные номера с листа Генератор wsNumbers.Range("G1:L10").Copy wsGame.Cells(i, 1) .PasteSpecial Paste:=xlPasteValues ​​i = i + 4 Seguinte b Seguinte t End Sub  

Queda por introducir os parámetros iniciais desexados nas celas amarelas e executar a macro Desenvolvedor - Macros (Desenvolvedor - Macros) ou atallo de teclado alt+F8.

Para máis claridade, tamén podes construír un diagrama para a última columna cun total acumulado, que reflicta o cambio no saldo monetario durante o xogo:

Comparación de diferentes estratexias

Agora, usando o simulador creado, podes probar calquera estratexia de xogo en sorteos reais en 2022 e ver os resultados que traería. Se xogas 1 boleto en cada sorteo, a imaxe xeral da "ameixeira" é así:

Aquí:

• Xerador é un xogo onde en cada sorteo escollemos números aleatorios creados polo noso xerador (co mesmo peso).
• Favoritos é un xogo no que en cada sorteo usamos os mesmos números, os que máis caeron nos últimos dous anos (27, 32, 11, 14, 34, 40).
• Outsiders – o mesmo, pero usamos os números despregables máis raros (12, 18, 26, 10, 21, 6).
• Frío – en todos os sorteos utilizamos números que non caeron desde hai moito tempo (35, 5, 39, 11, 6, 29).

Como podes ver, non hai gran diferenza, pero o xerador de números aleatorios compórtase un pouco mellor que as outras "estratexias".

Можно также попробовать играть большим количеством билетов в каждом тираже, чтераже, чтерь коб личество вариантов (иногда для этого несколько игроков объединяются в группу).

Xogando en cada sorteo cun boleto con números xerados aleatoriamente (co mesmo peso):

Xogando 10 boletos en cada sorteo con números xerados aleatoriamente (co mesmo peso):

Xogando 100 boletos en cada sorteo con números aleatorios (co mesmo peso):

Os comentarios, como se di, son superfluos: un drenaxe de depósito é inevitable en todos os casos 🙂