Pour biaiser le tirage du loto...
Un lecteur, suite à mes messages du 17 et du 21 février sur la fonction alea(), m'a demandé comment faire pour tirer les nombres du loto (de 1 à 49) en favorisant certains numéros et en en défavorisant d’autres.
J'ai pour cela créé un petit modèle avec en colonne B les probabilités voulues, en réduisant celles des premiers numéros et en augmentant celle des derniers numéros. La cellule B51 vérifie que le total des probabilités fait bien 100%.
En colonne C, nous calculons les probabilités cumulées, c’est-à-dire la probabilité dans chaque ligne d’obtenir un résultat inférieur au nombre placé en colonne A.
La formule de la cellule E2, que nous avons reproduite en commentaire, permet de tirer un nombre au hasard entre 1 et 49, en respectant ces probabilités.
L’astuce de cette formule est qu’elle trouve le nombre comme étant la position de la dernière valeur de C2:C50 qui soit inférieure ou égale au nombre aléatoire engendré par alea(). La probabilité de trouver chaque numéro est donc bien égale à la probabilité en colonne B…
Remarque 1 – Bien entendu, rien n’empêche que le même nombre sorte plusieurs fois dans la liste.
Remarque 2 – Tirer ainsi un aléa dans l’intervalle [ 0 ; 1 ] et le situer dans une liste de probabilités cumulées est donc une méthode imparable pour réaliser une simulation probabiliste d'un phénomène dont on connaît les probabilités.
J'ai pour cela créé un petit modèle avec en colonne B les probabilités voulues, en réduisant celles des premiers numéros et en augmentant celle des derniers numéros. La cellule B51 vérifie que le total des probabilités fait bien 100%.
En colonne C, nous calculons les probabilités cumulées, c’est-à-dire la probabilité dans chaque ligne d’obtenir un résultat inférieur au nombre placé en colonne A.
La formule de la cellule E2, que nous avons reproduite en commentaire, permet de tirer un nombre au hasard entre 1 et 49, en respectant ces probabilités.
L’astuce de cette formule est qu’elle trouve le nombre comme étant la position de la dernière valeur de C2:C50 qui soit inférieure ou égale au nombre aléatoire engendré par alea(). La probabilité de trouver chaque numéro est donc bien égale à la probabilité en colonne B…Remarque 1 – Bien entendu, rien n’empêche que le même nombre sorte plusieurs fois dans la liste.
Remarque 2 – Tirer ainsi un aléa dans l’intervalle [ 0 ; 1 ] et le situer dans une liste de probabilités cumulées est donc une méthode imparable pour réaliser une simulation probabiliste d'un phénomène dont on connaît les probabilités.
rédigé par Hervé Thiriez |
4 commentaire(s)
4 Commentaire(s):
je suis un lecteur assidu de ce blog et aujourd'hui je me retrouve avec un probleme relativement similaire à celui ci mentionné dans cet article.
A l'exception que la probabilité de chaque item dépend de deux facteurs
1. la position de l'item dans la liste (au début tres probable, à la fin peu probable)
2. si l'item fait partie ou non d'un catégorie. Et c'est la la subtilité
en effet, chaque itme est associé une catégorie (ex: bleu, jaune, vert). Et en gros si l'item n'est pas dans la catégorie souhaitée sa probabilité devient ZERO qqsoit sa position dans la liste (ie on ne veut pas qu'il soit tiré).
Dans une autre liste, je cherche à faire un tirage du type, 3 verts, 2 bleus, 1 jaune...
Parce que les probabilités vont changer au moment de l'évaluation de la formule (selon la catégorie), on ne peut pas renseigner de probabilité statiquement en colonne à coté des items. Et à coté de ca, on ne peut pas non plus faire une seule matrice de probabilités à deux dimension (ligne = item, colonne = categorie, data = probabilité) parce que la fonction EQUIV a besoin que les données soient classés par ordre croissant pour fonctionner correctement....
bref, à moins de faire autant de grille de probabilité qu'il y a de categorie et faire une formule à rallonge avec des si à gogo, je ne vois pas comment résoudre ça avec des formules. Il faut passer avec du code macro, ce qui est particulièrement peu esthétique et lisible pour un néophyte.
L'autre option serait de créer des fonctions custom (type un EQUIV qui accepte des données non tries) qu'on utilise ensuite dans la formule. Mais bon créer des foncitons custom, mm si c'était possible, je ne sais pas le faire.
"monsieur excel" a-t'il une idée pour résoudre ce pb d'esthétique excel? :)
By
Anonyme, sur 8:06 PM
Je suis désolé mais je ne parviens pas à comprendre exactement ce que vous voulez. Le mieux serait de me mailer un fichier Excel avec tous les éléments et des explications détaillées.
Mail : thiriez@hec.fr
By
Hervé Thiriez, sur 9:39 AM
c'est vrai que c'est difficile à expliquer sans le fichier à coté :)
mes explications sont peu claires.
finalement je suis en train de résoudre le probleme avec une fonction custom (User Defined Function?). Mais il faut écrire du code, les gens voient donc le fameux warning excel sur les macro. Et puis comme toute solution necessitant du code, je trouve ca terriblement peu éléguant et difficilement maintenable/partageable.
Une fois que j'arrive à cette solution, j'enverrai la réponse par mail avec le fichier. Par curiosité, je me demande si d'après vous on peut résoudre ca avec éléguence et esthétique, cad uniquement avec les fonction de base de excel et sans macro
Sinon, je trouve que les UDF sont vraiment un point fort de excel (plus que les macro je trouve). Ce sujet mériteraient pleinement un ou deux articles dans votre blog
By
Anonyme, sur 10:55 PM
En ce qui concerne les UDF, j'ai déjà traité de ce sujet.
Vous pouvez le voir dans mes articles du 22 janvier 2006 et du 15 septembre 2007.
By
Hervé Thiriez, sur 10:32 AM
Enregistrer un commentaire
<< Accueil