Comment générer une valeur aléatoire en fonction d’une probabilité attribuée dans Excel ?
Lorsque vous travaillez avec Excel, il vous arrive parfois de devoir générer des valeurs aléatoires reflétant des probabilités sous-jacentes précises. Par exemple, imaginez que vous disposiez d’un tableau listant plusieurs résultats possibles accompagnés de leurs probabilités respectives, comme illustré dans la capture d’écran ci-dessous.
Ce scénario est courant dans les simulations commerciales, la modélisation de projets et les usages pédagogiques, lorsque vous souhaitez que la sélection aléatoire reflète fidèlement la vraisemblance ou la fréquence indiquée par vos données.
Les besoins et cas d’usage typiques incluent :
- Simulez des réponses à des enquêtes ou des choix clients, en tenant compte de la probabilité plus élevée de certaines réponses.
- Générez des jeux de données de test ou des tirages aléatoires pour illustrer les concepts de probabilité.
- Automatisez des processus de sélection où chaque option présente une probabilité connue.
- Conception de jeux et analyse de risques, où les résultats doivent obéir à des lois de probabilité précises.
Découvrez ci-dessous plusieurs méthodes pour générer des valeurs aléatoires selon des probabilités attribuées dans Excel : des techniques classiques basées sur des formules, une automatisation avancée via VBA, ainsi que l’utilisation du complément intégré « Analyse des données ».
➤ VBA : Générer des valeurs aléatoires avec des probabilités attribuées
Générer une valeur aléatoire avec probabilité
Excel propose une solution accessible, basée sur des formules, pour générer des valeurs aléatoires selon des probabilités définies. Idéale pour les tâches rapides, cette méthode fonctionne entièrement dans les feuilles de calcul et ne requiert aucune configuration particulière.
Avant de commencer, assurez-vous que vos valeurs sont disposées dans une colonne (A2:A8) et que leurs probabilités correspondantes — exprimées sous forme de décimales comprises entre 0 et 1 — figurent dans la colonne adjacente (B2:B8). La somme de ces probabilités doit être égale à 1 pour garantir l’exactitude des résultats. Cette solution convient parfaitement aux tableaux contenant un nombre raisonnable de valeurs.
1.Dans une colonne adjacente (à partir de C2), saisissez la formule suivante pour calculer les probabilités cumulées :
=SUM($B$2:B2)Ensuite, faites glisser cette formule vers le bas pour couvrir toutes vos valeurs. Cela crée des plages cumulées pour chaque valeur, facilitant l’association d’un nombre aléatoire à un résultat spécifique.
2.Dans n’importe quelle cellule vide (par exemple D2), saisissez la formule ci-dessous pour obtenir une valeur aléatoire selon votre loi de probabilité :
=INDEX(A$2:A$8,COUNTIF(C$2:C$8,"<="&RAND())+1)Appuyez sur Entrée pour afficher une valeur aléatoire. Chaque fois que vous appuyez sur F9 (pour recalculer) ou que les données de la feuille changent, un nouveau résultat s’affiche.
Conseils et précautions :
- Les probabilités indiquées dans la colonne B doivent s’additionner exactement à 1 (ou à 100 % si vous utilisez des pourcentages ; toutefois, convertissez-les en décimales pour les formules) afin de garantir une répartition équitable.
- Cette technique convient particulièrement aux listes courtes. Avec des dizaines, voire des centaines de valeurs, les performances peuvent se dégrader et la maintenance devient plus complexe.
- Si vous devez répéter la sélection aléatoire pour plusieurs tirages (par exemple, générer un lot de résultats simulés), il vous suffit de copier la formule finale dans une plage située en dessous ou à côté.
- Attention aux lignes vides et aux plages incompatibles, car elles peuvent provoquer des erreurs ou des résultats inattendus.
Rappel en cas d’erreur : Si vous obtenez une erreur #REF! ou #VALEUR!, assurez-vous que votre colonne de probabilités cumulées a la même longueur que votre liste de valeurs et que toutes les probabilités sont des nombres valides.
Libérez la magie d’Excel avec Kutools IA
- Exécution intelligente : effectuez des opérations sur les cellules, analysez des données et créez des graphiques, le tout à partir de commandes simples.
- formules personnalisées : créez des formules sur mesure pour rationaliser vos flux de travail.
- Programmation VBA : Écrivez et implémentez du code VBA en toute simplicité.
- Interprétation de formules : Décryptez facilement les formules complexes.
- Traduction de texte : Franchissez les barrières linguistiques directement depuis vos feuilles de calcul.
VBA : Générer des valeurs aléatoires avec des probabilités attribuées
Pour les utilisateurs qui recherchent davantage d’automatisation ou doivent générer un grand volume de valeurs aléatoires — par exemple, des milliers d’échantillons —, Excel VBA constitue une solution à la fois pratique, plus rapide et plus souple que les formules de feuille de calcul. Elle s’avère particulièrement efficace pour traiter de grands ensembles de données ou produire en masse des résultats aléatoires destinés à des simulations.
1.Accédez à Outils de développement>Visual Basic, puis dans la fenêtre VBA, cliquez sur Insertion>Module, et collez le code suivant dans le module :
Sub GenerateRandomWithProbability()
Dim rngValues As Range
Dim rngProbs As Range
Dim n As Long
Dim i As Long
Dim cumProbs() As Double
Dim valList() As Variant
Dim randNum As Double
Dim resultRange As Range
Dim idx As Long
' On Error, ignore
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Select values
Set rngValues = Application.InputBox("Select values range", xTitleId, Type:=8)
' Select probabilities
Set rngProbs = Application.InputBox("Select probabilities range", xTitleId, Type:=8)
' Number of random values to generate
n = Application.InputBox("Number of random values to generate", xTitleId, "10", Type:=1)
' Where to output
Set resultRange = Application.InputBox("Select output start cell", xTitleId, Type:=8)
If rngValues.Rows.Count <> rngProbs.Rows.Count Then
MsgBox "Values and probabilities range must be the same size.", vbExclamation
Exit Sub
End If
ReDim cumProbs(1 To rngValues.Count)
ReDim valList(1 To rngValues.Count)
' Calculate cumulative probabilities
cumProbs(1) = rngProbs.Cells(1, 1).Value
valList(1) = rngValues.Cells(1, 1).Value
For i = 2 To rngValues.Count
cumProbs(i) = cumProbs(i - 1) + rngProbs.Cells(i, 1).Value
valList(i) = rngValues.Cells(i, 1).Value
Next i
' Generate random results
For i = 1 To n
randNum = Rnd
For idx = 1 To UBound(cumProbs)
If randNum <= cumProbs(idx) Then
resultRange.Cells(i, 1).Value = valList(idx)
Exit For
End If
Next idx
Next i
End Sub2. Dans la fenêtre VBA, cliquez sur le 
bouton d’exécution pour lancer le code. Vous serez successivement invité à sélectionner votre plage de valeurs, votre plage de probabilités, le nombre de valeurs aléatoires à générer, ainsi que la cellule de départ de la plage de sortie. La macro remplira en un clin d’œil les cellules ciblées avec des valeurs aléatoires conformes à vos probabilités attribuées.
- Vous pouvez répéter ce processus avec des jeux de données plus volumineux, et la colonne de sortie peut être personnalisée dans n’importe quelle feuille.
- Si la somme de votre plage de probabilités ne s’approche pas suffisamment de 1, le code risque de fausser la distribution ; vérifiez toujours vos hypothèses.
- Cette solution est idéale pour l’échantillonnage automatisé, la simulation et la génération reproductible de lots.
Conseils : Gardez vos valeurs et probabilités contiguës et clairement alignées. Enregistrez votre travail avant d’exécuter des macros, car les actions VBA ne peuvent pas être annulées à l’aide de la commande Annuler.
Articles associés :
- Comment générer un nombre aléatoire sans doublons dans Excel ?
- Comment empêcher les nombres aléatoires de se modifier dans Excel ?
- Comment générer aléatoirement « Oui » ou « Non » dans Excel ?
Meilleurs outils de productivité Office
Boostez vos compétences Excel avec Kutools pour Excel et découvrez une efficacité inégalée.Kutools pour Excel propose plus de 300 fonctionnalités avancées pour améliorer votre productivité et Gagner du temps.Cliquez ici pour obtenir la fonctionnalité dont vous avez le plus besoin...
Office Tab apporte une interface à onglets à Office et rend votre travail bien plus facile
- Activez l’édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
- Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
- Augmente votre productivité de 50 % et vous fait économiser des centaines de clics de souris chaque jour !
Tous les compléments Kutools. Un seul installateur
Kutools for Office regroupe les compléments pour Excel, Word, Outlook et PowerPoint, ainsi que Office Tab Pro, ce qui en fait le choix idéal pour les équipes travaillant à travers les applications Office.
- Suite tout-en-un— Compléments Excel, Word, Outlook et PowerPoint + Office Tab Pro
- Un seul installateur, une seule licence— installation en quelques minutes (compatible MSI)
- Fonctionne mieux ensemble— productivité optimisée dans toutes les applications Office
- Essai gratuit de 30 jours avec toutes les fonctionnalités— aucune inscription, aucune carte bancaire
- Meilleur rapport qualité-prix— économisez par rapport à l’achat de compléments individuels