Simulation de loterie dans Excel

La loterie n'est pas une chasse au hasard,

c'est une chasse aux perdants.

Avec une régularité enviable (et plus souvent ces derniers temps), les gens m'écrivent pour demander de l'aide dans divers calculs liés aux loteries. Quelqu'un veut mettre en œuvre son algorithme secret pour sélectionner les numéros gagnants dans Excel, quelqu'un veut trouver des modèles dans les numéros qui sont tombés des tirages précédents, quelqu'un veut attraper les organisateurs de la loterie dans un jeu malhonnête.

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

Tâche 1. Probabilité de gagner

Prenons l'exemple de la loterie classique Stoloto 6 sur 45. Selon les règles, seuls ceux qui ont deviné les 10 numéros sur 6 reçoivent un super prix (45 millions de roubles ou plus si le solde du fonds du prix s'est accumulé lors des tirages précédents). Si vous avez deviné 5, vous recevrez 150 4 roubles, si 1500 - 3 6 roubles. , si 150 numéros sur 2, puis 50 roubles, si XNUMX numéros – vous restituerez XNUMX roubles dépensés sur le billet. Devinez un seul ou aucun - n'obtenez que des endorphines du processus de jeu.

La probabilité mathématique de gagner peut être facilement calculée à l'aide de la fonction standard NUMÉRO COMB (COMBINER), qui est disponible dans Microsoft Excel pour un tel cas. Cette fonction calcule le nombre de combinaisons de N nombres sur M. Ainsi, pour notre loterie "6 sur 45", ce serait :

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

… qui est égal à 8, le nombre total de toutes les combinaisons possibles dans cette loterie.

Si vous voulez calculer la probabilité d'un gain partiel (2-5 numéros sur 6), vous devrez d'abord calculer le nombre de ces options, qui est égal au produit du nombre de combinaisons de nombres devinés sur 6 par le nombre de numéros non devinés parmi les autres (45-6) = 39 numéros. Ensuite, nous divisons le nombre total de toutes les combinaisons possibles (8) par le nombre de gains reçus pour chaque option - et nous obtenons les probabilités de gain pour chaque cas :

Soit dit en passant, la probabilité, par exemple, de mourir dans un accident d'avion dans Notre Pays est estimée à environ 1 sur un million. Et la probabilité de gagner dans un casino à la roulette, en pariant tout sur un numéro est de 1 à 37.

Si tout ce qui précède ne vous a pas arrêté et que vous êtes toujours prêt à continuer à jouer, continuez.

Tâche 2. Fréquence d'occurrence de chaque numéro

Pour commencer, déterminons avec quelle fréquence certains nombres tombent. Dans une loterie idéale, étant donné un intervalle de temps suffisamment grand pour l'analyse, toutes les boules devraient avoir la même probabilité d'être dans l'échantillon gagnant. En réalité, les caractéristiques de conception du tambour de loterie et la forme du poids des boules peuvent fausser cette image, et pour certaines boules, la probabilité de tomber peut bien être plus élevée/inférieure que pour d'autres. Testons cette hypothèse dans la pratique.

Prenons, par exemple, des données sur tous les tirages de loterie 2020 sur 21 qui ont eu lieu en 6-45 sur le site Web de leur organisateur Stoloto, conçu sous la forme d'un tel tableau «intelligent», pratique pour l'analyse, avec le nom tabCirculation des archives. Розыгрыши проходят два раза в день (в 11 утра и в 11 вечера), т.е. В этой таблице у н нас полторы ыысячи тиражей-сррк - Волне достаточна ouvert нчачалаit

Pour calculer la fréquence d'apparition de chaque nombre, utilisez la fonction COUNTIF (NB.SI) et y ajouter une fonction TEXTE (TEXTE)pour ajouter des zéros et des astérisques avant et après les nombres à un chiffre, de sorte que NB.SI recherche l'occurrence d'un nombre n'importe où dans la combinaison de la colonne B. De plus, pour plus de clarté, nous allons créer un graphique par résultats et trier les fréquences par ordre décroissant:

En moyenne, toute boule devrait tomber 1459 tirages * 6 boules / 45 numéros = 194,53 fois (c'est exactement ce qu'on appelle dans les statistiques ожиданием математическим), но хорошо видно, что некоторые числа (27, 32, 11…) выпадали заметно чаще (+18%), а некоторые (10, 21, 6…) наоборот заметно реже (-15%), чем основная масса. Соответственно, можно попробовать использовать эту информацию для стратегии выигры. либо ставить на те шары, что выпадают чаще, либо наоборот — делать ставку на редко выпадающие шары в надежде, что они должны нагнать отставание.

Tâche 3. Quels numéros n'ont pas été tirés depuis longtemps ?

Une autre stratégie est basée sur l'idée qu'avec un nombre suffisamment important de tirages, tôt ou tard chaque numéro parmi tous disponibles de 1 à 45 devrait tomber. Donc, si certains numéros ne sont pas apparus parmi les gagnants depuis longtemps (« boules froides »), alors il est logique d'essayer de parier sur eux à l'avenir. 

Il est possible que nous n'ayons jamais acheté de numéro, si vous souhaitez organiser vos archives pour l'année 2020-21 en réservant des dates et en utilisant des données. ункцию PLUS EXPOSÉ (RENCONTRE). Il effectuera une recherche de haut en bas (c'est-à-dire des nouvelles aux anciennes séries) pour rechercher chaque numéro et donner le numéro de série de la série (en comptant de la fin de l'année au début) où ce numéro a été supprimé pour la dernière fois :

Étape 4. Générateur de ciseaux

Une autre stratégie de jeu consiste à éliminer le facteur psychologique lors de la devinette des nombres. Lorsqu'un joueur choisit des numéros en faisant son pari, il le fait inconsciemment et pas tout à fait rationnellement. Selon les statistiques, par exemple, les nombres de 1 à 31 sont choisis 70% plus souvent que les autres (dates préférées), 13 est choisi moins souvent (damn douzaine), les nombres contenant le sept "chanceux" sont plus souvent choisis, etc. Mais nous jouons contre une machine (tambour de loterie) pour laquelle tous les numéros sont identiques, il est donc logique de les choisir avec la même impartialité mathématique afin d'égaliser nos chances. Pour ce faire, nous devons créer un générateur de nombres aléatoires et – surtout – non répétitifs dans Excel :

Pour faire ça:

1. Créons une table "intelligente" nommée générateur de table, où la première colonne sera nos nombres de 1 à 45.
2. Dans la deuxième colonne, entrez le poids de chaque nombre (nous en aurons besoin un peu plus tard). Si tous les nombres ont la même valeur pour nous et que nous voulons les choisir avec une probabilité égale, alors le poids peut être égal à 1 partout.
3. Dans la troisième colonne, nous utilisons la fonction SLCHIS (RAND), которая в Excel генерирует случайное дробное число от 0 до 1, добавив к нему вес из предѱыду Таким образом каждый раз при пересчёте листа (нажатии на клавишу F9) un nouvel ensemble de 45 nombres aléatoires sera généré en tenant compte du poids de chacun d'eux.
4. Ajoutons une quatrième colonne, où l'on utilise la fonction RANG (RANG) вычислим ранг (позицию в топе) для каждого из чисел.

Il reste maintenant à faire une sélection des six premiers numéros par rang 6 à l'aide de la fonction PLUS EXPOSÉ (RENCONTRE):

При нажатии на клавишу F9 les formules sur la feuille Excel seront recalculées et à chaque fois nous obtiendrons un nouvel ensemble de 6 chiffres dans des cellules vertes. De plus, les nombres pour lesquels un poids plus grand a été défini dans la colonne B recevront un rang proportionnellement plus élevé et apparaîtront donc plus souvent dans les résultats de notre échantillon aléatoire. Si le poids de tous les nombres est identique, ils seront tous sélectionnés avec la même probabilité. De cette façon, nous obtenons un générateur de nombres aléatoires juste et impartial de 6 sur 45, mais avec la possibilité de faire des ajustements au caractère aléatoire de la distribution si nécessaire.

Si nous décidons de jouer à chaque tirage non pas avec un, mais, par exemple, avec deux billets à la fois, dans chacun desquels nous choisirons des numéros non répétitifs, nous pouvons simplement ajouter des lignes supplémentaires du bas à la plage verte, en ajoutant 6, 12, 18, etc. au rang. ré. respectivement:

Tâche 5. Simulateur de loterie dans Excel

Comme apothéose de tout ce sujet, créons un simulateur de loterie à part entière dans Excel, où vous pouvez essayer toutes les stratégies et comparer les résultats (en théorie de l'optimisation, quelque chose de similaire est également appelé la méthode de Monte Carlo, mais ce sera plus simple pour nous).

Pour que tout soit aussi proche que possible de la réalité, imaginez un instant que nous sommes le 1er janvier 2022 et que nous avons devant nous les tirages de cette année, dans lesquels nous prévoyons de jouer. J'ai entré les vrais nombres abandonnés dans le tableau tablTiraži2022, séparant les nombres tirés en plus les uns des autres dans des colonnes séparées pour la commodité des calculs ultérieurs :

Sur une feuille séparée Jeux créer un blanc pour la modélisation sous la forme d'une table "intelligente" avec le nom ongletIgra la forme suivante :

Ici:

• Dans les cellules jaunes ci-dessus, nous fixerons pour la macro le nombre de tirages en 2022 auxquels nous voulons participer (1-82) et le nombre de billets que nous jouons à chaque tirage.
• Les données des 11 premières colonnes (AJ) seront recopiées par la macro du tirage 2022.
• Données pour les six prochaines colonnes (KP) que la macro prendra de la feuille Générateur, où nous avons implémenté un générateur de nombres aléatoires (voir problème 4 ci-dessus).
• Dans la colonne Q, nous comptons le nombre de correspondances entre les nombres supprimés et ceux générés à l'aide de la fonction SUMPRODUCT (SOMMEPROD).
• В столeux це r Вычисляем финансовый рзлльтат (если не Выиграли, то ми 50 рублей за билет, р.
• Dans la dernière colonne S, nous considérons le résultat global de l'ensemble du jeu comme un total cumulé afin de voir la dynamique dans le processus.

Et pour faire revivre toute cette structure, il nous faut une petite macro. Sur l'onglet promoteur (Développeur) choisir une équipe Visual Basic ou utilisez le raccourci clavier autre+F11. Ajoutez ensuite un nouveau module vide via le menu Insertion – Module et entrez-y le code suivant :

Sub Lottery() Dim iGames As Integer, iTickets As Integer, i As Long, t As Integer, b As Integer wsArchive = Worksheets("Тиражи 2022") iGames = wsGame.Range("C1") 'количество тиражей iTickets = wsGame.Range("C2") 'количество билетов в каждом тираже i = 5 'первая строка в таблице таблИгра wsGame.Rows ("6: 1048576"). Delete 'ччищаем старые данные pour t = 1 à igames pour b = 1 à iTeCKETS' копеируем Выигравшие номеotre (2022, 1) .copy Destination: = wsgame.Cells (i, 1) 'коареousse .PasteSpecial Paste:=xlPasteValues ​​i = i + 1 Suivant b Suivant t End Sub  

Il reste à entrer les paramètres initiaux souhaités dans les cellules jaunes et à exécuter la macro à travers Développeur – Macros (Développeur — Macros) ou raccourci clavier autre+F8.

Pour plus de clarté, vous pouvez également créer un diagramme pour la dernière colonne avec un total cumulé, reflétant l'évolution du solde d'argent pendant le jeu :

Comparaison de différentes stratégies

Maintenant, en utilisant le simulateur créé, vous pouvez tester n'importe quelle stratégie de jeu sur de vrais tirages en 2022 et voir les résultats que cela apporterait. Si vous jouez 1 ticket à chaque tirage, l'image globale de la "prune" ressemble à ceci :

Ici:

• Générateur est un jeu où à chaque tirage nous choisissons des nombres aléatoires créés par notre générateur (avec le même poids).
• Favoris est un jeu où à chaque tirage on utilise les mêmes numéros – ceux qui sont tombés le plus souvent dans les tirages au cours des deux dernières années (27, 32, 11, 14, 34, 40).
• Outsiders – idem, mais on utilise les numéros déroulants les plus rares (12, 18, 26, 10, 21, 6).
• Du froid – dans tous les tirages, nous utilisons des numéros qui ne sont pas tombés depuis longtemps (35, 5, 39, 11, 6, 29).

Comme vous pouvez le voir, il n'y a pas de grande différence, mais le générateur de nombres aléatoires se comporte un peu mieux que les autres "stratégies".

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

Jouer à chaque tirage avec un ticket avec des numéros générés aléatoirement (avec le même poids) :

Jouer 10 tickets à chaque tirage avec des numéros générés aléatoirement (avec le même poids) :

Jouer 100 tickets à chaque tirage avec des numéros aléatoires (avec le même poids) :

Les commentaires, comme on dit, sont superflus – une vidange de dépôt est inévitable dans tous les cas 🙂