Simulace loterie v Excelu

Loterie není hon na štěstí,

je to hon na poražené.

Se záviděníhodnou pravidelností (a v poslední době stále častěji) mi lidé píší s prosbou o pomoc při různých výpočtech souvisejících s loteriemi. Někdo chce implementovat svůj tajný algoritmus pro výběr výherních čísel v Excelu, někdo chce najít vzory v číslech, která vypadla z minulých losování, někdo chce nachytat organizátory loterie v nepoctivé hře.

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

Úkol 1. Pravděpodobnost výhry

Vezměme si jako příklad klasickou loterii Stoloto 6 ze 45. Podle pravidel získávají super cenu pouze ti, kteří uhodli všech 10 čísel ze 6 (45 milionů rublů nebo více, pokud se zůstatek cenového fondu nashromáždil z předchozích losování). Pokud jste uhodli 5, dostanete 150 tisíc rublů, pokud 4 – 1500 rublů. , pokud 3 čísla ze 6, pak 150 rublů, pokud 2 čísla – vrátíte 50 rublů utracených za tiket. Hádejte jen jeden nebo žádný – získejte z herního procesu pouze endorfiny.

Matematickou pravděpodobnost výhry lze snadno vypočítat pomocí standardní funkce NUMBER HŘEBEN (KOMBAJN), který je pro takový případ k dispozici v aplikaci Microsoft Excel. Tato funkce vypočítá počet kombinací N čísel z M. Takže pro naši loterii „6 ze 45“ by to bylo:

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

… což je rovno 8, celkový počet všech možných kombinací v této loterii.

Pokud chcete vypočítat pravděpodobnost částečné výhry (2-5 čísel ze 6), musíte nejprve vypočítat počet takových možností, který se rovná součinu počtu kombinací uhodnutých čísel z 6 podle počtu neuhodnutých čísel ze zbývajících (45-6) = 39 čísel. Poté vydělíme celkový počet všech možných kombinací (8) obdrženým počtem výher pro každou možnost – a dostaneme pravděpodobnost výher pro každý případ:

Mimochodem, pravděpodobnost například úmrtí při leteckém neštěstí u nás se odhaduje asi na 1 ku milionu. A pravděpodobnost výhry v kasinu v ruletě, vsazení všeho na jedno číslo je 1 až 37.

Pokud vás vše výše uvedené nezastavilo a jste stále připraveni hrát dále, pokračujte.

Úkol 2. Četnost výskytu každého čísla

Pro začátek si určíme, s jakou frekvencí vypadávají určitá čísla. V ideální loterii by při dostatečně velkém časovém intervalu pro analýzu měly mít všechny koule stejnou pravděpodobnost, že budou ve vítězném vzorku. Ve skutečnosti mohou konstrukční prvky loterijního bubnu a hmotnostní tvar koulí tento obrázek zkreslit au některých koulí může být pravděpodobnost vypadnutí vyšší/nižší než u jiných. Pojďme si tuto hypotézu ověřit v praxi.

Vezměme si například data o všech 2020 z 21 losování loterií, která proběhla v 6-45 z webu jejich organizátora Stoloto, navržená ve formě takové „chytré“ tabulky, vhodné pro analýzu, s názvem tabArchiv Výpůjčky. Розыгрыши проходят два раза в день (v 11 утра и в 11 вечера), т.е. v этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная даля началана

K výpočtu frekvence výskytu každého čísla použijte funkci COUNTIF (COUNTIF) a přidat k němu funkci TEXT (TEXT)přidat k jednociferným číslům úvodní nuly a hvězdičky před a za, aby COUNTIF hledal výskyt čísla kdekoli v kombinaci ve sloupci B. Také pro větší přehlednost sestavíme graf podle výsledků a seřadíme četnosti v sestupném pořadí:

Každý míč by měl v průměru padnout 1459 tahů * 6 míčků / 45 čísel = 194,53krát (přesně tak se říká ve statistikách математическим ожиданием), но хорошо вино, что некоторые числа (27, 32, 11…) ыыпадале зао18 ° C) Соответственно, можно попробовать использовать эту информацию для астратегеир. либо ставить на те шары, что ыпадаюю чаще, л л д ж ш ш ш ш ч ч ч ш ш ш ш ж ш ш ш ш н н н н н н н н н н н н н н н н н н н н н н н н н н н н н н н

Úkol 3. Jaká čísla se dlouho nelosovala?

Další strategie je založena na myšlence, že při dostatečně velkém počtu tahů by dříve nebo později mělo vypadnout každé číslo ze všech dostupných od 1 do 45. Pokud se tedy některá čísla mezi výherci dlouho neobjevila („studené koule“), pak je logické na ně v budoucnu zkusit sázet. 

Можно легко найти все давно не ыыадавшие номера, если отн т т т у л г г т т у л а г т т а а а а а а а а а а а а а а а а а а а а а а а а а VÍCE EXPOZOVANĚ (ZÁPAS). Bude prohledávat shora dolů (tj. od nových ke starým běhům), aby vyhledal každé číslo a vydal sériové číslo běhu (počítáno od konce roku do začátku), kde bylo toto číslo naposledy vypuštěno:

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

Další herní strategie je založena na eliminaci psychologického faktoru při hádání čísel. Když si hráč vybírá čísla tím, že sází, podvědomě to nedělá zcela racionálně. Podle statistik se například čísla od 1 do 31 volí o 70 % častěji než ostatní (oblíbená data), 13 se volí méně často (zatracený tucet), častěji se volí čísla obsahující „šťastnou“ sedmičku atd. Ale hrajeme proti automatu (loterijnímu bubnu), pro který jsou všechna čísla stejná, takže má smysl je volit se stejnou matematickou nestranností, abychom vyrovnali své šance. K tomu potřebujeme v Excelu vytvořit generátor náhodných a hlavně neopakujících se čísel:

Udělat toto:

1. Vytvořme „chytrou“ tabulku s názvem TableGenerator, kde v prvním sloupci budou naše čísla od 1 do 45.
2. Do druhého sloupce zadejte váhu pro každé číslo (budeme ji potřebovat o něco později). Pokud jsou pro nás všechna čísla stejně hodnotná a chceme je vybrat se stejnou pravděpodobností, pak lze váhu nastavit všude rovnou 1.
3. Ve třetím sloupci použijeme funkci SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 až 1, добавив к немущ вес изазолурес Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) bude vygenerována nová sada 45 náhodných čísel s přihlédnutím k hmotnosti každého z nich.
4. Přidejme čtvrtý sloupec, kde pomocí funkce HODNOST (HODNOST) вычислим ранг (позицию в топе) для каждого из чисел.

Nyní zbývá provést výběr prvních šesti čísel podle pořadí 6 pomocí funkce VÍCE EXPOZOVANĚ (ZÁPAS):

При нажатии на клавишу F9 vzorce na listu Excel se přepočítají a pokaždé dostaneme novou sadu 6 čísel v zelených buňkách. Čísla, pro která byla ve sloupci B nastavena větší váha, navíc získají úměrně vyšší hodnocení a budou se tak častěji objevovat ve výsledcích našeho náhodného vzorku. Pokud je váha pro všechna čísla nastavena na stejnou, pak budou všechna vybrána se stejnou pravděpodobností. Tímto způsobem získáme spravedlivý a nezaujatý generátor náhodných čísel 6 ze 45, ale s možností v případě potřeby provést úpravy náhodnosti rozdělení.

Pokud se rozhodneme hrát v každém tahu ne s jedním, ale např. se dvěma tikety najednou, z nichž v každém vybereme neopakující se čísla, pak jednoduše přidáme další řádky zespodu do zeleného rozsahu, přidání 6, 12, 18 atd. k hodnosti. d. respektive:

Úkol 5. Simulátor loterie v Excelu

Jako apoteózu celého tohoto tématu si pojďme vytvořit plnohodnotný simulátor loterie v Excelu, kde si můžete vyzkoušet libovolné strategie a porovnat výsledky (v teorii optimalizace se něčemu podobnému také říká metoda Monte Carlo, ale bude jednodušší pro nás).

Aby se vše co nejvíce přiblížilo realitě, představte si na chvíli, že je 1. leden 2022 a máme před sebou letošní losování, ve kterém plánujeme hrát. Zadal jsem do tabulky skutečná shozená čísla tablTiraži2022, oddělení dodatečně vylosovaných čísel od sebe do samostatných sloupců pro usnadnění následných výpočtů:

Na samostatném listu Hra vytvořte polotovar pro modelování ve formě „chytré“ tabulky s názvem tabIgra následující formulář:

Zde:

• Ve žlutých buňkách výše nastavíme makru počet tahů v roce 2022, kterých se chceme zúčastnit (1-82) a počet tiketů, které hrajeme v každém losování.
• Data pro prvních 11 sloupců (AJ) budou zkopírována makrem z listu výkresu 2022.
• Data pro dalších šest sloupců (KP) makro převezme z listu Generátor, kde jsme implementovali generátor náhodných čísel (viz problém 4 výše).
• Ve sloupci Q počítáme počet shod mezi vyřazenými čísly a čísly vygenerovanými pomocí funkce SUMPRODUCT (SUMPRODUKT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублезие палезие за
• V posledním sloupci S považujeme celkový výsledek celé hry za kumulativní součet, abychom viděli dynamiku procesu.

A abychom celou tuto strukturu oživili, potřebujeme malé makro. Na kartě vývojka (Vývojář) vybrat tým Visual Basic nebo použijte klávesovou zkratku Další+F11. Poté pomocí nabídky přidejte nový prázdný modul Vložit – Modul a zadejte tam následující kód:

Dílčí loterie() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки" Setsheet(nastavit)"Nastavit pracovní list (na лисеты) Set wsG wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576"). Odstraňte 'очищаем старые данные pro t = 1 až iGames pro b = 1 až itickets' (2022, 1) .Copy Destination: = WSGame.Cells (i, 1) 'копируем и вставляем специальной г г с с ~ .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Další b Další t End Sub  

Zbývá zadat požadované počáteční parametry do žlutých buněk a projít makro Vývojář – Makra (Vývojář — Makra) nebo klávesovou zkratkou Další+F8.

Pro přehlednost můžete také vytvořit diagram pro poslední sloupec s kumulativním součtem, který odráží změnu peněžního zůstatku během hry:

Porovnání různých strategií

Nyní můžete pomocí vytvořeného simulátoru v roce 2022 otestovat jakoukoli herní strategii na skutečných losováních a vidět výsledky, které by přinesla. Pokud v každém slosování zahrajete 1 tiket, celkový obrázek „švestky“ vypadá asi takto:

Zde:

• Generátor je hra, kde v každém losování vybíráme náhodná čísla vytvořená naším generátorem (se stejnou váhou).
• Oblíbené je hra, kde v každém tahu používáme stejná čísla – ta, která v posledních dvou letech nejčastěji vypadla při losování (27, 32, 11, 14, 34, 40).
• Outsiders – totéž, ale používáme nejvzácnější rozevírací čísla (12, 18, 26, 10, 21, 6).
• Studený – ve všech losováních používáme čísla, která dlouho nevypadla (35, 5, 39, 11, 6, 29).

Jak vidíte, není v tom žádný velký rozdíl, ale generátor náhodných čísel se chová o něco lépe než ostatní „strategie“.

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

Hraní v každém losování s jedním tiketem s náhodně vygenerovanými čísly (se stejnou váhou):

Hraní 10 tiketů v každém losování s náhodně vygenerovanými čísly (se stejnou váhou):

Hraní 100 tiketů v každém losování s náhodnými čísly (se stejnou váhou):

Komentáře, jak se říká, jsou nadbytečné – odvod záloh je nevyhnutelný ve všech případech 🙂