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

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

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

Розрахунок довірчого інтервалу

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

Існує два способи виконання цього завдання в Microsoft Excel:

• Оператор НОРМА ДОВІРИ – використовується у випадках, коли відома дисперсія;
• Оператор ДОВІРЯЙТЕ.СТУДЕНТУколи дисперсія невідома.

Нижче ми крок за кроком розберемо обидва методи на практиці.

Спосіб 1: оператор TRUST.NORM

Ця функція вперше була введена в арсенал програми в редакції Excel 2010 (до цієї версії її замінив оператор «ДОВІРЕНИЙ»). Оператор входить до категорії «статистичні».

Формула функції НОРМА ДОВІРИ виглядає так:

=ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер)

Як бачимо, функція має три аргументи:

• «Альфа» є показником рівня значущості, який береться за основу розрахунку. Рівень довіри розраховується наступним чином:
  • 1-"Альфа". Цей вираз застосовний, якщо значення «Альфа» представлений у вигляді коефіцієнта. Наприклад, 1-0,7 0,3 =, де 0,7=70%/100%.
  • (100-"Альфа")/100. Цей вираз буде застосовано, якщо ми врахуємо рівень довіри зі значенням «Альфа» у відсотках. Наприклад, (100-70) / 100 = 0,3.
• "Стандартне відхилення" – відповідно стандартне відхилення аналізованої вибірки даних.
• «Розмір» це розмір вибірки даних.

Примітка: Для цієї функції обов’язковою умовою є наявність усіх трьох аргументів.

Оператор "ДОВІРЕНИЙ», який використовувався в попередніх версіях програми, містить ті самі аргументи та виконує ті самі функції.

Формула функції ДОВІРЕНИЙ наступним чином:

=ДОВЕРИТ(Альфа;Станд_откл;Размер)

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

Границя довірчого інтервалу визначається за формулою:

X+(-)ДОВЕРИТ.НОРМ

де Х це середнє значення в зазначеному діапазоні.

Тепер подивимося, як застосувати ці формули на практиці. Отже, ми маємо таблицю з різними даними з 10 проведених вимірювань. У цьому випадку стандартне відхилення набору даних становить 8.

Наше завдання — отримати значення довірчого інтервалу з довірчим рівнем 95%.

1. Перш за все, виберіть клітинку для відображення результату. Потім натискаємо на кнопку «Вставити функцію» (зліва від рядка формул).
2. Відкриється вікно Майстра функцій. Клацнувши на поточній категорії функцій, розгорніть список і клацніть на рядку в ньому «Статистичний».
3. У запропонованому списку натисніть на оператора «НОРМА ДОВІРИ», Потім натисніть OK.
4. Побачимо вікно з налаштуваннями аргументів функції, заповнивши які, натискаємо кнопку OK.
   • в польових умовах «Альфа» вказати рівень значущості. Наше завдання передбачає достовірність 95%. Підставивши це значення в розглянуту нами вище формулу розрахунку, отримаємо вираз: (100-95)/100. Записуємо його в поле аргументу (або можна відразу записати результат обчислення рівний 0,05).
   • в польових умовах "std_off" за нашими умовами пишемо цифру 8.
   • у полі «Розмір» вказати кількість елементів, що перевіряються. У нашому випадку було зроблено 10 вимірювань, тому пишемо число 10.
5. Щоб уникнути необхідності переналаштовувати функцію під час зміни даних, її можна автоматизувати. Для цього використовуємо функцію «ПЕРЕВІРИТИ”. Помістіть покажчик в область введення інформації про аргумент «Розмір», потім клацніть піктограму трикутника в лівій частині панелі формул і клацніть елемент «Більше функцій…».
6. У результаті відкриється ще одне вікно Майстра функцій. Вибравши категорію «Статистичний», натисніть на функцію "ПЕРЕВІРКА”, потім добре.
7. На екрані з’явиться ще одне вікно з налаштуваннями аргументів функції, яка використовується для визначення кількості комірок у заданому діапазоні, що містять числові дані.

   Формула функції ПЕРЕВІРКА це написано так: =СЧЁТ(Значение1;Значение2;...).

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

8. У результаті виконаних дій у вибраній комірці буде відображено результат обчислень для оператора НОРМА ДОВІРИ. У нашій задачі його значення виявилося рівним 4,9583603.
9. Але це ще не остаточний результат нашого завдання. Далі потрібно обчислити середнє значення за заданий інтервал. Для цього потрібно скористатися функцією «СЕРЦЕ»A, який виконує завдання обчислення середнього значення за вказаним діапазоном даних.

   Формула оператора записується так: =СРЗНАЧ(число1;число2;...).

   Виділяємо комірку, куди ми плануємо вставити функцію, і натискаємо кнопку «Вставити функцію».

10. У категорії «Статистичний» вибрати нудного оператора «СЕРЦЕ» і натисніть кнопку OK.
11. В аргументах функції в значенні аргументу «Номер» вказати діапазон, який включає всі клітинки зі значеннями всіх вимірювань. Потім клацаємо ГАРАЗД.
12. У результаті виконаних дій буде автоматично розраховано середнє значення, яке відобразиться в комірці з нововставленою функцією.
13. Тепер нам потрібно обчислити межі ДІ (довірчого інтервалу). Почнемо з розрахунку значення правої межі. Вибираємо комірку, в якій хочемо відобразити результат, і виконуємо додавання отриманих результатів за допомогою операторів “СЕРЦЕ» і "НОРМИ ДОВІРИ”. У нашому випадку формула виглядає так: A14+A16. Після введення натисніть Що натомість? Створіть віртуальну версію себе у .
14. В результаті буде виконано розрахунок і результат відразу відобразиться в комірці з формулою.
15. Потім подібним чином виконуємо розрахунок для отримання значення лівої межі КІ. Тільки в цьому випадку значення результату “НОРМИ ДОВІРИ” від результату, отриманого за допомогою оператора «, потрібно не додавати, а відніматиСЕРЦЕ». У нашому випадку формула виглядає так: =A16-A14.
16. Натиснувши Enter, ми отримаємо результат у заданій клітинці з формулою.

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

• Для визначення правої межі КІ загальна формула матиме такий вигляд:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• Так само для лівої межі, тільки замість плюса потрібно поставити мінус:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Спосіб 2: Оператор TRUST.STUDENT

Тепер познайомимося з другим оператором визначення довірчого інтервалу − ДОВІРЯЙТЕ.СТУДЕНТУ. Ця функція була введена в програму порівняно недавно, починаючи з версії Excel 2010, і спрямована на визначення CI обраного набору даних за допомогою розподілу Стьюдента з невідомою дисперсією.

Формула функції ДОВІРЯЙТЕ.СТУДЕНТУ наступним чином:

=ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер)

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

1. Спочатку виділяємо комірку, в якій плануємо вивести результат. Потім натисніть на значок «Вставити функцію» (зліва від рядка формул).
2. Відкриється вже відоме вікно Майстра функцій. Виберіть категорію «Статистичний», потім із запропонованого списку функцій натисніть на оператор «ДОВІРЕНИЙ СТУДЕНТ», потім - OK.
3. У наступному вікні нам потрібно налаштувати аргументи функції:
   • У «Альфа» як і в першому способі, вказуємо значення 0,05 (або «100-95)/100»).
   • Переходимо до аргументації. "std_off". Оскільки згідно з умовами задачі його значення нам невідоме, потрібно провести відповідні обчислення, в яких оператор “STDEV.B”. Натисніть на кнопку додавання функції, а потім на елемент «Більше функцій…».
   • У наступному вікні Майстра функцій виберіть оператор «STDEV.B” в категорії «Статистичний» і натисніть кнопку OK.
   • Потрапляємо у вікно налаштувань аргументів функції, формула якого виглядає так: =СТАНДОТКЛОН.В(число1;число2;...). Як перший аргумент ми вказуємо діапазон, який включає всі комірки в стовпці «Значення» (не враховуючи заголовок).
   • Тепер потрібно повернутися до вікна з аргументами функції “ДОВІРЯЙ.СТУДЕНТУ”. Для цього натисніть на однойменний напис у полі введення формули.
   • Тепер перейдемо до останнього аргументу «Розмір». Як і в першому способі, тут можна або просто вказати діапазон комірок, або вставити оператор «ПЕРЕВІРКА». Вибираємо останній варіант.
   • Після заповнення всіх аргументів натисніть кнопку OK.
4. У виділеній комірці буде відображено значення довірчого інтервалу відповідно до заданих нами параметрів.
5. Далі нам потрібно розрахувати значення меж CI. А для цього потрібно отримати середнє значення для обраного діапазону. Для цього знову застосовуємо функцію «СЕРЦЕ». Алгоритм дій аналогічний описаному в першому способі.
6. Отримавши цінність “СЕРЦЕ», можна приступати до розрахунку меж КІ. Самі формули нічим не відрізняються від тих, що використовуються з «НОРМИ ДОВІРИ”:
   • Права межа ДІ=СЕРЕДНИЙ+УПЕВНЕНІСТЬ СТУДЕНТА
   • Ліва границя ДІ=СЕРЕДНИЙ РІВЕНЬ СТУДЕНТА

Висновок

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