Lottersimulering i Excel

Lotteriet er ikke en jagt på held,

det er en jagt på tabere.

Med misundelsesværdig regelmæssighed (og oftere på det seneste) skriver folk til mig og beder om hjælp til forskellige beregninger i forbindelse med lotterier. Nogen vil implementere deres hemmelige algoritme til at vælge vindende numre i Excel, nogen vil finde mønstre i tallene, der er faldet ud af tidligere trækninger, nogen vil fange arrangørerne af lotteriet i et uærligt spil.

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

Opgave 1. Sandsynlighed for at vinde

Lad os tage det klassiske Stoloto 6 ud af 45 lotteri som et eksempel. Ifølge reglerne er det kun dem, der har gættet alle 10 numre ud af 6, der modtager en superpræmie (45 millioner rubler eller mere, hvis saldoen i præmiefonden er akkumuleret fra tidligere trækninger). Hvis du gættede 5, vil du modtage 150 tusind rubler, hvis 4 - 1500 rubler. , hvis 3 numre ud af 6, så 150 rubler, hvis 2 numre - du vil returnere 50 rubler brugt på billetten. Gæt kun én eller ingen – få kun endorfiner fra spilprocessen.

Den matematiske sandsynlighed for at vinde kan nemt beregnes ved hjælp af standardfunktionen NUMBERCOMB (FORENE), som er tilgængelig i Microsoft Excel til sådan et tilfælde. Denne funktion beregner antallet af kombinationer af N tal ud af M. Så for vores "6 ud af 45" lotteri ville det være:

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

… som er lig med 8, det samlede antal af alle mulige kombinationer i dette lotteri.

Hvis du vil beregne sandsynligheden for en delvis gevinst (2-5 tal ud af 6), så skal du først beregne antallet af sådanne muligheder, som er lig med produktet af antallet af kombinationer af gættede tal ud af 6 med antallet af ugættede tal ud af de resterende (45-6) = 39 tal. Derefter dividerer vi det samlede antal af alle mulige kombinationer (8) med det modtagne antal gevinster for hver mulighed – og vi får vinder sandsynligheden for hver sag:

Sandsynligheden for for eksempel at dø i et flystyrt i Vort Land er i øvrigt anslået til omkring 1 ud af en million. Og sandsynligheden for at vinde i et casino på roulette, satse alt på ét tal er 1 til 37.

Hvis alt ovenstående ikke stoppede dig, og du stadig er klar til at spille videre, så fortsæt.

Opgave 2. Hyppighed af forekomst af hvert nummer

Til at begynde med, lad os bestemme med hvilken frekvens visse tal falder ud. I et ideelt lotteri, givet et tilstrækkeligt stort tidsinterval til analyse, bør alle kugler have samme sandsynlighed for at være i den vindende prøve. I virkeligheden kan lotteri-tromlens designtræk og kuglernes vægtform forvrænge dette billede, og for nogle kugler kan sandsynligheden for at falde ud meget vel være højere/lavere end for andre. Lad os teste denne hypotese i praksis.

Lad os for eksempel tage data om alle 2020 ud af 21 lodtrækninger, der fandt sted i 6-45 fra hjemmesiden for deres arrangør Stoloto, designet i form af sådan et "smart" bord, praktisk til analyse, med navnet tabArkiv Cirkulation. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка for анализа:

Brug funktionen til at beregne hyppigheden af ​​forekomsten af ​​hvert tal TÆL.HVIS (COUNTIF) og tilføje en funktion til det TEKST (TEKST)at tilføje foranstillede nuller og stjerner før og efter til etcifrede tal, så COUNTIF ser efter forekomsten af ​​et tal hvor som helst i kombinationen i kolonne B. For større klarhed vil vi også bygge et diagram efter resultater og sortere frekvenserne i faldende rækkefølge:

I gennemsnit skal enhver bold falde 1459 træk * 6 bolde / 45 numre = 194,53 gange (det er præcis, hvad der kaldes i statistikken математическим ожиданием; Соответственно, можно попробовать использовать эту иinfomацию для стратегии выигрыша, т.е. либо ставить,

Opgave 3. Hvilke tal har ikke været trukket i lang tid?

En anden strategi er baseret på ideen om, at med et tilstrækkeligt stort antal trækninger, før eller siden skulle hvert tal fra alle tilgængelige fra 1 til 45 falde ud. Så hvis nogle tal ikke er dukket op blandt vinderne i lang tid ("kolde bolde"), så er det logisk at prøve at satse på dem i fremtiden. 

Можно легко найти все давно не выпадавшие номера, сли отсортировать наш архив тиражей den 2020-21 MERE UDSAT (MATCH). Den vil søge fra top til bund (dvs. fra nye til gamle kørsler) for at søge efter hvert nummer og give serienummeret på kørslen (tæller fra slutningen af ​​året til begyndelsen), hvor dette nummer sidst blev droppet:

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

En anden spilstrategi er baseret på at eliminere den psykologiske faktor, når man gætter tal. Når en spiller vælger tal ved at lave sin indsats, gør han dette ubevidst ikke helt rationelt. Ifølge statistikker vælges for eksempel tal fra 1 til 31 70 % oftere end resten (favoritdatoer), 13 vælges sjældnere (pokkers dusin), numre, der indeholder de "heldige" syv, vælges oftere osv. Men vi spiller mod en maskine (lotteritromme), hvor alle numre er ens, så det giver mening at vælge dem med samme matematiske upartiskhed for at udligne vores chancer. For at gøre dette skal vi oprette en generator af tilfældige og – vigtigst af alt – ikke-gentagende tal i Excel:

At gøre dette:

1. Lad os oprette en "smart" tabel med navnet bordGenerator, hvor den første kolonne vil være vores tal fra 1 til 45.
2. I den anden kolonne skal du indtaste vægten for hvert tal (vi skal bruge det lidt senere). Hvis alle tal er lige meget værdifulde for os, og vi ønsker at vælge dem med lige stor sandsynlighed, så kan vægten sættes lig med 1 overalt.
3. I den tredje kolonne bruger vi funktionen SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 til 1, добавив к нему вес из предыдущего столбц. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) et nyt sæt med 45 tilfældige tal vil blive genereret, under hensyntagen til vægten for hver af dem.
4. Lad os tilføje en fjerde kolonne, hvor vi bruger funktionen RANG (RANG) вычислим ранг (позицию в топе) til каждого из чисел.

Nu er det tilbage at foretage et udvalg af de første seks tal efter rang 6 ved hjælp af funktionen MERE UDSAT (MATCH):

При нажатии на клавишу F9 formlerne på Excel-arket bliver genberegnet, og hver gang får vi et nyt sæt med 6 tal i grønne celler. Desuden vil de tal, for hvilke der er sat en større vægt i kolonne B, få en forholdsmæssigt højere rangering og derfor optræde oftere i resultaterne af vores tilfældige stikprøve. Hvis vægten for alle numre er sat til den samme, vil alle blive valgt med samme sandsynlighed. På denne måde får vi en retfærdig og upartisk tilfældig talgenerator på 6 ud af 45, men med mulighed for at justere tilfældigheden af ​​fordelingen, hvis det er nødvendigt.

Hvis vi beslutter os for at spille i hver lodtrækning ikke med én, men for eksempel med to lodder på én gang, i hver af hvilke vi vælger ikke-gentagende numre, så kan vi blot tilføje yderligere linjer fra bunden til det grønne område, tilføjer 6, 12, 18 osv. til ranglisten. d. henholdsvis:

Opgave 5. Lottersimulator i Excel

Som en apoteose af hele dette emne, lad os skabe en fuldgyldig lotterisimulator i Excel, hvor du kan prøve alle strategier og sammenligne resultaterne (i optimeringsteori kaldes noget lignende også Monte Carlo-metoden, men det vil være enklere for os).

For at gøre alting så tæt på virkeligheden som muligt, forestil dig et øjeblik, at det er 1. januar 2022, og vi har årets lodtrækninger foran os, hvor vi planlægger at spille. Jeg indtastede de rigtige tabte tal i tabellen tablTiraži2022, adskille de yderligere tegnede tal fra hinanden i separate kolonner for at gøre det lettere for efterfølgende beregninger:

På et separat ark Vildt opret et emne til modellering i form af et "smart" bord med navnet tabIgra følgende formular:

Her:

• I de gule celler ovenfor vil vi for makroen indstille antallet af trækninger i 2022, som vi ønsker at deltage i (1-82) og antallet af lodder, vi spiller i hver trækning.
• Dataene for de første 11 kolonner (AJ) vil blive kopieret af makroen fra 2022 tegnearket.
• Data for de næste seks kolonner (KP) vil makroen tage fra arket Generator, hvor vi har implementeret en tilfældig talgenerator (se opgave 4 ovenfor).
• I kolonne Q tæller vi antallet af matches mellem de tabte tal og dem, der er genereret ved hjælp af funktionen SUMPRODUCT (SUMPRODUKT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, есливилтат, есливиграли, за минус 50 рублей за билет, есливилтат, исливиграли, за минус XNUMX рублей за билет, есливиграли, за XNUMX рублей за билет, есливилет, за XNUMX р.
• I sidste kolonne S betragter vi det samlede resultat af hele spillet som en kumulativ total for at se dynamikken i processen.

Og for at genoplive hele denne struktur har vi brug for en lille makro. På fanen developer (Udvikler) vælge et hold Visual Basic eller brug tastaturgenvej andre+F11. Tilføj derefter et nyt tomt modul via menuen Indsæt – Modul og indtast 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("Иенгра") Indstil wsheets("") = Иенгра"). wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6:1048576").Slet 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тира2022жия 1s. (1, 1).Copy Destination:=wsGame.Cells(i, 10) 'копируем и вставляем специальной вставкой значений сгенерированнстые номират с.1Ge... .PasteSpecial Paste:=xlPasteValues ​​i = i + 4 Næste b Næste t End Sub  

Det er tilbage at indtaste de ønskede startparametre i de gule celler og køre makroen igennem Udvikler – Makroer (Udvikler – Makroer) eller tastaturgenvej andre+F8.

For klarhedens skyld kan du også bygge et diagram for den sidste kolonne med en kumulativ total, der afspejler ændringen i pengebalancen under spillet:

Sammenligning af forskellige strategier

Nu, ved hjælp af den oprettede simulator, kan du teste enhver spilstrategi på rigtige lodtrækninger i 2022 og se de resultater, den ville bringe. Hvis du spiller 1 lod i hver lodtrækning, så ser det overordnede billede af "blommen" sådan ud:

Her:

• Generator er et spil, hvor vi i hver trækning vælger tilfældige tal skabt af vores generator (med samme vægt).
• foretrukne er et spil, hvor vi i hver lodtrækning bruger de samme numre – dem der oftest er faldet ud i lodtrækninger de seneste to år (27, 32, 11, 14, 34, 40).
• Outsiders – det samme, men vi bruger de mest sjældne drop-down-numre (12, 18, 26, 10, 21, 6).
• Forkølelse – i alle lodtrækninger bruger vi tal, der ikke er faldet ud i lang tid (35, 5, 39, 11, 6, 29).

Som du kan se, er der ikke den store forskel, men tilfældig talgeneratoren opfører sig lidt bedre end de andre “strategier”.

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

Spil i hver lodtrækning med én lod med tilfældigt genererede tal (med samme vægt):

At spille 10 lodder i hver lodtrækning med tilfældigt genererede tal (med samme vægt):

Spil 100 lodder i hver lodtrækning med tilfældige tal (med samme vægt):

Kommentarer, som man siger, er overflødige – et pantdræn er uundgåeligt i alle tilfælde 🙂