Довірчі інтервали в Excel

Як обчислити довірчий інтервал в Excel: CONFIDENCE.NORM для відомого стандартного відхилення і CONFIDENCE.T для невідомого, межі інтервалу через AVERAGE, формули в одну дію.

Довірчий інтервал в Excel

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

Як це працює

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

В Excel є два основні варіанти обчислення: коли дисперсія (стандартне відхилення) генеральної сукупності відома і коли невідома. У першому випадку використовують функцію CONFIDENCE.NORM, у другому — CONFIDENCE.T.

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

Спосіб 1: функція CONFIDENCE.NORM

Функція CONFIDENCE.NORM зі статистичної групи з'явилася в Excel 2010; у ранніших версіях замість неї використовують аналог CONFIDENCE. Вона обчислює довірчий інтервал для середнього генеральної сукупності з нормальним розподілом.

Синтаксис:

=CONFIDENCE.NORM(alpha; standard_dev; size)
  • alpha — рівень значущості, за яким обчислюється рівень довіри. Рівень довіри дорівнює (1 - alpha) * 100 %.
  • standard_dev — стандартне відхилення генеральної сукупності.
  • size — розмір вибірки.

Усі аргументи обов'язкові.

Функція CONFIDENCE має ті самі аргументи й можливості, відрізняється лише назва:

=CONFIDENCE(alpha; standard_dev; size)

Для сумісності її залишено в Excel 2010 і новіших версіях у категорії «Сумісність» (Compatibility), а в Excel 2007 і раніших вона входить до основної групи статистичних функцій.

Межі довірчого інтервалу визначають так: до середнього значення вибірки X додають результат CONFIDENCE.NORM (права межа) і віднімають його (ліва межа).

Розгляньмо приклад. Проведено 12 випробувань, результати занесено в таблицю (діапазон B2:B13) — це наша сукупність. Стандартне відхилення дорівнює 8. Потрібно обчислити довірчий інтервал з рівнем довіри 97 %.

  1. Виділіть клітинку для результату і натисніть кнопку «Вставити функцію» (Insert Function).
  2. У майстрі функцій перейдіть у категорію «Статистичні» (Statistical), виберіть CONFIDENCE.NORM і натисніть «OK».
  3. Відкриється вікно аргументів.

    У полі Alpha вкажіть рівень значущості. Оскільки рівень довіри — 97 %, alpha = (100 − 97) / 100 = 0,03.

    У полі Standard_dev введіть 8.

    У полі Size потрібна кількість випробувань — 12. Щоб не редагувати формулу після кожного нового випробування, задайте це значення функцією COUNT: поставте курсор у поле Size і натисніть трикутник ліворуч від рядка формул. З'явиться список нещодавно використаних функцій; якщо COUNT у ньому немає, виберіть «Інші функції…» (More Functions).

  4. У майстрі функцій знову відкрийте категорію «Статистичні», виберіть COUNT і натисніть «OK».
  5. Відкриється вікно аргументів COUNT. Ця функція рахує кількість клітинок діапазону, що містять числа:
    =COUNT(value1; value2; ...)

    Аргументів може бути до 255, але тут достатньо одного. Поставте курсор у поле Value1, виділіть мишею діапазон із сукупністю і натисніть «OK».

  6. Excel обчислить результат і виведе його у клітинку. Формула має такий вигляд:
    =CONFIDENCE.NORM(0,03; 8; COUNT(B2:B13))
  7. Тепер потрібне середнє значення вибірки — його обчислює функція AVERAGE:
    =AVERAGE(number1; number2; ...)

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

  8. У категорії «Статистичні» виберіть AVERAGE і натисніть «OK».
  9. Поставте курсор у поле Number1, виділіть увесь діапазон значень і натисніть «OK» — Excel виведе середнє значення.
  10. Обчисліть праву межу інтервалу: у вільній клітинці поставте знак =, додайте клітинки з результатами AVERAGE і CONFIDENCE.NORM і натисніть Enter. У прикладі формула така: =F2+A16.
  11. Ліву межу обчисліть так само, але відніміть результат CONFIDENCE.NORM від середнього: =F2-A16.
  12. Усі кроки можна об'єднати в одну формулу. Права межа:
    =AVERAGE(B2:B13)+CONFIDENCE.NORM(0,03; 8; COUNT(B2:B13))
  13. Ліва межа:
    =AVERAGE(B2:B13)-CONFIDENCE.NORM(0,03; 8; COUNT(B2:B13))

Спосіб 2: функція CONFIDENCE.T

Ще одна функція для довірчого інтервалу — CONFIDENCE.T, вона теж з'явилася в Excel 2010. Вона обчислює довірчий інтервал за розподілом Стьюдента і зручна, коли дисперсія, а отже й стандартне відхилення, невідомі. Аргументи ті самі:

=CONFIDENCE.T(alpha; standard_dev; size)

Обчислимо межі довірчого інтервалу з невідомим стандартним відхиленням для тієї самої сукупності з рівнем довіри 97 %.

  1. Виділіть клітинку для результату і натисніть «Вставити функцію».
  2. У категорії «Статистичні» виберіть CONFIDENCE.T і натисніть «OK».
  3. У полі Alpha введіть 0,03 (рівень довіри 97 %).

    Стандартне відхилення цього разу невідоме — його обчислює функція STDEV.S. Поставте курсор у поле Standard_dev, натисніть трикутник ліворуч від рядка формул і, якщо потрібної функції немає в списку, виберіть «Інші функції…».

  4. У категорії «Статистичні» виберіть STDEV.S і натисніть «OK».
  5. Функція STDEV.S визначає стандартне відхилення за вибіркою:
    =STDEV.S(number1; number2; ...)

    Якщо вибірка розміщена одним діапазоном, досить одного аргументу. Поставте курсор у поле Number1 і виділіть сукупність. Не натискайте «OK», інакше результат буде некоректним: спершу поверніться до вікна аргументів CONFIDENCE.T, клацнувши її назву в рядку формул.

  6. У вікні аргументів CONFIDENCE.T поставте курсор у поле Size, натисніть трикутник ліворуч від рядка формул і виберіть COUNT (якщо її немає в списку — так само, як у першому способі).
  7. У вікні аргументів COUNT виділіть сукупність у полі Value1 і натисніть «OK».
  8. Excel обчислить і виведе значення довірчого інтервалу.
  9. Для меж знову потрібне середнє значення вибірки — його обчислюють функцією AVERAGE так само, як у першому способі.
  10. Додавши результати AVERAGE і CONFIDENCE.T, отримаєте праву межу інтервалу.
  11. Віднявши результат CONFIDENCE.T від результату AVERAGE, отримаєте ліву межу.
  12. Однією формулою права межа записується так:
    =AVERAGE(B2:B13)+CONFIDENCE.T(0,03; STDEV.S(B2:B13); COUNT(B2:B13))
  13. Ліва межа:
    =AVERAGE(B2:B13)-CONFIDENCE.T(0,03; STDEV.S(B2:B13); COUNT(B2:B13))

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

Схожі інструкції