Розрахунок коефіцієнта варіації в Microsoft Excel
Одним з основних статистичних показників послідовності чисел є коефіцієнт варіації. Для його знаходження виробляються досить складні розрахунки. Інструменти Microsoft Excel дозволяють значно полегшити їх для користувача.
зміст
Обчислення коефіцієнта варіації
Цей показник являє собою відношення стандартного відхилення до середнього арифметичного. Отриманий результат виражається у відсотках.
У Ексель не існує окремо функції для обчислення цього показника, але є формули для розрахунку стандартного відхилення і середнього арифметичного ряду чисел, а саме вони використовуються для знаходження коефіцієнта варіації.
Крок 1: розрахунок стандартного відхилення
Стандартне відхилення, або, як його називають по-іншому, середньоквадратичне відхилення, являє собою квадратний корінь з дисперсії . Для розрахунку стандартного відхилення використовується функція СТАНДОТКЛОН. Починаючи з версії Excel 2010 вона розділена, в залежності від того, з генеральної сукупності відбувається обчислення або за вибіркою, на два окремих варіанти: СТАНДОТКЛОН.Г і СТАНДОТКЛОН.В.
Синтаксис даних функцій виглядає відповідним чином:
= СТАНДОТКЛОН(Число1;Число2;…)
= СТАНДОТКЛОН.Г(Число1;Число2;…)
= СТАНДОТКЛОН.В(Число1;Число2;…)
- Для того, щоб розрахувати стандартне відхилення, виділяємо будь-яку вільну комірку на аркуші, яка зручна вам для того, щоб виводити в неї результати розрахунків. Клацаємо по кнопці «Вставити функцію». Вона має зовнішній вигляд піктограми і розташована зліва від рядка формул.
- Виконується активація Майстра функцій, який запускається у вигляді окремого вікна з переліком аргументів. Переходимо в категорію «Статистичні» або «Повний алфавітний перелік». Вибираємо найменування «СТАНДОТКЛОН.Г» або «СТАНДОТКЛОН.В», в залежності від того, з генеральної сукупності або по вибірці слід провести розрахунок. Тиснемо на кнопку «OK».
- Відкривається вікно аргументів цієї функції. Воно може мати від 1 до 255 полів, в яких можуть міститися, як конкретні числа, так і посилання на комірки або діапазони. Ставимо курсор в поле «Число1». Мишею виділяємо на аркуші той діапазон значень, який потрібно обробити. Якщо таких областей кілька і вони не суміжні між собою, то координати наступної вказуємо в полі «Число2» і т.д. Коли всі потрібні дані введені, тиснемо на кнопку «OK»
- У попередньо виділеної осередку відображається підсумок розрахунку обраного виду стандартного відхилення.
урок: Формула середнього квадратичного відхилення в Excel
Крок 2: розрахунок середнього арифметичного
Середнє арифметичне є відношенням загальної суми всіх значень числового ряду до їх кількості. Для розрахунку цього показника теж існує окрема функція - СРЗНАЧ. Обчислимо її значення на конкретному прикладі.
- Виділяємо на аркуші осередок для виведення результату. Тиснемо на вже знайому нам кнопку «Вставити функцію».
- У статистичній категорії Майстра функцій шукаємо найменування «СРЗНАЧ». Після його виділення тиснемо на кнопку «OK».
- Запускається вікно аргументів СРЗНАЧ. Аргументи повністю ідентичні тим, що і в операторів групи СТАНДОТКЛОН. Тобто, в їх якості можуть виступати як окремі числові величини, так і посилання. Встановлюємо курсор в поле «Число1». Так само, як і в попередньому випадку, виділяємо на аркуші потрібну нам сукупність осередків. Після того, як їх координати були занесені в поле вікна аргументів, тиснемо на кнопку «OK».
- Результат обчислення середнього арифметичного виводиться в ту клітинку, яка була виділена перед відкриттям Майстра функцій.
урок: Як порахувати середнє значення в Excel
Крок 3: знаходження коефіцієнта варіації
Тепер у нас є всі необхідні дані для того, щоб безпосередньо розрахувати сам коефіцієнт варіації.
- Виділяємо осередок, в яку буде виводитися результат. Перш за все, потрібно врахувати, що коефіцієнт варіації є процентним значенням. У зв'язку з цим слід поміняти формат осередки на відповідний. Це можна зробити після її виділення, перебуваючи у вкладці «Головна». Кількома по полю формату на стрічці в блоці інструментів «Число». З списку, що розкрився варіантів вибираємо «Процентний». Після цих дій формат у елемента буде відповідний.
- Знову повертаємося до осередку для виведення результату. Активуємо її подвійним клацанням лівої кнопки миші. Ставимо в ній знак «=». Виділяємо елемент, в якому розташований результат обчислення стандартного відхилення. Кількома по кнопці «розділити» (/) на клавіатурі. Далі виділяємо клітинку, в якій розташовується середнє арифметичне заданого числового ряду. Для того, щоб зробити розрахунок і вивести значення, клацаємо по кнопці Enter на клавіатурі.
- Як бачимо, результат розрахунку виведений на екран.
Таким чином ми зробили обчислення коефіцієнта варіації, посилаючись на комірки, в яких вже були розраховані стандартне відхилення і середнє арифметичне. Але можна поступити і дещо по-іншому, не розраховуючи окремо дані значення.
- Виділяємо попередньо отформатированную під процентний формат осередок, в якій буде виведений результат. Прописуємо в ній формулу по типу:
= СТАНДОТКЛОН.В(диапазон_значений)/СРЗНАЧ(диапазон_значений)Замість найменування «Діапазон значень» вставляємо реальні координати області, в якій розміщений досліджуваний числовий ряд. Це можна зробити простим виділенням даного діапазону. Замість оператора СТАНДОТКЛОН.В, якщо користувач вважає за потрібне, можна застосовувати функцію СТАНДОТКЛОН.Г.
- Після цього, щоб розрахувати значення і показати результат на екрані монітора, клацаємо по кнопці Enter.
Існує умовне розмежування. Вважається, що якщо показник коефіцієнта варіації менше 33%, то сукупність чисел однорідна. У зворотному випадку її прийнято характеризувати, як неоднорідну.
Як бачимо, програма Ексель дозволяє значно спростити розрахунок такого складного статистичного обчислення, як пошук коефіцієнта варіації. На жаль, в додатку поки не існує функції, яка вираховувала б цей показник в одну дію, але за допомогою операторів СТАНДОТКЛОН і СРЗНАЧ це завдання дуже спрощується. Таким чином, в Excel її може виконати навіть людина, яка не має високого рівня знань пов'язаних зі статистичними закономірностями.