Générer un nombre aléatoire selon une moyenne et un écart-type donnés dans Excel

Générer un ensemble de nombres aléatoires avec une moyenne et un écart-type spécifiques est une exigence courante dans des domaines tels que la simulation statistique, les tests d’algorithmes ou la modélisation de processus en finance, en ingénierie et en éducation. Toutefois, Excel ne dispose pas de fonction intégrée permettant de produire directement une telle série de nombres aléatoires ajustée à la fois à une moyenne et à un écart-type donnés. Si vous avez régulièrement besoin de créer des données de test aléatoires correspondant précisément à des caractéristiques statistiques définies, maîtriser cette méthode peut considérablement optimiser votre flux de travail et renforcer la qualité de vos données.

Dans ce tutoriel, découvrez des méthodes pratiques pour générer des nombres aléatoires selon la moyenne et l’écart-type de votre choix. Vous y trouverez des instructions détaillées pas à pas, des explications claires sur les paramètres des formules utilisées, ainsi que des conseils d’experts pour éviter les erreurs courantes et résoudre rapidement les problèmes. Nous proposons également une macro VBA prête à l’emploi, idéale pour automatiser cette tâche ou produire efficacement de grands volumes de données.

Générer un nombre aléatoire selon une moyenne et 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 selon une moyenne et 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 combinant plusieurs fonctions standard. Suivez ces étapes pour une solution adaptée aux jeux de données de petite ou moyenne taille, ou aux besoins ponctuels :

1. Commencez par saisir votre moyenne cible et votre écart-type dans deux cellules vides distinctes. Pour plus de clarté, supposons que vous utilisiez la cellule B1 pour la moyenne souhaitée et la cellule B2 pour l’écart-type souhaité. Voir la capture d’écran :

2. Pour générer les données aléatoires initiales, positionnez-vous dans la cellule B3 et saisissez la formule suivante :

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

Après avoir saisi la formule, faites glisser la poignée de recopie vers le bas pour remplir autant de lignes que nécessaire dans votre jeu de données aléatoires. Chaque cellule générera une valeur basée sur la moyenne et l’écart-type que vous avez spécifiés.

Conseil :Dans la formule =NORMINV(RAND();$B$1;$B$2):

• RAND() renvoie une valeur aléatoire différente comprise 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 souhaité.

Pour les versions récentes d’Excel (2010 et ultérieures), envisagez d’utiliser =NORM.INV(RAND();$B$1;$B$2), qui est fonctionnellement identique mais utilise les noms de fonctions mis à jour.

3. Pour vérifier que vos nombres générés correspondent statistiquement à la moyenne et à l’écart-type souhaités, utilisez les formules suivantes afin de calculer la valeur réelle de votre échantillon généré. Dans la cellule D1, calculez la moyenne de l’échantillon à l’aide de la formule :

=AVERAGE(B3:B16)

Dans la cellule D2, calculez l’écart-type de l’échantillon avec :

=STDEV.P(B3:B16)

Conseil :

• B3:B16 est simplement une plage d’exemple. Ajustez-la en fonction du nombre de valeurs aléatoires générées à l’étape 2.
• Un échantillon aléatoire plus grand permet d’obtenir une moyenne et un écart-type qui se rapprochent davantage des valeurs spécifiées, conformément à la loi des grands nombres.

4. Pour affiner davantage votre série et l’ajuster précisément à la moyenne et à l’écart-type souhaités, normalisez vos valeurs aléatoires initiales. Dans la cellule D3, saisissez la formule suivante :

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

Faites glisser la poignée de recopie vers le bas sur autant de lignes qu’il y a de nombres aléatoires. Cette formule normalise vos valeurs initiales et les ajuste avec précision pour qu’elles correspondent à la moyenne et à l’écart-type indiqués en B1 et B2.

Conseil :

• B1 correspond au niveau requis.
• B2 correspond à l’écart-type requis.
• B3 correspond à la valeur aléatoire initiale.
• D1 correspond à la moyenne de ces valeurs aléatoires initiales.
• D2 correspond à l’écart type de ces valeurs aléatoires initiales.

Vous pouvez désormais confirmer que l’ensemble final de valeurs répond à vos exigences en recalculant leur moyenne et leur écart-type, pour un contrôle qualité rigoureux et une documentation claire.

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

=AVERAGE(D3:D16)

Puis, dans la cellule D18, calculez l’écart-type à l’aide de la formule suivante :

=STDEV.P(D3:D16)

Conseil : D3:D16 correspond à la plage de vos nombres aléatoires finalisés.

Dépannage :

• Si vous voyez une erreur #VALEUR!, examinez attentivement toutes les plages de cellules référencées et assurez-vous qu’aucune formule ne fait référence à des cellules vides ou non valides.
• Si la formule continue de changer à chaque recalcul, sélectionnez les nombres aléatoires finaux, copiez-les, puis utilisez Collage spécial > Valeurs pour empêcher toute mise à jour ultérieure.
• N’oubliez pas que les générateurs aléatoires dans Excel dépendent du recalcul ; il est donc essentiel d’enregistrer des résultats statiques dès que la cohérence est requise.

---

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

Lorsque vous devez générer rapidement un grand volume de données aléatoires correspondant à une moyenne et un écart-type précis — notamment dans des scénarios répétitifs, automatisés ou à haute intensité — une macro VBA s’impose comme une solution efficace et gain de temps. En une seule exécution, elle crée un jeu de données complet directement dans votre classeur, éliminant ainsi les tâches manuelles fastidieuses et les erreurs liées à la copie de formules.

Cette approche convient aux situations suivantes :

• Générez automatiquement des jeux de données aléatoires pour vos simulations, tests de résistance ou démonstrations pédagogiques.
• Situations où vous souhaitez standardiser le format de sortie tout en minimisant l’intervention manuelle.
• Utilisateurs à l’aise avec l’utilisation de l’éditeur VBA dans Excel.

Comparée aux méthodes basées sur des formules, la solution VBA permet également des ajustements dynamiques et une intégration dans des flux de travail plus complexes — à condition toutefois que les macros soient activées dans votre classeur et que celui-ci soit éventuellement enregistré au format prenant en charge les macros (.xlsm).

1. Dans le ruban Excel, cliquez sur Outils de développement(si cette option n’est pas visible, activez-la via)Fichier > Options > Personnaliser le ruban), puis sélectionnez Visual Basic. Dans la fenêtre Visual Basic pour Applications, cliquez sur Insertion > Module, puis copiez le code suivant dans la fenêtre du 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 bouton Exécuter(ou appuyez sur)F5) pour lancer la macro. Une boîte de dialogue s’ouvrira alors pour vous demander de sélectionner la plage dans laquelle insérer les nombres aléatoires (par exemple, A1:A100 pour générer 100 valeurs). Vous devrez ensuite indiquer la moyenne et l’écart-type souhaités. La macro remplira automatiquement la plage sélectionnée avec des nombres aléatoires conformes à vos critères.

Conseils et dépannage :

• VBA utilise la fonction NormInv d’Excel pour générer des nombres distribués selon une loi normale — assurez-vous toujours que votre version la prend en charge ; dans les anciennes versions d’Excel, cette fonction pourrait s’appeler NORMINV.
• La graine aléatoire est définie avec Randomize afin d’obtenir des résultats variés à chaque exécution.
• Si vous souhaitez obtenir des résultats reproductibles, commentez ou supprimez la ligne Randomize.
• La macro écrasera toutes les données existantes dans la zone de placement de la liste sélectionnée ; veillez donc à 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 s’exécutera pas et affichera un message d’avertissement.

---

Articles associés :

• Générer des nombres aléatoires sans doublon dans Excel
• Générer des nombres aléatoires positifs ou négatifs dans Excel
• Empêcher les nombres aléatoires de changer dans Excel
• Générer aléatoirement « oui » ou « non » dans Excel