Simulacija lutrije u Excelu

Lutrija nije lov na srecu,

to je lov na gubitnike.

Sa zavidnom redovnošću (i sve češće u posljednje vrijeme) ljudi mi pišu tražeći pomoć u raznim kalkulacijama vezanim za lutriju. Neko želi da implementira svoj tajni algoritam za odabir dobitnih brojeva u Excelu, neko želi da pronađe obrasce u brojevima koji su ispali iz prošlih izvlačenja, neko želi da uhvati organizatore lutrije u nepoštenoj igri.

U ovom članku želim da odgovorim na dio ovih pitanja. Blago, u Excel-u za rješavanje takvih zadataka dostatno potrebnih alata, mnogi od njih, kstati, mogu se koristiti i u više prozaičnih radnih situacija.

Zadatak 1. Vjerovatnoća pobjede

Uzmimo za primjer klasičnu lutriju Stoloto 6 od 45. Prema pravilima, samo oni koji pogode svih 10 brojeva od 6 dobijaju super nagradu (45 miliona rubalja ili više ako je stanje nagradnog fonda akumulirano iz prethodnih izvlačenja). Ako pogodite 5, dobit ćete 150 hiljada rubalja, ako 4 – 1500 rubalja. , ako su 3 broja od 6, onda 150 rubalja, ako su 2 broja – vratit ćete 50 rubalja potrošenih na kartu. Pogodi samo jedan ili nijedan – uzmi samo endorfine iz procesa igre.

Matematička vjerovatnoća pobjede može se lako izračunati korištenjem standardne funkcije NUMBERCOMB (KOMBINA), koji je dostupan u Microsoft Excel-u za takav slučaj. 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, ukupan broj svih mogućih kombinacija u ovoj lutriji.

Ako želite izračunati vjerovatnoću za djelomičnu pobjedu (2-5 brojeva od 6), tada ćete prvo morati izračunati broj takvih opcija, koji je jednak proizvodu broja kombinacija pogodnih brojeva od 6 po broju nepogođenih brojeva od preostalih (45-6) = 39 brojeva. Zatim ukupan broj svih mogućih kombinacija (8) podijelimo sa primljenim brojem dobitaka za svaku opciju – i dobijemo vjerovatnoću dobitka za svaki slučaj:

Inače, vjerovatnoća da, na primjer, poginete u avionskoj nesreći u našoj zemlji procjenjuje se na oko 1 prema milion. A vjerovatnoća pobjede u kazinu na ruletu, kladeći sve na jedan broj je 1 do 37.

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

Zadatak 2. Učestalost pojavljivanja svakog broja

Za početak, odredimo s kojom frekvencijom ispadaju određeni brojevi. U idealnoj lutriji, s obzirom na dovoljno veliki vremenski interval za analizu, sve loptice treba da imaju istu vjerovatnoću da budu u pobjedničkom uzorku. U stvarnosti, karakteristike dizajna bubnja za lutriju i oblik težine loptica mogu iskriviti ovu sliku, a za neke loptice vjerovatnoća ispadanja može biti veća/manja nego za druge. Testirajmo ovu hipotezu u praksi.

Uzmimo, na primjer, podatke o svim 2020. od 21 izvlačenja lutrije koji su se odigrali 6-45 sa web stranice njihovog organizatora Stoloto, dizajnirane u obliku ovako “pametne” tablice, pogodne za analizu, sa nazivom tabArhiva Cirkulacija. Rozygryši prolaze dva puta u dan (u 11 sati i u 11 sati), t.e. u ovoj tablici u nas poltory tisuća tiražej-strok — sasvim dostatočna za početak izbora za analizu:

Da biste izračunali učestalost pojavljivanja svakog broja, koristite funkciju COUNTIF (COUNTIF) i dodajte mu funkciju TEKST (TEKST)dodati vodeće nule i zvjezdice prije i poslije jednocifrenim brojevima, tako da COUNTIF traži pojavljivanje broja bilo gdje u kombinaciji u koloni B. Također, radi veće jasnoće, napravićemo grafikon prema rezultatima i sortirati frekvencije u opadajućem redoslijedu:

U prosjeku, svaka loptica bi trebala pasti 1459 izvlačenja * 6 lopti / 45 brojeva = 194,53 puta (tačno tako se zove statistika matematičkim ožidaniem), no dobro je vidljivo, što su neke brojke (27, 32, 11…) padale zamjetno češće (+18%), a neke (10, 21, 6…) manje su zamjetne (-15%), od osnovne mase. Naravno, možete probati iskoristiti ovu informaciju za strategije igranja, t.e. ili staviti na te šare, što češće pada, ili naoborot — stavite postavku na redko padajuće šare u nadeždu, što oni moraju nagnati otklanjanje.

Zadatak 3. Koji brojevi se dugo nisu izvlačili?

Druga strategija se zasniva na ideji da sa dovoljno velikim brojem izvlačenja, prije ili kasnije svaki broj od svih dostupnih od 1 do 45 ispadne. Dakle, ako se neki brojevi već duže vrijeme ne pojavljuju među dobitnicima („hladne lopte“), onda je logično pokušati se kladiti na njih u budućnosti. 

Možete jednostavno pronaći sve davno neispadajuće sobe, ako sortirate naš arhiv tiraže za 2020-21 godinu po upadanju datuma i koristite funkciju IZLOŽENIJI (UTAKMICA). Tražit će od vrha do dna (tj. od novih do starih serija) kako bi potražio svaki broj i dao serijski broj serije (računajući od kraja godine do početka) gdje je ovaj broj zadnji put ispušten:

Zadača 4. Generator slučajnih čisel

Druga strategija igre zasniva se na eliminisanju psihološkog faktora prilikom pogađanja brojeva. Kada igrač bira brojeve ulažući svoju opkladu, on to podsvjesno čini ne sasvim racionalno. Prema statistikama, na primjer, brojevi od 1 do 31 biraju se 70% češće od ostalih (omiljeni datumi), 13 se bira rjeđe (prokleta tuceta), češće se biraju brojevi koji sadrže „sretnih“ sedam itd. Ali igramo protiv mašine (lutrijskog bubnja) za koju su svi brojevi isti, pa je logično da ih biramo sa istom matematičkom nepristrasnošću kako bismo izjednačili svoje šanse. Da bismo to učinili, moramo napraviti generator slučajnih i – što je najvažnije – brojeva koji se ne ponavljaju u Excelu:

Da biste to uradili:

1. Kreirajmo „pametnu“ tabelu pod nazivom tableGenerator, gdje će prva kolona biti naši brojevi od 1 do 45.
2. U drugu kolonu unesite težinu za svaki broj (trebat će nam malo kasnije). Ako su nam svi brojevi jednako vrijedni i želimo da ih izaberemo s jednakom vjerovatnoćom, onda se težina svuda može postaviti jednaka 1.
3. U trećoj koloni koristimo funkciju Slchis (RAND), koja u Excelu generira slučajnu sitnu cifru od 0 do 1, dodajući mu broj iz prethodnog stola. Ovakvim obrazom se nalazi lista pri peresčëtenoj listi (nažati na klavišu F9) biće generisan novi skup od 45 slučajnih brojeva, uzimajući u obzir težinu svakog od njih.
4. Dodajmo četvrti stupac, gdje koristimo funkciju RANK (RANG) vyčislim rang (poziciju u tope) za každoga iz čisela.

Sada ostaje da izvršimo izbor prvih šest brojeva po rangu 6 koristeći funkciju IZLOŽENIJI (UTAKMICA):

Pri nažatii na klavišu F9 formule na Excel listu će biti ponovo izračunate i svaki put ćemo dobiti novi set od 6 brojeva u zelenim ćelijama. Štaviše, brojevi za koje je postavljena veća težina u koloni B dobiće proporcionalno viši rang i stoga će se češće pojavljivati ​​u rezultatima našeg slučajnog uzorka. Ako je težina za sve brojeve postavljena na istu, tada će svi biti odabrani sa istom vjerovatnoćom. Na ovaj način dobijamo pravičan i nepristrasan generator slučajnih brojeva od 6 od 45, ali sa 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 tiketa odjednom, u svakom od kojih ćemo odabrati brojeve koji se ne ponavljaju, onda jednostavno možemo dodati dodatne linije od dna do zelenog raspona, dodajući 6, 12, 18, itd. rangu. d. odnosno:

Zadatak 5. Simulator lutrije u Excelu

Kao apoteozu cijele ove teme, napravimo punopravni simulator lutrije u Excelu, gdje možete isprobati bilo koju strategiju i uporediti rezultate (u teoriji optimizacije, nešto slično se naziva i Monte Carlo metoda, ali će biti jednostavnije za nas).

Da sve bude što bliže stvarnosti, zamislite na trenutak da je 1. januar 2022. godine i da su pred nama ovogodišnji žrebi u kojima planiramo da igramo. Uneo sam stvarne ispuštene brojeve u tabelu tablTiraži2022, odvajajući dodatno izvučene brojeve jedan od drugog u zasebne stupce radi pogodnosti naknadnih izračuna:

Na posebnom listu igra kreirajte 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 učestvovati (1-82) i broj tiketa koje igramo u svakom izvlačenju.
• Makro će kopirati podatke za prvih 11 stupaca (AJ) sa lista za izvlačenje 2022.
• Podatke za sljedećih šest kolona (KP) makro će preuzeti sa lista generator, gdje smo implementirali generator slučajnih brojeva (vidi problem 4 iznad).
• U koloni Q brojimo broj podudaranja između ispuštenih brojeva i onih generiranih pomoću funkcije SUMPRODUCT (SUMPROIZVOD).
• U stolbce R izračunavamo financijski rezultat (ako niste igrali, to je minus 50 rubalja za kartu, ako ste igrali, to je nagrada — 50 r. za kartu)
• U posljednjoj koloni S, ukupan rezultat cijele utakmice smatramo kumulativnim zbrojem kako bismo vidjeli dinamiku u procesu.

A da bismo oživjeli cijelu ovu strukturu, potreban nam je mali makro. Na kartici Developer (programer) izabrati tim Visual Basic ili koristite prečicu na tastaturi alt+F11. Zatim dodajte novi prazan modul preko menija Umetak – Modul i tamo unesite sljedeći kod:

Sub Luttery() Dim iGames As Integer, iTickets kao Integer, i As Long, t As Integer, b As Integer 'obʺâvlâem peremennye za ssylke na listi Set wsGame = Worksheets("Igra") Set wsNumbers = Worksheets("Generator") Set wsArchive = Worksheets("Tiraž 2022") iGames = wsGame.Range("C1") 'količestvo tiraže iTickets = wsGame.Range("C2") 'količestvo ulaznica u svakom tiraže i = 5 'pervaâ stroka u tablici tablice.RowsG tabla. ("6:1048576"). Izbriši 'očišćaem stare podatke Za t = 1 Za iGames Za b = 1 za iTickets 'kopirajmo uigravšie brojeve sa liste Tiraži 2022 i stavimo na listu Igra wsArchive.Cells(t + 1, 1).Resize (1, 10).Copy Destination:=wsGame.Cells(i, 1) 'kopiramo i ustavimo posebnu ustavku sgeneriranih brojeva sa liste Generator wsNumbers.Range("G4:L4").Copy wsGame.Cells(i, 11) .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Sljedeći b Sljedeći t Kraj pod  

Ostaje unijeti željene početne parametre u žute ćelije i proći kroz makro Programer – Makroi (Programer — Makroi) ili prečica na tastaturi alt+F8.

Radi jasnoće, možete napraviti i dijagram za posljednju kolonu sa kumulativnim ukupnim iznosom, koji odražava promjenu bilansa novca tokom igre:

Poređenje različitih strategija

Sada, koristeći kreirani simulator, možete testirati bilo koju strategiju igre na pravim izvlačenjima u 2022. i vidjeti rezultate koje bi to donijelo. Ako igrate 1 tiket u svakom izvlačenju, onda ukupna slika "šljive" izgleda otprilike ovako:

Ovdje:

• generator je igra u kojoj u svakom izvlačenju biramo nasumične brojeve koje kreira naš generator (sa istom težinom).
• Favoriti je igra u kojoj u svakom izvlačenju koristimo iste brojeve – one koji su najčešće ispadali u izvlačenju 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 ispali (35, 5, 39, 11, 6, 29).

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

Također možete probati igrati veći broj ulaznica u svakom tiražu, da biste otvorili veći broj varijanti (inogda za nekoliko igrača uključenih u grupu).

Igranje u svakom izvlačenju sa jednim listićem sa nasumično generisanim brojevima (sa istom težinom):

Igranje 10 tiketa u svakom izvlačenju sa nasumično generisanim brojevima (sa istom težinom):

Igranje 100 tiketa u svakom izvlačenju sa slučajnim brojevima (sa istom težinom):

Komentari su, kako kažu, suvišni – odliv depozita je neizbežan u svakom slučaju 🙂