Générer un nombre aléatoire à partir d'une moyenne et d'un écart type donnés dans Excel

La génération d'un ensemble de nombres aléatoires avec une moyenne et un écart type spécifiés est une exigence courante dans des domaines tels que la simulation statistique, les tests d'algorithmes ou la modélisation de processus dans des secteurs comme la finance, l'ingénierie et l'éducation. Cependant, Excel ne fournit pas de fonction directement intégrée pour générer instantanément une telle liste de nombres aléatoires adaptée à la fois à une moyenne et un écart type donnés. Si vous avez souvent besoin de créer des données de test aléatoires qui correspondent statistiquement à des caractéristiques particulières, savoir comment y parvenir peut améliorer considérablement l'efficacité de votre flux de travail et la qualité des données.

Dans ce tutoriel, nous présenterons des méthodes pratiques pour générer des nombres aléatoires basés sur la moyenne et l'écart type que vous spécifiez, avec des instructions détaillées étape par étape, des explications des paramètres des formules, ainsi que des conseils d'experts pour prévenir les erreurs et résoudre les problèmes. De plus, nous fournissons une solution macro VBA pour les utilisateurs qui ont besoin d'automatiser ce processus ou de générer efficacement de grands ensembles de données.

Générer un nombre aléatoire à partir d'une moyenne et d'un écart type donnés

Code VBA - Générer des nombres aléatoires avec une moyenne et un écart type spécifiés

---

Générer un nombre aléatoire à partir d'une moyenne et d'un écart type donnés

Dans Excel, vous pouvez créer un ensemble de nombres aléatoires correspondant à la moyenne et à l'écart type souhaités en appliquant une combinaison de fonctions standard. Suivez ces étapes pour une solution adaptée aux petits ou moyens ensembles de données, ou pour des besoins rapides et ponctuels :

1. Tout d'abord, entrez votre moyenne cible et votre écart type dans deux cellules vides distinctes. Pour plus de clarté et d'organisation, disons que vous utilisez la cellule B1 pour votre moyenne requise et la cellule B2 pour votre écart type requis. Voir la capture d'écran :

2. Pour créer les données aléatoires initiales, allez dans la cellule B3 et entrez la formule suivante :

=NORMINV(RAND(),$B$1,$B$2)

Après avoir entré la formule, faites glisser la poignée de remplissage vers le bas pour remplir autant de lignes que nécessaire pour votre ensemble de données aléatoires. Chaque cellule générera une valeur basée sur la moyenne et l'écart type spécifiés.

Astuce : Dans la formule =NORMINV(RAND(),$B$1,$B$2) :

• RAND() produit une probabilité aléatoire différente entre 0 et 1 à chaque recalcul de la feuille de calcul.
• $B$1 fait référence à la valeur moyenne que vous avez spécifiée.
• $B$2 fait référence à l'écart type désiré.

Pour les versions modernes d'Excel (2010 et ultérieures), envisagez d'utiliser =NORM.INV(RAND(),$B$1,$B$2), qui est fonctionnellement identique mais reflète des noms de fonctions mis à jour.

3. Pour vérifier que vos nombres générés ressemblent statistiquement à votre moyenne et écart type souhaités, utilisez les formules suivantes pour calculer les valeurs réelles de votre échantillon généré. Dans la cellule D1, calculez la moyenne de l'échantillon avec :

=AVERAGE(B3:B16)

Dans D2, calculez l'écart type de l'échantillon avec :

=STDEV.P(B3:B16)

Astuce :

• B3:B16 est juste un exemple de plage. Ajustez-la selon le nombre de valeurs aléatoires que vous avez générées à l'étape 2.
• Un échantillon aléatoire plus grand donne une moyenne et un écart type réels plus proches de vos valeurs spécifiées, en raison de la loi des grands nombres.

4. Pour ajuster davantage votre série afin qu'elle corresponde exactement à votre moyenne et écart type souhaités, normalisez vos valeurs aléatoires initiales. Dans la cellule D3, entrez la formule suivante :

=$B$1+(B3-$D$1)*$B$2/$D$2

Faites glisser la poignée de remplissage vers le bas à travers autant de lignes que vous avez de nombres aléatoires. Cette formule standardise vos valeurs initiales et les met à l'échelle précisément pour répondre à la moyenne et à l'écart type dans B1 et B2.

Astuce :

• B1 est votre moyenne requise.
• B2 est votre écart type requis.
• B3 est la valeur aléatoire originale.
• D1 est la moyenne de ces valeurs aléatoires originales.
• D2 est l'écart type de ces valeurs aléatoires originales.

Vous pouvez maintenant confirmer que l'ensemble final de valeurs répond à vos exigences en recalculant leur moyenne et écart type à des fins de contrôle qualité et de documentation.

5. Dans la cellule D17, calculez la moyenne de votre ensemble final de nombres aléatoires avec la formule suivante :

=AVERAGE(D3:D16)

Ensuite, dans la cellule D18, calculez l'écart type avec la formule ci-dessous :

=STDEV.P(D3:D16)

Astuce : D3:D16 fait référence à votre plage de nombres aléatoires finalisée.

Dépannage :

• Si vous voyez une erreur #VALEUR!, revérifiez toutes les plages de cellules référencées et assurez-vous qu'aucune formule ne fait référence à des cellules vides ou invalides.
• Si la formule continue de changer à chaque recalcul, sélectionnez les nombres aléatoires finaux, copiez-les, et utilisez Collage spécial > Valeurs pour empêcher toute mise à jour supplémentaire.
• Rappelez-vous que les générateurs aléatoires dans Excel reposent sur le recalcul, donc enregistrer des résultats statiques est nécessaire lorsque la cohérence est critique.

---

Code VBA - Générer des nombres aléatoires avec une moyenne et un écart type spécifiés

Pour les scénarios où vous devez rapidement produire une grande quantité de données aléatoires correspondant à une moyenne et un écart type spécifiés — notamment dans des cas répétitifs, automatisés ou à volume élevé — une macro VBA offre une solution gagnant du temps. Avec une seule exécution, vous pouvez créer un ensemble de données complet directement dans votre classeur, réduisant la répétition manuelle et minimisant les erreurs de copie de formules.

Cette approche convient pour :

• Générer automatiquement des ensembles de données aléatoires pour des simulations, des tests de stress ou des démonstrations éducatives.
• Les situations où vous souhaitez standardiser le format de sortie avec une intervention manuelle minimale.
• Les utilisateurs à l'aise avec l'utilisation de l'Éditeur VBA dans Excel.

Comparée aux méthodes basées sur des formules, VBA peut également permettre des ajustements dynamiques ou une intégration avec des flux de travail plus complexes, mais soyez conscient que les macros doivent être activées dans votre classeur et peuvent nécessiter une sauvegarde explicite au format .xlsm « compatible macros ».

1. Sur le ruban Excel, cliquez sur Outils de développement (si non visible, activez-le via Fichier > Options > Personnaliser le ruban), puis sélectionnez Visual Basic. Dans la fenêtre Visual Basic for Applications, cliquez sur Insertion > Module, et copiez le code suivant dans la fenêtre de module vide :

Sub GenerateRandomNumbersWithMeanStd()
    Dim outputRange As Range
    Dim meanValue As Double, stdDevValue As Double
    Dim numItems As Long, i As Long
    Dim xTitleId As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set outputRange = Application.InputBox("Select the output range", xTitleId, Type:=8)
    meanValue = Application.InputBox("Enter the mean value", xTitleId, "", Type:=1)
    stdDevValue = Application.InputBox("Enter the standard deviation", xTitleId, "", Type:=1)
    
    If outputRange Is Nothing Or meanValue = 0 Or stdDevValue = 0 Then
        MsgBox "Please ensure you have specified all required parameters.", vbExclamation, "KutoolsforExcel"
        Exit Sub
    End If
    
    numItems = outputRange.Count
    Randomize
    
    For i = 1 To numItems
        outputRange.Cells(i).Value = Application.WorksheetFunction.NormInv(Rnd, meanValue, stdDevValue)
    Next i
End Sub

2. Cliquez sur le Exécuter bouton (ou appuyez sur F5) pour lancer la macro. Une boîte de dialogue vous demandera de sélectionner la plage où vous souhaitez afficher les nombres aléatoires (par exemple, sélectionnez A1:A100 pour 100 valeurs). Ensuite, vous serez invité à entrer la moyenne et l'écart type souhaités. La macro remplira la plage avec des nombres aléatoires correspondant à vos spécifications.

Conseils et Dépannage :

• VBA utilise la fonction NormInv d'Excel pour générer des nombres distribués normalement — vérifiez toujours si votre version la prend en charge ; pour les anciennes versions d'Excel, la fonction pourrait devoir être NORMINV.
• Le germe aléatoire est défini avec Randomize pour obtenir des résultats variés à chaque exécution.
• Si vous voulez des résultats reproductibles, commentez ou supprimez la ligne Randomize.
• La macro écrasera toutes les données existantes dans la plage de sortie sélectionnée, alors assurez-vous de choisir une zone vide si nécessaire.
• Si vous saisissez des valeurs inappropriées (par exemple, un écart type négatif ou nul), la macro ne se poursuivra pas et affichera un message d'avertissement.

---

Articles connexes :

• Générer des nombres aléatoires sans répétition dans Excel
• Générer des nombres aléatoires positifs ou négatifs dans Excel
• Arrêter les changements de nombres aléatoires dans Excel
• Générer un oui ou non aléatoire dans Excel