Simularea loteriei în Excel

Loteria nu este o vânătoare de noroc,

este o vânătoare de învinși.

Cu o regularitate de invidiat (și mai des în ultimul timp), oamenii îmi scriu cerându-mi ajutor în diverse calcule legate de loterie. Cineva vrea să-și implementeze algoritmul secret pentru selectarea numerelor câștigătoare în Excel, cineva vrea să găsească modele în numerele care au căzut din extragerile trecute, cineva vrea să-i prindă pe organizatorii loteriei într-un joc necinstit.

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

Sarcina 1. Probabilitatea de a câștiga

Să luăm ca exemplu loteria clasică Stoloto 6 din 45. Conform regulilor, doar cei care au ghicit toate cele 10 numere din 6 primesc un super premiu (45 de milioane de ruble sau mai mult dacă soldul fondului de premii s-a acumulat din extragerile anterioare). Dacă ați ghicit 5, veți primi 150 de mii de ruble, dacă 4 – 1500 de ruble. , dacă 3 numere din 6, atunci 150 de ruble, dacă 2 numere – vei returna 50 de ruble cheltuite pe bilet. Ghiciți doar una sau niciuna - obțineți doar endorfine din procesul de joc.

Probabilitatea matematică de câștig poate fi calculată cu ușurință folosind funcția standard NUMBERCOMB (COMBINA), care este disponibil în Microsoft Excel pentru un astfel de caz. Această funcție calculează numărul de combinații de N numere din M. Deci, pentru loteria noastră „6 din 45” ar fi:

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

… care este egal cu 8, numărul total al tuturor combinațiilor posibile din această loterie.

Dacă doriți să calculați probabilitatea unui câștig parțial (2-5 numere din 6), atunci va trebui mai întâi să calculați numărul de astfel de opțiuni, care este egal cu produsul numărului de combinații de numere ghicite din 6 după numărul de numere neghicit din restul (45-6) = 39 de numere. Apoi împărțim numărul total al tuturor combinațiilor posibile (8) la numărul de câștiguri primit pentru fiecare opțiune – și obținem probabilitățile de câștig pentru fiecare caz:

Apropo, probabilitatea, de exemplu, de a muri într-un accident de avion în țara noastră este estimată la aproximativ 1 la un milion. Și probabilitatea de a câștiga într-un cazinou la ruletă, pariând totul pe un număr este de la 1 la 37.

Dacă toate cele de mai sus nu v-au oprit și sunteți încă gata să jucați mai departe, continuați.

Sarcina 2. Frecvența de apariție a fiecărui număr

Pentru început, să stabilim cu ce frecvență cad anumite numere. Într-o loterie ideală, având în vedere un interval de timp suficient de mare pentru analiză, toate bilele ar trebui să aibă aceeași probabilitate de a fi în eșantionul câștigător. În realitate, caracteristicile de design ale tobei de loterie și forma greutății bilelor pot distorsiona această imagine, iar pentru unele bile probabilitatea de a cădea poate fi mai mare/mai mică decât pentru altele. Să testăm această ipoteză în practică.

Să luăm, de exemplu, datele despre toate 2020 din 21 de extrageri de loterie care au avut loc în 6-45 de pe site-ul organizatorului lor Stoloto, concepute sub forma unui astfel de tabel „inteligent”, convenabil pentru analiză, cu numele tabArhivă Circulație. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. в этой таблице у нас полторы тысячи тиражей-строк — вполне достаточная для начала выбия занлдорка

Pentru a calcula frecvența de apariție a fiecărui număr, utilizați funcția COUNTIF (COUNTIF) și adăugați-i o funcție TEXT (TEXT)pentru a adăuga zerouri și asteriscuri înainte și după numerelor cu o singură cifră, astfel încât COUNTIF să caute apariția unui număr oriunde în combinația din coloana B. De asemenea, pentru o mai mare claritate, vom construi o diagramă după rezultate și vom sorta frecvențele în ordine descrescătoare:

În medie, orice minge ar trebui să cadă 1459 extrageri * 6 bile / 45 de numere = 194,53 ori (așa este exact ceea ce se numește în statistici математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11 ...) ыыаacи заметно чаще (+18%), н чзаuter чаuter ча раo. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша. либо ставить на те шары, что ыыадаюю чаще, либо наоборот - делать с for шие.

Sarcina 3. Ce numere nu au fost extrase de mult timp?

O altă strategie se bazează pe ideea că, cu un număr suficient de mare de extrageri, mai devreme sau mai târziu fiecare număr din toate cele disponibile de la 1 la 45 ar trebui să cadă. Deci, dacă unele numere nu au mai apărut printre câștigători de multă vreme („bile reci”), atunci este logic să încerci să pariezi pe ele în viitor. 

Можно леëко найти все давно не ыыадавшие номера, если отсоражейватьаа дца зцц зц2020-21- MAI EXPUSĂ (MECI). Acesta va căuta de sus în jos (adică de la noile la vechile curse) pentru a căuta fiecare număr și va oferi numărul de serie al cursei (numărând de la sfârșitul anului până la început) unde acest număr a fost ultima dată:

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

O altă strategie de joc se bazează pe eliminarea factorului psihologic atunci când ghiciți numere. Când un jucător alege numere făcând pariul său, el subconștient face acest lucru nu în totalitate rațional. Conform statisticilor, de exemplu, numerele de la 1 la 31 sunt alese cu 70% mai des decât restul (date preferate), 13 este ales mai rar (la naiba de duzină), numerele care conțin șapte „norocoși” sunt mai des alese etc. Dar jucăm împotriva unei mașini (tobă de loterie) pentru care toate numerele sunt aceleași, așa că are sens să le alegem cu aceeași imparțialitate matematică pentru a ne egaliza șansele. Pentru a face acest lucru, trebuie să creăm un generator de numere aleatorii și – cel mai important – care nu se repetă în Excel:

Pentru a face acest lucru:

1. Să creăm un tabel „inteligent” numit tableGenerator, unde prima coloană va fi numerele noastre de la 1 la 45.
2. În a doua coloană, introduceți greutatea fiecărui număr (vom avea nevoie de ea puțin mai târziu). Dacă toate numerele sunt la fel de valoroase pentru noi și vrem să le alegem cu aceeași probabilitate, atunci ponderea poate fi setată egală cu 1 peste tot.
3. În a treia coloană folosim funcția SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предодыдусащ. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) se va genera un nou set de 45 de numere aleatorii, ținând cont de greutatea pentru fiecare dintre ele.
4. Să adăugăm o a patra coloană, unde folosim funcția RANG (RANG) вычислим ранг (позицию в топе) для каждого из чисел.

Acum rămâne să facem o selecție a primelor șase numere de rangul 6 folosind funcția MAI EXPUSĂ (MECI):

При нажатии на клавишу F9 formulele de pe foaia Excel vor fi recalculate si de fiecare data vom obtine un nou set de 6 numere in celule verzi. Mai mult, numerele pentru care a fost stabilită o pondere mai mare în coloana B vor primi un rang proporțional mai mare și, astfel, vor apărea mai des în rezultatele eșantionului nostru aleatoriu. Dacă ponderea tuturor numerelor este setată la aceeași, atunci toate vor fi selectate cu aceeași probabilitate. În acest fel obținem un generator de numere aleatoare corect și imparțial de 6 din 45, dar cu capacitatea de a face ajustări ale aleatoriei distribuției, dacă este necesar.

Dacă decidem să jucăm la fiecare extragere nu cu unul, ci, de exemplu, cu două bilete deodată, în fiecare dintre ele vom alege numere care nu se repetă, atunci putem pur și simplu să adăugăm linii suplimentare din partea de jos la gama verde, adăugând la rang 6, 12, 18 etc. d. respectiv:

Sarcina 5. Simulator de loterie în Excel

Ca o apoteoză a întregului subiect, să creăm un simulator de loterie cu drepturi depline în Excel, unde puteți încerca orice strategii și puteți compara rezultatele (în teoria optimizării, ceva similar se numește și metoda Monte Carlo, dar va fi mai simplu pentru noi).

Pentru a face totul cât mai aproape de realitate, imaginați-vă pentru o clipă că este 1 ianuarie 2022 și avem în față extragerile din acest an, în care plănuim să jucăm. Am introdus numerele reale pierdute în tabel tablTiraži2022, separând numerele extrase suplimentar unele de altele în coloane separate pentru confortul calculelor ulterioare:

Pe o foaie separată Joc creați un spațiu liber pentru modelare sub forma unui tabel „inteligent” cu numele tabIgra urmatoarea forma:

Aici:

• În celulele galbene de mai sus, vom seta pentru macro numărul de extrageri în 2022 la care dorim să participăm (1-82) și numărul de bilete pe care le jucăm la fiecare extragere.
• Datele pentru primele 11 coloane (AJ) vor fi copiate de macro-ul din foaia de desen 2022.
• Date pentru următoarele șase coloane (KP) macro-ul va prelua din foaie Generator, unde am implementat un generator de numere aleatorii (vezi problema 4 de mai sus).
• În coloana Q, numărăm numărul de potriviri dintre numerele aruncate și cele generate cu ajutorul funcției SUMPRODUCT (SUMAPRODUS).
• В столбце R вычисляем финансовый результат (если не выиграли, то минус 50 рублей зансовый результат) — В столбце
• În ultima coloană S, considerăm rezultatul general al întregului joc ca un total cumulat pentru a vedea dinamica procesului.

Și pentru a reînvia această structură, avem nevoie de un mic macro. Pe fila dezvoltator (Dezvoltator) alege o echipa Visual Basic sau utilizați comanda rapidă de la tastatură Alt+F11. Apoi adăugați un nou modul gol prin meniu Inserare – Modul și introduceți următorul cod acolo:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets ="Set wsгторнестра" wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576"). Ștergeți 'очищаея старые данные pentru t = 1 to Igames pentru b = 1 la iTickets' копируея ыи suflet, 2022, 1. (1, 1).Copy Destination:=wsGame.Cells(i, 10) 'копируем и вставляем специальной вставкой значений сгенерированные номера с листа Генератор wsNumbers.Range("G1:L4").Copy wsGame.Cells(i, 4) .PasteSpecial Paste:=xlPasteValues ​​i = i + 11 Următorul b Următorul t End Sub  

Rămâne să introduceți parametrii inițiali doriti în celulele galbene și să rulați macro-ul Dezvoltator – Macro-uri (Dezvoltator – Macrocomenzi) sau scurtătură de la tastatură Alt+F8.

Pentru claritate, puteți, de asemenea, să construiți o diagramă pentru ultima coloană cu un total cumulat, care să reflecte modificarea soldului de bani în timpul jocului:

Compararea diferitelor strategii

Acum, folosind simulatorul creat, puteți testa orice strategie de joc pe extrageri reale în 2022 și puteți vedea rezultatele pe care le-ar aduce. Dacă jucați 1 bilet la fiecare extragere, atunci imaginea de ansamblu a „prunei” arată cam așa:

Aici:

• Generator este un joc în care în fiecare extragere alegem numere aleatorii create de generatorul nostru (cu aceeași greutate).
• favorite este un joc în care în fiecare extragere folosim aceleași numere – cele care au căzut cel mai des la extrageri în ultimii doi ani (27, 32, 11, 14, 34, 40).
• Din afară – la fel, dar folosim cele mai rare numere derulante (12, 18, 26, 10, 21, 6).
• Rece – la toate extragerile folosim numere care nu au mai căzut de mult timp (35, 5, 39, 11, 6, 29).

După cum puteți vedea, nu există o mare diferență, dar generatorul de numere aleatoare se comportă puțin mai bine decât celelalte „strategii”.

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

Jucând la fiecare extragere cu un bilet cu numere generate aleatoriu (cu aceeași greutate):

Jucând 10 bilete în fiecare extragere cu numere generate aleatoriu (cu aceeași greutate):

Jucând 100 de bilete la fiecare extragere cu numere aleatorii (cu aceeași greutate):

Comentariile, după cum se spune, sunt de prisos – o scurgere a depozitelor este inevitabilă în toate cazurile 🙂