Simulazione della lotteria in Excel

La lotteria non è una caccia alla fortuna,

è una caccia ai perdenti.

Con invidiabile regolarità (e più spesso ultimamente) le persone mi scrivono chiedendomi aiuto nei vari calcoli legati alle lotterie. Qualcuno vuole implementare il suo algoritmo segreto per selezionare i numeri vincenti in Excel, qualcuno vuole trovare schemi nei numeri che sono caduti da estrazioni passate, qualcuno vuole catturare gli organizzatori della lotteria in un gioco disonesto.

In questo stato il mio hotel si è rivolto a questa domanda. Благо, в Excel для ршения таких задач достаточно инструменles

Compito 1. Probabilità di vincita

Prendiamo come esempio la classica lotteria Stoloto 6 su 45. Secondo le regole, solo coloro che hanno indovinato tutti i 10 numeri su 6 ricevono un superpremio (45 milioni di rubli o più se il saldo del montepremi si è accumulato dalle estrazioni precedenti). Se hai indovinato 5, riceverai 150 mila rubli, se 4 – 1500 rubli. , se 3 numeri su 6, allora 150 rubli, se 2 numeri – restituirai 50 rubli spesi per il biglietto. Indovina solo uno o nessuno: ottieni solo endorfine dal processo di gioco.

La probabilità matematica di vincita può essere facilmente calcolata utilizzando la funzione standard NUMBERCOMB (COMBINARE), disponibile in Microsoft Excel per questo caso. Questa funzione calcola il numero di combinazioni di N numeri su M. Quindi per la nostra lotteria "6 su 45" sarebbe:

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

…che è uguale a 8, il numero totale di tutte le possibili combinazioni in questa lotteria.

Se vuoi calcolare la probabilità di una vincita parziale (2-5 numeri su 6), dovrai prima calcolare il numero di tali opzioni, che è uguale al prodotto del numero di combinazioni di numeri ipotizzati su 6 per il numero di numeri non indovinati dei restanti (45-6) = 39 numeri. Quindi dividiamo il numero totale di tutte le possibili combinazioni (8) per il numero di vincite ricevute per ciascuna opzione e otteniamo le probabilità di vincita per ciascun caso:

A proposito, la probabilità, ad esempio, di morire in un incidente aereo nel Nostro Paese è stimata in circa 1 su un milione. E la probabilità di vincere in un casinò alla roulette, scommettendo tutto su un numero è da 1 a 37.

Se tutto quanto sopra non ti ha fermato e sei ancora pronto per continuare a giocare, continua.

Compito 2. Frequenza di occorrenza di ciascun numero

Per cominciare, determiniamo con quale frequenza cadono determinati numeri. In una lotteria ideale, dato un intervallo di tempo sufficientemente ampio per l'analisi, tutte le palline dovrebbero avere la stessa probabilità di essere nel campione vincente. In realtà, le caratteristiche del design del tamburo della lotteria e la forma del peso delle palline possono distorcere questa immagine e per alcune palline la probabilità di cadere potrebbe essere maggiore/inferiore rispetto ad altre. Mettiamo alla prova questa ipotesi in pratica.

Prendiamo, ad esempio, i dati su tutte le 2020 estrazioni della lotteria 21 avvenute in 6-45 dal sito web del loro organizzatore Stoloto, progettato sotto forma di un tavolo così "intelligente", comodo per l'analisi, con il nome tab Circolazione archivio. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. in questa tabella, sui lettori di questo tipo di tiraggio — vengono forniti per l'analisi dell'analisi:

Per calcolare la frequenza di occorrenza di ciascun numero, utilizzare la funzione COUNTIF (CONTA SE) e aggiungi una funzione ad esso TESTO (TESTO)per aggiungere zeri iniziali e asterischi prima e dopo a numeri a una cifra, in modo che COUNTIF cerchi l'occorrenza di un numero in qualsiasi punto della combinazione nella colonna B. Inoltre, per maggiore chiarezza, costruiremo un grafico in base ai risultati e ordineremo le frequenze in ordine decrescente:

In media ogni pallina dovrebbe cadere 1459 estrazioni * 6 palline / 45 numeri = 194,53 volte (questo è esattamente ciò che viene chiamato nelle statistiche educazione matematica), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые (10, 21, 6…) наоборот заметно реже (-15%), чем основная масса. Соответственно, можно попробовать использовать эту информацию для стратегии выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.

Compito 3. Quali numeri non vengono estratti da molto tempo?

Un'altra strategia si basa sull'idea che con un numero sufficientemente elevato di estrazioni, prima o poi ogni numero tra tutti quelli disponibili da 1 a 45 dovrebbe cadere. Quindi, se alcuni numeri non compaiono da molto tempo tra i vincitori ("palle fredde"), è logico provare a scommetterci su in futuro. 

Puoi leggere tutto il giorno senza un nome, se non uscire dall'archetipo del 2020-21 per l'anno XNUMX-XNUMX даты и использовать функцию PIÙ ESPOSTO (INCONTRO). Effettuerà la ricerca dall'alto verso il basso (cioè da nuove a vecchie corse) per cercare ciascun numero e fornire il numero di serie della corsa (contando dalla fine dell'anno all'inizio) in cui questo numero è stato rilasciato l'ultima volta:

Punto 4. Generatore di chiavi a punta

Un'altra strategia di gioco si basa sull'eliminazione del fattore psicologico quando si indovinano i numeri. Quando un giocatore sceglie i numeri facendo la sua scommessa, inconsciamente lo fa non del tutto razionalmente. Secondo le statistiche, ad esempio, i numeri da 1 a 31 vengono scelti il ​​70% in più rispetto agli altri (date preferite), il 13 viene scelto meno spesso (maledetta dozzina), i numeri contenenti il ​​sette "fortunato" vengono scelti più spesso, ecc. Ma stiamo giocando contro una macchina (il tamburo della lotteria) per la quale tutti i numeri sono gli stessi, quindi ha senso sceglierli con la stessa imparzialità matematica per pareggiare le nostre possibilità. Per fare ciò, dobbiamo creare un generatore di numeri casuali e, soprattutto, non ripetitivi in ​​Excel:

Per fare questo:

1. Creiamo una tabella "intelligente" denominata generatore di tabelle, dove la prima colonna saranno i nostri numeri da 1 a 45.
2. Nella seconda colonna, inserisci il peso per ogni numero (ci servirà poco dopo). Se tutti i numeri hanno lo stesso valore per noi e vogliamo sceglierli con uguale probabilità, allora il peso può essere posto uguale a 1 ovunque.
3. Nella terza colonna utilizziamo la funzione SLCHIS (RANDO), которая в Excel генерирует случайное дробное число от 0 to 1, doбавив к нему вес из предыдущего столбца. Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) verrà generato un nuovo set di 45 numeri casuali, tenendo conto del peso per ciascuno di essi.
4. Aggiungiamo una quarta colonna, dove si usa la funzione RANGO (RANGO) вычислим RANг (позицию в топе) для каждого из чисел.

Ora resta da fare una selezione dei primi sei numeri per rango 6 usando la funzione PIÙ ESPOSTO (INCONTRO):

Prima di iniziare la lezione F9 le formule sul foglio Excel verranno ricalcolate e ogni volta otterremo un nuovo set di 6 numeri nelle celle verdi. Inoltre, i numeri per i quali è stato impostato un peso maggiore nella colonna B riceveranno un rango proporzionalmente più alto e, quindi, appariranno più spesso nei risultati del nostro campione casuale. Se il peso di tutti i numeri è uguale, tutti verranno selezionati con la stessa probabilità. In questo modo otteniamo un generatore di numeri casuali equo e imparziale di 6 su 45, ma con la possibilità di apportare modifiche alla casualità della distribuzione, se necessario.

Se decidiamo di giocare in ogni estrazione non con uno, ma, ad esempio, con due biglietti contemporaneamente, in ognuno dei quali sceglieremo numeri non ripetuti, allora possiamo semplicemente aggiungere ulteriori linee dal basso all'intervallo verde, aggiungendo 6, 12, 18, ecc. al rango. d. rispettivamente:

Attività 5. Simulatore della lotteria in Excel

Come apoteosi di tutto questo argomento, creiamo un vero e proprio simulatore di lotteria in Excel, dove puoi provare qualsiasi strategia e confrontare i risultati (nella teoria dell'ottimizzazione, qualcosa di simile è anche chiamato metodo Monte Carlo, ma sarà più semplice per noi).

Per rendere tutto il più vicino possibile alla realtà, immagina per un momento che sia il 1 gennaio 2022 e abbiamo davanti a noi i sorteggi di quest'anno, in cui abbiamo intenzione di giocare. Ho inserito i numeri reali persi nella tabella tablTiraži2022, separando l'uno dall'altro i numeri estratti in colonne separate per comodità di calcoli successivi:

Su un foglio separato Gioco creare uno spazio vuoto per la modellazione sotto forma di una tabella "intelligente" con il nome tabIgra il seguente modulo:

Qui:

• Nelle celle gialle sopra, imposteremo per la macro il numero di estrazioni nel 2022 a cui vogliamo partecipare (1-82) e il numero di biglietti che giocheremo in ogni estrazione.
• I dati per le prime 11 colonne (AJ) verranno copiati dalla macro dal foglio di disegno 2022.
• Dati per le successive sei colonne (KP) che la macro prenderà dal foglio Generatore, dove abbiamo implementato un generatore di numeri casuali (vedi problema 4 sopra).
• Nella colonna Q, contiamo il numero di corrispondenze tra i numeri eliminati e quelli generati utilizzando la funzione MATR.SOMMA.PRODOTTO (SOMMAPRODOTTO).
• В столбце r ычычиляеtal финансо guarda
• Nell'ultima colonna S, consideriamo il risultato complessivo dell'intera partita come un totale cumulativo per vedere le dinamiche nel processo.

E per far rivivere tutta questa struttura, ci vuole una piccola macro. Sulla scheda sviluppatore (Sviluppatore) scegli una squadra Visual Basic o usa la scorciatoia da tastiera altro+F11. Quindi aggiungi un nuovo modulo vuoto tramite il menu Inserisci – Modulo e inserisci lì il seguente codice:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Set wsGame = Worksheets("Игра") Set wsNumbers = Worksheets("Гененера") Set wsNumbers = Worksheets("Гененера") wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ; (6, 1048576). Copia destinazione: = wsgame.cells (i, 1) 'копеем и в всвляеtal спиаль н в в с с с с с с с с с с сsSHS. .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Next b Next t End Sub  

Resta da inserire i parametri iniziali desiderati nelle celle gialle ed eseguire la macro Sviluppatore – Macro (Sviluppatore — Macro) o scorciatoia da tastiera altro+F8.

Per chiarezza, puoi anche costruire un diagramma per l'ultima colonna con un totale cumulativo, che rifletta la variazione del saldo di denaro durante il gioco:

Confronto di diverse strategie

Ora, utilizzando il simulatore creato, puoi testare qualsiasi strategia di gioco su estrazioni reali nel 2022 e vedere i risultati che porterebbe. Se giochi 1 biglietto in ogni estrazione, l'immagine generale della "prugna" è simile a questa:

Qui:

• Generatore è un gioco in cui in ogni estrazione scegliamo numeri casuali creati dal nostro generatore (con lo stesso peso).
• Preferiti è un gioco in cui in ogni estrazione usiamo gli stessi numeri, quelli che sono caduti più spesso in pareggio negli ultimi due anni (27, 32, 11, 14, 34, 40).
• Outsiders – lo stesso, ma utilizziamo i numeri a discesa più rari (12, 18, 26, 10, 21, 6).
• Freddo – in tutte le estrazioni utilizziamo numeri che non cadono da molto tempo (35, 5, 39, 11, 6, 29).

Come puoi vedere, non c'è una grande differenza, ma il generatore di numeri casuali si comporta un po' meglio delle altre "strategie".

Puoi anche fare in modo di ottenere grandi quantità di cibo in una tirata, come fare un sacco di soldi чество вариантов (иногда для этого несколько игроков объединяются в группу).

Giocare in ogni estrazione con un biglietto con numeri generati casualmente (con lo stesso peso):

Giocare 10 biglietti in ogni estrazione con numeri generati casualmente (con lo stesso peso):

Giocare 100 biglietti in ogni estrazione con numeri casuali (con lo stesso peso):

I commenti, come si suol dire, sono superflui: in tutti i casi è inevitabile un drenaggio dei depositi 🙂