Функція середньоквадратичне відхилення в Excel. Що таке стандартне відхилення - використання функції стандотклон для розрахунку стандартного відхилення в excel

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

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

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

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

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

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

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

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

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

Як бачимо, механізм розрахунку середньоквадратичного відхилення в 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.

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

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

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

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

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

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

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

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

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

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

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

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

Поняття відсоток відхилення має на увазі різницю між двома числовими значеннями у відсотках. Наведемо конкретний приклад: допустимо одного дня з оптового складу було продано 120 штук планшетів, а наступного дня – 150 штук. Різниця в обсягах продажу – очевидна, на 30 штук більше продано планшетів наступного дня. При відніманні від 150 числа 120 отримуємо відхилення, яке дорівнює числу +30. Виникає питання: чим є відсоткове відхилення?

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

Відсоток відхилення обчислюється через віднімання старого значення нового значення, а далі розподіл результату на старе значення. Результат обчислення цієї формули в Excel повинен відображатися у відсотковому форматі комірки. У цьому прикладі формула обчислення виглядає так (150-120)/120=25%. Формулу легко перевірити 120 25% = 150.

Зверніть увагу!Якщо ми старе і нове число поміняємо місцями, то в нас виходить формула для обчислення націнки.

Нижче на малюнку представлений приклад, як описане вище обчислення представити у вигляді формули Excel. Формула в осередку D2 обчислює відсоток відхилення між значеннями продажів для поточного та минулого року: =(C2-B2)/B2

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

Правильно з дужками введіть формулу в комірку D2, а потім просто скопіюйте її в інші порожні комірки діапазону D2: D5. Щоб скопіювати формулу найшвидшим способом, достатньо підвести курсор мишки до маркера курсору клавіатури (до правого нижнього кута) так, щоб курсор мишки змінився зі стрілочки на чорний хрестик. Після чого просто зробіть подвійне клацання лівою кнопкою мишки і Excel сам автоматично заповнить порожні комірки формулою, при цьому сам визначить діапазон D2:D5, який потрібно заповнити до комірки D5 і не більше. Це дуже зручний лайфхак у Excel.



Альтернативна формула для обчислення відсотка відхилення в Excel

В альтернативній формулі, що обчислює відносне відхилення значень продажів з поточного року відразу ділитися на значення продажів минулого року, а потім від результату віднімається одиниця: =C2/B2-1.

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

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

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

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

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

Що це таке

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

Суть цього поняття у тому, щоб виявити ступінь мінливості інструмента, тобто, це, свого роду, індикатор родом з описової статистики. Він виявляє зміни волатильності інструменту у будь-якому часовому проміжку. За допомогою формул СТАНДОТКЛОН можна оцінити стандартне відхилення під час вибірки, при цьому логічні та текстові значення ігноруються.

Формула

Допомагає розрахувати середнє квадратичне відхилення Excel формула, яка автоматично передбачена в програмі Excel. Щоб її знайти, необхідно знайти в Екселі розділ формули, а вже там вибрати ту, що має назву СТАНДОТКЛОН, тому дуже просто.

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

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

Відео на допомогу