Як вважається стандартне відхилення в Excel. Як розрахувати коефіцієнт варіації та інші статистичні величини в Excel

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

Що таке коефіцієнт варіації і навіщо він потрібний?

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

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

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

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

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

Розраховуємо коефіцієнт в Екселі

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

Щоб розрахувати показник варіації в Excel, необхідно згадати шкільний курс математики і розділити стандартне відхилення на середнє значення вибірки. Тобто насправді формула виглядає так - СТАНДОТКЛОН(заданий діапазон даних)/СРЗНАЧ(заданий діапазон даних). Ввести цю формулу необхідно в той осередок Excel, в якому ви хочете отримати потрібний вам розрахунок.

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

1. Перейдіть на вкладку «Головна».
2. Знайдіть у ній категорію «Формат осередків» та виберіть необхідний параметр.

Як варіант, можна задати процентний формат комірці за допомогою кліка правою кнопкою миші на активованій клітинці таблиці. У контекстному меню, що з'явилося, аналогічно вищезазначеному алгоритму потрібно вибрати категорію «Формат осередку» і задати необхідне значення.

Виберіть «Відсотковий», а за необхідності вкажіть кількість десяткових знаків

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

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

Необхідне втручання менеджменту виявлення причин відхилень.

Для побудови контрольної карти використовую вихідні дані, середнє значення (μ) і стандартне відхилення (σ). В 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-файлі

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

Що вимірює стандартне відхилення?

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

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

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

У нашому прикладі ми скористалися функцією Excel СТАНДОТКЛОН, щоб розрахувати показник стандартного відхилення разом із середнім.

У випадку з першим менеджером стандартне відхилення склало 2. Це говорить нам про те, що кожне значення у вибірці в середньому відкланяється на 2 від середнього значення. Чи це добре? Давайте розглянемо питання під іншим кутом - стандартне відхилення рівне 0, говорить нам про те, що кожне значення у вибірці дорівнює його середньому значенню (у нашому випадку, 32,2). Так, стандартне відхилення 2 трохи відрізняється від 0, і вказує на те, що більшість значень знаходяться поруч із середнім значенням. Чим ближче стандартне відхилення до 0, тим надійніше середнє. Більше того, стандартне відхилення близьке до 0 говорить про маленьку варіабельність даних. Тобто, величина стоку зі стандартним відхиленням 2 вказує на неймовірну послідовність першого менеджера.

У випадку з другим магазином стандартне відхилення склало 18,9. Тобто вартість стоку в середньому відхиляється на величину 18,9 від середнього від тижня до тижня. Божевільний розкид! Що далі стандартне відхилення від 0, то менш точно середнє значення. У нашому випадку цифра 18,9 вказує на те, що середньому значенню (32,8 у.о. на тиждень) просто не можна довіряти. Воно також говорить нам про те, що щотижнева величина стоку має велику варіабельність.

Така концепція стандартного відхилення двома словами. Хоча воно не дає уявлення про інші важливі статистичні виміри (Мода, Медіана…), фактично стандартне відхилення відіграє вирішальну роль більшості статистичних розрахунків. Розуміння принципів стандартного відхилення проллє світло на суть багатьох процесів вашої діяльності.

Як розрахувати стандартне відхилення?

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

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

Нижче описано кроки, які робить Excel, щоб прийти до цифри 21,6.

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

Спочатку Excel знаходить середнє значення вибірки. У нашому випадку середнє вийшло рівним 40, яке на наступному кроці віднімають від кожного значення вибірки. Кожну отриману різницю зводять у квадрат і підсумовують. У нас вийшла сума, що дорівнює 2800, яку необхідно розділити на кількість елементів вибірки мінус 1. Так як у нас 7 елементів, виходить необхідно 2800 розділити на 6. З отриманого результату знаходимо квадратний корінь, ця цифра буде стандартним відхиленням.

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

Функції розрахунку стандартного відхилення в Excel

В Excel є кілька різновидів формул стандартного відхилення. Вам достатньо набрати = СТАНДОТКЛОН і ви самі в цьому переконаєтесь.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Розрахунок стандартного відхилення

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

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

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

Цей документ зберігаєте.

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

Вписуєте знак «=» і далі прописуєте формулу. Знак рівності є обов'язковим. Інакше програма не визнає запропоновані дані. Формула вводиться без пропусків.

Утиліта видасть назви кількох формул. Вибираєте « СТАНДОТКЛОН». Це формула обчислення стандартного відхилення. Існує два види розрахунку:

• з обчисленням за вибіркою;
• з обчисленням за генеральною сукупністю.

Вибравши одну з них, вказуєте діапазон даних. Уся введена формула виглядатиме так: «=СТАНДОТКЛОН (В2: В5)».

Потім клацаєте по кнопці « Enter». Отримані дані з'являться у зазначеному пункті.

Розрахунок середнього арифметичного

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

• залишиться тільки виділити діапазоні клацнути по кнопці «Введення». А в осередку тепер відобразиться результат із вище взятих даних.

Розрахунок коефіцієнта варіації

Формула розрахунку коефіцієнта варіації:

V= S/X, де S – це стандартне відхилення, а X – середнє значення.

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

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

Тепер виділяєте осередок, який відвели під число для обчислення варіації. Відкриваєте вкладку « Головна», якщо вона не відкрита. Клацаєте по інструменту « Число». Вибираєте відсотковий формат.

Переходьте до зазначеного осередку і клацаєте по ньому двічі. Потім вводьте знак рівності та виділяєте пункт, куди вписано підсумок стандартного відхилення. Потім клацаєте на клавіатурі за кнопкою «слеш» або «розділити» (виглядає так: «/»). Виділяєте пункт, куди вписано середнє арифметичне, і натискаєте на кнопку «Enter». Повинно вийти так:

А ось і результат після натискання «Enter»:

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

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

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

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

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

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

Співвідношенням розмаху варіації до середнього називається коефіцієнтом осциляції. Готових формул в Екселі немає, тому потрібно компонуватикілька функцій на одну.

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

Дисперсія

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

Змінні набувають таких значень:

У Excel є дві функції, які визначають дисперсію:

Щоб зробити розрахунок, під числами, які необхідно порахувати, виділяється осередок. Заходьте до вкладки функції. Вибираєте категорію « Статистичні». У списку вибираєте одну з функцій і натискаєте на кнопку «Enter».

Максимум та мінімум

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

Щоб вирахувати максимум, виділяєте весь діапазоннеобхідних чисел у таблиці та окремий осередок, потім клацаєте по значку «Σ» або « Автосума». У вікні вибираєте «Максимум» і, натиснувши кнопку «Enter», отримуєте потрібне значення.

Теж робите, щоб отримати мінімум. Тільки вибираєте функцію "Мінімум".