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, le test 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 intégrée directe 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 randomisées qui correspondent statistiquement à des caractéristiques particulières, savoir comment y parvenir peut grandement améliorer votre efficacité de travail et la qualité de vos données.

Dans ce tutoriel, nous introduirons 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, et des conseils d'experts pour prévenir les erreurs et résoudre les problèmes. De plus, nous fournissons une solution de macro VBA pour les utilisateurs qui ont besoin d'automatiser ce processus ou de générer de grands ensembles de données efficacement.

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 qui correspondent à votre moyenne et écart type souhaités en appliquant une combinaison de fonctions standards. Suivez ces étapes pour une solution adaptée aux petits ou moyens ensembles de données, ou pour des besoins rapides et ad hoc :

1. Tout d'abord, entrez votre moyenne cible et votre écart type dans deux cellules vides séparées. 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 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 souhaité.

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 les noms de fonctions mis à jour.

3. Pour vérifier que vos nombres générés ressemblent statistiquement à votre moyenne et écart type prévus, 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 une plage d'exemple. Ajustez-la en fonction du 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 prévus, 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 précisément à l'échelle 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 d'assurance 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 à la plage de vos nombres aléatoires finalisés.

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 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 — surtout dans des cas répétitifs, automatisés ou à haut volume — 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 ainsi 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 contrainte ou des démonstrations éducatives.
• Situations où vous souhaitez standardiser le format de sortie avec une intervention manuelle minimale.
• Utilisateurs à l'aise avec l'utilisation de l'Éditeur VBA dans Excel.

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

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 pour Applications, cliquez sur Insérer > 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 F5pour 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 devrez 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 — revérifiez toujours si votre version prend cela en charge ; pour les anciennes versions d'Excel, la fonction pourrait devoir être NORMINV.
• Le germe aléatoire est défini avec Randomize pour 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 zéro), la macro ne se poursuivra pas et affichera un message d'avertissement.

---

Articles relatifs :

• 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 le changement des nombres aléatoires dans Excel
• Générer aléatoirement oui ou non dans Excel