Обчислення довірчого інтервалу в Microsoft Excel

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

Читайте також: Статистичні функції в Ексель

процедура обчислення

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

У Ексель існують два основні варіанти зробити обчислення за допомогою даного методу: коли дисперсія відома, і коли вона невідома. У першому випадку для обчислень застосовується функція ДОВЕРІТ.НОРМ, а в другому - ДОВЕРІТ.СТЮДЕНТ.

Спосіб 1: функція ДОВЕРІТ.НОРМ

Оператор ДОВЕРІТ.НОРМ, що відноситься до статистичної групі функцій, вперше з'явився в Excel 2010. У більш ранніх версіях цієї програми використовується його аналог ДОВЕРИТ. Завданням цього оператора є розрахунок довірчого інтервалу з нормальним розподілом для середньої генеральної сукупності.

Його синтаксис виглядає наступним чином:

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

«Альфа» - аргумент, який вказує на рівень значущості, який застосовується для розрахунку довірчого рівня. Довірчий рівень дорівнює наступного виразу:

(1-«Альфа»)*100

«Стандартне відхилення» - це аргумент, суть якого зрозуміла з назви. Це стандартне відхилення запропонованої вибірки.

«Розмір» - аргумент, який визначає величину вибірки.

Всі аргументи даного оператора є обов'язковими.

Функція ДОВЕРИТ має точно такі ж аргументи і можливості, що і попередня. Її синтаксис такий:

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

Як бачимо, відмінності тільки в найменуванні оператора. Зазначена функція в цілях сумісності залишена в Excel 2010 і в більш нових версіях в спеціальній категорії «Сумісність». У версіях ж Excel 2007 і раніше вона присутня в основній групі статистичних операторів.

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

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

Де X - це середнє вибіркове значення, яке розташоване посередині обраного діапазону.

Тепер давайте розглянемо, як розрахувати довірчий інтервал на конкретному прикладі. Було проведено 12 випробувань, внаслідок яких були отримані різні результати, занесені в таблицю. Це і є наша сукупність. Стандартне відхилення дорівнює 8. Нам потрібно розрахувати довірчий інтервал при рівні довіри 97%.

1. Виділяємо осередок, куди буде виводитися результат обробки даних. Клацаємо по кнопці «Вставити функцію».
2. З'являється Майстер функцій. Переходимо в категорію «Статистичні» і виділяємо найменування «ДОВЕРІТ.НОРМ». Після цього клацають по кнопці «OK».
3. Відкривається віконце аргументів. Його поля закономірно відповідають найменуванням аргументів.
   Встановлюємо курсор в перше поле - «Альфа». Тут нам слід вказати рівень значимості. Як ми пам'ятаємо, рівень довіри у нас дорівнює 97%. У той же час ми говорили, що він розраховується таким шляхом:

   (1-«Альфа»)*100

   Значить, щоб порахувати рівень значущості, тобто, визначити значення «Альфа» слід застосувати формулу такого виду:

   (1-уровень доверия)/100

   Тобто, підставивши значення, отримуємо:

   (1-97)/100

   Шляхом нехитрих розрахунків дізнаємося, що аргумент «Альфа» дорівнює 0,03. Вводимо дане значення в поле.

   Як відомо, за умовою стандартне відхилення дорівнює 8. Тому в поле «Стандартне відхилення» просто записуємо це число.

   В поле «Розмір» потрібно ввести кількість елементів проведених випробувань. Як ми пам'ятаємо, їх 12. Але щоб автоматизувати формулу і не редагувати її кожен раз при проведенні нового випробування, давайте задамо дане значення не звичайним числом, а за допомогою оператора РАХУНОК. Отже, встановлюємо курсор в поле «Розмір», а потім натискаємо по трикутнику, який розміщений зліва від рядка формул.

   З'являється список недавно застосовуваних функцій. Якщо оператор РАХУНОК застосовувався вами недавно, то він повинен бути в цьому списку. В такому випадку, потрібно просто клікнути по його найменуванню. У зворотному ж випадку, якщо ви його не знайдете, то переходите за пунктом «Інші функції ...».

4. З'являється вже знайомий нам Майстер функцій. Знову подорожуємо у групу «Статистичні». Виділяємо там найменування «РАХУНОК». Клацають по кнопці «OK».
5. З'являється вікно аргументів вищевказаного оператора. Ця функція призначена для того, щоб обчислювати кількість осередків в зазначеному діапазоні, які містять числові значення. Синтаксис її наступний:

   =СЧЁТ(значение1;значение2;…)

   Група аргументів «Значення» є посилання на діапазон, в якому потрібно розрахувати кількість заповнених числовими даними осередків. Всього може налічуватися до 255 подібних аргументів, але в нашому випадку знадобиться лише один.

   Встановлюємо курсор в поле «значення1» і, затиснувши ліву кнопку миші, виділяємо на аркуші діапазон, який містить нашу сукупність. Потім його адресу буде відображено в полі. Клацають по кнопці «OK».

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

   =ДОВЕРИТ.НОРМ(0,03;8;СЧЁТ(B2:B13))

   Загальний результат обчислень склав 5,011609.

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

   Даний оператор призначений для розрахунку середнього арифметичного значення обраного діапазону чисел. Він має такий досить простий синтаксис:

   =СРЗНАЧ(число1;число2;…)

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

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

8. Відкривається Майстер функцій. Знову переходимо в категорію «Статистичні» і вибираємо зі списку найменування «СРЗНАЧ». Як завжди, клацають по кнопці «OK».
9. Запускається вікно аргументів. Встановлюємо курсор в поле «Число1» і з затиснутою лівою кнопкою миші виділяємо весь діапазон значень. Після того, як координати відобразилися в поле, клацають по кнопці «OK».
10. Після цього СРЗНАЧ виводить результат розрахунку в елемент листа.
11. Виконуємо розрахунок правої межі довірчого інтервалу. Для цього виділяємо окрему клітинку, ставимо знак «=» і складаємо вміст елементів листа, в яких розташовані результати обчислень функцій СРЗНАЧ і ДОВЕРІТ.НОРМ. Для того, щоб виконати розрахунок, тиснемо на клавішу Enter. У нашому випадку вийшла наступна формула:

    =F2+A16

    Результат обчислення: 6,953276

12. Таким же чином виробляємо обчислення лівої межі довірчого інтервалу, тільки на цей раз від результату обчислення СРЗНАЧ віднімаємо результат обчислення оператора ДОВЕРІТ.НОРМ. Виходить формула для нашого прикладу наступного типу:

    =F2-A16

    Результат обчислення: -3,06994

13. Ми спробували докладно описати всі дії по обчисленню довірчого інтервалу, тому детально розписали кожну формулу. Але можна все дії з'єднати в одній формулі. Обчислення правої межі довірчого інтервалу можна записати так:

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

14. Аналогічне обчислення лівої межі буде виглядати так:

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

Спосіб 2: функція ДОВЕРІТ.СТЮДЕНТ

Крім того, в Ексель є ще одна функція, яка пов'язана з обчисленням довірчого інтервалу - ДОВЕРІТ.СТЮДЕНТ. Вона з'явилася, тільки починаючи з Excel 2010. Даний оператор виконує обчислення довірчого інтервалу генеральної сукупності з використанням розподілу Стьюдента. Його дуже зручно використовувати в тому випадку, коли дисперсія і, відповідно, стандартне відхилення невідомі. Синтаксис оператора такий:

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

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

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

1. Виділяємо осередок, в яку буде проводитися розрахунок. Клацають по кнопці «Вставити функцію».
2. У відкритому Майстрі функцій переходимо в категорію «Статистичні». Вибираємо найменування «ДОВЕРІТ.СТЮДЕНТ». Клацають по кнопці «OK».
3. Виробляється запуск вікна аргументів зазначеного оператора.

   В поле «Альфа», враховуючи, що рівень довіри становить 97%, записуємо число 0,03. Другий раз на принципах розрахунку даного параметра зупинятися не будемо.

   Після цього встановлюємо курсор в поле «Стандартне відхилення». На цей раз даний показник нам невідомий і його потрібно розрахувати. Робиться це за допомогою спеціальної функції - СТАНДОТКЛОН.В. Щоб викликати вікно даного оператора, натискаємо по трикутнику зліва від рядка формул. Якщо в списку не знаходимо потрібного найменування, то переходимо по пункту «Інші функції ...».

4. Запускається Майстер функцій. Переміщаємося в категорію «Статистичні» і відзначаємо в ній найменування «СТАНДОТКЛОН.В». Потім клацають по кнопці «OK».
5. Відкривається вікно аргументів. Завданням оператора СТАНДОТКЛОН.В є визначення стандартного відхилення при вибірці. Його синтаксис виглядає так:

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

   Неважко здогадатися, що аргумент «Число» - це адреса елемента вибірки. Якщо вибірка розміщена єдиним масивом, то можна, використавши тільки один аргумент, дати посилання на даний діапазон.

   Встановлюємо курсор в поле «Число1» і, як завжди, затиснувши ліву кнопку миші, виділяємо сукупність. Після того, як координати потрапили в поле, чи не поспішаємо тиснути на кнопку «OK», так як результат вийде некоректним. Перш нам потрібно повернутися до вікна аргументів оператора ДОВЕРІТ.СТЮДЕНТ, щоб внести останній аргумент. Для цього натискаємо щодо відповідного найменування в рядку формул.

6. Знову відкривається вікно аргументів вже знайомої функції. Встановлюємо курсор в поле «Розмір». Знову тиснемо на вже знайомий нам трикутник для переходу до вибору операторів. Як ви зрозуміли, нам потрібно найменування «РАХУНОК». Так як ми використовували цю функцію при обчисленнях в попередньому способі, в даному списку вона присутня, так що просто клацаємо по ній. Якщо ж ви її не знайдете, то дійте за алгоритмом, описаним в першому способі.
7. Потрапивши в вікно аргументів РАХУНОК, ставимо курсор в полі «Число1» і з затиснутою кнопкою миші виділяємо сукупність. Потім клацають по кнопці «OK».
8. Після цього програма виробляє розрахунок і виводить значення довірчого інтервалу.
9. Для визначення меж нам знову потрібно буде розрахувати середнє значення вибірки. Але, з огляду на те, що алгоритм розрахунку за допомогою формули СРЗНАЧ той же, що і в попередньому способі, і навіть результат не змінився, не будемо на цьому детально зупинятися вдруге.
10. Склавши результати обчислення СРЗНАЧ і ДОВЕРІТ.СТЮДЕНТ, отримуємо праву межу довірчого інтервалу.
11. Віднявши від результатів розрахунку оператора СРЗНАЧ результат розрахунку ДОВЕРІТ.СТЮДЕНТ, маємо ліву межу довірчого інтервалу.
12. Якщо розрахунок записати однією формулою, то обчислення правої межі в нашому випадку буде виглядати так:

    =СРЗНАЧ(B2:B13)+ДОВЕРИТ.СТЬЮДЕНТ(0,03;СТАНДОТКЛОН.В(B2:B13);СЧЁТ(B2:B13))

13. Відповідно, формула розрахунку лівої межі буде виглядати так:

    =СРЗНАЧ(B2:B13)-ДОВЕРИТ.СТЬЮДЕНТ(0,03;СТАНДОТКЛОН.В(B2:B13);СЧЁТ(B2:B13))

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

• ← Об'єднання рядків в Microsoft Excel
• Конвертація HTML у формати Microsoft Excel →