Simulacija lutrije u Excelu

Lutrija nije lov na sreću,

to je lov na gubitnike.

Zavidnom redovitošću (u zadnje vrijeme i sve češće) pišu mi ljudi tražeći pomoć u raznim izračunima vezanim uz lutriju. Netko želi implementirati svoj tajni algoritam za odabir dobitnih brojeva u Excelu, netko želi pronaći uzorke u brojevima koji su ispali iz prošlih izvlačenja, netko želi uhvatiti organizatore lutrije u nečasnoj igri.

U ovoj članku želio bih odgovoriti na dio ovih pitanja. Blago, u Excelu za rješavanje takvih zadataka dovoljno instrumenata, mnogi od njih, kstati, mogu se koristiti iu više prozaičnih radnih situacija.

Zadatak 1. Vjerojatnost dobitka

Uzmimo kao primjer klasičnu lutriju Stoloto 6 od 45. Prema pravilima, samo oni koji su pogodili svih 10 brojeva od 6 dobivaju super nagradu (45 milijuna rubalja ili više ako se saldo nagradnog fonda nakupio iz prethodnih izvlačenja). Ako pogodite 5, dobit ćete 150 tisuća rubalja, ako 4 – 1500 rubalja. , ako su 3 broja od 6, tada 150 rubalja, ako su 2 broja – vratit ćete 50 rubalja potrošenih na kartu. Pogodite samo jedan ili nijedan – dobivajte samo endorfine iz procesa igre.

Matematička vjerojatnost dobitka može se jednostavno izračunati pomoću standardne funkcije BROJ ČEŠALJ (KOMBINIRATI), koji je za takav slučaj dostupan u programu Microsoft Excel. Ova funkcija izračunava broj kombinacija N brojeva od M. Dakle, za našu lutriju "6 od 45" to bi bilo:

=ČISLKOMB(45;6)

… što je jednako 8, ukupnom broju svih mogućih kombinacija u ovoj lutriji.

Ako želite izračunati vjerojatnost za djelomični dobitak (2-5 brojeva od 6), tada ćete prvo morati izračunati broj takvih opcija, koji je jednak umnošku broja kombinacija pogađanih brojeva od 6 po broju nepogođenih brojeva od preostalih (45-6) = 39 brojeva. Potom dijelimo ukupan broj svih mogućih kombinacija (8) s dobivenim brojem dobitaka za svaku opciju – i dobivamo vjerojatnosti dobitka za svaki slučaj:

Inače, vjerojatnost, primjerice, pogibije u zrakoplovnoj nesreći kod nas se procjenjuje na oko 1 prema milijun. A vjerojatnost dobitka u kasinu na ruletu, kladeći sve na jedan broj je 1 prema 37.

Ako vas sve navedeno nije zaustavilo i još uvijek ste spremni igrati dalje, nastavite.

Zadatak 2. Učestalost pojavljivanja svakog broja

Za početak, odredimo kojom učestalošću ispadaju određeni brojevi. U idealnoj lutriji, s obzirom na dovoljno velik vremenski interval za analizu, sve bi kuglice trebale imati istu vjerojatnost da budu u dobitnom uzorku. U stvarnosti, značajke dizajna bubnja za lutriju i oblik težine kuglica mogu iskriviti ovu sliku, a za neke kuglice vjerojatnost ispadanja može biti veća/manja nego za druge. Provjerimo ovu hipotezu u praksi.

Uzmimo, na primjer, podatke o svim 2020 od 21 izvlačenja lutrije koje su se održale u 6-45 sa web stranice njihovog organizatora Stoloto, dizajnirane u obliku takve "pametne" tablice, zgodne za analizu, s nazivom tabArhiva Tiraž. Rozygryši prolaze dva puta u dan (u 11 utra i u 11 navečer), t.e. u ovoj tablici kod nas pola tisuće tiraž-strok — sasvim točna za početak izbora za analizu:

Za izračun učestalosti pojavljivanja svakog broja upotrijebite funkciju COUNTIF (COUNTIF) i dodati mu funkciju TEKST (TEKST)za dodavanje vodećih nula i zvjezdica prije i poslije jednoznamenkastim brojevima, tako da COUNTIF traži pojavljivanje broja bilo gdje u kombinaciji u stupcu B. Također, radi veće jasnoće, napravit ćemo grafikon prema rezultatima i sortirati učestalosti u silaznom redoslijedu:

U prosjeku bi bilo koja kuglica trebala pasti 1459 izvlačenja * 6 kuglica / 45 brojeva = 194,53 puta (to je upravo ono što se zove statistika matematičkim ožidaniem), no dobro je vidljivo da su neke brojke (27, 32, 11…) pale znatno više (+18%), a neke (10, 21, 6…) su se znatno smanjile (-15%), nego osnovna masa. Dakle, možete pokušati koristiti ovu informaciju za igranje strategije, tj. ili staviti na te šare, što vypadaût čaŝe, ili naoborot — napraviti stavku na redko vypadaûŝie šary u nadeždi, da oni moraju nagnati otstavanie.

Zadatak 3. Koji brojevi nisu dugo izvučeni?

Druga strategija temelji se na ideji da s dovoljno velikim brojem izvlačenja prije ili kasnije svaki broj od svih dostupnih od 1 do 45 treba ispasti. Dakle, ako se neki brojevi dugo nisu pojavljivali među dobitnicima („hladne kuglice“), onda je logično pokušati se kladiti na njih u budućnosti. 

Možete lako pronaći sve davno neispadajuće sobe, ako sortirate našu arhivu tiraža za 2020.-21. godinu prema smanjenju datuma i koristite funkciju EKSPONIRANIJI (UTAKMICA). Tražit će od vrha prema dolje (tj. od novih prema starim serijama) kako bi potražio svaki broj i dao serijski broj serije (računajući od kraja godine do početka) gdje je ovaj broj posljednji put ispušten:

Zadaća 4. Generator slučajnih dlijeta

Druga strategija igre temelji se na uklanjanju psihološkog čimbenika pri pogađanju brojeva. Kada igrač bira brojeve ulažući svoju okladu, on to podsvjesno čini ne sasvim racionalno. Prema statistici, primjerice, brojevi od 1 do 31 biraju se 70% češće od ostalih (omiljeni datumi), 13 se rjeđe bira (prokleta tuceta), češće se biraju brojevi koji sadrže “sretnu” sedmicu itd. Ali igramo protiv stroja (bubanj za lutriju) kojemu su svi brojevi isti, pa ih ima smisla birati s jednakom matematičkom nepristranošću kako bismo izjednačili šanse. Da bismo to učinili, moramo stvoriti generator slučajnih i – što je najvažnije – brojeva koji se ne ponavljaju u Excelu:

Uraditi ovo:

1. Kreirajmo "pametnu" tablicu pod nazivom TableGenerator, gdje će prvi stupac biti naši brojevi od 1 do 45.
2. U drugi stupac upišite težinu za svaki broj (trebat će nam malo kasnije). Ako su nam svi brojevi jednako vrijedni i želimo ih odabrati s jednakom vjerojatnošću, tada se težina svugdje može postaviti jednaka 1.
3. U trećem stupcu koristimo funkciju SLCHIS (RAND), koji u Excelu generira slučajno sitno broj od 0 do 1, dodajući mu težinu iz prethodne tablice. Na taj način svaki put pri presjeci liste (nazad na tipku F9) novi skup od 45 nasumičnih brojeva bit će generiran, uzimajući u obzir težinu za svaki od njih.
4. Dodajmo četvrti stupac, gdje koristimo funkciju RANG (RANG) izbrojim rang (poziciju u tope) za svakoga iz cijela.

Sada preostaje napraviti izbor prvih šest brojeva po rangu 6 pomoću funkcije EKSPONIRANIJI (UTAKMICA):

Pri nažatii na tipku F9 formule na Excel listu će se ponovno izračunati i svaki put ćemo dobiti novi skup od 6 brojeva u zelenim ćelijama. Štoviše, brojevi za koje je postavljena veća težina u stupcu B dobit će proporcionalno viši rang i stoga se češće pojavljuju u rezultatima našeg slučajnog uzorka. Ako je težina za sve brojeve postavljena na istu, tada će svi biti odabrani s istom vjerojatnošću. Na ovaj način dobivamo pošten i nepristran generator slučajnih brojeva od 6 od 45, ali s mogućnošću prilagođavanja slučajnosti distribucije ako je potrebno.

Ako odlučimo igrati u svakom izvlačenju ne s jednim, već, na primjer, s dva listića odjednom, u svakom ćemo izabrati brojeve koji se ne ponavljaju, tada možemo jednostavno dodati dodatne linije od dna do zelenog raspona, dodavanje 6, 12, 18 itd. rangu. d. odnosno:

Zadatak 5. Simulator lutrije u Excelu

Kao apoteoza cijele ove teme, napravimo potpuni simulator lutrije u Excelu, gdje možete isprobati bilo koju strategiju i usporediti rezultate (u teoriji optimizacije, nešto slično se također naziva Monte Carlo metoda, ali bit će jednostavnija za nas).

Kako bi sve bilo što bliže stvarnosti, zamislimo na trenutak da je 1. siječnja 2022. i da su pred nama ovogodišnja izvlačenja u kojima planiramo nastupiti. U tablicu sam upisao stvarne ispale brojeve tablTirazi2022, odvajajući dodatno izvučene brojeve jedan od drugog u zasebne stupce radi lakšeg naknadnog izračuna:

Na posebnom listu Igra stvoriti prazninu za modeliranje u obliku "pametne" tablice s imenom tabIgra sljedeći obrazac:

Ovdje:

• U gornjim žutim ćelijama ćemo za makro postaviti broj izvlačenja u 2022. u kojima želimo sudjelovati (1-82) i broj listića koje igramo u svakom izvlačenju.
• Podatke za prvih 11 stupaca (AJ) kopirat će makronaredba iz liste za izvlačenje 2022. godine.
• Podatke za sljedećih šest stupaca (KP) makro će preuzeti s lista Generator, gdje smo implementirali generator slučajnih brojeva (vidi problem 4 gore).
• U stupcu Q brojimo podudaranja između ispuštenih brojeva i onih generiranih pomoću funkcije SUMPRODUCT (SUMPROIZVOD).
• U stolbce R vyčislâem financijski rezultat (ako niste igrali, to minus 50 rublej za kartu, ako ste igrali, to priz — 50 r. za kartu)
• U posljednjem stupcu S uzimamo u obzir ukupni rezultat cijele utakmice kao kumulativni zbroj kako bismo vidjeli dinamiku u procesu.

A da bismo oživjeli cijelu ovu strukturu, potreban nam je mali makro. Na kartici razvijač (Programer) izabrati tim Visual Basic ili koristite tipkovni prečac drugo+F11. Zatim dodajte novi prazan modul putem izbornika Umetak – Modul i tamo unesite sljedeći kod:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'obʺâvlâem promenljive za ssylke na liste Set wsGame = Worksheets("Igra") Set wsNumbers = Worksheets("Generator") Set wsArchive = Radni listovi("Tiraži 2022") iGames = wsGame.Range("C1") 'količina tiraža iTickets = wsGame.Range("C2") 'količina karata u svakom tiražu i = 5 'prva linija u tablici tablIgra wsGame.Rows ("6:1048576").Izbriši 'očišćavamo stare podatke For t = 1 To iGames For b = 1 To iTickets 'kopiram igrane brojeve s liste Tiraži 2022 i postavljam na listu Igra wsArchive.Cells(t + 1, 1).Promijeni veličinu (1, 10).Copy Destination:=wsGame.Cells(i, 1) 'kopiram i ustavljam posebnu postavku vrijednosti generiranih brojeva s liste Generator wsNumbers.Range("G4:L4").Kopiraj wsGame.Cells(i, 11) .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Next b Next t End Sub  

Ostaje unijeti željene početne parametre u žute ćelije i pokrenuti makro Programer – Macros (Razvojni programer — Makronaredbe) ili tipkovni prečac drugo+F8.

Radi jasnoće, također možete napraviti dijagram za zadnji stupac s kumulativnim ukupnim iznosom, koji odražava promjenu novčanog salda tijekom igre:

Usporedba različitih strategija

Sada, pomoću stvorenog simulatora, možete testirati bilo koju strategiju igre na stvarnim izvlačenjima u 2022. i vidjeti rezultate koje će ona donijeti. Ako igrate 1 listić u svakom izvlačenju, tada ukupna slika "šljive" izgleda otprilike ovako:

Ovdje:

• Generator je igra u kojoj u svakom izvlačenju biramo slučajne brojeve koje je kreirao naš generator (s istom težinom).
• Omiljene je igra u kojoj u svakom izvlačenju koristimo iste brojeve – one koji su najčešće ispadali u izvlačenjima u posljednje dvije godine (27, 32, 11, 14, 34, 40).
• Autsajderi – isto, ali koristimo najrjeđe padajuće brojeve (12, 18, 26, 10, 21, 6).
• Hladan – u svim izvlačenjima koristimo brojeve koji dugo nisu ispadali (35, 5, 39, 11, 6, 29).

Kao što vidite, nema velike razlike, ali generator slučajnih brojeva ponaša se malo bolje od ostalih "strategija".

Također možete pokušati igrati veliki broj ulaznica u svakoj tiraži, da biste otvorili veći broj varijanti (inogda za ovo nekoliko igrača nalazi se u grupi).

Igranje u svakom izvlačenju s jednim listićem s nasumično generiranim brojevima (iste težine):

Igranje 10 listića u svakom izvlačenju s nasumično generiranim brojevima (s istom težinom):

Igranje 100 listića u svakom izvlačenju s nasumičnim brojevima (s istom težinom):

Komentari su, kako kažu, suvišni – odljev pologa neizbježan je u svim slučajevima 🙂