Simulació de loteria en Excel

La loteria no és una recerca de sort,

és una caça de perdedors.

Amb una regularitat envejable (i més sovint últimament), la gent m'escriu demanant ajuda en diversos càlculs relacionats amb les loteries. Algú vol implementar el seu algorisme secret per seleccionar els números guanyadors a Excel, algú vol trobar patrons en els números que han caigut en sortejos passats, algú vol atrapar els organitzadors de la loteria en un joc deshonest.

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

Tasca 1. Probabilitat de guanyar

Prenem com a exemple la clàssica loteria Stoloto 6 de 45. Segons les regles, només aquells que han endevinat els 10 números de 6 reben un súper premi (45 milions de rubles o més si el saldo del fons del premi s'ha acumulat en sortejos anteriors). Si n'has endevinat 5, rebràs 150 mil rubles, si 4 - 1500 rubles. , si hi ha 3 números de 6, aleshores 150 rubles, si 2 números, tornaràs 50 rubles gastats en el bitllet. Endevineu-ne només una o cap: obteniu només endorfines del procés del joc.

La probabilitat matemàtica de guanyar es pot calcular fàcilment mitjançant la funció estàndard NUMBERCOMB (COMBINAR), que està disponible a Microsoft Excel per a aquest cas. Aquesta funció calcula el nombre de combinacions de N nombres de M. Per tant, per a la nostra loteria "6 de 45" seria:

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

… que és igual a 8, el nombre total de totes les combinacions possibles en aquesta loteria.

Si voleu calcular la probabilitat d'una victòria parcial (2-5 números de 6), primer haureu de calcular el nombre d'aquestes opcions, que és igual al producte del nombre de combinacions de nombres endevinats de 6 pel nombre de nombres no endevinats dels restants (45-6) = 39 nombres. A continuació, dividim el nombre total de totes les combinacions possibles (8) pel nombre de guanys rebuts per a cada opció, i obtenim les probabilitats de guanyar per a cada cas:

Per cert, la probabilitat, per exemple, de morir en un accident d'avió al nostre país s'estima en aproximadament 1 entre un milió. I la probabilitat de guanyar en un casino a la ruleta, apostant-ho tot en un número és de l'1 al 37.

Si tot l'anterior no us ha aturat i encara esteu preparat per jugar més, continua.

Tasca 2. Freqüència d'aparició de cada nombre

Per començar, determinem amb quina freqüència cauen certs nombres. En una loteria ideal, donat un interval de temps prou gran per a l'anàlisi, totes les boles haurien de tenir la mateixa probabilitat d'estar a la mostra guanyadora. En realitat, les característiques de disseny del tambor de la loteria i la forma del pes de les boles poden distorsionar aquesta imatge, i per a algunes boles la probabilitat de caure pot ser més alta/menor que per a altres. Comprovem aquesta hipòtesi a la pràctica.

Prenguem, per exemple, les dades de tots els sortejos de loteria 2020 de 21 que van tenir lloc el 6-45 del lloc web del seu organitzador Stoloto, dissenyat en forma d'una taula tan "intel·ligent", convenient per a l'anàlisi, amb el nom pestanyaArxiu Circulació. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выблия занлдорка

Per calcular la freqüència d'aparició de cada nombre, utilitzeu la funció COUNTIF (COUNTIF) i afegir-hi una funció TEXT (TEXT)per afegir zeros i asteriscs abans i després als números d'un sol dígit, de manera que COUNTIF cerqui l'aparició d'un nombre en qualsevol lloc de la combinació de la columna B. A més, per a una major claredat, construirem un gràfic per resultats i ordenarem les freqüències. en ordre descendent:

De mitjana, qualsevol bola hauria de caure 1459 empats * 6 boles / 45 números = 194,53 vegades (això és exactament el que s'anomena a les estadístiques математическим ожиданием), норошо видно, что некоторые числа (27, 32, 11…) ыпадаа затно чаще (+18%), i некоорые (10, 21, 6 ...) натотзззззззз ^ нззззз. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша. л л ставить н те шары, что ыпадают чаще, л наобот — - деле ставку на редко ыпагты доistre д techч д.

Tasca 3. Quins números fa temps que no es dibuixen?

Una altra estratègia es basa en la idea que amb un nombre prou gran de sortejos, tard o d'hora, cada número de tots els disponibles de l'1 al 45 hauria de caure. Per tant, si fa molt de temps que alguns números no apareixen entre els guanyadors ("boles fredes"), és lògic intentar apostar per ells en el futur. 

Можно легко найти давно не ыпадавшие номера, если отсооиítв наш ахив тиж за 2020-21 MÉS EXPOSADA (PARTIDA). Cercarà de dalt a baix (és a dir, de sèries noves a antigues) per cercar cada número i donarà el número de sèrie de la tirada (des del final de l'any fins al principi) on es va deixar aquest número per última vegada:

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

Una altra estratègia de joc es basa en eliminar el factor psicològic a l'hora d'endevinar números. Quan un jugador tria números fent la seva aposta, inconscientment ho fa no del tot racionalment. Segons les estadístiques, per exemple, els números de l'1 al 31 es trien un 70% més sovint que la resta (dates preferides), el 13 s'escull amb menys freqüència (maleïda dotzena), els números que contenen el set "afortunat" s'escullen més sovint, etc. Però estem jugant contra una màquina (tambor de loteria) per a la qual tots els números són iguals, així que té sentit triar-los amb la mateixa imparcialitat matemàtica per igualar les nostres possibilitats. Per fer-ho, hem de crear un generador de números aleatoris i, el més important, que no es repeteixen a Excel:

Fer això:

1. Creem una taula "intel·ligent" anomenada tableGenerator, on la primera columna seran els nostres números de l'1 al 45.
2. A la segona columna, introduïu el pes de cada número (el necessitarem una mica més endavant). Si tots els nombres són igual de valuosos per a nosaltres i volem triar-los amb la mateixa probabilitat, llavors el pes es pot establir igual a 1 a tot arreu.
3. A la tercera columna fem servir la funció SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предоз число от XNUMX до XNUMX, добавив к нему вес из предисло от. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) es generarà un nou conjunt de 45 nombres aleatoris, tenint en compte el pes de cadascun d'ells.
4. Afegim una quarta columna, on s'utilitza la funció RANK (CLASIFICACIÓ) вычислим ранг (позицию в топе) для каждого из чисел.

Ara queda fer una selecció dels sis primers nombres pel rang 6 mitjançant la funció MÉS EXPOSADA (PARTIDA):

При нажатии на клавишу F9 es tornaran a calcular les fórmules del full d'Excel i cada vegada obtindrem un nou conjunt de 6 nombres en cel·les verdes. A més, els nombres per als quals es va establir un pes més gran a la columna B rebran un rang proporcionalment més alt i, per tant, apareixeran amb més freqüència als resultats de la nostra mostra aleatòria. Si el pes de tots els nombres s'estableix en el mateix, es seleccionaran tots amb la mateixa probabilitat. D'aquesta manera obtenim un generador de números aleatoris just i imparcial de 6 de 45, però amb la possibilitat de fer ajustos a l'aleatorietat de la distribució si cal.

Si decidim jugar a cada sorteig no amb un, sinó, per exemple, amb dos bitllets alhora, en cadascun dels quals escollirem números que no es repeteixen, només podem afegir línies addicionals des de la part inferior fins al rang verd, afegint 6, 12, 18, etc. al rang. d. respectivament:

Tasca 5. Simulador de loteria en Excel

Com a apoteosi de tot aquest tema, creem un simulador de loteria complet a Excel, on podeu provar qualsevol estratègia i comparar els resultats (en teoria d'optimització, una cosa semblant també s'anomena mètode Monte Carlo, però serà més senzill). Per a nosaltres).

Perquè tot sigui el més proper possible a la realitat, imagineu-vos per un moment que és l'1 de gener de 2022 i tenim per davant els sortejos d'enguany, en els quals tenim previst jugar. Vaig introduir els números reals caigut a la taula tablTiraži2022, separant els nombres extrets addicionals entre si en columnes separades per a la comoditat dels càlculs posteriors:

En un full a part Joc creeu un espai en blanc per modelar en forma de taula "intel·ligent" amb el nom tabIgra la forma següent:

Aquí:

• A les cel·les grogues de dalt, establirem per a la macro el nombre de sortejos del 2022 en què volem participar (1-82) i el nombre d'entrades que juguem a cada sorteig.
• Les dades de les primeres 11 columnes (AJ) seran copiades per la macro del full de sorteig de 2022.
• Dades de les sis columnes següents (KP) que la macro agafarà del full Generador, on hem implementat un generador de nombres aleatoris (vegeu el problema 4 anterior).
• A la columna Q, comptem el nombre de coincidències entre els números deixats caure i els generats mitjançant la funció SUMPRODUCT (SUMAPRODUCTE).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей зансовый результат) — В столбце
• A l'última columna S, considerem el resultat global de tot el joc com un total acumulat per tal de veure la dinàmica del procés.

I per reviure tota aquesta estructura, necessitem una petita macro. A la pestanya revelador (Desenvolupador) triar un equip Visual Basic o utilitzeu la drecera del teclat Alt+F11. A continuació, afegiu un nou mòdul buit mitjançant el menú Inserir – Mòdul i introduïu-hi el codi següent:

Sub Loteria() Dim iGames com a enter, iTickets com a enter, i Com llarg, t com a enter, b com a enter wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576"). Elimineu 'очищаем старые данные per a t = 1 a iGames per b = 1 a itickets' копиреем ыигигшш нsarCHа.ReSize. (2022, 1).Copy Destination:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенерированные номера с листа Генератор wsNumbers.Range("G1:L10").Copy wsGame.Cells(i, 1) .PasteSpecial Paste:=xlPasteValues ​​i = i + 4 Següent b Següent t End Sub  

Queda per introduir els paràmetres inicials desitjats a les cel·les grogues i executar la macro Desenvolupador - Macros (Desenvolupador - Macros) o drecera de teclat Alt+F8.

Per a més claredat, també podeu crear un diagrama per a l'última columna amb un total acumulat, que reflecteixi el canvi en el saldo monetari durant el joc:

Comparació de diferents estratègies

Ara, amb el simulador creat, podeu provar qualsevol estratègia de joc amb sorteigs reals el 2022 i veure els resultats que comportaria. Si jugues 1 bitllet a cada sorteig, la imatge general de la "pruna" sembla una cosa així:

Aquí:

• Generador és un joc on en cada sorteig escollim números aleatoris creats pel nostre generador (amb el mateix pes).
• Favorits és un joc on en cada sorteig utilitzem els mateixos números, els que han caigut amb més freqüència en els sorteigs dels últims dos anys (27, 32, 11, 14, 34, 40).
• Outsiders – el mateix, però fem servir els números desplegables més rars (12, 18, 26, 10, 21, 6).
• Fred – en tots els sortejos fem servir números que fa temps que no han caigut (35, 5, 39, 11, 6, 29).

Com podeu veure, no hi ha una gran diferència, però el generador de números aleatoris es comporta una mica millor que les altres "estratègies".

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

Jugant a cada sorteig amb un bitllet amb números generats aleatòriament (amb el mateix pes):

Jugant 10 entrades a cada sorteig amb números generats aleatòriament (amb el mateix pes):

Jugant 100 entrades a cada sorteig amb números aleatoris (amb el mateix pes):

Els comentaris, com diuen, són superfluos: un drenatge de dipòsits és inevitable en tots els casos 🙂