Стандартне відхилення в Excel

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

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

Стандартне відхилення - що це таке

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

1. Спочатку отримуємо середнє арифметичне.
2. Після цього кожне початкове значення порівнюють із середнім і визначають різницю між ними.
3. Після цього кожна різниця зводиться до другого степеня, після чого отримані результати складаються.
4. Нарешті, останнім кроком є ​​ділення отриманого значення на загальну кількість елементів у даній вибірці.

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

1. Якщо ви отримаєте дисперсію з одного числа, вона завжди дорівнюватиме нулю.
2. Якщо випадкове число помножити на константу А, то дисперсія збільшиться на коефіцієнт А в квадраті. Простіше кажучи, константу можна вилучити зі знака дисперсії та звести у другий ступінь.
3. Якщо константу А додати до довільного числа або відняти від нього, то дисперсія від цього не зміниться.
4. Якщо два випадкових числа, позначені, наприклад, змінними X і Y, не залежать одне від одного, то в цьому випадку для них справедлива формула. D(X+Y) = D(X) + D(Y)
5. Якщо ми внесемо зміни в попередню формулу і спробуємо визначити дисперсію різниці між цими значеннями, то вона також буде сумою цих дисперсій.

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

Різниця між дисперсією та стандартним відхиленням лежить, так би мовити, виключно в площині одиниць. Стандартне відхилення набагато легше читати, оскільки воно показано не в квадратах числа, а безпосередньо в значеннях. Простими словами, якщо в числовій послідовності 1,2,3,4,5 середнє арифметичне дорівнює 3, то, відповідно, стандартним відхиленням буде число 1,58. Це говорить нам про те, що в середньому одне число відхиляється від середнього числа (яке в нашому прикладі становить 1,58) на XNUMX.

Дисперсія буде тим самим числом, тільки в квадраті. У нашому прикладі це трохи менше 2,5. В принципі, для статистичних розрахунків можна використовувати як дисперсію, так і стандартне відхилення, потрібно лише точно знати, з яким показником працює користувач.

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

У нас є два основних варіанти формули. Перший розраховується на вибірковій сукупності. Другий – за словами генерала. Щоб обчислити стандартне відхилення для вибіркової сукупності, потрібно скористатися функцією STDEV.V. Якщо необхідно провести розрахунок для генеральної сукупності, то необхідно використовувати функцію STDEV.G.

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

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

Спосіб 1. Ручне введення формули

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

1. Клацаємо лівою кнопкою миші по клітинці, в якій буде записана формула для отримання стандартного відхилення. Ви також можете ввести його як аргумент будь-якої іншої функції. У цьому випадку вам потрібно клацнути по рядку введення формули, а потім почати вводити аргумент, де повинен відображатися результат.
2. Загальна формула така: =STDEV.Y(число1(адреса_комірки1), число2(адреса_комірки2),…). Якщо використовувати другий варіант, то все робиться точно так само, тільки буква G в назві функції змінюється на B. Максимальна кількість підтримуваних аргументів - 255.
3. Після того, як введення формули завершено, підтверджуємо свої дії. Для цього натисніть клавішу enter.

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

1. Введення формули вручну може заощадити багато часу. Користувач Excel, який пам’ятає формулу та її синтаксис, має значну перевагу перед особою, яка тільки починає працювати та шукає потрібну функцію у списку Майстра функцій або на стрічці. Крім того, саме введення з клавіатури набагато швидше, ніж використання миші.
2. Менше втомлюються очі. Вам не потрібно постійно перемикати фокус зі столу на вікно, потім на інше вікно, потім на клавіатуру, а потім знову на стіл. Це також допомагає істотно заощадити час і сили, які потім можна витратити на обробку реальної інформації, а не на підтримку формул.
3. Введення формул вручну набагато гнучкіше, ніж використання наступних двох методів. Користувач може відразу вказати необхідні комірки діапазону, не вибираючи його безпосередньо, або переглянути всю таблицю відразу, уникаючи ризику, що діалогове вікно заблокує її.
4. Використання формул вручну є своєрідним містком до написання макросів. Звичайно, це не допоможе вам вивчити мову VBA, але формує правильні звички. Якщо людина звикла віддавати команди комп'ютера за допомогою клавіатури, йому буде набагато простіше освоїти будь-яку іншу мову програмування, в тому числі розробляти макроси для електронних таблиць.

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

Спосіб 2. Табл. Формули

Інший спосіб, доступний користувачеві, який хоче отримати стандартне відхилення від діапазону, це використання вкладки «Формули» в головному меню. Розповімо докладніше, що для цього потрібно зробити:

1. Виділяємо клітинку, в яку ми хочемо записати результат.
2. Після цього знаходимо на стрічці вкладку «Формули» і переходимо в неї.
3. Скористаємося блоком «Бібліотека функцій». Є кнопка «Додаткові функції». У списку, який буде, ми знайдемо пункт «Статистика». Після цього ми вибираємо, яку формулу ми будемо використовувати.
4. Після цього з'являється вікно для введення аргументів. У ньому вказуємо всі числа, посилання на комірки або діапазони, які братимуть участь в обчисленнях. Коли ми закінчимо, натисніть кнопку «ОК».

Переваги цього методу:

1. швидкість. Цей спосіб досить швидкий і дозволяє ввести потрібну формулу всього за кілька кліків.
2. Точність. Немає ризику випадково написати неправильну клітинку або написати неправильну букву, а потім витратити час на переробку.

Можна сказати, що це другий найкращий спосіб після ручного введення. АЛЕ третій спосіб теж корисний в деяких ситуаціях.

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

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

Після цього з'явиться список функцій. Ви можете або спробувати знайти його в повному алфавітному списку, або відкрити категорію «Статистика», де також можна знайти цей оператор.

Ми бачимо в списку, що функція СТДЕВ все ще присутній. Це робиться для того, щоб старі файли були сумісними з новою версією Excel. Однак ми настійно рекомендуємо вам використовувати нові функції, перелічені вище, оскільки в якийсь момент ця застаріла функція може більше не підтримуватися.

Після того, як ми натиснемо OK, ми матимемо можливість відкрити вікно аргументів. Кожен аргумент є окремим числом, адресою комірки (якщо вона містить числове значення) або діапазонами значень, які будуть використовуватися для середнього арифметичного та стандартного відхилення. Після того, як ми введемо всі аргументи, натискаємо на кнопку «ОК». Дані будуть введені в комірку, в яку ми ввели формулу.

Висновок

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