C3 : Manipulations statistiques 1- Génération de valeurs aléatoires 2

C3 : Manipulations statistiques
Dorat Rémi
1- Génération de valeurs aléatoires
p2
2- Statistiques descriptives
p3
3- Tests statistiques
p8
4- Régression linéaire
p8
Manipulations statistiques 1
1- Génération de valeurs aléatoires
Fonction de génération de base et construction de générateurs
La fonction ALEA() renvoie un nombre aléatoire tiré uniformément dans [0;1]. A partir de cette fonction
simple, il est possible de construire différents générateurs aléatoires. Soit par exemple une cellule nommée a qui
contient une valeur numérique positive, une cellule nommée b qui contient une valeur numérique positive telle que b>a.
●
Dans ce cas, la fonction
a*ALEA()
renvoie un nombre dans [0;a]
●
En continuant, on a
(b-a)*ALEA() qui renvoie un nombre dans [0;b-a]
●
Enfin, la fonction
(b-a)*ALEA()+a
renvoie un nombre dans [a;b]
Génération de nombres entiers
En première approche, pour tirer des valeurs entières, on utilise le générateur de valeurs réelles. Par
exemple, pour tirer un nombre entier dans [N;M], on commence par nommer une cellule N qui contient la valeur de la
borne inférieure, une cellule M qui contient la valeur de la borne supérieure. Comme fonction de tirage aléatoire d'un
nombre entier, on peut proposer : ARRONDI((M-N)*ALEA()+N) qui tire une valeur aléatoire dans [N;M] et arrondit
ensuite cette valeur à l'entier le plus proche.
Une telle fonction est cependant biaisée, étudions l'évolution de ARRONDI((M-N)*ALEA()+N) en fonction
de la valeur de (M-N)*ALEA()+N :
La valeur de (M-N)*ALEA()+N est dans :
Valeur de ARRONDI((M-N)*ALEA()+N)
[N;N+0.5[
N
[N+0.5;N+1.5[
N+1
[N+1.5;N+2.5[
N+2
...
...
[M-1.5;M-0.5[
M-1
[M-0.5;M]
M
On remarque que la taille de l'intervalle qui permet d'obtenir N est 0.5, de même que la taille de l'intervalle qui permet
d'obtenir M. En revanche, la taille de l'intervalle permettant d'obtenir K entier dans ]N;M[ est 1. Donc, le générateur
aléatoire ARRONDI((M-N)*ALEA()+N) ne produit pas une distribution uniforme sur les entiers. Pour produire une
distribution uniforme, on part d'un tirage dans l'ensemble [N-0,5;M+0,5] : les arrondis des valeurs tirées dans cet
Manipulations statistiques 2
ensemble sont des entiers dans [N;M]. Le générateur :
ARRONDI((M-N+1)*ALEA()+N-0.5) permet de faire le tirage uniforme d'entiers dans [N;M].
Les générateurs d'Excel
Les générateurs construits proposés dans les paragraphes précédents recoupent des outils d'Excel. En
effet, non seulement on dispose du générateur de base ALEA(), mais également d'une fonction aléatoire qui permet de
tirer des nombres entier dans un intervalle : ALEA.ENTRE.BORNES(2;5) renvoi des entiers tirés au hasard
uniformément dans [2;5].
Construction d'un générateur de valeurs Gaussiennes-La méthode de Box-Mueller
Une méthode permet de générer des valeurs aléatoires gaussiennes à partir d'un générateur uniforme. Il
s'agit de la méthode de Box Mueller. Celle-ci consiste à tirer des valeurs uniformes. Pour un couple
[−1 ;1]
valeurs réelles tirées uniformément dans

v 0= x∗ −2∗ln
 s
s
et

v 1= y∗ −2∗ln
, en posant
s=x 2 y 2
, et si
s1
x , y
de
, alors les valeurs :
 s
s
sont à la base d'une distribution gaussienne centrée réduite. Par suite, il est possible de générer des tirages aléatoires
pour n'importe quelle gaussienne : cf a*N(0,1)+b.
Disposer d'un générateur de valeurs aléatoires s'avère nécessaire pour la simulation de processus, les
simulations Monte-Carlo etc.
2- Statistiques descriptives
Les moments
Excel propose une série de fonctions qui permettent d'obtenir les moments de base d'une distribution. On
passe une plage de valeurs en paramètres : =MOYENNE(A2:A14) (ou plusieurs plages =MOYENNE(A2:A14;B2;8)). De
la même manière, on peut utiliser les fonctions :
●
ECARTYPE
●
VAR pour la variance
●
KURTOSIS
D'autres caractérisations classique des distributions :
Manipulations statistiques 3
●
MEDIANE
●
MODE : renvoie la valeur la plus fréquence d'une plage de données.
●
COEFFICIENT.ASYMETRIE
●
MOYENNE.HARMONIQUE
Covariance et coefficient de corrélation
D'autres coefficients d'évaluation statistique :
●
COVARIANCE : calcul le coefficient de COVARIANCE entre deux séries selon la formule classique :
n
 ∗ y i−Y 
cov  X , Y =∑  x i− X
i=1
●
COEFFICIENT.CORRELATION : calcule le coefficient de corrélation entre deux séries.
CoeffCorr X ,Y =
Cov  X , Y 
 X  Y 
Calcul de distributions à partir de données disponibles
Pour obtenir une distribution, il existe plusieurs solutions. On en présente deux en parallèle : l'utilisation de
la fonction NB.SI qui a été vu dans le cours précédent et l'utilisation de la fonction FREQUENCE : ces deux fonctions
produisent un résultat équivalent.
Pour la fonction de fréquence, on donne comme premier paramètre la plage des valeurs dont on veut
obtenir la distribution et comme second paramètre une plage de valeurs qui détermine la distribution. Par exemple, on
suppose avoir la liste des valeurs dont on veut obtenir la distribution dans la plage A1:A300 et la série des valeurs qui
déterminent les intervalles dans B1:B11. Dans ce cas, on rentre la formule matricielle =FREQUENCE(A1:A300;B1:B11),
on a introduite une telle formule dans la colonne C. Si par exemple on a mis une série de valeurs aléatoires tirées entre
8 et 9 dans la colonne A (=(9-8)*ALEA()+8), alors :
Manipulations statistiques 4
On constate qu'en C2, on a l'ensemble des valeurs entre B2 et B1, qu'en C3, on a l'ensemble des valeurs entre B3 et B2
etc. Pour l'autre manière de faire la distribution : on introduit =NB.SI($A$1:$A$300;"<"&B1) dans E1, =NB.SI($A$1:$A
$300;"<"&B2) dans E2 etc. Ensuite, dans F2, on met E2-E1, dans F3, on met E3-E2 etc. On obtient donc la même
distribution dans la colonne C et dans la colonne F.
D'autres fonctions statistiques
La fonction QUARTILE. Cette fonction prend deux arguments : une plage de données et une option. Le
paramètre d'option peut prendre les valeurs 0, 1, 2, 3 ou 4. Pour 0, la fonction renvoie la valeur minimale. Pour 1, la
fonction renvoie la valeur telle que le quart des valeurs sont plus petites, le reste des valeurs étant plus grandes. Pour 2,
la fonction renvoie la médiane. Pour 3, la fonction renvoie la valeur telle que un quart des valeurs lui sont supérieures.
Pour 4, la valeur renvoie la valeur maximale de la distribution.
La fonction CENTILE fonctionne de la même manière que QUARTILE, elle prend en premier paramètre
une plage de données et en second paramètre une valeur comprise entre 0 et 1. Soit para2 le second paramètre. La
fonction renvoie la valeur V telle qu'une part para2 des valeurs de la plage de données sont inférieures à V, le reste
étant supérieur.
La fonction GRANDE.VALEUR prend deux paramètres : une plage de données et un entier K, elle renvoie
la Kème plus grande valeur de la plage de donnée. De la même manière, il existe une fonction PETITE.VALEUR pour
renvoyer la Kème plus petite valeur.
Information sur les distributions classiques
Excel donne accès à une série de distributions classiques. Pour chacune de ces distributions, le logiciel
Manipulations statistiques 5
propose à la fois une fonction permettant, à partir d'une valeur, d'établir sa probabilité, de manière cumulée (ensemble
des valeurs inférieures) ou la valeur associée en fonction de la fonction de distribution classique. Par exemple, pour
savoir quelle est la probabilité associée à la valeur 2 pour une gaussienne centrée réduite :
=LOI.NORMALE(2;0;1;FAUX)
Les paramètres fonctionnent de la sorte :
●
2 est la valeur x pour laquelle on veut connaître la valeur par la fonction de probabilité.
●
0 est la moyenne de la gaussienne qu'on utilise
●
1 est l'écart-type de la gaussienne qu'on utilise
●
FAUX signifie que l'on veut la valeur de la fonction de distribution et non pas la somme des f(x) pour tout x
inférieur à 2 (la fonction de répartition).
Pour une gaussienne centrée réduite, on peut directement utiliser LOI.NORMALE.STANDARD : en lui passant une
valeur x*, on obtient la somme des f(x) pour x<x*.
Tracer une distribution de probabilité devient alors assez simple. On intruduit :
et on obtient :
Manipulations statistiques 6
0,45
0,4
0,35
0,3
0,25
0,2
Série1
0,15
0,1
0,05
0
-6
-4
-2
0
2
4
6
D'autres distributions de probabilité classiques sont utilisables de la même manière (à voir à chaque fois leur
paramétrage dans l'aide associée à chaque fonction) :
●
LOI.BETA
●
LOI.BINOMIALE
●
LOI.EXPONENTIELLE
●
LOI.F
●
LOI.GAMMA
●
LOI.HYPERGEOMETRIQUE
●
LOI.KHIDEUX
●
LOI.POISSON
●
LOI.STUDENT
●
LOI.WEIBULL
Pour la plupart de ces fonctions, il est également possible d'obtenir l'inverse de la distribution : ie pour une probabilité, il
est possible de déterminer la valeur x qui donne cette probabilité par la fonction de distribution. Par exemple, on veut
savoir quelle est la valeur x qui donne la probabilité 0,3 par la loi normale :
=LOI.NORMALE.INVERSE(0,3;0;1) :
●
0,3 est la probabilité pour laquelle on veut connaître la valeur qui donne cette probabilité par la fonction de
distribution
Manipulations statistiques 7
●
0 est la moyenne de la loi normale utilisée
●
1 est l'écart type de la loi normale utilisée
3- Tests statistiques
http://www.bibmath.net/dico/index.php3?action=affiche&quoi=./c/chideuxtest.html
On propose une feuille d'exemple pour un test du CHI-DEUX dans une feuille jointe.
Voire le livre et les feuilles qui sont sur le CD.
Parler de la fonction PEARSON, parler des renvois de valeur de test.
●
INTERVALLE.CONFIANCE : renvoie l'intervalle de confiance pour la moyenne d'une population
●
TEST.F : à partir de deux ensembles de valeurs, donne la probabilité que la probabilité soit la même.
TEST.KHIDEUX
TEST.STUDENT
TEST.Z
4- Regressions
Excel propose un certain nombre de fonctions reposant sur la régression linéaire : des fonctions de
prévision, des calculs de tendance etc...
Fonction de base pour la régression
On se place dans le cas suivant : dans la colonne A, de A1 à A300, on a une série de valeurs pour une
variable explicative X, dans la colonne B, de B1 à B300, on a une série de valeurs pour la variable expliquée Y. On
suppose que Y=a*X+b et on veut approximer les coefficients a et b. Excel résout ce problème en utilisant la méthode
classique des moindres carrés ordinaires.
Pour obtenir les coefficients de la régression, on utilise la fonction DROITEREG. Cette fonction prend 4
paramètres dont 3 qui sont optionnels. Le premier paramètre est la plage contenant les y_connus, soit les valeurs
connues de la variables expliquées. Si aucun autre argument n'est spécifié, le logiciel utilisera des valeurs de la variable
Manipulations statistiques 8
explicative par défaut (1, 2, 3, 4 etc). Le troisième argument possible est soit VRAI ou omis et dans ce cas, le logiciel
fera une régression de la forme Y=a*X+b, soit il est FAUX et dans ce cas, le logiciel fera une régression Y=aX (c'est à
dire que b sera fixé à 0). Enfin, le 4ème argument est soit FAUX, soit VRAI. S'il est fixé à VRAI, la machine calculera des
statistiques complémentaires sur la regression.
Le résultat de la fonction est une matrice de régression contenant deux colonnes (une colonne pour le
paramètre a, une colonne pour le paramètre b) et contenant 5 lignes si on a demandé le calcul de statistiques
complémentaires, 1 ligne sinon. Un exemple de régression vous est proposé dans le classeur C3_TestRegression.xsl. Si
on demande l'affichage de statistiques supplémentaires :
●
En première ligne, on a les estimations pour les paramètres a et b
●
En seconde ligne : les valeurs d'erreur type pour l'estimation de a et de la constantes
●
En troisième ligne : le coefficient de détermination r² : ce coefficient est entre 1 (pour une régression parfaite) et
0. En deuxième colonne et troisième ligne : le coefficient d'erreur type pour la valeur y estimée.
En quatrième ligne : la statistiques F ou valeur F observée en première colonne, le nombre des degrés de
●
liberté en seconde colonne.
En cinquième colonne : la somme de regression des carrés en première colonne, la somme résiduelle des
●
carrés en seconde colonne.
Soit le tableau suivant pour le cas d'une régression linéaire à deux paramètres.
Estimation a
Estimation b
Erreur Type a
Erreur Type b
Coefficient
Erreur type y
détermination
Statistique F
Degrés de liberté
Somme de régression Somme résiduelle des
des carrés.
carrés
Régression linéaire : le cas de plus d'une variable explicative
Il est possible de vouloir faire une régression linéaire selon un schéma :
N
Y =∑ a i∗X ib
plutôt que selon un schéma
Y =a∗X b
i=1
Dans ce cas, le deuxième paramètre de la fonction DROITEREG ne doit pas être une plage de cellules d'une colonne
unique, mais une plage de cellules comprenant plusieurs colonnes. Dans ce cas, si l'option Statistiques est à VRAI lors
Manipulations statistiques 9
du calcul de la fonction, la matrice de retour contiendra deux lignes de N+1 colonnes, N étant le nombre des colonnes de
la matrice, et trois lignes de 2 colonnes. Soit (attention à l'ordre des coefficients estimés) :
Estimation a4
Estimation a3
Estimation a2
Estimation a1
Estimation b
Erreur Type a4
Erreur Type a3
Erreur Type a2
Erreur Type a1
Erreur Type b
Coefficient
Erreur type y
détermination
Statistique F
Degrés de liberté
Somme de régression Somme résiduelle des
des carrés
carrés.
Un exemple de telle régression est proposée sur le classeur C3_TestRegression.xsl
Autres fonctions utilisant la régression linéaire
La fonction TENDANCE : cette fonction permet le calcul de valeurs à partir d'une tendance linéaire. La
fonction PENTE renvoie la pente obtenue à partir d'une régression linéaire. Enfin, la fonction ORDONNEE ORIGINE
calcule le point auquel une droite doit croiser l'axe des ordonnées à partir d'une régression linéaire.
La fonction LOGREG : une autre régression que la régression linéaire
On ne développe pas l'utilisation de cette fonction qui est identique à la fonction DROITEREG.
Manipulations statistiques 10