У статистичному аналізі дисперсія серед членів набору даних показано, наскільки далеко розташовані точки даних від лінії тренду, також відомої як a лінія регресії. Чим вище дисперсія, тим більше розкидані точки даних. Дослідження дисперсійного аналізу показує, які частини дисперсії можна пояснити характеристиками даних, і які можна віднести до випадкових факторів. Частина дисперсії, яку неможливо пояснити, називається залишковою дисперсією.
Використання таблиць Excel для обчислення залишкової дисперсії
Формула розрахунку залишкової дисперсії передбачає численні складні розрахунки. Для малих наборів даних процес обчислення залишкової дисперсії вручну може бути стомлюючим. Для великих наборів даних завдання може бути вичерпаним. За допомогою електронної таблиці Excel потрібно лише ввести точки даних і вибрати правильну формулу. Програма обробляє складні розрахунки і забезпечує швидкий результат.
Точки даних
Відкрийте нову електронну таблицю Excel і введіть точки даних у дві колонки. Лінії регресії вимагають, щоб кожна точка даних мала два елементи. Статистики зазвичай позначають ці елементи "X" і "Y". Наприклад, Generic Insurance Co. прагне знайти залишкову дисперсію висоти і ваги своїх співробітників. Змінна X представляє висоту, а змінна Y - вагу. Введіть висоту в колонці А і вагові коефіцієнти в стовпці Б.
Пошук середнього
The маю на увазі являє собою середнє значення для кожного елемента в наборі даних. У цьому прикладі Generic Insurance хоче знайти середнє, стандартне відхилення та коваріацію 10 висот працівників і ваги. Середнє значення висот, зазначених у колонці А, можна знайти, ввівши функцію "= AVERAGE (A1: A10)" в комірку F1. Середнє значення ваг, зазначених у колонці B, можна знайти, ввівши функцію "= AVERAGE (B1: B10)" у клітинку F3.
Пошук стандартного відхилення та коваріації
The стандартне відхилення вимірює, наскільки далеко розташовані точки передачі даних від середнього. The коваріації вимірює, наскільки змінюються два елементи точки даних. Стандартне відхилення висот визначається введенням функції "= STDEV (A1: A10)" в комірку F2. Стандартне відхилення ваг виявляється шляхом введення функції "= STDEV (B1: B10)" в комірку F4. Коваріація між висотами і вагами знайдена шляхом введення функції "= COVAR (A1: A10; B1: B10)" в комірку F5.
Пошук лінії регресії
The лінія регресії являє собою лінійну функцію, яка слідує за трендом точок даних. Формула для лінії регресії виглядає так: Y = aX + b.
Користувач може знайти значення для "a" і "b", використовуючи розрахунки для засобів, стандартних відхилень і коваріації. Значення "b" являє собою точку, де лінія регресії перехоплює вісь Y. Значення можна знайти, взявши коваріацію і розділивши її на квадрат стандартного відхилення X-значень. Формула Excel переходить у клітинку F6 і виглядає так: = F5 / F2 ^ 2.
Значення "a" являє собою нахил лінії регресії. Формула 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 * A (i) + $ F $ 7
Де A (i) - значення для стовпця A у рядку (i). Формули виглядають так у таблиці:
= $ F $ 6 * A1 + $ F $ 7
= $ F $ 6 * A2 + $ F $ 7
= $ F $ 6 * A3 + $ F $ 7 і так далі
Записи у стовпці D показують відмінності між очікуваними та фактичними значеннями для Y. Формули виглядають так:
= B (i) -C (i), Де B (i) і C (i) є значеннями у рядку (i) у колонках B і C відповідно.
Пошук залишкової дисперсії
The формула залишкової дисперсії переходить до клітинки F9 і виглядає так:
= SUMSQ (D1: D10) / (COUNT (D1: D10) -2)
Де SUMSQ (D1: D10) є сумою квадратів різниць між фактичними і очікуваними значеннями Y, і (COUNT (D1: D10) -2) - кількість точок даних, мінус 2 для ступенів свободи в даних.
