En analyse statistique, le variance parmi les membres d’un ensemble de données indique la distance qui sépare les points de données d’une ligne de tendance, également appelée ligne de régression. Plus la variance est élevée, plus les points de données sont dispersés. L'étude de l'analyse de la variance montre quelles parties de la variance peuvent être expliquées par les caractéristiques des données et lesquelles peuvent être attribuées à des facteurs aléatoires. La partie de la variance qui ne peut pas être expliquée s'appelle la variance résiduelle..
Utilisation de feuilles de calcul Excel pour calculer la variance résiduelle
La formule de calcul de la variance résiduelle implique de nombreux calculs complexes. Pour les petits ensembles de données, le processus de calcul manuel de la variance résiduelle peut s'avérer fastidieux. Pour les grands ensembles de données, la tâche peut être épuisant. En utilisant une feuille de calcul Excel, il vous suffit de saisir les points de données et de sélectionner la formule appropriée. Le programme gère les calculs complexes et fournit un résultat rapidement.
Points de données
Ouvrez une nouvelle feuille de calcul Excel et entrez les points de données dans deux colonnes. Les lignes de régression exigent que chaque point de données comporte deux éléments. Les statisticiens appellent généralement ces éléments "X" et "Y". Par exemple, Generic Insurance Co. souhaite connaître la variance résiduelle de la taille et du poids de ses employés. La variable X représente la hauteur et la variable Y le poids. Entrez les hauteurs dans la colonne A et les poids dans la colonne B.
Trouver la moyenne
le signifier représente la moyenne pour chaque élément de l'ensemble de données. Dans cet exemple, Generic Insurance souhaite rechercher la moyenne, l'écart type et la covariance des hauteurs et des poids de 10 employés. La moyenne des hauteurs indiquées dans la colonne A peut être trouvée en entrant la fonction "= AVERAGE (A1: A10)" dans la cellule F1. La moyenne des poids énumérés dans la colonne B peut être trouvée en entrant la fonction "= MOYENNE (B1: B10)" dans la cellule F3.
Trouver l'écart type et la covariance
le déviation standard mesure la distance qui sépare les points de données de la moyenne. le covariance mesure dans quelle mesure les deux éléments du point de données changent ensemble. La déviation standard des hauteurs est trouvée en entrant la fonction "= STDEV (A1: A10)" dans la cellule F2. La déviation standard des poids est trouvée en entrant la fonction "= STDEV (B1: B10)" dans la cellule F4. La covariance entre les hauteurs et les poids est trouvée en entrant la fonction "= COVAR (A1: A10; B1: B10)" dans la cellule F5.
Trouver la ligne de régression
le ligne de régression représente une fonction linéaire qui suit la tendance des points de données. La formule de la droite de régression ressemble à ceci: Y = aX + b.
L'utilisateur peut trouver les valeurs pour "a" et "b" en utilisant les calculs pour les moyennes, les écarts types et la covariance. La valeur de "b" représente le point où la ligne de régression intercepte l'axe des ordonnées. La valeur peut être trouvée en prenant la covariance et en la divisant par le carré de l'écart type des valeurs X. La formule Excel va dans la cellule F6 et ressemble à ceci: = F5 / F2 ^ 2.
La valeur de "a" représente la pente de la droite de régression. La formule Excel va dans la cellule F7 et ressemble à ceci: = F3-F6 * F1.
Pour voir la formule de la ligne de régression, entrez cette concaténation de chaîne dans la cellule F8:
= CONCATENER ("Y ="; ROUND (F6; 2); "X"; IF (SIGN (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Calculer les valeurs Y
L'étape suivante consiste à calculer les valeurs Y sur la ligne de régression pour les valeurs X données dans l'ensemble de données. La formule pour trouver les valeurs Y va dans la colonne C et ressemble à ceci:
= 6 F $ * A (i) + 7 F $
Où A (i) est la valeur de la colonne A de la ligne (i). Les formules ressemblent à ceci dans le tableur:
= 6 F $ * A1 + 7 F $
= 6 F $ * A2 + 7 F $
= 6 F $ * A3 + 7 F $, et ainsi de suite
Les entrées de la colonne D montrent les différences entre les valeurs attendues et réelles de Y. Les formules ressemblent à ceci:
= B (i) -C (i), Où B (i) et C (i) sont les valeurs de la ligne (i) des colonnes B et C, respectivement.
Trouver la variance résiduelle
le formule pour la variance résiduelle va dans la cellule F9 et ressemble à ceci:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Où SUMSQ (D1: D10) est la somme des carrés des différences entre les valeurs Y réelles et attendues, et (COUNT (D1: D10) -2) est le nombre de points de données, moins 2 pour les degrés de liberté dans le Les données.
