Lotteri simulering i Excel

Lotteriet är inte en jakt på tur,

det är en jakt på förlorare.

Med avundsvärd regelbundenhet (och oftare på senare tid) skriver folk till mig och ber om hjälp med olika beräkningar relaterade till lotterier. Någon vill implementera sin hemliga algoritm för att välja vinnande nummer i Excel, någon vill hitta mönster i siffrorna som fallit ur tidigare dragningar, någon vill fånga arrangörerna av lotteriet i ett oärligt spel.

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

Uppgift 1. Sannolikhet att vinna

Låt oss ta det klassiska Stoloto lotteriet 6 av 45 som exempel. Enligt reglerna får endast de som gissade alla 10 nummer av 6 ett superpris (45 miljoner rubel eller mer om saldot i prisfonden har samlats från tidigare dragningar). Om du gissade 5, kommer du att få 150 tusen rubel, om 4 - 1500 rubel. , om 3 nummer av 6, sedan 150 rubel, om 2 nummer – du kommer att returnera 50 rubel som spenderats på biljetten. Gissa bara en eller ingen – få bara endorfiner från spelprocessen.

Den matematiska sannolikheten att vinna kan enkelt beräknas med hjälp av standardfunktionen NUMBERCOMB (KOMBINERA), som är tillgänglig i Microsoft Excel för ett sådant fall. Den här funktionen beräknar antalet kombinationer av N nummer av M. Så för vårt "6 av 45"-lotteri skulle det vara:

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

… vilket är lika med 8, det totala antalet av alla möjliga kombinationer i detta lotteri.

Om du vill beräkna sannolikheten för en partiell vinst (2-5 nummer av 6), måste du först beräkna antalet sådana alternativ, vilket är lika med produkten av antalet kombinationer av gissade nummer av 6 av antalet otippade siffror av de återstående (45-6) = 39 siffror. Sedan dividerar vi det totala antalet av alla möjliga kombinationer (8) med det mottagna antalet vinster för varje alternativ – och vi får vinstsannolikheterna för varje fall:

Sannolikheten att till exempel dö i en flygolycka i Vårt Land uppskattas för övrigt till cirka 1 på miljonen. Och sannolikheten för att vinna på ett kasino på roulette, satsa allt på ett nummer är 1 till 37.

Om allt ovanstående inte hindrade dig och du fortfarande är redo att spela vidare, fortsätt.

Uppgift 2. Frekvens av förekomst av varje nummer

Till att börja med, låt oss bestämma med vilken frekvens vissa nummer faller ut. I ett idealiskt lotteri, givet ett tillräckligt stort tidsintervall för analys, bör alla bollar ha samma sannolikhet att vara i det vinnande provet. I verkligheten kan lotteritrummans designegenskaper och bollarnas vikt förvränga denna bild, och för vissa bollar kan sannolikheten att falla ut mycket väl vara högre/lägre än för andra. Låt oss testa denna hypotes i praktiken.

Låt oss till exempel ta data om alla 2020 av 21 lotteridragningar som ägde rum i 6-45 från webbplatsen för deras arrangör Stoloto, designad i form av en sådan "smart" tabell, bekväm för analys, med namnet tabArkiv Cirkulation. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выборка för анализа:

För att beräkna frekvensen av förekomsten av varje nummer, använd funktionen COUNTIF (COUNTIF) och lägg till en funktion till den TEXT (TEXT)att lägga till inledande nollor och asterisker före och efter till ensiffriga tal, så att COUNTIF letar efter förekomsten av ett tal var som helst i kombinationen i kolumn B. För större tydlighet bygger vi också ett diagram efter resultat och sorterar frekvenserna i fallande ordning:

I genomsnitt bör varje boll falla 1459 drag * 6 bollar / 45 nummer = 194,53 gånger (det är precis vad som kallas i statistiken математическим ожиданием), но хорошо видно, что нек- Соответственно, можно попробовать использовать эту иinfomацию för стратегии выигрыша, т.е. лиimin

Uppgift 3. Vilka nummer har inte dragits på länge?

En annan strategi bygger på tanken att med ett tillräckligt stort antal dragningar, förr eller senare borde varje nummer från alla tillgängliga från 1 till 45 falla ut. Så om några nummer inte har dykt upp bland vinnarna på länge ("kalla bollar"), så är det logiskt att försöka satsa på dem i framtiden. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей den 2020-21 MER EXPONERAD (MATCH). Den kommer att söka uppifrån och ned (dvs. från nya till gamla körningar) för att söka efter varje nummer och ge ut serienumret för körningen (räknat från slutet av året till början) där detta nummer senast släpptes:

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

En annan spelstrategi bygger på att eliminera den psykologiska faktorn när man gissar siffror. När en spelare väljer siffror genom att göra sin insats gör han detta undermedvetet inte helt rationellt. Enligt statistik väljs till exempel nummer från 1 till 31 70% oftare än resten (favoritdatum), 13 väljs mer sällan (jävligt dussin), nummer som innehåller de "lyckliga" sju väljs oftare, etc. Men vi spelar mot en maskin (lotteritrumma) där alla nummer är lika, så det är vettigt att välja dem med samma matematiska opartiskhet för att utjämna våra chanser. För att göra detta måste vi skapa en generator av slumpmässiga och – viktigast av allt – icke-upprepande tal i Excel:

Att göra detta:

1. Låt oss skapa en "smart" tabell med namnet tabellGenerator, där den första kolumnen kommer att vara våra siffror från 1 till 45.
2. I den andra kolumnen anger du vikten för varje nummer (vi kommer att behöva det lite senare). Om alla siffror är lika värda för oss och vi vill välja dem med lika stor sannolikhet, så kan vikten sättas lika med 1 överallt.
3. I den tredje kolumnen använder vi funktionen SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 till 1, добавив к нему вес из предыдущего столбц. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) en ny uppsättning med 45 slumptal kommer att genereras, med hänsyn till vikten för vart och ett av dem.
4. Låt oss lägga till en fjärde kolumn, där vi använder funktionen RANG (RANG) вычислим ранг (позицию в топе) för каждого из чисел.

Nu återstår att göra ett urval av de första sex siffrorna genom att rangordna 6 med funktionen MER EXPONERAD (MATCH):

При нажатии на клавишу F9 formlerna på Excel-arket kommer att räknas om och varje gång får vi en ny uppsättning med 6 siffror i gröna celler. Dessutom kommer de siffror för vilka en större vikt sattes i kolumn B att få en proportionellt högre rankning och därmed förekomma oftare i resultaten av vårt slumpmässiga urval. Om vikten för alla nummer är inställd på samma, kommer alla att väljas med samma sannolikhet. På så sätt får vi en rättvis och opartisk slumptalsgenerator på 6 av 45, men med möjlighet att göra justeringar av fördelningens slumpmässighet vid behov.

Om vi ​​bestämmer oss för att spela i varje dragning inte med en, utan till exempel med två biljetter samtidigt, i var och en av vilka vi kommer att välja icke-repeterande nummer, så kan vi helt enkelt lägga till ytterligare rader från botten till det gröna intervallet, lägga till 6, 12, 18, etc. till rangordningen. d. respektive:

Uppgift 5. Lottersimulator i Excel

Som en apoteos av hela detta ämne, låt oss skapa en fullfjädrad lotterisimulator i Excel, där du kan prova alla strategier och jämföra resultaten (i optimeringsteorin kallas något liknande också Monte Carlo-metoden, men det kommer att vara enklare för oss).

För att göra allt så nära verkligheten som möjligt, föreställ dig för ett ögonblick att det är 1 januari 2022 och vi har årets dragningar framför oss, där vi planerar att spela. Jag skrev in de verkliga nedlagda siffrorna i tabellen tablTiraži2022, separera de ytterligare ritade siffrorna från varandra i separata kolumner för att underlätta efterföljande beräkningar:

På ett separat blad Spel skapa ett tomt för modellering i form av ett "smart" bord med namnet tabIgra följande formulär:

Här:

• I de gula cellerna ovan kommer vi att ställa in för makrot antalet dragningar under 2022 som vi vill delta i (1-82) och antalet lotter vi spelar i varje dragning.
• Data för de första 11 kolumnerna (AJ) kommer att kopieras av makrot från 2022 ritningsarket.
• Data för de kommande sex kolumnerna (KP) kommer makrot att hämta från arket Generator, där vi har implementerat en slumptalsgenerator (se problem 4 ovan).
• I kolumn Q räknar vi antalet matchningar mellan de tappade siffrorna och de som genereras med funktionen SUMPRODUCT (SUMMAPRODUKT).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей за билет, есливилтат, есливириграли, то минус 50 рублей за билет, есливириграли —лесливириграли, за минус XNUMX рублей за билет, есливириграли, за XNUMX рублей за билет, есливир, за XNUMX р.
• I den sista kolumnen S betraktar vi det totala resultatet av hela spelet som en kumulativ summa för att se dynamiken i processen.

Och för att återuppliva hela den här strukturen behöver vi ett litet makro. På fliken utvecklare (Utvecklaren) välja ett lag Visual Basic eller använd kortkommandon andra+F11. Lägg sedan till en ny tom modul via menyn Infoga – Modul och skriv in följande kod där:

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

Det återstår att ange de önskade initiala parametrarna i de gula cellerna och köra makrot igenom Utvecklare – Makron (Utvecklare – Makron) eller kortkommandon andra+F8.

För tydlighetens skull kan du också bygga ett diagram för den sista kolumnen med en ackumulerad summa, som återspeglar förändringen i pengabalansen under spelet:

Jämförelse av olika strategier

Nu, med hjälp av den skapade simulatorn, kan du testa vilken spelstrategi som helst på riktiga dragningar 2022 och se resultaten som den skulle ge. Om du spelar 1 lott i varje dragning ser den övergripande bilden av "plommonet" ut ungefär så här:

Här:

• Generator är ett spel där vi i varje dragning väljer slumpmässiga nummer skapade av vår generator (med samma vikt).
• Favoriter är ett spel där vi i varje dragning använder samma siffror – de som oftast fallit ut i drag de senaste två åren (27, 32, 11, 14, 34, 40).
• Utomstående – samma sak, men vi använder de mest sällsynta rullgardinsnumren (12, 18, 26, 10, 21, 6).
• Förkylning – i alla dragningar använder vi nummer som inte har fallit ut på länge (35, 5, 39, 11, 6, 29).

Som du kan se är det ingen stor skillnad, men slumptalsgeneratorn beter sig lite bättre än de andra "strategierna".

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

Att spela i varje dragning med en lott med slumpmässigt genererade nummer (med samma vikt):

Spela 10 lotter i varje dragning med slumpmässigt genererade nummer (med samma vikt):

Spela 100 lotter i varje dragning med slumpmässiga nummer (med samma vikt):

Kommentarer är som de säger överflödiga – ett pantavlopp är oundvikligt i alla fall 🙂