Formation sur les fonctions statistiques sous excel
Formation sur les fonctions statistiques sous excel
1. Utilité des Statistiques
Les objectifs
- Avoir une idée du comportement d’un événement ou dela valeur normale d’une donnée.
- Décrire et estimer la valeur des paramètres et déterminer des critères de décisions.
- Prévoir et éventuellement expliquer un comportementou une donnée.
Les moyens
- Différents loi de répartition des données existentElles. ont chacune leurs domaines d’applications (Loi Gaussienne, de Poisson, Gamma, Binomiale,…)
- Des tests de validité des lois permettent de vérifier la pertinence de leur utilisation suivant les situations analysées. (Khi², Corrélation,…)
- Des paramètres permettent de caractériser les lois,(Moyenne, Variance, Minimum, Maximum, Médiane,….)
Le rôle du statisticien est de :
- Récolter judicieusement les données d’une situation
- Trouver la loi qui correspond le mieux à cette situ ation.
- Vérifier la validité de cette loi.
- Eventuellement, Prévoir et expliquer les situationsnormales et anormales.
2. Les Statistiques dans EXCEL
Le logiciel « Excel » permet de réaliser des opérations de statistique à l’aide des Fonctions de catégorieStatistique.
L’utilisation de ces fonctions nécessite une connaissances du champs d’application de la fonction mathématique et de se mise en pratique.
La mauvais utilisation des ces fonctions pourrait entrainer des conclusions erronées et des actions correctives inadaptées.
Par exemple, Excel propose 5 variantes de calcul de la moyenne d’un série de valeurs. Chaque variante donne un résultat différent.
Le premier Tableau Reprend toutes les Fonctions Statistiques disponibles dans EXCEL 2003, et les descriptif associés.
3. Enumération
…
| LOI.BINOMIALE.NEG(nombre_échecs;nombre_succès;probabilité_succès) | Renvoie la probabilité d'une variable aléatoire discrète suivant une loi binomiale négative. La fonction LOI.BINOMIALE.NEG renvoie la probabilité d'obtenirun nombre d'échecs égal à l'argument nombre_échecsavant de parvenir au succès dont le rang est donné par l'argument nombre_succès, lorsque la probabilité de uccès, définie par l'argument probabilité_succès, est constante. Cette fonction est similaire à la loi binomiale, à l a différence que le nombre de succès est fixe et le nombre d'essais variable. Comme pour la loi binomiale, les essais sont supposés indépendants. |
| LOI.EXPONENTIELLE(x;lambda;cumulative) | Renvoie la distribution exponentielle. Utilisez la fonction LOI.EXPONENTIELLE pour prévoir la durée séparant des événements, tel le temps mis par un distributeur automatique bancaire pour délivrer de l'argent. Par xemple, vous pouvez utiliser LOI.EXPONENTIELLE pour calculer la probabilité que l'opération dure moins d'une minute. |
| NBVAL(valeur1;valeur2;...) | Compte le nombre de cellules qui ne sont pas vides et les valeurs comprises dans la liste des arguments. Utilisez NBVAL pour compter le nombre de cellules contenant des données dans une plage ou une matrice. Calcule le point auquel une droite doit couper l'axe des ordonnées en utilisant les valeurs x et y existantes. |
| ORDONNEE.ORIGINE (y_connus;x_connus) | L'ordonnée à l'origine est déterminée en traçant une droite de régression linéaire qui passe par les aleursv x et y connues. Utilisez la fonction ORDONNEE.ORIGINE pour déterminer la valeur de la variable dépendante lorsque la variable indépendante est égale à 0 (zéro). |
| PEARSON (matrice1;matrice2) | Renvoie le coefficient de corrélation d'échantillonnage de Pearson r, un indice dont la valeur varie entre -1,0 et 1,0 inclus qui reflète le degré de linéarité entre deuxséries de données. |
| PENTE(y_connus,x_connus) | Renvoie la pente d'une droite de régression linéaire à l'aide de données sur les points d'abscisse etd'ordonnée connus. La pente est la distance verticale divisée par la distance horizontale séparant deux points d'une ligne ; elle exprime le taux de changement le long de la droite de régression. |
| PERMUTATION(nombre;nombre_choisi) | Renvoie le nombre de permutations pour un nombre donné d'objets pouvant être sélectionnés à partir nd'unombre d'objets déterminé par l'argument nombre. Une permutation est un ensemble ou un sous-ensemble d'objets oud'événements ordonnés de façon précise et significative. En cela, les permutations diffèrent des combinaisons pour lesquelles l'ordre des éléments n'est pas significatif. Utilisez cette fonction dans les calculs de probabilité de type loterie. |
| PETITE.VALEUR(matrice;k) | Renvoie la k-ième plus petite valeur d'une série dedonnées. Utilisez cette fonction pour renvoyer desvaleurs avec une position relative particulière à l'intérieur d'une série de données. |
| PREVISION(x;y_connus;x_connus) | Calcule ou prévoit une valeur capitalisée à partirde valeurs existantes. La valeur prévue est une valeur x pour une valeur y donnée. Les valeurs connues sont des valeurs x et y existantes, et la nouvelle valeur prévueest calculée par la méthode de régression linéaire. Vous pouvez utiliser cette fonction pour établir des prévisions deventes, des besoins en stock ou des tendances de consommation. |
| PROBABILITE(plage_x;plage_probabilité;limite_inf; limite_sup) | Renvoie la probabilité que des valeurs d'une plagesoient comprises entre deux limites. Si l'argument limite_sup n'est pas fourni, la fonction renvoie la probabilité queles valeurs de l'argument plage_x soient égales à limite_inf. |
| QUARTILE(matrice;quart) | Renvoie le quartile d'une série de données. Les quartiles sont souvent utilisés pour les données relatives aux ventes et aux enquêtes afin de séparer les populations en groupes. Ainsi, vous pouvez utiliser la fonction QUARTILE pour déterminer les vingt-cinq pour cent de revenus lesplus élevés d'une population. |
| RANG(nombre;référence;ordre) | Renvoie le rang d'un nombre dans une liste d'arguments. Le rang d'un nombre est donné par sa taille comparée aux autres valeurs de la liste. (Si vous deviez trier la liste, le rang d'un nombre serait sa position). |
| RANG.POURCENTAGE(matrice;x;précision) | Renvoie le rang d'une valeur d'une série de donnéessous forme de pourcentage. Cette fonction vous permet d'évaluer la position relative d'une valeur dans une série dedonnées. Par exemple, vous pouvez utiliser la fonction RANG.POURCENTAGE pour évaluer la position d'un résultat à un test d'aptitude parmi tous les résultats à ce test. |
| SOMME.CARRES.ECARTS(nombre1;nombre2;...) | Renvoie la somme des carrés des déviations des observations à partir de leur moyenne d'échantillonnage. |
Les fonctions Statistiques sous « EXCEL »
| STDEVA valeur1;valeur2;...) | Calcule l'écart type sur la base d'un échantillonL'écart. type mesure la dispersion des valeurs par apport à la moyenne (valeur moyenne). Outre des nombres, le calcul peut comprendre du texte ou des valeurs logiques telles que VRAI et FAUX.  |
| STDEVPA (valeur1;valeur2;...) | Calcule l'écart type d'une population en prenant encompte toute la population et en utilisant les arguments spécifiés, y compris le texte et les valeurs logiques. L'écarttype mesure la dispersion des valeurs par rapport à la moyenne(valeur moyenne). |
| TENDANCE (y_connus;x_connus;x_nouveaux;constante) | Calcule les valeurs par rapport à une tendance linéaire. Ajuste une droite (calculée selon la méthodedes moindres carrés) aux valeurs des matrices définies par les rgumentsa y_connus et x_connus. Renvoie, le long de cette droite, les valeurs y correspondant aux valeurs x de la matrice définie par l'argument x_nouveaux spécifié. |
| TEST.F(matrice1;matrice2) | Renvoie le résultat d'un test F. Un test F renvoiela probabilité unilatérale que les variances des arguments matrice1 et matrice2 ne présentent pas des différences significatives. Utilisez cette fonction pour comparer les variances de deux échantillons. Par exemple, à partir des résultats d'examen dans des écoles publiques et privées, vouspouvez tester ces écoles pour voir si elles ont des écarts de résultad'examen différents. |
| TEST.KHIDEUX(plage_réelle;plage_attendue) | Renvoie le test d'indépendance. TEST.KHIDEUX renvoi la valeur de la distribution khi-deux (c2) pour la statistique et les degrés de liberté appropriés. Utilisez lesests c2 pour déterminer si les résultats prévus sont vérifiés par une expérimentation. |
| TEST.STUDENT(matrice1;matrice2;uni/bilatéral;type) | Renvoie la probabilité associée à un test T de Student. Utilisez la fonction TEST.STUDENT pour déterminer dans quelle mesure deux échantillons sont susceptibles de provenir de deux populations sous-jacentes ayant la même moyenne. |
| TEST.Z(matrice, µ 0, sigma) | Renvoie la valeur-probabilité unilatérale d'un testz. Pour une moyenne de population supposée donnée,µ0, TEST.Z renvoie la probabilité que la moyenne d'échantillonnage soit supérieure à la moyenne des observations dans l'ensemble de données (matrice), à savoir la moyenne d'échantillonnage observée. |
| VAR(nombre1;nombre2;...) | Calcule la variance sur la base d'un échantillon. |
| VAR.P(nombre1;nombre2;...) | Calcule la variance sur la base de l'ensemble de la population. |
| VARA(valeur1;valeur2;...) | Calcule la variance sur la base d'un échantillon. Outre des nombres, le calcul peut comprendre du texte ou des valeurs logiques telles que VRAI et FAUX. |
| VARPA(valeur1;valeur2;...) | Calcule la variance sur la base de l'ensemble de la population. Outre des nombres, le calcul peut comprendre du texte ou des valeurs logiques telles que VRAI et FAUX. |
4. Descriptions Mathématique – Domaines d’applications
AVERAGEA
(valeur1;valeur2;...)
BETA.INVERSE
(probabilité;alpha;bêta;A;B)
CENTILE
(matrice;k)
CENTREE.REDUITE
(x;moyenne;écart_type)
COEFFICIENT.ASYMETRIE
(nombre1;nombre2;...)
COEFFICIENT.CORRELATION
(matrice1;matrice2)
COEFFICIENT.DETERMINATION
(y_connus;x_connus)
COVARIANCE
(matrice1;matrice2)
CRITERE.LOI.BINOMIALE
(essais;probabilité_s;alpha)
CROISSANCE
(y_connus;x_connus;x_nouveaux;constante)
DROITEREG
(y_connus;x_connus;constante;statistiques)
ECART.MOYEN
(nombre1;nombre2;...)
ECARTYPE
(nombre1;nombre2;...)
ECARTYPEP
(nombre1;nombre2;...)
ERREUR.TYPE.XY
(y_connus;x_connus)
FISHER
(x)
FISHER.INVERSE
(y)
FREQUENCE
(tableau_données;matrice_intervalles)
GRANDE.VALEUR
(matrice;k)
INTERVALLE.CONFIANCE
(alpha;standard_dev;taille)
…
le contrôle de qualité statistique, la description de certains phénomènes liés à la désintégration radioactive
LOI.STUDENT
(x;degrés_liberté;uni/bilatéral)
LOI.STUDENT.INVERSE
(probabilité;degrés_liberté)
LOI.WEIBULL
(x;alpha;bêta;cumulée)
MAX
(nombre1;nombre2;...)
MAXA
(valeur1;valeur2;...)
MEDIANE
(nombre1;nombre2;...)
MIN
(nombre1;nombre2;...)
…
ORDONNEE.ORIGINE
(y_connus;x_connus)
PEARSON
(matrice1;matrice2)
PENTE
(y_connus,x_connus)
PERMUTATION (nombre;nombre_choisi)
PETITE.VALEUR (matrice;k)
PREVISION (x;y_connus;x_connus)
PROBABILITE
(plage_x;plage_probabilité;limite_inf; limite_sup)
QUARTILE
(matrice;quart)
RANG
(nombre;référence;ordre)
RANG.POURCENTAGE
(matrice;x;précision)
SOMME.CARRES.ECARTS
(nombre1;nombre2;...)
STDEVA
(valeur1;valeur2;...)
STDEVPA
(valeur1;valeur2;...)
TENDANCE
(y_connus;x_connus;x_nouveaux;constante)
TEST.F
(matrice1;matrice2)
…
TEST.STUDENT
(matrice1;matrice2;uni/bilatéral;type)
TEST.Z
(matrice, µ 0, sigma)
VAR
(nombre1;nombre2;...)
VAR.P
(nombre1;nombre2;...)
VARA
(valeur1;valeur2;...)
VARPA
(valeur1;valeur2;...)
5. Utilisations
Les moyennes
La moyenne d’une série de valeurs peut-être calculée de différentes manières selon la situation rencontrée (Selon la manière dont le 'total' des individus est calculé).
La Moyenne arithmétique .
C’est la plus classique et la plus ancienne méthode employée pour caractériser un ensemble de donnéeset indiquer une tendance centrale.
La Moyenne Géométrique.
Cette moyenne est souvent oubliée mais néanmoins très connue dans le domaine de l'économétrie et de lafinance d'entreprise.
La Moyenne Harmonique
C'est l'inverse de la moyenne arithmétique de l'inverse des termes. Dans certains cas, elle donne la véritable notion de « moyenne ». Elle est utilisée pour le calcul des circuits électriques à deux ou plusieurs résistances reliées en parallèle.
Le calcul de la vitesse moyenne est donné par la moyenne harmonique pour un même temps de trajet.
La Moyenne Quadratique
C'est une des moyennes les plus connues en statistiques car l'écart-type est une moyenne quadratique.(Moyenne à 2 dimensions)
La Médiane ou( moyenne milieu)
C’est la mesure se situant au centre d'un ensemble d'observations.
Ces observations doivent être rangées par ordre croissant ou décroissant. Nous devons trouver 50% des observations de part et d'autre de la médiane.
Il est souvent intéressant de lier conjointement lecalcul d'une moyenne arithmétique avec cet indicateur qu'est la médiane. Car si les observations listées contiennent de nombreuses données extrêmes,la médiane permet un éclairage de la mesure centrale.
Le Mode
Le mode d'un ensemble d'observations est la valeurla plus fréquemment rencontrée. C’est un complémentàla moyenne et à la médiane.
La Fonction Gaussienne
Une fonction gaussienne est une fonction en exponentielle de l'opposé du carré de l'abscisse (une fonction enexp( − x2)). Elle a une forme caractéristique de courbe en cloche.
Nombre de phénomènes physiques suivent une distribution de type gaussien, expliqué par le théorème dela limite centrale. L'intérêt des fonctions gaussiennes en physique estégalement dû à certaines de leurs propriétés mathématiques remarquables.
Avec :
µ: La moyenne de l’échantillon des valeurs
µ =
σ : l’écart type de l’échantillon des valeurs
, où : .
La loi Normale (loi de gauss sous Excel)
(L'exemple le plus connu de la fonction Gaussienne est la densité de probabilité de la loi normale)
En probalité, X suit uneloi normale d'espéranceµ et d'écart typeσ strictement positif si cette variable aléatoire réelle X admet pour densité de probabilité la fonctionp(x) définie, pour tout nombre réelx, par :
La loi normale est une des principales distributions de probabilité.
La loi de poisson
Le domaine d'application de la loi de Poisson a étélongtemps limité à celui des événements rares comme les suicides d'enfants, les arrivées de bateaux dans un port ou les accidents dus aux coups de pied de cheval dans les armées.
Actuellement, on l'utilise beaucoup dans les télécommunications, le contrôle de qualité statistique, la description de certains phénomènes liés à la désintégration radioactive, la biologie (mutations), la météorologie, la finance pour modéliser la probabilité de défaut d'un crédit…
Si le nombre moyen d'occurrences dans cet intervalle est λ, alors la probabilité qu'il existe exactementk occurrences est :
- k étant un entier naturel,k = 0, 1, 2, …
- λ est un nombre réel strictement positif.
Méthode des moindres carrés
La méthode des moindres carréspermet de comparer des données expérimentales, généralement entachées d’erreurs de mesure, à un modèle mathématique censé décrire ces données.
La méthode des moindres carrés permet alors de minimiser l’impact des erreurs expérimentales en « ajoutant de l’information » dans le processus de mesure.
Rappel : Régression Linéaire
La régression linéaireconsiste à déterminer une estimation des valeurs a et b et à quantifier la validité de cette relation grâc e au coefficient decorrélation linéaire.
Avec :
· Moyenne empirique des xi :
· Moyenne empirique des yi :
· Variance empirique des xi :
· Ecart-type empirique des xi :
· Variance empirique des yi :
- Ecart-type empirique des yi : .
- Covariance empirique des xi, yi : .
Coefficient de corrélation
Le coefficient de corrélation simple est un indicede mesure de l'intensité d'un lien qui peut existerentre deux variables. Le coefficient de corrélation peut prendre une valeur comprise entre-1 et +1. S'il est égal à 0, cela signifie qu'il n'existe aucun lien entre ces 2 variables. Il est très généralement utilisé dans le cadre de l'analyse devariables quantitatives.
Dans le cadre d'un échantillon de taille n :
r est donc un estimateur dit le coefficient de corrélation d'échantillonnage.