Simulácia lotérie v Exceli

Lotéria nie je honba za šťastím,

je to hon na porazených.

So závideniahodnou pravidelnosťou (a v poslednej dobe čoraz častejšie) mi ľudia píšu o pomoc pri rôznych výpočtoch súvisiacich s lotériami. Niekto chce implementovať svoj tajný algoritmus na výber výherných čísel v Exceli, niekto chce nájsť vzory v číslach, ktoré vypadli z minulých žrebovaní, niekto chce prichytiť organizátorov lotérie v nečestnej hre.

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

Úloha 1. Pravdepodobnosť výhry

Zoberme si ako príklad klasickú lotériu Stoloto 6 zo 45. Podľa pravidiel získajú super cenu iba tí, ktorí uhádli všetkých 10 čísel zo 6 (45 miliónov rubľov alebo viac, ak sa zostatok výherného fondu nahromadil z predchádzajúcich žrebovaní). Ak ste uhádli 5, dostanete 150 tisíc rubľov, ak 4 – 1500 rubľov. , ak 3 čísla zo 6, tak 150 rubľov, ak 2 čísla – vrátiš 50 rubľov vynaložených na tiket. Hádaj len jeden alebo žiadny – získaj z herného procesu iba endorfíny.

Matematická pravdepodobnosť výhry sa dá jednoducho vypočítať pomocou štandardnej funkcie NUMBERCOMB (KOMBINOVAŤ), ktorý je pre takýto prípad dostupný v programe Microsoft Excel. Táto funkcia vypočíta počet kombinácií N čísel z M. Takže pre našu lotériu „6 zo 45“ by to bolo:

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

… čo sa rovná 8, celkový počet všetkých možných kombinácií v tejto lotérii.

Ak chcete vypočítať pravdepodobnosť čiastočnej výhry (2-5 čísel zo 6), musíte najskôr vypočítať počet takýchto možností, ktorý sa rovná súčinu počtu kombinácií uhádnutých čísel z 6 podľa počtu neuhádnutých čísel zo zostávajúcich (45-6) = 39 čísel. Potom vydelíme celkový počet všetkých možných kombinácií (8) získaným počtom výhier pre každú možnosť – a získame pravdepodobnosť výhry pre každý prípad:

Mimochodom, pravdepodobnosť napríklad úmrtia pri leteckom nešťastí v našej krajine sa odhaduje asi na 1 ku miliónu. A pravdepodobnosť výhry v kasíne v rulete, stávkovanie všetkého na jedno číslo, je 1 až 37.

Ak vás všetko vyššie uvedené nezastavilo a stále ste pripravení hrať ďalej, pokračujte.

Úloha 2. Frekvencia výskytu každého čísla

Na začiatok si určme, s akou frekvenciou vypadávajú určité čísla. V ideálnej lotérii by pri dostatočne veľkom časovom intervale na analýzu mali mať všetky loptičky rovnakú pravdepodobnosť, že budú vo výhernej vzorke. V skutočnosti môžu konštrukčné prvky lotériového bubna a tvar hmotnosti loptičiek skresliť tento obraz a pri niektorých loptách môže byť pravdepodobnosť vypadnutia vyššia/nižšia ako pri iných. Otestujme si túto hypotézu v praxi.

Vezmime si napríklad údaje o všetkých 2020 z 21 žrebovaní lotérií, ktoré sa uskutočnili v rokoch 6-45 z webovej stránky ich organizátora Stoloto, navrhnuté vo forme takejto „inteligentnej“ tabuľky vhodnej na analýzu s názvom tabArchív Výbeh. Розыгрыши проходят два раза в день (v 11 утра и в 11 вечера), т.е. v этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная даля нарбалала

Na výpočet frekvencie výskytu každého čísla použite funkciu COUNTIF (COUNTIF) a pridajte k nemu funkciu TEXT (TEXT)pridať pred a za jednociferné čísla úvodné nuly a hviezdičky, aby COUNTIF hľadal výskyt čísla kdekoľvek v kombinácii v stĺpci B. Pre väčšiu prehľadnosť tiež zostavíme tabuľku podľa výsledkov a zoradíme frekvencie v zostupnom poradí:

Každá loptička by mala v priemere padnúť 1459 žrebov * 6 loptičiek / 45 čísel = 194,53 krát (presne sa tak hovorí v štatistikách математическим ожиданием), хорошо видно, что некоторые числа (27, 32, 11…) ыыыадали заметно чче (+18%), а н 10? Соответственно, можно попробовать использовать эtu информацию для стратегеир. либо ставить на те шары, что ыы ыыаю чаще, либо наоборот - делать на но наы жа жа жаы жаы же шаы жаы жаы жа Kedy жаы жа Kedy жаы жаы ж rád аыиаы.

Úloha 3. Aké čísla neboli dlho vyžrebované?

Ďalšia stratégia je založená na myšlienke, že pri dostatočne veľkom počte žrebovaní by skôr či neskôr malo vypadnúť každé číslo zo všetkých dostupných od 1 do 45. Ak sa teda nejaké čísla medzi výhercami už dlho neobjavili („studené gule“), potom je logické skúsiť na ne v budúcnosti staviť. 

„ VIAC EXPOZOVANÉ (ZÁPAS). Bude vyhľadávať zhora nadol (tj od nových po staré série), aby vyhľadal každé číslo a vydal sériové číslo série (počítajúc od konca roka do začiatku), kde bolo toto číslo naposledy vypustené:

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

Ďalšia herná stratégia je založená na odstránení psychologického faktora pri hádaní čísel. Keď si hráč vyberá čísla stávkou, podvedome to nerobí celkom racionálne. Podľa štatistík sa napríklad čísla od 1 do 31 vyberajú o 70 % častejšie ako ostatné (obľúbené dátumy), 13 sa vyberá menej často (prekliaty tucet), častejšie sa vyberajú čísla obsahujúce „šťastnú“ sedmičku atď. Ale hráme proti stroju (lotériovému bubnu), pre ktorý sú všetky čísla rovnaké, preto má zmysel vyberať ich s rovnakou matematickou nestrannosťou, aby sme si vyrovnali šance. Aby sme to dosiahli, musíme v Exceli vytvoriť generátor náhodných a čo je najdôležitejšie - neopakujúcich sa čísel:

Postupujte nasledovne:

1. Vytvorme „inteligentnú“ tabuľku s názvom TableGenerator, kde v prvom stĺpci budú naše čísla od 1 do 45.
2. V druhom stĺpci zadajte hmotnosť pre každé číslo (budeme ju potrebovať o niečo neskôr). Ak sú pre nás všetky čísla rovnako hodnotné a chceme ich vybrať s rovnakou pravdepodobnosťou, tak váhu môžeme všade nastaviť rovnú 1.
3. V treťom stĺpci použijeme funkciu SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 až 1, добавив к немущ вес изстолурес изстолурес Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) vygeneruje sa nový súbor 45 náhodných čísel, pričom sa zohľadní váha každého z nich.
4. Pridajme štvrtý stĺpec, kde pomocou funkcie RANK (RANK) вычислим ранг (позицию в топе) для каждого из чисел.

Teraz zostáva vykonať výber prvých šiestich čísel podľa poradia 6 pomocou funkcie VIAC EXPOZOVANÉ (ZÁPAS):

При нажатии на клавишу F9 vzorce na hárku Excel sa prepočítajú a zakaždým dostaneme novú sadu 6 čísel v zelených bunkách. Navyše čísla, pre ktoré bola v stĺpci B nastavená väčšia váha, získajú proporcionálne vyššie hodnotenie, a preto sa častejšie objavia vo výsledkoch našej náhodnej vzorky. Ak je váha pre všetky čísla nastavená na rovnakú, potom sa všetky vyberú s rovnakou pravdepodobnosťou. Takto získame spravodlivý a nezaujatý generátor náhodných čísel 6 zo 45, ale s možnosťou v prípade potreby upraviť náhodnosť rozdelenia.

Ak sa rozhodneme hrať v každom žrebovaní nie s jedným, ale napríklad s dvoma tiketmi naraz, z ktorých si v každom vyberieme neopakujúce sa čísla, tak jednoducho do zeleného rozsahu môžeme pridať ďalšie riadky zdola, pridanie 6, 12, 18 atď. d. v tomto poradí:

Úloha 5. Simulátor lotérie v Exceli

Ako apoteózu celej tejto témy si vytvoríme plnohodnotný simulátor lotérie v Exceli, kde si môžete vyskúšať ľubovoľné stratégie a porovnať výsledky (v teórii optimalizácie sa niečomu podobnému hovorí aj metóda Monte Carlo, ale bude to jednoduchšie pre nás).

Aby sa všetko čo najviac priblížilo realite, predstavte si na chvíľu, že je 1. január 2022 a pred nami sú tohtoročné žrebovania, v ktorých plánujeme hrať. Do tabuľky som zapísal reálne padnuté čísla tablTiraži2022, čím sa dodatočne vylosované čísla oddelia od seba do samostatných stĺpcov pre uľahčenie následných výpočtov:

Na samostatnom liste Hra vytvorte polotovar na modelovanie vo forme „inteligentnej“ tabuľky s názvom tabIgra nasledujúci formulár:

Tu:

• V žltých bunkách vyššie nastavíme pre makro počet žrebovaní v roku 2022, ktorých sa chceme zúčastniť (1-82) a počet tiketov, ktoré hráme v každom žrebovaní.
• Údaje pre prvých 11 stĺpcov (AJ) skopíruje makro z hárku výkresu 2022.
• Údaje pre ďalších šesť stĺpcov (KP) makro prevezme z listu Generator, kde sme implementovali generátor náhodných čísel (pozri problém 4 vyššie).
• V stĺpci Q počítame počet zhôd medzi vypadnutými číslami a číslami vygenerovanými pomocou funkcie SUMPRODUCT (SUMA PRODUKT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус, минус 50 рублезие залей
• V poslednom stĺpci S považujeme celkový výsledok celej hry za kumulatívny súčet, aby sme videli dynamiku procesu.

A na oživenie celej tejto štruktúry potrebujeme malé makro. Na karte vývojka (vývojár) vybrať tím Visual Basic alebo použite klávesovú skratku ostatné+F11. Potom cez menu pridajte nový prázdny modul Vložiť – Modul a zadajte tam nasledujúci kód:

Čiastková lotéria() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки)"Nastaviť pracovný hárok(na листерты) Set wsG. wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576"). Vymažte 'чищаем старые данные pre t = 1 do igames pre b = 1 do iTickets' копируем данные pre t = 2022 to igames pre b = 1 do iTickets 'к копируем данные pre t = 1 to igames pre b = 1 do iTickets' к 'к л л л л л я я я я я я я я я я я я л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л л (10, 1).Copy Destination:=wsGame.Cells(i, 4) 'копируем и вставляем специальной вставкой значений сгенерированные номера с листа Генератор wsNumbers.Range("G4:L11").Copy wsGame.Cells(i, 1) .PasteSpecial Paste:=xlPasteValues ​​i = i + XNUMX Ďalší b Ďalší t End Sub  

Zostáva zadať požadované počiatočné parametre do žltých buniek a spustiť makro Vývojár – makrá (Vývojár – makrá) alebo klávesová skratka ostatné+F8.

Pre prehľadnosť môžete tiež zostaviť diagram pre posledný stĺpec s kumulatívnym súčtom, ktorý odráža zmenu peňažného zostatku počas hry:

Porovnanie rôznych stratégií

Teraz môžete pomocou vytvoreného simulátora otestovať akúkoľvek hernú stratégiu na skutočných žreboch v roku 2022 a vidieť výsledky, ktoré by to prinieslo. Ak v každom žrebovaní zahráte 1 tiket, celkový obrázok „slivky“ vyzerá približne takto:

Tu:

• Generator je hra, kde v každom žrebovaní vyberáme náhodné čísla vytvorené naším generátorom (s rovnakou váhou).
• Obľúbené je hra, kde v každom žrebovaní používame rovnaké čísla – tie, ktoré najčastejšie vypadli v žrebovaní za posledné dva roky (27, 32, 11, 14, 34, 40).
• Outsideri – to isté, ale používame najvzácnejšie rozbaľovacie čísla (12, 18, 26, 10, 21, 6).
• Studený – pri všetkých žrebovaniach používame čísla, ktoré dlho nevypadli (35, 5, 39, 11, 6, 29).

Ako vidíte, nie je v tom žiadny veľký rozdiel, ale generátor náhodných čísel sa správa o niečo lepšie ako ostatné „stratégie“.

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

Hranie v každom žrebovaní s jedným tiketom s náhodne vygenerovanými číslami (s rovnakou váhou):

Hranie 10 tiketov v každom žrebovaní s náhodne vygenerovanými číslami (s rovnakou váhou):

Hranie 100 tiketov v každom žrebovaní s náhodnými číslami (s rovnakou váhou):

Komentáre sú, ako sa hovorí, nadbytočné – odtok zálohy je nevyhnutný vo všetkých prípadoch 🙂