Lottó szimuláció Excelben

A lottó nem szerencsevadászat,

ez a vesztesek vadászata.

Irigylésre méltó rendszerességgel (és mostanában egyre gyakrabban) írnak nekem segítséget kérve a sorsolásokkal kapcsolatos különféle számításokhoz. Valaki a nyerőszámok kiválasztására szolgáló titkos algoritmusát akarja megvalósítani Excelben, valaki mintákat akar találni a korábbi sorsolásokból kiesett számokban, valaki tisztességtelen játékban akarja elkapni a lottó szervezőit.

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

1. feladat. A nyerési valószínűség

Vegyük például a klasszikus Stoloto 6/45 lottót. A szabályok szerint csak azok kapnak szuperdíjat, akik a 10-ból mind a 6 számot kitalálták (45 millió rubel vagy több, ha a nyereményalap egyenlege a korábbi sorsolásokból halmozódott fel). Ha 5-öt tippelt, 150 ezer rubelt kap, ha 4-1500 rubelt. , ha 3-ból 6 szám, akkor 150 rubel, ha 2 szám – a jegyre költött 50 rubelt visszaadja. Tippelj csak egyet vagy egyet sem – csak endorfinokat nyerj a játék folyamatából.

A nyerés matematikai valószínűsége könnyen kiszámítható a standard függvény segítségével SZÁMFÉS (KOMBÁJN), amely ilyen esetre elérhető a Microsoft Excelben. Ez a függvény kiszámítja az M-ből N szám kombinációinak számát. Tehát a „6-ből 45” sorsolásunk esetében ez lenne:

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

… ami egyenlő 8-cal, az összes lehetséges kombináció számával ebben a lottón.

Ha ki akarja számítani a részleges nyeremény valószínűségét (2-ból 5-6 szám), akkor először ki kell számítania az ilyen opciók számát, amely megegyezik a kitalált számok kombinációinak szorzatával. 6 a ki nem talált számok számával a fennmaradó (45-6) közül = 39 szám. Ezután elosztjuk az összes lehetséges kombináció számát (8) az egyes opciók nyert nyereményeinek számával – és megkapjuk az egyes esetek nyerési valószínűségét:

Mellesleg, annak a valószínűsége, hogy hazánkban például repülőbalesetben meghalnak, körülbelül 1:1 millióra becsülik. És annak a valószínűsége, hogy egy rulett kaszinóban nyerünk, mindenre egy számra fogadva 37-XNUMX.

Ha a fentiek mindegyike nem akadályozta meg, és továbbra is készen áll a további játékra, folytassa.

2. feladat Az egyes számok előfordulási gyakorisága

Először is határozzuk meg, hogy bizonyos számok milyen gyakorisággal esnek ki. Egy ideális lottó során, ha kellően hosszú időintervallumot kell adni az elemzéshez, minden golyónak azonos valószínűséggel kell szerepelnie a nyerő mintában. A valóságban a lottódob tervezési jellemzői és a golyók súlyformája torzíthatják ezt a képet, és egyes golyók esetében a kiesés valószínűsége magasabb/alacsonyabb lehet, mint másoké. Teszteljük ezt a hipotézist a gyakorlatban.

Vegyük például a 2020-21 között lezajlott 6 lottósorsolás közül mind a 45-as adatot a szervezőjük Stoloto weboldaláról, amelyet egy ilyen „okos” táblázat formájában terveztek, amely kényelmes elemzésre, névvel. lapArchívum Körforgalom. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Az egyes számok előfordulási gyakoriságának kiszámításához használja a függvényt COUNTIF (COUNTIF) és adjunk hozzá egy függvényt TEXT (SZÖVEG)a kezdő nullák és csillagok hozzáadásához az egyjegyű számokhoz, így a COUNTIF megkeresi egy szám előfordulását bárhol a kombinációban a B oszlopban. Ezenkívül a nagyobb áttekinthetőség érdekében diagramot készítünk eredmények alapján, és rendezzük a gyakoriságokat csökkenő sorrendben:

Átlagosan minden labdának 1459 döntetlen * 6 labda / 45 szám = 194,53 alkalommal kell leesnie (pontosan ezt hívják a statisztikákban математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а метно чаще (+10%), а незноа, 21.масрое (6, яебекоторые, орео15…) Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редганиж выпадающие выпадающие шары.

3. feladat Milyen számokat nem húztak ki sokáig?

Egy másik stratégia azon az elképzelésen alapul, hogy kellően nagy számú húzás esetén előbb-utóbb minden szám kiesik az 1-től 45-ig elérhető összes szám közül. Tehát ha néhány szám már régóta nem jelent meg a nyertesek között („hideg labdák”), akkor logikus, hogy a jövőben megpróbáljunk rájuk fogadni. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за архив изубульсей за 2020-21 гопадынишие не выпадавшие номера KIÉPÍTETTebb (MÉRKŐZÉS). Felülről lefelé fog keresni (azaz újtól a régi futásig), hogy megkeresse az egyes számokat, és kiadja annak a futamnak a sorozatszámát (az év végétől az elejéig), ahol ez a szám utoljára kiesett:

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

Egy másik játékstratégia a lélektani tényező kiküszöbölésén alapul a számok találgatása során. Amikor egy játékos úgy választ számokat, hogy megteszi a tétjét, tudat alatt ezt nem teljesen racionálisan teszi. A statisztikák szerint például az 1-től 31-ig terjedő számokat 70%-kal gyakrabban választják ki, mint a többit (kedvenc dátumok), a 13-at ritkábban (rohadt tucat), gyakrabban választják a „szerencsés” hetest tartalmazó számokat stb. De egy olyan gép (lottódob) ellen játszunk, amelynél minden szám azonos, ezért érdemes azonos matematikai pártatlansággal választani őket, hogy esélyeinket kiegyenlítsük. Ehhez létre kell hoznunk egy véletlenszerű és – ami a legfontosabb – nem ismétlődő számokból álló generátort az Excelben:

Ezt csináld meg:

1. Hozzunk létre egy „okos” táblázatot, melynek neve táblázatGenerátor, ahol az első oszlop a mi számaink lesznek 1-től 45-ig.
2. A második oszlopba írja be az egyes számok súlyát (kicsit később lesz szükségünk rá). Ha minden szám egyformán értékes számunkra, és azonos valószínűséggel akarjuk kiválasztani, akkor a súlyt mindenhol 1-re lehet állítani.
3. A harmadik oszlopban a függvényt használjuk SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) egy 45 véletlen számból álló új halmaz jön létre, figyelembe véve mindegyik súlyát.
4. Adjunk hozzá egy negyedik oszlopot, ahol a függvény használatával RANG (RANG) вычислим ранг (позицию в топе) для каждого из чисел.

Most már csak az első hat számot kell kiválasztani a 6. rang szerint a funkció segítségével KIÉPÍTETTebb (MÉRKŐZÉS):

При нажатии на клавишу F9 az Excel lapon lévő képletek újraszámításra kerülnek, és minden alkalommal egy új, 6 számból álló halmazt kapunk zöld cellákban. Ráadásul azok a számok, amelyeknek a B oszlopban nagyobb súlyt adtak meg, arányosan magasabb rangot kapnak, és így gyakrabban jelennek meg véletlenszerű mintánk eredményeiben. Ha az összes szám súlya azonosra van állítva, akkor mindegyik azonos valószínűséggel kerül kiválasztásra. Így 6-ből 45 tisztességes és elfogulatlan véletlenszám-generátort kapunk, de szükség esetén módosítani tudjuk az eloszlás véletlenszerűségét.

Ha úgy döntünk, hogy minden sorsoláson nem egy, hanem például egyszerre két jeggyel játszunk, amelyek mindegyikében nem ismétlődő számokat választunk, akkor egyszerűen hozzáadhatunk további sorokat alulról a zöld tartományhoz, a ranghoz adva 6, 12, 18 stb. d. illetőleg:

5. feladat: Lottó szimulátor Excelben

Ennek az egész témának az apoteózisaként készítsünk Excelben egy teljes értékű lottó szimulátort, ahol bármilyen stratégiát kipróbálhatunk és az eredményeket összehasonlíthatjuk (az optimalizálás elméletben valami hasonlót Monte Carlo módszernek is hívnak, de egyszerűbb lesz nekünk).

Hogy minden a lehető legközelebb álljon a valósághoz, képzeljük el egy pillanatra, hogy 1. január 2022-je van, és előttünk állnak az idei sorsolások, amelyeken a tervek szerint játszani fogunk. A valós kiejtett számokat beírtam a táblázatba tablTiraži2022, külön oszlopokba választva a kiegészítőleg rajzolt számokat egymástól a későbbi számítások megkönnyítése érdekében:

Külön lapon játék hozzon létre egy ürességet a modellezéshez egy „okos” táblázat formájában a névvel tabIgra a következő űrlapot:

Itt:

• A fenti sárga cellákban beállítjuk a makróhoz, hogy 2022-ben hány sorsoláson kívánunk részt venni (1-82) és az egyes sorsolásokon kijátszott jegyek számát.
• Az első 11 oszlop (AJ) adatait a makró a 2022-es sorsolási lapról másolja.
• A következő hat oszlop (KP) adatait a makró a munkalapról veszi Generátor, ahol megvalósítottunk egy véletlenszám-generátort (lásd fent a 4. feladatot).
• A Q oszlopban megszámoljuk az elejtett számok és a függvény segítségével generált számok közötti egyezések számát SUMPRODUCT (ÖSSZES TERMÉK).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если вы) зигперет, если вы
• Az utolsó S oszlopban a teljes játék összesített eredményét összesített összegnek tekintjük, hogy lássuk a folyamat dinamikáját.

És ennek az egész szerkezetnek az újraélesztéséhez szükségünk van egy kis makróra. A lapon fejlesztő (Fejlesztő) válassz csapatot Visual Basic vagy használja a billentyűparancsot más+F11. Ezután adjon hozzá egy új üres modult a menün keresztül Beszúrás – Modul és oda írja be a következő kódot:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6:1048576").Delete 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тиражи 2022 и вставляем на лист Игра wsArchive.Cells(t + 1, 1).Resize (1, 10).Cél másolása:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенер значений,Copy .PasteSpecial Paste:=xlPasteValues ​​i = i + 4 Következő b Következő t Vége Al  

A sárga cellákba be kell írni a kívánt kezdeti paramétereket, és végig kell futtatni a makrót Fejlesztő – Makrók (Fejlesztő – Makrók) vagy billentyűparancsot más+F8.

Az egyértelműség kedvéért diagramot is készíthet az utolsó oszlophoz a halmozott végösszeggel, amely tükrözi a pénzegyenleg játék közbeni változását:

Különböző stratégiák összehasonlítása

A létrehozott szimulátor segítségével 2022-ben bármilyen játékstratégiát tesztelhet valódi húzásokon, és láthatja, milyen eredményeket hozna. Ha minden sorsoláson 1 jeggyel játszik, akkor a „szilva” összképe valahogy így néz ki:

Itt:

• Generátor egy olyan játék, ahol minden sorsolásnál a generátorunk által létrehozott véletlen számokat választunk (azonos súllyal).
• Érdekes egy olyan játék, ahol minden sorsolásnál ugyanazokat a számokat használjuk – azokat, amelyek az elmúlt két évben a legtöbbször kiestek a sorsolásnál (27, 32, 11, 14, 34, 40).
• Outsiders – ugyanaz, de mi a legritkább legördülő számokat használjuk (12, 18, 26, 10, 21, 6).
• Hideg – minden sorsolásnál olyan számokat használunk, amelyek régóta nem estek ki (35, 5, 39, 11, 6, 29).

Mint látható, nincs nagy különbség, de a véletlenszám-generátor kicsit jobban viselkedik, mint a többi „stratégia”.

a ъединяются в группу).

Minden sorsoláson egy jeggyel játszva véletlenszerűen generált számokkal (azonos súllyal):

Minden sorsoláson 10 jegy kijátszása véletlenszerűen generált számokkal (azonos súllyal):

Minden sorsoláson 100 jegy kijátszása véletlenszerű számokkal (azonos súlyú):

A kommentek, ahogy mondani szokták, feleslegesek – a betétlevezetés minden esetben elkerülhetetlen 🙂