Formule générique
=MATCH(RAND(),cumulative_probability)
Sommaire
Pour générer un nombre aléatoire, pondéré avec une probabilité donnée, vous pouvez utiliser une table d'aide avec une formule basée sur les fonctions RAND et MATCH.
Dans l'exemple illustré, la formule en F5 est:
=MATCH(RAND(),D$5:D$10)
Explication
Cette formule repose sur la table d'assistance visible dans la plage B4: D10. La colonne B contient les six nombres que nous voulons comme résultat final. La colonne C contient le poids de probabilité attribué à chaque nombre, entré sous forme de pourcentage. La colonne D contient la probabilité cumulée, créée avec cette formule dans D5, copiée vers le bas:
=SUM(D4,C4)
Notez que nous décalons intentionnellement la probabilité cumulée d'une ligne vers le bas, de sorte que la valeur dans D5 soit zéro. Ceci permet de s'assurer que MATCH est capable de trouver une position pour toutes les valeurs jusqu'à zéro comme expliqué ci-dessous.
Pour générer une valeur aléatoire, en utilisant la probabilité pondérée dans la table d'assistance, F5 contient cette formule, copiée vers le bas:
=MATCH(RAND(),D$5:D$10)
Dans MATCH, la valeur de recherche est fournie par la fonction RAND. RAND génère une valeur aléatoire entre zéro et 1. Le tableau de recherche est la plage D5: D10, verrouillé afin qu'il ne change pas lorsque la formule est copiée dans la colonne.
Le troisième argument de MATCH, type de correspondance, est omis. Lorsque le type de correspondance est omis, MATCH renvoie la position de la plus grande valeur inférieure ou égale à la valeur de recherche *. En termes pratiques, cela signifie que la fonction MATCH se déplace le long des valeurs dans D5: D10 jusqu'à ce qu'une valeur plus grande soit rencontrée, puis "revient" à la position précédente. Lorsque MATCH rencontre une valeur supérieure à la dernière valeur la plus grande dans D5: D10 (.7 dans l'exemple), il renvoie la dernière position (6 dans l'exemple). Comme mentionné ci-dessus, la première valeur de D5: D10 est délibérément zéro pour garantir que les valeurs inférieures à .1 sont «capturées» par la table de consultation et renvoient une position de 1.
* Les valeurs de la plage de recherche doivent être triées par ordre croissant.
Valeur de texte pondérée aléatoire
Pour renvoyer une valeur de texte pondérée aléatoire (c'est-à-dire une valeur non numérique), vous pouvez entrer des valeurs de texte dans la plage B5: B10, puis ajouter INDEX pour renvoyer une valeur dans cette plage, en fonction de la position renvoyée par MATCH:
=INDEX($B$5:$B$10,MATCH(RAND(),D$5:D$10))
Remarques
- J'ai rencontré cette approche dans un message de forum sur mrexcel.com
- RAND est une fonction volatile et recalculera à chaque changement de feuille de calcul
- Une fois que vous avez des valeurs aléatoires, utilisez coller> valeurs spéciales pour remplacer la formule si nécessaire
