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

Зміст

• Процедура обчислення
  • Спосіб 1: функція довірить.НОРМА
  • Спосіб 2: функція довірить.СТЮДЕНТ
• Питання та відповіді

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

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

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

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

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

Спосіб 1: функція довірить.НОРМА

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

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

=довірить.НОРМ (альфа; стандартное_откл;розмір)

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

(1 - "Альфа") * 100

" стандартне відхилення» &8212; це аргумент, суть якого зрозуміла з найменування. Це стандартне відхилення запропонованої вибірки.

»Розмір" &8212; аргумент, що визначає величину вибірки.

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

Функція довірить має точно такі ж аргументи та можливості, як і попередній. Її синтаксис такий:

=довірить (альфа; стандартне_откл;розмір)

Як бачимо, відмінності тільки в найменуванні оператора. Зазначена функція з метою сумісності залишена в 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;...)

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

   Встановлюємо курсор в поле " Число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 дозволяють істотно полегшити обчислення довірчого інтервалу і його меж. Для цих цілей використовуються окремі оператори для вибірок, у яких дисперсія відома і невідома.