Loi log-normale dans Excel : LOGNORM.INV, paramètres et usage
En bref : une grandeur le plus souvent modérée mais qui, de temps en temps, atteint plusieurs fois sa valeur courante, comme le coût d’une réparation. Elle ne descend jamais sous zéro.
Une loi log-normale décrit une grandeur qui ne peut pas descendre sous zéro et qui a une longue queue à droite, comme un coût, une perte ou une durée ; son logarithme suit une loi normale. Dans Excel, =LOGNORM.INV(RAND(), LN(median), s) en tire une valeur. Le coût de réparation d’un sinistre, de médiane 2 000 (dollars US) et avec s = 0,8, a une moyenne de 2 754, supérieure à la médiane, et un P90 de 5 576, soit 2,8 fois la médiane.
Ce qu’est une loi log-normale
Là où une loi normale additionne de petits effets indépendants, une loi log-normale les multiplie : une réparation qui se révèle 20 % plus complexe, puis exige une pièce 30 % plus chère, puis prend 10 % de temps en plus. Ses valeurs sont toujours positives, la plupart se regroupent vers le bas, et une queue mince s’étire loin vers la droite : quelques sinistres coûtent plusieurs fois le sinistre typique.
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. Le long tronçon bas à droite correspond aux sinistres rares et coûteux.
Deux nombres la définissent. La médiane est la valeur typique : la moitié des résultats lui sont inférieurs. La dispersion s dit jusqu’où s’étire la queue : vers 0,25, les valeurs restent assez proches de la médiane ; vers 1, certaines sont plusieurs fois plus grandes. (Techniquement, s est l’écart type du logarithme des valeurs.) La moyenne, le pic et les centiles découlent tous de ces deux nombres.
Formules : moyenne, écart type, pic et centiles
| Grandeur | Formule et exemple |
|---|---|
| Moyenne | médiane × es²/2 = 2 000 × e0,32 = 2 754 |
| Écart type | moyenne × √(es² − 1) = 2 608 |
| Pic (mode) | médiane × e−s² = 1 055 |
| P10 et P90 | médiane × e∓1,2816 s = 717 et 5 576 |
L’ordre pic < médiane < moyenne est typique d’une grandeur asymétrique à droite. La moyenne est tirée vers le haut par la queue : seulement 34,5 % des sinistres simulés coûtent plus que le sinistre moyen. Un budget bâti sur la médiane, ou sur le coût le plus probable, reste en dessous de la moyenne.
La loi log-normale dans Excel
LOGNORM.INV(probability, mean, standard_dev) (LOI.LOGNORMALE.INVERSE.N dans Excel en français) se lit facilement de travers : sa moyenne et son écart type sont ceux du logarithme des valeurs, et non ceux des valeurs. Pour le sinistre ci-dessus, ce sont LN(2 000) = 7,60 et 0,8. Saisir la moyenne et l’écart type des coûts eux-mêmes est l’erreur la plus courante avec cette fonction, et elle donne des résultats absurdes. Le fichier Excel garde donc la médiane dans une cellule et passe LN(median) ; en une seule cellule, le tirage s’écrit =LOGNORM.INV(RAND(),LN(D6),F6).
| Cellule | Formule | Valeur |
|---|---|---|
| Nombre aléatoire u (I6) | =RAND() | |
| Tirage (J6) | =LOGNORM.INV(I6, | |
| Tirage en une seule cellule | =LOGNORM.INV(RAND(), | |
| Moyenne (K6) | =D6*EXP(F6^2/2) | 2 754 |
| P10 (L6) | =LOGNORM.INV(0.1, | 717 |
| P90 (M6) | =LOGNORM.INV(0.9, | 5 576 |
Vérifier les chiffres : valeurs exactes comparées à 10 000 tirages d’Excel et de xellstorm
| Valeur exacte | Excel, RAND() | xellstorm | |
|---|---|---|---|
| Moyenne | 2 754 | 2 797 | 2 754 |
| Écart type | 2 608 | 2 659 | 2 603 |
| P10 | 717 | 734 | 717 |
| P50 (médiane) | 2 000 | 2 020 | 2 000 |
| P90 | 5 576 | 5 642 | 5 575 |
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.
Si vous connaissez plutôt la moyenne et l’écart type des valeurs, convertissez-les d’abord : s = √(ln(1 + (sd / mean)²)) et médiane = moyenne / √(1 + (sd / mean)²). Une moyenne de 3 000 et un écart type de 1 500 donnent s = 0,4724 et une médiane de 2 683. Dans Excel : =SQRT(LN(1+(sd/mean)^2)) et =mean/SQRT(1+(sd/mean)^2) (RACINE dans Excel en français). LOGNORM.DIST(x, LN(median), s, TRUE) (LOI.LOGNORMALE.N dans Excel en français) donne la probabilité d’une valeur inférieure à x, et l’ancienne LOGINV fonctionne comme LOGNORM.INV.
Quand utiliser une loi log-normale
- Des coûts, des pertes et des montants de sinistres qui ne peuvent pas être négatifs et atteignent parfois plusieurs fois leur valeur typique.
- Des durées et des délais : un travail se termine rarement beaucoup plus vite que d’habitude, mais peut prendre bien plus longtemps, comme dans l’exemple des délais de livraison, où une loi log-normale est ajustée aux livraisons passées.
- Des prix et des facteurs de croissance qui varient en pourcentage : un prix qui monte ou baisse de pourcentages aléatoires sur de nombreuses périodes finit par suivre une loi log-normale.
Par rapport aux autres choix : une loi normale est symétrique et peut devenir négative ; une distribution PERT ou triangulaire a un maximum ferme, ce qui convient à la fourchette d’un expert mais pas à une grandeur qui peut dépasser plusieurs fois l’estimation ; une loi gamma a le même genre de forme, avec une queue plus mince. Quand vous avez des données, ajustez une log-normale et une gamma, puis comparez.
Saisir une loi log-normale dans xellstorm
- Faites de la cellule une entrée puis, à l’étape Distributions, choisissez Log-normale.
- Ses champs suivent scipy : s est la dispersion du logarithme, scale la médiane et loc un décalage, égal à 0 sauf si les valeurs commencent au-dessus de zéro. Pour le sinistre : s 0,8, scale 2 000, loc 0. Les colonnes Moyenne et P10 – P90 confirment ce que vous avez saisi.
- Si vous raisonnez en centiles, sélectionnez l’entrée et utilisez « À partir d’estimations » dans le panneau latéral : saisissez un P10, un P50 et un P90 et choisissez Log-normale, qui trouve s et la médiane à votre place. Avec des valeurs passées, « À partir de données » ajuste une loi log-normale avec loc 0 et la compare à d’autres formes.
« Importer depuis le fichier » convertit en entrées log-normales les cellules =LOGNORM.INV(RAND(), mu, sigma), ainsi que des fonctions de compléments comme RiskLognorm (moyenne et écart type des valeurs), RiskLognorm2 (de leur logarithme), VoseLognormal et PsiLogNormal.
Questions fréquentes
Comment générer des nombres aléatoires log-normaux dans Excel ?
Utilisez =LOGNORM.INV(RAND(), LN(median), s), où median est la valeur typique et s l’écart type de son logarithme. Pour une médiane de 2 000 et s = 0,8, cela donne =LOGNORM.INV(RAND(), LN(2000), 0.8). Chaque recalcul tire une nouvelle valeur.
Que sont la moyenne et l’écart type dans LOGNORM.INV ?
Ce sont la moyenne et l’écart type de ln(x), souvent notés μ et σ, et non ceux des valeurs elles-mêmes. μ est le logarithme de la médiane : LN(2 000) = 7,60. Pour partir de la moyenne et de l’écart type des valeurs, convertissez-les : σ = √(ln(1 + (sd / mean)²)) et μ = ln(mean) − σ² / 2.
Pourquoi la moyenne d’une loi log-normale est-elle supérieure à sa médiane ?
Parce que sa queue s’étire loin vers la droite alors que les valeurs ne peuvent pas descendre sous zéro : de temps en temps, une valeur très grande tire la moyenne vers le haut. La moyenne vaut la médiane × es²/2, si bien que l’écart grandit avec la dispersion : avec s = 0,8, une médiane de 2 000 donne une moyenne de 2 754.
Log-normale ou normale : laquelle choisir ?
Utilisez une loi normale pour des grandeurs qui varient symétriquement autour d’une cible et restent loin de zéro. Utilisez une loi log-normale pour des grandeurs positives dont les valeurs hautes s’étirent plus loin que les valeurs basses : coûts, pertes, durées, prix. Quand l’écart type est petit devant la moyenne (moins d’un dixième environ), les deux lois sont presque identiques.
À 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 gamma dans Excel : temps d’attente, totaux et GAMMA.INV
La loi gamma décrit des grandeurs positives et asymétriques à droite, comme la durée totale de plusieurs étapes. Forme et échelle, GAMMA.INV et GAMMA.DIST dans Excel, et gamma contre log-normale. - Délais de livraison
Quelle est la probabilité qu’une livraison arrive à temps ?
Ajustez des distributions à un petit historique synthétique de livraisons, comparez les probabilités de ponctualité et découvrez pourquoi davantage d’itérations ne compensent pas un petit échantillon. Fichier Excel inclus.
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.