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

Максимальне та мінімальне значення

Середнє лінійне відхилення

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

a- Середнє лінійне відхилення,

X- аналізований показник,

X̅- Середнє значення показника,

n

В екселі ця функція називається СРОТКЛ.

Після вибору функції СРОТКЛ вказуємо діапазон даних, яким має відбутися розрахунок. Натискаємо "ОК".

Дисперсія

(Module 111)

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

s 2- вибіркова дисперсія, розрахована за даними спостережень,

X- Окремі значення,

X̅- Середнє арифметичне за вибіркою,

n– кількість значень у аналізованій сукупності даних.

Відповідна функція Excel — ДИСП.Г. При аналізі щодо невеликих вибірок (приблизно до 30 спостережень) слід використовувати , яка розраховується за наступною формулою.

Відмінність, очевидно, лише у знаменнику. В Excel для розрахунку вибіркової незміщеної дисперсії є функція ДИСП.В.

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

Середньоквадратичне відхилення

Середньоквадратичне відхилення (СКО) – це корінь із дисперсії. Цей показник також називають стандартним відхиленням та розраховують за формулою:

по генеральної сукупності

за вибіркою

Можна просто витягти корінь з дисперсії, але в Excel для середньоквадратичного відхилення є готові функції: СТАНДОТКЛОН.Гі СТАНДОТКЛОН.(за генеральною та вибірковою сукупністю відповідно).

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

Далі, як завжди, вказуємо потрібний діапазон та натискаємо на «ОК». Середньоквадратичне відхилення має самі одиниці виміру, як і аналізований показник, тому можна порівняти з вихідними даними. Про це нижче.

Коефіцієнт варіації

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

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

СТАНДОТКЛОН.Г()/СРЗНАЧ()

У дужках зазначається діапазон даних. При необхідності використовують середнє відхилення за вибіркою (СТАНДОТКЛОН.В).

Коефіцієнт варіації зазвичай виявляється у відсотках, тому комірку з формулою можна обрамити відсотковим форматом. Потрібна кнопка знаходиться на стрічці на вкладці «Головна»:

Змінити формат також можна, вибравши з контекстного меню після виділення комірки та натискання правою кнопкою мишки.

Коефіцієнт варіації, на відміну інших показників розкиду значень, використовується як самостійний і інформативний індикатор варіації даних. У статистиці прийнято вважати, що й коефіцієнт варіації менше 33%, то сукупність даних є однорідною, якщо понад 33%, то – неоднорідною. Ця інформація може бути корисною для попереднього опису даних та визначення можливостей проведення подальшого аналізу. Крім того, коефіцієнт варіації, що вимірюється у відсотках, дозволяє порівнювати ступінь розкиду різних даних незалежно від їх масштабу та одиниць вимірів. Корисна властивість.

Коефіцієнт осциляції

Ще один показник розкиду даних на сьогодні – коефіцієнт осциляції. Це співвідношення розмаху варіації (різниці між максимальним та мінімальним значенням) до середньої. Готовий формули Excel немає, тому доведеться скомпонувати три функції: МАКС, МІН, СРЗНАЧ.

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

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

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

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

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

(Module 111)

Щоб повернути дисперсію в реальність, тобто використовувати з більш приземлених цілей, з неї витягують квадратний корінь. Виходить так зване середньоквадратичне відхилення (СКО). Зустрічаються назви « стандартне відхилення» або «Сігма» (від назви грецької літери). Формула стандартного відхилення має вигляд:

Для отримання цього показника за вибіркою використовують формулу:

Як і з дисперсією, є й трохи інший варіант розрахунку. Але зі зростанням вибірки різниця зникає.

Середньоквадратичне відхилення, очевидно, також характеризує міру розсіювання даних, але тепер (на відміну дисперсії) його можна порівнювати з вихідними даними, так як одиниці виміру у них однакові (це випливає з формули розрахунку). Але і цей показник у чистому вигляді не дуже інформативний, тому що в ньому закладено занадто багато проміжних розрахунків, які збивають з пантелику (відхилення, квадрат, сума, середнє, корінь). Тим не менш, із середньоквадратичним відхиленням вже можна працювати безпосередньо, тому що властивості даного показника добре вивчені та відомі. Наприклад, є таке правило трьох сигм, Що свідчить, що у даних 997 значень з 1000 знаходяться в межах ±3 сигми від середньої арифметичної. Середньоквадратичне відхилення як міра невизначеності також бере участь у багатьох статистичних розрахунках. З її допомогою встановлюють ступінь точності різних оцінок та прогнозів. Якщо варіація дуже велика, то стандартне відхилення теж вийде великим, отже, і прогноз буде неточним, що висловиться, наприклад, у дуже широких довірчих інтервалах.

Коефіцієнт варіації

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

Коефіцієнт варіації вимірюється у відсотках (якщо помножити на 100%). За цим показником можна порівнювати самих різних явищнезалежно від їх масштабу та одиниць виміру. Цей факті робить коефіцієнт варіації настільки популярним.

У статистиці прийнято, що, якщо значення коефіцієнта варіації менше 33%, то сукупність вважається однорідною, якщо більше 33%, то неоднорідною. Мені тут важко щось прокоментувати. Не знаю хто і чому так визначив, але це вважається аксіомою.

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

І графік нагадування.

За цими даними розрахуємо різні показникиваріації.

Середнє значення – це середня середня арифметична.

Розмах варіації – різниця між максимумом та мінімумом:

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

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

Розрахунок зведемо до таблички.

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

Підіб'ємо деякі підсумки.

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

1. Розмах варіації – різниця між максимумом та мінімумом. Відображає діапазон можливих значень.
2. Середнє лінійне відхилення – відбиває середнє з абсолютних (за модулем) відхилень всіх значень аналізованої сукупності їх середньої величини.
3. Дисперсія – середній квадрат відхилень.
4. Середньоквадратичне відхилення – корінь із дисперсії (середнього квадрата відхилень).
5. Коефіцієнт варіації – найбільш універсальний показник, відбиває ступінь розкиду значень незалежно від своїх масштабу та одиниць виміру. Коефіцієнт варіації вимірюється у відсотках і може бути використаний для порівняння варіації різних процесів та явищ.

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

Х i -випадкові (поточні) величини;

X̅– середнє значення випадкових величин за вибіркою, розраховується за такою формулою:

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

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

Розгадка магічного слова «дисперсія» полягає у цих трьох словах: середній – квадрат – відхилень.

Середнє квадратичне відхилення (СКО)

Витягуючи з дисперсії квадратний корінь, отримуємо так зване « середньоквадратичне відхилення".Зустрічаються назви "стандартне відхилення" або "сигма" (від назви грецької літери σ .). Формула середнього квадратичного відхилення має вигляд:

Отже, дисперсія – це сигма у квадраті, або – середнє квадратичне відхилення у квадраті.

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

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

Для порівняння правила двох сигм та правила трьох сигм використовуємо формулу Лапласа:

Ф - Ф ,

де Ф(x) – функція Лапласа;

Мінімальне значення

β = максимальне значення

s = значення сигми (середнє квадратичне відхилення)

a = середнє значення

У цьому випадку використовується приватний вид формули Лапласа коли межі α та β значень випадкової величини X одно відстоять від центру розподілу a = M(X) на деяку величину d: a = a-d, b = a + d. Або (1) Формула (1) визначає можливість заданого відхилення d випадкової величини X з нормальним законом розподілу від її математичного очікування М(X) = a.

Якщо у формулі (1) прийняти послідовно d = 2s та d = 3s, то отримаємо: (2), (3).

Правило двох сигм

Майже достовірно (з довірчою ймовірністю 0,954) можна стверджувати, що всі значення випадкової величини X з нормальним законом розподілу відхиляються від її математичного очікування M(X) = a на величину невелику 2s (двох середніх квадратичних відхилень). Довірчою ймовірністю (Pд) називають ймовірність подій, що умовно приймаються за достовірні (їх ймовірність близька до 1). Проілюструємо правило двох сигм геометрично. На рис. 6 зображена крива Гауса з центром розподілу а. Площа, обмежена всією кривою та віссю Оx, дорівнює 1 (100%), а площаміж абсцисами а-2s і а+2s, згідно з правилом двох сигм, дорівнює 0,954 (95,4% від усієї площі). Площа заштрихованих ділянок дорівнює 1-0,954 = 0,046 (5% від всієї площі). Ці ділянки називають критичною областю значень випадкової величини. Значення випадкової величини, які у критичну область, малоймовірні і практично умовно приймаються за неможливі.

Імовірність умовно неможливих значень називають рівнем важливості випадкової величини. Рівень значущості пов'язаний із довірчою ймовірністю формулою:

де q – рівень значимості, виражений у відсотках.

Правило трьох сигм

При вирішенні питань, що вимагають більшої надійності, коли довірчу ймовірність (Pд) приймають рівною 0,997 (точніше - 0,9973), замість правила двох сигм, згідно з формулою (3), використовують правило трьох сигм.

Згідно правилу трьох сигмпри довірчої ймовірності 0,9973 критичною областю буде область значень ознаки поза інтервалом (а-3s, а+3s). Рівень значущості складає 0,27%.

Іншими словами, ймовірність того, що абсолютна величинавідхилення перевищить потрійне середнє квадратичне відхилення, дуже мала, саме дорівнює 0,0027=1-0,9973. Це означає, що лише 0,27% випадків може статися. Такі події, з принципу неможливості малоймовірних подій, вважатимуться практично неможливими. Тобто. Вибірка високоточна.

У цьому полягає сутність правила трьох сигм:

Якщо випадкова величина розподілена нормально, то абсолютна величина її відхилення від математичного очікування вбирається у потрійного середнього квадратичного відхилення (СКО).

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

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

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

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

Але, природно, що в Екселі користувачеві не доводиться це вираховувати, оскільки за нього робить програма. Давайте дізнаємося, як порахувати стандартне відхилення в Excel.

Розрахунок у Excel

Розрахувати вказану величину в Екселі можна за допомогою двох спеціальних функцій СТАНДОТКЛОН.(за вибірковою сукупністю) та СТАНДОТКЛОН.Г(за генеральною сукупністю). Принцип їхньої дії абсолютно однаковий, але викликати їх можна трьома способами, про які ми поговоримо нижче.

Спосіб 1: майстер функцій

Спосіб 2: вкладка "Формули"

Спосіб 3: ручне введення формули

Існує також спосіб, коли взагалі не потрібно буде викликати вікно аргументів. Для цього слід запровадити формулу вручну.

Як бачимо, механізм розрахунку середньоквадратичного відхилення в Excel дуже простий. Користувачеві потрібно лише ввести числа із сукупності або посилання на комірки, які їх містять. Усі розрахунки виконує сама програма. Набагато складніше усвідомити, що ж є показник, що розраховується, і як результати розрахунку можна застосувати на практиці. Але розуміння цього вже належить більше до сфери статистики, ніж навчання роботи з програмним забезпеченням.

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

Середньоквадратичне відхиленнядає можливість оцінити розкид значень, отриманих у результаті виміру якогось параметра. Позначається символом (грецька літера "сигма").

Формула до розрахунку досить проста. Щоб знайти середньоквадратичне відхилення, потрібно взяти квадратний корінь із дисперсії. Тож тепер ви повинні запитати: "А що ж таке дисперсія?"

Що таке дисперсія

Визначення дисперсії звучить так. Дисперсія це середнє арифметичне від квадратів відхилень значень від середнього.

Щоб знайти дисперсію, послідовно проведіть такі обчислення:

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

Розглянемо з прикладу. Допустимо, ви з друзями вирішили виміряти зростання ваших собак (у міліметрах). В результаті вимірів ви отримали такі дані вимірювань росту (в загривку): 600 мм, 470 мм, 170 мм, 430 мм і 300 мм.

Обчислимо середнє значення, дисперсію та середньоквадратичне відхилення.

Спочатку знайдемо середнє значення. Як ви вже знаєте, для цього потрібно скласти всі виміряні значення та поділити на кількість вимірів. Хід обчислень:

Середня мм.

Отже, середня (середньоарифметична) становить 394 мм.

Тепер потрібно визначити відхилення зростання кожного з собак від середнього:

Зрештою, щоб обчислити дисперсію, кожну з отриманих різниць зводимо в квадрат, а потім знаходимо середнє арифметичне від отриманих результатів:

Дисперсія мм 2 .

Таким чином, дисперсія становить 21 704 мм 2 .

Як знайти середньоквадратичне відхилення

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

Мм (округлено до найближчого цілого значення мм).

Застосувавши цей метод, ми з'ясували, що деякі собаки (наприклад, ротвейлери) дуже великі собаки. Але є й дуже маленькі собаки (наприклад, такси, тільки казати їм цього не варто).

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

Тобто за допомогою середньоквадратичного відхилення ми отримуємо "стандартний" метод, який дозволяє дізнатися, яке із значень є нормальним (середньостатистичним), а яке екстраординарно більшим або, навпаки, малим.

Що таке стандартне відхилення

Але… все буде трохи інакше, якщо ми аналізуватимемо вибіркуданих. У нашому прикладі ми розглядали генеральну сукупність.Тобто наші 5 собак були єдиними у світі собаками, які нас цікавили.

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

Якщо є значень, то:

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

Наприклад, якщо наших п'ять собак – лише вибірка з генеральної сукупності собак (всіх собак на планеті), ми маємо ділити на 4, а не на 5,а саме:

Дисперсія вибірки = мм 2 .

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

Можна сказати, що ми зробили деяку “корекцію” у випадку, коли наші значення є лише невеликою вибіркою.

Примітка. Чому саме квадрати різниць?

Але чому при обчисленні дисперсії ми беремо квадрати різниць? Допустимо при вимірі якогось параметра, ви отримали наступний набір значень: 4; 4; -4; -4. Якщо ми просто складемо абсолютні відхилення від середнього (різниці) між собою. від'ємні значеннявзаємно знищаться з позитивними:

.

Виходить, цей варіант марний. Тоді, можливо, варто спробувати абсолютні значення відхилень (тобто модулі цих значень)?

На перший погляд виходить непогано (отримана величина, до речі, називається середнім абсолютним відхиленням), але не у всіх випадках. Спробуємо інший приклад. Нехай у результаті виміру вийшов наступний набір значень: 7; 1; -6; -2. Тоді середнє абсолютне відхилення одно:

Ось це так! Знов отримали результат 4, хоча різниці мають набагато більший розкид.

А тепер подивимося, що вийде, якщо звести різниці у квадрат (і взяти потім квадратний корінь із їхньої суми).

Для першого прикладу вийде:

.

Для другого прикладу вийде:

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

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

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

Про те, як знайти середньоквадратичне відхилення, вам розповів , Сергій Валерійович