Ce qui montre la variance de la valeur observée. Variance et écart type dans MS EXCEL

Parmi les nombreux indicateurs utilisés en statistique, il faut souligner le calcul de la variance. Il convient de noter que l'exécution manuelle de ce calcul est une tâche assez fastidieuse. Heureusement, il existe des fonctions dans Excel qui vous permettent d'automatiser la procédure de calcul. Découvrons l'algorithme pour travailler avec ces outils.

La variance est une mesure de la variation, c'est-à-dire le carré moyen des écarts par rapport à espérance mathématique. Ainsi, il exprime la dispersion des nombres autour de la moyenne. Le calcul de la variance peut être effectué comme population, ainsi que sélectivement.

Méthode 1 : calcul sur la population générale

Pour le calcul cet indicateur dans Excel sur la population générale, la fonction est appliquée DISP.G. La syntaxe de cette expression est la suivante :

DISP.G(Numéro1;Numéro2;…)

Au total, de 1 à 255 arguments peuvent être appliqués. Les arguments peuvent être à la fois des valeurs numériques et des références aux cellules dans lesquelles ils sont contenus.

Voyons comment calculer cette valeur pour une plage de données numériques.

Méthode 2 : exemple de calcul

Contrairement au calcul de la valeur pour la population générale, dans le calcul pour l'échantillon, le dénominateur n'est pas indiqué total nombres, mais un de moins. Ceci est fait afin de corriger l'erreur. Excel prend en compte cette nuance dans une fonction spéciale conçue pour ce type de calcul - DISP.V. Sa syntaxe est représentée par la formule suivante :

VAR.B(Numéro1;Numéro2;…)

Le nombre d'arguments, comme dans la fonction précédente, peut également aller de 1 à 255.

Comme vous pouvez le voir, le programme Excel est capable de grandement faciliter le calcul de la variance. Cette statistique peut être calculée par l'application à la fois pour la population et pour l'échantillon. Dans ce cas, toutes les actions de l'utilisateur sont en fait réduites à spécifier uniquement la plage de numéros traités, et les principaux Travail Excel le fait lui-même. Bien sûr, cela fera gagner un temps considérable aux utilisateurs.

Cette page décrit exemple standard trouver la variance, vous pouvez également consulter d'autres tâches pour la trouver

Exemple 1. Détermination de groupe, moyenne de groupe, intergroupe et écart total

Exemple 2. Recherche de la variance et du coefficient de variation dans un tableau de regroupement

Exemple 3. Trouver la variance de série discrète

Exemple 4. Nous avons les données suivantes pour un groupe de 20 étudiants service de correspondance. Il est nécessaire de construire une série d'intervalles de la distribution des caractéristiques, de calculer la valeur moyenne de la caractéristique et d'étudier sa variance

Construisons un groupement d'intervalles. Déterminons la plage de l'intervalle par la formule :

où X max– valeur maximum signe de regroupement;
X min est la valeur minimale de la caractéristique de regroupement ;
n est le nombre d'intervalles :

Nous acceptons n=5. Le pas est: h \u003d (192 - 159) / 5 \u003d 6,6

Faisons un regroupement par intervalle

Pour d'autres calculs, nous allons construire une table auxiliaire :

X "i - le milieu de l'intervalle. (par exemple, le milieu de l'intervalle 159 - 165,6 \u003d 162,3)

La croissance moyenne des étudiants est déterminée par la formule de la moyenne pondérée arithmétique:

Nous déterminons la dispersion par la formule :

La formule peut être convertie comme ceci :

De cette formule il résulte que l'écart est la différence entre la moyenne des carrés des options et le carré et la moyenne.

dispersion dans série de variantes avec des intervalles égaux selon la méthode des moments peut être calculé de la manière suivante en utilisant la deuxième propriété de la dispersion (en divisant toutes les options par la valeur de l'intervalle). Définition de la variance, calculé par la méthode des moments, selon la formule suivante prend moins de temps :

où i est la valeur de l'intervalle ;
A - zéro conditionnel, ce qui est pratique pour utiliser le milieu de l'intervalle avec la fréquence la plus élevée;
m1 est le carré du moment du premier ordre ;
m2 - moment du second ordre

Écart de fonctionnalité (si dans la population statistique l'attribut change de telle manière qu'il n'y a que deux options mutuellement exclusives, alors cette variabilité est appelée alternative) peut être calculée par la formule :

Remplacer dans cette formule dispersion q \u003d 1- p, on obtient :

Types de dispersion

Écart total mesure la variation d'un trait sur l'ensemble de la population dans son ensemble sous l'influence de tous les facteurs qui provoquent cette variation. Il est égal au carré moyen des écarts des valeurs individuelles de la caractéristique x par rapport à la valeur moyenne totale x et peut être défini comme une variance simple ou une variance pondérée.

Écart intragroupe caractérise la variation aléatoire, c'est-à-dire une partie de la variation, qui est due à l'influence de facteurs non pris en compte et ne dépend pas du trait-facteur sous-jacent au regroupement. Une telle variance est égale au carré moyen des écarts des valeurs individuelles d'une caractéristique au sein du groupe X par rapport à la moyenne arithmétique du groupe et peut être calculée comme une simple variance ou comme une variance pondérée.

De cette façon, mesures de variance intra-groupe variation d'un trait au sein d'un groupe et est déterminé par la formule :

où xi - moyenne du groupe ;
ni est le nombre d'unités dans le groupe.

Par exemple, les variances intra-groupe, qui doivent être déterminées dans le problème de l'étude de l'influence des qualifications des travailleurs sur le niveau de productivité du travail dans l'atelier, montrent des variations de production dans chaque groupe, causées par tous les facteurs possibles ( état techniqueéquipement, disponibilité des outils et des matériaux, âge des travailleurs, intensité de travail, etc.), à l'exception des différences de catégorie de qualification(au sein d'un groupe, tous les travailleurs ont les mêmes qualifications).

Parallèlement à l'étude de la variation d'un trait dans l'ensemble de la population dans son ensemble, il est souvent nécessaire de retracer les changements quantitatifs du trait dans les groupes dans lesquels la population est divisée, ainsi qu'entre les groupes. Cette étude de variation est réalisée par le calcul et l'analyse diverses sortes dispersion.
Distinguer dispersion totale, intergroupe et intragroupe.
Écart total σ 2 mesure la variation d'un trait sur l'ensemble de la population sous l'influence de tous les facteurs qui ont provoqué cette variation, .

La variance intergroupe (δ) caractérise la variation systématique, c'est-à-dire différences dans l'ampleur du trait à l'étude, résultant de l'influence du trait-facteur sous-jacent au groupement. Il est calculé par la formule :
.

Variance intra-groupe (σ) reflète une variation aléatoire, c'est-à-dire partie de la variation qui se produit sous l'influence de facteurs non pris en compte et qui ne dépend pas du trait-facteur sous-jacent au groupement. Il est calculé par la formule :
.

Moyenne des variances intra-groupe: .

Il existe une loi liant 3 types de dispersion. La variance totale est égale à la somme de la moyenne des variances intragroupe et intergroupe : .
Ce rapport est appelé règle d'ajout d'écart.

Dans l'analyse, une mesure est largement utilisée, qui est la proportion de la variance entre les groupes dans la variance totale. Il porte le nom coefficient de détermination empirique (η 2): .
La racine carrée du coefficient de détermination empirique est appelée rapport de corrélation empirique (η):
.
Il caractérise l'influence de l'attribut sous-jacent au regroupement sur la variation de l'attribut résultant. Le rapport de corrélation empirique varie de 0 à 1.
Montrons-le utilisation pratique dans l'exemple suivant (tableau 1).

Exemple 1. Tableau 1 - Productivité du travail de deux groupes de travailleurs de l'un des ateliers de NPO "Cyclone"

Calculez les moyennes et les écarts du total et du groupe :

Les données initiales pour le calcul de la moyenne de la dispersion intragroupe et intergroupe sont présentées dans le tableau. 2.
Tableau 2
Calcul et δ 2 pour deux groupes de travailleurs.

Groupes de travailleurs	Nombre de travailleurs, pers.	Moyenne, dét./shift.	Dispersion
Formation technique réussie	5	95	42,0
Pas de formation technique	5	81	231,2
Tous les travailleurs	10	88	185,6

Calculons les scores. Moyenne des variances intra-groupe :
.
Écart intergroupe

Écart total :
Ainsi, le rapport de corrélation empirique : .

Parallèlement à la variation des traits quantitatifs, une variation des traits qualitatifs peut également être observée. Cette étude de variation est réalisée en calculant les genres suivantsécarts :

La variance intra-groupe de la part est déterminée par la formule

où n je– le nombre d'unités dans des groupes séparés.
La proportion du trait étudié dans l'ensemble de la population, qui est déterminée par la formule :
Les trois types de dispersion sont liés les uns aux autres comme suit :
.

Ce rapport de variances est appelé théorème d'addition de variance de part de caractéristique.

La dispersion est une mesure de la dispersion qui décrit l'écart relatif entre les valeurs des données et la moyenne. C'est la mesure de dispersion la plus utilisée dans les statistiques, calculée en additionnant, au carré, l'écart de chaque valeur de données par rapport à taille moyenne. La formule de calcul de la variance est indiquée ci-dessous :

s 2 - variance de l'échantillon ;

x cf est la valeur moyenne de l'échantillon ;

n — taille de l'échantillon (nombre de valeurs de données),

(x i – x cf) est l'écart par rapport à la valeur moyenne pour chaque valeur de l'ensemble de données.

Pour mieux comprendre la formule, regardons un exemple. Je n'aime pas vraiment cuisiner, donc je le fais rarement. Cependant, pour ne pas mourir de faim, je dois de temps en temps aller aux fourneaux pour mettre en œuvre le plan visant à saturer mon corps de protéines, de graisses et de glucides. L'ensemble de données ci-dessous montre combien de fois Renat cuisine des aliments chaque mois :

La première étape du calcul de la variance consiste à déterminer la moyenne de l'échantillon, qui dans notre exemple est de 7,8 fois par mois. Les calculs restants peuvent être facilités à l'aide du tableau suivant.

La phase finale du calcul de la variance ressemble à ceci :

Pour ceux qui aiment faire tous les calculs en une seule fois, l'équation ressemblera à ceci :

Utilisation de la méthode du comptage brut (exemple de cuisson)

Il y a plus méthode efficace calcul de la variance, méthode dite du "comptage brut". Bien qu'à première vue l'équation puisse sembler assez lourde, en fait elle n'est pas si effrayante. Vous pouvez vérifier cela, puis décider quelle méthode vous préférez.

est la somme de chaque valeur de données après mise au carré,

est le carré de la somme de toutes les valeurs de données.

Ne perdez pas la tête maintenant. Mettons le tout sous forme de tableau, et vous verrez alors qu'il y a moins de calculs ici que dans l'exemple précédent.

Comme vous pouvez le voir, le résultat est le même que lors de l'utilisation de la méthode précédente. Avantages cette méthode deviennent évidents à mesure que la taille de l'échantillon (n) augmente.

Calcul de la variance dans Excel

Comme vous l'avez probablement déjà deviné, Excel a une formule qui vous permet de calculer la variance. De plus, à partir d'Excel 2010, vous pouvez trouver 4 variétés de la formule de dispersion :

1) VAR.V - Renvoie la variance de l'échantillon. Les valeurs booléennes et le texte sont ignorés.

2) VAR.G - Renvoie la variance de la population. Les valeurs booléennes et le texte sont ignorés.

3) VASP - Renvoie la variance de l'échantillon, en tenant compte des valeurs booléennes et textuelles.

4) VARP - Renvoie la variance de la population, en tenant compte des valeurs logiques et textuelles.

Voyons d'abord la différence entre un échantillon et une population. Le but des statistiques descriptives est de résumer ou d'afficher des données de manière à obtenir rapidement une vue d'ensemble, pour ainsi dire, une vue d'ensemble. L'inférence statistique vous permet de faire des inférences sur une population à partir d'un échantillon de données de cette population. La population représente tous les résultats ou mesures possibles qui nous intéressent. Un échantillon est un sous-ensemble d'une population.

Par exemple, nous nous intéressons à la totalité d'un groupe d'étudiants d'une des universités russes et nous devons déterminer le score moyen du groupe. Nous pouvons calculer la performance moyenne des élèves, puis le chiffre obtenu sera un paramètre, puisque toute la population sera impliquée dans nos calculs. Cependant, si nous voulons calculer le GPA de tous les étudiants de notre pays, ce groupe sera notre échantillon.

La différence dans la formule de calcul de la variance entre l'échantillon et la population se trouve au dénominateur. Où pour l'échantillon il sera égal à (n-1), et pour la population générale seulement n.

Passons maintenant aux fonctions de calcul de la variance avec les terminaisons MAIS, dans la description duquel il est dit que le calcul tient compte du texte et des valeurs logiques. À ce cas Lors du calcul de la variance d'un jeu de données spécifique où se produisent des valeurs non numériques, Excel interprète le texte et les faux booléens comme 0 et les vrais booléens comme 1.

Ainsi, si vous disposez d'un tableau de données, il ne sera pas difficile de calculer sa variance à l'aide de l'une des fonctions Excel répertoriées ci-dessus.

Selon l'enquête par sondage, les déposants ont été regroupés en fonction de la taille du dépôt à la Sberbank de la ville :

Définir:

1) plage de variation ;

2) montant moyen du dépôt ;

3) moyenne déviation linéaire;

4) dispersion ;

5) écart type ;

6) coefficient de variation des cotisations.

La solution:

Cette série de distribution contient des intervalles ouverts. Dans de telles séries, la valeur de l'intervalle du premier groupe est conventionnellement supposée égale à la valeur de l'intervalle du suivant, et la valeur de l'intervalle du dernier groupe est égale à la valeur de l'intervalle du précédent. une.

La valeur d'intervalle du deuxième groupe est 200, par conséquent, la valeur du premier groupe est également 200. La valeur d'intervalle de l'avant-dernier groupe est 200, ce qui signifie que le dernier intervalle aura également une valeur égale à 200.

1) Définir la plage de variation comme la différence entre le plus grand et le la plus petite valeur pancarte:

La plage de variation de la taille de la contribution est de 1000 roubles.

2) La taille moyenne de la contribution est déterminée par la formule de la moyenne pondérée arithmétique.

Définissons au préalable quantité discrète fonction dans chaque intervalle. Pour ce faire, en utilisant la simple formule de la moyenne arithmétique, nous trouvons les milieux des intervalles.

La valeur moyenne du premier intervalle sera égale à :

la seconde - 500, etc.

Mettons les résultats des calculs dans le tableau:

Montant du dépôt, frotter.	Nombre de contributeurs, f	Le milieu de l'intervalle, x	xf
200-400	32	300	9600
400-600	56	500	28000
600-800	120	700	84000
800-1000	104	900	93600
1000-1200	88	1100	96800
Total	400	-	312000

Le dépôt moyen dans la Sberbank de la ville sera de 780 roubles :

3) L'écart linéaire moyen est la moyenne arithmétique des écarts absolus des valeurs individuelles de l'attribut par rapport à la moyenne totale :

La procédure de calcul de l'écart linéaire moyen dans la série de distribution d'intervalle est la suivante :

1. La moyenne pondérée arithmétique est calculée, comme indiqué au paragraphe 2).

2. Les écarts absolus de la variante par rapport à la moyenne sont déterminés :

3. Les écarts obtenus sont multipliés par les fréquences :

4. La somme des écarts pondérés est trouvée sans tenir compte du signe :

5. La somme des écarts pondérés est divisée par la somme des fréquences :

Il est pratique d'utiliser le tableau des données calculées :

Montant du dépôt, frotter.	Nombre de contributeurs, f	Le milieu de l'intervalle, x
200-400	32	300	-480	480	15360
400-600	56	500	-280	280	15680
600-800	120	700	-80	80	9600
800-1000	104	900	120	120	12480
1000-1200	88	1100	320	320	28160
Total	400	-	-	-	81280

L'écart linéaire moyen de la taille du dépôt des clients de la Sberbank est de 203,2 roubles.

4) La dispersion est la moyenne arithmétique des écarts au carré de chaque valeur de caractéristique par rapport à la moyenne arithmétique.

Calcul de la dispersion en série d'intervalles la distribution se fait selon la formule :

La procédure de calcul de la variance dans ce cas est la suivante :

1. Déterminer la moyenne arithmétique pondérée, comme indiqué au paragraphe 2).

2. Trouvez les écarts par rapport à la moyenne :

3. Mise au carré de l'écart de chaque option par rapport à la moyenne :

4. Multipliez les écarts au carré par les poids (fréquences) :

5. Résumez les travaux reçus :

6. Le montant obtenu est divisé par la somme des pondérations (fréquences) :

Mettons les calculs dans un tableau :

Montant du dépôt, frotter.	Nombre de contributeurs, f	Le milieu de l'intervalle, x
200-400	32	300	-480	230400	7372800
400-600	56	500	-280	78400	4390400
600-800	120	700	-80	6400	768000
800-1000	104	900	120	14400	1497600
1000-1200	88	1100	320	102400	9011200
Total	400	-	-	-	23040000