Зведення
У цій статті описано функцію CONFIDENCE в Microsoft Office Excel 2003 і Microsoft Office Excel 2007, що ілюструє, як використовується функція, і порівнює результати функції в Excel 2003 та Excel 2007 з результатами CONFIDENCE в попередніх версіях Excel.
Значення довірчого інтервалу часто неправильно інтерпретується, і ми намагаємося надати пояснення припустимих і неприпустимих тверджень, які можуть бути зроблені після визначення значення CONFIDENCE з ваших даних.
Додаткові відомості
Функція CONFIDENCE(альфа, сигма, n) повертає значення, яке можна використовувати для побудови довірчого інтервалу для середнього значення сукупності. Довірчий інтервал – це діапазон значень, відцентрованих за відомим середнім вибіркою. Припускається, що спостереження в вибірці походять від нормального розподілу з відомим стандартним відхиленням, сигмою, а кількість спостережень у вибірці дорівнює n.
Синтаксис
CONFIDENCE(alpha,sigma,n)
Параметри: Альфа – імовірність і 0 < альфа < 1. Сигма – додатне число, а n – додатне ціле число, яке відповідає розміру вибірки.
Зазвичай альфа – це невелика ймовірність, наприклад 0,05.
Приклад використання
Припустімо, що результати аналітики (IQ) відповідають нормальному розподілу зі стандартним відхиленням 15. Ви перевіряєте IQ для вибірки з 50 учнів у місцевій школі та отримаєте середнє значення вибірки 105. Потрібно обчислити довірчий інтервал 95% для середнього значення сукупності. Довірчий інтервал 95% або 0,95 відповідає альфа = 1 - 0,95 = 0,05.
Щоб проілюструвати функцію CONFIDENCE, створіть пустий аркуш Excel, скопіюйте наведену нижче таблицю, а потім виділіть клітинку A1 на пустому аркуші Excel. У меню Edit (Редагування) виберіть команду Paste (Вставити).
Примітка.: В Excel 2007 на вкладці Основне в групі Буфер обміну натисніть кнопку Вставити.
Записи в таблиці нижче заповнюють клітинки A1:B7 на аркуші.
|
Альфа |
0,05 |
|
stdev |
15 |
|
n |
50 |
|
середнє значення вибірки |
105 |
|
=CONFIDENCE(B1;B2;B3) |
|
|
=NORMSINV(1 – B1/2)*B2/SQRT(B3) |
Вставивши цю таблицю в новий аркуш Excel, натисніть кнопку Параметри вставлення та виберіть пункт Враховувати форматування кінцевого фрагмента.
Виділивши вставлений діапазон, наведіть вказівник миші на пункт Стовпець у меню Формат і виберіть автодобір за шириною виділеного фрагмента.
Примітка.: У програмі Excel 2007 із виділеним діапазоном клітинок натисніть кнопку Формат у групі Клітинки на вкладці Основне та виберіть автодобір ширини стовпця.
Клітинка A6 відображає значення CONFIDENCE. Клітинка A7 відображає те саме значення, тому що виклик confidence(альфа, сигма, n) повертає результат обчислення:
NORMSINV(1 – alpha/2) * sigma / SQRT(n)
У програмі Microsoft Excel 2002 не було внесено жодних змін безпосередньо до функції CONFIDENCE, але в excel 2002 та Excel 2007 було покращено нові вдосконалення. Таким чином, функція CONFIDENCE може повертати різні (і покращені) результати в цих пізніших версіях Excel, оскільки функція CONFIDENCE залежить від ФУНКЦІЇ NORMSINV.
Це не означає, що ви маєте втратити довіру до впевненості в попередніх версіях Excel. Неточності в NORMSINV зазвичай відбувалися для значень аргументу, дуже близьких до 0 або дуже близьких до 1. На практиці альфа зазвичай має значення 0,05, 0,01 або 0,001. Значення альфа мають бути набагато меншими, ніж це, наприклад 0,0000001, перш ніж помилки округлення в NORMSINV, швидше за все, буде помічено.
Примітка.: Обговорення обчислювальних відмінностей в NORMSINV див. в статті про NORMSINV.
Щоб отримати додаткові відомості, клацніть номер статті в базі знань Microsoft Knowledge Base:
826772 Статистичні функції Excel: NORMSINV
Інтерпретація результатів confidence
Файл довідки Excel для CONFIDENCE було перезаписано для Excel 2003 і Excel 2007, оскільки всі попередні версії файлу довідки дали хибні поради щодо інтерпретації результатів. У прикладі говориться: "Припустімо, ми спостерігаємо, що в вибірці 50 пасажирів середня тривалість поїздок на роботу становить 30 хвилин зі стандартним відхиленням населення 2,5. Ми можемо бути впевнені на 95 відсотків, що середнє значення сукупності перебуває в інтервалі 30 +/- 0,692951", де 0,692951 – це значення, повернуте CONFIDENCE(0,05, 2,5, 50).
У цьому ж прикладі йдеться про те, що "середня тривалість подорожі до роботи дорівнює 30 ± 0,692951 хвилин або від 29,3 до 30,7 хвилин". Імовірно, це також твердження про середнє значення сукупності, що падає в межах інтервалу [30 – 0,692951, 30 + 0,692951] з імовірністю 0,95.
Перед проведенням експерименту, який дав дані для цього прикладу, класичний статистик (на відміну від байєзійського статистику) не може робити жодних заяв про розподіл імовірності середнього значення сукупності. Натомість класичний статистик займається тестуванням гіпотез.
Наприклад, класичному статистику може знадобитися провести двобічний тест гіпотези, який базується на наблизі нормального розподілу з відомим стандартним відхиленням (наприклад, 2,5), певним попередньо вибраним значенням середнього значення генеральної сукупності, μ0 і попередньо вибраним рівнем точності (наприклад, 0,05). Результат тесту базуватиметься на значенні середнього значення спостережуваної вибірки (наприклад, 30) і null-гіпотезі про те, що середнє значення генеральної сукупності μ0 буде відхилено на рівні точності 0,05, якщо середнє значення спостережуваної вибірки було занадто далеко від μ0 в будь-якому напрямку. Якщо нульову гіпотезу відхилено, то інтерпретація полягає в тому, що вибірка означає, що далеко або далі від μ0 відбудеться випадково менше 5% часу під припущенням, що μ0 є істинним середнім значенням генеральної сукупності. Після проведення цього тесту класичний статистик все одно не може робити жодної заяви про розподіл ймовірності середнього значення сукупності.
Байєзійський статистик, з іншого боку, почав би з очікуваного розподілу ймовірності для середнього значення сукупності (названий апріорі-розподілом), зібрав експериментальні докази так само, як класичний статистик, і використовував би ці докази для перегляду її або його розподілу ймовірності для середнього значення сукупності і тим самим отримати задній розподіл. У програмі Excel немає статистичних функцій, які б допомогли байєзіанському статистику в цьому починанні. Статистичні функції Excel призначені для класичних статистиків.
Довірчі інтервали пов'язані з тестами гіпотез. З огляду на експериментальні докази, довірчий інтервал робить стислу заяву про значення гіпотеризованого середнього значення сукупності μ0, що дасть змогу прийняти null-гіпотезу про те, що середнє значення сукупності становить μ0, а значення μ0, які дали б відмову від null-гіпотези про те, що середнє значення сукупності дорівнює μ0. Класичний статистик не може зробити будь-яких заяв про ймовірність того, що середнє населення падає в будь-який конкретний інтервал, тому що вона або він ніколи не робить апріорі припущення про цей розподіл ймовірності і такі припущення були б необхідні, якби можна було використовувати експериментальні докази для їх перегляду.
Ознайомтеся зі зв'язком між тестами гіпотез і довірчими інтервалами, використовуючи приклад на початку цього розділу. Зв'язок між CONFIDENCE і NORMSINV зазначено в останньому розділі, ви маєте:
CONFIDENCE(0.05, 2.5, 50) = NORMSINV(1 – 0.05/2) * 2.5 / SQRT(50) = 0.692951
Оскільки середнє значення вибірки становить 30, довірчий інтервал становить 30 +/- 0,692951.
Тепер розглянемо двобічне тест гіпотези з рівнем точності 0,05, як описано раніше, що передбачає нормальний розподіл зі стандартним відхиленням 2,5, розмір вибірки 50 і конкретне гіпотеризоване середнє значення генеральної сукупності, μ0. Якщо це середнє значення сукупності, то середнє значення вибірки буде отримано зі звичайного розподілу із середнім значенням генеральної сукупності μ0 і стандартним відхиленням, 2,5/SQRT(50). Цей розподіл симетричний приблизно на μ0, і ви хочете відхилити null-гіпотезу, якщо ABS(середнє значення вибірки - μ0) > деяке значення відсікання. Значення відсікання було б таким, що якби μ0 було істинним середнім значенням генеральної сукупності, значення середнього вибірки – μ0 вище цього відсікання або значення μ0 – середнє значення вибірки вище, ніж це скорочення, кожне з них відбувається з імовірністю 0,05/2. Це значення відсікання
NORMSINV(1 – 0.05/2) * 2.5/SQRT(50) = CONFIDENCE(0.05, 2.5, 50) = 0. 692951
Тому відхилюйте null-гіпотезу (середнє значення сукупності = μ0), якщо одне з наведених нижче тверджень істинне:
середнє значення вибірки – μ0 > 0.
692951
0 – середнє значення вибірки > 0. 692951
Оскільки середнє значення вибірки = 30 у нашому прикладі, ці дві інструкції стають такими операторами:
30 – μ0 > 0.
692951
μ0 – 30 > 0. 692951
Перезаписуючи їх таким чином, щоб ліворуч відображалися лише μ0, з'являться такі інструкції:
μ0 < 30 – 0.
692951
μ0 > 30 + 0. 692951
Це саме ті значення μ0, які не містять довірчого інтервалу [30 – 0,692951, 30 + 0,692951]. Таким чином, довірчий інтервал [30 – 0,692951, 30 + 0,692951] містить ті значення μ0, де нульова гіпотеза про те, що середнє значення генеральної сукупності μ0 не буде відхилено, враховуючи зразки доказів. Для значень μ0 поза цим інтервалом гіпотеза про те, що середнє значення генеральної сукупності дорівнює μ0, буде відхилено з огляду на вибіркові докази.
Висновки
Неточності в попередніх версіях Excel зазвичай виникають для дуже малих або дуже великих значень p в NORMSINV(p). Confidence оцінюється викликом NORMSINV(p), тому точність NORMSINV є потенційною турботою для користувачів CONFIDENCE. Однак значення p, які використовуються на практиці, навряд чи будуть досить екстремальними, щоб викликати значні помилки округлення в NORMSINV, і продуктивність CONFIDENCE не повинна стосуватися користувачів будь-якої версії Excel.
Більшість з цих статей була зосереджена на інтерпретації результатів CONFIDENCE. Іншими словами, ми запитали: "Яке значення довірчого інтервалу?" Часто неправильно розуміються довірчі інтервали. На жаль, файли довідки Excel у всіх версіях Excel, раніших за Excel 2003, сприяли цьому непорозумінню. Удосконалено файл довідки Excel 2003.
