Lotto simulointi Excelissä

Lotto ei ole onnen metsästystä,

se on häviäjien metsästystä.

Kadehdittavalla säännöllisyydellä (ja viime aikoina useammin) ihmiset kirjoittavat minulle ja pyytävät apua erilaisiin arpajaisiin liittyviin laskelmiin. Joku haluaa toteuttaa salaisen algoritminsa voittonumeroiden valitsemiseksi Excelissä, joku haluaa löytää kuvioita aiemmista arvonnoista pudonneista numeroista, joku haluaa saada arpajaisten järjestäjät kiinni epärehelliseen peliin.

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

Tehtävä 1. Voiton todennäköisyys

Otetaan esimerkkinä klassinen Stoloto 6/45 lotto. Sääntöjen mukaan vain ne, jotka arvasivat kaikki 10 numeroa 6:sta, saavat superpalkinnon (45 miljoonaa ruplaa tai enemmän, jos palkintorahaston saldoa on kertynyt aikaisemmista arvonnoista). Jos arvasit 5, saat 150 tuhatta ruplaa, jos 4 - 1500 ruplaa. , jos 3 numeroa 6:sta, niin 150 ruplaa, jos 2 numeroa – palaat lippuun käytetyt 50 ruplaa. Arvaa vain yksi tai ei yhtään – hanki pelistä vain endorfiineja.

Matemaattinen voiton todennäköisyys on helppo laskea vakiofunktiolla NUMEROKOMB (YHDISTÄÄ), joka on saatavilla Microsoft Excelissä tällaista tapausta varten. Tämä funktio laskee N numeron yhdistelmien lukumäärän M:stä. Joten meidän "6/45" -arvontaan se olisi:

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

… joka on 8, kaikkien mahdollisten yhdistelmien kokonaismäärä tässä lotossa.

Jos haluat laskea osittaisen voiton todennäköisyyden (2-5 numeroa 6:sta), sinun on ensin laskettava tällaisten vaihtoehtojen lukumäärä, joka on yhtä suuri kuin arvattujen numeroiden yhdistelmien lukumäärä. 6 arvaamattomien numeroiden määrällä jäljellä olevista (45-6) = 39 numeroa. Sitten jaamme kaikkien mahdollisten yhdistelmien kokonaismäärän (8) kunkin vaihtoehdon saatujen voittojen määrällä – ja saamme kunkin tapauksen voittotodennäköisyydet:

Muuten, esimerkiksi todennäköisyydeksi kuolla lento-onnettomuudessa Maassamme on arvioitu noin yhdeksi miljoonasta. Ja todennäköisyys voittaa kasinolla ruletissa, panostamalla kaikki yhteen numeroon, on 1-1.

Jos kaikki yllä olevat eivät estäneet sinua ja olet edelleen valmis pelaamaan edelleen, jatka.

Tehtävä 2. Kunkin numeron esiintymistiheys

Aluksi selvitetään, millä taajuudella tietyt luvut putoavat. Ihanteellisessa arpajaisessa, kun analyysin aikaväli on riittävän pitkä, kaikilla palloilla pitäisi olla sama todennäköisyys olla voittavassa näytteessä. Todellisuudessa lottorummun suunnitteluominaisuudet ja pallojen painomuoto voivat vääristää tätä kuvaa, ja joidenkin pallojen putoamisen todennäköisyys voi hyvinkin olla suurempi/pienempi kuin toisilla. Testataan tätä hypoteesia käytännössä.

Otetaan esimerkiksi tiedot kaikista vuoden 2020 21 lottoarvonnosta, jotka tapahtuivat 6-45 niiden järjestäjän Stoloton verkkosivuilta, jotka on suunniteltu sellaisen "älykkään" taulukon muodossa, joka on kätevä analysoida ja jonka nimi on välilehtiArkisto Levikki. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Laske kunkin luvun esiintymistiheys käyttämällä funktiota LASKE.JOS (COUNTIF) ja lisää siihen funktio TEKSTI (TEKSTI)lisätäksesi etunollia ja tähtiä ennen ja jälkeen yksinumeroisiin lukuihin, jotta COUNTIF etsii luvun esiintymistä mistä tahansa yhdistelmästä sarakkeessa B. Selvyyden vuoksi rakennamme kaavion tulosten mukaan ja lajittelemme taajuudet laskevassa järjestyksessä:

Keskimäärin minkä tahansa pallon pitäisi pudota 1459 tasapeliä * 6 palloa / 45 numeroa = 194,53 kertaa (tätä kutsutaan tilastoissa juuri näin математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18 %), а нзноасно,. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие выпадающие выпадающие шары.

Tehtävä 3. Mitä lukuja ei ole piirretty pitkään aikaan?

Toinen strategia perustuu ajatukseen, että riittävän suurella arvonnalla ennen tai myöhemmin jokaisen numeron kaikista saatavilla olevista 1-45 pitäisi pudota. Joten jos jotkut numerot eivät ole näkyneet voittajien joukossa pitkään aikaan ("kylmät pallot"), on loogista yrittää panostaa niihin tulevaisuudessa. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за архив изунсульва за 2020-21 годыниюшие не выпадавшие номера LISÄTIETOJA (OTTELU). Se etsii ylhäältä alas (eli uusista ajoista vanhoihin ajoihin) etsiäkseen jokaista numeroa ja antaakseen sen ajon sarjanumeron (laskettuna vuoden lopusta alkuun), jossa tämä numero viimeksi pudotettiin:

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

Toinen pelistrategia perustuu psykologisen tekijän poistamiseen numeroiden arvailussa. Kun pelaaja valitsee numerot tekemällä panoksensa, hän ei tee tätä alitajuisesti täysin rationaalisesti. Tilastojen mukaan esimerkiksi numeroita 1-31 valitaan 70% useammin kuin muut (suosikkipäivämäärät), 13 valitaan harvemmin (helvetin tusina), "onnen" seitsemän sisältävät numerot valitaan useammin jne. Mutta pelaamme konetta (arparumpu) vastaan, jonka kaikki numerot ovat samat, joten on järkevää valita ne samalla matemaattisella puolueettomuudella mahdollisuutemme tasaamiseksi. Tätä varten meidän on luotava satunnaisten ja – mikä tärkeintä – ei-toistuvien numeroiden generaattori Exceliin:

Tehdä tämä:

1. Luodaan "älykäs" taulukko nimeltä pöytägeneraattori, jossa ensimmäinen sarake on meidän numeromme välillä 1-45.
2. Syötä toiseen sarakkeeseen kunkin numeron paino (tarvitsemme sitä hieman myöhemmin). Jos kaikki luvut ovat meille yhtä arvokkaita ja haluamme valita ne samalla todennäköisyydellä, niin paino voidaan asettaa 1:ksi kaikkialla.
3. Kolmannessa sarakkeessa käytämme funktiota SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) luodaan uusi 45 satunnaisluvun sarja ottaen huomioon kunkin painon.
4. Lisätään neljäs sarake, jossa käytetään funktiota RANK (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

Nyt on vielä valittava kuusi ensimmäistä numeroa arvon 6 mukaan funktion avulla LISÄTIETOJA (OTTELU):

При нажатии на клавишу F9 Excel-taulukon kaavat lasketaan uudelleen ja joka kerta saamme uuden joukon 6 numeroa vihreissä soluissa. Lisäksi luvut, joille on asetettu suurempi paino sarakkeessa B, saavat suhteellisesti korkeamman sijoituksen ja siten näkyvät useammin satunnaisotoksemme tuloksissa. Jos kaikkien numeroiden painoarvo on sama, ne kaikki valitaan samalla todennäköisyydellä. Näin saamme oikeudenmukaisen ja puolueettoman satunnaislukugeneraattorin 6/45, mutta jossa on mahdollisuus tarvittaessa säätää jakauman satunnaisuutta.

Jos päätämme pelata jokaisessa arvonnassa ei yhdellä, vaan esimerkiksi kahdella lipulla kerralla, joista kussakin valitsemme ei-toistuvia numeroita, voimme yksinkertaisesti lisätä ylimääräisiä rivejä alhaalta vihreälle alueelle, lisäämällä arvoon 6, 12, 18 jne. d. vastaavasti:

Tehtävä 5. Arpajaissimulaattori Excelissä

Koko tämän aiheen apoteoosiksi luodaan Exceliin täysimittainen arpajaissimulaattori, jossa voit kokeilla mitä tahansa strategioita ja vertailla tuloksia (optimointiteoriassa jotain vastaavaa kutsutaan myös Monte Carlo -menetelmäksi, mutta se on yksinkertaisempaa meille).

Jotta kaikki olisi mahdollisimman lähellä todellisuutta, kuvittele hetkeksi, että on 1. tammikuuta 2022 ja meillä on edessämme tämän vuoden arvonnat, joissa aiomme pelata. Syötin taulukkoon todelliset pudonneet luvut tablTiraži2022Erottelemalla lisäksi piirretyt luvut toisistaan ​​erillisiin sarakkeisiin myöhempien laskelmien helpottamiseksi:

Erillisellä arkilla Peli luo aihio mallintamista varten nimellä "älykäs" taulukko tabIgra seuraavalla lomakkeella:

Tässä:

• Yllä oleviin keltaisiin soluihin asetamme makrolle vuoden 2022 arvonnat, joihin haluamme osallistua (1-82) ja kussakin arvonnassa pelaavien lippujen lukumäärän.
• Makro kopioi 11 ensimmäisen sarakkeen tiedot (AJ) vuoden 2022 arvontataulukosta.
• Seuraavan kuuden sarakkeen (KP) tiedot makro ottaa taulukosta Generaattori, jossa olemme toteuttaneet satunnaislukugeneraattorin (katso tehtävä 4 yllä).
• Sarakkeessa Q lasketaan pudonneiden lukujen ja funktion avulla luotujen osumien lukumäärä SUMPRODUCT (SUMPRODUCT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если вы) за биралет, если вы.
• Viimeisessä sarakkeessa S pidämme koko pelin kokonaistulosta kumulatiivisena summana nähdäksemme prosessin dynamiikan.

Ja koko tämän rakenteen elvyttämiseksi tarvitsemme pienen makron. Välilehdellä kehittäjä (Kehittäjä) valitse joukkue Visual Basic tai käytä pikanäppäintä muut+F11. Lisää sitten uusi tyhjä moduuli valikon kautta Insert – moduuli ja syötä sinne seuraava koodi:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Setрантраs"s) SetИграs"sa =" Worksheetраs" wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576". (1, 1).Kopioi Destination:=wsGame.Cells(i, 2022) 'копируем и вставляем специальной вставкой значений сгенери значений сгенер(Coells"1"Lnpy). .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Seuraava b Seuraava t Lopeta ali  

Vielä on syötettävä halutut alkuparametrit keltaisiin soluihin ja suoritettava makro läpi Kehittäjä – makrot (Kehittäjä – makrot) tai pikanäppäimellä muut+F8.

Selvyyden vuoksi voit myös rakentaa kaavion viimeiselle sarakkeelle, jossa on kumulatiivinen kokonaissumma, joka kuvastaa rahasaldon muutosta pelin aikana:

Erilaisten strategioiden vertailu

Nyt luodun simulaattorin avulla voit testata mitä tahansa pelistrategiaa oikeilla arvonnoilla vuonna 2022 ja nähdä sen tuomat tulokset. Jos pelaat 1 lipun jokaisessa arvonnassa, kokonaiskuva "luumusta" näyttää tältä:

Tässä:

• Generaattori on peli, jossa jokaisessa arvonnassa valitsemme generaattorimme luomia satunnaislukuja (samalla painolla).
• Suosikit on peli, jossa käytämme jokaisessa arvonnassa samoja numeroita – niitä, jotka ovat useimmiten pudonneet arvonnassa viimeisen kahden vuoden aikana (27, 32, 11, 14, 34, 40).
• Outsiders – sama, mutta käytämme harvinaisimpia avattavia numeroita (12, 18, 26, 10, 21, 6).
• Kylmä – kaikissa arvonnoissa käytämme numeroita, jotka eivät ole pudonneet pitkään aikaan (35, 5, 39, 11, 6, 29).

Kuten näette, suurta eroa ei ole, mutta satunnaislukugeneraattori käyttäytyy hieman paremmin kuin muut "strategiat".

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

Pelaaminen jokaisessa arvonnassa yhdellä lipulla satunnaisesti luoduilla numeroilla (samalla painolla):

Pelaaminen 10 lipulla jokaisessa arvonnassa satunnaisesti luoduilla numeroilla (samalla painolla):

Pelaaminen 100 lipulla jokaisessa arvonnassa satunnaisilla numeroilla (samalla painolla):

Kommentit, kuten sanotaan, ovat turhia – panttivuoto on väistämätön kaikissa tapauksissa 🙂