Довірчий інтервал в Excel. 2 способи обчислення довірчого інтервалу в Excel

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

Обчислення довірчого інтервалу за допомогою оператора CONFID.NORM

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

Повна функція виглядає так: =CONFIDENCE.NORM(Альфа,Стандарт,Розмір).

Розглянемо операторну формулу за аргументами (кожен з них повинен фігурувати в обчисленні):

1. «Альфа» вказує на рівень значущості, на якому базується розрахунок.

Розрахувати додатковий рівень можна двома способами:

• 1-(Альфа) – підходить, якщо аргументом є коефіцієнт. Приклад: 1-0,4=0,6 (0,4=40%/100%);
• (100-(Альфа))/100 – формула використовується при розрахунку інтервалу у відсотках. Приклад: (100-40)/100=0,6.

2. Стандартне відхилення - це допустиме відхилення в конкретній вибірці.
3. Розмір – кількість аналізованої інформації

Звернути увагу! Оператор TRUST все ще можна знайти в Excel. Якщо вам потрібно його використовувати, знайдіть його в розділі «Сумісність».

Давайте перевіримо формулу в дії. Вам потрібно створити таблицю з кількома значеннями статистичних розрахунків. Припустимо, що стандартне відхилення дорівнює 7. Мета полягає в тому, щоб визначити інтервал із довірчим рівнем 80%.

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

1. Виберіть порожню комірку та відкрийте «Диспетчер функцій». Він з’явиться на екрані після натискання значка «F (x)» поруч із рядком формул. Також потрапити в меню функцій можна через вкладку «Формули» на панелі інструментів, в лівій її частині знаходиться кнопка «Вставити функцію» з тим же знаком.

2. Виберіть розділ «Статистика» і знайдіть серед елементів списку оператор TRUST.NORM. Потрібно натиснути на нього і натиснути «ОК».

3. Відкриється вікно заповнення аргументів. Перший рядок має містити формулу для обчислення аргументу «Альфа». Відповідно до умови рівень довіри виражається у відсотках, тому використовуємо другу формулу: (100-(Альфа))/100.
4. Стандартне відхилення вже відомо, запишемо його в рядку або виділимо комірку з даними, розміщену на сторінці. Третій рядок містить кількість записів у таблиці – їх 10. Після заповнення всіх полів натисніть «Enter» або «ОК».

Функцію можна автоматизувати, щоб зміна інформації не спричиняла збій розрахунку. Давайте розберемося, як це зробити крок за кроком.

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

2. Диспетчер функцій знову з’явиться. Серед статистичних операторів потрібно знайти функцію «Рахунок» і вибрати її.

Важливо! Аргументами функції COUNT можуть бути числа, клітинки або групи клітинок. У цьому випадку підійде останній. Всього формула може мати не більше 255 аргументів.

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

У комірці з’явиться значення інтервалу. Це число було отримано на прикладі даних: 2,83683532.

Визначення довірчого інтервалу через CONFIDENCE.STUDENT

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

Формула відрізняється від попередньої лише оператором. Це виглядає так: =ДОВІРЯЙ.СТУДЕНТУ(альфа;Ctand_off;розмір).

Використовуємо збережену таблицю для нових розрахунків. Стандартне відхилення в новій задачі стає невідомим аргументом.

1. Відкрийте «Диспетчер функцій» одним із описаних вище способів. У розділі «Статистика» необхідно знайти функцію ДОВІР.СТУДЕНТ, вибрати її та натиснути «ОК».

2. Заповніть аргументи функції. У першому рядку та сама формула: (100-(Альфа))/100.
3. Відхилення невідоме, згідно з умовою задачі. Для його розрахунку використовуємо додаткову формулу. Потрібно клацнути по другому полю у вікні аргументів, відкрити меню функцій і вибрати пункт «Інші функції».

4. Потрібен оператор STDDEV.B (за зразком) у розділі Statistical. Виберіть його та натисніть OK.

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

6. Повернемося до аргументів TRUST.STUDENT, двічі клацнувши на цьому написі в рядку формул. У полі «Розмір» встановіть оператор COUNT, як і минулого разу.

Після натискання «Enter» або «ОК» у комірці з’явиться нове значення довірчого інтервалу. За словами Студента, воно виявилося менше – 0,540168684.

Визначення меж інтервалу з обох сторін

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

1. Відкрийте «Диспетчер функцій» і в розділі «Статистика» виберіть потрібний оператор.

2. Додайте групу комірок, що містять значення, до першого поля аргументу та натисніть кнопку ОК.

3. Тепер ви можете визначити правий і лівий кордони. Це займе трохи простої математики. Розрахунок правої межі: виділити порожню клітинку, додати в неї клітинки з довірчим інтервалом і середнім значенням.

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

5. Такі ж операції виконуємо з довірчим інтервалом Стьюдента. В результаті отримуємо межі інтервалу в двох варіантах.

Висновок

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