Как построить доверительный интервал в excel

Функция ДОВЕРИТ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование в Microsoft Excel.

Описание

Возвращает доверительный интервал для среднего генеральной совокупности с нормальным распределением.

Доверительный интервал — это диапазон значений. Выборка «x» находится в центре этого диапазона, а диапазон — x ± ДОВЕРИТ. Например, если x — это пример времени доставки продуктов, заказаных по почте, то x ± ДОВЕРИТ — это диапазон средств численности населения. Для любого средней численности населения (μ0) в этом диапазоне вероятность получения выборки от μ0 больше, чем x, больше, чем альфа; для любого средней численности населения (μ0, не в этом диапазоне), вероятность получения выборки от μ0 больше, чем x, меньше, чем альфа. Другими словами, предположим, что для построения двунамерного теста на уровне значимости альфа гипотезы о том, что это μ0, используются значения x, standard_dev и размер. Тогда мы не отклонить эту гипотезу, если μ0 находится через доверительный интервал, и отклонить эту гипотезу, если μ0 не находится в доверительный интервал. Доверительный интервал не позволяет нам сделать вывод о том, что вероятность 1 — альфа, что следующий пакет займет время доставки через доверительный интервал.

Важно: Эта функция была заменена одной или несколькими новыми функциями, которые обеспечивают более высокую точность и имеют имена, лучше отражающие их назначение. Хотя эта функция все еще используется для обеспечения обратной совместимости, она может стать недоступной в последующих версиях Excel, поэтому мы рекомендуем использовать новые функции.

Чтобы узнать больше о новых функциях, см. в разделах Функция ДОВЕРИТ.НОРМ и Функция ДОВЕРИТ.СТЬЮДЕНТ.

Синтаксис

Аргументы функции ДОВЕРИТ описаны ниже.

Альфа — обязательный аргумент. Уровень значимости, используемый для вычисления доверительного уровня. Доверительный уровень равен 100*(1 — альфа) процентам или, иными словами, значение аргумента «альфа», равное 0,05, означает 95-процентный доверительный уровень.
Стандартное_откл — обязательный аргумент. Стандартное отклонение генеральной совокупности для диапазона данных, предполагается известным.
Размер — обязательный аргумент. Размер выборки.

Замечания

Если какой-либо из аргументов не является числом, возвращается #VALUE! значение ошибки #ЗНАЧ!.
Если альфа ≤ 0 или ≥ 1, доверит возвращает #NUM! значение ошибки #ЗНАЧ!.
Если Standard_dev ≤ 0, возвращается #NUM! значение ошибки #ЗНАЧ!.
Если значение аргумента «размер» не является целым числом, оно усекается.
Если размер < 1, доверит возвращает #NUM! значение ошибки #ЗНАЧ!.
Если предположить, что альфа = 0,05, то нужно вычислить область под стандартной нормальной кривой, которая равна (1 — альфа), или 95 процентам. Это значение равно ± 1,96. Следовательно, доверительный интервал определяется по формуле:

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Доверительный интервал для оценки дисперсии в EXCEL

Построение доверительного интервала для оценки среднего приведено в статье Доверительный интервал для оценки среднего (дисперсия известна) в MS EXCEL . Процедура построения доверительного интервала для оценки дисперсии имеет много общего с процедурой для оценки среднего , поэтому в этой статье она изложена менее подробно, чем в указанной статье.

Формулировка задачи. Предположим, что из генеральной совокупности имеющей нормальное распределение с неизвестным средним значением μ и неизвестной дисперсией σ 2 взята выборка размера n. Необходимо на основании этой выборки оценить дисперсию распределения и построить доверительный интервал .

Примечание : Построение доверительного интервала для оценки среднего относительно нечувствительно к отклонению генеральной совокупности от нормального закона . А вот при построении доверительного интервала для оценки дисперсии требование нормальности является строгим.

СОВЕТ : Для построения Доверительного интервала нам потребуется знание следующих понятий:

дисперсия и стандартное отклонение ,
доверительный интервал для оценки среднего ,
выборочное распределение статистики ,
уровень доверия/ уровень значимости ,
нормальное распределение и распределение ХИ-квадрат .

В качестве точечной оценкой дисперсии распределения, из которого взята выборка , используют Дисперсию выборки s 2 .

Также, перед процедурой проверки гипотезы , исследователь устанавливает требуемый уровень значимости – это допустимая для данной задачи ошибка первого рода , т.е. вероятность отклонить нулевую гипотезу , когда она верна ( уровень значимости обозначают буквой α (альфа) и чаще всего выбирают равным 0,1; 0,05 или 0,01)

В статье про ХИ2-распределение показано, что выборочное распределение статистики y=(n-1) s 2 /σ 2 , имеет ХИ2-распределение с n-1 степенью свободы.

Воспользуемся этим свойством и построим двухсторонний доверительный интервал для оценки дисперсии :

где χ 2 _α/2,n-1 – верхний α/2-квантиль распределения ХИ-квадрат с n -1 степенью свободы ( такое значение случайной величины χ 2 _n-1 , что P ( χ 2 _n-1 >= χ 2 _α/2,n-1 )=α/2) . Чтобы найти этот квантиль в MS EXCEL используйте формулу =ХИ2.ОБР.ПХ(α; n-1) . χ 2 _1-α/2,n-1 – верхний 1-α/2-квантиль , который равен нижнему α/2- квантилю. Чтобы найти этот квантиль в MS EXCEL используйте формулу =ХИ2.ОБР(α; n-1) .

Примечание : В файле примера на листе Квантили показан расчет квантилей для распределения ХИ2 . На рисунке выделена область соответствующая уровню доверия 95%, которая ограничена верхним и нижним квантилем . Обратите внимание, что в отличие от нормального и t-распределения распределение ХИ2 несимметрично, поэтому для двустороннего доверительного интервала потребуется вычислить два квантиля , значения которых будут отличаться.

Примечание : Доверительный интервал для стандартного отклонения может быть получен путем извлечения квадратного корня из вышеуказанного выражения.

В файле примера на листе 2х сторонний создана форма для расчета и построения двухстороннего доверительного интервала .

Для построения односторонних доверительных интервалов используйте нижеследующие выражения:

Задача

В следующей задаче найдем верхний односторонний доверительный интервал для дисперсии .

Автоматический аппарат заполняет емкости с растворителем. Предполагается, что объемы налитой жидкости в емкостях распределены по нормальному закону . Если разброс значений объемов будет слишком велик, то значительная часть емкостей будет существенно переполнена или не заполнена. Для оценки дисперсии в качестве выборки взято 20 наполненных жидкостью емкостей. На основе выборки была вычислена дисперсия выборки s 2 , которая составила 0,0153 (литров 2 ). Принято решение оценить верхний уровень дисперсии с уровнем доверия 95%.

Для решения задачи воспользуемся выражением

Сначала найдем верхний (1-α)-квантиль (или равный ему нижний α-квантиль ) ХИ2-распределения с n-1 степенью свободы при у ровне значимости α равном 1-0,95=0,05. Это можно сделать в MS EXCEL по формулам: =ХИ2.ОБР.ПХ(1-0,05; 20-1) или =ХИ2.ОБР(0,05; 20-1)

В результате получим верхний доверительный интервал для дисперсии: σ 2

Или тоже, но словами и для стандартного отклонения: «Значения выборки показывают, что с вероятностью 95%, стандартное отклонение процесса наполнения емкости растворителем меньше или равно 0,17 литров».

Ход решения приведен в файле примера на листе 1 сторонний .

СОВЕТ : О построении других доверительных интервалов см. статью Доверительные интервалы в MS EXCEL .

Как построить доверительные интервалы в Excel (с примерами)

Доверительный интервал представляет собой диапазон значений, который может содержать некоторый параметр генеральной совокупности с определенным уровнем достоверности.

В этом руководстве объясняется, как отображать доверительные интервалы на линейчатых диаграммах в Excel.

Пример 1. Нанесение доверительных интервалов на гистограмму

Предположим, у нас есть следующие данные в Excel, которые показывают среднее значение четырех различных категорий вместе с соответствующей погрешностью для 95% доверительных интервалов:

Чтобы создать гистограмму для визуализации средних значений категорий, выделите ячейки в диапазоне A1:B5 , а затем щелкните вкладку « Вставка » на верхней ленте. Затем нажмите « Вставить столбец или гистограмму» в группе «Диаграммы».

Это создаст следующую гистограмму:

Чтобы добавить полосы доверительного интервала, нажмите знак «плюс» (+) в правом верхнем углу гистограммы, затем нажмите « Полосы ошибок » и « Дополнительные параметры »:

В появившемся справа окне нажмите кнопку « Пользовательский » внизу. В появившемся новом окне выберите =Sheet1!$C$2:$C$5 как для положительного, так и для отрицательного значения ошибки. Затем нажмите ОК .

Это создаст следующие полосы доверительного интервала на гистограмме:

Не стесняйтесь также менять цвет столбцов, чтобы полосы доверительного интервала было легче увидеть:

построить доверительный интервал в Excel

Верхняя часть столбца представляет среднее значение для каждой категории, а столбцы ошибок показывают диапазон доверительного интервала для каждого среднего значения.

Среднее значение для категории А равно 12, а 95% доверительный интервал для этого среднего колеблется от 10 до 14.
Среднее значение для категории B равно 15, а 95% доверительный интервал для этого среднего колеблется от 12 до 18.
Среднее значение для категории C равно 18, а 95% доверительный интервал для этого среднего колеблется от 13 до 23.
Среднее значение для категории D равно 13, а 95% доверительный интервал для этого среднего колеблется от 10 до 16.

Функция ДОВЕРИТ.СТЬЮДЕНТ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование функции ДОВЕРИТ.СТЬЮДЕНТ в Microsoft Excel.

Возвращает доверительный интервал для среднего генеральной совокупности, используя распределение Стьюдента.