Дисперсія - це міра розсіювання, що описує порівняльне відхилення між значеннями даних та середньою величиною. Є найбільш використовуваною мірою розсіювання в статистиці, що обчислюється шляхом підсумовування, зведеного квадрат, відхилення кожного значення даних від середньої величини. Формула для обчислення дисперсії представлена нижче:
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.
Добридень!
У статті я вирішив розглянути, як працює стандартне відхилення Excel за допомогою функції СТАНДОТКЛОН. Я просто дуже давно не описував і не коментував, а ще просто тому, що це дуже корисна функція для тих, хто вивчає вищу математику. А допомогти студентам – це святе, по собі знаю, як важко вона освоюється. В реальності функції стандартних відхилень можна використовувати для визначення стабільності продукції, що продається, створення ціни, коригування або формування асортименту, ну і інших не менше корисних аналізівваших продажів.
В Excel використовуються кілька варіантів цієї функції:
Математична теорія
Для початку трошки про теорію, як математичною мовоюможна описати функцію стандартного відхиленнядля застосування в Excel, для аналізу, наприклад, даних статистики продажів, але про це далі. Попереджаю відразу, писатиму дуже багато незрозумілих слів ...)))), якщо що нижче за текстом дивіться відразу практичне застосування в програмі.
Що ж робить стандартне відхилення? Воно проводить оцінку середньоквадратичного відхилення. випадкової величиниХ щодо її математичного очікуванняна основі незміщеної оцінки її дисперсії. Погодьтеся, звучить заплутано, але я думаю учні зрозуміють про що власне йдеться!
Для початку нам потрібно визначити «середньоквадратичне відхилення», щоб надалі зробити розрахунок «стандартного відхилення», у цьому нам допоможе формула: 
Описати формулу можливо так: вимірюватиметься в тих самих одиницях як і виміри випадкової величини і застосовується при обчисленні стандартної середньоарифметичної помилки, коли виробляються побудови довірчих інтервалів, при перевірці гіпотез на статистику або ж при аналізі лінійного взаємозв'язку між незалежними величинами. Функцію визначають як квадратний коріньіз дисперсії незалежних величин.
Тепер можна дати визначення та стандартного відхилення- Це аналіз середньоквадратичного відхилення випадкової величини Х порівняно її математичної перспективи на основі незміщеної оцінки її дисперсії. Формула записується так: 
Зазначу, що всі дві оцінки надаються зміщеними. При загальних випадкахпобудувати незміщену оцінку неможливо. Але оцінка на основі оцінки незміщеної дисперсії буде заможною.
Практичне втілення у Excel
Ну а тепер відійдемо від нудної теорії та на практиці подивимося, як працює функція СТАНДОТКЛОН. Я не розглядатиму всі варіації функції стандартного відхилення в Excel, достатньо і однієї, але в прикладах. Для прикладу розглянемо, як визначається статистика стабільності продажів.
Для початку подивіться на орфографію функції, а вона, як ви бачите, дуже проста:
СТАНДОТКЛОН.Г(_число1_;_число2_; ….), де:
Тепер створимо файл прикладу та на його основі розглянемо роботу цієї функції. 
Так як для проведення аналітичних обчислень необхідно використовувати не менше трьох значень, як у принципі у будь-якому статистичному аналізі, то і я взяв умовно 3 періоди, це може бути рік, квартал, місяць чи тиждень. У моєму випадку – місяць. Для найбільшої достовірності рекомендую брати якомога більшу кількість періодів, але ніяк не менше трьох. Усі дані у таблиці дуже прості для наочності роботи та функціональності формули.
Спочатку нам необхідно порахувати середнє значення по місяцях. Будемо використовувати для цього функцію СРЗНАЧ і буде формула: =СРЗНАЧ(C4:E4). 
Тепер, власне, ми і можемо знайти стандартне відхилення за допомогою функції СТАНДОТКЛОН.Г у значенні якої потрібно проставити продаж товару кожного періоду. Вийде формула наступного виду: =СТАНДОТКЛОН.Г(C4;D4;E4). 
Ну ось і зроблено половину справ. Наступним крокомми формуємо «Варіацію», це виходить розподілом на середнє значення, стандартного відхилення та результат переводимо у відсотки. Отримуємо таку таблицю: 
Ну ось основні розрахунки закінчені, залишилося розібратися, як ідуть продажі стабільно чи ні. Візьмемо як умову, що відхилення в 10% це вважається стабільно, від 10 до 25% це невеликі відхилення, а ось все, що вище 25%, це вже не стабільно. Для отримання результату за умовами скористаємося логічною та для отримання результату напишемо формулу:
ЯКЩО(H4<0,1;"стабильно";ЕСЛИ(H4<0,25;"нормально";"не стабильно"))
Всі діапазони взяті умовно для наочності, ваші завдання можуть мати зовсім інші умови. 
Для поліпшення візуалізації даних, коли ваша таблиця має тисячі позицій, варто скористатися можливістю, накласти за деякими умовами, які вам потрібні або використовувати, щоб колірною гамою виділити певні варіанти, це буде дуже наочно.
Для початку виділяєте , для яких застосовуватиметься умовне форматування. У панелі керування «Головна» вибираєте «Умовне форматування» і в меню «Правила виділення осередків» і наступним натискаєте пункт меню «Текст містить…». З'являється діалогове вікно, у яке ви вписуєте свої умови.
Після того як прописали умови, наприклад, "стабільно" - зелений колір, "нормально" - жовтий і "не стабільно" - червоний, отримаємо красиву і зрозумілу таблицю в якій видно на що в першу чергу звертати увагу.
Використання VBA для функції СТАНДОТКЛОН.
Кому буде цікаво, може автоматизувати свої обчислення за допомогою макросів і скористатися наступною функцією:
Function MyStDevP(Arr) Dim x, aCnt&, aSum#, aAver#, tmp# For Each x In Arr aSum = aSum + x " обчислюємо суму елементів масиву aCnt = aCnt + 1 " обчислюємо кількість елементів Next x aAver = aSum / aCnt "середнє значення For Each x In Arr tmp = tmp + (x - aAver) ^ 2 " обчислюємо суму квадратів різниці елементів масиву та середнього значення Next x MyStDevP = Sqr(tmp / aCnt) " обчислюємо СТАНДОТКЛОН.
Function MyStDevP (Arr) Dim x, aCnt &, aSum#, aAver#, tmp# For Each x In Arr aSum = aSum + x "обчислюємо суму елементів масиву |
У статистиці використовується величезна кількість показників, і один із них — розрахунок дисперсії в Excel. Якщо це робити самому вручну, піде дуже багато часу, можна припустити безліч помилок. Сьогодні ми розглянемо як розкласти математичні формули на прості функції. Давайте розберемо кілька найпростіших, найшвидших і зручніших способів розрахунку, які дозволять все зробити за лічені хвилини.
Обчислюємо дисперсію
Дисперсією випадкової величини називається математичне очікування квадрата відхилення випадкової величини від її математичного очікування.
Розраховуємо за генеральною сукупністю
Щоб обчислити мат. очікування у програмі буде застосовуватися функція ДИСП.Г, та її синтаксис виглядає так «=ДИСП.Г(Число1;Число2;…)».
Можливо застосувати максимум 255 аргументів, не більше. Аргументами можуть бути прості числа або посилання на комірки, в яких вони вказані. Давайте розглянемо, як порахувати дисперсію в Microsoft Excel:
1. Насамперед слід виділити комірку, де буде відображатися результат обчислень, а далі натиснути на кнопку «Вставити функцію».
2. Відкриється оболонка керування функціями. Там потрібно шукати функцію «ДІСП.Г», яка може бути у категорії «Статистичні» або «Повний алфавітний список». Коли її буде знайдено, слід виділити її та клікнути «ОК».
3. Запуститься вікно з аргументами функції. У ньому потрібно виділити рядок «Число 1» і на аркуші виділити діапазон осередків із числовим рядом.
4. Після цього в осередку, куди було введено функцію, будуть виведені результати розрахунків.
Ось так легко можна знайти дисперсію в Excel.
Проводимо розрахунок за вибіркою
В даному випадку вибіркова дисперсія в Excel вираховується із зазначенням у знаменнику загальної кількості чисел, а на одне менше. Це робиться для меншої похибки за допомогою спеціальної функції ДИСП.В, синтаксис якої = ДИСП.В(Число1; Число2; ...). Алгоритм дій:
- Як і в попередньому методі, потрібно виділити комірку для результату.
- У майстрі функції слід знайти "ДИСП.В" у категорії "Повний алфавітний перелік" або "Статистичні".
- Далі з'явиться вікно, і слід діяти так само, як і в попередньому методі.
Відео: Розрахунок дисперсії в Excel
Висновок
Дисперсія в Excel обчислюється дуже просто, набагато швидше і зручніше, ніж робити це вручну, адже функція математичного очікування досить складна і її обчислення може піти багато часу й сил.
Серед безлічі показників, що застосовуються у статистиці, потрібно виділити розрахунок дисперсії. Слід зазначити, що виконання вручну цього обчислення – досить стомлююче заняття. На щастя, в Excel є функції, що дозволяють автоматизувати процедуру розрахунку. З'ясуємо алгоритм роботи із цими інструментами.
Дисперсія – це показник варіації, що є середній квадрат відхилень від математичного очікування. Отже, він висловлює розкид чисел щодо середнього значення. Обчислення дисперсії може проводитися як за генеральною сукупністю, так і вибірковою.
Спосіб 1: розрахунок за генеральною сукупністю
Для розрахунку цього показника в Excel за генеральною сукупністю застосовується функція ДИСП.Г. Синтаксис цього виразу має такий вигляд:
ДИСП.Г(Число1; Число2; ...)
Усього може бути застосовано від 1 до 255 аргументів. Як аргументи можуть виступати, як числові значення, так і посилання на комірки, в яких вони містяться.
Подивимося, як визначити це значення для діапазону з числовими даними.
Спосіб 2: розрахунок за вибіркою
На відміну від обчислення значення по генеральній сукупності, у розрахунку за вибіркою у знаменнику вказується не загальна кількість чисел, а на одне менше. Це робиться з метою корекції похибки. Ексель враховує даний нюанс у спеціальній функції, яка призначена для цього виду обчислення – ДИСП.В. Її синтаксис представлений такою формулою:
ДИСП.В(Число1; Число2; ...)
Кількість аргументів, як і попередньої функції, теж може коливатися від 1 до 255.
Як бачимо, програма Ексель здатна значною мірою полегшити розрахунок дисперсії. Ця статистична величина може бути розрахована додатком як за генеральною сукупністю, так і за вибіркою. При цьому всі дії користувача фактично зводяться тільки до вказівки діапазону чисел, що обробляються, а основну роботу Excel робить сам. Безумовно, це заощадить значну кількість часу користувачів.
Функція стандартне відхилення вже з розряду вищої математики що стосується статистики. У Excel існує кілька варіантів використання Функції стандартного відхилення:
- Функція СТАНДОТКЛОНП.
- Функція СТАНДОТКЛОН.
- Функція СТАНДОТКЛОНПУ
Дані функції у статистиці продажів нам знадобляться виявлення стабільності продажів (аналіз XYZ). Ці дані можна використовувати як для ціноутворення, так і для формування (коригування) асортиментної матриці та інших корисних аналізів продажів, про які я обов'язково розповім у наступних статтях.
Передмова
Давайте подивимося на формули спочатку математичною мовою, а потім (нижче за текстом) докладно розберемо формулу в Excel і як результат застосовується в аналізі статистичних даних продажів.
Отже, Стандартне відхилення – це оцінка середньоквадратичного відхилення випадкової величини xщодо її математичного очікування на основі незміщеної оцінки її дисперсії)))) Не лякайтеся не зрозумілих слів, потерпіть і Ви все зрозумієте!
Опис формули: Середньоквадратичне відхилення вимірюється в одиницях вимірювання випадкової величини і використовується при розрахунку стандартної помилки середнього арифметичного, при побудові довірчих інтервалів, при статистичній перевірці гіпотез, при вимірюванні лінійного взаємозв'язку між випадковими величинами. Визначається як квадратний корінь із дисперсії випадкової величини
Тепер стандартне відхилення – оцінка середньоквадратичного відхилення випадкової величини xщодо її математичного очікування на основі незміщеної оцінки її дисперсії:
Дисперсія;
- i-й елемент вибірки;
Обсяг вибірки;
Середня арифметична вибірка:
Слід зазначити, що обидві оцінки є зміщеними. Загалом незміщену оцінку побудувати неможливо. Проте оцінка з урахуванням оцінки несмещенной дисперсії є заможною.
Правило трьох сигм() - Практично всі значення нормально розподіленої випадкової величини лежать в інтервалі. Суворіше - приблизно з 0,9973 ймовірністю значення нормально розподіленої випадкової величини лежить у зазначеному інтервалі (за умови, що величина істинна, а не отримана в результаті обробки вибірки). Ми ж використовуватимемо округлений інтервал 0,1
Якщо ж справжня величина невідома, слід користуватися не , а s. Таким чином, правило трьох сигм перетворюється на правило трьох s. Саме це правило допоможе нам визначити стабільність продажів, але про це трохи пізніше.
Тепер Функція стандартного відхилення в Excel
Сподіваюся, я не надто Вас завантажив математикою? Можливо комусь дана інформація буде потрібна для реферату або ще якихось цілей. Тепер розжуємо як ці формули працюють в Excel.
Для визначення стабільності продажів нам не потрібно вникати у всі варіанти функцій стандартного відхилення. Ми будемо користуватися лише однією:
Функція СТАНДОТКЛОНП
СТАНДОТКЛОНП(число1;число2;... )
Число1, число2,.- Від 1 до 30 числових аргументів, що відповідають генеральній сукупності.
Тепер розберемо на прикладі:
Давайте створимо книгу та імпровізовану таблицю. Даний приклад Excel ви завантажуєте в кінці статті.
Далі буде!!!
І знову здрастуйте. Ну що!? Видалася вільна хвилина. Давайте продовжимо?
І так стабільність продажів за допомогою Функції СТАНДОТКЛОНП
Для наочності візьмемо кілька імпровізованих товарів:
В аналітиці, будь то прогноз, дослідження або ще те, що пов'язано зі статистикою завжди необхідно брати три періоди. Це може бути тиждень, місяць, квартал чи рік. Можна і навіть найкраще брати якнайбільше періодів, але не менше трьох.
Я спеціально показав перебільшені продажі, де не озброєним оком видно, що продається стабільно, а що ні. Так простіше буде зрозуміти як працюють формули.
І так у нас є продаж, тепер нам потрібно розрахувати середні значення продажів за періодами.
Формула середнього значення СРЗНАЧ(дані періоду) у моєму випадку формула виглядає так =СРЗНАЧ(C6:E6)
Простягаємо формулу по всіх товарах. Це можна зробити, взявшись за правий кут виділеного осередку і протягнути до кінця списку. Або поставити курсор на стовпець з товаром та натиснути такі комбінації клавіш:
Ctrl + Вниз курсор переміститися в коней списку.
Ctrl + Вправо, курсор переміститися у праву частину таблиці. Ще раз праворуч і ми потрапимо на стовпець із формулою.
Тепер затискаємо
Ctrl + Shift та натискаємо вгору. Так ми виділимо область протягування формули.
І комбінація клавіш Ctrl+D протягне функцію там, де нам треба.
Запам'ятайте ці комбінації, вони реально збільшують швидкість роботи в Excel, особливо коли Ви працюєте з великими масивами.
Наступний етап, сама функція стандартного відкланення, як я вже говорив, ми будемо користуватися лише однією СТАНДОТКЛОНП
Прописуємо функцію і значеннях функції ставимо значення продажів кожного періоду. Якщо у Вас продажу в таблиці один за одним можна використовувати діапазон, як у мене у формулі =СТАНДОТКЛОНП(C6:E6) або через точку з комою перераховуємо потрібні осередки =СТАНДОТКЛОНП(C6;D6;E6)
Ось усі розрахунки й готові. Але як зрозуміти, що продається стабільно, а що ні? Просто проставимо умовність XYZ де,
Х – це стабільно
Y - з невеликими відхиленнями
Z – не стабільно
Для цього використовуємо інтервали похибки. якщо коливання відбуваються в межах 10% вважатимемо, що продажі стабільні.
Якщо в межах від 10 до 25 відсотків – це буде Y.
І якщо значення варіації перевищує 25% – це не стабільність.
Щоб правильно задати букви кожному товару, скористаємося формулою ЯКЩО докладніше про . У моїй таблиці дана функція виглядатиме так:
ЯКЩО(H6<0,1;"X";ЕСЛИ(H6<0,25;"Y";"Z"))
Відповідно всі формули простягаємо за всіма найменуваннями.
Постараюся одразу відповісти на запитання, Чому інтервали 10% та 25%?
Насправді, інтервали можуть бути іншими, все залежить від конкретного завдання. Я спеціально показав Вам перебільшені значення продажів, де різниця видно на "око". Вочевидь, що товар 1 продається стабільно, але динаміка показує збільшення продажів. Такий товар даємо спокій.
А ось товар 2, тут уже дистабілізація в наявності. І наші розрахунки показують Z, що говорить нам про не стабільність продажів. Товар 3 та Товар 5 показують стабільні показники, зверніть увагу, Варіація в межах 10%.
Тобто. Товар 5 з показниками 45, 46 та 45 показує варіацію 1%, що є стабільним числовим рядом.
А ось Товар 2 з показниками 10, 50 та 5 показують варіацію у 93%, що є НЕ стабільним числовим рядом.
Після всіх розрахунків, можна поставити фільтр і відфільтрувати стабільність, таким чином, якщо Ваша таблиця становить кілька тисяч найменувань, ви з легкістю виділите які не стабільні у продажах або ж на обіг, які стабільні.
У моїй таблиці не вийшло "Y", я думаю для наочності числового ряду, його потрібно додати. Малюю Товар 6...
Ось бачите, числовий ряд 40, 50 та 30 показує 20% варіації. Начебто великої похибки немає, але все ж таки розкид суттєвий...
І так під підсумком:
10,50,5 – Z не стабільність. Варіація понад 25%
40,50,30 - Y на цей товар можна звернути увагу, та покращити його продажі. Варіація менша за 25%, але більша за 10%
45,46,45 - X це стабільність, з цим товаром поки що нічого робити не треба. Варіація менше 10%
На цьому все! Сподіваюся я все зрозуміло виклав, якщо ні питайте, що не зрозуміло. І буду вдячний Вам за кожен коментар чи то похвала, чи критика. Так я знатиму, що Ви мене читаєте і Вам, що дуже ВАЖЛИВО, цікаво. І відповідно з'являтимуться нові уроки.
