Excel 2013 comprend 52 nouvelles fonctions, dont la plupart ont été ajoutées pour être conformes aux normes Open Document Spreadsheet.
Cet article couvrira la fonction Gauss d'Excel 2013.
Actuellement, l'aide d'Excel est un peu terne dans sa description de la fonction.
Syntaxe: =GAUSS(x)- Renvoie 0,5 de moins que la distribution cumulative normale standard.
Pour rappel, la distribution normale standard est un cas particulier avec une moyenne de 0 et un écart type de 1. Vous la reconnaîtrez comme la courbe en cloche.
Excel a toujours eu un moyen de calculer les probabilités pour la courbe normale standard. D'abord NORMSDIST puis dans Excel 2010 NORM.S.DIST (z, True) calculeraient les probabilités. L'argument "z" est le nombre d'écarts types par rapport à la moyenne.
Voici un exemple simple d'utilisation de NORM.S.DIST pour calculer une probabilité. Quelle est la probabilité qu'un membre aléatoire de la population soit inférieur à -0,5 écart-type par rapport à la moyenne? Il s'agit de la zone grisée sur la figure 2. La formule est simple =NORM.S.DIST(-0.5,True).
Assez simple, non? Si vous n'étiez intéressé que par de petites choses, cette formule serait tout ce dont vous avez besoin. Cependant, les chercheurs s'intéressent fréquemment à d'autres plages que le côté gauche de la courbe.
Dans la figure 3, vous voulez connaître la probabilité qu'un membre aléatoire se situe entre (moyenne-0,5 écart-type) et (moyenne + 1 écart-type). Il n'y a pas de fonction NORM.S.DIST.RANGE, vous pouvez donc simplement demander la probabilité entre -0,5,1). Au lieu de cela, vous devez trouver la réponse dans deux sous-formules. Calculez la probabilité d'être inférieur à +1 avec =NORM.S.DIST(1,True), puis soustrayez la probabilité d'être inférieur à -0,5 avec =NORM.S.DIST(-.5,True). Vous pouvez le faire dans une seule formule, comme le montre la figure 3.
Je me rends compte que c'est un long article, mais l'image ci-dessus est l'image la plus importante pour comprendre la nouvelle fonction GAUSS. Relisez ce paragraphe pour vous assurer que vous comprenez le concept. Pour obtenir la probabilité qu'un membre de la population tombe entre deux points de la courbe, vous commencez par la LISTE NORM.S du point de droite et soustrayez la LISTE NORM.S du point de gauche. Ce n'est pas sorcier. Ce n'est même pas aussi compliqué que VLOOKUP. La fonction renvoie toujours la probabilité du bord gauche de la courbe (-infinity) à la valeur de z.
Et si vous êtes intéressé par la probabilité d'être plus grand qu'une certaine taille? Pour trouver la chance d'être plus grand que (moyenne + 1 écart type), vous pouvez commencer par 100% et soustraire la possibilité d'être plus petit que (moyenne + 1 écart type). Ce serait =100%-NORM.S.DIST(1,True). Puisque 100% équivaut à 1, vous pouvez raccourcir la formule à =1-NORM.S.DIST(1,True). Ou, vous pouvez vous rendre compte que la courbe est symétrique et demander la NORM.S.DIST (-1, True) pour obtenir la même réponse.
Pour ceux d'entre vous aussi TOC que moi, je peux vous assurer que si vous =SUM(30.85,53.28,15.87)vous retrouvez avec 100%. Je sais parce que je l'ai vérifié dans la feuille de calcul.
Revenons à la figure 3 - vous devez savoir comment calculer la probabilité à partir de deux points z1 et z2. Soustrayez NORM.S.DIST (z2, True) -NORM.S.DIST (z1, True) et vous aurez la réponse. Considérons le cas très particulier où z1 est la moyenne. Vous essayez de déterminer la probabilité qu'une personne se situe entre la moyenne et +1,5 écarts-types par rapport à la moyenne, comme l'illustre la figure 6.
En utilisant ce que vous avez appris de la figure 3, lequel de ces éléments trouverait la probabilité de l'aire sous la courbe ci-dessus?
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) - Aucune de ces réponses
Comment as-tu fais? Si vous avez répondu A, B ou C, vous avez obtenu un score de 100% au test. Toutes nos félicitations. Comme je l'ai dit, ce n'est vraiment pas sorcier.
Pour ceux d'entre vous qui aiment les raccourcis, rappelez-vous qu'il y a une probabilité de 50% que quelque chose soit inférieur ou égal à la moyenne. Lorsque vous voyez = NORM.S.DIST (0, True), vous pouvez instantanément penser, "Oh - c'est 50%!". Ainsi, la réponse B ci-dessus pourrait être réécrite comme
=NORM.S.DIST(1.5,True)-50%
Mais si vous aimez les raccourcis, vous détestez taper 50% et le raccourciriez à 0,5:
=NORM.S.DIST(1.5,True)-.5
Pourriez-vous utiliser l'opposé symétrique de l'aire sous la courbe? Oui, = .5-NORM.S.DIST (-1.5, True) vous donnera le même résultat. Ainsi, le quiz ci-dessus pourrait être:
-
=NORM.S.DIST(1.5,True)-NORM.S.DIST(0,True) -
=NORM.S.DIST(1.5,True)-.5 -
=.5-NORM.S.DIST(-1.5,True) - Tout ce qui précède
À condition que vous choisissiez une réponse, je vous donnerai tout le crédit. Après tout, c'est Excel. Il y a cinq façons de faire n'importe quoi et j'accepterai toute réponse qui fonctionne (enfin, autre que le codage en dur = 0,433 dans une cellule).
Pour ceux d'entre vous qui ont obtenu la réponse correcte à la dernière question, arrêtez de lire. Tout le monde aura besoin de GAUSS:
Et la fonction GAUSS? Eh bien, la fonction GAUSS nous donne encore une autre façon de résoudre le cas spécifique où la plage va de la moyenne à un point au-dessus de la moyenne. Plutôt que d'utiliser les réponses ci-dessus, vous pouvez utiliser =GAUSS(1.5).
Oui… ils ont ajouté une fonction pour les personnes qui ne peuvent pas soustraire 0,5 de NORM.S.DIST!
Si vous êtes comme moi, vous demandez: "Sérieusement? Ils ont gaspillé des ressources en ajoutant cette fonction?" Eh bien, de retour dans Excel 2007, l'équipe d'Excel a pris la décision de nous permettre d'enregistrer des documents au format .ODS. Il s'agit du format de feuille de calcul Open Document. Ce n'est pas un format contrôlé par Microsoft. Comme ils offrent la prise en charge de l'ODS, Microsoft est obligé d'ajouter toutes les fonctions prises en charge par la feuille de calcul Open Document. Apparemment, la majorité des membres du consortium Open Document Spreadsheet ne pouvaient pas comprendre que la réponse à mon premier quiz était A, ils ont donc ajouté une toute nouvelle fonction.
Je suppose que Microsoft n'était pas ravi d'ajouter la prise en charge de fonctions similaires à d'autres fonctions déjà dans Excel. Je peux presque imaginer la conversation entre le rédacteur technique chargé d'écrire sur GAUSS dans l'aide Excel et le chef de projet de l'équipe Excel:
Écrivain: "Alors, parlez-moi de GAUSS"
PM: "C'est stupide. Prenez =NORM.S.DISTet soustrayez 0,5. Je ne peux pas croire que nous devions ajouter ça."
Le rédacteur a ensuite édité les commentaires éditoriaux et proposé cette rubrique d'aide:
Alors - laissez-moi vous proposer cette autre rubrique d'aide:
GAUSS (z) - Calcule la probabilité qu'un membre d'une population normale standard se situe entre la moyenne et les écarts-types + z par rapport à la moyenne.
- z Obligatoire. Le nombre d'écarts types au-dessus de la moyenne. Généralement compris entre +0,01 et +3.
- Ajouté à Excel 2013 pour prendre en charge les personnes qui ne peuvent pas soustraire deux nombres.
- Pas particulièrement significatif pour les valeurs négatives de Z. Pour calculer la probabilité que quelque chose tombe dans la plage de -1,5 à la moyenne, utilisez
=GAUSS(1.5). - Ne fonctionnera pas dans Excel 2010 et versions antérieures. Dans Excel 2010 et versions antérieures, utilisez
=NORM.S.DIST(z,True)-0.5.
Voilà… plus que vous n'avez jamais voulu en savoir sur GAUSS. C'est certainement plus que je n'ai jamais voulu savoir. À propos, mes livres Excel en profondeur offrent une description complète de toutes les 452 fonctions d'Excel. Consultez l'édition précédente, Excel 2010 en profondeur ou le nouvel Excel 2013 en profondeur qui sortira en novembre 2012.
