Lotteri simulering i Excel

Lotteriet er ikke en jakt på flaks,

det er en jakt på tapere.

Med misunnelsesverdig regelmessighet (og oftere i det siste) skriver folk til meg og ber om hjelp til ulike beregninger knyttet til lotterier. Noen vil implementere sin hemmelige algoritme for å velge vinnertall i Excel, noen vil finne mønstre i tallene som har falt ut av tidligere trekninger, noen vil fange arrangørene av lotteriet i et uærlig spill.

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

Oppgave 1. Vinnersannsynlighet

La oss ta det klassiske Stoloto 6 av 45-lotteriet som et eksempel. I følge reglene er det bare de som gjettet alle 10 av 6 tall som mottar en superpremie (45 millioner rubler eller mer hvis saldoen i premiefondet har samlet seg fra tidligere trekninger). Hvis du gjettet 5, vil du motta 150 tusen rubler, hvis 4 - 1500 rubler. , hvis 3 tall av 6, deretter 150 rubler, hvis 2 tall - du vil returnere 50 rubler brukt på billetten. Gjett bare én eller ingen – få bare endorfiner fra spillprosessen.

Den matematiske sannsynligheten for å vinne kan enkelt beregnes ved hjelp av standardfunksjonen NUMBERCOMB (KOMBINERE), som er tilgjengelig i Microsoft Excel for et slikt tilfelle. Denne funksjonen beregner antall kombinasjoner av N tall ut av M. Så for vårt "6 av 45" lotteri vil det være:

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

… som er lik 8, det totale antallet av alle mulige kombinasjoner i dette lotteriet.

Hvis du vil beregne sannsynligheten for en delvis gevinst (2-5 tall av 6), må du først beregne antall slike alternativer, som er lik produktet av antall kombinasjoner av gjettede tall ut av 6 med antall ubestemte tall av de resterende (45-6) = 39 tall. Deretter deler vi det totale antallet av alle mulige kombinasjoner (8) med det mottatte antallet gevinster for hvert alternativ – og vi får vinnersannsynlighetene for hvert tilfelle:

Sannsynligheten for for eksempel å dø i en flyulykke i Vårt Land er forresten anslått til ca 1 av en million. Og sannsynligheten for å vinne i et kasino på rulett, å satse alt på ett tall er 1 til 37.

Hvis alt det ovennevnte ikke stoppet deg og du fortsatt er klar til å spille videre, fortsett.

Oppgave 2. Hyppighet av forekomst av hvert tall

Til å begynne med, la oss bestemme med hvilken frekvens visse tall faller ut. I et ideelt lotteri, gitt et tilstrekkelig stort tidsintervall for analyse, bør alle kuler ha samme sannsynlighet for å være i vinnerutvalget. I virkeligheten kan designtrekkene til lotterietrommelen og vektformen på ballene forvrenge dette bildet, og for noen baller kan sannsynligheten for å falle ut være høyere/lavere enn for andre. La oss teste denne hypotesen i praksis.

La oss for eksempel ta data om alle 2020 av 21 lotteritrekninger som fant sted i 6-45 fra nettsiden til deres arrangør Stoloto, designet i form av et slikt "smart" bord, praktisk for analyse, med navnet tabArkiv Opplag. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка for анализа:

For å beregne hyppigheten av forekomst av hvert tall, bruk funksjonen ANTALL.HVIS (COUNTIF) og legg til en funksjon til den TEKST (TEKST)å legge til innledende nuller og stjerner før og etter til ensifrede tall, slik at COUNTIF ser etter forekomsten av et tall hvor som helst i kombinasjonen i kolonne B. For større klarhet vil vi også bygge et diagram etter resultater og sortere frekvensene i synkende rekkefølge:

I gjennomsnitt skal enhver ball falle 1459 trekk * 6 baller / 45 tall = 194,53 ganger (det er akkurat det som kalles i statistikken математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые (10, 21, 6…) наоборот заметно реже (-15%), чем основная масса. Соответственно, можно попробовать использовать эту иinfomацию для стратегии выигрыша, т.е. либо ставить на теары, что выадают чаще, ио наобороnd - деее ежжжжжжжжжpyng

Oppgave 3. Hvilke tall har ikke blitt trukket på lenge?

En annen strategi er basert på ideen om at med et tilstrekkelig stort antall trekninger, før eller siden skulle hvert tall fra alle tilgjengelige fra 1 til 45 falle ut. Så hvis noen tall ikke har dukket opp blant vinnerne på lenge ("kalde baller"), så er det logisk å prøve å satse på dem i fremtiden. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей for 2020-21 MER UTSATT (KAMP). Den vil søke fra topp til bunn (dvs. fra nye til gamle kjøringer) for å søke etter hvert nummer og gi ut serienummeret til kjøringen (teller fra slutten av året til begynnelsen) der dette nummeret sist ble droppet:

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

En annen spillstrategi er basert på å eliminere den psykologiske faktoren når du gjetter tall. Når en spiller velger tall ved å gjøre sin innsats, gjør han dette ubevisst ikke helt rasjonelt. I følge statistikk velges for eksempel tall fra 1 til 31 70% oftere enn resten (favorittdatoer), 13 velges sjeldnere (dusin), tall som inneholder de "heldige" syv blir oftere valgt, etc. Men vi spiller mot en maskin (lotterietromme) der alle tallene er like, så det er fornuftig å velge dem med samme matematiske upartiskhet for å utjevne sjansene våre. For å gjøre dette må vi lage en generator av tilfeldige og – viktigst av alt – ikke-repeterende tall i Excel:

Å gjøre dette:

1. La oss lage en "smart" tabell med navnet tabellGenerator, der den første kolonnen vil være tallene våre fra 1 til 45.
2. I den andre kolonnen skriver du inn vekten for hvert tall (vi trenger det litt senere). Hvis alle tall er like verdifulle for oss og vi ønsker å velge dem med lik sannsynlighet, så kan vekten settes lik 1 overalt.
3. I den tredje kolonnen bruker vi funksjonen SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 til 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) et nytt sett med 45 tilfeldige tall vil bli generert, tatt i betraktning vekten for hvert av dem.
4. La oss legge til en fjerde kolonne, hvor du bruker funksjonen RANG (RANG) вычислим ранг (позицию в топе) for каждого из чисел.

Nå gjenstår det å gjøre et utvalg av de seks første tallene etter rangering 6 ved å bruke funksjonen MER UTSATT (KAMP):

При нажатии на клавишу F9 formlene på Excel-arket vil bli beregnet på nytt og hver gang vil vi få et nytt sett med 6 tall i grønne celler. Dessuten vil tallene som det ble satt en større vekt for i kolonne B få en proporsjonalt høyere rangering og dermed vises oftere i resultatene av vårt tilfeldige utvalg. Hvis vekten for alle tall er satt til det samme, vil alle av dem bli valgt med samme sannsynlighet. På denne måten får vi en rettferdig og upartisk tilfeldig tallgenerator på 6 av 45, men med mulighet til å gjøre justeringer av tilfeldigheten i fordelingen om nødvendig.

Hvis vi bestemmer oss for å spille i hver trekning, ikke med én, men for eksempel med to billetter på en gang, i hver av dem vil vi velge ikke-gjentakende tall, så kan vi ganske enkelt legge til flere linjer fra bunnen til det grønne området, legge til 6, 12, 18 osv. til rangeringen. d. henholdsvis:

Oppgave 5. Lottersimulator i Excel

Som en apoteose av hele dette emnet, la oss lage en fullverdig lotterisimulator i Excel, hvor du kan prøve ut hvilke som helst strategier og sammenligne resultatene (i optimaliseringsteori kalles noe lignende også Monte Carlo-metoden, men det vil være enklere for oss).

For å gjøre alt så nærme virkeligheten som mulig, tenk for et øyeblikk at det er 1. januar 2022 og vi har årets trekninger foran oss, der vi planlegger å spille. Jeg skrev inn de virkelige tapte tallene i tabellen tablTiraži2022, separere de ekstra tegnede tallene fra hverandre i separate kolonner for å lette etterfølgende beregninger:

På et eget ark Spill lag et tomt for modellering i form av et "smart" bord med navnet tabIgra følgende skjema:

Her:

• I de gule cellene over vil vi sette for makroen antall trekninger i 2022 som vi ønsker å delta i (1-82) og antall lodd vi spiller i hver trekning.
• Dataene for de første 11 kolonnene (AJ) vil bli kopiert av makroen fra tegnearket for 2022.
• Data for de neste seks kolonnene (KP) makroen vil hente fra arket Generator, hvor vi har implementert en tilfeldig tallgenerator (se oppgave 4 ovenfor).
• I kolonne Q teller vi antall treff mellom de droppede tallene og de som er generert ved hjelp av funksjonen SUMPRODUCT (SUMPRODUKT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, есливилтат, есливиграли, за минус 50 рублей за билет, есливиграли, з XNUMX р.
• I siste kolonne S betrakter vi det samlede resultatet av hele spillet som en kumulativ total for å se dynamikken i prosessen.

Og for å gjenopplive hele denne strukturen, trenger vi en liten makro. På fanen utvikler (Utvikler) velge et lag Visual Basic eller bruk hurtigtaster andre+F11. Legg deretter til en ny tom modul via menyen Sett inn – modul og skriv inn følgende kode der:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets("Иенгра") Sett wsheets("") = Set Worksheets("") wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6:1048576").Slett 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тира2022жиг 1s. (1, 1). Copy Destination:=wsGame.Cells(i, 10) 'копируем и вставляем специальной вставкой значений сгенерированнстые номират с.1Gi" (L)s" (4, 4). .PasteSpecial Paste:=xlPasteValues ​​i = i + 11 Neste b Neste t Slutt Sub  

Det gjenstår å angi de ønskede startparametrene i de gule cellene og kjøre makroen gjennom Utvikler – Makroer (Utvikler – Makroer) eller hurtigtast andre+F8.

For klarhetens skyld kan du også bygge et diagram for den siste kolonnen med en kumulativ sum, som gjenspeiler endringen i pengesaldoen under spillet:

Sammenligning av ulike strategier

Nå, ved å bruke den opprettede simulatoren, kan du teste hvilken som helst spillstrategi på ekte trekninger i 2022 og se resultatene den ville gi. Hvis du spiller 1 lodd i hver trekning, ser det totale bildet av "plommen" omtrent slik ut:

Her:

• Generator er et spill hvor vi i hver trekning velger tilfeldige tall laget av generatoren vår (med samme vekt).
• Favoritter er et spill hvor vi i hver trekning bruker de samme tallene – de som oftest har falt ut i trekninger de siste to årene (27, 32, 11, 14, 34, 40).
• Outsiders – det samme, men vi bruker de mest sjeldne nedtrekksnumrene (12, 18, 26, 10, 21, 6).
• Forkjølelse – i alle trekninger bruker vi tall som ikke har falt ut på lenge (35, 5, 39, 11, 6, 29).

Som du kan se er det ingen stor forskjell, men tilfeldig tallgeneratoren oppfører seg litt bedre enn de andre "strategiene".

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

Å spille i hver trekning med ett lodd med tilfeldig genererte tall (med samme vekt):

Å spille 10 lodd i hver trekning med tilfeldig genererte tall (med samme vekt):

Å spille 100 lodd i hver trekning med tilfeldige tall (med samme vekt):

Kommentarer er, som de sier, overflødige – panteavløp er uunngåelig i alle tilfeller 🙂