Expert Statistiques

Centrer une valeur aléatoire

Temps de lecture 3 minutes

Fonctions aléatoires dans Excel

Excel propose 3 fonctions pour retourner des nombres aléatoires. Il y a 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.

=TABLEAU.ALEA(100;;20;50;VRAI)

Graphique de representation dune serie aleatoire entre 20 et 50

On peut voir dans cet exemple, que les 100 valeurs sont réparties de façon similaire sur toute la tranche 20-50 sans suivre un schéma de distribution particulier.

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

Si vous ne connaissez pas bien la notion d'écart-type, cet article vous expliquera son intérêt et son application (vous allez voir, c'est facile à comprendre 😉).

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).

Courbe de Gauss avec explication ecart type

Comment interpréter cette courbe ?

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, voir 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, avec une répartition qui suit une répartition comme un 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 valeur.

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 formule plus courts ?)

Certes, le nom et 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éatoire 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 sur, 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.

Et en faisant une représentation graphique des données retournées par notre formule, on voit que la répartition est centrée sur la valeur moyenne 😉

Serie aleatoire centree sur une valeur moyenne

Par contre, avec cette méthode, il n'est pas possible d'appliquer une borne minimale et maximale directement dans la formule.

Related posts

Trouver la valeur la plus proche dans une plage non ordonnée

Frédéric LE GUEN

Recherche partielle avec NB.SI.ENS

Frédéric LE GUEN

Les moyennes mobiles

Frédéric LE GUEN

Laissez un commentaire

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.