Loi normale dans Excel : formule NORM.INV, moyenne et écart type
En bref : la courbe en cloche. La plupart des résultats tombent près de la moyenne ; plus on s’en éloigne, plus ils sont rares, autant d’un côté que de l’autre.
Une loi normale décrit une grandeur qui varie symétriquement autour de sa moyenne, avec une dispersion fixée par son écart type. Dans Excel, =NORM.INV(RAND(), mean, sd) en tire une valeur. Pour une demande de moyenne 1 000 unités et d’écart type 200, le P10 est 744 et le P90 1 256, et environ 95 % des résultats se situent à moins de deux écarts types de la moyenne : de 600 à 1 400.
Ce qu’est une loi normale
La loi normale est la courbe en cloche : la plupart des résultats se trouvent près de la moyenne, de moins en moins à mesure qu’on s’en éloigne, et les deux côtés se reflètent. Deux nombres suffisent à la définir. La moyenne en fixe le centre, qui est aussi la médiane et la valeur la plus probable. L’écart type en fixe la largeur : environ deux tiers des résultats se trouvent à moins d’un écart type de la moyenne.
Comment lire le graphique : chaque barre compte combien des 10 000 valeurs simulées sont tombées dans cet intervalle, et la ligne est la courbe théorique. Plus la barre est haute, plus les valeurs tombent souvent à cet endroit. Les pointillés marquent le P10 et le P90 : huit résultats sur dix se situent entre les deux.
| Plage | Demande | Part des résultats |
|---|---|---|
| Moyenne ± 1 écart type | 800 – 1 200 | 68,3 % |
| Moyenne ± 2 écarts types | 600 – 1 400 | 95,4 % |
| Moyenne ± 3 écarts types | 400 – 1 600 | 99,7 % |
Les centiles découlent des mêmes deux nombres : le P90 se situe à 1,2816 écart type au-dessus de la moyenne et le P10 à la même distance en dessous, si bien que 80 % des résultats tombent entre 744 et 1 256.
La loi normale dans Excel
NORM.INV(probability, mean, standard_deviation) renvoie la valeur sous laquelle se trouve la part indiquée des résultats (LOI.NORMALE.INVERSE.N dans Excel en français). Avec une probabilité fixe, elle donne un centile ; avec RAND() (ALEA dans Excel en français) comme probabilité, elle donne un tirage aléatoire, nouveau à chaque recalcul de la feuille. Le fichier Excel de cette page garde le nombre aléatoire dans une cellule à part, pour que l’on voie quel tirage vient de quel nombre ; en une seule cellule, le tirage s’écrit =NORM.INV(RAND(),D5,F5).
| Cellule | Formule | Valeur |
|---|---|---|
| Nombre aléatoire u (I5) | =RAND() | |
| Tirage (J5) | =NORM.INV(I5, | |
| Tirage en une seule cellule | =NORM.INV(RAND(), | |
| Moyenne (K5) | =D5 | 1 000 |
| P10 (L5) | =NORM.INV(0.1, | 744 |
| P90 (M5) | =NORM.INV(0.9, | 1 256 |
Vérifier les chiffres : valeurs exactes comparées à 10 000 tirages d’Excel et de xellstorm
| Valeur exacte | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Moyenne | 1 000 | 998 | 1 000 |
| Écart type | 200 | 199 | 200 |
| P10 | 744 | 743 | 744 |
| P50 (médiane) | 1 000 | 996 | 1 000 |
| P90 | 1 256 | 1 251 | 1 256 |
Excel tire avec RAND(), de façon entièrement aléatoire ; xellstorm répartit ses tirages régulièrement sur toute la fourchette (échantillonnage par hypercube latin), si bien que ses statistiques tombent plus près des valeurs exactes.
La question inverse, la probabilité que la demande reste sous un niveau donné, se traite avec NORM.DIST(x, mean, standard_deviation, TRUE) (LOI.NORMALE.N dans Excel en français) : =NORM.DIST(700, 1000, 200, TRUE) donne 6,7 %, la probabilité d’une saison sous 700 unités. Les anciens noms NORMINV et NORMDIST fonctionnent toujours. Un tirage n’est qu’une saison possible ; pour voir ce que l’incertitude fait à un bénéfice ou à un niveau de stock, il faut des milliers de tirages, et c’est ce que fait une simulation de Monte-Carlo (voir la simulation de Monte-Carlo sur Excel).
Quand la loi normale convient
- Des mesures autour d’une cible. Les cotes d’usinage, les poids de remplissage et les erreurs de mesure varient symétriquement autour d’une valeur nominale, comme dans l’exemple du cumul de tolérances.
- Des totaux de nombreuses parties indépendantes. Additionnez beaucoup de petites incertitudes qui ne dépendent pas les unes des autres : le total ressemble à une courbe en cloche, quelle que soit la forme de chacune. Les statisticiens appellent cela le théorème central limite.
- La demande d’un produit établi, dont l’écart type est faible devant la moyenne, comme celle de l’exemple de la quantité à commander.
Quand elle ne convient pas
Une loi normale n’a pas de borne inférieure : elle donne des valeurs négatives avec une certaine probabilité. Pour la demande de cette page, elle est négligeable, mais avec un écart type de 500, soit la moitié de la moyenne, 2,3 % des tirages seraient une demande négative. Pour des grandeurs qui ne peuvent pas être négatives et qui s’étalent largement, utilisez une loi log-normale ou une loi gamma, ou tronquez la loi normale à zéro.
Elle est aussi symétrique. Les coûts et les durées dépassent en général l’estimation plus qu’ils ne restent en dessous ; une distribution PERT, une loi triangulaire ou log-normale rend compte de cette asymétrie. Et elle rend les valeurs extrêmes très rares : au-delà de trois écarts types de la moyenne, presque rien ne se produit, ce qui sous-estime les grands mouvements des prix et des rendements. La loi de Student donne plus souvent des valeurs extrêmes.
Saisir une loi normale dans xellstorm
- Ouvrez le fichier Excel, cliquez sur la cellule dont vous n’êtes pas sûr et choisissez Définir comme entrée.
- À l’étape Distributions, choisissez Normale et saisissez la moyenne et l’écart type. Les colonnes Moyenne et P10 – P90 montrent ce que donnent ces deux nombres avant l’exécution.
- Si vous raisonnez en valeur basse, valeur typique et valeur haute plutôt qu’en écart type, sélectionnez l’entrée et utilisez « À partir d’estimations » dans le panneau latéral : saisissez votre P10, votre P50 et votre P90 et choisissez Normale. Avec des valeurs passées, « À partir de données » leur ajuste une loi normale et d’autres formes, et les classe.
- Pour garder des tirages positifs, ouvrez « Tronquer / décaler / mettre à l’échelle » sous les paramètres et fixez une troncature min de 0.
Un fichier Excel qui tire déjà avec =NORM.INV(RAND(), mean, sd) n’a pas besoin d’être réécrit : « Importer depuis le fichier » transforme une telle cellule en entrée normale, la moyenne et l’écart type restant liés à leurs cellules. Les modèles conçus pour un complément se convertissent de la même façon depuis RiskNormal, VoseNormal et PsiNormal.
Questions fréquentes
Comment générer des nombres aléatoires suivant une loi normale dans Excel ?
Utilisez =NORM.INV(RAND(), mean, sd), par exemple =NORM.INV(RAND(), 1000, 200) pour une moyenne de 1 000 et un écart type de 200 (dans Excel en français : =LOI.NORMALE.INVERSE.N(ALEA(); 1000; 200)). Chaque recalcul (F9) tire une nouvelle valeur. Recopiez la formule vers le bas d’une colonne pour obtenir de nombreux tirages : leur moyenne se rapproche de la moyenne choisie, et leur écart type de celui que vous avez saisi.
Quelle est la différence entre NORM.INV et NORM.DIST ?
Elles répondent à des questions inverses. NORM.DIST(x, mean, sd, TRUE) prend une valeur et renvoie la probabilité d’un résultat inférieur ; NORM.INV(p, mean, sd) prend une probabilité et renvoie la valeur sous laquelle se trouve cette part des résultats. Pour une demande de moyenne 1 000 et d’écart type 200, NORM.INV(0.9, 1000, 200) vaut 1 256, et NORM.DIST de cette valeur renvoie 0,9.
Comment trouver le P90 d’une loi normale ?
Le P90 est la moyenne plus 1,2816 écart type, soit dans Excel =NORM.INV(0.9, mean, sd) : 1 256 pour une moyenne de 1 000 et un écart type de 200. Le P10 est à la même distance sous la moyenne, 744, et le P95 vaut 1 329. Ce sont les centiles d’une seule entrée ; le P90 d’un total de plusieurs entrées demande une simulation, car les centiles ne s’additionnent pas (voir P50, P80 et P90).
Une loi normale peut-elle donner des valeurs négatives ?
Oui. Une loi normale s’étend sans limite dans les deux sens : elle donne donc toujours des valeurs négatives avec une certaine probabilité. Quand la moyenne dépasse zéro de plus de quatre écarts types environ, cette probabilité est infime ; quand l’écart type vaut la moitié de la moyenne, elle est de 2,3 %. Pour des montants qui ne peuvent pas être négatifs, tronquez la loi à zéro ou utilisez une loi log-normale ou gamma.
À lire aussi
- Guide
Quelle loi de probabilité choisir ? Guide pour les modèles de simulation de Monte-Carlo
Trouvez la bonne loi de probabilité en deux questions : PERT, triangulaire, normale, log-normale, Weibull, Poisson, binomiale, bêta et d’autres, avec l’usage de chacune et sa formule Excel. - Guide
Loi log-normale dans Excel : LOGNORM.INV, paramètres et usage
Une loi log-normale est positive, avec une longue queue à droite. Comment fonctionnent les paramètres de LOGNORM.INV, comment les tirer d’une moyenne et d’un écart type, et quand l’utiliser. - Cumul de tolérances
Analyse de cumul de tolérances : pire cas, somme quadratique ou Monte-Carlo ?
Un boîtier et cinq pièces : le pire cas laisse le jeu descendre à 0,120 mm ; la RSS prévoit 3,29 % de jeux inférieurs à 0,4 mm et 20 000 itérations de Monte-Carlo en trouvent 3,21 %.
xellstorm est un outil de simulation de Monte-Carlo qui fonctionne dans le navigateur, pour les modèles Excel : aucun complément, et votre fichier ne quitte jamais votre ordinateur.