Нормальное распределение (гаусса) в excel

Общий обзор

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

Однако, для большинства случаев стандартная ошибка как такова не приемлема. Гораздо полезнее объединить эту меру точности с интервальной оценкой для параметра популяции.

Это можно сделать, используя знания о теоретическом распределении вероятности выборочной статистики (параметра) для того, чтобы вычислить доверительный интервал (CI — Confidence Interval, ДИ — Доверительный интервал) для параметра.

Вообще, доверительный интервал расширяет оценки в обе стороны некоторой величиной, кратной стандартной ошибке (данного параметра); два значения (доверительные границы), определяющие интервал, обычно отделяют запятой и заключают в скобки.

Форматы функции CONFIDENCE

Функция CONFIDENCE или ДОВЕРИТ, определяется пределами доверия — это нижняя и верхняя границы ДИ и являются 95% показателями. Например, при изучении предпочтении, было обнаружено, что 70% людей предпочитают Боржоми , по сравнению с Пепси при ДИ в 3% и уровнем доверия 95%, тогда существует 95-процентная вероятность того, что истинная пропорция составляет от 67 до 73%.

Функции “ДОВЕРИТ” отображаются под различными синтаксисами в разных версиях Excel. Например, Excel 2010 имеет две функции: “ДОВЕРИТ.НОРМ” и “ДОВЕРИТ.T”, которые помогают вычислять ширину “ДИ. ДОВЕРИТ.НОРМ” используется, когда известно стандартное отклонение измерения. В противном случае применяется “ДОВЕРИТ.T”, оценка осуществляется по данным выборки. Доверительные интервалы в excel до 2010 года имели только функцию “ДОВЕРИТ”. Его аргументы и результаты были аналогичными аргументам функции “ДОВЕРИТ.НОРМ”.

Первый по-прежнему доступен в более поздних версиях Excel для обеспечения совместимости. #NUM! Error — происходит, если альфа меньше или равна 0, или больше или равна 0. Данное стандартное отклонение меньше или равно 0. Указанный размер аргумента меньше единицы. #СТОИМОСТЬ! Error — происходит, если любой из предоставленных аргументов не является числовым.

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

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

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

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

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

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

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

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

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

Как написать коэффициент в экселе

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

Вычисление коэффициента вариации

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

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

Шаг 1: расчет стандартного отклонения

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

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

Начиная с версии Excel 2010 она разделена, в зависимости от того, по генеральной совокупности происходит вычисление или по выборке, на два отдельных варианта: СТАНДОТКЛОН.Г и СТАНДОТКЛОН.В.

Синтаксис данных функций выглядит соответствующим образом:

= СТАНДОТКЛОН(Число1;Число2;…) = СТАНДОТКЛОН.Г(Число1;Число2;…)

= СТАНДОТКЛОН.В(Число1;Число2;…)

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

Выполняется активация Мастера функций, который запускается в виде отдельного окна с перечнем аргументов. Переходим в категорию «Статистические» или «Полный алфавитный перечень». Выбираем наименование «СТАНДОТКЛОН.Г» или «СТАНДОТКЛОН.В», в зависимости от того, по генеральной совокупности или по выборке следует произвести расчет. Жмем на кнопку «OK».

Открывается окно аргументов данной функции. Оно может иметь от 1 до 255 полей, в которых могут содержаться, как конкретные числа, так и ссылки на ячейки или диапазоны. Ставим курсор в поле «Число1».

Мышью выделяем на листе тот диапазон значений, который нужно обработать. Если таких областей несколько и они не смежные между собой, то координаты следующей указываем в поле «Число2» и т.д.

Когда все нужные данные введены, жмем на кнопку «OK»

В предварительно выделенной ячейке отображается итог расчета выбранного вида стандартного отклонения.

Шаг 2: расчет среднего арифметического

Среднее арифметическое является отношением общей суммы всех значений числового ряда к их количеству. Для расчета этого показателя тоже существует отдельная функция – СРЗНАЧ. Вычислим её значение на конкретном примере.

Выделяем на листе ячейку для вывода результата. Жмем на уже знакомую нам кнопку «Вставить функцию».

В статистической категории Мастера функций ищем наименование «СРЗНАЧ». После его выделения жмем на кнопку «OK».

Запускается окно аргументов СРЗНАЧ. Аргументы полностью идентичны тем, что и у операторов группы СТАНДОТКЛОН. То есть, в их качестве могут выступать как отдельные числовые величины, так и ссылки.

После того, как их координаты были занесены в поле окна аргументов, жмем на кнопку «OK».

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

Шаг 3: нахождение коэффициента вариации

Теперь у нас имеются все необходимые данные для того, чтобы непосредственно рассчитать сам коэффициент вариации.

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

Это можно сделать после её выделения, находясь во вкладке «». Кликаем по полю формата на ленте в блоке инструментов «Число». Из раскрывшегося списка вариантов выбираем «Процентный».

После этих действий формат у элемента будет соответствующий.

Снова возвращаемся к ячейке для вывода результата. Активируем её двойным щелчком левой кнопки мыши. Ставим в ней знак «=». Выделяем элемент, в котором расположен итог вычисления стандартного отклонения.

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

Точечная и интервальная оценки среднего значения

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

Если оценку среднего требуется связать с определённой вероятностью, то интересующий
параметр генеральной совокупности нужно оценивать не одним числом, а интервалом. Доверительным
интервалом называют интервал, в котором с определённой вероятностью P
находится значение оцениваемого
показателя генеральной совокупности. Доверительный интервал, в котором с вероятностью
P
= 1 — α

находится случайная
величина ,
рассчитывается следующим образом:

α
= 1 — P

, которое можно найти
в приложении к практически любой книге по статистике.

На практике среднее значение генеральной совокупности
и дисперсия
не известны, поэтому дисперсия генеральной совокупности заменяется дисперсией выборки ,
а среднее генеральной совокупности — средним значением выборки . Таким образом, доверительный
интервал в большинстве случаев рассчитывается так:

Формулу доверительного интервала можно использовать для оценки среднего генеральной
совокупности, если

известно стандартное отклонение генеральной совокупности;
или стандартное отклонение генеральной совокупности не известно, но объём выборки — больше 30.

Среднее значение выборки
является несмещённой оценкой среднего генеральной совокупности .
В свою очередь, дисперсия выборки
не является несмещённой оценкой дисперсии генеральной совокупности .
Для получения несмещённой оценки дисперсии генеральной совокупности в формуле дисперсии выборки объём
выборки n
следует заменить на n
-1.

Пример 1.
Собрана информация из 100 случайно выбранных кафе в
некотором городе о том, что среднее число работников в них составляет 10,5 со стандартным отклонением
4,6. Определить доверительный интервал 95% числа работников кафе.

где —
критическое значение стандартного нормального распределения для уровня значимости
α
= 0,05
.

Таким образом, доверительный интервал 95% среднего числа работников кафе
составил от 9,6 до 11,4.

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

сумма значений в наблюдениях ,

сумма квадратов отклонения значений от среднего .

Вычислить доверительный интервал 95 % для математического ожидания.

вычислим стандартное отклонение:

вычислим среднее значение:

Подставляем значения в выражение для доверительного интервала:

где —
критическое значение стандартного нормального распределения для уровня значимости
α
= 0,05
.

Получаем:

Таким образом, доверительный интервал 95% для математического ожидания данной выборки
составил от 7,484 до 11,266.

Пример 3.
Для случайной выборки из генеральной совокупности из 100
наблюдений вычислено среднее значение 15,2 и стандартное отклонение 3,2. Вычислить доверительный интервал
95 % для математического ожидания, затем доверительный интервал 99 %. Если мощность выборки и её
вариация остаются неизменными, а увеличивается доверительный коэффициент, то доверительный интервал
сузится или расширится?

Подставляем данные значения в выражение для доверительного интервала:

где —
критическое значение стандартного нормального распределения для уровня значимости
α
= 0,05
.

Получаем:

Таким образом, доверительный интервал 95% для среднего данной выборки
составил от 14,57 до 15,82.

Вновь подставляем данные значения в выражение для доверительного интервала:

где —
критическое значение стандартного нормального распределения для уровня значимости
α
= 0,01
.

Получаем:

Таким образом, доверительный интервал 99% для среднего данной выборки
составил от 14,37 до 16,02.

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

Функция ДОВЕРИТ.СТЬЮДЕНТ — Служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for 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.

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

Синтаксис

ДОВЕРИТ.СТЬЮДЕНТ(альфа;стандартное_откл;размер)

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

Альфа — обязательный аргумент. Уровень значимости, используемый для вычисления доверительного уровня.

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

Замечания

Если какой-либо из аргументов не является числом, доверит. T возвращает #VALUE! значение ошибки #ЗНАЧ!.
Если standard_dev ≤ 0, доверит. T возвращает #NUM! значение ошибки #ЗНАЧ!.
Если значение аргумента «размер» не является целым числом, оно усекается.
Если размер равен 1, доверит. T возвращает #DIV/0! значение ошибки #ЗНАЧ!.

Пример

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

Формула	Описание	Результат
=ДОВЕРИТ.СТЬЮДЕНТ(0,05;1;50)	Доверительный интервал для среднего значения по выборке размером 50 % с уровнем значимости 5 % и стандартным отклонением 1. Это основано на t-распределении учащихся.	0,284196855

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

крайне мала и равна 0,003(1–0,997). Такие маловероятные события считаются практически невозможными, а потому величину

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

Интервал, в который с данной степенью вероятности будет заключена неизвестная величина оцениваемого параметра, называют доверительным, а вероятность Р – доверительной вероятностью. Чаще всего доверительную вероятность принимают равной 0,95 или 0,99, тогда коэффициент доверия t равен соответственно 1,96 и 2,58. Это означает, что доверительный интервал с заданной вероятностью заключает в себе генеральную среднюю.

Наряду с абсолютной величиной предельной ошибки выборки рассчитывается и относительная ошибка выборки, которая определяется как процентное отношение предельной ошибки выборки к соответствующей характеристике выборочной совокупности:

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

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

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

Доверительный интервал представляет собой диапазон значений. Выборочное среднее x является серединой этого диапазона, следовательно, доверительный интервал определяется как (x ± ДОВЕРИТ). Например, если x — это среднее выборочное значение времени доставки товаров, заказанных по почте, то математическое ожидание генеральной совокупности принадлежит интервалу (x ± ДОВЕРИТ).

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

Синтаксис

ДОВЕРИТ(альфа ;станд_откл;размер)

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

Станд_откл — стандартное отклонение генеральной совокупности для интервала данных, предполагается известным.

Размер — размер выборки.

Замечания

· Если какой-либо из аргументов не является числом, функция ДОВЕРИТ возвращает значение ошибки #ЗНАЧ!.

· Если альфа ≤ 0 или альфа ≥ 1, функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.

· Если станд_откл ≤ 0, функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.

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

· Если размер < 1, функция ДОВЕРИТ возвращает значение ошибки #ЧИСЛО!.

· Если предположить, что альфа = 0,05, то нужно определить ту часть стандартной нормальной кривой, которая равна (1 — альфа), или 95 процентам. Это значение равно ± 1,96. Следовательно, доверительный интервал, следовательно, определяется по формуле: