LOI.LOGNORMALE.INVERSE.N : Calculer les Inverses de Distribution Lognormale en Excel
=LOI.LOGNORMALE.INVERSE.N(probabilité; moyenne; écart_type)La formule LOI.LOGNORMALE.INVERSE.N est une fonction statistique avancée d'Excel qui permet de calculer la valeur inverse d'une distribution lognormale. Elle est particulièrement utile pour les analystes financiers, les ingénieurs et les statisticiens qui travaillent avec des données suivant une distribution lognormale, notamment dans l'analyse des rendements boursiers, la modélisation des prix de matières premières ou l'évaluation des risques. Cette formule représente l'inverse de la fonction LOI.LOGNORMALE.N et permet de trouver la valeur x correspondant à une probabilité donnée. En d'autres termes, si vous connaissez la probabilité cumulée et les paramètres de la distribution (moyenne et écart-type du logarithme naturel), vous pouvez déterminer le quantile correspondant. C'est un outil indispensable pour les simulations de Monte-Carlo, les analyses de scénarios et la gestion des risques. La distribution lognormale est particulièrement pertinente dans les domaines financiers car elle modélise bien les phénomènes naturels qui ne peuvent pas être négatifs, comme les prix, les rendements ou les durées de vie des équipements.
Syntaxe et paramètres
La syntaxe complète de LOI.LOGNORMALE.INVERSE.N est : =LOI.LOGNORMALE.INVERSE.N(probabilité; moyenne; écart_type). Le premier paramètre, probabilité (ou probability), est obligatoire et doit être une valeur comprise entre 0 et 1 (exclu). Il représente la probabilité cumulée pour laquelle vous souhaitez trouver le quantile correspondant. Par exemple, 0,95 signifie que vous cherchez la valeur en dessous de laquelle se situent 95% des observations. Le deuxième paramètre, moyenne, est également obligatoire et représente la moyenne du logarithme naturel de vos données, c'est-à-dire la moyenne de ln(x). Ce n'est pas la moyenne arithmétique directe des valeurs, mais celle de leurs logarithmes. Le troisième paramètre, écart_type, correspond à l'écart-type du logarithme naturel des données. Il doit être strictement positif et représente la dispersion des logarithmes des valeurs autour de leur moyenne. Conseil pratique : avant d'utiliser cette formule, assurez-vous que vos données suivent effectivement une distribution lognormale. Vous pouvez vérifier cela en calculant le logarithme naturel de vos données et en vérifiant qu'elles suivent une distribution normale. Utilisez les fonctions LN() pour transformer vos données et MOYENNE() et ECARTYPE() pour calculer les paramètres nécessaires.
probabilitymeanstandard_devExemples pratiques
Analyse des rendements boursiers
=LOI.LOGNORMALE.INVERSE.N(0,95; 0,08; 0,15)Cette formule calcule le quantile à 95% de la distribution lognormale. Elle retourne la valeur de rendement en dessous de laquelle se situent 95% des observations historiques, utile pour évaluer les risques extrêmes.
Évaluation des prix de matières premières
=LOI.LOGNORMALE.INVERSE.N(0,75; 3,5; 0,35)Cette formule identifie le prix du pétrole en dessous duquel se situent 75% des prix observés. C'est un indicateur clé pour la planification des achats et la gestion des stocks.
Modélisation de la durée de vie d'équipements
=LOI.LOGNORMALE.INVERSE.N(0,5; 4,2; 0,5)En utilisant la probabilité 0,5 (50e percentile ou médiane), on obtient la durée de vie médiane du composant. C'est un point de référence important pour la planification de la maintenance préventive.
Points clés à retenir
- LOI.LOGNORMALE.INVERSE.N calcule le quantile d'une distribution lognormale pour une probabilité donnée, l'inverse de LOI.LOGNORMALE.N
- Les paramètres doivent être la moyenne et l'écart-type du logarithme naturel des données, pas des données brutes
- La fonction est essentielle pour l'analyse financière, la gestion des risques et les simulations de Monte-Carlo
- Testez toujours que vos données suivent une distribution lognormale avant d'utiliser cette formule pour éviter des résultats invalides
- Combinez avec ALEA() pour générer des scénarios aléatoires et avec SI() pour créer des analyses multi-scénarios
Astuces de pro
Utilisez les noms de plages pour rendre vos formules lisibles. Créez des plages nommées pour la probabilité, la moyenne et l'écart-type, puis utilisez =LOI.LOGNORMALE.INVERSE.N(Probabilité; Moyenne; EcartType). Cela améliore la maintenabilité et réduit les erreurs.
Impact : Augmente la clarté de vos modèles, facilite le débogage et permet aux autres utilisateurs de comprendre rapidement votre logique.
Testez toujours vos données pour la normalité du logarithme avant d'utiliser cette fonction. Créez un graphique Q-Q ou utilisez le test de Shapiro-Wilk en VBA pour confirmer que vos données transformées suivent une distribution normale.
Impact : Évite les erreurs d'interprétation et garantit que vos résultats sont statistiquement valides. Une mauvaise distribution peut conduire à des prévisions erronées.
Pour les simulations de Monte-Carlo, utilisez ALEA.ENTRE(1; 10000) avec ALEA() pour créer plusieurs scénarios. Copiez votre formule avec LOI.LOGNORMALE.INVERSE.N des milliers de fois et analysez les résultats avec MOYENNE, MEDIANE et PERCENTILE.
Impact : Génère des distributions réalistes et permet une analyse de risque robuste. Plus vous avez de simulations, plus vos résultats sont fiables.
Combinez avec RECHERCHEV ou INDEX/MATCH pour automatiser les calculs basés sur des catégories. Par exemple, récupérez automatiquement la moyenne et l'écart-type d'une table de paramètres selon le produit ou le marché.
Impact : Crée des modèles scalables et automatisés qui s'adaptent facilement à de nouvelles données ou catégories sans modification manuelle.
Combinaisons utiles
Intervalle de confiance lognormal
=LOI.LOGNORMALE.INVERSE.N(0,025; moyenne; écart_type) et =LOI.LOGNORMALE.INVERSE.N(0,975; moyenne; écart_type)Combinez deux formules pour créer un intervalle de confiance à 95%. La première calcule le quantile inférieur (2,5e percentile) et la seconde le quantile supérieur (97,5e percentile). Cela encadre 95% des valeurs attendues de la distribution.
Analyse de scénarios avec SI et LOI.LOGNORMALE.INVERSE.N
=SI(A1="Optimiste"; LOI.LOGNORMALE.INVERSE.N(0,75; B1; C1); SI(A1="Pessimiste"; LOI.LOGNORMALE.INVERSE.N(0,25; B1; C1); LOI.LOGNORMALE.INVERSE.N(0,5; B1; C1)))Créez des scénarios d'analyse (optimiste, pessimiste, probable) en combinant SI avec LOI.LOGNORMALE.INVERSE.N. Chaque scénario utilise un percentile différent pour modéliser différentes situations.
Validation de données avec ALEA et LOI.LOGNORMALE.INVERSE.N
=LOI.LOGNORMALE.INVERSE.N(ALEA(); MOYENNE(LN(plage)); ECARTYPE(LN(plage)))Générez des valeurs aléatoires suivant une distribution lognormale pour valider des modèles ou des hypothèses. Combinez ALEA() pour générer des probabilités, LN() pour transformer les données historiques, et MOYENNE/ECARTYPE pour calculer les paramètres.
Erreurs courantes
Cause : La probabilité fournie est hors de l'intervalle ]0; 1[ (par exemple 0, 1, -0,5 ou 1,5), ou l'écart-type est négatif ou égal à zéro.
Solution : Vérifiez que votre probabilité est strictement comprise entre 0 et 1 (0 < P < 1). Assurez-vous que l'écart-type est positif. Exemple correct : =LOI.LOGNORMALE.INVERSE.N(0,95; 0,08; 0,15) au lieu de =LOI.LOGNORMALE.INVERSE.N(1,5; 0,08; 0,15)
Cause : Les paramètres ne sont pas des nombres valides. Par exemple, une cellule contenant du texte est utilisée ou les références de cellules sont incorrectes.
Solution : Vérifiez que tous les paramètres sont des nombres. Utilisez =ISNUMBER() pour tester les cellules. Convertissez le texte en nombres avec VALUE() si nécessaire. Exemple : =LOI.LOGNORMALE.INVERSE.N(A1; B1; C1) où A1, B1 et C1 contiennent des nombres.
Cause : Les références de cellules utilisées dans la formule pointent vers des cellules supprimées ou invalides, ou la plage de références est incorrecte.
Solution : Vérifiez que toutes les cellules référencées existent et contiennent des données valides. Utilisez les noms de plages pour améliorer la clarté. Exemple : =LOI.LOGNORMALE.INVERSE.N(Probabilité; Moyenne; EcartType) en remplaçant les références directes par des noms explicites.
Checklist de dépannage
- 1.Vérifiez que la probabilité est strictement entre 0 et 1 (non inclus). Les valeurs 0 ou 1 génèrent #NUM!
- 2.Confirmez que l'écart-type est positif et non zéro. Un écart-type négatif ou nul provoque une erreur #NUM!
- 3.Assurez-vous que tous les paramètres sont des nombres. Utilisez =ISNUMBER(cellule) pour vérifier chaque entrée
- 4.Vérifiez que les références de cellules existent et ne sont pas supprimées. Un #REF! indique une référence cassée
- 5.Testez que vos données suivent réellement une distribution lognormale en créant un histogramme des logarithmes
- 6.Utilisez =ERREUR.TYPE() pour identifier le type exact d'erreur et affiner votre diagnostic
Cas particuliers
Probabilité très proche de 0 (par exemple 0,0001)
Comportement : La fonction retourne une très petite valeur, proche de zéro. Cela représente le quantile extrêmement bas de la distribution.
Solution : C'est un comportement attendu. Utilisez cette valeur pour identifier les scénarios catastrophe ou les risques extrêmes dans vos analyses.
Utile pour la gestion des risques de queue de distribution (tail risk)
Probabilité très proche de 1 (par exemple 0,9999)
Comportement : La fonction retourne une très grande valeur. Cela représente le quantile extrêmement haut de la distribution.
Solution : C'est un comportement attendu et représente les scénarios optimistes extrêmes. Attention aux valeurs aberrantes qui pourraient biaiser vos analyses.
À utiliser avec prudence pour les prévisions à long terme où les extrêmes peuvent être moins probables
Moyenne = 0 et écart-type = 1 (paramètres standards)
Comportement : La fonction retourne les quantiles d'une distribution lognormale standard. Pour la probabilité 0,5 (médiane), le résultat est 1 (e^0 = 1).
Solution : C'est un cas de référence utile pour valider vos calculs. Vérifiez que =LOI.LOGNORMALE.INVERSE.N(0,5; 0; 1) retourne 1.
Utilisez ce cas pour tester vos formules et vous assurer que tout fonctionne correctement
Limitations
- •La fonction suppose que les données suivent exactement une distribution lognormale. Si ce n'est pas le cas, les résultats peuvent être inexacts ou trompeurs. Toujours valider l'ajustement de la distribution avec des tests statistiques appropriés.
- •Les paramètres (moyenne et écart-type) doivent être calculés à partir de données historiques fiables. Si les données historiques sont biaisées, incomplètes ou non représentatives, les résultats seront également biaisés.
- •La fonction ne gère que les distributions lognormales standards. Elle ne peut pas être utilisée pour les distributions lognormales tronquées, les mélanges de distributions ou les distributions avec paramètres de forme supplémentaires.
- •Pour les très petites probabilités (< 0,001) ou très grandes (> 0,999), la précision numérique peut être affectée. Excel utilise une précision en virgule flottante qui peut introduire des erreurs d'arrondi pour les valeurs extrêmes.
Alternatives
Syntaxe plus courte et compatible avec les versions antérieures d'Excel. Fonctionne identiquement à LOI.LOGNORMALE.INVERSE.N.
Quand : Utilisez cette fonction si vous travaillez avec Excel 2010 ou si vous devez assurer la compatibilité avec des fichiers hérités. Exemple : =LOGNORM.INV(0,95; 0,08; 0,15)
Permet une plus grande flexibilité et une meilleure compréhension du processus mathématique sous-jacent. Utile pour les calculs personnalisés.
Quand : Utilisez =EXP(NORM.INV(probabilité; moyenne; écart_type)) si vous avez besoin de modifier les paramètres ou de comprendre la transformation logarithmique. Cette approche est équivalente à LOI.LOGNORMALE.INVERSE.N.
Offre un contrôle total sur le processus et permet des analyses plus détaillées. Peut être plus transparent pour l'audit.
Quand : Pour les projets complexes nécessitant plusieurs milliers de simulations ou des distributions personnalisées. Créez une colonne avec =ALEA() et appliquez LOI.LOGNORMALE.INVERSE.N à chaque valeur.
Compatibilité
✓ Excel
Depuis Excel 2010
=LOI.LOGNORMALE.INVERSE.N(probabilité; moyenne; écart_type) ou =LOGNORM.INV(probabilité; moyenne; écart_type)✓Google Sheets
=LOGNORM.INV(probabilité; moyenne; écart_type)Google Sheets utilise la syntaxe anglaise LOGNORM.INV. La syntaxe française LOI.LOGNORMALE.INVERSE.N n'est pas disponible. Convertissez vos formules en utilisant les séparateurs appropriés (virgule ou point-virgule selon vos paramètres régionaux).
✓LibreOffice
=LOGNORM.INV(probabilité; moyenne; écart_type)