Довірчі інтервали в Excel: значення, визначення, побудова і розрахунок

Розширені функції Excel пропонують незамінні і зручні методи для різних статистичних розрахунків і аналізу. Однією з таких особливостей є інтервал довіри, який використовується для вираження ступеня невизначеності, пов'язаної з дослідженням. Довірчі інтервали в excel — це оцінка подій у поєднанні з верифікацією ймовірностей. Вони забезпечують ймовірний діапазон вибіркової пропорції або вибіркового середнього від істинної частки /середнього, знайденого в популяції і відображаються як: оцінка +/- похибка.

Функція довірчого інтервалу

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


Довірчий інтервал (ДІ) дає розуміння про те, наскільки середня величина може коливатися. Він являє собою діапазон значень, які однаково центровані від відомого середнього числа вибірки. Чим вище рівень довіри (у відсотках), тим менше інтервал, точнішими будуть результати. Дослідження зразків з більшою мінливістю або великим стандартним відхиленням породжує більш широкі довірчі інтервали в excel. Існує співвідношення зворотного квадратного кореня між ДІ і розмірами вибірки. Менші розміри генерують більш широкі ДІ, тому для одержання більш точних оцінок або скорочення граничної похибки наполовину, необхідно приблизно в чотири рази збільшити розмір вибірки.

Побудова середнього значення сукупності

Щоб побудувати довірчий інтервал для середнього значення сукупності, наданої ймовірності, розміру вибірки, потрібно застосувати функцію "CONFIDENCE" в Excel, яка використовує нормальний розподіл для обчислення значення довіри. Припустимо, дослідники випадково вибрали 100 осіб, виміряли їх вага і встановили середній 76 кг. Якщо потрібно дізнатися середній показник для людей конкретному місті, малоймовірно, що він для більш великої групи буде мати таке ж середнє значення, як і вибірка, що складається всього з 100 чоловік. Набагато більш імовірно, що вибіркове середнє 76 кг може бути приблизно дорівнює (невідомому) популяционному середнього, і потрібно знати, наскільки точним є оцінний відповідь. Ця невизначеність, пов'язана з оцінкою інтервалів, називається рівнем достовірності, зазвичай 95%. Функція CONFIDENCE" (альфа, сигма, n) повертає значення, яке використовується для побудови ДІ середнього числа сукупності. Передбачається, що дані вибірок відповідають стандартним нормальним розподілів з відомою сігмою стандартного відхилення, а розмір вибірки дорівнює n. Перед тим як розрахувати довірчий інтервал в excel 95% рівня, приймають альфу як 1 - 095 = 005.

Формати функції CONFIDENCE

Функція CONFIDENCE чи ДОВІРИТЬ, визначається межами довіри — це нижня і верхня межі ДІ і є 95% показниками. Наприклад, при вивченні перевазі, було виявлено, що 70% людей воліють Боржомі , порівняно з Пепсі при ДІ в 3% і рівнем довіри 95%, тоді існує 95-відсоткова ймовірність того, що справжня пропорція складає від 67 до 73%.
Функції "CONFIDENCE" відображаються під різними синтаксисами в різних версіях Excel. Наприклад, Excel 2010 має дві функції: "ДОВІРИТЬ.НОРМ і ДОВІРИТЬ.T", які допомагають обчислити ширину "ДІ. ДОВІРИТЬ.НОРМ" використовується, коли відомо стандартне відхилення вимірювання. В іншому випадку застосовується "ДОВІРИТЬ.T", оцінка здійснюється за даними вибірки. Довірчі інтервали в excel до 2010 року мали тільки функцію "CONFIDENCE". Його аргументи і результати були аналогічними аргументів функції "ДОВІРИТЬ.НОРМ". Перший доступний у більш пізніх версіях Excel для забезпечення сумісності. #NUM! Error — відбувається, якщо альфа менше або дорівнює 0 або більше або дорівнює 0. Дане стандартне відхилення менше або дорівнює 0. Зазначений розмір аргументу менше одиниці. #ВАРТІСТЬ! Error — відбувається, якщо будь-який з наданих аргументів не є числом.

Функція інтерполяції довіри

"ДОВІРИТЬ." класифікується за функціями статистики і буде вираховувати і повертати ДІ для середнього значення. Довірчі інтервали в excel можуть бути надзвичайно корисними для фінансового аналізу. Як аналітик, "ДОВІРИТЬ." допомагає в прогнозуванні та коригування для широкого кола цілей, шляхом оптимізації прийняття фінансових рішень. Це виконується із застосуванням графічного відображення даних у наборі змінних.
Аналітики можуть приймати більш ефективні рішення на основі статистичної інформації, наданої нормальним розподілом. Наприклад, вони можуть знайти зв'язок між отриманим доходом та витратами, витрачає на предмети розкоші. Щоб обчислити для ДІ середнього значення сукупності, що повертається довірче значення, повинно бути додано віднята із середнього значення вибірки. Наприклад, для середнього значення вибірки x: Довірчий інтервал = x ± ДОВІРИТЬ. Приклад розрахунку довірчого інтервалу в excel - припустимо, що нам дані наступні дані: Рівень значущості: 005. Стандартне відхилення населення: 25. Розмір вибірки: 100. Функція довірчого інтервалу Excel використовується для розрахунку ДІ зі значенням 005 (тобто рівень достовірності 95%) для середнього часу вибірки для вивчення часу комутації в офісі на 100 осіб. Середнє значення зразка становить 30 хвилин, а стандартне відхилення складає 25 хвилини. Довірчий інтервал становить 30 ± 048999 що відповідає діапазону 29510009 і 3048999 (хвилин).

Інтервали і нормальне розподіл

Найбільш знайоме використання довірчого інтервалу, означає «похибка помилок». В опитуваннях похибка становить плюс або мінус 3%. ДІ корисні в контекстах, які виходять за рамки цієї простої ситуації. Вони можуть використовуватися з ненормальними розподілами, які сильно спотворені. Для обчислення прогнозу довірчого інтервалу в excel потрібні наступні будівельні блоки:

Середнє значення. Стандартне відхилення спостережень. Кількість опитувань у вибірці. Рівень довіри, який потрібно застосувати до ДІ. Перед тим як побудувати довірчий інтервал в excel, вивчають його навколо середнього значення вибірки, починають з прийняття рішення про те, який буде прийнятий відсоток інших засобів вибірки, якщо вони були зібрані і розраховані в цьому інтервалі. Якщо це так , то 95% можливих зразків будуть захоплені ДІ з 196 стандартних відхилень вище і нижче зразка.

Стандартна помилка середнього

Допустимий інтервал або похибка не приймаються з урахуванням похибки вимірювання або зміщення огляду, тому фактична невизначеність може бути вище, ніж вказана. Перед тим як порахувати довірчий інтервал в excel, розрахунок повинен бути забезпечений хорошим збором даних, надійними вимірювальними системами і задовільним дизайном обстеження. Довірчі інтервали для середнього значення можуть бути отримані декількома способами: за допомогою SigmaXL, описової статистики, гістограм,1-образного t-тесту та інтервалів довіри, односторонніх діаграм ANOVA і Multi-Vari. Щоб графічно ілюструвати ДІ для середнього значення «Задоволеність», створюють діаграму Multi-Vari (з 95% CI Mean Options) з використанням даних Customer Data.xls. Точки відповідають окремим даними. Маркери показують максимальний довірча межа 99%, і середній 95%-ий межа. Тепер тестування гіпотези будуть використовуватися для більш точних середніх оцінок задоволеності і визначення статистична значимість результатів.

Розрахунок з допомогою SigmaXL

Довірчі інтервали дуже важливі для розуміння отриманих даних і прийняття рішень по них. Щоб розрахувати ДІ для дискретної пропорції, використовують SigmaXL> Шаблони і калькулятори> Основні статистичні шаблони> 1 інтервал довірчих відносин. Перед тим як знайти довірчий інтервал в excel, виконують такі дії:
Відкрити Client Data.xls. Натиснути вкладку «Лист 1» або F4 щоб активувати останній робочий аркуш. Натиснути SigmaXL> Статистичні інструменти> Описова статистика. Встановити прапорець «Використовувати всю таблицю даних». Натиснути «Далі». Вибрати «Загальна задоволеність», натиснути «Числові змінні даних» (Y). Вибрати «Тип клієнта», натиснути «Категорія групи» (X1). За замовчуванням рівень довіри 95%. Натиснути «ОК». Звернути увагу, що довірчий інтервал 95% означає: в середньому істинний параметр популяції (середній, або стандартне відхилення пропорція) буде знаходитися в інтервалі 19 разів з 20. Буде представлений користувачам: 95%-ий довірчий інтервал для кожного відліку. Середнє значення (95% CI). Довірчий інтервал 95% стандартного відхилення (95% CI Sigma — не плутати це з рівнем якості Sigma Process).

Статистика і рівні довіри

Довірчий інтервал не є числом, в якому істинне значення параметра знайдено з точністю. Дійсно, випадкова величина теоретично може приймати всі можливі значення в межах законів фізики. Довірчий інтервал — це фактично область, в якій істинне (невідоме значення параметра, що вивчається в популяції, найбільш ймовірно з імовірністю, яку обирають. При його використанні інтервал заснований на обчисленні довірчого порога, похибки і коефіцієнта запасу. Перед тим як визначити довірчий інтервал в excel, визначають ці елементи, які залежать від параметрів: Мінливості вимірюваних характеристик. Розмір вибірки: чим вона більша, тим більш висока точність. Методи відбору проб. Рівень довіри - s. Рівень довіри являє собою гарантовану впевненість. Наприклад, з рівнем достовірності 90%, це означає, що 10% ризик буде неправильним. Як правило, доброю практикою є вибір достовірності 95%. Таким чином, максимальний довірчий рівень є більшим, чим більше розмір вибірки. Маржинальний коефіцієнт є індикатором, виведеним безпосередньо з довірчого порога. В таблиці наведені деякі приклади для найбільш поширених значень.

Рівень довіри s



Коефіцієнт маржі при n> 30



80%



128



85%



144



90%



1645



95%



196



96%



205



98%



233



99%



2575

Індикатори для розрахунку

У випадку коли потрібно оцінити середнє значення популяції з її вибірки, визначають довірчий інтервал. Він залежить від розміру вибірки та закону змінної. Формула для розрахунку довірчого інтервалу в excel виглядає наступним чином: Нижня межа інтервалу = середній пробіг — коефіцієнт поля * стандартну помилку. Верхня межа діапазону = приблизний середній + коефіцієнт поля * стандартну помилку . Значення t буде залежати від розміру вибірки: n> 30: коефіцієнт запасу нормального закону, званий z. n <30: коэффициент запаса , называемый t для n-1. У цій ситуації відповідні одиниці самі є середніми значеннями. Досліднику необхідно буде знати стандартне відхилення не від початкових і індивідуальних спостережень, а від коштів, які розраховуються на основі них. Це відхилення має назву — стандартна помилка середнього.

Смуги помилок

Подання мінливості даних використовуються на графіках, щоб вказати на помилку або невизначеність вимірі. Вони дають загальне уявлення про те, наскільки точним є вимірювання, або, навпаки, наскільки далекі від повідомленого істинного значення і оформляються у вигляді смуг помилок. Вони являють собою одне стандартне відхилення невизначеності, одну стандартну помилку або визначений довірчий інтервал (наприклад, інтервал 95%). Ці величини не співпадають, тому обрана міра повинна бути зазначена у графіку або в тексті. Смуги помилок можуть використовуватися для порівняння двох величин, якщо виконуються статистично значущі умови. Рядки помилок вказують на прийнятність відповідності функції, тобто наскільки добре вона описує дані. Наукові роботи в експериментальних науках, включають в себе помилки на всіх графіках, хоча практика дещо відрізняється і кожен дослідник має власний стиль помилок. Смуги помилок можуть використовуватися як інтерфейс прямої маніпуляції для управління імовірнісними алгоритмами для приблизного обчислення. Смуги помилок можуть бути виражені в знак плюс-мінус(±). Плюс - верхня межа, а мінус - нижня межа помилки.

Калькулятор критичного значення

Для правильного визначення ДІ існують онлайн-калькулятори, які значно спрощують роботу. Починають процес визначення з відбору даних. Він є основою всіх досліджень. Надійна вибірка допомагає впевнено приймати бізнес-рішення. Перше питання, яке потрібно вирішити — правильне визначення цільової групи, він має визначальне значення. Якщо дослідник проводить опитування з людьми поза цієї групи - неможливо успішно виконати завдання. Наступний крок — вирішити, скільки людей потрібно для проведення співбесіди. Фахівці знають, що невелика репрезентативна вибірка буде відображати думки і поведінку групи, з якої вона була складена. Чим більше зразок, тим точніше він представляє цільову групу. Тим не менш швидкість поліпшення точності зменшується по мірі збільшення розміру вибірки. Наприклад, збільшення з 250 до 1000 подвоює точність. Приймають рішення про розмір вибірки на основі таких факторів, як: доступний час, бюджет і необхідний ступінь точності. Існує три фактори, які визначають розмір ДІ для цього рівня достовірності: розмір вибірки; процентна частка вибірки; розмір популяції. Якщо 99% учасників опитування сказали «Так» і 1% сказали «Ні», ймовірність помилки мала, незалежно від розміру вибірки. Проте якщо проценти становлять 51 і 49%, ймовірність помилки набагато вище. Легше бути впевненим в крайніх відповідях, ніж у середніх. При визначенні розміру вибірки, необхідної для заданого рівня точності, потрібно використовувати найгірший відсоток (50%). Нижче показана формула розрахунку довірчого інтервалу в excel розміру вибірки онлайн-калькулятора. Розрахунки довірчого інтервалу припускають, що є справжня випадкова вибірка відповідного населення. Якщо опитування не є випадковим, не можна покладатися на інтервали. Невипадкові вибірки звичайно виникають з-за недоліків у процедурі.

Створення лінійних діаграм

Створення графіка довірчого інтервалу в Excel відносно просте. Спочатку створюють свою лінійну діаграму. Потім з обраним поруч вибирають «Інструменти діаграми»> «Макет»> «Панель помилок»> «Додаткові параметри панелі». У діалоговому спливаючому меню можна вибрати позитивні або негативні панелі помилок, або і те, і інше. Можна вибрати стиль і вибрати суму, яку потрібно відобразити. Це може бути фіксоване значення, відсоток, стандартне відхилення або настроюваний діапазон. Якщо в даних є стандартне відхилення за замовчуванням для кожної точки, вибирають інтерфейс і натискають кнопку «Визначити значення». Потім з'являється інше спливаюче меню можна вибрати діапазон комірок як для позитивних, так і негативних панелей. Порядок будівництва діаграми: Підготувати дані. Спочатку на додаток до середніх значень, знадобиться розрахунок стандартного відхилення (або помилки). Потім у рядку 4 потрібно розрахувати верхня межа групи, тобто для B4 розрахунок буде: =B2+B3 У рядку 5 потрібно розрахувати нижня межа діапазону, тобто Для B5 розрахунок буде: =B2-B4 Створити графік. Виділіть рядки 124 і 5 таблиці, а потім натиснути «Вставити»> «Графік»> «Кругова діаграма». Excel створить лінійну діаграму. Видалити легенду й лінії сітки. Потім клацнути правою кнопкою миші на верхню групу діапазонів і вибрати «Змінити тип діаграми». Відформатувати довірчі діапазони. Щоб закінчити діаграму просто відформатувати верхню серію з блакитним заповненням (згідно з синьою лінією), а нижню серію — білою заливкою. У цій діаграмі легко побачити межі помилок, однак якщо багато даних, вигляд буде безладний. З першого погляду довірча межа набагато більш очевидний, враховуючи середнє значення вибірки, і він буде ставати все більш жорстким по мірі збільшення кількості вибірок