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

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

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

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

Ниже на рисунке показан синтаксис аргумента функции, а также расшифровка данных.


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

Пример расчета доверительного интервала
В нашем примере количество испытаний равно 12, у всех испытаний различные данные результата. Все они отображены в табличной форме. Эти данные и есть та самая совокупность данных. Значение стандартного отклонения равно восьми.
Уровень доверия при расчете доверительного интервала берем в процентах и оно равно 97%.
Теперь переходим к манипуляциям в Excel. Нам необходимо определится с ячейкой в которой будет зафиксирован результат вычислений. Выделяем эту ячейку и делаем как показано на рисунке. В окне рядом со значком «функция» вставляем нужную функцию.

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

Перед нашим взором появится окно. В котором нам необходимо заполнить необходимые и обязательные данные.
На рисунке показано куда вставлять необходимые данные. В первые две строки вставляем значения Альфа=0.003, в станд_откл вставляем 8. Со следующей строкой вроде тоже все понятно – ставим число двенадцать и вроде все сделано.Однако, что бы постоянно не менять в будущих расчетах эту переменную, а она постоянно меняется, предлагаем автоматизировать установку переменного значения.

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

Жмем на него и получаем новое окно. На рисунке видно где необходимо найти название «СЧЕТ» жмем на него и получаем окно в котором следует указать значение, 2. и так далее. Выделяем необходимый диапазон и получаем Значение 1. Нам больше для данного вычисления в примере не требуется. На рисунке четко видно, что следует делать и что в результате получится.


Следующий рисунок покажет, что после нажатия клавиши «ОК» мы получим результат наших вычислений.
Он равен 5, 011609

Однако, напомним, что границы доверительного интервала получаются путем получения суммы и разности от выбранного среднего значения. И это вычисление провел excel функцией ДОВЕРИТ.НОРМ.
А как вычислить само среднее значение? Рассчитываем его с помощью функции СРЗНАЧ. Для этого выделяем ячейку, куда будет записан результат вычислений этого инструмента. И щелчком кнопкой Вставить функцию, записываем в ячейку нужную нам функцию.

Установщик функций откроет нам очередное окно, где мы выберем статистические, потом СРЗНАЧ. Заканчиваем свои действия нажатием кнопки ОК.

На рисунке хорошо видно что следует делать с подстановкой аргументов.

Таким образом получаем результат среднего значения.

Расчет правой границы диапазона
Выделяем ячейку в нее вставляем формулу =F2+A16. В этих ячейках находятся результаты двух функций. Это ДОВЕРИТ.НОРМ и СРЗНАЧ.
Данные суммирования будут равны 6,953276

Таким же образом производим вычисление левой границы нужного нам интервала.
На рисунке хорошо показано как вводить формулу =F2-A16. Вычисления дадут число -3, 06994, это и будет нижняя или левая граница доверительного интервала.
Где в итоге получим значение равное -3,0699

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


На этом описание создания доверительного интервала с помощью функции ДОВЕРИТ.НОРМ заканчивается.
Другой способ основанный на применении фикции ДОВЕРИТ.СТЮДЕН будет описан в другой статье:
Как рассчитать доверительный интервал в Excel при не известной дисперсии

Рейтинг:(голосов:1)