Inhalte
Die Lotterie ist keine Jagd nach Glück,
es ist eine Jagd nach Verlierern.
Mit beneidenswerter Regelmäßigkeit (und in letzter Zeit häufiger) schreiben mir Leute und bitten um Hilfe bei verschiedenen Berechnungen im Zusammenhang mit Lotterien. Jemand möchte seinen geheimen Algorithmus zur Auswahl von Gewinnzahlen in Excel implementieren, jemand möchte Muster in den Zahlen finden, die aus früheren Ziehungen gefallen sind, jemand möchte die Organisatoren der Lotterie bei einem unehrlichen Spiel erwischen.
В этой статье мне хотелось бы ответить на часть этих вопросов. Благо, в Excel для решения таких задач достаточно инструментов, многие из которых, кстати, могут пригодиться и в бо лее прозаических рабочих ситуациях.
Aufgabe 1. Gewinnwahrscheinlichkeit
Nehmen wir als Beispiel die klassische Stoloto 6 aus 45 Lotterie. Gemäß den Regeln erhalten nur diejenigen, die alle 10 von 6 Zahlen erraten haben, einen Superpreis (45 Millionen Rubel oder mehr, wenn sich der Saldo des Preisfonds aus früheren Ziehungen angesammelt hat). Wenn Sie 5 erraten haben, erhalten Sie 150 Rubel, wenn 4 – 1500 Rubel. , wenn 3 Zahlen von 6, dann 150 Rubel, wenn 2 Zahlen – Sie geben 50 Rubel zurück, die Sie für das Ticket ausgegeben haben. Raten Sie nur einen oder keinen – erhalten Sie nur Endorphine aus dem Spielprozess.
Die mathematische Gewinnwahrscheinlichkeit lässt sich ganz einfach mit der Standardfunktion berechnen ZAHLENKAMM (KOMBINIEREN), die für einen solchen Fall in Microsoft Excel zur Verfügung steht. Diese Funktion berechnet die Anzahl der Kombinationen von N Zahlen aus M. Für unsere Lotterie „6 aus 45“ wäre das also:
=ЧИСЛКОМБ(45;6)
… was gleich 8 ist, der Gesamtzahl aller möglichen Kombinationen in dieser Lotterie.
Wenn Sie die Wahrscheinlichkeit für einen Teilgewinn (2-5 Zahlen von 6) berechnen möchten, müssen Sie zuerst die Anzahl solcher Optionen berechnen, die dem Produkt der Anzahl der Kombinationen von erratenen Zahlen entspricht 6 durch die Anzahl der nicht erratenen Zahlen aus den verbleibenden (45-6) = 39 Zahlen. Dann teilen wir die Gesamtzahl aller möglichen Kombinationen (8) durch die erhaltene Anzahl an Gewinnen für jede Option – und erhalten die Gewinnwahrscheinlichkeiten für jeden Fall:
Übrigens wird die Wahrscheinlichkeit, beispielsweise bei einem Flugzeugabsturz in unserem Land zu sterben, auf etwa 1 zu einer Million geschätzt. Und die Wahrscheinlichkeit, in einem Casino beim Roulette zu gewinnen, alles auf eine Zahl zu setzen, liegt bei 1 zu 37.
Wenn all dies Sie nicht aufgehalten hat und Sie immer noch bereit sind, weiter zu spielen, fahren Sie fort.
Aufgabe 2. Häufigkeit des Auftretens jeder Zahl
Lassen Sie uns zunächst feststellen, mit welcher Häufigkeit bestimmte Zahlen herausfallen. In einer idealen Lotterie sollten bei einem ausreichend großen Zeitintervall für die Analyse alle Kugeln die gleiche Wahrscheinlichkeit haben, in der Gewinnstichprobe zu sein. In der Realität können die Konstruktionsmerkmale der Lotterietrommel und die Gewichtsform der Kugeln dieses Bild verzerren, und bei manchen Kugeln kann die Wahrscheinlichkeit des Herausfallens höher/niedriger sein als bei anderen. Lassen Sie uns diese Hypothese in der Praxis testen.
Nehmen wir zum Beispiel Daten zu allen 2020 von 21 Lotterieziehungen, die in 6-45 stattfanden, von der Website ihres Veranstalters Stoloto, die in Form einer solchen „intelligenten“ Tabelle mit dem Namen gestaltet ist, die für die Analyse geeignet ist tabArchivumlauf. Розыгрыши проходят 11 раза в день (im 11. September und im XNUMX. Dezember), т.е. In dieser Tabelle finden Sie die von Ihnen erstellten Analysetools:
Verwenden Sie die Funktion, um die Häufigkeit des Auftretens jeder Zahl zu berechnen COUNTIF (ZÄHLENWENN) und füge eine Funktion hinzu TEXT (TEXT)um führende Nullen und Sternchen vor und nach einstelligen Zahlen hinzuzufügen, sodass ZÄHLENWENN nach dem Vorkommen einer Zahl irgendwo in der Kombination in Spalte B sucht. Außerdem werden wir zur besseren Übersichtlichkeit ein Diagramm nach Ergebnissen erstellen und die Häufigkeiten sortieren in absteigender Reihenfolge:
Im Durchschnitt sollte jede Kugel 1459 Züge * 6 Kugeln / 45 Zahlen = 194,53 Mal fallen (genau so heißt es in der Statistik математическим ожиданием. использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.
Aufgabe 3. Welche Zahlen wurden lange nicht gezogen?
Eine andere Strategie basiert auf der Idee, dass bei einer ausreichend großen Anzahl von Ziehungen früher oder später jede Zahl von allen verfügbaren Zahlen von 1 bis 45 herausfallen sollte. Wenn also einige Zahlen schon lange nicht mehr unter den Gewinnern aufgetaucht sind („kalte Bälle“), dann ist es logisch, in Zukunft zu versuchen, darauf zu setzen.
Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей за 2020-21 год по убывани ю даты и использовать функцию AUSGESETZTER (PASSEN). Es wird von oben nach unten (dh von neuen zu alten Läufen) gesucht, um nach jeder Nummer zu suchen und die Seriennummer des Laufs (gezählt vom Ende des Jahres bis zum Anfang) auszugeben, bei dem diese Nummer zuletzt abgelegt wurde:
Schritt 4. Der Generator des Schlüsselgehäuses
Eine andere Spielstrategie basiert darauf, den psychologischen Faktor beim Zahlenraten zu eliminieren. Wenn ein Spieler Zahlen wählt, indem er seinen Einsatz macht, tut er dies unbewusst nicht ganz rational. Laut Statistik werden zum Beispiel Zahlen von 1 bis 31 70% häufiger gewählt als der Rest (Lieblingsdaten), 13 wird seltener gewählt (verdammtes Dutzend), Zahlen mit den „glücklichen“ Sieben werden häufiger gewählt usw. Aber wir spielen gegen eine Maschine (Lottotrommel), bei der alle Zahlen gleich sind, also ist es sinnvoll, sie mit der gleichen mathematischen Unvoreingenommenheit zu wählen, um unsere Chancen auszugleichen. Dazu müssen wir in Excel einen Generator für zufällige und – am wichtigsten – sich nicht wiederholende Zahlen erstellen:
Um dies zu tun:
- Lassen Sie uns eine „intelligente“ Tabelle mit dem Namen erstellen Tabellengenerator, wobei die erste Spalte unsere Zahlen von 1 bis 45 sein wird.
- Geben Sie in der zweiten Spalte das Gewicht für jede Zahl ein (wir brauchen es etwas später). Wenn für uns alle Zahlen gleich wertvoll sind und wir sie mit gleicher Wahrscheinlichkeit wählen wollen, dann kann das Gewicht überall gleich 1 gesetzt werden.
- In der dritten Spalte verwenden wir die Funktion SLCHIS (RAND), wird in Excel automatisch zwischen 0 und 1 generiert und befindet sich auf der vorherigen Seite. Dies geschieht nur, wenn Sie die auf der Tastatur angezeigte Liste lesen F9) wird ein neuer Satz von 45 Zufallszahlen generiert, wobei das Gewicht für jede von ihnen berücksichtigt wird.
- Fügen wir eine vierte Spalte hinzu, in der die Funktion verwendet wird RANK (RANG) Wählen Sie den Bereich (Position im Ziel) für die Person aus.
Nun bleibt noch, mit der Funktion eine Auswahl der ersten sechs Zahlen nach Rang 6 zu treffen AUSGESETZTER (PASSEN):
Nach dem Klicken auf die Tastatur F9 Die Formeln auf dem Excel-Blatt werden neu berechnet und jedes Mal erhalten wir einen neuen Satz von 6 Zahlen in grünen Zellen. Außerdem erhalten die Zahlen, für die in Spalte B ein größeres Gewicht gesetzt wurde, einen proportional höheren Rang und erscheinen somit häufiger in den Ergebnissen unserer Stichprobe. Wenn das Gewicht für alle Zahlen gleich eingestellt ist, werden alle mit der gleichen Wahrscheinlichkeit ausgewählt. Auf diese Weise erhalten wir einen fairen und unvoreingenommenen Zufallszahlengenerator von 6 aus 45, aber mit der Möglichkeit, bei Bedarf Anpassungen an der Zufälligkeit der Verteilung vorzunehmen.
Wenn wir uns entscheiden, bei jeder Ziehung nicht mit einem, sondern beispielsweise mit zwei Tickets gleichzeitig zu spielen, bei denen wir jeweils nicht wiederkehrende Zahlen auswählen, können wir einfach zusätzliche Linien von unten zum grünen Bereich hinzufügen. Hinzufügen von 6, 12, 18 usw. zum Rang. d. beziehungsweise:
Aufgabe 5. Lotteriesimulator in Excel
Lassen Sie uns als Apotheose dieses ganzen Themas einen vollwertigen Lotteriesimulator in Excel erstellen, in dem Sie beliebige Strategien ausprobieren und die Ergebnisse vergleichen können (in der Optimierungstheorie wird etwas Ähnliches auch als Monte-Carlo-Methode bezeichnet, aber es wird einfacher sein für uns).
Um alles so realitätsnah wie möglich zu machen, stellen Sie sich für einen Moment vor, es ist der 1. Januar 2022 und wir haben die diesjährigen Ziehungen vor uns, an denen wir teilnehmen möchten. Ich habe die wirklich fallengelassenen Zahlen in die Tabelle eingetragen tablTiraži2022, wobei die zusätzlich gezogenen Zahlen zur Erleichterung nachfolgender Berechnungen in separate Spalten voneinander getrennt werden:
Auf einem separaten Blatt Spiel Erstellen Sie einen Rohling zum Modellieren in Form einer „intelligenten“ Tabelle mit dem Namen tabIgra folgendes Formular:
Hier:
- In den gelben Zellen oben legen wir für das Makro die Anzahl der Ziehungen im Jahr 2022 fest, an denen wir teilnehmen möchten (1-82) und die Anzahl der Lose, die wir bei jeder Ziehung spielen.
- Die Daten für die ersten 11 Spalten (AJ) werden vom Makro aus dem Ziehungsblatt 2022 kopiert.
- Daten für die nächsten sechs Spalten (KP) nimmt das Makro aus dem Blatt Stromerzeuger, wo wir einen Zufallszahlengenerator implementiert haben (siehe Problem 4 oben).
- In Spalte Q zählen wir die Anzahl der Übereinstimmungen zwischen den gelöschten Zahlen und denen, die mit der Funktion generiert wurden SUMMENPRODUKT (SUMMENPRODUKT).
- В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, если выиграли, то приз) т.
- In der letzten Spalte S betrachten wir das Gesamtergebnis des gesamten Spiels als kumulierte Summe, um die Dynamik im Verlauf zu sehen.
Und um diese ganze Struktur wiederzubeleben, brauchen wir ein kleines Makro. Auf der Registerkarte Entwickler (Entwickler) wähle ein Team Visual Basic oder verwenden Sie die Tastenkombination Andere+F11. Fügen Sie dann über das Menü ein neues leeres Modul hinzu Einfügen – Modul und dort folgenden Code eingeben:
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"). (1, 1) .Copy -Ziel: = wsgame.cells (i, 2022) 'к пwirkungen и и в в втт з з copy в в в ы ы ы ы ы ы н н & р р & р р & р р & р р & р р & р р & р р & р н & р н & р н & р нkunft (н нkunft)' к пртт & р нkunft (л н & р оkunft). .PasteSpecial Paste:=xlPasteValues i = i + 1 Next b Next t End Sub Es bleibt, die gewünschten Anfangsparameter in die gelben Zellen einzugeben und das Makro auszuführen Entwickler – Makros (Entwickler – Makros) oder Tastenkürzel Andere+F8.
Zur Verdeutlichung können Sie auch ein Diagramm für die letzte Spalte mit einer kumulativen Summe erstellen, die die Änderung des Geldbestands während des Spiels widerspiegelt:
Vergleich verschiedener Strategien
Jetzt können Sie mit dem erstellten Simulator jede Spielstrategie bei echten Ziehungen im Jahr 2022 testen und die Ergebnisse sehen, die sie bringen würden. Wenn Sie in jeder Ziehung 1 Tipp spielen, dann sieht das Gesamtbild der „Pflaume“ in etwa so aus:
Hier:
- Stromerzeuger ist ein Spiel, bei dem wir bei jeder Ziehung Zufallszahlen auswählen, die von unserem Generator erstellt wurden (mit dem gleichen Gewicht).
- Favoriten ist ein Spiel, bei dem wir bei jeder Ziehung dieselben Zahlen verwenden – diejenigen, die in den letzten zwei Jahren am häufigsten in Ziehungen gefallen sind (27, 32, 11, 14, 34, 40).
- Outsiders – das gleiche, aber wir verwenden die seltensten Dropdown-Zahlen (12, 18, 26, 10, 21, 6).
- Kälte – Bei allen Ziehungen verwenden wir Zahlen, die schon lange nicht mehr gefallen sind (35, 5, 39, 11, 6, 29).
Wie Sie sehen, gibt es keinen großen Unterschied, aber der Zufallszahlengenerator verhält sich etwas besser als die anderen „Strategien“.
Es ist auch möglich, das Spielen mehrerer Gruppen von Spielen in der Regel zu Hause durchzuführen, da viele Gruppenvarianten (vorübergehend für diese kleine Gruppe von Spielern) erstellt werden müssen ъединяются в группу).
Spielen in jeder Ziehung mit einem Ticket mit zufällig generierten Zahlen (mit dem gleichen Gewicht):
Spielen von 10 Tickets in jeder Ziehung mit zufällig generierten Zahlen (mit dem gleichen Gewicht):
Spielen von 100 Tickets in jeder Ziehung mit Zufallszahlen (mit dem gleichen Gewicht):
Kommentare sind, wie man so schön sagt, überflüssig – ein Pfandabfluss ist in jedem Fall unvermeidlich 🙂