Lotery simulasie in Excel

Die lotery is nie 'n jag na geluk nie,

dit is 'n jag vir verloorders.

Met benydenswaardige reëlmaat (en meer dikwels die afgelope tyd) skryf mense vir my om hulp te vra in verskeie berekeninge wat met loterye verband hou. Iemand wil hul geheime algoritme implementeer om wennommers in Excel te kies, iemand wil patrone vind in die getalle wat uit vorige trekkings geval het, iemand wil die organiseerders van die lotery in 'n oneerlike speletjie vang.

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

Taak 1. Waarskynlikheid om te wen

Kom ons neem die klassieke Stoloto 6 uit 45 lotery as 'n voorbeeld. Volgens die reëls ontvang slegs diegene wat al 10 nommers uit 6 geraai het 'n superprys (45 miljoen roebels of meer as die balans van die prysfonds van vorige trekkings opgehoop het). As jy 5 geraai het, sal jy 150 duisend roebels ontvang, as 4 - 1500 roebels. , as 3 nommers uit 6, dan 150 roebels, indien 2 nommers - jy sal 50 roebels wat op die kaartjie spandeer is, teruggee. Raai net een of geen - kry net endorfiene uit die spelproses.

Die wiskundige waarskynlikheid om te wen kan maklik met behulp van die standaardfunksie bereken word NOMMERKOMB (KOMBINEER), wat beskikbaar is in Microsoft Excel vir so 'n geval. Hierdie funksie bereken die aantal kombinasies van N getalle uit M. So vir ons "6 uit 45" lotery sou dit wees:

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

… wat gelyk is aan 8, die totale aantal van alle moontlike kombinasies in hierdie lotery.

As jy die waarskynlikheid vir 'n gedeeltelike oorwinning (2-5 getalle uit 6) wil bereken, moet jy eers die aantal sulke opsies bereken, wat gelyk is aan die produk van die aantal kombinasies van geraaide getalle uit 6 deur die aantal ongeraaide getalle uit die oorblywende (45-6) = 39 getalle. Dan deel ons die totale aantal van alle moontlike kombinasies (8) deur die aantal oorwinnings wat vir elke opsie ontvang is – en ons kry die wenwaarskynlikhede vir elke geval:

Terloops, die waarskynlikheid om byvoorbeeld in 'n vliegtuigongeluk in Ons Land te sterf, word op ongeveer 1 uit 'n miljoen geraam. En die waarskynlikheid om te wen in 'n casino by roulette, wed alles op een nommer is 1 tot 37.

As al die bogenoemde jou nie gekeer het nie en jy is steeds gereed om verder te speel, gaan voort.

Taak 2. Frekwensie van voorkoms van elke nommer

Om mee te begin, kom ons bepaal met watter frekwensie sekere getalle uitval. In 'n ideale lotery, gegewe 'n voldoende groot tydsinterval vir analise, moet alle balle dieselfde waarskynlikheid hê om in die wenmonster te wees. In werklikheid kan die ontwerpkenmerke van die lottodrom en die gewig-vorm van die balle hierdie prentjie verdraai, en vir sommige balle kan die waarskynlikheid om uit te val heel moontlik hoër/laer wees as vir ander. Kom ons toets hierdie hipotese in die praktyk.

Kom ons neem byvoorbeeld data oor al 2020 uit 21 loterytrekkings wat in 6-45 plaasgevind het vanaf die webwerf van hul organiseerder Stoloto, ontwerp in die vorm van so 'n "slim" tafel, gerieflik vir ontleding, met die naam tabArgief-sirkulasie. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная vir начала выборка vir анализа:

Gebruik die funksie om die frekwensie van voorkoms van elke getal te bereken COUNTIF (COUNTIF) en voeg 'n funksie daarby TEKS (TEKS)om voorste nulle en sterretjies voor en na by enkelsyfergetalle te voeg, sodat COUNTIF soek na die voorkoms van 'n getal op enige plek in die kombinasie in kolom B. Ook, vir groter duidelikheid, sal ons 'n grafiek volgens resultate bou en die frekwensies sorteer in dalende volgorde:

Gemiddeld moet enige bal 1459 trekkings val * 6 balle / 45 nommers = 194,53 keer (dit is presies wat in die statistieke genoem word математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые (10, 21, 6…) наоборот заметно реже (-15%), чем основная масса. Соответственно, можно попробовать использовать эту и inligting vir стратегии выигрыша, т.е. либо ставить на т шары, что выадают чаще, либо наоборот - де с т ыдддддддддддддддддддддддддддддддддддддддддддддддддддддддддддддддддedige нддддддддддддддддддддддддддддддддддддддддддд dryf

Taak 3. Watter getalle is lanklaas getrek?

Nog 'n strategie is gebaseer op die idee dat met 'n voldoende groot aantal trekkings, vroeër of later elke nommer van alle beskikbare van 1 tot 45 moet uitval. So as sommige nommers lanklaas onder die wenners verskyn het (“koue balle”), dan is dit logies om in die toekoms daarop te probeer wed. 

Meer inligting is nie beskikbaar nie. MEER BLOOTSTELLING (MATCH). Dit sal van bo na onder soek (dws van nuwe na ou lopies) om vir elke nommer te soek en die reeksnommer van die lopie uit te gee (tel vanaf die einde van die jaar tot die begin) waar hierdie nommer laas laat val is:

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

Nog 'n speletjiestrategie is gebaseer op die uitskakeling van die sielkundige faktor wanneer getalle geraai word. Wanneer 'n speler nommers kies deur sy weddenskap te maak, doen hy dit onbewustelik nie heeltemal rasioneel nie. Volgens statistieke word nommers van 1 tot 31 byvoorbeeld 70% meer dikwels as die res gekies (gunsteling datums), 13 word minder dikwels gekies (verdomde dosyn), nommers wat die "gelukkige" sewe bevat word meer dikwels gekies, ens. Maar ons speel teen 'n masjien (lotto-trom) waarvoor alle getalle dieselfde is, so dit maak sin om hulle met dieselfde wiskundige onpartydigheid te kies om ons kanse gelyk te maak. Om dit te doen, moet ons 'n genereerder van ewekansige en – bowenal – nie-herhalende getalle in Excel skep:

Om dit te doen:

1. Kom ons skep 'n "slim" tabel met die naam tafelGenerator, waar die eerste kolom ons getalle van 1 tot 45 sal wees.
2. In die tweede kolom, voer die gewig vir elke nommer in (ons sal dit 'n bietjie later nodig hê). As alle getalle vir ons ewe waardevol is en ons wil hulle met gelyke waarskynlikheid kies, dan kan die gewig oral gelyk aan 1 gestel word.
3. In die derde kolom gebruik ons ​​die funksie SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 tot 1, добавив к нему вес из предыдущего столбц. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) ’n nuwe stel van 45 ewekansige getalle sal gegenereer word, met inagneming van die gewig vir elk van hulle.
4. Kom ons voeg 'n vierde kolom by, waar die funksie gebruik word RANK (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

Nou bly dit om 'n seleksie van die eerste ses getalle te maak deur rang 6 met behulp van die funksie MEER BLOOTSTELLING (MATCH):

При нажатии на клавишу F9 die formules op die Excel-blad sal herbereken word en elke keer sal ons 'n nuwe stel van 6 getalle in groen selle kry. Boonop sal die getalle waarvoor 'n groter gewig in kolom B gestel is, 'n proporsionele hoër rangorde kry en dus meer dikwels in die resultate van ons ewekansige steekproef verskyn. As die gewig vir alle getalle op dieselfde gestel is, sal almal met dieselfde waarskynlikheid gekies word. Op hierdie manier kry ons 'n regverdige en onbevooroordeelde ewekansige getalgenerator van 6 uit 45, maar met die vermoë om aanpassings aan die ewekansigheid van die verspreiding te maak indien nodig.

As ons besluit om in elke trekking te speel, nie met een nie, maar byvoorbeeld met twee kaartjies gelyktydig, in elkeen waarvan ons nie-herhalende nommers sal kies, dan kan ons eenvoudig addisionele reëls van onder af by die groen reeks voeg, 6, 12, 18, ens. by die rangorde te voeg. d. onderskeidelik:

Taak 5. Lotery Simulator in Excel

As 'n apoteose van hierdie hele onderwerp, kom ons skep 'n volwaardige lotery-simulator in Excel, waar u enige strategieë kan probeer en die resultate kan vergelyk (in optimaliseringsteorie word iets soortgelyks ook die Monte Carlo-metode genoem, maar dit sal eenvoudiger wees vir ons).

Om alles so na as moontlik aan die werklikheid te maak, verbeel jou vir 'n oomblik dat dit 1 Januarie 2022 is en ons het vanjaar se trekkings voor ons, waarin ons beplan om te speel. Ek het die werklike gedaalde getalle in die tabel ingevoer tablTiraži2022, die bykomende getekende getalle van mekaar te skei in aparte kolomme vir die gerief van daaropvolgende berekeninge:

Op 'n aparte blad Game skep 'n spasie vir modellering in die vorm van 'n "slim" tabel met die naam tabIgra die volgende vorm:

hier:

• In die geel selle hierbo sal ons vir die makro die aantal trekkings in 2022 stel waaraan ons wil deelneem (1-82) en die aantal kaartjies wat ons in elke trekking speel.
• Die data vir die eerste 11 kolomme (AJ) sal deur die makro vanaf die 2022-tekenblad gekopieer word.
• Data vir die volgende ses kolomme (KP) wat die makro van die blad af sal neem Generator, waar ons 'n ewekansige getalgenerator geïmplementeer het (sien probleem 4 hierbo).
• In kolom Q tel ons die aantal passings tussen die gedaalde getalle en dié wat met die funksie gegenereer is SUMPRODUCT (SOMPRODUK).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, есливилтат, исли выграли, то минус 50 рублей за билет, есливилтат, сливиграли, с XNUMX
• In die laaste kolom S beskou ons die algehele resultaat van die hele wedstryd as 'n kumulatiewe totaal om die dinamika in die proses te sien.

En om hierdie hele struktuur te laat herleef, het ons 'n klein makro nodig. Op die blad ontwikkelaar (Ontwikkelaar) kies 'n span Visual Basic of gebruik sleutelbordkortpaaie alt+F11. Voeg dan 'n nuwe leë module by via die spyskaart Voeg in – Module en voer die volgende kode daar in:

Sub Lottery() Dim iGames As Heelgetal, iTickets As Heelgetal, i Solank, t As Heelgetal, b As Heelgetal 'объявляем переменные для ссылки на листы Stel wsGame = Worksheets("Иенгра") Stel wsheetnommers("") = Генра") wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6:1048576"). Verwyder 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тира2022жиг 1s. (1, 1). Kopieer bestemming:=wsGame.Cells(i, 10) 'копируем и вставляем специальной вставкой значений сгенерированные номират с.1Ge. .PasteSpecial Paste:=xlPasteWaardes i = i + 4 Volgende b Volgende t Einde Sub  

Dit bly om die gewenste aanvanklike parameters in die geel selle in te voer en die makro deur te voer Ontwikkelaar - Makro's (Ontwikkelaar - Makro's) of sleutelbordkortpad alt+F8.

Vir duidelikheid kan jy ook 'n diagram vir die laaste kolom bou met 'n kumulatiewe totaal, wat die verandering in die geldsaldo tydens die speletjie weerspieël:

Vergelyking van verskillende strategieë

Nou, met behulp van die geskepte simulator, kan u enige speletjiestrategie op regte trekkings in 2022 toets en die resultate sien wat dit sou oplewer. As jy 1 kaartjie in elke trekking speel, dan lyk die algehele prentjie van die "pruim" so iets:

hier:

• Generator is 'n speletjie waar ons in elke trekking ewekansige getalle kies wat deur ons kragopwekker geskep is (met dieselfde gewig).
• Gunstelinge is 'n speletjie waar ons in elke trekking dieselfde nommers gebruik – dié wat die afgelope twee jaar die meeste in trekkings uitgeval het (27, 32, 11, 14, 34, 40).
• Buitestaanders – dieselfde, maar ons gebruik die skaarsste aftreknommers (12, 18, 26, 10, 21, 6).
• Koue – in alle trekkings gebruik ons ​​getalle wat lanklaas uitgeval het (35, 5, 39, 11, 6, 29).

Soos u kan sien, is daar geen groot verskil nie, maar die ewekansige getalgenerator tree 'n bietjie beter op as die ander "strategieë".

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

Speel in elke trekking met een kaartjie met ewekansige gegenereerde nommers (met dieselfde gewig):

Speel 10 kaartjies in elke trekking met ewekansige gegenereerde nommers (met dieselfde gewig):

Speel 100 kaartjies in elke trekking met ewekansige nommers (met dieselfde gewig):

Opmerkings, soos hulle sê, is oorbodig – 'n deposito-dreinering is in alle gevalle onvermydelik 🙂