Розрахунок коефіцієнта варіації в Microsoft Excel

Зміст

• Обчислення коефіцієнта варіації
  • Крок 1: Розрахунок стандартного відхилення
  • Крок 2: розрахунок середнього арифметичного
  • Крок 3: знаходження коефіцієнта варіації
• Питання та відповіді

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

Обчислення коефіцієнта варіації

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

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

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

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

Синтаксис даних функцій виглядає відповідним чином:


= СТАНДОТКЛОН(Число1; Число2;...)
= СТАНДОТКЛОН.Г (Число1; Число2;...)
= СТАНДОТКЛОН.В (Число1; Число2;...)


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

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

3. Відкривається вікно аргументів даної функції. Воно може мати від 1 до 255 полів, в яких можуть міститися, як конкретні числа, так і посилання на осередки або діапазони. Ставимо курсор в поле " Число1» . Мишею виділяємо на аркуші той діапазон значень, який потрібно обробити. Якщо таких областей кілька і вони не суміжні між собою, то координати наступної вказуємо в поле " Число2» і т. д. коли всі потрібні дані введені, тиснемо на кнопку «OK»

4. У попередньо виділеній комірці відображається підсумок розрахунку обраного виду стандартного відхилення.

Урок: Формула середнього квадратичного відхилення в Excel

Крок 2: розрахунок середнього арифметичного

Середнє арифметичне є відношенням загальної суми всіх значень числового ряду до їх кількості. Для розрахунку цього показника теж існує окрема функція – СРЗНАЧ . Обчислимо її значення на конкретному прикладі.

1. Виділяємо на аркуші осередок для виведення результату. Тиснемо на вже знайому нам кнопку " Вставити функцію» .

2. У статистичній категорії Майстра функцій шукаємо найменування " СРЗНАЧ» . Після його виділення тиснемо на кнопку «OK» .

3. Запускається вікно аргументів СРЗНАЧ . Аргументи повністю ідентичні тим, що і в операторів групи СТАНДОТКЛОН . Тобто, в їх якості можуть виступати як окремі числові величини, так і посилання. Встановлюємо курсор в поле " Число1» . Так само, як і в попередньому випадку, виділяємо на аркуші потрібну нам сукупність осередків. Після того, як їх координати були занесені в поле вікна аргументів, тиснемо на кнопку «OK» .

4. Результат обчислення середнього арифметичного виводиться в ту клітинку, яка була виділена перед відкриттям майстри функцій .

Урок: Як порахувати середнє значення в Excel

Крок 3: знаходження коефіцієнта варіації

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

1. Виділяємо осередок, в яку буде виводитися результат. Перш за все, потрібно врахувати, що коефіцієнт варіації є процентним значенням. У зв'язку з цим слід поміняти формат комірки на відповідний. Це можна зробити після її виділення, перебуваючи у вкладці»Головна" . Кількома по полю формату на стрічці в блоці інструментів " Число» . З розкрився списку варіантів вибираємо »відсоток" . Після цих дій формат у елемента буде відповідний.

2. Знову повертаємося до комірки для виведення результату. Активуємо її подвійним клацанням лівої кнопки миші. Ставимо в ній знак «=» . Виділяємо елемент, в якому розташований підсумок обчислення стандартного відхилення. Кількома по кнопці " розділити» (/) на клавіатурі. Далі виділяємо осередок, в якій розташовується середнє арифметичне заданого числового ряду. Для того, щоб зробити розрахунок і вивести значення, клацаємо по кнопці Enter на клавіатурі.

3. Як бачимо, результат розрахунку виведений на екран.

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

1. Виділяємо попередньо відформатовану під процентний формат осередок, в якій буде виведений результат. Прописуємо в ній формулу за типом:

   = Стандарт.В (діапазон_значений) / СРЗНАЧ(діапазон_значений)

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


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

Існує умовне розмежування. Вважається, що якщо показник коефіцієнта варіації менше 33%, то сукупність чисел однорідна. У зворотному випадку її прийнято характеризувати, як неоднорідну.

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