Loterijas simulācija programmā Excel

Loterija nav veiksmes medības,

tās ir zaudētāju medības.

Ar apskaužamu regularitāti (un pēdējā laikā biežāk) man raksta, lūdzot palīdzību dažādos ar izlozēm saistītos aprēķinos. Kāds vēlas ieviest savu slepeno algoritmu laimestu skaitļu atlasei programmā Excel, kāds vēlas atrast modeļus skaitļos, kas izkrituši no iepriekšējām izlozēm, kāds vēlas pieķert loterijas rīkotājus negodīgā spēlē.

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

1. uzdevums. Uzvaras varbūtība

Kā piemēru ņemsim klasisko Stoloto 6 no 45 loteriju. Saskaņā ar noteikumiem tikai tie, kuri uzminēja visus 10 skaitļus no 6, saņem superbalvu (45 miljonus rubļu vai vairāk, ja balvu fonda atlikums ir uzkrājies no iepriekšējām izlozēm). Ja uzminējāt 5, saņemsiet 150 tūkstošus rubļu, ja 4 – 1500 rubļu. , ja 3 cipari no 6, tad 150 rubļi, ja 2 cipari – atdosi biļetei iztērētos 50 rubļus. Uzminiet tikai vienu vai nevienu — iegūstiet tikai endorfīnus no spēles procesa.

Matemātisko laimesta iespējamību var viegli aprēķināt, izmantojot standarta funkciju NUMBERCOMB (KOMBINĒT), kas šādam gadījumam ir pieejama programmā Microsoft Excel. Šī funkcija aprēķina N skaitļu kombināciju skaitu no M. Tātad mūsu loterijai “6 no 45” tas būtu:

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

…kas ir vienāds ar 8, visu iespējamo kombināciju kopskaitu šajā loterijā.

Ja vēlaties aprēķināt daļēja laimesta iespējamību (2-5 skaitļi no 6), tad vispirms būs jāaprēķina šādu opciju skaits, kas ir vienāds ar uzminēto skaitļu kombināciju skaita reizinājumu no 6 pēc neuzminēto skaitļu skaita no atlikušajiem (45-6) = 39 skaitļi. Pēc tam visu iespējamo kombināciju kopējo skaitu (8) sadalām ar katras opcijas saņemto uzvaru skaitu – un iegūstam katra gadījuma uzvaras varbūtības:

Starp citu, varbūtība, piemēram, iet bojā aviokatastrofā Mūsu valstī, tiek lēsta aptuveni 1 pret miljonu. Un iespējamība laimēt kazino ruletē, liekot visu uz vienu skaitli, ir 1 pret 37.

Ja viss iepriekš minētais jūs neapturēja un jūs joprojām esat gatavs spēlēt tālāk, turpiniet.

2. uzdevums. Katra skaitļa sastopamības biežums

Sākumā noteiksim, ar kādu frekvenci noteikti skaitļi izkrīt. Ideālā loterijā, ņemot vērā pietiekami lielu analīzes laika intervālu, visām bumbiņām ir jābūt vienādai iespējai būt uzvarētāju izlasē. Patiesībā loterijas bungas dizaina iezīmes un bumbiņu svara forma var izkropļot šo attēlu, un dažām bumbiņām izkrišanas varbūtība var būt lielāka/mazāka nekā citām. Pārbaudīsim šo hipotēzi praksē.

Ņemsim, piemēram, datus par visiem 2020. gadiem no 21 loterijas izlozes, kas notika 6.–45. gadā no to organizatora Stoloto vietnes, kas izveidota šāda “gudra” tabulas veidā, ērta analīzei, ar nosaukumu cilneArhīvs Tirāža. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Lai aprēķinātu katra skaitļa rašanās biežumu, izmantojiet funkciju COUNTIF (COUNTIF) un pievienojiet tam funkciju TEKSTS (TEKSTS)lai viencipara skaitļiem pievienotu sākuma nulles un zvaigznītes pirms un pēc, lai COUNTIF meklētu skaitļa sastopamību jebkurā kombinācijā B kolonnā. Turklāt lielākas skaidrības labad mēs izveidosim diagrammu pēc rezultātiem un sakārtosim frekvences dilstošā secībā:

Vidēji jebkurai bumbiņai jākrīt 1459 neizšķirti * 6 bumbiņas / 45 skaitļi = 194,53 reizes (tas ir tieši tas, ko sauc statistikā математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а мметно чаще (+10%), а нзноа, .21. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие выпадающие выпадающие шары.

Uzdevums 3. Kādi skaitļi nav izlozēti ilgu laiku?

Cita stratēģija ir balstīta uz domu, ka ar pietiekami lielu izložu skaitu agri vai vēlu katram skaitlim no visiem pieejamajiem no 1 līdz 45 vajadzētu izkrist. Tātad, ja daži skaitļi ilgu laiku nav parādījušies starp uzvarētājiem (“aukstās bumbas”), tad ir loģiski mēģināt likt uz tiem nākotnē. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за 2020-21-XNUMX гопадавшие VAIRĀK ATKLĀTA (MATCH). Tā veiks meklēšanu no augšas uz leju (ti, no jauniem uz veciem braucieniem), lai meklētu katru numuru un norādītu tā skrējiena sērijas numuru (skaitot no gada beigām līdz sākumam), kurā šis numurs pēdējo reizi tika izmests:

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

Vēl viena spēles stratēģija ir balstīta uz psiholoģiskā faktora novēršanu, minot skaitļus. Kad spēlētājs izvēlas skaitļus, veicot likmi, viņš zemapziņā to nedara gluži racionāli. Saskaņā ar statistiku, piemēram, skaitļi no 1 līdz 31 tiek izvēlēti par 70% biežāk nekā pārējie (mīļākie datumi), 13 tiek izvēlēti retāk (sasodīts ducis), biežāk tiek izvēlēti skaitļi, kas satur “laimīgo” septītnieku utt. Bet mēs spēlējam pret mašīnu (loterijas bungas), kurai visi skaitļi ir vienādi, tāpēc ir jēga tos izvēlēties ar tādu pašu matemātisku objektivitāti, lai izlīdzinātu mūsu izredzes. Lai to izdarītu, programmā Excel ir jāizveido nejaušu un, pats galvenais, neatkārtotu skaitļu ģenerators:

Lai to izdarītu:

1. Izveidosim “gudru” tabulu ar nosaukumu tabula Ģenerators, kur pirmajā kolonnā būs mūsu skaitļi no 1 līdz 45.
2. Otrajā kolonnā ievadiet katra skaitļa svaru (mums tas būs nepieciešams nedaudz vēlāk). Ja visi skaitļi mums ir vienlīdz vērtīgi un mēs vēlamies tos izvēlēties ar vienādu varbūtību, tad svaru visur var iestatīt vienādu ar 1.
3. Trešajā kolonnā mēs izmantojam funkciju SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) tiks ģenerēta jauna 45 nejaušu skaitļu kopa, ņemot vērā katra no tiem svaru.
4. Pievienosim ceturto kolonnu, kur izmantojot funkciju RANK (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

Tagad atliek atlasīt pirmos sešus skaitļus pēc 6. ranga, izmantojot funkciju VAIRĀK ATKLĀTA (MATCH):

При нажатии на клавишу F9 formulas Excel lapā tiks pārrēķinātas un katru reizi iegūsim jaunu 6 skaitļu kopu zaļajās šūnās. Turklāt skaitļi, kuriem B kolonnā ir noteikts lielāks svars, saņems proporcionāli augstāku rangu un tādējādi biežāk parādīsies mūsu nejaušās izlases rezultātos. Ja visu skaitļu svars ir iestatīts uz vienādu, tad tie visi tiks atlasīti ar vienādu varbūtību. Tādā veidā mēs iegūstam godīgu un objektīvu nejaušo skaitļu ģeneratoru ar 6 no 45, bet ar iespēju nepieciešamības gadījumā veikt sadalījuma nejaušības korekcijas.

Ja katrā izlozē nolemjam spēlēt nevis ar vienu, bet, piemēram, ar divām biļetēm uzreiz, katrā no kurām izvēlēsimies neatkārtojamus skaitļus, tad varam vienkārši pievienot papildu rindas no apakšas uz zaļo diapazonu, rangam pievienojot 6, 12, 18 utt. d. attiecīgi:

5. uzdevums. Loterijas simulators programmā Excel

Kā apoteozi visai šai tēmai izveidosim pilnvērtīgu loterijas simulatoru programmā Excel, kur var izmēģināt jebkuras stratēģijas un salīdzināt rezultātus (optimizācijas teorijā kaut ko līdzīgu sauc arī par Montekarlo metodi, bet tā būs vienkāršāk priekš mums).

Lai viss būtu pēc iespējas tuvāk realitātei, uz mirkli iedomājieties, ka ir 1. gada 2022. janvāris un mums priekšā šī gada izlozes, kurās plānojam spēlēt. Tabulā ievadīju reālos nomestos skaitļus tablTiraži2022, atdalot papildus izvilktos skaitļus vienu no otra atsevišķās kolonnās turpmāko aprēķinu ērtībai:

Uz atsevišķas lapas Spēle izveidojiet sagatavi modelēšanai "gudras" tabulas veidā ar nosaukumu tabIgra šāda forma:

Šeit:

• Augšpusē esošajās dzeltenajās šūnās makro iestatīsim izložu skaitu 2022. gadā, kurās vēlamies piedalīties (1-82) un katrā izlozē izspēlēto biļešu skaitu.
• Dati par pirmajām 11 kolonnām (AJ) tiks kopēti ar makro no 2022. gada izlozes lapas.
• Nākamo sešu kolonnu (KP) datus makro ņems no lapas Ģenerators, kur esam ieviesuši nejaušo skaitļu ģeneratoru (skat. 4. uzdevumu iepriekš).
• Kolonnā Q mēs uzskaitām atbilstības skaitu starp nomestajiem skaitļiem un tiem, kas ģenerēti, izmantojot funkciju SUMPRODUCT (SUMPRODUCT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если вы за биналет, если вы)
• Pēdējā kolonnā S mēs uzskatām visas spēles kopējo rezultātu kā kumulatīvo kopsummu, lai redzētu procesa dinamiku.

Un, lai atdzīvinātu visu šo struktūru, mums ir nepieciešams neliels makro. Uz cilnes attīstītājs (Izstrādātājs) izvēlēties komandu Visual Basic vai izmantojiet īsinājumtaustiņus cits+F11. Pēc tam izvēlnē pievienojiet jaunu tukšu moduli Ievietot - modulis un ievadiet tur šādu kodu:

Apakšloterija() Dim iGames kā vesels skaitlis, iBiļetes kā vesels skaitlis, iBiļetes kā vesels skaitlis, i tik garš, t kā vesels skaitlis, b kā vesels skaitlis 'объявляем переменные для ссылки на листы = ws. wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576"). Dzēst 'ччищем старые даные t = 1 līdz igames for b = 1 uz Itickets' копируем выигравшие номе с с л л + + л л л л л л л л л л л л л л л л л л л л л л л л л л л л л (2022, 1).Kopēt Destination:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенери значений сгенери (Copy,1 .PasteSpecial Paste:=xlPasteValues ​​i = i + 10 Nākamais b Nākamais t Beigas apakš.  

Atliek dzeltenajās šūnās ievadīt vēlamos sākotnējos parametrus un palaist makro cauri Izstrādātājs – Makro (Izstrādātājs — makro) vai īsinājumtaustiņš cits+F8.

Skaidrības labad varat arī izveidot diagrammu pēdējai kolonnai ar kumulatīvo kopsummu, kas atspoguļo naudas atlikuma izmaiņas spēles laikā:

Dažādu stratēģiju salīdzinājums

Tagad, izmantojot izveidoto simulatoru, jūs varat pārbaudīt jebkuru spēles stratēģiju uz reālām izlozēm 2022. gadā un redzēt rezultātus, ko tas nestu. Ja katrā izlozē izspēlē 1 biļeti, tad “plūmes” kopējais attēls izskatās apmēram šādi:

Šeit:

• Ģenerators ir spēle, kurā katrā izlozē mēs izvēlamies nejaušus skaitļus, ko izveidojis mūsu ģenerators (ar vienādu svaru).
• Izlase ir spēle, kurā katrā izlozē izmantojam vienus un tos pašus skaitļus – tos, kuri pēdējo divu gadu laikā visbiežāk izkrituši neizšķirtos (27, 32, 11, 14, 34, 40).
• Nepiederošas – tas pats, bet mēs izmantojam visretākos nolaižamos skaitļus (12, 18, 26, 10, 21, 6).
• vēsa – visās izlozēs izmantojam skaitļus, kas sen nav izkrituši (35, 5, 39, 11, 6, 29).

Kā redzat, lielas atšķirības nav, taču nejaušo skaitļu ģenerators darbojas nedaudz labāk nekā citas "stratēģijas".

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

Spēlējot katrā izlozē ar vienu biļeti ar nejauši ģenerētiem cipariem (ar vienādu svaru):

Izspēlējot 10 biļetes katrā izlozē ar nejauši ģenerētiem cipariem (ar vienādu svaru):

Izspēlējot 100 biļetes katrā izlozē ar nejaušiem skaitļiem (ar vienādu svaru):

Komentāri, kā saka, lieki – depozīta aizplūšana ir neizbēgama visos gadījumos 🙂