Προσομοίωση λοταρίας στο Excel

Το λαχείο δεν είναι κυνήγι τύχης,

είναι κυνήγι ηττημένων.

Με αξιοζήλευτη κανονικότητα (και πιο συχνά τελευταία), μου γράφουν άνθρωποι ζητώντας βοήθεια σε διάφορους υπολογισμούς που σχετίζονται με λαχεία. Κάποιος θέλει να εφαρμόσει τον μυστικό του αλγόριθμο για την επιλογή αριθμών που κερδίζουν στο Excel, κάποιος θέλει να βρει μοτίβα στους αριθμούς που έχουν πέσει έξω από προηγούμενες κληρώσεις, κάποιος θέλει να πιάσει τους διοργανωτές της λαχειοφόρου αγοράς σε ένα ανέντιμο παιχνίδι.

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

Εργασία 1. Πιθανότητα νίκης

Ας πάρουμε ως παράδειγμα την κλασική λοταρία Stoloto 6 από 45. Σύμφωνα με τους κανόνες, μόνο όσοι μάντευαν και τους 10 αριθμούς από τους 6 λαμβάνουν ένα σούπερ έπαθλο (45 εκατομμύρια ρούβλια ή περισσότερα εάν το υπόλοιπο του κεφαλαίου επάθλου έχει συσσωρευτεί από προηγούμενες κληρώσεις). Αν μαντέψατε 5, θα λάβετε 150 χιλιάδες ρούβλια, αν 4 – 1500 ρούβλια. , εάν 3 αριθμοί από τους 6, τότε 150 ρούβλια, εάν 2 αριθμοί - θα επιστρέψετε 50 ρούβλια που ξοδέψατε για το εισιτήριο. Μαντέψτε μόνο ένα ή κανένα - λάβετε μόνο ενδορφίνες από τη διαδικασία του παιχνιδιού.

Η μαθηματική πιθανότητα νίκης μπορεί εύκολα να υπολογιστεί χρησιμοποιώντας την τυπική συνάρτηση NUMBERCOMB (ΣΥΝΔΥΑΣΜΟΣ), το οποίο είναι διαθέσιμο στο Microsoft Excel για μια τέτοια περίπτωση. Αυτή η συνάρτηση υπολογίζει τον αριθμό των συνδυασμών των N αριθμών από το M. Έτσι για την κλήρωση "6 από 45" θα ήταν:

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

… που ισούται με 8, ο συνολικός αριθμός όλων των δυνατών συνδυασμών σε αυτήν την κλήρωση.

Εάν θέλετε να υπολογίσετε την πιθανότητα για μερική νίκη (2-5 αριθμοί από τους 6), τότε θα πρέπει πρώτα να υπολογίσετε τον αριθμό τέτοιων επιλογών, ο οποίος είναι ίσος με το γινόμενο του αριθμού των συνδυασμών των μαντέψων αριθμών από 6 με τον αριθμό των άγνωστων αριθμών από τους υπόλοιπους (45-6) = 39 αριθμούς. Στη συνέχεια διαιρούμε τον συνολικό αριθμό όλων των δυνατών συνδυασμών (8) με τον αριθμό των κερδών που λάβαμε για κάθε επιλογή – και παίρνουμε τις πιθανότητες νίκης για κάθε περίπτωση:

Παρεμπιπτόντως, η πιθανότητα, για παράδειγμα, να πεθάνεις σε αεροπορικό δυστύχημα στη Χώρα μας υπολογίζεται περίπου σε 1 στο εκατομμύριο. Και η πιθανότητα να κερδίσετε σε ένα καζίνο στη ρουλέτα, ποντάροντας τα πάντα σε έναν αριθμό είναι 1 προς 37.

Εάν όλα τα παραπάνω δεν σας εμπόδισαν και είστε έτοιμοι να παίξετε περαιτέρω, συνεχίστε.

Εργασία 2. Συχνότητα εμφάνισης κάθε αριθμού

Αρχικά, ας προσδιορίσουμε με ποια συχνότητα πέφτουν ορισμένοι αριθμοί. Σε μια ιδανική λοταρία, δεδομένου ενός αρκετά μεγάλου χρονικού διαστήματος για ανάλυση, όλες οι μπάλες θα πρέπει να έχουν την ίδια πιθανότητα να βρίσκονται στο νικητήριο δείγμα. Στην πραγματικότητα, τα σχεδιαστικά χαρακτηριστικά του τυμπάνου του λαχείου και το σχήμα βάρους των μπάλων μπορεί να παραμορφώσουν αυτήν την εικόνα και για ορισμένες μπάλες η πιθανότητα να πέσουν έξω μπορεί να είναι υψηλότερη/χαμηλότερη από ό,τι για άλλες. Ας δοκιμάσουμε αυτή την υπόθεση στην πράξη.

Ας πάρουμε, για παράδειγμα, δεδομένα για όλες τις κληρώσεις του 2020 από τις 21 κληρώσεις που πραγματοποιήθηκαν στις 6-45 από τον ιστότοπο του διοργανωτή τους Stoloto, σχεδιασμένο με τη μορφή ενός τόσο «έξυπνου» πίνακα, βολικού για ανάλυση, με το όνομα tabΑρχείο Κυκλοφορία. Розыгрыши проходят два раза в день (во 11 ουτρα και στις 11 βραδιές), т.е. в этой таблице у нас полторы тысячи тиражей-строк — πλήρης παράδοση για την αρχή της επιλογής για την ανάλυση:

Για να υπολογίσετε τη συχνότητα εμφάνισης κάθε αριθμού, χρησιμοποιήστε τη συνάρτηση COUNTIF (COUNTIF) και προσθέστε μια συνάρτηση σε αυτό ΚΕΙΜΕΝΟ (ΚΕΙΜΕΝΟ)για να προσθέσετε μηδενικά και αστερίσκους πριν και μετά σε μονοψήφιους αριθμούς, έτσι ώστε το COUNTIF να αναζητά την εμφάνιση ενός αριθμού οπουδήποτε στον συνδυασμό στη στήλη Β. Επίσης, για μεγαλύτερη σαφήνεια, θα δημιουργήσουμε ένα γράφημα με βάση τα αποτελέσματα και θα ταξινομήσουμε τις συχνότητες κατά φθίνουσα σειρά:

Κατά μέσο όρο, κάθε μπάλα πρέπει να πέσει 1459 κληρώσεις * 6 μπάλες / 45 αριθμοί = 194,53 φορές (αυτό ακριβώς λέγεται στα στατιστικά математическим ожиданием), но хорошо видно, что некоторые числа (27, 32, 11…) σημείωσε άφθονο чаще (+18%), ένα νέο (10, 21, 6…) για να το κάνει (-15%), με βάση το βάρος. Соответственно, може да попробовать использовать эtu πληροφορίες за στρατηγικές выигрыша, т.е. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.

Εργασία 3. Ποιοι αριθμοί δεν έχουν κληρωθεί για πολύ καιρό;

Μια άλλη στρατηγική βασίζεται στην ιδέα ότι με έναν αρκετά μεγάλο αριθμό κληρώσεων, αργά ή γρήγορα κάθε αριθμός από όλους τους διαθέσιμους από το 1 έως το 45 θα πρέπει να πέσει έξω. Έτσι, εάν κάποιοι αριθμοί δεν έχουν εμφανιστεί μεταξύ των νικητών για μεγάλο χρονικό διάστημα («κρύες μπάλες»), τότε είναι λογικό να δοκιμάσετε να ποντάρετε σε αυτούς στο μέλλον. 

Можно легко найти все давно не выпадавшие номера, если отсортировать наш архив тиражей για 2020-21 год. ΠΙΟ ΕΚΤΙΘΕΤΑ (ΑΓΩΝΑΣ). Θα πραγματοποιήσει αναζήτηση από πάνω προς τα κάτω (δηλαδή από νέες σε παλιές σειρές) για να αναζητήσει κάθε αριθμό και να δώσει τον αύξοντα αριθμό της σειράς (μετρώντας από το τέλος του έτους έως την αρχή) όπου αυτός ο αριθμός απορρίφθηκε τελευταία φορά:

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

Μια άλλη στρατηγική παιχνιδιού βασίζεται στην εξάλειψη του ψυχολογικού παράγοντα κατά την εικασία αριθμών. Όταν ένας παίκτης επιλέγει αριθμούς κάνοντας το στοίχημά του, υποσυνείδητα το κάνει αυτό όχι εντελώς ορθολογικά. Σύμφωνα με στατιστικά στοιχεία, για παράδειγμα, οι αριθμοί από το 1 έως το 31 επιλέγονται 70% πιο συχνά από τους υπόλοιπους (αγαπημένες ημερομηνίες), το 13 επιλέγεται λιγότερο συχνά (καταραμένη δωδεκάδα), οι αριθμοί που περιέχουν το "τυχερό" επτά επιλέγονται συχνότερα κ.λπ. Όμως παίζουμε με ένα μηχάνημα (τύμπανο λοταρίας) για το οποίο όλοι οι αριθμοί είναι ίδιοι, οπότε είναι λογικό να τους επιλέξουμε με την ίδια μαθηματική αμεροληψία για να ισοφαρίσουμε τις πιθανότητές μας. Για να γίνει αυτό, πρέπει να δημιουργήσουμε μια γεννήτρια τυχαίων και – το πιο σημαντικό – μη επαναλαμβανόμενων αριθμών στο Excel:

Για να γινει αυτο:

1. Ας δημιουργήσουμε έναν «έξυπνο» πίνακα με το όνομα tableGenerator, όπου η πρώτη στήλη θα είναι οι αριθμοί μας από το 1 έως το 45.
2. Στη δεύτερη στήλη, εισάγετε το βάρος για κάθε αριθμό (θα το χρειαστούμε λίγο αργότερα). Αν όλοι οι αριθμοί είναι εξίσου πολύτιμοι για εμάς και θέλουμε να τους επιλέξουμε με ίση πιθανότητα, τότε το βάρος μπορεί να οριστεί ίσο με 1 παντού.
3. Στην τρίτη στήλη χρησιμοποιούμε τη συνάρτηση ΣΛΧΗΣ (ΑΚΡΑ), η οποία δημιουργείται στο Excel δημιουργεί ένα κομμάτι από το 0 έως το 1, το οποίο δεν είναι κάτι που θα σας αρέσει. Таким образом каждый раз при пересчёте λίστα (нажатии на клавишу F9) θα δημιουργηθεί ένα νέο σύνολο 45 τυχαίων αριθμών, λαμβάνοντας υπόψη το βάρος για τον καθένα από αυτούς.
4. Ας προσθέσουμε μια τέταρτη στήλη, όπου χρησιμοποιείται η συνάρτηση ΤΑΞΗ (ΤΑΞΗ) вычислим ранг (θέση в топе) для каждого из чисел.

Τώρα απομένει να κάνετε μια επιλογή των πρώτων έξι αριθμών με την κατάταξη 6 χρησιμοποιώντας τη συνάρτηση ΠΙΟ ΕΚΤΙΘΕΤΑ (ΑΓΩΝΑΣ):

При наготии на клавишу F9 οι τύποι στο φύλλο του Excel θα επανυπολογίζονται και κάθε φορά θα λαμβάνουμε ένα νέο σύνολο 6 αριθμών σε πράσινα κελιά. Επιπλέον, οι αριθμοί για τους οποίους ορίστηκε μεγαλύτερο βάρος στη στήλη Β θα λάβουν αναλογικά υψηλότερη κατάταξη και, επομένως, θα εμφανίζονται πιο συχνά στα αποτελέσματα του τυχαίου δείγματος μας. Εάν το βάρος για όλους τους αριθμούς οριστεί στο ίδιο, τότε θα επιλεγούν όλοι με την ίδια πιθανότητα. Με αυτόν τον τρόπο έχουμε μια δίκαιη και αμερόληπτη γεννήτρια τυχαίων αριθμών 6 από 45, αλλά με τη δυνατότητα να κάνουμε προσαρμογές στην τυχαιότητα της κατανομής εάν είναι απαραίτητο.

Εάν αποφασίσουμε να παίξουμε σε κάθε κλήρωση όχι με ένα, αλλά, για παράδειγμα, με δύο εισιτήρια ταυτόχρονα, σε καθένα από τα οποία θα επιλέξουμε μη επαναλαμβανόμενους αριθμούς, τότε μπορούμε απλώς να προσθέσουμε επιπλέον γραμμές από το κάτω μέρος στο πράσινο εύρος, προσθέτοντας 6, 12, 18 κ.λπ. στην κατάταξη. ρε. αντίστοιχα:

Εργασία 5. Προσομοιωτής Λοταρίας στο Excel

Ως αποθέωση όλου αυτού του θέματος, ας δημιουργήσουμε έναν πλήρη προσομοιωτή λοταρίας στο Excel, όπου μπορείτε να δοκιμάσετε οποιεσδήποτε στρατηγικές και να συγκρίνετε τα αποτελέσματα (στη θεωρία βελτιστοποίησης, κάτι παρόμοιο ονομάζεται επίσης μέθοδος Monte Carlo, αλλά θα είναι απλούστερη για εμάς).

Για να τα κάνουμε όλα όσο πιο κοντά γίνεται στην πραγματικότητα, φανταστείτε για μια στιγμή ότι είναι 1η Ιανουαρίου 2022 και έχουμε μπροστά μας τις φετινές κληρώσεις, στις οποίες σκοπεύουμε να παίξουμε. Έβαλα τους πραγματικούς πεσμένους αριθμούς στον πίνακα tablTiraži2022, διαχωρίζοντας τους επιπλέον σχεδιασμένους αριθμούς μεταξύ τους σε ξεχωριστές στήλες για τη διευκόλυνση των επακόλουθων υπολογισμών:

Σε ξεχωριστό φύλλο Παιχνίδι δημιουργήστε ένα κενό για μοντελοποίηση με τη μορφή ενός "έξυπνου" πίνακα με το όνομα tabIgra την παρακάτω φόρμα:

Εδώ:

• Στα κίτρινα κελιά παραπάνω, θα ορίσουμε για τη μακροεντολή τον αριθμό των κληρώσεων το 2022 στις οποίες θέλουμε να συμμετάσχουμε (1-82) και τον αριθμό των εισιτηρίων που παίζουμε σε κάθε κλήρωση.
• Τα δεδομένα για τις πρώτες 11 στήλες (AJ) θα αντιγραφούν από τη μακροεντολή από το φύλλο κλήρωσης 2022.
• Δεδομένα για τις επόμενες έξι στήλες (KP) που θα λάβει η μακροεντολή από το φύλλο Γεννήτρια, όπου έχουμε εφαρμόσει μια γεννήτρια τυχαίων αριθμών (βλ. πρόβλημα 4 παραπάνω).
• Στη στήλη Q, μετράμε τον αριθμό των αντιστοιχιών μεταξύ των αριθμών που απορρίφθηκαν και εκείνων που δημιουργήθηκαν χρησιμοποιώντας τη συνάρτηση ΑΝΤΙΠΡΟΣΩΠΟΣ (SUMPRODUCT).
• В столбце R вычисляем финансовый результат (осли не выиграли, то минус 50 ρούβλια για ένα εισιτήριο, если выиграли, то приз — 50 ρ. για εισιτήριο)
• Στην τελευταία στήλη S, θεωρούμε το συνολικό αποτέλεσμα ολόκληρου του παιχνιδιού ως αθροιστικό σύνολο για να δούμε τη δυναμική στη διαδικασία.

Και για να αναβιώσουμε όλη αυτή τη δομή, χρειαζόμαστε μια μικρή μακροεντολή. Στην καρτέλα προγραμματιστή (Προγραμματιστής) επιλέξτε μια ομάδα Visual Basic ή χρησιμοποιήστε συντόμευση πληκτρολογίου άλλος+F11. Στη συνέχεια, προσθέστε μια νέα κενή ενότητα μέσω του μενού Εισαγωγή – Ενότητα και εισάγετε τον παρακάτω κωδικό εκεί:

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer 'объявляем переменные для ссылки на листы Σετ wsGame = Φύλλα εργασίας("Игра") Σετ wsNumbers("ен wsArchive = Φύλλα εργασίας("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов во каждом тираже i = 5 'перваблиаGaws ("6:1048576").Διαγραφή 'очищаем старые данные Για t = 1 Προς iGames Για b = 1 Προς iTickets 'копируем выигравшие номера со λίστα Тиражи 2022 и вставляем на лист Игра wsArchive.Resells,1(t). (1, 1).Αντιγραφή Προορισμού:=wsGame.Cells(i, 10) 'копируем и вставляем специјалной вставкой значений сгенерированные номера со листа Генератор wsNumbers.Range("G1:L4").Αντιγραφή wss(4,Cell. .PasteSpecial Paste:=xlPasteValues ​​i = i + 11 Επόμενο b Επόμενο t Τέλος Υπο  

Απομένει να εισαγάγετε τις επιθυμητές αρχικές παραμέτρους στα κίτρινα κελιά και να εκτελέσετε τη μακροεντολή Προγραμματιστής – Μακροεντολές (Προγραμματιστής — Μακροεντολές) ή συντόμευση πληκτρολογίου άλλος+F8.

Για λόγους σαφήνειας, μπορείτε επίσης να δημιουργήσετε ένα διάγραμμα για την τελευταία στήλη με ένα αθροιστικό σύνολο, που να αντικατοπτρίζει την αλλαγή στο υπόλοιπο χρημάτων κατά τη διάρκεια του παιχνιδιού:

Σύγκριση διαφορετικών στρατηγικών

Τώρα, χρησιμοποιώντας τον προσομοιωτή που δημιουργήθηκε, μπορείτε να δοκιμάσετε οποιαδήποτε στρατηγική παιχνιδιού σε πραγματικές κληρώσεις το 2022 και να δείτε τα αποτελέσματα που θα έφερνε. Αν παίζετε 1 εισιτήριο σε κάθε κλήρωση, τότε η συνολική εικόνα του «δαμάσκηνου» μοιάζει κάπως έτσι:

Εδώ:

• Γεννήτρια είναι ένα παιχνίδι όπου σε κάθε κλήρωση επιλέγουμε τυχαίους αριθμούς που δημιουργεί η γεννήτριά μας (με το ίδιο βάρος).
• αγαπημένα είναι ένα παιχνίδι όπου σε κάθε κλήρωση χρησιμοποιούμε τους ίδιους αριθμούς – αυτούς που έχουν πέσει πιο συχνά σε ισοπαλίες τα τελευταία δύο χρόνια (27, 32, 11, 14, 34, 40).
• Outsiders – το ίδιο, αλλά χρησιμοποιούμε τους πιο σπάνιους αναπτυσσόμενους αριθμούς (12, 18, 26, 10, 21, 6).
• Κρύο – σε όλες τις κληρώσεις χρησιμοποιούμε αριθμούς που δεν έχουν πέσει έξω για πολύ καιρό (35, 5, 39, 11, 6, 29).

Όπως μπορείτε να δείτε, δεν υπάρχει μεγάλη διαφορά, αλλά η γεννήτρια τυχαίων αριθμών συμπεριφέρεται λίγο καλύτερα από τις άλλες «στρατηγικές».

Можно также попробовать играть большим количеством εισιτήρια σε κάθε περίπτωση, chtobы perekrыtь bolьshee coliчество variantov (иногда для этого несколько игроков объединяются во γκρουπ).

Παίζοντας σε κάθε κλήρωση με ένα δελτίο με αριθμούς που δημιουργούνται τυχαία (με το ίδιο βάρος):

Παίζοντας 10 εισιτήρια σε κάθε κλήρωση με αριθμούς που δημιουργούνται τυχαία (με το ίδιο βάρος):

Παίζοντας 100 εισιτήρια σε κάθε κλήρωση με τυχαίους αριθμούς (με το ίδιο βάρος):

Τα σχόλια, όπως λένε, είναι περιττά - η διαρροή καταθέσεων είναι αναπόφευκτη σε όλες τις περιπτώσεις 🙂