Loterijos modeliavimas Excel

Loterija nėra sėkmės medžioklė,

tai nevykėlių medžioklė.

Su pavydėtinu reguliarumu (ir pastaruoju metu dažniau) man rašo žmonės, prašydami padėti įvairiuose su loterijomis susijusiuose skaičiavimuose. Kažkas nori įdiegti savo slaptą algoritmą laimėjusiems skaičiams pasirinkti programoje „Excel“, kažkas nori rasti skaičių modelius, kurie iškrito iš ankstesnių traukimų, kažkas nori sugauti loterijos organizatorius nesąžiningame žaidime.

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

Užduotis 1. Tikimybė laimėti

Kaip pavyzdį paimkime klasikinę „Stoloto 6 iš 45“ loteriją. Pagal taisykles tik tie, kurie atspėjo visus 10 skaičių iš 6, gauna super prizą (45 mln. rublių ar daugiau, jei prizinio fondo likutis susikaupė iš ankstesnių traukimų). Jei atspėjote 5, gausite 150 tūkstančių rublių, jei 4 – 1500 rublių. , jei 3 skaičiai iš 6, tai 150 rublių, jei 2 skaičiai – grąžinsite bilietui išleistus 50 rublių. Atspėk tik vieną arba nė vieno – gaukite tik endorfinus iš žaidimo proceso.

Matematinę laimėjimo tikimybę galima lengvai apskaičiuoti naudojant standartinę funkciją SKAIČIUS KOMB (SUJUNGTI), kuri tokiam atvejui yra „Microsoft Excel“. Ši funkcija apskaičiuoja N skaičių derinių skaičių iš M. Taigi mūsų loterijoje „6 iš 45“ būtų:

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

… tai yra 8, bendras visų galimų šios loterijos kombinacijų skaičius.

Jei norite apskaičiuoti dalinio laimėjimo tikimybę (2-5 skaičiai iš 6), pirmiausia turėsite apskaičiuoti tokių variantų skaičių, kuris yra lygus atspėtų skaičių derinių skaičiaus sandaugai 6 pagal neatspėtų skaičių skaičių iš likusių (45-6) = 39 skaičiai. Tada bendrą visų galimų kombinacijų skaičių (8) padaliname iš gauto kiekvieno varianto laimėjimų skaičiaus – ir gauname kiekvieno atvejo laimėjimo tikimybes:

Beje, tikimybė, pavyzdžiui, žūti lėktuvo katastrofoje Mūsų šalyje, įvertinta maždaug 1 iš milijono. O tikimybė laimėti kazino ruletėje, statant viską ant vieno skaičiaus, yra nuo 1 iki 37.

Jei visa tai, kas išdėstyta aukščiau, jūsų nesustabdė ir vis dar esate pasirengęs žaisti toliau, tęskite.

2 užduotis. Kiekvieno skaičiaus atsiradimo dažnis

Pirmiausia nustatykime, kokiu dažniu tam tikri skaičiai iškrenta. Idealioje loterijoje, atsižvelgiant į pakankamai ilgą laiko intervalą analizei, visi rutuliukai turėtų turėti vienodą tikimybę, kad bus laimėjusiame imtyje. Tiesą sakant, loterijos būgno dizaino ypatybės ir kamuoliukų svorio forma gali iškreipti šį vaizdą, o kai kurių kamuoliukų iškritimo tikimybė gali būti didesnė / mažesnė nei kitų. Išbandykime šią hipotezę praktiškai.

Paimkime, pavyzdžiui, duomenis apie visus 2020 m. iš 21 loterijos, įvykusios 6–45 m., iš jų organizatoriaus Stoloto svetainės, sukurtos tokios „protingos“ lentelės pavidalu, patogios analizei, su pavadinimu. tabArchyvas Tiražas. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Norėdami apskaičiuoti kiekvieno skaičiaus pasireiškimo dažnį, naudokite funkciją COUNTIF (COUNTIF) ir pridėkite prie jo funkciją TEKSTAS (TEKSTAS)prie vienaženklių skaičių pridėti priekinius nulius ir žvaigždutes, kad COUNTIF ieškotų skaičiaus atsiradimo bet kurioje B stulpelio derinio vietoje. Be to, siekiant didesnio aiškumo, sudarysime diagramą pagal rezultatus ir surūšiuosime dažnius mažėjančia tvarka:

Vidutiniškai bet kuris kamuoliukas turėtų nukristi 1459 lygiosios * 6 kamuoliukai / 45 skaičiai = 194,53 karto (būtent taip vadinama statistika математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а нзноасно, 10.мосно, 21, ебекоторые (6, ебекоторые), орео15… XNUMX Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие выпадающие шары.

Užduotis 3. Kokie skaičiai nebuvo brėžiami ilgą laiką?

Kita strategija paremta idėja, kad esant pakankamai dideliam burtų skaičiui, anksčiau ar vėliau kiekvienas skaičius iš visų turimų nuo 1 iki 45 turėtų iškristi. Taigi, jei kai kurie skaičiai ilgą laiką nepasirodė tarp laimėtojų („šalti kamuoliai“), logiška ateityje bandyti dėl jų statyti. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей пуюдынию за 2020-21 m. DAUGIAU ATSKIRTA (MATCH). Ji ieškos iš viršaus į apačią (ty nuo naujų iki senų), kad ieškos kiekvieno numerio ir pateiks bėgimo serijos numerį (skaičiuojant nuo metų pabaigos iki pradžios), kuriame šis skaičius buvo paskutinį kartą išmestas:

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

Kita žaidimo strategija paremta psichologinio faktoriaus pašalinimu spėjant skaičius. Kai žaidėjas pasirenka skaičius atlikdamas statymą, jis nesąmoningai tai daro ne visai racionaliai. Pagal statistiką, pavyzdžiui, skaičiai nuo 1 iki 31 pasirenkami 70% dažniau nei kiti (mėgstamiausios datos), 13 pasirenkami rečiau (velniškai tuzinas), dažniau pasirenkami skaičiai, kuriuose yra „laimingasis“ septynetas ir pan. Bet mes žaidžiame prieš mašiną (loterijos būgną), kuriai visi skaičiai yra vienodi, todėl tikslinga juos pasirinkti tokiu pat matematiniu nešališkumu, kad išlygintume savo galimybes. Norėdami tai padaryti, „Excel“ turime sukurti atsitiktinių ir, svarbiausia, nesikartojančių skaičių generatorių:

Padaryti tai:

1. Sukurkime „protingą“ lentelę pavadinimu stalo generatorius, kur pirmasis stulpelis bus mūsų skaičiai nuo 1 iki 45.
2. Antrame stulpelyje įveskite kiekvieno skaičiaus svorį (jo mums prireiks šiek tiek vėliau). Jei visi skaičiai mums vienodai vertingi ir norime juos pasirinkti vienoda tikimybe, tai visur galima nustatyti svorį lygų 1.
3. Trečiame stulpelyje naudojame funkciją SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) bus sugeneruotas naujas 45 atsitiktinių skaičių rinkinys, atsižvelgiant į kiekvieno iš jų svorį.
4. Pridėkime ketvirtą stulpelį, kur naudodami funkciją RANK (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

Dabar belieka pasirinkti pirmuosius šešis skaičius pagal 6 rangą naudojant funkciją DAUGIAU ATSKIRTA (MATCH):

При нажатии на клавишу F9 formulės Excel lape bus perskaičiuotos ir kiekvieną kartą gausime naują 6 skaičių rinkinį žaliuose langeliuose. Be to, skaičiai, kuriems B stulpelyje buvo nustatytas didesnis svoris, gaus proporcingai aukštesnį reitingą ir todėl dažniau atsiras mūsų atsitiktinės imties rezultatuose. Jei visų skaičių svoris yra vienodas, tada visi jie bus pasirinkti su ta pačia tikimybe. Tokiu būdu gauname teisingą ir nešališką atsitiktinių skaičių generatorių 6 iš 45, bet su galimybe prireikus pakoreguoti pasiskirstymo atsitiktinumą.

Jei nuspręsime kiekvienoje burtų traukoje žaisti ne su vienu, o, pavyzdžiui, su dviem bilietais iš karto, kiekviename iš kurių pasirinksime nesikartojančius skaičius, tada galime tiesiog pridėti papildomų eilučių iš apačios į žalią diapazoną, prie rango pridėjus 6, 12, 18 ir kt. d. atitinkamai:

5 užduotis. Loterijos simuliatorius programoje Excel

Kaip visos šios temos apoteozę, sukurkime pilnavertį loterijos simuliatorių Excel programoje, kuriame galėsite išbandyti bet kokias strategijas ir palyginti rezultatus (optimizavimo teorijoje kažkas panašaus dar vadinamas Monte Karlo metodu, bet bus paprasčiau mums).

Kad viskas būtų kuo arčiau realybės, akimirkai įsivaizduokite, kad yra 1 m. sausio 2022 d. ir mūsų laukia šių metų burtai, kuriuose planuojame žaisti. Lentelėje įvedžiau tikrus nukritusius skaičius tablTiraži2022, atskiriant papildomai nubrėžtus skaičius vienas nuo kito į atskirus stulpelius, kad būtų patogiau atlikti tolesnius skaičiavimus:

Ant atskiro lapo žaidimas sukurkite ruošinį modeliavimui „protingos“ lentelės su pavadinimu pavidalu tabIgra tokia forma:

Čia:

• Aukščiau esančiuose geltonuosiuose langeliuose makrokomandai nustatysime traukimų skaičių 2022 m., kuriuose norime dalyvauti (1-82) ir bilietų skaičių, kuriuos žaisime kiekviename burtų traukime.
• Pirmųjų 11 stulpelių (AJ) duomenys bus nukopijuoti makrokomandos iš 2022 m. piešimo lapo.
• Kitų šešių stulpelių (KP) duomenis makrokomanda paims iš lapo Generatorius, kur įdiegėme atsitiktinių skaičių generatorių (žr. 4 uždavinį aukščiau).
• Q stulpelyje skaičiuojame atitikčių skaičių tarp iškritusių skaičių ir sugeneruotų naudojant funkciją SUMPRODUCT (SUMPRODUCT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если вы за бинансовый результат, если вы
• Paskutiniame S stulpelyje mes laikome bendrą viso žaidimo rezultatą kaip bendrą sumą, kad pamatytume proceso dinamiką.

Ir norint atgaivinti visą šią struktūrą, mums reikia mažos makrokomandos. Skirtuke ryškalas (Programuotojas) pasirinkti komandą "Visual Basic" arba naudokite sparčiuosius klavišus Kitas+F11. Tada per meniu pridėkite naują tuščią modulį Įterpimas – modulis ir ten įveskite šį kodą:

Sub Loterija() Dim iGames kaip sveikasis skaičius, iBilietai kaip sveikasis skaičius, i kaip ilgas, t kaip sveikasis skaičius, b kaip sveikasis skaičius 'объявляем переменные для ссылки на листы ссылки на листы Setрантраs"se) Set. wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ) (6, 1048576).Kopijuoti paskirties vietą:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенер значений сгенер 1. .PasteSpecial Paste:=xlPasteValues ​​i = i + 2022 Kitas b Kitas t Pabaiga Sub  

Belieka įvesti norimus pradinius parametrus į geltonus langelius ir paleisti makrokomandą Kūrėjas – makrokomandos (Kūrėjas – makrokomandos) arba spartusis klavišas Kitas+F8.

Aiškumo dėlei taip pat galite sukurti paskutinio stulpelio diagramą su kaupiamąja suma, atspindinčia pinigų likučio pasikeitimą žaidimo metu:

Įvairių strategijų palyginimas

Dabar, naudodamiesi sukurtu treniruokliu, galite išbandyti bet kurią žaidimo strategiją realiose traukimuose 2022 m. ir pamatyti jos rezultatus. Jei lošiate po 1 bilietą kiekvienoje loterijoje, tada bendras „slyvos“ vaizdas atrodo maždaug taip:

Čia:

• Generatorius yra žaidimas, kuriame kiekvienoje burtų traukoje pasirenkame mūsų generatoriaus sukurtus atsitiktinius skaičius (su vienodu svoriu).
• Mėgstamiausi yra žaidimas, kuriame kiekviename burtų traukime naudojame tuos pačius skaičius – tuos, kurie dažniausiai iškrito per pastaruosius dvejus metus (27, 32, 11, 14, 34, 40).
• Pašaliečių – tas pats, bet naudojame rečiausius išskleidžiamuosius skaičius (12, 18, 26, 10, 21, 6).
• Šalta – visuose burtuose naudojame seniai neiškritusius skaičius (35, 5, 39, 11, 6, 29).

Kaip matote, didelio skirtumo nėra, tačiau atsitiktinių skaičių generatorius veikia šiek tiek geriau nei kitos „strategijos“.

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

Žaisti kiekviename burtų traukime su vienu bilietu su atsitiktinai sugeneruotais skaičiais (su tokiu pačiu svoriu):

Žaisti po 10 bilietų kiekviename burtų traukime su atsitiktinai sugeneruotais skaičiais (su tokiu pačiu svoriu):

Žaisti po 100 bilietų kiekviename burtų traukime su atsitiktiniais skaičiais (to paties svorio):

Komentarai, kaip sakoma, pertekliniai – depozito nutekėjimas visais atvejais neišvengiamas 🙂