Що свідчить дисперсія спостерігається величини. Дисперсія та стандартне відхилення у MS EXCEL

Серед безлічі показників, що застосовуються у статистиці, потрібно виділити розрахунок дисперсії. Слід зазначити, що виконання вручну цього обчислення – досить стомлююче заняття. На щастя, у Excel є функції, що дозволяють автоматизувати процедуру розрахунку. З'ясуємо алгоритм роботи із цими інструментами.

Дисперсія – це показник варіації, який є середнім квадратом відхилень від математичного очікування. Таким чином, він виражає розкид чисел щодо середнього значення. Обчислення дисперсії може проводитися як за генеральної сукупності, Так і за вибірковою.

Спосіб 1: розрахунок за генеральною сукупністю

Для розрахунку даного показникав Excel за генеральною сукупністю застосовується функція ДИСП.Г. Синтаксис цього виразу має такий вигляд:

ДИСП.Г(Число1; Число2; ...)

Усього може бути застосовано від 1 до 255 аргументів. Як аргументи можуть виступати, як числові значення, так і посилання на осередки, в яких вони містяться.

Подивимося, як визначити це значення для діапазону з числовими даними.

Спосіб 2: розрахунок за вибіркою

На відміну від обчислення значення за генеральною сукупністю, у розрахунку за вибіркою у знаменнику вказується не Загальна кількістьчисел, але в одне менше. Це робиться з метою корекції похибки. Ексель враховує даний нюанс у спеціальній функції, яка призначена для цього виду обчислення – ДИСП.В. Її синтаксис представлений такою формулою:

ДИСП.В(Число1; Число2; ...)

Кількість аргументів, як і попередньої функції, теж може коливатися від 1 до 255.

Як бачимо, програма Ексель здатна значною мірою полегшити розрахунок дисперсії. Ця статистична величина може бути розрахована додатком як за генеральною сукупністю, так і за вибіркою. При цьому всі дії користувача фактично зводяться тільки до вказівки діапазону чисел, що обробляються, а основну роботу Excelробить сам. Безумовно, це заощадить значну кількість часу користувачів.

На цій сторінці описано стандартний прикладзнаходження дисперсії, також Ви можете переглянути інші завдання на її знаходження

Приклад 1. Визначення групової, середньої з групової, міжгрупової та загальної дисперсії

Приклад 2. Знаходження дисперсії та коефіцієнта варіації у групувальній таблиці

Приклад 3. Знаходження дисперсії у дискретному ряду

Приклад 4. Є такі дані щодо групи з 20 студентів заочного відділення. Потрібно побудувати інтервальний ряд розподілу ознаки, розрахувати середнє значення ознаки та вивчити його дисперсію

Побудуємо інтервальне угруповання. Визначимо розмах інтервалу за формулою:

де X max- максимальне значеннягрупувальної ознаки;
X min-мінімальне значення групувальної ознаки;
n – кількість інтервалів:

Приймаємо n=5. Крок дорівнює: h = (192 - 159) / 5 = 6,6

Складемо інтервальне угруповання

Для подальших розрахунків збудуємо допоміжну таблицю:

X"i - середина інтервалу. (наприклад середина інтервалу 159 - 165,6 = 162,3)

Середню величину зростання студентів визначимо за формулою середньої арифметичної зваженої:

Визначимо дисперсію за такою формулою:

Формулу можна перетворити так:

З цієї формули випливає, що дисперсія дорівнює різниці середньої з квадратів варіантів і квадрата та середньої.

Дисперсія у варіаційних рядах з рівними інтервалами за способом моментів може бути розрахована наступним способом при використанні другої властивості дисперсії (розділивши всі варіанти на величину інтервалу). Визначення дисперсії, обчисленої за способом моментів, за такою формулою менш трудомісткий:

де i – величина інтервалу;
А - умовний нуль, як який зручно використовувати середину інтервалу, що володіє найбільшою частотою;
m1 – квадрат моменту першого порядку;
m2 – момент другого порядку

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

Підставляючи в цю формулудисперсії q =1-р, отримуємо:

Види дисперсії

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

Внутрішньогрупова дисперсія характеризує випадкову варіацію, тобто. частина варіації, яка обумовлена ​​впливом неврахованих факторів і не залежить від ознаки-фактора, покладеної в основу угруповання. Така дисперсія дорівнює середньому квадрату відхилень окремих значень ознаки всередині групи X від середньої арифметичної групи і може бути обчислена як проста дисперсія або зважена дисперсія.

Таким чином, внутрішньогрупова дисперсія вимірюєваріацію ознаки всередині групи та визначається за формулою:

де хі - групова середня;
ni – число одиниць у групі.

Наприклад, внутрішньогрупові дисперсії, які треба визначити в задачі вивчення впливу кваліфікації робітників на рівень продуктивності праці в цеху показують варіації виробітку в кожній групі, викликані всіма можливими факторами ( технічний станобладнання, забезпеченість інструментами та матеріалами, вік робітників, інтенсивність праці тощо), крім відмінностей у кваліфікаційному розряді(Всередині групи всі робітники мають одну й ту саму кваліфікацію).

Поряд із вивченням варіації ознаки по всій по всій сукупності в цілому часто буває необхідно простежити кількісні зміни ознаки по групах, на які поділяється сукупність, а також між групами. Таке вивчення варіації досягається за допомогою обчислення та аналізу різних видівдисперсії.
Виділяють дисперсію загальну, міжгрупову та внутрішньогрупову.
Загальна дисперсія σ 2вимірює варіацію ознаки у всій сукупності під впливом всіх чинників, що зумовили цю варіацію, .

Міжгрупова дисперсія (δ) характеризує систематичну варіацію, тобто. відмінності у величині досліджуваного ознаки, що виникають під впливом ознаки-фактора, покладеного в основу угруповання. Вона розраховується за такою формулою:
.

Внутрішньогрупова дисперсія (σ)відбиває випадкову варіацію, тобто. частина варіації, що відбувається під впливом неврахованих факторів і не залежить від ознаки-фактора, покладеного в основу угруповання. Вона обчислюється за такою формулою:
.

Середня із внутрішньогрупових дисперсій: .

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

В аналізі широко використовується показник, що є частиною міжгрупової дисперсії в загальній дисперсії. Він має назву емпіричного коефіцієнта детермінації (? 2): .
Корінь квадратний з емпіричного коефіцієнта детермінації зветься емпіричного кореляційного відношення (η):
.
Воно характеризує вплив ознаки, покладеної в основу угруповання, на варіацію результативної ознаки. Емпіричне кореляційне відношення змінюється не більше від 0 до 1.
Покажемо його практичне використанняна наступному прикладі (табл. 1).

Приклад №1. Таблиця 1 – Продуктивність праці двох груп робітників одного з цехів НВО «Циклон»

Розрахуємо загальну та групові середні та дисперсії:

Вихідні дані для обчислення середньої внутрішньогрупової та міжгрупової дисперсії представлені в табл. 2.
Таблиця 2
Розрахунок і 2 по двох групах робочих.

Групи робітників	Чисельність робітників, чол.	Середня, дет./змін.	Дисперсія
Пройшли технічне навчання	5	95	42,0
Не пройшли технічне навчання	5	81	231,2
Усі робітники	10	88	185,6

Розрахуємо показники. Середня із внутрішньогрупових дисперсій:
.
Міжгрупова дисперсія

Загальна дисперсія:
Отже, емпіричне кореляційне співвідношення: .

Поряд із варіацією кількісних ознак може спостерігатися і варіація якісних ознак. Таке вивчення варіації досягається за допомогою обчислення наступних видівдисперсій:

Внутрішньогрупова дисперсія частки визначається за формулою

де n i- Чисельність одиниць в окремих групах.
Частка досліджуваного ознаки у всій сукупності, що визначається за такою формулою:
Три види дисперсії пов'язані між собою так:
.

Це співвідношення дисперсій називається теоремою складання дисперсій частки ознаки.

Дисперсія - це міра розсіювання, що описує порівняльне відхилення між значеннями даних та середньою величиною. Є найбільш використовуваним заходом розсіювання в статистиці, що обчислюється шляхом підсумовування, зведеного в квадрат, відхилення кожного значення даних від середньої величини. Формула для обчислення дисперсії представлена ​​нижче:

s 2 – дисперсія вибірки;

x ср - середнє значення вибірки;

n — розмір вибірки (кількість значень даних),

(x i - x ср) - відхилення від середньої величини для кожного значення набору даних.

Для кращого розуміння формули розберемо приклад. Я не дуже люблю готування, тому заняттям цим займаюся дуже рідко. Проте, щоб не померти з голоду, час від часу мені доводиться підходити до плити для реалізації задуму щодо насичення мого організму білками, жирами та вуглеводами. Набір даних, поданий нижче, показує, скільки разів Ренат готує їжу щомісяця:

Першим кроком при обчисленні дисперсії є визначення середнього значення вибірки, яке в прикладі дорівнює 7,8 рази на місяць. Інші обчислення можна полегшити за допомогою наступної таблиці.

Фінальна фаза обчислення дисперсії виглядає так:

Для тих, хто любить робити всі обчислення за один раз, рівняння виглядатиме так:

Використання методу «сирого рахунку» (приклад із готуванням)

Існує більше ефективний спосібобчислення дисперсії відомий як метод «сирого рахунку». Хоча з першого погляду рівняння може здатися дуже громіздким, насправді воно не таке страшне. Можете в цьому переконатись, а потім і вирішіть, який метод вам більше подобається.

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

- Квадрат суми всіх значень даних.

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

Як бачите, результат вийшов той самий, що й під час використання попереднього методу. Переваги даного методустають очевидними зі зростанням розміру вибірки (n).

Розрахунок дисперсії в Excel

Як ви вже, напевно, здогадалися, в Excel є формула, що дозволяє розрахувати дисперсію. Причому, починаючи з Excel 2010, можна знайти 4 різновиди формули дисперсії:

1) ДИСП.В - Повертає дисперсію за вибіркою. Логічні значення та текст ігноруються.

2) ДИСП.Г - Повертає дисперсію по генеральній сукупності. Логічні значення та текст ігноруються.

3) ДИСПА - Повертає дисперсію за вибіркою з урахуванням логічних та текстових значень.

4) ДИСПРА - Повертає дисперсію по генеральній сукупності з урахуванням логічних та текстових значень.

Для початку розберемося в різниці між вибіркою та генеральною сукупністю. Призначення описової статистики у тому, щоб підсумовувати чи відображати дані те щоб оперативно отримувати загальну картину, так би мовити, огляд. Статистичний висновок дозволяє робити висновки про будь-яку сукупність на основі вибірки даних із цієї сукупності. Сукупність є всі можливі результати чи виміри, які становлять нам інтерес. Вибірка - це підмножина сукупності.

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

Різниця у формулі розрахунку дисперсії між вибіркою та сукупністю полягає у знаменнику. Де для вибірки він дорівнюватиме (n-1), а для генеральної сукупності тільки n.

Тепер розберемося з функціями розрахунку дисперсії із закінченнями А,в описі яких сказано, що при розрахунку враховуються текстові та логічні значення. У даному випадкупри розрахунку дисперсії певного масиву даних, де зустрічаються не числові значення, Excel інтерпретуватиме текстові та хибні логічні значення як рівними 0, а справжні логічні значення як рівними 1.

Отже, якщо у вас є масив даних, розрахувати його дисперсію не складе ніяких труднощів, скориставшись однією з перерахованих вище функцій Excel.

За даними вибіркового обстеження проведено угруповання вкладників за розміром вкладу в Ощадбанку міста:

Визначте:

1) розмах варіації;

2) середній розмір вкладу;

3) середнє лінійне відхилення;

4) дисперсію;

5) середнє квадратичне відхилення;

6) коефіцієнт варіації вкладів.

Рішення:

Цей ряд розподілу містить відкриті інтервали. У таких рядах умовно приймається величина інтервалу першої групи дорівнює величині інтервалу наступної, а величина інтервалу останньої групи дорівнює величині інтервалу попередньої.

Величина інтервалу другої групи дорівнює 200, отже, і величина першої групи також дорівнює 200. Величина інтервалу передостанньої групи дорівнює 200, отже останній інтервал матиме величину, рівну 200.

1) Визначимо розмах варіації як різницю між найбільшим і найменшим значеннямознаки:

Розмах варіації обсягу вкладу дорівнює 1000 рублів.

2) Середній розмір вкладу визначимо за формулою середньої арифметичної зваженої.

Попередньо визначимо дискретну величинуознаки у кожному інтервалі. Для цього за формулою середньої арифметичної простий знайдемо середини інтервалів.

Середнє значення першого інтервалу дорівнюватиме:

другого - 500 і т.д.

Занесемо результати обчислень до таблиці:

Розмір внеску, руб.	Число вкладників, f	Середина інтервалу, х	xf
200-400	32	300	9600
400-600	56	500	28000
600-800	120	700	84000
800-1000	104	900	93600
1000-1200	88	1100	96800
Разом	400	-	312000

Середній розмір вкладу в Ощадбанку міста дорівнюватиме 780 рублів:

3) Середнє лінійне відхилення є середня арифметична з абсолютних відхилень окремих значень ознаки від загальної середньої:

Порядок розрахунку середнього лінійного відхилення в інтервальному ряду розподілу наступний:

1. Обчислюється середня арифметична зважена, як показано у п. 2).

2. Визначаються абсолютні відхилення варіант від середньої:

3. Отримані відхилення множаться на частоти:

4. Знаходиться сума зважених відхилень без урахування знака:

5. Сума завислих відхилень ділиться на суму частот:

Зручно користуватися таблицею розрахункових даних:

Розмір внеску, руб.	Число вкладників, f	Середина інтервалу, х
200-400	32	300	-480	480	15360
400-600	56	500	-280	280	15680
600-800	120	700	-80	80	9600
800-1000	104	900	120	120	12480
1000-1200	88	1100	320	320	28160
Разом	400	-	-	-	81280

Середнє лінійне відхилення обсягу вкладу клієнтів Ощадбанку становить 203,2 рубля.

4) Дисперсія – це середня арифметична квадратів відхилень кожного значення ознаки від середньої арифметичної.

Розрахунок дисперсії в інтервальних рядахрозподілу провадиться за формулою:

Порядок розрахунку дисперсії у разі наступний:

1. Визначають середню арифметичну зважену, як показано у п. 2).

2. Знаходять відхилення варіант від середньої:

3. Зводять квадрат відхилення кожної варіанти від середньої:

4. Помножують квадрати відхилень на ваги (частоти):

5. Підсумовують отримані твори:

6. Отримана сума поділяється на суму ваг (частот):

Розрахунки оформимо до таблиці:

Розмір внеску, руб.	Число вкладників, f	Середина інтервалу, х
200-400	32	300	-480	230400	7372800
400-600	56	500	-280	78400	4390400
600-800	120	700	-80	6400	768000
800-1000	104	900	120	14400	1497600
1000-1200	88	1100	320	102400	9011200
Разом	400	-	-	-	23040000