Loterijsimulatie in Excel

De loterij is geen jacht op geluk,

het is een jacht op verliezers.

Met benijdenswaardige regelmaat (en de laatste tijd vaker) schrijven mensen me om hulp bij verschillende berekeningen met betrekking tot loterijen. Iemand wil zijn geheime algoritme voor het selecteren van winnende nummers in Excel implementeren, iemand wil patronen vinden in de nummers die uit eerdere trekkingen zijn gevallen, iemand wil de organisatoren van de loterij betrappen in een oneerlijk spel.

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

Taak 1. Kans om te winnen

Laten we als voorbeeld de klassieke Stoloto 6 van de 45 loterij nemen. Volgens de regels ontvangen alleen degenen die alle 10 nummers van de 6 hebben geraden een superprijs (45 miljoen roebel of meer als het saldo van het prijzengeld is opgebouwd uit eerdere trekkingen). Als je 5 hebt geraden, ontvang je 150 duizend roebel, als 4 - 1500 roebel. , als 3 nummers van de 6, dan 150 roebel, als 2 nummers - u 50 roebel besteed aan het ticket terug. Raad er maar één of geen - haal alleen endorfines uit het spelproces.

De wiskundige kans om te winnen kan eenvoudig worden berekend met behulp van de standaardfunctie NUMMERCOMB (COMBINEREN), die voor een dergelijk geval beschikbaar is in Microsoft Excel. Deze functie berekent het aantal combinaties van N getallen uit M. Dus voor onze "6 van de 45" loterij zou het zijn:

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

… wat gelijk is aan 8, het totale aantal van alle mogelijke combinaties in deze loterij.

Als je de kans op een gedeeltelijke winst wilt berekenen (2-5 getallen van de 6), dan moet je eerst het aantal van dergelijke opties berekenen, dat gelijk is aan het product van het aantal combinaties van geraden getallen uit 6 door het aantal niet-geraden nummers van de resterende (45-6) = 39 nummers. Vervolgens delen we het totale aantal van alle mogelijke combinaties (8) door het ontvangen aantal overwinningen voor elke optie - en we krijgen de winkansen voor elk geval:

Overigens wordt de kans om bijvoorbeeld om te komen bij een vliegtuigongeluk in Ons Land geschat op ongeveer 1 op een miljoen. En de kans om te winnen in een casino bij roulette, alles inzetten op één nummer is 1 tot 37.

Als al het bovenstaande je niet heeft tegengehouden en je nog steeds klaar bent om verder te spelen, ga dan verder.

Taak 2. Frequentie van voorkomen van elk nummer

Laten we om te beginnen bepalen met welke frequentie bepaalde getallen uitvallen. In een ideale loterij, gegeven een voldoende groot tijdsinterval voor analyse, zouden alle ballen dezelfde kans moeten hebben om in de winnende steekproef te komen. In werkelijkheid kunnen de ontwerpkenmerken van de loterijtrommel en de gewichtsvorm van de ballen dit beeld vertekenen, en voor sommige ballen kan de kans dat ze eruit vallen hoger/lager zijn dan voor andere. Laten we deze hypothese in de praktijk testen.

Laten we bijvoorbeeld gegevens nemen over alle 2020 van de 21 loterijtrekkingen die plaatsvonden in 6-45 van de website van hun organisator Stoloto, ontworpen in de vorm van zo'n "slimme" tabel, handig voor analyse, met de naam tabArchief Oplage. озыгрыши проходят два раза в день (в 11 утра и в 11 вечера), .е. ой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка для анализа:

Gebruik de functie om de frequentie van voorkomen van elk nummer te berekenen COUNTIF (AANTAL.ALS) en voeg er een functie aan toe TEKST (TEKST)om voorloopnullen en sterretjes voor en na toe te voegen aan enkelcijferige getallen, zodat AANTAL.ALS zoekt naar het voorkomen van een getal ergens in de combinatie in kolom B. Voor meer duidelijkheid zullen we ook een grafiek maken op resultaten en de frequenties sorteren in aflopende volgorde:

Gemiddeld zou elke bal 1459 trekkingen * 6 ballen / 45 nummers = 194,53 keer moeten vallen (dit is precies wat in de statistieken wordt genoemd атематическим ожиданием), о хорошо видно, о некоторые числа (27, 32, 11…) выпадали заметно аще (+18%), а екоторые (10, 21, 6…) аоборетнс е аозоретнес е оответственно, ожно попробовать использовать информацию стратегии выигрыша, т.е. ибо ставить а те шары, что выпадают аще, либо наоборот — елать ставку на редко выпадающие шары в адеждаааоеи, еать ставку на едко выпадающие шары в аденаааоеи,

Opdracht 3. Welke nummers zijn er al lang niet meer getrokken?

Een andere strategie is gebaseerd op het idee dat bij een voldoende groot aantal trekkingen, vroeg of laat elk nummer van alle beschikbare 1 tot 45 uit moet vallen. Dus als sommige nummers lange tijd niet bij de winnaars zijn verschenen (“koude ballen”), dan is het logisch om in de toekomst te proberen erop te wedden. 

ожно легко найти все давно не выпадавшие номера, если отсортировать аш архив тиражей за 2020-21 MEER BLOOTGESTELD (WEDSTRIJD). Het zal van boven naar beneden zoeken (dwz van nieuwe naar oude runs) om naar elk nummer te zoeken en het serienummer van de run te geven (geteld vanaf het einde van het jaar tot het begin) waar dit nummer voor het laatst is gevallen:

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

Een andere spelstrategie is gebaseerd op het elimineren van de psychologische factor bij het raden van getallen. Wanneer een speler getallen kiest door zijn inzet te doen, doet hij dit onbewust niet geheel rationeel. Volgens statistieken worden bijvoorbeeld getallen van 1 tot 31 70% vaker gekozen dan de rest (favoriete datums), 13 wordt minder vaak gekozen (verdomd dozijn), getallen met de "gelukkige" zeven worden vaker gekozen, enz. Maar we spelen tegen een machine (loterijtrommel) waarvoor alle nummers hetzelfde zijn, dus het is logisch om ze met dezelfde wiskundige onpartijdigheid te kiezen om onze kansen gelijk te maken. Om dit te doen, moeten we een generator van willekeurige en - belangrijker nog - niet-herhalende getallen in Excel maken:

Om dit te doen:

1. Laten we een "slimme" tabel maken met de naam tafelGenerator, waarbij de eerste kolom onze getallen van 1 tot 45 zijn.
2. Voer in de tweede kolom het gewicht voor elk nummer in (we hebben het iets later nodig). Als alle getallen voor ons even waardevol zijn en we willen ze met gelijke waarschijnlijkheid kiezen, dan kan het gewicht overal gelijk op 1 worden gezet.
3. In de derde kolom gebruiken we de functie SLCHIS (RAND), которая Excel генерирует случайное дробное число от 0 о 1, обавив нему вес из предыдущего столбца. аким образом каждый раз при пересчёте листа (нажатии на клавишу F9) een nieuwe set van 45 willekeurige getallen wordt gegenereerd, rekening houdend met het gewicht voor elk van hen.
4. Laten we een vierde kolom toevoegen, waarbij we de functie gebruiken RANK (RANG) ислим ранг (позицию в топе) аждого из чисел.

Nu rest het nog om een ​​selectie te maken van de eerste zes nummers op rang 6 met behulp van de functie MEER BLOOTGESTELD (WEDSTRIJD):

и ажатии а авишу F9 de formules op het Excel-blad worden opnieuw berekend en elke keer krijgen we een nieuwe set van 6 cijfers in groene cellen. Bovendien zullen de getallen waarvoor in kolom B een hoger gewicht is ingesteld, een verhoudingsgewijs hogere rangorde krijgen en dus vaker voorkomen in de resultaten van onze willekeurige steekproef. Als het gewicht voor alle getallen op hetzelfde is ingesteld, worden ze allemaal met dezelfde waarschijnlijkheid geselecteerd. Op deze manier krijgen we een eerlijke en onbevooroordeelde generator van willekeurige getallen van 6 uit 45, maar met de mogelijkheid om de willekeurigheid van de verdeling indien nodig aan te passen.

Als we besluiten om in elke trekking niet met één, maar bijvoorbeeld met twee tickets tegelijk te spelen, waarbij we elk niet-herhalende nummers zullen kiezen, dan kunnen we eenvoudig extra lijnen van de onderkant naar de groene reeks toevoegen, 6, 12, 18, enz. aan de rang toevoegen. d. respectievelijk:

Taak 5. Loterijsimulator in Excel

Laten we als apotheose van dit hele onderwerp een volwaardige loterijsimulator in Excel maken, waar u alle strategieën kunt uitproberen en de resultaten kunt vergelijken (in de optimalisatietheorie wordt iets soortgelijks ook wel de Monte Carlo-methode genoemd, maar het zal eenvoudiger zijn voor ons).

Om alles zo dicht mogelijk bij de realiteit te brengen, moet je je even voorstellen dat het 1 januari 2022 is en dat we de trekkingen van dit jaar voor de boeg hebben, waarin we van plan zijn te spelen. Ik heb de echte gevallen nummers in de tabel ingevoerd tablTiraži2022, waarbij de extra getrokken getallen van elkaar worden gescheiden in afzonderlijke kolommen voor het gemak van latere berekeningen:

Op een apart blad Spel maak een blanco voor modellering in de vorm van een "slimme" tabel met de naam tabIgra het volgende formulier:

Hier:

• In de gele cellen hierboven stellen we voor de macro het aantal trekkingen in 2022 waaraan we willen deelnemen (1-82) en het aantal loten dat we in elke trekking spelen.
• De gegevens voor de eerste 11 kolommen (AJ) worden gekopieerd door de macro uit het 2022-tekenblad.
• Gegevens voor de volgende zes kolommen (KP) die de macro uit het blad haalt Generator, waar we een generator voor willekeurige getallen hebben geïmplementeerd (zie probleem 4 hierboven).
• In kolom Q tellen we het aantal overeenkomsten tussen de verwijderde nummers en de nummers die zijn gegenereerd met behulp van de functie SUMPRODUCT (SOMPRODUCT).
• столбце R вычисляем финансовый результат (если не играли, то минус 50 ей за билет, если выиграли, о иза 50 илеза)
• In de laatste kolom S beschouwen we het totale resultaat van het hele spel als een cumulatief totaal om de dynamiek in het proces te zien.

En om deze hele structuur nieuw leven in te blazen, hebben we een kleine macro nodig. Op het tabblad ontwikkelaar (Ontwikkelaar) kies een team Visual Basic of gebruik de sneltoets anders+F11. Voeg dan een nieuwe lege module toe via het menu Invoegen – Module en voer daar de volgende code in:

Subloterij () dim igames als geheel getal, iTickets als integer, ik zo lang, als gehele getal, b als gehele getal 'овляем перемемеменные дwoord сылки на листы set wsgame = worksEets ("ament WSNUMBers (" ии и ио то то то т то € set WSNUMBers (" wsArchive = Werkbladen("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билао иаоме ааоме ("5:6").Verwijder 'очищаем старые данные For t = 1048576 To iGames For b = 1 To iTickets 'копируем выигравшие номера с листа Тиража Тиражи 1 имснаае (2022, 1).Copy Destination:=wsGame.Cells(i, 1) 'копируем и вставляем специальной вставкой значений сгенерированные номера с лисем специальной вставкой значений сгенерированные номера с лисеатоГенисаГоГенаой гачений сгенерированные номера с лисеатоГенсеатоГенеа .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Next b Next t End Sub  

Het blijft om de gewenste initiële parameters in de gele cellen in te voeren en de macro door te voeren Ontwikkelaar – Macro's (Ontwikkelaar — Macro's) of sneltoets anders+F8.

Voor de duidelijkheid kun je ook een diagram maken voor de laatste kolom met een cumulatief totaal, dat de verandering in het geldsaldo tijdens het spel weerspiegelt:

Vergelijking van verschillende strategieën

Nu kun je met behulp van de gemaakte simulator elke spelstrategie testen op echte trekkingen in 2022 en de resultaten zien die het zou opleveren. Als je in elke trekking 1 lot speelt, ziet het algemene beeld van de "pruim" er ongeveer zo uit:

Hier:

• Generator is een spel waarbij we bij elke trekking willekeurige getallen kiezen die door onze generator zijn gemaakt (met hetzelfde gewicht).
• Favorieten is een spel waarbij we in elke trekking dezelfde nummers gebruiken - de nummers die de afgelopen twee jaar het vaakst zijn gevallen in trekkingen (27, 32, 11, 14, 34, 40).
• Buitenstaanders – hetzelfde, maar we gebruiken de meest zeldzame vervolgkeuzenummers (12, 18, 26, 10, 21, 6).
• Koud – in alle trekkingen gebruiken we getallen die al lang niet meer uitgevallen zijn (35, 5, 39, 11, 6, 29).

Zoals je kunt zien, is er geen groot verschil, maar de generator voor willekeurige getallen gedraagt ​​​​zich iets beter dan de andere "strategieën".

ожно также попробовать играть большим количеством билетов в аждом тираже, чтобы перекрыть большее количеснтооварииоовариио ииесом

Spelen in elke trekking met één lot met willekeurig gegenereerde nummers (met hetzelfde gewicht):

10 loten spelen in elke trekking met willekeurig gegenereerde nummers (met hetzelfde gewicht):

100 loten spelen in elke trekking met willekeurige getallen (met hetzelfde gewicht):

Opmerkingen, zoals ze zeggen, zijn overbodig - een afzettingsafvoer is in alle gevallen onvermijdelijk 🙂