LOI.LOGNORMALE : Calculer la Distribution Lognormale en Excel
=LOI.LOGNORMALE(x; moyenne; ecart_type)La formule LOI.LOGNORMALE est une fonction statistique avancée d'Excel permettant de calculer la probabilité d'une distribution lognormale pour une valeur donnée. Cette distribution est particulièrement utile en finance, en sciences et en gestion de risques, car elle modélise des phénomènes naturels qui ne peuvent prendre que des valeurs positives, comme les rendements boursiers, les durées de vie des équipements ou les concentrations de polluants. Contrairement à la distribution normale classique qui peut générer des valeurs négatives, la distribution lognormale garantit des résultats strictement positifs. Cela en fait l'outil idéal pour analyser des données asymétriques où les petites valeurs sont plus fréquentes que les grandes. La fonction LOI.LOGNORMALE calcule la fonction de densité de probabilité (PDF) ou la fonction de répartition cumulative (CDF) selon les paramètres fournis. Comprendre et maîtriser cette formule est essentiel pour les analystes financiers, les statisticiens et les professionnels du contrôle qualité qui travaillent avec des données non-normales. Elle s'intègre parfaitement dans des modèles prédictifs sophistiqués et permet de prendre des décisions éclairées basées sur des distributions probabilistes réalistes.
Syntaxe et paramètres
La syntaxe complète de LOI.LOGNORMALE est : =LOI.LOGNORMALE(x; moyenne; ecart_type). Le paramètre x représente la valeur à évaluer, qui doit être strictement positive (x > 0), sinon la fonction retourne une erreur. La moyenne correspond à la moyenne du logarithme naturel de x, notée μ (mu), qui définit le centre de la distribution sur l'échelle logarithmique. L'écart_type est l'écart-type du logarithme naturel de x, noté σ (sigma), qui contrôle la dispersion de la distribution. Il est crucial de comprendre que ces paramètres ne sont pas directement les caractéristiques de x lui-même, mais celles de ln(x). Par exemple, si vous avez une série de rendements boursiers, vous devez d'abord calculer le logarithme naturel de chaque rendement, puis déterminer la moyenne et l'écart-type de ces logarithmes. Cette distinction est souvent source de confusion. La fonction retourne une valeur entre 0 et 1 représentant la probabilité cumulative. Pour obtenir la densité de probabilité plutôt que la probabilité cumulative, vous devez utiliser une version alternative ou appliquer des calculs supplémentaires. En pratique, utilisez LOGNORM.DIST() dans les versions récentes pour plus de flexibilité.
xmeanstandard_devExemples pratiques
Analyse de rendements boursiers
=LOI.LOGNORMALE(1,1618; 0,10; 0,08)On convertit d'abord le rendement de 15% en valeur réelle : e^0,15 ≈ 1,1618. La formule calcule la probabilité cumulative que le rendement soit inférieur à ce seuil avec les paramètres statistiques donnés.
Durée de vie des composants électroniques
=LOI.LOGNORMALE(1,0289; 5,5; 0,3)La valeur 250 heures se convertit en années puis en logarithme. La fonction retourne la probabilité de défaillance avant ce délai, essentielle pour la planification de maintenance préventive.
Concentration de polluants atmosphériques
=LOI.LOGNORMALE(25; 3,2; 0,45)La formule évalue directement la valeur réelle (25) en calculant sa probabilité cumulative dans la distribution lognormale définie par les paramètres. Ceci aide à évaluer les risques de dépassement de seuils de qualité de l'air.
Points clés à retenir
- LOI.LOGNORMALE calcule la probabilité cumulative pour une distribution lognormale, idéale pour les données strictement positives et asymétriques.
- Les paramètres moyenne et écart_type doivent être calculés sur LN(x), pas sur x lui-même. C'est la source principale d'erreur.
- Préférez LOGNORM.DIST pour les versions récentes d'Excel, qui offre plus de flexibilité avec le choix entre PDF et CDF.
- Combinez avec LOGINV pour les analyses inverses, les simulations Monte Carlo et la détermination de seuils de risque.
- Validez toujours les données avec des tests statistiques et des graphiques Q-Q plot pour confirmer que la distribution lognormale est appropriée.
Astuces de pro
Toujours valider que moyenne et écart_type sont calculés sur LN(données), pas sur les données brutes. Utilisez =AVERAGE(LN(plage)) et =STDEV(LN(plage)) pour éviter les erreurs systématiques.
Impact : Évite 80% des résultats aberrants et garantit une modélisation statistiquement correcte.
Utilisez des noms de plages pour les paramètres : définissez 'MoyenneLN' et 'EcartTypeLN' dans le gestionnaire de noms. Cela rend les formules lisibles et maintenables : =LOI.LOGNORMALE(x; MoyenneLN; EcartTypeLN)
Impact : Améliore la clarté du modèle et facilite les audits et modifications futures.
Combinez avec SI() pour gérer les cas limites : =SI(x<=0; "Invalide"; LOI.LOGNORMALE(x; moy; écart)). Cela prévient les erreurs #NUM! et rend le modèle robuste.
Impact : Augmente la fiabilité et permet une gestion élégante des données aberrantes.
Créez un tableau de sensibilité en faisant varier moyenne et écart_type pour comprendre leur impact sur la distribution. Visualisez avec un graphique XY pour intuitionner le comportement.
Impact : Développe une compréhension profonde de la distribution et aide à identifier les paramètres critiques.
Combinaisons utiles
Intervalle de confiance lognormal
=LOGINV(0,025; moyenne; écart_type) et =LOGINV(0,975; moyenne; écart_type)Combinez LOGINV avec LOI.LOGNORMALE pour créer un intervalle de confiance à 95%. Les deux LOGINV calculent les bornes inférieure et supérieure correspondant aux percentiles 2,5% et 97,5%.
Probabilité entre deux valeurs
=LOI.LOGNORMALE(x2; moy; écart) - LOI.LOGNORMALE(x1; moy; écart)Calculez la probabilité qu'une valeur soit comprise entre x1 et x2 en soustrayant deux CDF. Utile pour définir des plages de rendements acceptables ou des bandes de tolérance.
Simulation Monte Carlo avec ALEA()
=EXP(LOI.NORMALE.INVERSE(ALEA(); moyenne; écart_type))Générez des valeurs aléatoires suivant une distribution lognormale en combinant LOI.NORMALE.INVERSE avec ALEA(). Idéal pour les simulations de risque financier ou les prévisions probabilistes.
Erreurs courantes
Cause : La valeur x est négative, zéro ou l'écart-type est négatif/zéro. La distribution lognormale n'accepte que des valeurs strictement positives et un écart-type positif.
Solution : Vérifiez que x > 0 et ecart_type > 0. Utilisez =SI(x<=0; "Erreur"; LOI.LOGNORMALE(x; moyenne; ecart_type)) pour valider les données avant le calcul.
Cause : Les paramètres contiennent du texte ou des références invalides. Par exemple, une cellule contenant 'N/A' ou un format de texte au lieu d'un nombre.
Solution : Vérifiez que tous les paramètres sont numériques. Utilisez ISNUMBER() pour valider : =SI(ISNUMBER(x); LOI.LOGNORMALE(x; moyenne; ecart_type); "Données invalides")
Cause : Une référence de cellule a été supprimée ou déplacée, rendant la formule invalide. Cela survient souvent lors de suppressions de colonnes ou de lignes.
Solution : Recalculez les références en naviguant jusqu'aux cellules mentionnées. Utilisez le gestionnaire de noms pour vérifier les plages nommées, ou réécrivez la formule avec les bonnes références.
Checklist de dépannage
- 1.Vérifier que x > 0 (strictement positif). Les valeurs négatives ou zéro génèrent #NUM!
- 2.Confirmer que moyenne et écart_type sont basés sur LN(données), pas sur les données brutes
- 3.S'assurer que écart_type > 0. Un écart-type nul ou négatif provoque une erreur
- 4.Vérifier les formats de cellules : tous les paramètres doivent être numériques, pas du texte
- 5.Tester avec des valeurs connues : calculer manuellement pour une valeur simple et comparer
- 6.Examiner les références de cellules pour détecter les liens brisés ou les suppressions accidentelles
Cas particuliers
x = 1 (logarithme naturel égal à 0)
Comportement : La fonction retourne la probabilité cumulative au point où ln(x) = 0. Cela correspond à la probabilité que la valeur soit inférieure à 1.
Solution : Le résultat est valide et représente souvent le point médian si moyenne = 0. Vérifiez que ce point a du sens dans votre contexte métier.
Utile pour tester la cohérence de votre modèle.
Très grande valeur de x (ex : x = 1 000 000)
Comportement : La fonction retourne une probabilité très proche de 1, mais peut rencontrer des problèmes de précision numérique si l'écart-type est très petit.
Solution : Vérifiez les paramètres et envisagez une transformation d'échelle. Utilisez des valeurs logarithmiques directement si possible.
Les limites numériques d'Excel peuvent affecter la précision pour les extrêmes.
Écart-type très proche de 0 (ex : 0,001)
Comportement : La distribution devient très concentrée autour de la moyenne. Les valeurs éloignées ont une probabilité quasi nulle.
Solution : Ceci est mathématiquement correct. Vérifiez que cela reflète vraiment votre réalité (faible variabilité). Sinon, réexaminez le calcul de l'écart-type.
Utile pour modéliser des processus très stables ou pour tester les limites du modèle.
Limitations
- •LOI.LOGNORMALE retourne uniquement la probabilité cumulative (CDF), pas la densité de probabilité (PDF). Pour cette dernière, utilisez LOGNORM.DIST avec le paramètre cumulative = FAUX.
- •La fonction suppose que les données suivent exactement une distribution lognormale. Si ce n'est pas le cas, les résultats seront biaisés. Validez toujours avec des tests statistiques (Shapiro-Wilk, Kolmogorov-Smirnov).
- •Les paramètres moyenne et écart_type doivent être estimés correctement à partir de LN(données). Une mauvaise estimation (par exemple, utiliser directement les statistiques de x au lieu de ln(x)) invalide complètement les résultats.
- •Excel a des limites de précision numérique pour les valeurs extrêmes. Les probabilités très proches de 0 ou 1 peuvent perdre en précision. Pour les analyses critiques, envisagez des outils statistiques spécialisés comme R ou Python.
Alternatives
Compatibilité
✓ Excel
Depuis Excel 2007
=LOI.LOGNORMALE(x; moyenne; ecart_type)✓Google Sheets
=LOGNORMDIST(x; moyenne; ecart_type)Google Sheets utilise LOGNORMDIST au lieu de LOI.LOGNORMALE. La syntaxe est identique mais le nom change. Attention aux séparateurs (virgule vs point-virgule selon la locale).
✓LibreOffice
=LOGNORMDIST(x; moyenne; écart_type)