В статистическия анализ,. T промяна сред членовете на набор от данни показва колко далеч един от друг са данните от линия на тенденцията, известна също като a линия на регресия, Колкото по-висока е дисперсията, толкова по-разпространени са данните. Изследването на дисперсионния анализ показва кои части от дисперсията могат да се обяснят с характеристиките на данните и които могат да бъдат приписани на случайни фактори. Частта от дисперсията, която не може да бъде обяснена, се нарича остатъчна дисперсия.
Използване на Excel таблици за изчисляване на остатъчната разлика
Формулата за изчисляване на остатъчната дисперсия включва множество сложни изчисления. За малки набори от данни процесът на изчисляване на остатъчната дисперсия на ръка може да бъде досаден. За големи масиви от данни задачата може да бъде изтощителна. Като използвате електронна таблица на Excel, трябва само да въведете точките за данни и да изберете правилната формула. Програмата се справя със сложните изчисления и доставя резултат бързо.
Точки за данни
Отворете нова електронна таблица на Excel и въведете точките за данни в две колони. Регресионните линии изискват всяка точка от данни да има два елемента. Статистиците обикновено етикетират тези елементи "X" и "Y". Например, Generic Insurance Co. иска да намери остатъчната разлика в ръста и теглото на своите служители. Променливата X представлява височината, а променливата Y представлява теглото. Въведете височините в колона А и теглата в колона Б.
Намиране на средното
Най- означава представлява средната стойност за всеки елемент в набора от данни. В този пример Generic Insurance иска да намери средното, стандартното отклонение и ковариацията на височините и теглата на 10 служители. Средната стойност на височините, посочени в колона А, може да бъде намерена чрез въвеждане на функцията "= AVERAGE (A1: A10)" в клетка F1. Средната стойност на теглата, изброени в колона В, може да бъде намерена чрез въвеждане на функцията "= AVERAGE (B1: B10)" в клетка F3.
Намиране на стандартно отклонение и ковариация
Най- стандартно отклонение измерва колко далеч един от друг се разпространяват точките от средната стойност. Най- ковариация измерва колко два елемента от точката за данни се променят заедно. Стандартното отклонение на височините се намира чрез въвеждане на функцията "= STDEV (A1: A10)" в клетка F2. Стандартното отклонение на теглата се намира чрез въвеждане на функцията "= STDEV (B1: B10)" в клетка F4. Ковариацията между височините и теглата се намира чрез въвеждане на функцията "= COVAR (A1: A10; B1: B10)" в клетка F5.
Намиране на регресионната линия
Най- линия на регресия представлява линейна функция, която следва тенденцията на точките от данни. Формулата за регресионната линия изглежда така: Y = aX + b.
Потребителят може да намери стойностите за "а" и "б", като използва изчисленията за средствата, стандартните отклонения и ковариацията. Стойността за "b" представлява точката, в която регресионната линия пресича ос Y. Стойността може да се намери, като се вземе ковариацията и се раздели на квадрата на стандартното отклонение на X-стойностите. Формулата на Excel влиза в клетка F6 и изглежда така: = F5 / F2 ^ 2.
Стойността за "а" представлява наклонът на регресионната линия. Формулата на Excel влиза в клетка F7 и изглежда така: = F3-F6 * F1.
За да видите формулата за регресионния ред, въведете това свързване на низ в клетка F8:
= CONCATENATE ("Y ="; ROUND (F6; 2); "X"; IF (ЗНАК (F7) = 1; "+"; "-"); ABS (ROUND (F7; 2)))
Изчислете Y стойностите
Следващата стъпка включва изчисляване на Y-стойностите на регресионната линия за дадените X-стойности в набора от данни. Формулата за намиране на стойностите на Y отива в колона С и изглежда по следния начин:
= $ F $ 6 * А (I) + $ F 7 $
Където A (i) е стойността за колона А в ред (i). Формулите изглеждат по следния начин в електронната таблица:
= $ F $ 6 * A1 + $ F 7 $
= $ F $ 6 * А2 + $ F 7 $
= $ F $ 6 * A3 + $ F $ 7 и т.н.
Записите в колона D показват разликите между очакваните и действителните стойности за Y. Формулите изглеждат по следния начин:
= В (I) -C (I), Когато B (i) и C (i) са стойностите в ред (i) в колони B и C, съответно.
Намиране на остатъчната вариация
Най- формула за остатъчна дисперсия влиза в клетка F9 и изглежда така:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Където SUMSQ (D1: D10) е сумата от квадратите на разликите между действителните и очакваните Y стойности, и (COUNT (D1: D10) -2) е броят на точките данни, минус 2 за степени на свобода в данни.
