Середнє абсолютне відхилення в Excel. Excel

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

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

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

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

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

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

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

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

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

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

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

Середньоквадратичне відхиленнядає можливість оцінити розкид значень, отриманих у результаті виміру якогось параметра. Позначається символом (грецька буква "сигма").

Формула до розрахунку досить проста. Щоб знайти середньоквадратичне відхилення, потрібно взяти квадратне коріння з дисперсії. Тож тепер ви повинні запитати: "А що ж таке дисперсія?"

Що таке дисперсія

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

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

Визначте середнє (просте середнє арифметичне ряду значень).
Потім від кожного зі значень відніміть середнє і зведіть отриману різницю в квадрат (отримали квадрат різниці).
Наступним кроком буде обчислення середнього арифметичного отриманих квадратів різниць (чому саме квадратів ви зможете дізнатися нижче).

Розглянемо з прикладу. Допустимо, ви з друзями вирішили виміряти зростання ваших собак (у міліметрах). В результаті вимірів ви отримали такі дані вимірювань росту (в загривку): 600 мм, 470 мм, 170 мм, 430 мм і 300 мм.

Обчислимо середнє значення, дисперсію та середньоквадратичне відхилення.

Спочатку знайдемо середнє значення. Як ви вже знаєте, для цього потрібно скласти всі виміряні значення та поділити на кількість вимірів. Хід обчислень:

Середня мм.

Отже, середня (середньоарифметична) становить 394 мм.

Тепер потрібно визначити відхилення зростання кожного з собак від середнього:

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

Дисперсія мм 2 .

Таким чином, дисперсія становить 21 704 мм 2 .

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

То як же тепер вирахувати середньоквадратичне відхилення, знаючи дисперсію? Як ми пам'ятаємо, взяти із неї квадратний корінь. Тобто середньоквадратичне відхилення одно:

Мм (округлено до найближчого цілого значення мм).

Застосувавши цей метод, ми з'ясували, деякі собаки (наприклад, ротвейлери) – дуже великі собаки. Але є й дуже маленькі собаки (наприклад, такси, тільки казати їм цього не варто).

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

Тобто за допомогою середньоквадратичного відхилення ми отримуємо “стандартний” метод, який дозволяє дізнатися, яке із значень є нормальним (середньостатистичним), а яке екстраординарно більшим або, навпаки, малим.

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

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

Але якщо дані є вибіркою (значеннями, які обрали із великої генеральної сукупності), тоді обчислення потрібно вести інакше.

Якщо є значень, то:

Решта розрахунків проводяться аналогічно, зокрема і визначення середнього.

Наприклад, якщо наших п'ять собак – лише вибірка з генеральної сукупності собак (всіх собак на планеті), ми маємо ділити на 4, а не на 5,а саме:

Дисперсія вибірки = мм 2 .

При цьому стандартне відхилення щодо вибірки дорівнює мм (округлено до найближчого цілого значення).

Можна сказати, що ми зробили деяку “корекцію” у випадку, коли наші значення є лише невеликою вибіркою.

Примітка. Чому саме квадрати різниць?

Але чому при обчисленні дисперсії ми беремо квадрати різниць? Допустимо при вимірі якогось параметра, ви отримали наступний набір значень: 4; 4; -4; -4. Якщо ми просто складемо абсолютні відхилення від середнього (різниці) між собою. від'ємні значеннявзаємно знищаться з позитивними:

Виходить, цей варіант марний. Тоді, можливо, варто спробувати абсолютні значення відхилень (тобто модулі цих значень)?

На перший погляд виходить непогано (отримана величина, до речі, називається середнім абсолютним відхиленням), але не у всіх випадках. Спробуємо інший приклад. Нехай у результаті виміру вийшов наступний набір значень: 7; 1; -6; -2. Тоді середнє абсолютне відхилення одно:

Оце так! Знов отримали результат 4, хоча різниці мають набагато більший розкид.

А тепер подивимося, що вийде, якщо звести різниці у квадрат (і взяти потім квадратний корінь із їхньої суми).

Для першого прикладу вийде:

Для другого прикладу вийде:

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

Фактично в даному методівикористана та сама ідея, що й під час обчислення відстані між точками, лише застосована іншим способом.

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

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

Поняття відсоток відхилення має на увазі різницю між двома числовими значеннями у відсотках. Наведемо конкретний приклад: допустимо одного дня з оптового складу було продано 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. Щоб її знайти, необхідно знайти в Екселі розділ формули, а вже там вибрати ту, що має назву СТАНДОТКЛОН, так що дуже просто.

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

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