Parmi les nombreux outils statistiques, la courbe de Gauss va permettre de déterminer si vos données suivent une tendance ou pas.
- Utiliser une des fonctions aléatoires d'Excel
Vous utilisez ALEA, ALEA.ENTRE.BORNES ou TABLEAU.ALEA
- Fonction LOI.NORMALE.INVERSE.N
Cette fonction va permettre de retourne une valeur aléatoire en suivant une moyenne et un écart-type
Fonctions aléatoires dans Excel
Excel propose 3 fonctions pour retourner des nombres aléatoires.
- ALEA
- ALEA.ENTRE.BORNES
- TABLEAU.ALEA.
Par contre, toutes ces fonctions ont un défaut majeur ; elles retournent des valeurs sans contrôler la dispersion.
Dans l'exemple ci-dessous, j'ai utilisé la fonction TABLEAU.ALEA pour retourner une liste de 100 nombres aléatoires entre 20 et 50 afin de simuler des âges fictifs. Dans cet exemple, les 100 valeurs sont réparties de façon similaire sur toute la tranche 20-50 sans suivre un schéma de distribution particulier.
=TABLEAU.ALEA(100;;20;50;VRAI)
C'est quoi une dispersion en statistique ?
Lorsque l'on évoque le terme de dispersion en statistique, cela induit 2 choses :
- La moyenne (ou encore appelé l'espérance).
- L'écart-type, c'est à dire l'écart moyen autour de la moyenne.
Maintenant, pour en revenir à la notion de dispersion, nous allons étudier la courbe suivante, appelée courbe de Gauss ou courbe centrée réduite ou encore courbe en cloche (les 3 termes désignent la même chose).
Comment interpréter une courbe de Gauss ?
Tout d'abord, le maximum de la courbe est atteint pour la valeur 0. Ceci signifie que la valeur la plus représentée dans notre échantillon c'est la valeur 0 ou autour de 0. La valeur 0 est donc l'espérance de notre échantillon.
Ensuite, plus on s'éloigne de l'espérance (la moyenne), plus la courbe s'aplatie, voire devient quasiment plate. Alors c'est quoi la règle ?
Et bien, cette représentation graphique indique la chose suivante :
- Quand les données sont comprises entre l'espérance et 1 fois l'écart-type, nous y retrouvons 68% des données de notre échantillon.
- Entre l'espérance et 2 fois l'écart-type, nous regroupons 95% des données.
- Et 3 fois l'écart-type, nous avons quasiment l'intégralité de l'échantillon ; 99,7%.
Donc, une répartition qui suit une répartition comme une loi normale, regroupe la majorité des données autour de la moyenne. Et plus on s'éloigne de la moyenne, moins on va retrouver de valeurs.
Comment centrer une distribution aléatoire dans Excel ?
Alors comment appliquer la règle de répartition d'une loi normale avec les fonctions aléatoires d'Excel ? Et bien, il existe une fonction qui a été développée pour cela.
Et cette fonction c'est LOI.NORMALE.INVERSE.N 😯😱 (Svp Monsieur Microsoft, vous pouvez faire des noms de formules plus courts ?).
Certes, le nom est long (et surtout a été mal traduit de l'anglais) mais cette fonction va nous être très utile car elle a besoin de tous les éléments nécessaires.
- La probabilité (renvoyée par une formule aléatoire et toujours entre 0 et 1).
- L'espérance.
- L'écart-type.
Par exemple, si je veux générer une série de nombres aléatoires avec une moyenne de 27 pour un écart-type de 4, je vais écrire la formule suivante :
=ARRONDI(LOI.NORMALE.INVERSE.N(TABLEAU.ALEA(500;;0;1;FAUX);27;4);0)
Détaillons la formule
- Tout d'abord, je génère un nombre aléatoire entre 0 et 1 grâce à la formule TABLEAU.ALEA(500;;0;1;FAUX).
- Le paramètre 500 indique que je vais générer une liste de 500 nombres aléatoires.
La raison de créer une liste de 500 valeurs aléatoires permet d'atténuer les nombres aléatoires "parasites" (ceux qui sont au-delà de 3 écart-types).
Ensuite, cette valeur est utilisée comme probabilité dans la fonction LOI.NORMALE.INVERSE.N. Et bien sûr, on rajoute la moyenne souhaitée (27) et l'écart-type (4) de notre série.
Pour finir, on arrondit la valeur retournée à l'entier.
Avec un graphique, on voit que les données renvoyée par la formule sont centrées sur la moyenne.
Par contre, avec cette méthode, il n'est pas possible d'appliquer une borne minimale et maximale directement dans la formule.
