Qu'est-ce que la fonction LOI.
LOI.NORMALE.STANDARD.INVERSE (NORM.S.INV en anglais) te sert dès que tu dois fixer un seuil à partir d'un niveau de risque accepté, plutôt que de recopier une valeur depuis une table imprimée : Value at Risk, limite de contrôle qualité, borne d'un intervalle de confiance.
C'est une fonction essentielle pour calculer la Value at Risk en finance, déterminer les limites de contrôle statistique en contrôle qualité, et construire des intervalles de confiance en analyse de données. Les valeurs z de référence que tout statisticien connaît par coeur, 1,645 et 1,96, viennent directement de cette fonction.
Syntaxe
=LOI.NORMALE.STANDARD.INVERSE(probabilité)Clique sur un argument pour aller à son explication.
Cette fonction s'applique uniquement à la distribution normale standard (moyenne = 0, écart-type = 1). Si ta distribution a une moyenne et un écart-type différents, utilise LOI.NORMALE.INVERSE qui accepte ces deux paramètres supplémentaires.
Comprendre chaque paramètre de la fonction LOI.NORMALE.STANDARD.INVERSE
probabilité
: une valeur numérique strictement comprise entre `0` et `1` qui représente la probabilité cumulative de la distribution normale standardC'est la surface sous la courbe de Gauss à gauche du score z que tu cherches.
Par exemple, une probabilité de 0,95 te donnera le score z tel que 95% des valeurs de la distribution se trouvent en dessous de ce score (environ 1,645). Une probabilité de 0,5 retourne 0, c'est-à-dire la médiane.
Attention : La probabilité doit être strictement comprise entre 0 et 1. Les valeurs 0, 1 ou en dehors de cet intervalle génèrent l'erreur #NOMBRE!.
Exemples pratiques pas à pas
Comment convertir une probabilité en score z avec la fonction LOI.NORMALE.STANDARD.INVERSE
Tu dois fixer le score à partir duquel un résultat cesse d'être attribuable au hasard, et ce seuil doit venir d'un calcul et non d'une valeur recopiée depuis une table imprimée. Ton tableau liste la probabilité retenue pour chaque test, sur l'échelle centrée réduite où les scores s'expriment en écarts-types. La fonction LOI.NORMALE.STANDARD.INVERSE rend le score z qui correspond à chacune de ces probabilités.
- 1Dans une cellule, écris
=LOI.NORMALE.STANDARD.INVERSE(. - 2En 1er argument : clique sur la cellule qui porte l'aire cumulée à gauche du score cherché,
B2, soit0,70pour le premier test. C'est le seul argument que la fonction accepte, car la courbe centrée réduite fixe d'office sa moyenne à 0 et son écart-type à 1, si bien que lui en passer un second déclenche une erreur au lieu d'être ignoré. Cette aire doit rester strictement comprise entre 0 et 1, une valeur nulle ou totale ne correspondant à aucun score fini et renvoyant#NOMBRE!. - 3Ferme la parenthèse et appuie sur Entrée, puis recopie la formule vers le bas. Les probabilités supérieures à
0,50rendent un score positif et celles qui lui sont inférieures un score négatif, ce qui offre le contrôle de cohérence le plus rapide sur la colonne.
=LOI.NORMALE.STANDARD.INVERSE(B2)0,50 rend 0,0000 et la ligne 0,05 rend l'opposé exact de la ligne 0,95, ce qui reflète la symétrie de la courbe autour de zéro.
Comment calculer un seuil critique unilatéral avec la fonction LOI.NORMALE.STANDARD.INVERSE
Ton test ne cherche un écart que dans un seul sens, et tu dois annoncer à partir de quel score tu conclus, en fixant d'avance le risque de te tromper. Ce risque est exprimé en queue droite, celle des scores élevés, alors que la fonction ne sait lire que l'aire cumulée depuis la gauche. Le risque doit donc être retourné avant d'être passé à la fonction LOI.NORMALE.STANDARD.INVERSE.
- 1Dans une cellule, écris
=LOI.NORMALE.STANDARD.INVERSE(. - 2En 1er argument : saisis non pas le risque brut mais son complément,
1-B2, pour transformer ce risque de queue droite en aire cumulée à gauche. Un risque de0,1placé enB2devient ainsi0,9, soit la valeur que la fonction attend vraiment. Lui passer le risque tel quel rendrait-1,6449. Ce score, à l'autre bout de la courbe, a l'air correct et ne l'est pas. - 3Ferme la parenthèse et appuie sur Entrée, puis recopie vers le bas. Le résultat est la frontière de la zone de rejet. Diviser le risque par dix ne double pas ce seuil ; il ne monte que de
1,6449à2,3263puis3,0902, parce que la courbe s'aplatit très vite dans ses extrémités.
=LOI.NORMALE.STANDARD.INVERSE(1-B2)1 avant l'appel. Passer 0,05 directement rendrait -1,6449, un score négatif à l'autre bout de la courbe, qui a l'air d'un résultat correct et ne l'est pas. La ligne T-05 retombe sur 1,6449, le seuil de référence des tests unilatéraux à 95 %.
Comment convertir un score z en valeur réelle avec la fonction LOI.NORMALE.STANDARD.INVERSE
Ton test de recrutement est calibré sur une moyenne de 100 et un écart-type de 15, et tu dois publier des paliers exprimés dans cette échelle, parce qu'un score de 0,5244 ne parle à personne. La fonction rend un nombre d'écarts-types, pas un score de test, et le donner tel quel serait une erreur de lecture. Il faut donc le ramener sur l'échelle réelle avant de le communiquer.
- 1Dans une cellule, écris
=100+15*LOI.NORMALE.STANDARD.INVERSE(. Le100+15*posé devant la fonction convertit l'échelle, où15est l'écart-type du test qui redonne au score sa largeur réelle et100sa moyenne qui le recentre. L'ordre des opérations compte, la multiplication ne portant que sur le score z, jamais sur la moyenne déjà ajoutée. - 2En 1er argument : clique sur la cellule du palier visé,
B2, soit0,7pour la première ligne. La fonction remonte de cette aire cumulée à gauche vers le score z correspondant, ici un peu plus d'un demi-écart-type. - 3Ferme la parenthèse et appuie sur Entrée, puis recopie vers le bas. Chaque palier au-dessus de
0,50dépasse 100 et chaque palier en dessous reste sous 100, ce qui vérifie du même coup le recentrage et le changement d'échelle.
=100+15*LOI.NORMALE.STANDARD.INVERSE(B2)P-05 descend à 75,33, symétrique de 124,67 par rapport à la moyenne de 100, ce que la symétrie de la courbe imposait.
Les erreurs fréquentes avec la fonction LOI.NORMALE.STANDARD.INVERSE
Presque tous les ratés viennent de la même confusion : tu tapes 95 en pensant « 95% » alors que la fonction attend une probabilité entre 0 et 1, donc 0,95. Le résultat est un #NOMBRE!, le même que tu obtiens si tu donnes pile 0 ou 1 (ces bornes correspondraient à un score z infini).
L'autre source classique est un argument texte qui déclenche #VALEUR! : pense à diviser un pourcentage par 100 ou à passer la cellule dans CNUM avant de la donner à la fonction.
Probabilité hors limites : erreur #NOMBRE!
L'erreur #NOMBRE! apparaît quand la probabilité n'est pas comprise entre 0 et 1 (exclus). Les valeurs exactement égales à 0 ou 1 génèrent également cette erreur car elles correspondraient à des scores z infinis.
Solution : Utilise =LOI.NORMALE.STANDARD.INVERSE(0,95) et non =LOI.NORMALE.STANDARD.INVERSE(95). Si ta cellule contient un pourcentage saisi comme 95, divise par 100 : =LOI.NORMALE.STANDARD.INVERSE(A1/100).
Type de donnée incorrect : erreur #VALEUR!
Cette erreur survient quand tu passes un argument non numérique à la fonction. Excel ne peut pas convertir automatiquement du texte en nombre pour cette fonction statistique.
Solution : Assure-toi que l'argument est bien un nombre ou une référence à une cellule contenant un nombre. Si nécessaire, convertis le texte avec CNUM : =LOI.NORMALE.STANDARD.INVERSE(CNUM(A1)).
Confusion entre probabilité et pourcentage
L'erreur la plus fréquente est d'entrer un pourcentage (95) au lieu d'une probabilité (0,95). Excel attend toujours une valeur entre 0 et 1.
Solution : Vérifie le format de ta cellule source. Si elle contient 95% formaté en pourcentage Excel, la valeur réelle est déjà 0,95 et tu peux l'utiliser directement. Si elle contient le nombre 95, divise par 100.
LOI.NORMALE.STANDARD.INVERSE vs LOI.NORMALE.INVERSE vs LOI.STUDENT.INVERSE
Prends LOI.NORMALE.STANDARD.INVERSE quand tu raisonnes en scores z purs : VaR, tests standardisés, le fameux 1,96 qui ne bouge jamais. Dès que tes données ont leur propre moyenne et écart-type, passe à LOI.NORMALE.INVERSE qui te ressort directement une valeur dans l'unité réelle au lieu d'un z à reconvertir.
Garde LOI.STUDENT.INVERSE pour les petits échantillons (n < 30) : là, la valeur critique dépend des degrés de liberté et s'écarte du 1,96 normal, par exemple 2,228 pour dl = 10.
| Critère | LOI.NORMALE.STANDARD.INVERSE | LOI.NORMALE.INVERSE | LOI.STUDENT.INVERSE |
|---|---|---|---|
| Distribution ciblée | Normale standard (moy=0, ET=1) | Toute distribution normale | Student (petits échantillons) |
| Arguments | probabilité (1 seul) | probabilité + moyenne + écart_type | probabilité + degrés_liberté |
| Quand l'utiliser | Scores z, VaR, tests standardisés | Intervalle de confiance sur distribution réelle | Petits échantillons (n < 30) |
| Valeur à 95% bilatéral | 1,96 (invariable) | Dépend de la distribution | Varie selon dl (ex : 2,228 pour dl=10) |
Questions fréquentes
LOI.NORMALE.STANDARD.INVERSE s'applique uniquement à la distribution normale standard (moyenne = 0, écart-type = 1) et ne prend qu'un seul argument : la probabilité. LOI.NORMALE.INVERSE permet de travailler avec n'importe quelle distribution normale en spécifiant la moyenne et l'écart-type.
Si tes données ont une moyenne de 50 et un écart-type de 10, utilise =LOI.NORMALE.INVERSE(0,95; 50; 10) directement plutôt que de standardiser manuellement.
Pour un intervalle de confiance bilatéral à 95%, répartis les 5% de risque dans les deux queues (2,5% de chaque côté). La borne inférieure est =LOI.NORMALE.STANDARD.INVERSE(0,025) qui retourne -1,96, et la borne supérieure =LOI.NORMALE.STANDARD.INVERSE(0,975) qui retourne 1,96.
Ces valeurs de -1,96 et +1,96 sont les bornes classiques utilisées dans la majorité des tests statistiques pour les grands échantillons.
Excel retourne l'erreur #NOMBRE! car les probabilités doivent être comprises entre 0 et 1 exclus. Une probabilité de 0 ou 1 exactement génère également une erreur.
Mathématiquement, une probabilité de 0 correspondrait à un score z de moins l'infini, et une probabilité de 1 à plus l'infini. Ces valeurs ne peuvent pas être représentées numériquement.
Pour calculer la VaR à 95% (risque de 5%), utilise la probabilité 0,05 pour obtenir le score z de la queue gauche : LOI.NORMALE.STANDARD.INVERSE(0,05) retourne -1,645. La VaR se calcule ensuite ainsi : =Valeur_portefeuille * (Rendement_moyen + LOI.NORMALE.STANDARD.INVERSE(0,05) * Volatilité).
Avec un portefeuille de 100 000 €, un rendement moyen de 0,05% et une volatilité de 1,2%, la VaR à 95% serait d'environ 1 923 €.
La distribution normale standard est centrée sur 0 (moyenne = 0). La probabilité 0,5 (50%) correspond exactement à la médiane, qui est également 0. Les probabilités inférieures à 0,5 se situent dans la moitié gauche de la distribution, qui correspond aux valeurs négatives.
C'est pourquoi =LOI.NORMALE.STANDARD.INVERSE(0,025) retourne -1,96, une valeur négative qui se trouve à gauche du centre de la courbe.
Oui, exactement. Si LOI.NORMALE.STANDARD(1,96) retourne 0,975, alors LOI.NORMALE.STANDARD.INVERSE(0,975) retourne 1,96. Les deux fonctions sont inverses l'une de l'autre sur leur domaine commun.
Cela signifie que LOI.NORMALE.STANDARD.INVERSE(LOI.NORMALE.STANDARD(x)) retourne toujours x pour tout x réel.
Pour aller plus loin
Continue sur ta lancée après la fonction LOI.NORMALE.STANDARD.INVERSE : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
