Стандартне відхилення і дисперсія в EXCEL – Як рахувати

Обчислимо в MS EXCEL дисперсію і стандартне відхилення вибірки. Також обчислимо дисперсію випадкової величини, якщо відомо її розподіл.

Спочатку розглянемо дисперсію , потім стандартне відхилення .

дисперсія вибірки

Дисперсія вибірки ( вибіркова дисперсія, sample variance ) характеризує розкид значень в масиві щодо середнього .

Всі 3 формули математично еквівалентні.

З першої формули видно, що дисперсія вибірки це сума квадратів відхилень кожного значення в масиві від середнього , поділена на розмір вибірки мінус 1.

В MS EXCEL 2007 і раніших версіях для обчислення дисперсії вибірки використовується функція ДИСП () , англ. назва VAR, тобто VARiance. З версії MS EXCEL 2010 рекомендується використовувати її аналог ДИСП.В () , англ. назва VARS, тобто Sample VARiance. Крім того, починаючи з версії MS EXCEL 2010 присутня функція ДИСП.Г (), англ. назва VARP, тобто Population VARiance, яка обчислює дисперсію для генеральної сукупності . Вся відмінність зводиться до знаменника: замість n-1 як у ДИСП.В () , у ДИСП.Г () в знаменнику просто n. До MS EXCEL 2010 року для обчислення дисперсії генеральної сукупності використовувалася функція ДИСПР () .

Дисперсію вибірки можна також обчислити безпосередньо за нижче вказаними формулами (див. ФАЙЛ ПРИКЛАДУ ) = КВАДРОТКЛ (Вибірка) / (РАХУНОК (Вибірка) -1) = (СУММКВ (Вибірка) -СЧЁТ (Вибірка) * СРЗНАЧ (Вибірка) ^ 2) / ( РАХУНОК (Вибірка) -1) – звичайна формула = СУММ ((Вибірка -СРЗНАЧ (Вибірка)) ^ 2) / (РАХУНОК (Вибірка) -1 ) – формула масиву

Дисперсія вибірки дорівнює 0, тільки в тому випадку, якщо всі значення рівні між собою і, відповідно, рівні середнього значення . Зазвичай, чим більше величина дисперсії , тим більше розкид значень в масиві.

Дисперсія вибірки є точковою оцінкою дисперсії розподілу випадкової величини, з якої була зроблена вибірка .

Дисперсія випадкової величини

Щоб обчислити дисперсію випадкової величини, необхідно знати її функцію розподілу .

Для дисперсії випадкової величини Х часто використовують позначення Var (Х). Дисперсія дорівнює математичному очікуванню квадрата відхилення від середнього E (X): Var (Х) = E [(XE (X)) ² ]

Якщо випадкова величина має дискретний розподіл , то дисперсія обчислюється за формулою:

де x _i – значення, яке може приймати випадкова величина, а μ – середнє значення ( математичне очікування випадкової величини ), р (x) – ймовірність, що випадкова величина прийме значення х.

Якщо випадкова величина має неперервний розподіл , то дисперсія обчислюється за формулою:

де р (x) – щільність ймовірності .

Для розподілів, представлених в MS EXCEL , дисперсію можна обчислити аналітично, як функцію від параметрів розподілу. Наприклад, для біноміального розподілу дисперсія дорівнює добутку його параметрів: n * p * q.

Примітка : Дисперсія, є другим центральним моментом , позначається D [X], VAR (х), V (x). Другий центральний момент – числова характеристика розподілу випадкової величини, яка є мірою розкиду випадкової величини щодо математичного очікування .

Розмірність дисперсії відповідає квадрату одиниці виміру вихідних значень. Наприклад, якщо значення у вибірці є вимірювання ваги деталі (в кг), то розмірність дисперсії буде кг ² . Це буває складно інтерпретувати, тому для характеристики розкиду значень частіше використовують величину рівну квадратному кореню з дисперсії – стандартне відхилення .

Деякі властивості дисперсії :

Var (Х + a) = Var (Х), де Х – випадкова величина, а – константа.

Var (ах) = a ² Var (X)

Var (Х) = E [(XE (X)) ² ] = E [X ² -2 * X * E (X) + (E (X)) ² ] = E (X ² ) -E (2 * X * E (X)) + (E (X)) ² = E (X ² ) -2 * E (X) * E (X) + (E (X)) ² = E (X ² ) – (E ( X)) ²

Var (Х + Y) = Var (Х) + Var (Y) + 2 * Cov (Х; Y), де Х і Y – випадкові величини, Cov (Х; Y) – коваріація цих випадкових величин.

Якщо випадкові величини незалежні (independent), то їх коваріація дорівнює 0, і, отже, Var (Х + Y) = Var (Х) + Var (Y). Це властивість дисперсії використовується при виведенні стандартної помилки середнього .

Покажемо, що для незалежних величин Var (Х-Y) = Var (Х + Y). Дійсно, Var (Х-Y) = Var (Х-Y) = Var (Х + (- Y)) = Var (Х) + Var (-Y) = Var (Х) + Var (-Y) = Var ( Х) + (- 1) ² Var (Y) = Var (Х) + Var (Y) = Var (Х + Y). Це властивість дисперсії використовується для побудови довірчого інтервалу для різниці 2х середніх .

Стандартне відхилення вибірки

Стандартне відхилення вибірки – це міра того, наскільки широко розкидані значення у вибірці щодо їх середнього .

За визначенням, стандартне відхилення дорівнює квадратному кореню з дисперсії :

Стандартне відхилення не враховує величину значень в вибірці , а тільки ступінь розсіювання значень навколо їх середнього . Щоб проілюструвати це наведемо приклад.

Обчислимо стандартне відхилення для 2-х вибірок: (1; 5; 9) і (+1001; 1 005; 1009). В обох випадках, s = 4. Очевидно, що відношення величини стандартного відхилення до значень масиву у вибірок істотно відрізняється. Для таких випадків використовується Коефіцієнт варіації (Coefficient of Variation, CV) – відношення Стандартного відхилення до середнього арифметичного , вираженого у відсотках.

В MS EXCEL 2007 і раніших версіях для обчислення Стандартного відхилення вибірки використовується функція = СТАНДОТКЛОН () , англ. назва STDEV, тобто STandard DEViation. З версії MS EXCEL 2010 рекомендується використовувати її аналог = СТАНДОТКЛОН.В () , англ. назва STDEV.S, тобто Sample STandard DEViation.

Крім того, починаючи з версії MS EXCEL 2010 присутня функція СТАНДОТКЛОН.Г () , англ. назва STDEV.P, тобто Population STandard DEViation, яка обчислює стандартне відхилення для генеральної сукупності . Вся відмінність зводиться до знаменника: замість n-1 як у СТАНДОТКЛОН.В () , у СТАНДОТКЛОН.Г () в знаменнику просто n.

Стандартне відхилення можна також обчислити безпосередньо за нижче вказаними формулами (див. ФАЙЛ ПРИКЛАДУ ) = КОРІНЬ (КВАДРОТКЛ (Вибірка) / (РАХУНОК (Вибірка) -1)) = КОРЕНЬ ((СУММКВ (Вибірка) -СЧЁТ (Вибірка) * СРЗНАЧ (Вибірка) ^ 2) / (СЧЕТ (Вибірка) -1))

Інші заходи розкиду

Функція КВАДРОТКЛ () обчислює з умму квадратів відхилень значень від їх середнього . Ця функція поверне той же результат, що і формула = ДИСП.Г ( Вибірка ) * РАХУНОК ( Вибірка ) , де Вибірка – посилання на діапазон, що містить масив значень вибірки ( іменований діапазон ). Обчислення в функції КВАДРОТКЛ () здійснюються за формулою:

Функція СРОТКЛ () є також мірою розкиду безлічі даних. Функція СРОТКЛ () обчислює середнє абсолютних значень відхилень значень від середнього . Ця функція поверне той же результат, що і формула = СУММПРОИЗВ (ABS (Вибірка-СРЗНАЧ (Вибірка))) / РАХУНОК (Вибірка) , де Вибірка – посилання на діапазон, що містить масив значень вибірки.

Обчислення в функції СРОТКЛ () здійснюються за формулою: