Simulacija loterije v Excelu

Loterija ni lov na srečo,

to je lov na poražence.

Zavidljivo redno (v zadnjem času pa vse pogosteje) mi pišejo ljudje s prošnjo za pomoč pri različnih izračunih v zvezi z loterijami. Nekdo želi implementirati svoj skrivni algoritem za izbiro dobitnih številk v Excelu, nekdo želi najti vzorce v številkah, ki so izpadle iz preteklih žrebanj, nekdo želi ujeti organizatorje loterije v nepošteni igri.

V tem članku želim odgovoriti na del teh vprašanj. Dobro, v Excelu za reševanje takšnih nalog je dovolj orodij, mnogi od njih, kstati, se lahko uporabijo in v bolj prozaičnih delovnih situacijah.

Naloga 1. Verjetnost zmage

Vzemimo za primer klasično loterijo Stoloto 6 od 45. V skladu s pravili prejmejo super nagrado samo tisti, ki so uganili vseh 10 številk od 6 (45 milijonov rubljev ali več, če se je stanje nagradnega sklada nabralo iz prejšnjih žrebanj). Če ste uganili 5, boste prejeli 150 tisoč rubljev, če 4 - 1500 rubljev. , če 3 številke od 6, potem 150 rubljev, če 2 številki – vrnete 50 rubljev, porabljenih za vstopnico. Ugani samo enega ali nobenega – iz procesa igre dobiš samo endorfine.

Matematično verjetnost zmage je mogoče enostavno izračunati s standardno funkcijo NUMBERCOMB (ZDRUŽI), ki je za tak primer na voljo v programu Microsoft Excel. Ta funkcija izračuna število kombinacij N števil iz M. Za našo loterijo »6 od 45« bi bilo torej:

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

… kar je enako 8, skupnemu številu vseh možnih kombinacij v tej loteriji.

Če želite izračunati verjetnost za delni dobitek (2-5 številk od 6), boste morali najprej izračunati število takšnih možnosti, ki je enako zmnožku števila kombinacij uganjenih števil iz 6 po številu neuganjenih števil od preostalih (45-6) = 39 števil. Nato skupno število vseh možnih kombinacij (8) delimo s prejetim številom dobitkov za vsako opcijo – in dobimo verjetnosti zmage za vsak primer:

Mimogrede, verjetnost, da na primer umrete v letalski nesreči pri nas, ocenjujejo na približno 1 proti milijon. Verjetnost zmage v igralnici na ruleti, pri kateri stavite vse na eno številko, je 1 proti 37.

Če vas vse našteto ni ustavilo in ste še vedno pripravljeni igrati naprej, nadaljujte.

Naloga 2. Pogostost pojavljanja posameznega števila

Za začetek ugotovimo, s kakšno pogostostjo izpadajo določene številke. Pri idealni loteriji bi morale imeti vse žogice enako verjetnost, da so v zmagovalnem vzorcu, ob dovolj velikem časovnem intervalu za analizo. V resnici lahko oblikovne značilnosti bobna za loterijo in oblika teže kroglic popačijo to sliko in pri nekaterih kroglicah je verjetnost, da izpadejo, večja/manjša kot pri drugih. Preizkusimo to hipotezo v praksi.

Vzemimo za primer podatke o vseh 2020 od 21 žrebanj loterije, ki so potekala v 6-45 s spletne strani njihovega organizatorja Stoloto, zasnovane v obliki takšne "pametne" tabele, priročne za analizo, z imenom zavihekArhiv Naklada. Rozygryši potekajo dva časa v dnevu (v 11 utri in v 11 zvečer), t.e. v tej tabeli v nas poltory tisoč tiražej-strok — precejšnja za začetek izbire za analizo:

Za izračun pogostosti pojavljanja posamezne številke uporabite funkcijo COUNTIF (COUNTIF) in mu dodajte funkcijo BESEDILO (BESEDILO)za dodajanje začetnih ničel in zvezdic pred in za enomestnimi številkami, tako da COUNTIF išče pojavljanje števila kjer koli v kombinaciji v stolpcu B. Prav tako bomo za večjo jasnost zgradili grafikon glede na rezultate in razvrstili frekvence v padajočem vrstnem redu:

V povprečju bi morala katera koli žogica pasti 1459 žrebanj * 6 žogic / 45 številk = 194,53-krat (točno to se imenuje v statistiki matematičnim ожиданием), vendar je dobro razvidno, da so nekatera števila (27, 32, 11…) padla opazno več (+18 %), a nekatera (10, 21, 6…) na obratu opazno nižje (-15 %), kot je osnovna masa. Torej, lahko poskusite uporabiti to informacijo za strategijo igranja, t.e. либо ставить на te šarы, что выпадают чаще, либо наоборот — делать ставку na redko izpadajoče šarы v nade, da morajo nagnati odstavljanje.

Naloga 3. Katere številke že dolgo niso bile izžrebane?

Druga strategija temelji na ideji, da bi ob dovolj velikem številu žrebanj prej ali slej izpadla vsaka številka od vseh razpoložljivih od 1 do 45. Torej, če se nekatere številke že dolgo niso pojavile med zmagovalci (»hladne kroglice«), potem je logično, da poskusite staviti nanje v prihodnosti. 

Lahko enostavno poiščete vse davno neizpadle sobe, če razvrstite naš arhiv tiraž za 2020-21 leto po padajočih datumih in uporabite funkcijo BOLJ IZPOSTAVLJENO (TEKMA). Iskal bo od zgoraj navzdol (tj. od novih do starih serij), da bi poiskal vsako številko in dal serijsko številko serije (šteto od konca leta do začetka), kjer je bila ta številka nazadnje izpuščena:

Naloga 4. Generator naključnih čisl

Druga strategija igre temelji na odpravi psihološkega dejavnika pri ugibanju številk. Ko igralec s stavo izbira številke, to podzavestno počne ne povsem racionalno. Po statističnih podatkih so na primer številke od 1 do 31 izbrane 70% pogosteje kot ostale (najljubši datumi), 13 je izbrana manj pogosto (prekleta ducata), številke, ki vsebujejo "srečno" sedem, so pogosteje izbrane itd. A igramo proti stroju (loterijskemu bobnu), pri katerem so vse številke enake, zato jih je smiselno izbrati z enako matematično nepristranskostjo, da si izenačimo možnosti. Za to moramo ustvariti generator naključnih in – kar je najpomembneje – neponavljajočih se števil v Excelu:

Storiti to:

1. Ustvarimo "pametno" tabelo z imenom tableGenerator, kjer bodo v prvem stolpcu naše številke od 1 do 45.
2. V drugi stolpec vpišite težo za vsako številko (potrebovali jo bomo malo kasneje). Če so vsa števila za nas enako vredna in jih želimo izbrati z enako verjetnostjo, potem lahko utež povsod nastavimo enako 1.
3. V tretjem stolpcu uporabimo funkcijo SLČIS (RAND), ki v Excelu generira naključno drobno število od 0 do 1, dodano k njegovi teži iz prejšnjega stola. Tako vsak раз при пересчёте листа (нажатии на клавишу F9) bo ustvarjen nov niz 45 naključnih števil ob upoštevanju teže vsakega od njih.
4. Dodajmo četrti stolpec, kjer uporabljamo funkcijo RANK (RANG) izštej rang (pozicijo v vrhu) za vsakega iz črte.

Zdaj ostane še, da s funkcijo izberemo prvih šest številk po rangu 6 BOLJ IZPOSTAVLJENO (TEKMA):

При нажатии на клавишу F9 formule na Excelovem listu bodo preračunane in vsakič bomo dobili nov niz 6 števil v zelenih celicah. Poleg tega bodo številke, za katere je bila v stolpcu B določena večja utež, deležna sorazmerno višjega ranga in se tako pogosteje pojavljajo v rezultatih našega naključnega vzorca. Če je utež za vsa števila enaka, bodo vsa izbrana z enako verjetnostjo. Na ta način dobimo pošten in nepristranski generator naključnih števil 6 od 45, vendar z možnostjo prilagajanja naključnosti porazdelitve, če je potrebno.

Če se odločimo, da bomo v vsakem žrebanju igrali ne z enim, ampak na primer z dvema listkoma hkrati, v vsakem od njih bomo izbrali številke, ki se ne ponavljajo, potem lahko preprosto dodamo dodatne vrstice od spodaj do zelenega območja, dodajanje 6, 12, 18 itd. d. oziroma:

Naloga 5. Simulator loterije v Excelu

Kot apoteoza celotne te teme, ustvarimo popoln simulator loterije v Excelu, kjer lahko preizkusite poljubne strategije in primerjate rezultate (v teoriji optimizacije se nekaj podobnega imenuje tudi metoda Monte Carlo, vendar bo preprostejša za nas).

Da bo vse skupaj čim bolj resnično, si za trenutek predstavljajte, da je 1. januar 2022 in da so pred nami letošnja žrebanja, v katerih nameravamo igrati. V tabelo sem vpisal realna izpadla števila tablTiraži2022, ki ločujejo dodatno izžrebane številke med seboj v ločene stolpce za udobje poznejših izračunov:

Na posebnem listu Game ustvarite praznino za modeliranje v obliki "pametne" tabele z imenom tabIgra naslednji obrazec:

Tukaj:

• V zgornjih rumenih celicah bomo za makro določili število žrebanj v letu 2022, v katerih želimo sodelovati (1-82) in število listkov, ki jih igramo v posameznem žrebanju.
• Podatke za prvih 11 stolpcev (AJ) bo kopiral makro iz žrebanja 2022.
• Podatke za naslednjih šest stolpcev (KP), ki jih bo makro vzel iz lista Generator, kjer smo implementirali generator naključnih števil (glej problem 4 zgoraj).
• V stolpcu Q štejemo število ujemanj med izpuščenimi številkami in tistimi, ki so ustvarjene s funkcijo SUMPRODUCT (SUM PRODUKT).
• V stolbce R izračunavamo finančni rezultat (če niste igrali, to minus 50 rublej za vstopnico, če ste igrali, to priz — 50 rubljev za vstopnico)
• V zadnjem stolpcu S upoštevamo skupni rezultat celotne igre kot kumulativni seštevek, da vidimo dinamiko v procesu.

In za oživitev celotne strukture potrebujemo majhen makro. Na zavihku razvijalec (Razvijalec) izberite ekipo Visual Basic ali uporabite bližnjico na tipkovnici druga+F11. Nato dodajte nov prazen modul prek menija Vstavi – Modul in tam vnesite naslednjo kodo:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'objavljamo spremembe za povezave na listih Set wsGame = Worksheets("Igra") Set wsNumbers = Worksheets("Generator") Set wsArchive = Worksheets("Tiraži 2022") iGames = wsGame.Range("C1") 'število tiražej iTickets = wsGame.Range("C2") 'število vstopnic v vsakem tiražu i = 5 'prva vrstica v tablici tabIgra wsGame.Rows ("6:1048576").Izbriši 'očiščujemo stare podatke Za t = 1 Za iGames Za b = 1 Za iTickets 'kopiramo izigrane številke s seznama Tiraž 2022 in vstavimo na seznam Igra wsArchive.Cells(t + 1, 1).Spremeni velikost (1, 10).Copy Destination:=wsGame.Cells(i, 1) 'kopiramo in vstavljamo posebno vstavko vrednosti generiranih številk s seznama Generator wsNumbers.Range("G4:L4").Kopiramo wsGame.Cells(i, 11) .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Next b Next t End Sub  

V rumene celice je treba vnesti želene začetne parametre in zagnati makro Razvijalec – Makri (Razvijalec — Makri) ali bližnjico na tipkovnici druga+F8.

Zaradi jasnosti lahko sestavite tudi diagram za zadnji stolpec s kumulativno vsoto, ki odraža spremembo denarnega stanja med igro:

Primerjava različnih strategij

Zdaj lahko z ustvarjenim simulatorjem preizkusite katero koli strategijo igre na resničnih žrebanjih leta 2022 in si ogledate rezultate, ki bi jih prinesla. Če igrate 1 vstopnico v vsakem žrebanju, potem je splošna slika "šljive" videti nekako takole:

Tukaj:

• Generator je igra, kjer pri vsakem žrebanju izberemo naključna števila, ki jih ustvari naš generator (z enako težo).
• Priljubljene je igra, kjer pri vsakem žrebanju uporabljamo iste številke – tiste, ki so največkrat izpadle v žrebanjih zadnjih dveh let (27, 32, 11, 14, 34, 40).
• Outsiders – enako, vendar uporabljamo najbolj redke spustne številke (12, 18, 26, 10, 21, 6).
• Hladno – pri vseh žrebanjih uporabljamo številke, ki že dolgo niso izpadle (35, 5, 39, 11, 6, 29).

Kot lahko vidite, ni velike razlike, vendar se generator naključnih števil obnaša nekoliko bolje kot druge "strategije".

Lahko tudi preizkusite igranje velikega števila vstopnic v vsakem tiražu, da odprete večje število možnosti (inogda za to nekaj igralcev je vključenih v skupino).

Igranje v vsakem žrebanju z enim listkom z naključno generiranimi številkami (z enako težo):

Igranje 10 listkov v vsakem žrebanju z naključno ustvarjenimi številkami (z enako težo):

Igranje 100 listkov v vsakem žrebanju z naključnimi številkami (z enako težo):

Komentarji so, kot pravijo, odveč – črpanje depozita je v vseh primerih neizogibno 🙂