VARIANCE - Feuilles Excel et Google

Ce didacticiel montre comment utiliser le Fonction VARIANCE Excel dans Excel pour estimer la variance sur la base d'un échantillon donné.

Présentation de la fonction VARIANCE

La fonction VARIANCE Calcule la variance estimée sur la base d'un échantillon donné.

Pour utiliser la fonction de feuille de calcul Excel VARIANCE, sélectionnez une cellule et tapez :

(Remarquez comment les entrées de formule apparaissent)

Fonction VARIANCE Syntaxe et entrées :

1	=VAR(nombre1,[nombre2],… )

Nombres- Valeurs pour obtenir la variance

Comment calculer la variance dans Excel

La variance vous indique l'écart entre les valeurs d'un ensemble de données et la moyenne. Mathématiquement parlant, la variance est la moyenne de la différence au carré de chaque score par rapport à la moyenne (mais nous y reviendrons sous peu).

Excel vous donne un certain nombre de fonctions pour calculer la variance - VAR.S, VAR.P, VARA, VARPA et deux fonctions plus anciennes, VAR et VARP.

Avant de nous plonger dans ces fonctions et d'apprendre à les utiliser, parlons de la variance et de la façon dont elle est calculée.

Qu'est-ce que l'écart ?

Lors de l'analyse des données, une première étape courante consiste à calculer la moyenne. C'est bien sûr une statistique utile à calculer, mais elle ne vous donne pas une image complète de ce qui se passe avec vos données.

Prenez l'ensemble de données suivant, qui pourrait être un groupe de résultats de test notés sur 100 :

1	48,49,50,51,52

La moyenne de cette plage est de 50 (somme les nombres et divise par n, où n est le nombre de valeurs).

Ensuite, prenez l'ensemble de résultats de test suivant :

1	10,25,50,75,90

La moyenne de cette gamme est aussi 50 - mais évidemment, nous avons ici deux plages de données très différentes.

En soi, la moyenne ne peut rien vous dire sur l'étalement des scores. Il ne vous dit pas si les valeurs sont toutes regroupées comme dans les premiers exemples, ou écartées comme dans le second. La variance peut vous aider à apprendre cela.

La variance est également utilisée comme point de base pour une gamme de procédures statistiques plus complexes.

Comment l'écart est calculé

Prenons un exemple de base et calculons la variance à la main. De cette façon, vous saurez ce qui se passe dans les coulisses lorsque vous commencez réellement à mettre en œuvre les fonctions de variance d'Excel.

Supposons que nous ayons un ensemble de données représentant trois cartes à jouer, un 4, un 6 et un 8.

Pour calculer l'écart, vous suivez ce processus :

1) Calculer la moyenne

Tout d'abord, nous calculons la moyenne. Nous savons que notre plage de données est de 4, 6, 8, donc la moyenne sera :

1	(6 + 4 + 8) / 3 = 6

J'ai confirmé cela ci-dessous avec la fonction Excel AVERAGE<> :

1	=MOYENNE(C4:C6)

2) Soustraire la moyenne de chaque valeur de l'ensemble de données

Ensuite, nous soustrayons la moyenne de chacune de nos valeurs.

Je l'ai fait avec la formule suivante :

1

=C4-$H$4

La moyenne est stockée dans H4, donc je la soustrait juste de chaque valeur de la table. Les signes dollar ici "verrouillent" simplement cette référence de cellule à H4, de sorte que lorsque je la copie dans la colonne, elle reste la même.

Les résultats:

Nous avons:

123	4 - 6 = -26 - 6 = 08 - 6 = 2

Nous devons obtenir la moyenne de ces différences à partir de la moyenne, mais la moyenne de ces trois valeurs est nulle ! Nous devons donc souligner les différences, ce que nous faisons en les mettant au carré.

3) Égaliser les différences

Ajoutons une nouvelle colonne et mettons au carré les nombres de la colonne D :

1

=D4*D4

D'accord, c'est mieux. Maintenant que la moyenne des différences n'est pas nulle, nous pouvons calculer la variance.

4) Calculer la moyenne des différences au carré

Ici, nous rencontrons une fourche dans la route. Il existe deux façons de calculer la variance, et celle que vous utilisez dépend du type de données dont vous disposez.

• Si vous utilisez données démographiques, vous prenez simplement la moyenne comme d'habitude (additionnez les valeurs et divisez par n)
• Si vous utilisez exemples de données, vous additionnez les valeurs et divisez par n-1

Les données démographiques signifient que vous avez la totalité des données dont vous avez besoin, par exemple, si vous voulez l'âge moyen des enseignants dans une école particulière, et que vous avez les données d'âge pour chaque enseignant de cette école, vous avez des données démographiques.

Les données d'échantillon signifient que vous n'avez pas toutes vos données, juste un échantillon tiré d'une population plus large. Donc, si vous voulez connaître l'âge moyen des enseignants dans l'ensemble du pays et que vous n'avez des données que sur les enseignants d'une école, vous disposez d'exemples de données.

Dans notre exemple, nous avons des données de population. Nous ne nous intéressons qu'à nos trois cartes - c'est la population, et nous n'en avons pas pris un échantillon. Nous pouvons donc simplement prendre la moyenne des différences au carré de la manière normale :

1	=MOYENNE(E4:E8)

La variance de notre population est donc de 2,666.

Si ce était des exemples de données (nous avions peut-être tiré ces trois cartes d'un ensemble plus vaste), nous calculerions la moyenne comme suit :

1	Variance d'échantillon = (4 + 0 + 4) / (3 - 1)

Ou:

1	Variance de l'échantillon = 8 / 2 = 4

Pourquoi diviser par n-1 avec des exemples de données, au lieu de juste n ?

La réponse courte à cette question est « Parce que cela donne la bonne réponse ». Mais j'imagine que vous voudrez un peu plus que ça ! Il s'agit d'un sujet complexe, je vais donc en donner un bref aperçu ici.

Pensez-y comme ceci : si vous prenez un échantillon de données d'une population, ces valeurs auront tendance à être plus proches de la moyenne de la échantillon qu'ils ne le sont à la moyenne des population.

Cela signifie que si vous divisez simplement par n, vous sous-estimerez un peu la variance de la population. La division par n-1 corrige un peu cela.

Avec notre jeu de trois cartes, nous sommes bien placés pour tester cette théorie. Comme il n'y a que trois cartes, il y a un petit nombre d'échantillons que nous pouvons éventuellement prendre.

Prenons des échantillons de deux cartes. Nous allons choisir une carte, la remettre, la mélanger, puis en choisir une autre. Cela signifie qu'il y a neuf combinaisons de deux cartes que nous pouvons choisir.

Avec seulement neuf échantillons possibles, nous pouvons calculer chaque variance d'échantillon possible en utilisant les deux méthodes (diviser par n et diviser par n-1), en faire la moyenne et voir laquelle nous donne la bonne réponse.

Dans le tableau ci-dessous, j'ai tout mis en place. Chaque ligne du tableau est un échantillon différent, et les colonnes B et C montrent les deux cartes qui ont été choisies dans chaque échantillon. Ensuite, j'ai ajouté deux autres colonnes : une où j'ai calculé la variance de cet échantillon de deux cartes en divisant par n, et une autre où j'ai divisé par n - 1.

Regarde:

A droite du tableau, j'ai montré les moyennes des colonnes D et E.

La moyenne de la colonne D, en divisant par n, nous donne une variance de 1,333.

La moyenne de la colonne E, en divisant par n-1, nous donne une variance de 2,666.

Nous savons déjà d'après notre exemple précédent que la variance de la population est de 2,666. Donc, diviser par n-1 lors de l'utilisation de données d'échantillon nous donne des estimations plus précises.

Les fonctions Excel pour calculer la variance

Maintenant que vous avez vu un exemple de calcul de la variance, passons aux fonctions Excel.

Vous avez plusieurs options ici :

• P renvoie la variance pour les données de population (en utilisant la méthode de division par n)
• S renvoie la variance pour les données d'échantillon (divisé par n-1)
• VAR est une ancienne fonction qui fonctionne exactement de la même manière que VAR.S
• VARA est le même que VAR.S, sauf qu'il inclut des cellules de texte et des valeurs booléennes
• VARPA est le même que VAR.P, sauf qu'il inclut des cellules de texte et des valeurs booléennes

Passons en revue ces un par un.

La fonction Excel VAR.P

VAR.P calcule la variance pour les données de population (en utilisant la méthode de division par n). Utilisez-le comme ceci :

1	=VAR.P(C4:C6)

Vous ne définissez qu'un seul argument dans VAR.P : la plage de données pour laquelle vous souhaitez calculer la variance. Dans notre cas ici, ce sont les valeurs de la carte en C4:C6.

Comme vous le voyez ci-dessus, VAR.P renvoie 2,666 pour notre ensemble de trois cartes. C'est la même valeur que nous avons calculée à la main plus tôt.

Notez que VAR.P ignore complètement les cellules contenant du texte ou des valeurs booléennes (TRUE/FALSE). Si vous devez les inclure, utilisez plutôt VARPA.

La fonction Excel VAR.S

VAR.S calcule la variance pour les données d'échantillon (en divisant par n-1). Vous l'utilisez comme ceci :

1	=VAR.S(C4:C6)

Encore une fois, il n'y a qu'un seul argument - votre plage de données.

Dans ce cas, VAR.S renvoie 4. Nous avons obtenu le même chiffre à l'étape 4 lorsque nous avons effectué le calcul manuel ci-dessus.

VAR.S ignore complètement les cellules contenant du texte ou des valeurs booléennes (TRUE/FALSE). Si vous devez les inclure, utilisez plutôt VARA.

La fonction Excel VAR

VAR est tout à fait équivalent à VAR.S : il calcule les variances pour les données d'échantillon (en utilisant la méthode n-1). Voici comment l'utiliser :

1	=VAR(C4:C6)

VAR est une « fonction de compatibilité ». Cela signifie que Microsoft est en train de supprimer cette fonction d'Excel. Pour le moment, il est toujours disponible, mais vous devez utiliser VAR.S à la place, afin que vos feuilles de calcul restent compatibles avec les futures versions d'Excel.

La fonction VARA d'Excel

VARA renvoie également la variance des données d'échantillon, mais il présente des différences clés par rapport à VAR et VAR.S. À savoir, il inclut des valeurs booléennes et textuelles dans son calcul :

• Les valeurs VRAIES sont comptées comme 1
• Les valeurs FAUX sont comptées comme 0
• Les chaînes de texte sont comptées comme 0

Voici comment vous l'utilisez :

1	=VARA(C4:C11)

Nous avons ajouté cinq lignes supplémentaires au tableau : J, Q, K, TRUE et FALSE. La colonne D montre comment VARA interprète ces valeurs.

Parce que nous avons maintenant un nouveau lot de valeurs basses dans notre table, la variance a augmenté à 10,268.

La fonction VARPA d'Excel

VARPA calcule la variance pour les données de population. Il est similaire à VAR.P, sauf qu'il inclut également des valeurs booléennes et des chaînes de texte dans le calcul :

• Les valeurs VRAIES sont comptées comme 1
• Les valeurs FAUX sont comptées comme 0
• Les chaînes de texte sont comptées comme 0

Vous l'utilisez comme ceci :

1	=VARPA(C4:C12)

Nous avons ajouté cinq lignes supplémentaires au tableau : J, Q, K, TRUE et FALSE. La colonne D montre comment VARPA interprète ces valeurs.

En raison de l'ajout de ce groupe de valeurs inférieures aux données, la variance est passée à 8,984.

Fonction VARIANCE dans Google Sheets

La fonction CORREL fonctionne exactement de la même manière dans Google Sheets que dans Excel :