Доверительный интервал в Excel

Обновлено: 20.11.2024

Если вы хотите узнать, как рассчитать доверительный интервал в Excel, вы обратились по адресу!

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

Оглавление

Объяснение доверительного интервала

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

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

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

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

Вы также можете выбрать уровень значимости 0,05 %, что означает, что уровень значимости равен 95 % (другими словами, если у вас есть 100 повторных тестов, истинное среднее значение находится в 95).

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

Как рассчитать доверительный интервал в Excel?

Чтобы узнать доверительный интервал, необходимо использовать 3 функции:

СРЕДНЕЕ – найти истинное среднее значение;
STDEV.P – для расчета стандартного отклонения;
ДОВЕРИТЕЛЬНОСТЬ — для расчета значения достоверности.

Функция СРЗНАЧ в Excel

Функция СРЗНАЧ позволяет вычислить среднее (истинное среднее) значение заданного диапазона данных.

Эта функция имеет следующий синтаксис:

диапазон — для диапазона можно выделить весь диапазон (например, A1:A20) или выделить отдельные ячейки и разделить их запятой (A1, A2, A3).

Функция Excel STDEV.P

Для расчета стандартного отклонения диапазона данных можно использовать функцию СТАНДОТКЛОН.P.

Эта функция игнорирует все нечисловые значения и учитывает в расчетах только числа.

Эта функция принимает только один аргумент:

диапазон — для диапазона можно выделить весь диапазон (например, A1:A20) или выделить отдельные ячейки и разделить их запятой (A1, A2, A3).

Функция УВЕРЕННОСТЬ в Excel

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

Функция имеет следующие аргументы:

альфа – уровень значимости от 0 до 1 (процент в виде десятичного числа);
standard_dev — стандартное отклонение генеральной совокупности для диапазона данных;
size – численность населения.

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

В более новой версии Excel вы можете найти еще две похожие функции:

ДОВЕРИТЕЛЬНОСТЬ.НОРМ – возвращает доверительный интервал для диапазона данных, используя нормальное распределение;
CONFIDENCE.T — возвращает доверительный интервал для диапазона данных с использованием распределения T Стьюдента.

Вычисление доверительного интервала в Excel

Во-первых, необходимо заполнить диапазон данных, который будет использоваться для формулы.

Допустим, у нас разное время, необходимое для выполнения задачи в столбце A.

Во-первых, необходимо заполнить необходимые параметры для формулы ДОВЕРИТЕЛЬНОСТЬ.

В ячейке D2 введите уровень значимости (0,05).
В поле D2 введите формулу истинного среднего:

В ячейку D3 введите формулу стандартного отклонения:

В поле D4 введите численность населения (12).

Теперь вы можете рассчитать значение достоверности — введите следующую формулу в D5:

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

Нижний предел — среднее значение достоверности (52,92–12,91 = 40,01);
Верхний предел – это среднее значение + доверительный интервал (52,92 + 12,91 = 65,82).

О чем следует помнить при расчете доверительного интервала

Давайте еще раз кратко пройдемся по ключевым моментам расчета доверительного интервала в Excel.

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

Кроме того, стандартное отклонение не будет рассчитываться правильно, если вы используете старую функцию (СТАНДОТКЛОН).

Однако новая функция (STDEV.P) игнорирует все пробелы и нулевые значения при расчете стандартного отклонения.

Значение значимости должно быть представлено в виде десятичного числа в процентах от 0 до 1 (обычно это значение составляет от 5% до 0,05).
Функция ДОВЕРИТЕЛЬНОСТЬ возвращает значение достоверности. Имея в виду, что вам нужно получить доверительный интервал, не забудьте рассчитать нижний и верхний пределы.

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

Обычные ошибки в функции доверительного интервала

При расчете доверительного интервала с помощью функции 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, меньше, чем альфа. Другими словами, предположим, что мы используем x, standard_dev и size для построения двустороннего критерия на уровне значимости альфа гипотезы о том, что среднее значение генеральной совокупности равно µ0. Тогда мы не будем отвергать эту гипотезу, если µ0 находится в доверительном интервале, и отвергнем эту гипотезу, если µ0 не находится в доверительном интервале. Доверительный интервал не позволяет нам сделать вывод о том, что существует вероятность 1 – альфа того, что наша следующая посылка будет доставлена в течение времени, находящегося в доверительном интервале.

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

Дополнительную информацию о новых функциях см. в разделах Функция ДОВЕРИЕ.НОРМ и Функция ДОВЕРИЕ.T.

Синтаксис

Синтаксис функции CONFIDENCE имеет следующие аргументы:

Требуется альфа-канал. Уровень значимости, используемый для вычисления уровня достоверности. Уровень достоверности равен 100*(1 - альфа)%, или, другими словами, альфа, равная 0,05, указывает на уровень достоверности 95 %.

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

Обязательный размер. Размер выборки.

Примечания

Если размер не является целым числом, он усекается.

Пример

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

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

Что такое доверительная функция в Excel?

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

Когда вы вводите формулу для доверительного интервала в Excel, вы применяете синтаксис =ДОВЕРИТЕЛЬНОСТЬ(alpha,standard_dev,n) , где альфа-значение представляет уровень значимости между нулем и единицей, а n представляет размер выборки. Функция также применяет стандартное отклонение среднего значения выборки. Когда Excel вернет значение достоверности, примените его к набору данных, чтобы получить доверительный интервал. Это результат с двойным значением, который показывает, насколько далеко от среднего ваши данные могут варьироваться в любом направлении, не влияя на нулевую гипотезу.

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

Выполните следующие действия для расчета доверительного интервала с использованием обоих форматов функции =ДОВЕРИТЕЛЬНОСТЬ() в Excel:

1. Вычислить выборочное среднее

Расположите данные в таблице в порядке возрастания. Используя функцию =СРЗНАЧ(), рассчитайте среднее значение выборки данных. Например, предположим, что вы упорядочиваете значения данных в столбце А электронной таблицы. Используя первую строку для маркировки ваших столбцов, первое значение в ваших данных будет начинаться с ячейки A2. Если в ваших данных перечислены значения до строки 40, последнее значение в вашем наборе будет в ячейке A40.

В отдельном столбце используйте ячейку для записи размера выборки 38, например, в столбце C, ячейке C2. В столбце B пометьте соответствующие ячейки для каждого значения, которое вы вычисляете в столбце C. Таким образом, в ячейке B2 у вас есть метка «размер выборки», соответствующая значению в ячейке C2. Затем пометьте ячейку B3 как «среднее» и примените формулу =СРЗНАЧ(A2:A40) в ячейке C3. Предположим, что среднее значение для выборки равно 44,55.

2. Найдите стандартное отклонение

Примените функцию =STDEV.P() для расчета стандартного отклонения ваших данных. Пометьте другую ячейку в столбце B как «стандартное отклонение» под вашим «средним». Затем примените команду стандартного отклонения для примера набора данных в столбце A как =STDEV.P(A2:A40) в ячейке C4. Предположим, что программа возвращает стандартное отклонение 14 для примера набора данных.

3. Введите альфа-значение

Альфа-значение — это значение вероятности, которое дает коэффициент статистической значимости в процентах. Это представляет вероятность того, что нулевая гипотеза неверна. Поскольку статистика логического вывода нацелена на высокий уровень достоверности взаимосвязей данных, альфа-значение обычно находится в диапазоне от нуля до единицы, где 0 альфа-значение 1 .

При вводе функции достоверности в Excel используйте альфа-значение, полученное при оценке степени достоверности. Например, 96-процентная достоверность нулевой гипотезы даст вам значение альфа, равное 0,04. Создайте метку для альфа-значения под меткой стандартного отклонения в столбце B. Затем укажите числовое значение в столбце C в соответствующей ячейке.

4. Введите функцию достоверности

Когда у вас есть все значения для формулы, используйте новую ячейку, чтобы ввести формулу достоверности. Пометьте новую ячейку в столбце B как «значение достоверности» в разделе «альфа-значение» и укажите формулу в соответствующей ячейке C. Поскольку вы перечисляете каждое значение в ячейке, вы можете использовать метки ячеек в формуле. При размере выборки n=38 в ячейке C2, стандартном отклонении 14 в ячейке C4 и значении альфа 0,04 в ячейке C5 это будет выглядеть как =УВЕРЕННОСТЬ(C2,C4,C5) . Вы также можете ввести числовые значения, которые будут читаться как =ДОВЕРИТЕЛЬНОСТЬ(0,04,14,38), чтобы получить значение достоверности ±4,66.

5. Рассчитать доверительный интервал

Значение достоверности, которое вы получаете в результате вычисления, добавляется к среднему значению и вычитается из него, чтобы дать вам диапазон, который ваши данные могут распределить и по-прежнему поддерживать нулевую гипотезу. Используя примерное значение достоверности ±4,66, сложите и вычтите, чтобы получить интервал. Это дает (44,55 + 4,66 = 49,21) и (44,55 - 4,66 = 39,89) для доверительного интервала между 39,89 и 49,21. Вы также можете использовать формулу доверительного интервала в электронной таблице как = [ 44,55 ] + (1,96 * ( [ 14 ] / SQRT ( [ 38 ] ))) .

Советы по использованию функции достоверности

При использовании Excel для расчета доверительного интервала учитывайте следующее:

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

Используйте соответствующую функцию стандартного отклонения.Для числовых расчетов используйте функцию СТАНДОТКЛОН.П, а не СТАНДОТКЛОНПА, поскольку команда СТАНДОТКЛОН.П игнорирует данные, которые не являются числовыми.

Осторожно применяйте формулы. Убедитесь, что вы правильно вводите функции команд, так как каждая функция имеет определенные элементы, которые работают в определенном порядке при вводе их в Excel.

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

У научного консультанта есть выборка из шести результатов тестов из университетского класса философии, состоящего из 432 студентов. Они хотят рассчитать доверительный интервал в электронной таблице, чтобы определить, близки ли эти оценки к нормативному среднему. Перечисление этих данных в электронной таблице дает советнику:

A	B	C
1	Тесты	Расчет:	Формула:
2	81,3	Среднее	=СРЗНАЧ(A2:A7) = 85,183
3	81,8	=STDEV.P(A2:A7) = 3,010
4	84,5	0,03
5	85,9	Доверительное значение	=ДОВЕРИТЕЛЬНОСТЬ(C4,C3,C7) = ±2,667
6	88,2	Доверительный интервал
7	89,4	Объем выборки	6

В электронной таблице значения баллов отображаются в столбце A, а метки — в столбце B. Функции команд располагаются в ячейках столбца C. В Excel ввод командных функций автоматически возвращает числовое значение, преобразуя формулу к результату после нажатия «Enter». Для результатов теста значение достоверности равно 2,667, которое советник прибавляет и вычитает из среднего значения 85,183, чтобы получить распределение между 82,516 и 87,85. Поскольку значение альфа равно 0,03 или 3 %, это означает, что уровень достоверности равен 97 %.*

Обратите внимание, что любая компания, упомянутая в этой статье, не связана с Indeed.

Функция ДОВЕРИЕ относится к категории статистических функций Excel. Функции Список наиболее важных функций Excel для финансовых аналитиков. Эта шпаргалка охватывает сотни функций, которые важно знать аналитику Excel. Он будет использовать нормальное распределение для расчета и возврата доверительного интервала для среднего значения совокупности. Формула доверительного интервала Excel может быть чрезвычайно полезна для финансового анализа.

Как финансовый аналитик Финансовый аналитик Описание работы Приведенное ниже описание работы финансового аналитика дает типичный пример всех навыков, образования и опыта, необходимых для найма на работу аналитика в банке, учреждении или корпорации. Выполняйте финансовые прогнозы, отчеты и отслеживайте операционные показатели, анализируйте финансовые данные, создавайте финансовые модели. =УВЕРЕННОСТЬ() помогает прогнозировать и корректировать широкий спектр финансовых целей за счет оптимизации принятия финансовых решений. Это делается путем применения и графического отображения финансовых данных в набор переменных распределения.

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

Формула доверительного интервала в Excel

=УВЕРЕННОСТЬ(alpha,standard_dev,size)

Функция ДОВЕРИТЕЛЬНОСТЬ использует следующие аргументы:

Альфа (обязательный аргумент). Это уровень значимости, используемый для вычисления уровня достоверности. Уровень значимости равен 1 – уровень достоверности. Таким образом, уровень значимости 0,05 соответствует уровню достоверности 95 %.
Standard_dev (обязательный аргумент) — это стандартное отклонение для диапазона данных.
Размер (обязательный аргумент) — размер выборки.

Чтобы рассчитать доверительный интервал для среднего значения генеральной совокупности, возвращаемое значение достоверности необходимо добавить к среднему значению выборки и вычесть из него. Например, для выборки означает x:

Доверительный интервал = x ± ДОВЕРИТЕЛЬНОСТЬ

Как использовать формулу Excel для доверительного интервала?

Чтобы понять использование функции, рассмотрим пример:

Пример — доверительный интервал Excel

Предположим, что нам даны следующие данные:

Уровень значимости: 0,05
Стандартное отклонение генеральной совокупности: 2,5
Размер выборки: 100

Функция доверительного интервала Excel используется для расчета доверительного интервала со значимостью 0,05 (т. е. уровень достоверности 95 %) для среднего значения выборочного времени поездки в офис для 100 человек. Среднее значение выборки составляет 30 минут, а стандартное отклонение – 2,5 минуты.

Чтобы узнать доверительный интервал для среднего значения генеральной совокупности, мы будем использовать следующую формулу:

Мы получаем следующий результат:

Поэтому доверительный интервал составляет 30 ± 0,48999, что соответствует диапазону от 29,510009 до 30,48999 (минут).

Примечания о функции

Дополнительные ресурсы

Спасибо, что прочитали руководство CFI по функции доверительного интервала Excel. Потратив время на изучение и освоение этих функций, вы значительно ускорите свое финансовое моделирование Что такое финансовое моделирование Финансовое моделирование выполняется в Excel для прогнозирования финансовых показателей компании. Обзор того, что такое финансовое моделирование, как и зачем строить модель. . Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

Подсчет непустых ячеек в Excel Функция COUNTA Функция COUNTA вычисляет количество непустых ячеек в заданном наборе значений. Функцию =counta() также часто называют формулой Excel Countif Not Blank. Для финансового аналитика эта функция может быть полезна для подсчета ячеек, которые не являются пустыми или пустыми в заданном диапазоне.
Усовершенствованные формулы Excel, которые необходимо знать Усовершенствованные формулы Excel, которые необходимо знать Эти расширенные формулы Excel крайне важны для понимания и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
Сочетания клавиш Excel для ПК и Mac Ярлыки Excel для ПК Mac Сочетания клавиш Excel — список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, специалистов в области финансов и бухгалтерского учета. Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, работу с данными, редактирование формул и ячеек и другие сочетания клавиш.

Бесплатное руководство по Excel

Чтобы овладеть искусством работы с Excel, ознакомьтесь с БЕСПЛАТНЫМ ускоренным курсом CFI по Excel. Основы Excel — формулы для финансов Вы ищете ускоренный курс по Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. , который научит вас, как стать опытным пользователем Excel. Изучите самые важные формулы, функции и сочетания клавиш, чтобы уверенно проводить финансовый анализ.

Запустите бесплатный курс CFI по Excel прямо сейчас Основы Excel — формулы для финансов Вы ищете ускоренный курс Excel? Пройдите бесплатное обучение Excel, чтобы начать карьеру в сфере корпоративных финансов и инвестиционно-банковских услуг, от Института корпоративных финансов.

Функция «УВЕРЕННОСТЬ» — одна из старейших статистических функций Excel.

Функция «ДОВЕРИТЕЛЬНОСТЬ» вычисляет значение достоверности для доверительного интервала набора данных . Доверительный интервал – это определенный диапазон значений, который может содержать истинное среднее значение набора данных .

В этом руководстве вы узнаете больше о функции «УВЕРЕННОСТЬ», заглянете в ее суть и поймете, как заставить ее работать.

Каспер Лангманн, соучредитель Spreadsheeto

*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы можете выполнить те же действия.

Оглавление

Получите БЕСПЛАТНЫЙ файл с упражнениями

Прежде чем начать:

В этом руководстве вам понадобится набор данных для практики.

Я включил один для вас (бесплатно).

Загрузите прямо ниже!

Загрузите БЕСПЛАТНЫЙ файл упражнения

Что такое функция «УВЕРЕННОСТЬ»?

Функция «ДОВЕРИТЕЛЬНОСТЬ» — это статистическая функция Excel, которая возвращает значение достоверности с использованием нормального распределения .

В свою очередь, доверительное значение используется для вычисления доверительного интервала (или доверительного интервала) истинного среднего (или среднего) совокупности.

Это способ представить неопределенность ваших данных научным способом . Это дает читателю или пользователю так называемую «предельную погрешность».

Каспер Лангманн, соучредитель Spreadsheeto

В большинстве случаев люди используют 95 % в качестве уровня достоверности. Это означает, что из 100 повторных экспериментов истинное среднее значение найдено в 95 из них.

Однако уровень достоверности 95 % не является стандартом. Вы можете выбрать свой собственный уровень уверенности, хотя люди обычно используют 90% — 99%, чтобы… вселить уверенность. 😊

После того, как вы вычислите значение достоверности, доверительный интервал представлен со средним значением рядом со значением достоверности со знаком плюс-минус ( ±) между ними . р>

Синтаксис функции «УВЕРЕННОСТЬ»

Вот синтаксис этой функции:

=УВЕРЕННОСТЬ(alpha,standard_dev,size)

У него три (3) обязательных аргумента:

Альфа(уровень значимости, который рассчитывается как 1 — уровень достоверности; уровень достоверности 95% имеет уровень значимости 0,05)
Standard_dev(стандартное отклонение набора данных)
Размер(число населения)

Хотя среднее значение не является одним из аргументов, вам необходимо вычислить среднее значение, чтобы получить доверительный интервал. Результат функции «УВЕРЕННОСТЬ» добавляется к среднему значению и вычитается из него .

Каспер Лангманн, соучредитель Spreadsheeto

Если среднее значение равно 100, а доверительный интервал равен 10, это означает, что доверительный интервал составляет 100 ± 10 или 90–110.

Если у вас нет среднего значения набора данных, вы можете найти его с помощью функции Excel "СРЕДНИЙ".

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

В Excel есть две функции, которые можно использовать для расчета стандартного отклонения: СТАНДОТКЛОН.П и СТАНДОТКЛОНПА . Используйте только первый — STDEV.P — поскольку он игнорирует нечисловые данные.

Новые функции

Excel выпустил две новые похожие функции: «УВЕРЕННОСТЬ.НОРМ» и «УВЕРЕННОСТЬ.T» с похожими аргументами.

Функция «УВЕРЕННОСТЬ» по-прежнему может использоваться для обеспечения обратной совместимости. Однако рекомендуется изучить две новые функции, поскольку Excel утверждает, что эти функции обладают повышенной точностью.

Читайте также: