Сводка
В этой статье описывается функция CONFIDENCE в Microsoft Office Excel 2003 и Microsoft Office Excel 2007, показано, как используется функция, а также сравниваются результаты функции для Excel 2003 и Excel 2007 с результатами достоверности в более ранних версиях Excel.
Значение доверительного интервала часто неправильно интерпретируется, и мы стараемся предоставить объяснение допустимых и недопустимых утверждений, которые можно сделать после определения значения ДОСТОВЕРНОСТИ из данных.
Дополнительные сведения
Функция 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. В меню Правка выберите команду Вставить.
Примечание: В Excel 2007 нажмите кнопку Вставить в группе Буфер обмена на вкладке Главная.
Записи в таблице ниже заполняют ячейки A1:B7 на листе.
|
Альфа |
0,05 |
|
Stdev |
15 |
|
м |
50 |
|
Среднее значение образца |
105 |
|
=ДОСТОВЕРНОСТЬ(B1;B2;B3) |
|
|
=НОРМИНВ(1 - B1/2)*B2/SQRT(B3) |
Вставив эту таблицу на новый лист Excel, нажмите кнопку Параметры вставки, а затем выберите Пункт Форматирование назначения.
После того как вставленный диапазон по-прежнему выбран, в меню Формат наведите указатель на пункт Столбец, а затем выберите пункт Автоподбор выбора.
Примечание: В Excel 2007 с выбранным диапазоном ячеек нажмите кнопку Формат в группе Ячейки на вкладке Главная, а затем выберите Пункт Автоподбор ширины столбца.
Ячейка A6 показывает значение CONFIDENCE. Ячейка A7 отображает то же значение, так как вызов CONFIDENCE(альфа, сигма, n) возвращает результат вычислений:
NORMSINV(1 – alpha/2) * sigma / SQRT(n)
Не было внесено никаких изменений непосредственно в CONFIDENCE, но в Microsoft Excel 2002 была улучшена функция НОРМИНВ, а затем были внесены дополнительные улучшения между Excel 2002 и Excel 2007. Таким образом, функция CONFIDENCE может возвращать различные (и улучшенные) результаты в этих более поздних версиях Excel, так как функция CONFIDENCE использует НОРМИНВ.
Это не означает, что для более ранних версий Excel вы должны потерять доверие к ДОВЕРИЮ. Неточности в НОРМИНВ обычно имели место для значений аргумента , очень близких к 0 или очень близких к 1. На практике альфа обычно имеет значение 0,05, 0,01 или, возможно, 0,001. Значения альфа-значения должны быть гораздо меньше, чем, например, 0,0000001, прежде чем ошибки округления в НОРМИНВ, скорее всего, будут замечены.
Примечание: Обсуждение вычислительных различий в НОРМИНВ см. в статье о НОРМИНВ.
Для получения дополнительных сведений щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:
826772 Статистические функции Excel: НОРМИНВ
Интерпретация результатов достоверности
Файл справки 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 в любом направлении. Если гипотеза null отклонена, интерпретация заключается в том, что выборка означает, что далеко или дальше от μ0 произойдет случайно менее 5 % времени при предположении, что μ0 является истинным средним значением по совокупности. После проведения этого теста классический статистик по-прежнему не может сделать никаких утверждений о распределении вероятностей среднего значения совокупности.
Байесовский статистик, с другой стороны, будет начинаться с предполагаемого распределения вероятностей для среднего по совокупности (называется априорным распределением), будет собирать экспериментальные доказательства таким же образом, как классический статистик, и будет использовать это свидетельство для пересмотра своего или его распределения вероятностей для средней совокупности и тем самым получить распределение по задним лучам. Excel не предоставляет статистических функций, которые помогли бы байесовскому статистику в этом начинании. Статистические функции Excel предназначены для классических статистиков.
Доверительный интервал связан с тестами гипотез. Учитывая экспериментальные данные, доверительный интервал делает краткое утверждение о значениях гипотетизированной совокупности среднее μ0, что даст принятие нулевой гипотезы о том, что среднее значение популяции равно μ0, и значения μ0, которые придадут отклонение нулевой гипотезы, что среднее значение популяции равно м0. Классический статистик не может сделать никаких утверждений о вероятности того, что популяция падает в какой-либо конкретный интервал, потому что она или он никогда не делает априорных предположений об этом распределении вероятностей, и такие предположения потребуются, если бы кто-то использовал экспериментальные доказательства для их пересмотра.
Изучите связь между тестами гипотез и доверительными интервалами с помощью примера в начале этого раздела. В связи между ДОСТОВЕРНОСТЬЮ и НОРМИНВ, указанной в последнем разделе, вы можете:
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 - среднее значение выборки выше этого отсечения каждое из них с вероятностью 0,05/2. Это значение отсечения равно
NORMSINV(1 – 0.05/2) * 2.5/SQRT(50) = CONFIDENCE(0.05, 2.5, 50) = 0. 692951
Поэтому отклоните гипотезу NULL (среднее число населения = μ0), если одно из следующих утверждений имеет значение true:
среднее значение выборки — μ0 > 0.
692951
0 — среднее значение выборки > 0. 692951
Так как в нашем примере значение sample среднее = 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, где гипотеза null о том, что среднее значение по совокупности равно м0, не будет отклонена с учетом образца доказательства. Для значений μ0 за пределами этого интервала гипотеза null о том, что среднее значение генеральной совокупности равно μ0, будет отклонена с учетом образца доказательства.
Выводы
Неточности в более ранних версиях Excel обычно встречаются для очень малых или очень больших значений p в НОРМИНВ(p). Достоверность оценивается путем вызова НОРМИНВ(p), поэтому точность НОРМИНВ является потенциальной проблемой для пользователей CONFIDENCE. Однако значения p, которые используются на практике, скорее всего, не будут достаточно экстремальными, чтобы вызвать значительные ошибки округления в НОРМИНВ, и производительность CONFIDENCE не должна беспокоить пользователей любой версии Excel.
Большая часть этой статьи сосредоточена на интерпретации результатов ДОСТОВЕРНОСТИ. Другими словами, мы спросили: "Каково значение доверительного интервала?" Доверительный интервал часто неправильно понимается. К сожалению, файлы справки Excel во всех версиях Excel, предшествующих Excel 2003, способствовали этому недоразумению. Файл справки Excel 2003 был улучшен.
