Коефіцієнт варіації – це порівняння розсіювання двох випадково взятих величин. Величини мають одиниці виміру, що призводить до отримання порівняного результату. Цей коефіцієнт необхідний підготовки статистичного аналізу.
За допомогою нього інвестори можуть розрахувати показники ризикуперед тим, як зробити вклади у вибрані активи. Він корисний, коли вибрані активи мають різну прибутковість і ступінь ризику. Наприклад, в одного активу може бути високий дохід і рівень ризику теж високий, а в іншого, навпаки, малий дохід і рівень ризику відповідно менша.
Розрахунок стандартного відхилення
Стандартне відхилення є статистичною величиною. За допомогою розрахунку цієї величини користувач отримає інформацію про те, наскільки дані відхиляються в ту чи іншу сторону щодо середнього значення. Стандартне відхилення в Excel розраховується за кілька кроків.
Підготовляєте дані: відкриваєте сторінку, де відбуватимуться розрахунки У нашому випадку це картинка, але може бути будь-який інший файл. Головне зібрати ту інформацію, яку використовуватимете в таблиці для розрахунку.
Вводьте дані в будь-який табличний редактор (у нашому випадку Excel), заповнюючи комірки зліва направо. Починати слідз колонки "А". Заголовки введіть у рядку зверху, а назви в тих же стовпцях, які стосуються заголовків, лише нижче. Потім дату та дані, що підлягають розрахунку, праворуч від дати. 
Цей документ зберігаєте. 
Тепер переходимо до обчислення. Виділяєте курсором осередокпісля останнього введеного значення знизу. 
Вписуєте знак «=» і далі прописуєте формулу. Знак рівності є обов'язковим. Інакше програма не визнає запропоновані дані. Формула вводиться без пропусків. 
Утиліта видасть назви кількох формул. Вибираєте « СТАНДОТКЛОН». Це формула обчислення стандартного відхилення. Існує два види розрахунку:
- з обчисленням за вибіркою;
- з обчисленням за генеральною сукупністю.
Вибравши одну з них, вказуєте діапазон даних. Уся введена формула виглядатиме так: «=СТАНДОТКЛОН (В2: В5)». 
Потім клацаєте по кнопці « Enter». Отримані дані з'являться у зазначеному пункті. 
Розрахунок середнього арифметичного
Обчислюється, коли користувачеві необхідно створити звіт, наприклад, із заробітної плати у його компанії. Робиться це так:
- залишиться тільки виділити діапазоні клацнути по кнопці «Введення». А в осередку тепер відобразиться результат із вище взятих даних.
Розрахунок коефіцієнта варіації
Формула розрахунку коефіцієнта варіації:
V= S/X, де S – це стандартне відхилення, а X – середнє значення.
Щоб порахувати коефіцієнт варіації в Excel, необхідно знайти стандартне відхилення і середнє арифметичне. Тобто, зробивши перші два розрахунку, які були показані вище, можна перейти до роботи над коефіцієнтом варіації.
Для цього відкриваєте Excel, заповнюємо два поля, куди слід вписати отримані числа стандартного відхилення та середнього значення. 
Тепер виділяєте осередок, який відвели під число для обчислення варіації. Відкриваєте вкладку « Головна», якщо вона не відкрита. Клацаєте по інструменту « Число». Вибираєте відсотковий формат. 
Переходьте до зазначеного осередку і клацаєте по ньому двічі. Потім вводьте знак рівності та виділяєте пункт, куди вписано підсумок стандартного відхилення. Потім клацаєте на клавіатурі за кнопкою «слеш» або «розділити» (виглядає так: «/»). Виділяєте пункт, куди вписано середнє арифметичне, і натискаєте на кнопку «Enter». Повинно вийти так: 
А ось і результат після натискання «Enter»: 
Також для розрахунку коефіцієнта варіації можна використовувати онлайн калькуляторинаприклад planetcalc.ru і allcalc.ru. Достатньо внести необхідні цифри та запустити розрахунок, після чого отримати необхідні відомості.
Середньоквадратичне відхилення
Середньоквадратичне відхилення в Excel вирішується за допомогою двох формул: 
Простими словами, витягується корінь із дисперсії. Як визначити дисперсію розглянуто нижче. 
Середнє квадратичне відхилення є синонімом стандартного та обчислюється точне також. Виділяється осередок для результату під числами, які потрібно розрахувати. Вставляється одна з функцій, вказаних на малюнку вище. Клацається кнопка « Enter». Результат отримано.
Коефіцієнт осциляції
Співвідношенням розмаху варіації до середнього називається коефіцієнтом осциляції. Готових формул в Екселі немає, тому потрібно компонуватикілька функцій на одну. 
Функціями, які необхідно скомпонувати, є формули середнього значення, максимуму та мінімуму. Цей коефіцієнт використовують із порівняння набору даних.
Дисперсія
Дисперсія - це функція, за допомогою якої характеризують розкид данихнавколо математичного очікування. Обчислюється за наступним рівнянням: 
Змінні набувають таких значень: 
У Excel є дві функції, які визначають дисперсію:
Щоб зробити розрахунок, під числами, які необхідно порахувати, виділяється осередок. Заходьте до вкладки функції. Вибираєте категорію « Статистичні». У списку вибираєте одну з функцій і натискаєте на кнопку «Enter».
Максимум та мінімум
Максимум і мінімум потрібні для того, щоб не шукати вручну серед великої кількості чисел мінімальне чи максимальне число.
Щоб вирахувати максимум, виділяєте весь діапазоннеобхідних чисел у таблиці та окремий осередок, потім клацаєте по значку «Σ» або « Автосума». У вікні вибираєте «Максимум» і, натиснувши кнопку «Enter», отримуєте потрібне значення.
Теж робите, щоб отримати мінімум. Тільки вибираєте функцію "Мінімум". 
Необхідне втручання менеджменту виявлення причин відхилень.
Для побудови контрольної карти використовую вихідні дані, середнє значення (μ) і стандартне відхилення (σ). В Excel: μ = СРЗНАЧ($F$3:$F$15), σ = СТАНДОТКЛОН($F$3:$F$15)
Сама контрольна карта включає: вихідні дані, середнє значення (μ), нижній контрольний кордон (μ – 2σ) і верхній контрольний кордон (μ + 2σ):
Завантажити замітку у форматі, приклади у форматі
Подивившись на подану карту, я помітив, що вихідні дані демонструють цілком помітну лінійну тенденцію до зниження частки накладних витрат:
Щоб додати лінію тренду виділіть на графіку ряд даних (у нашому прикладі – зелені точки), клацніть правою кнопкою миші та виберіть опцію «Додати лінію тренда». У вікні «Формат лінії тренду», поекспериментуйте з опціями. Я зупинився на лінійному тренді.
Якщо вихідні дані не розкидані відповідно до довкілля, то описувати їх параметрами μ і σ не цілком коректно. Для опису замість середнього значення краще підійде пряма лінійного тренда та контрольні межі, що рівно віддалені від цієї лінії тренда.
Лінію тренду Excel дозволяє побудувати за допомогою функції ПЕРЕДКАЗ. Нам буде потрібний додатковий ряд А3:А15, щоб відомі значення Хбули безперервним рядом (номери кварталів такий безперервний ряд не утворюють). Замість середнього значення в стовпці Н вводимо функцію ПЕРЕДКАЗА:
Стандартне відхилення σ (функція СТАНДОТКЛОН в Excel) обчислюється за такою формулою:
На жаль, я не знайшов у Excel функції для такого визначення стандартного відхилення (стосовно тренду). Завдання можна розв'язати за допомогою формули масиву. Хто не знайомий із формулами масиву, пропоную спочатку почитати.
Формула масиву може повертати одне значення чи масив. У нашому випадку формула масиву поверне одне значення:
Давайте докладніше вивчимо, як працює формула масиву в осередку G3
СУМ(($F$3:$F$15-$H$3:$H$15)^2) визначає суму квадратів різниць; фактично формула вважає наступну суму = (F3 – H3) 2 + (F4 – H4) 2 + … + (F15 – H15) 2
РАХУНОК($F$3:$F$15) – кількість значень у діапазоні F3:F15
КОРІНЬ(СУМ(($F$3:$F$15-$H$3:$H$15)^2)/(РАХУНОК($F$3:$F$15)-1)) = σ
Значення 6,2% - точка нижньої контрольної межі = 8,3% - 2 σ
Фігурні лапки по обидва боки формули означають, що це формула масиву. Для того, щоб створити формулу масиву, після введення формули в комірку G3:
H4 – 2*КОРІНЬ(СУМ(($F$3:$F$15-$H$3:$H$15)^2)/(РАХУНЧЕННЯ($F$3:$F$15)-1))
необхідно натиснути не Enter, а Ctrl+Shift+Enter. Не намагайтеся ввести фігурні дужки з клавіатури – формула масиву не почне працювати. Якщо потрібно відредагувати формулу масиву, зробіть це так само, як із звичайною формулою, але знову ж таки після закінчення редагування натисніть не Enter, а Ctrl + Shift + Enter.
Формулу масиву, що повертає одне значення, можна "протягувати", як і звичайну формулу.
В результаті отримали контрольну карту, побудовану для даних, що мають тенденцію до зниження
P.S. Після того, як нотатка була написана, я зміг удосконалити формули, які використовуються для обчислення стандартного відхилення даних з тенденцією. Ознайомитись з ними ви можете у Excel-файлі
Нам доводиться стикатися з розрахунком таких значень, як дисперсія, середньоквадратичне відхиленняі, очевидно, коефіцієнт варіації. Саме розрахунку останнього варто приділити особливу увагу. Дуже важливо, щоб кожен новачок, який тільки починає працювати з табличним редактором, міг швидко підрахувати відносну межу розкиду значень.
Що таке коефіцієнт варіації і навіщо він потрібний?
Отже, на мою думку, незайвим буде провести невеликий теоретичний екскурс і розібратися в природі коефіцієнта варіації. Цей показник необхідний відображення діапазону даних щодо середнього значення. Інакше кажучи, він показує ставлення стандартного відхилення до середнього значення. Коефіцієнт варіації прийнято вимірювати у відсотковому вираженні та відображати за його допомогою однорідність часового ряду.
Коефіцієнт варіації стане незамінним помічником у тому випадку, коли вам необхідно буде зробити прогноз за даними із заданої вибірки. Цей індикатор виділить основні ряди значень, які будуть найкориснішими для подальшого прогнозування, а також очистить вибірку від незначних чинників. Так, якщо ви бачите, що значення коефіцієнта дорівнює 0%, то з упевненістю заявляйте про те, що ряд є однорідним, а отже, всі значення у ньому рівні один з одним. У випадку, якщо коефіцієнт варіації набуває значення, що перевищує позначку в 33%, це говорить про те, що ви маєте справу з неоднорідним рядом, в якому окремі значення істотно відрізняються від середнього показника вибірки.
Як знайти середнє квадратичне відхилення?
Оскільки для розрахунку показника варіації в Excel необхідно використовувати середнє квадратичне відхилення, то цілком доречно буде з'ясувати, як нам порахувати цей параметр.
Зі шкільного курсу алгебри ми знаємо, що середнє квадратичне відхилення - це витягнутий з дисперсії квадратний корінь, тобто цей показник визначає ступінь відхилення конкретного показника загальної вибірки від її середнього значення. З його допомогою ми можемо виміряти абсолютну міру коливання ознаки, що вивчається, і чітко її інтерпретувати.
Розраховуємо коефіцієнт в Екселі
На жаль, в Excel не закладено стандартну формулу, яка дозволила б розрахувати показник варіації автоматично. Але це не означає, що вам доведеться робити розрахунки в умі. Відсутність шаблону в «Строк формул» ніяк не применшує здібностей Excel, тому ви цілком зможете змусити програму виконати необхідний вам розрахунок, прописавши відповідну команду вручну.
Щоб розрахувати показник варіації в Excel, необхідно згадати шкільний курсматематики та розділити стандартне відхилення на середнє значення вибірки. Тобто насправді формула виглядає так - СТАНДОТКЛОН(заданий діапазон даних)/СРЗНАЧ(заданий діапазон даних). Ввести цю формулу необхідно в той осередок Excel, в якому ви хочете отримати потрібний вам розрахунок.
Не забувайте і про те, що оскільки коефіцієнт виражається у відсотках, то осередку з формулою потрібно буде задати відповідний формат. Зробити це можна так:
- Перейдіть на вкладку «Головна».
- Знайдіть у ній категорію «Формат осередків» та виберіть необхідний параметр.
Як варіант, можна задати процентний формат комірці за допомогою кліка правою кнопкою миші на активованій клітинці таблиці. У контекстному меню, що з'явилося, аналогічно вищезазначеному алгоритму потрібно вибрати категорію «Формат осередку» і задати необхідне значення.
Виберіть «Відсотковий», а за необхідності вкажіть кількість десяткових знаків
Можливо, комусь вищеописаний алгоритм видасться складним. Насправді ж розрахунок коефіцієнта так само простий, як додавання двох натуральних чисел. Одного разу, виконавши це завдання в Екселі, ви більше ніколи не повернетеся до стомлюючих складних рішень у зошиті.
Ви ще не можете зробити якісне порівняння ступеня розкиду даних? Втрачаєтеся в масштабах вибірки? Тоді прямо зараз беріться за справу та освоюйте на практиці весь теоретичний матеріал, який був викладений вище! Нехай статистичний аналізі розробка прогнозу більше не викликають у вас страху та негативу. Заощаджуйте свої сили та час разом з
Дисперсія - це міра розсіювання, що описує порівняльне відхилення між значеннями даних та середньою величиною. Є найбільш використовуваним заходом розсіювання в статистиці, що обчислюється шляхом підсумовування, зведеного в квадрат, відхилення кожного значення даних від середньої величини. Формула для обчислення дисперсії представлена нижче:
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.
Обчислимо вMSEXCELдисперсію та стандартне відхилення вибірки. Також обчислимо дисперсію випадкової величини, якщо відомий її розподіл.
Спочатку розглянемо дисперсію, потім стандартне відхилення.
Дисперсія вибірки
Дисперсія вибірки (вибіркова дисперсія,samplevariance) характеризує розкид значень у масиві щодо .
Усі 3 формули математично еквівалентні.
З першої формули видно, що дисперсія вибіркице сума квадратів відхилень кожного значення в масиві від середнього, Поділена на розмір вибірки мінус 1.
дисперсії вибіркивикористовується функція ДИСП(), англ. назва 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[(X-E(X)) 2 ]
дисперсіяобчислюється за такою формулою:
де x i – значення, яке може набувати випадкова величина, а μ – середнє значення (), р(x) – ймовірність, що випадкова величина прийме значення х.
Якщо випадкова величина має, то дисперсіяобчислюється за такою формулою:
Розмірність дисперсіївідповідає квадрату одиниці виміру вихідних значень. Наприклад, якщо значення у вибірці є вимірювання ваги деталі (в кг), то розмірність дисперсії буде кг 2 . Це буває складно інтерпретувати, тому для характеристики розкиду значень частіше використовують рівну величину квадратного кореняз дисперсії – стандартне відхилення.
Деякі властивості дисперсії:
Var(Х + a) = Var (Х), де Х - випадкова величина, а - константа.
Var(aХ)=a 2 Var(X)
Var(Х)=E[(X-E(X)) 2 ]=E=E(X 2)-E(2*X*E(X))+(E(X)) 2 =E(X 2)- 2*E(X)*E(X)+(E(X)) 2 =E(X 2)-(E(X)) 2
Ця властивість дисперсії використовується в статті про лінійну регресію.
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) 2 Var(Y)=Var(Х)+Var(Y)=Var(Х+Y). Ця властивість дисперсії використовується для побудови.
Стандартне відхилення вибірки
Стандартне відхилення вибірки- це міра того, наскільки широко розкидані значення у вибірці щодо них.
За визначенням, стандартне відхиленняодно квадратному кореню з дисперсії:
Стандартне відхиленняне враховує величину значень у вибірці, а тільки ступінь розсіювання значень навколо них середнього. Щоб проілюструвати це наведемо приклад.
Обчислимо стандартне відхилення для 2-х вибірок: (1; 5; 9) та (1001; 1005; 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(Вибірка-СРЗНАЧ(Вибірка)))/РАХУНОК(Вибірка), де Вибірка- Посилання на діапазон, що містить масив значень вибірки.
Обчислення функції СРОТКЛ () проводяться за формулою:
