Стандартне відхилення - Excel і Google Таблиці

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

ОГЛЯД СТАНДАРТНОГО ВІДМОВЛЕННЯ

Функція «СТАНДАРТНЕ ВИКОНАННЯ» Обчислює розрахунок стандартного відхилення для всієї сукупності.

Щоб скористатися функцією STANDARD DEVIATION на робочому аркуші Excel, виберіть клітинку та введіть:

(Зверніть увагу, як виглядають вхідні дані формул)

Функція STANDARD DEVIATION Синтаксис та входи:

1	= STDEV (число1, [число2],…)

цифри- Значення для отримання стандартної дисперсії

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

Щоразу, коли ви маєте справу з даними, вам потрібно буде пройти деякі основні тести, які допоможуть вам це зрозуміти. Зазвичай ви починаєте з обчислення середнього значення, використовуючи функцію Excel AVERAGE <>.

Це дає вам уявлення про те, де знаходиться «середина» даних. І звідти ви захочете подивитися, наскільки розподілені дані навколо цієї середньої точки. Тут приходить стандартне відхилення.

Excel надає ряд функцій для розрахунку стандартного відхилення - STDEV, STDEV.P, STDEV.S та DSTDEV. Ми дійдемо до всіх з них, але спочатку давайте дізнаємося, що таке стандартне відхилення є, точно.

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

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

1	48,49,50,51,52

Середнє значення цього набору даних становить 50 (додайте всі числа та поділіть на n, де n - кількість значень у діапазоні).

Тепер подивіться на наступний набір даних:

1	10,25,50,75,90

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

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

Стандартне відхилення є мірою цього спреду.

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

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

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

1) Обчисліть середнє значення

Візьмемо наш перший набір даних вище: 48,49,50,51,52

Ми вже знаємо середнє значення (50), яке я підтвердив тут за допомогою функції СЕРЕДНЯ Excel <>:

1	= СЕРЕДНЯ (C4: C8)

2) Відніміть середнє значення від кожного значення в наборі даних

Я зробив це за такою формулою:

1	= C4- $ H $ 4

Наше середнє значення в H4, і я "заблокував" посилання на клітинку, поставивши знаки долара перед стовпцем і рядком (натиснувши F4). Це означає, що я можу скопіювати формулу вниз стовпця без оновлення посилання на клітинку.

Результат:

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

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

3) Квадратні відмінності

Ми можемо досягти цього, подолавши відмінності. Отже, давайте додамо новий стовпець і введемо в квадрат цифри у стовпці D:

1

= D4*D4

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

4) Обчисліть дисперсію - середнє значення квадратичних різниць

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

• Якщо ви використовуєте дані про населення, ви просто берете середнє значення (підсумовуйте значення та діліть на n)
• Якщо ви використовуєте вибіркові даніВи берете суму значень і ділите на n-1

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

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

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

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

1	= СЕРЕДНЯ (E4: E8)

Гаразд, у нас є 2. Цей показник відомий як "дисперсія", і він є базовим для багатьох статистичних тестів, включаючи стандартне відхилення. Детальніше про дисперсію можна прочитати на її головній сторінці: як розрахувати дисперсію в Excel <>.

5) Отримайте квадратний корінь дисперсії

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

1	= SQRT (H4)

І ми маємо свій результат: стандартне відхилення 1,414

Оскільки ми ввели коріння нашим раніше квадратним числам, стандартне відхилення вказане в тих самих одиницях, що і вихідні дані. Отже, стандартне відхилення тут становить 1414 контрольних точок.

Стандартне відхилення, коли дані більш розкидані

Раніше у нас був другий приклад діапазону даних: 10,25,50,75,90

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

Усі формули точно такі ж, як і раніше (зверніть увагу, що загальна середня величина все ще становить 50).

Єдине, що змінилося, - це розкид оцінок у стовпці C. Але зараз наше стандартне відхилення набагато вище - 29,832 тестових балів.

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

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

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

Excel має для цього кілька функцій:

• Стор обчислює стандартне відхилення для даних про населення (використовуючи точний метод, який ми використовували у наведеному вище прикладі)
• S обчислює стандартне відхилення для вибіркових даних (використовуючи метод n-1, про який ми говорили раніше)
• СТДЕВ точно так само, як STDEV.S. Це старіша функція, замінена на STDEV.S та STDEV.P.
• СТДЕВА дуже схожий на STDEV.S, за винятком того, що він включає текстові комірки та логічні (TRUE/FALSE) комірки під час обчислення.
• STDEVPA дуже схожий на STDEV.P, за винятком того, що він включає текстові комірки та логічні (TRUE/FALSE) комірки під час обчислення.

Вау, тут багато варіантів! Не лякайтеся - у переважній більшості випадків ви будете використовувати або STDEV.P, або STDEV.S.

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

Функція Excel STDEV.P

STDEV.P обчислює стандартне відхилення для даних про населення. Ви використовуєте його так:

1	= STDEV.P (C4: C8)

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

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

Примітка. STDEV.P ігнорує будь -які комірки, що містять текст або булеві значення (TRUE/FALSE). Якщо вам потрібно включити їх, використовуйте STDEVPA.

Функція Excel STDEV.S

STDEV.S обчислює стандартне відхилення для вибіркових даних. Використовуйте його так:

1	= STDEV.S (C4: C8)

Знову ж таки, потрібен один аргумент - діапазон даних, для якого потрібно знати стандартне відхилення.

Перш ніж перейти до прикладу, давайте обговоримо різницю між STDEV.S та STDEV.P.

Як ми вже обговорювали, STDEV.S слід використовувати для вибіркових даних - коли ваші дані є частиною більшого набору. Тож припустимо тепер, що у нашому прикладі вище тест пройшло більше людей. Ми хочемо оцінити стандартне відхилення кожного, хто пройшов тест, використовуючи лише ці п’ять балів. Тепер ми використовуємо вибіркові дані.

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

Замість того, щоб використовувати звичайний метод - підсумовуємо всі значення та ділимо на n, ми підсумовуємо всі значення та ділимо на n-1:

1	= SUM (E4: E8) / (COUNT (E4: E8) -1)

У цій формулі:

• SUM отримує суму квадратів різниць
• COUNT повертає наше n, з якого ми віднімаємо 1
• Потім ми просто ділимо нашу суму на наш n-1

Цього разу середнє значення квадратичних різниць становить 2,5 (ви можете згадати, що раніше це було 2, тому це трохи вище).

Тож чому ми ділимо на n-1 замість n, коли маємо справу з вибірковими даними?

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

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

Гаразд, тепер у нас є дисперсія для вибірки, тому, щоб отримати стандартне відхилення для вибірки, ми просто отримаємо квадратний корінь дисперсії:

1	= SQRT (H4)

Отримуємо 1,581.

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

1	= STDEV.S (C4: C8)

Так, знову 1,581.

Функція Excel STDEV

Функція Excel STDEV працює точно так само, як STDEV.S - тобто вона обчислює стандартне відхилення для вибірки даних.

Ви використовуєте його так само:

1	= STDEV (C4: C8)

Знову отримуємо той самий результат.

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

Функція Excel STDEVA

STDEVA також використовується для розрахунку стандартного відхилення для вибірки, але вона має кілька важливих відмінностей, про які вам потрібно знати:

• Значення TRUE зараховуються як 1
• Значення FALSE зараховуються як 0
• Текстові рядки зараховуються як 0

Використовуйте його наступним чином:

1	= STDEVA (C4: C8)

Ще чотири друзі та члени родини оцінили результати тестів. Вони наведені у стовпці С, а стовпець D вказує, як STDEVA інтерпретує ці дані.

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

Функція Excel STDEVPA

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

• Значення TRUE зараховуються як 1
• Значення FALSE зараховуються як 0
• Текстові рядки зараховуються як 0

Ви використовуєте його так:

1	= STDEVPA (C4: C12)

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

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

Ви можете зробити це дуже легко за допомогою функцій бази даних Excel: DSTDEV (для зразків) та DSTDEVP (для популяцій).

Ці функції дозволяють вам створити таблицю критеріїв, в якій можна визначити всі необхідні фільтри. Функції застосовують ці фільтри за кадром перед поверненням стандартного відхилення. Таким чином, вам не потрібно торкатися автофільтра або витягувати дані в окремий аркуш - DSTDEV і SDTDEVP можуть зробити все це за вас.

Дізнайтесь більше на головній сторінці функцій Excel DSTDEV та DSTDEVP <>.

Функція стандартного відхилення в Таблицях Google

Стандартна функція відхилення працює точно так само в Google Таблицях, як і в Excel: