Lotteriesimulatioun an Excel

D'Lotterie ass keng Juegd op Gléck,

et ass eng Juegd op Verléierer.

Mat beneideg Regularitéit (a méi dacks an der leschter Zäit) schreiwen d'Leit mech fir Hëllef a verschiddene Berechnungen am Zesummenhang mat Lotterien ze froen. Eppes wëll hire geheime Algorithmus fir d'Auswiel vun Gewënnzuelen an Excel implementéieren, een wëll Musteren an den Zuelen fannen, déi aus vergaangenen Zeechnungen gefall sinn, een wëll d'Organisateuren vun der Lotterie an engem onéierleche Spill fänken.

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

Aufgab 1. Wahrscheinlechkeet ze gewannen

Loosst eis de klassesche Stoloto 6 aus 45 Lotterie als Beispill huelen. No de Regelen, kréien nëmmen déi, déi all 10 Zuelen aus 6 roden e super Präis (45 Milliounen Rubelen oder méi, wann d'Gläichgewiicht vun der Präispolitik Fonds aus virdrun molen cumuléierten huet). Wann Dir 5 roden, kritt Dir 150 dausend Rubel, wann 4 - 1500 Rubel. , wann 3 Zuelen aus 6, dann 150 Rubelen, wann 2 Zuelen - Dir wäert zréck 50 Rubelen op der Ticket verbruecht. Guess nëmmen een oder keen - kritt nëmmen Endorphine vum Spillprozess.

D'mathematesch Wahrscheinlechkeet vun Gewënn kann einfach mat der Norm Funktioun berechent ginn NUMMER KOMB (KOMBINÉIERT), déi an Microsoft Excel fir esou e Fall verfügbar ass. Dës Funktioun berechent d'Zuel vun de Kombinatioune vun N Zuelen aus M. Also fir eis "6 aus 45" Lotterie wier et:

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

... dat ass gläich wéi 8, d'Gesamtzuel vun alle méigleche Kombinatiounen an dëser Lotterie.

Wann Dir d'Wahrscheinlechkeet fir eng partiell Victoire (2-5 Zuelen aus 6) ze berechnen wëllt, da musst Dir als éischt d'Zuel vun esou Optiounen berechnen, déi gläich wéi d'Produkt vun der Unzuel vun de Kombinatioune vu geschätzten Zuelen aus 6 vun der Zuel vun unguessed Zuelen aus de Rescht (45-6) = 39 Zuelen. Dann deele mir d'Gesamtzuel vun alle méigleche Kombinatiounen (8) duerch déi kritt Zuel vu Gewënn fir all Optioun - a mir kréien d'Gewënnwahrscheinlechkeeten fir all Fall:

Iwwregens gëtt d'Wahrscheinlechkeet zum Beispill bei engem Fligeraccident an eisem Land op ronn 1 op eng Millioun geschat. An d'Wahrscheinlechkeet fir an engem Casino am Roulette ze gewannen, alles op eng Nummer ze wetten ass 1 bis 37.

Wann all déi uewe genannten Iech net gestoppt hunn an Dir sidd nach ëmmer prett fir weider ze spillen, fuert weider.

Aufgab 2. Heefegkeet vun Optriede vun all Zuel

Fir unzefänken, loosst eis bestëmmen mat wéi enger Frequenz bestëmmten Zuelen erausfalen. An enger idealer Lotterie, mat enger genuch grousser Zäitintervall fir d'Analyse, sollen all Bäll déi selwecht Wahrscheinlechkeet hunn fir am Gewënnprouf ze sinn. A Wierklechkeet kënnen d'Designmerkmale vun der Lotterietrommel an d'Gewiichtform vun de Bäll dëst Bild verzerren, a fir e puer Bäll kann d'Wahrscheinlechkeet fir erauszefalen méi héich / méi niddereg sinn wéi fir anerer. Loosst eis dës Hypothese an der Praxis testen.

Loosst eis zum Beispill Daten iwwer all 2020 vun 21 Lotterie-Zeechnungen huelen, déi am 6-45 vun der Websäit vun hirem Organisateur Stoloto stattfonnt hunn, entworf a Form vun esou engem "smart" Dësch, praktesch fir Analyse, mam Numm tabArchive Circulatioun. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная fir начала выборка fir анализа:

Fir d'Frequenz vum Optriede vun all Zuel ze berechnen, benotzt d'Funktioun COUNTIF (COUNTIF) an dobäi eng Funktioun TEXT (TEXT)fir führend Nullen an Asterisken virun an no un eenzifferen Zuelen ze addéieren, sou datt COUNTIF no der Optriede vun enger Zuel iwwerall an der Kombinatioun an der Kolonn B sicht. Och fir méi Kloerheet wäerte mir en Diagramm no Resultater bauen an d'Frequenzen sortéieren an erofgaangend Reiefolleg:

Am Duerchschnëtt soll all Ball 1459 Zeechnungen falen * 6 Bäll / 45 Zuelen = 194,53 Mol (dat ass genau dat wat an de Statistiken genannt gëtt математическим ожиданием(27, но хорошо вино, что Некорые чти (32, 11, 18, qаа, таорем Соответственно, можно попробовать использовать эту информацию fir стратегии выигрыша, т.е. loosse sech op d'Schrëften, loosst eis liewen, liewen op d'Betreiung - schléisst d'Reduzéierung vun der Vergaangenheet an der Vergaangenheet.

Aufgab 3. Wéi eng Zuelen sinn scho laang net gezeechent?

Aner Strategie baséiert op der Iddi, datt mat enger genuch grouss Zuel vun molen, fréier oder spéider all Zuel vun all sinn aus 1 ze 45 falen soll. Also wann e puer Zuelen net ënnert de Gewënner fir eng laang Zäit wossten ("kal Bäll"), dann ass et logesch ze probéieren op hinnen an Zukunft wetten. 

E puer vun de meeschte Fäll ass et net e bëssen op der Säit, an et ass e bësse méi spéit den 2020. Februar 21. MÉI ausgesat (MATCH). Et wäert vun uewe bis ënnen sichen (dh vun neien op alen Runen) fir no all Nummer ze sichen an d'Seriennummer vun der Course erauszeginn (vun Enn vum Joer bis Ufank zielt) wou dës Zuel fir d'lescht erofgaang ass:

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

Eng aner Spillstrategie baséiert op der Eliminatioun vum psychologesche Faktor wann Dir Zuelen roden. Wann e Spiller Zuelen wielt andeems hien seng wetten mécht, mécht hien dat onbewosst net ganz rational. Laut Statistiken, zum Beispill, ginn d'Zuelen vun 1 bis 31 70% méi dacks gewielt wéi de Rescht (Lieblingsdatum), 13 gëtt manner dacks gewielt (verdammt Dosen), Zuelen déi "glécklech" siwen enthalen méi dacks gewielt, etc. Mee mir spillen géint eng Maschinn (Lotteriestrommel) fir déi all Zuelen déi selwecht sinn, also mécht et Sënn fir se mat der selwechter mathematescher Onparteilechkeet ze wielen fir eis Chancen auszegläichen. Fir dëst ze maachen, musse mir e Generator vun zoufälleg an - am wichtegsten - net-widderhuelend Zuelen an Excel erstellen:

Fir dëst ze maachen:

1. Loosst eis e "Smart" Dësch erstellen mam Numm tableGenerator, wou déi éischt Kolonn eis Zuele vun 1 bis 45 sinn.
2. An der zweeter Kolonn, gitt d'Gewiicht fir all Zuel (mir brauchen et e bësse méi spéit). Wann all Zuelen fir eis gläich wäertvoll sinn a mir wëllen se mat gläicher Wahrscheinlechkeet wielen, da kann d'Gewiicht iwwerall gläich 1 gesat ginn.
3. An der drëtter Kolonn benotze mir d'Funktioun SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 bis 1, добавив к нему вес из предыдущего столбц. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) gëtt en neie Set vu 45 zoufälleg Zuelen generéiert, andeems d'Gewiicht fir jidderee vun hinnen berücksichtegt gëtt.
4. Loosst eis eng véiert Kolonn derbäi, wou d'Funktioun benotzt RANK (RANK) вычислим ранг (позицию в топе) fir каждого из чисел.

Elo bleift et eng Auswiel vun den éischte sechs Zuelen ze maachen duerch Rang 6 mat der Funktioun MÉI ausgesat (MATCH):

При нажатии на клавишу F9 d'Formelen op der Excel Blat ginn nei berechent an all Kéier kréie mir en neie Set vu 6 Zuelen a gréng Zellen. Ausserdeem kréien d'Zuelen, fir déi e méi grousst Gewiicht an der Kolonn B gesat gouf, e proportional méi héije Rang an doduerch méi dacks an de Resultater vun eiser zoufälleger Probe. Wann d'Gewiicht fir all Zuelen op d'selwecht gesat ass, da ginn se all mat der selwechter Wahrscheinlechkeet ausgewielt. Op dës Manéier kréie mir e fairen an onparteiesche Zoufallsnummergenerator vu 6 aus 45, awer mat der Fäegkeet fir Upassungen un d'Zoufällegkeet vun der Verdeelung ze maachen wann néideg.

Wa mir décidéieren an all Zeechnen ze spillen net mat engem, mä, zum Beispill, mat zwee Ticketen gläichzäiteg, an all vun deenen mir wäerten net widderhuelen Zuelen wielen, da kënne mir einfach zousätzlech Linnen vun ënnen op déi gréng Gamme dobäi, dobäi 6, 12, 18, etc. d. respektiv:

Aufgab 5. Lotterie Simulator an Excel

Als Apotheose vun dësem ganzen Thema, loosst eis e vollwäertege Lotteriesimulator an Excel erstellen, wou Dir all Strategien ausprobéiere kënnt an d'Resultater vergläichen (an der Optimiséierungstheorie gëtt eppes ähnleches och Monte Carlo Method genannt, awer et wäert méi einfach sinn fir eis).

Fir alles esou no wéi méiglech un d'Realitéit ze maachen, stellt Iech e Moment vir datt et den 1. Januar 2022 ass a mir hunn d'Zeechnunge vun dësem Joer virun eis, wou mir plangen ze spillen. Ech hunn déi richteg erofgaang Zuelen an der Tabell aginn tablTiraži2022, déi zousätzlech gezeechent Zuelen vuneneen trennen an getrennte Kolonnen fir d'Bequemlechkeet vun de spéider Berechnungen:

Op engem getrennten Blat Spill erstellt eng eidel fir d'Modellerung a Form vun engem "Smart" Dësch mam Numm tabIgra folgend Form:

Hei:

• An de gielen Zellen hei uewen wäerte mir fir de Makro d'Zuel vun den Zeechnungen am Joer 2022 setzen, wou mir wëllen matmaachen (1-82) an d'Zuel vun den Ticketen déi mir an all Zeechnen spillen.
• D'Donnéeën fir déi éischt 11 Sailen (AJ) ginn vum Makro vum 2022 Zeechnungsblat kopéiert.
• Daten fir déi nächst sechs Kolonnen (KP) wäert de Makro aus dem Blat huelen Generator, wou mir e zoufälleg Zuel Generator ëmgesat hunn (kuckt Problem 4 uewen).
• An der Kolonn Q ziele mir d'Zuel vun de Matcher tëscht den erofgaangen Zuelen an déi generéiert mat der Funktioun SUMPRODUCT (SUMPRODUKT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, сливири, 50 рублей за билет, есливиры, XNUMX р.
• An der leschter Kolonn S betruechte mir d'Gesamtresultat vum ganze Spill als e kumulativen Total fir d'Dynamik am Prozess ze gesinn.

A fir dës ganz Struktur erëmbeliewen, brauche mir e klenge Makro. Op der Tab Entwéckler (Entwéckler) wielt eng Equipe visuell Grondleegend oder benotzt Tastatur Ofkiirzung Alt+F11. Füügt dann en neien eidele Modul iwwer de Menü Insert - Modul a gitt de folgende Code do:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets("Иенгра") Set wsheetsNumbers("") Set wsheetsnumbers("") wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6:1048576"). Läschen 'очищаем старые данные For t = 1 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тира2022жия Ис листа Тира1жия 1s. (1, 10).Copy Destination:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенерированнстые номират с.4.C.4s.11s.1s" .PasteSpecial Paste:=xlPasteValues ​​i = i + XNUMX Next b Next t End Sub  

Et bleift fir déi gewënscht initial Parameteren an de giel Zellen anzeginn an de Makro duerch ze lafen Entwéckler - Macros (Entwéckler - Macros) oder Tastatur Ofkiirzung Alt+F8.

Fir Kloerheet kënnt Dir och en Diagramm fir déi lescht Kolonn mat engem kumulativen Total bauen, wat d'Verännerung vum Geldbalance während dem Spill reflektéiert:

Verglach vu verschiddene Strategien

Elo, andeems Dir de erstallte Simulator benotzt, kënnt Dir all Spillstrategie op realen Zeechnungen am Joer 2022 testen an d'Resultater gesinn déi et bréngt. Wann Dir 1 Ticket an all Zeechnen spillt, da gesäit d'Gesamtbild vun der "Plum" sou aus:

Hei:

• Generator ass e Spill wou mir an all Zeechnen zoufälleg Zuelen auswielen, déi vun eisem Generator erstallt ginn (mat deemselwechte Gewiicht).
• Favoritten ass e Spill wou mir an all Zeechnen déiselwecht Zuelen benotzen - déi, déi am meeschten an de leschten zwee Joer erausgefall sinn (27, 32, 11, 14, 34, 40).
• Auslänner - déi selwecht, mä mir benotzen déi selten Ausklapplëscht Zuelen (12, 18, 26, 10, 21, 6).
• Kale - an all Zeechnungen benotze mir Zuelen déi net laang gefall sinn (35, 5, 39, 11, 6, 29).

Wéi Dir gesitt kann, gëtt et keen groussen Ënnerscheed, mä de zoufälleg Zuel Generator behuelen e bësse besser wéi déi aner "Strategien".

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

Spillt an all Zeechnen mat engem Ticket mat zoufälleg generéierten Zuelen (mat deemselwechte Gewiicht):

Spillt 10 Ticketen an all Zeechnen mat zoufälleg generéierten Zuelen (mat deemselwechte Gewiicht):

Spillt 100 Ticketen an all Zeechnen mat zoufälleg Zuelen (mat deemselwechte Gewiicht):

Kommentaren, wéi se soen, sinn iwwerflësseg - en Depot Drain ass inévitabel an alle Fäll 🙂