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

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

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

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

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

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

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

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

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

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

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

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

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

• Среднее значение для категории А равно 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 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. 2 способа расчета доверительного интервала в Эксель

Доверительный интервал рассчитывают для решения статистических вопросов. Узнать это число без помощи компьютера достаточно сложно, поэтому стоит воспользоваться инструментами Excel при необходимости узнать допустимый диапазон отклонения от среднего значения выборки.

Вычисление доверительного интервала с оператором ДОВЕРИТ.НОРМ

Оператор относится к категории «Статистические». В ранних версиях он называется «ДОВЕРИТ», его функция состояла из тех же аргументов.

Полная функция выглядит так: =ДОВЕРИТ.НОРМ(Альфа;Станд_откл;Размер).

Рассмотрим формулу оператора по аргументам (каждый из них обязательно должен появиться в расчете):

1. «Альфа» – указывает на уровень значимости, на котором основывается вычисление.

Посчитать дополнительный уровень можно двумя способами:

• 1-(Альфа) – подойдет в случае, если аргумент является коэффициентом. Пример: 1-0,4=0,6 (0,4=40%/100%);
• (100-(Альфа))/100 – формулу используют при подсчете интервала в процентах. Пример: (100-40)/100=0,6.

1. Стандартное отклонение – допустимое отклонение в конкретной выборке.
2. Размер – количество анализируемой информации

Обратите внимание! Оператор ДОВЕРИТ все еще можно встретить в Excel. Если необходимо воспользоваться именно им, ищите его в разделе «Совместимость».

Проверим формулу в действии. Необходимо создать таблицу с несколькими значениями статистических расчетов. Представим, что стандартное отклонение равняется 7. Цель – определить интервал с уровнем доверия 80%.

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

1. Выбираем пустую ячейку и открываем «Менеджер функций». Он появится на экране после клика по значку «F(x)» возле строки формул. Попасть в меню функций также можно через вкладку «Формулы» на панели инструментов, в ее левой части находится кнопка «Вставить функцию» с таким же знаком.

1. Выбираем раздел «Статистические» и находим среди пунктов списка оператор ДОВЕРИТ.НОРМ. Требуется кликнуть по нему и нажать «ОК».

1. Откроется окно заполнения аргументов. В первой строке должна оказаться формула расчета аргумента «Альфа». Согласно условию, уровень доверия выражается в процентах, поэтому используем вторую формулу: (100-(Альфа))/100.
2. Стандартное отклонение уже известно, впишем его в строку или выберем ячейку с вынесенными на страницу данными. В третью строку попадает количество записей в таблице – здесь их 10. После заполнения всех полей нажимаем «Enter» или «ОК».

Функцию можно автоматизировать, чтобы изменение информации не привело к сбою вычисления. Выясним, как сделать это пошагово.

1. Когда поле «Размер» еще не заполнено, кликаем по нему, делая его активным. Потом открываем меню функций – оно находится в левой части экрана на одной линии со строкой формул. Чтобы открыть его, нажмем на стрелку. Нужно выбрать раздел «Другие функции», это последняя запись в списке.

1. Вновь появится «Менеджер функций». Среди статистических операторов необходимо найти функцию «Счет» и выбрать ее.

Важно! Аргументами функции СЧЕТ могут быть числа, ячейки или группы ячеек. В рассматриваемом случае подойдет последнее. Всего в формуле может быть не более 255-и аргументов.

1. В верхнем поле должны оказаться сгруппированные в диапазон ячейки значения. Кликнем по первому аргументу, выделим столбец без учета шапки и нажмем кнопку «ОК».

Значение интервала появится в ячейке. Такое число получилось с использованием данных примера: 2,83683532.

Определение доверительного интервала через ДОВЕРИТ.СТЬЮДЕНТ

Этот оператор тоже предназначен для вычисления диапазона отклонения. В расчетах применяют другую стратегию – в ней используется распределение Стьюдента при условии, что разброс величины неизвестен.

Формула отличается о предыдущей только оператором. Это выглядит так: =ДОВЕРИТ.СТЬЮДЕНТ(Альфа;Cтанд_откл;Размер).

Используем сохранившуюся таблицу для новых расчетов. Стандартное отклонение в новой задаче становится неизвестным аргументом.

1. Откроем «Менеджер функций» одним из описанных выше способов. Нужно найти в разделе «Статистические» функцию ДОВЕРИТ.СТЬЮДЕНТ, выбрать его и нажать «ОК».

1. Заполним аргументы функции. В первой строке остается та же формула: (100-(Альфа))/100.
2. Отклонение неизвестно, согласно условию задачи. Чтобы вычислить его, используем дополнительную формулу. Нужно кликнуть по второму полю в окне аргументов, открыть меню функций и выбрать пункт «Другие функции».

1. Необходим оператор СТАНДАРТОТКЛОН.В (по выборке) в разделе «Статистические». Выбираем его и нажимаем «ОК».

1. Заполняем первый аргумент открывшегося окна диапазоном ячеек со значениями без учета шапки. Не нужно нажимать после этого кнопку «ОК».

1. Вновь перейдем к аргументам ДОВЕРИТ.СТЬЮДЕНТ, дважды кликнув по этой надписи в строке формул. В поле «Размер» устанавливаем оператор СЧЕТ, как в прошлый раз.

После нажатия «Enter» или «ОК» в ячейке появится новое значение доверительного интервала. По Стьюденту оно получилось меньше – 0,540168684.

Определение границ интервала с двух сторон

Для расчета границ интервала необходимо узнать, каково среднее значение по нему, с помощью функции СРЗНАЧ.

1. Откроем «Менеджер функций» и выберем нужный оператор в разделе «Статистические».

1. Добавляем группу ячеек, содержащих значения, в поле первого аргумента и жмем кнопку «ОК».

1. Теперь можно определить правую и левую границу. Понадобятся простые математические действия. Расчет правой границы: выбираем пустую ячейку, проводим в ней сложение ячеек с доверительным интервалом и средним значением.

1. Для определения левой границы из среднего значения нужно вычесть доверительный интервал.

1. Такие же операции проделываем с доверительным интервалом по Стьюденту. В результате получаем границы интервала в двух вариантах.

Заключение

Благодаря «Менеджеру функций» Excel не составит труда найти доверительный интервал. Определить его можно двумя способами, в которых используются разные методы вычисления.

Функция ДОВЕРИТ и нормальный доверительный интервал в Excel

Функция ДОВЕРИТ в Excel предназначена для определения доверительного интервала для среднего значения, найденного для генеральной совокупности, которая имеет нормальное распределение.

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

Как построить доверительный интервал нормального распределения в Excel

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

Рассматриваемая функция была заменена функцией ДОВЕРИТ.НОРМ с версии Excel 2010. Функция ДОВЕРИТ была оставлена для обеспечения совместимости с документами, созданными в более ранних версиях табличного редактора.

Пример расчета доверительного интервала в Excel

Пример 1. В заводском цехе производят деталь, длина которой должна составлять 200 мм. Стандартное отклонение от длины – 3,6 мм. Для контроля качества деталей из партии (генеральная совокупность) делают выборку из 25 деталей. Определить интервал с доверительный уровнем 95%.

Вид таблицы данных:

Для определения доверительного интервала используем функцию:

• 1-B2 – уровень значимости (рассчитан с учетом зависимости от доверительного уровня);
• B3 – значение стандартного отклонения;
• B4 – количество деталей в выборке.

То есть, границы доверительного интервала соответствуют: (Xср-1,4112;Xср+1,4112). Допустим, было определено среднее значение выборки – 199,5 мм. Тогда доверительный интервал примерно определяется как (198,1;200,9), при этом номинальная длина детали (200 мм) находится в доверительном диапазоне, то есть производственный процесс не нарушен.

Как найти границы доверительного интервала в Excel

Пример 2. Были проведены опыты по определению скорости распространения звуковой волны в воздухе. Результаты 10 опытов записаны в таблицу. Определить левую и правую границы доверительного интервала для среднего значения.

Вид таблицы данных:

Для нахождения левой границы используем формулу:

В данном случае выборка и генеральная совокупность приняты как имеющиеся данные для 10 проведенных опытов. Среднее выборочное значение рассчитано с помощью функции СРЗНАЧ. Для получения левой границы доверительного интервала из данного значения вычитаем число, полученное в результате выполнения функции ДОВЕРИТ, в которой значение второго аргумента определено с помощью функции СТАНДОТКЛОН.Г, а число опытов – подсчетом количества ячеек функцией СЧЁТЗ.

Поскольку уровень значимости не задан, используем стандартное значение – 0,05.

Правая граница определяется аналогично с разницей в том, что к среднему значению выборки прибавляется результат расчета функции ДОВЕРИТ:

Как посчитать доверительный интервал по функции ДОВЕРИТ в Excel

Функция имеет следующую синтаксическую запись:

• альфа – обязательный, принимает числовое значение, характеризующее уровень значимости – вероятность отклонения нулевой (неверной) гипотезы в том случае, когда она на самом деле верна. Определяется как 1-, где  — уровень доверия (вероятность нахождения истинного значения некоторой оцениваемой величины в определенном интервале, называемом доверительным).
• стандартное_откл – обязательный, принимает значение стандартного отклонения величины для генеральной совокупности значений (в Excel предусмотрена функция для определения этой величины — СТАНДОТКЛОН.Г).
• размер – обязательный, принимает числовое значение, характеризующее количество точек данных в анализируемой выборке (ее размер).

1. Все аргументы функции должны указываться в виде числовых значений или данных, которые могут быть преобразованы в числа (например, текстовые строки с числами, логические ИСТИНА, ЛОЖЬ). В противном случае результатом выполнения функции ДОВЕРИТ будет код ошибки #ЧИСЛО!
2. Аргумент альфа должен быть указан числовым значением из диапазона от 0 до 1 (оба включительно). Иначе функция ДОВЕРИТ вернет код ошибки #ЧИСЛО! Аналогичная ошибка возникает в случаях, когда аргумент стандартное_откл задан числом, взятым из диапазона отрицательных значений или нулем.
3. Диапазон допустимых значений для аргумента размер – от 1 до бесконечности со знаком плюс.

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры