Как рассчитать доверительный интервал в 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 при не известной дисперсии
Основной задачей данного расчета является избежать погрешностей точечной оценки. Более целесообразно находить возможный интервал данных, в которых может находиться искомая величина. То есть с помощью доверительного интервала можно сделать более точный статистический вывод о вероятности нахождения определенного параметра в определенном массиве данных.
Программа 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)
Предыдущая статья: Именованные диапазоны в Excel
Следующая статья: Как рассчитать доверительный интервал в Excel при неизвестной дисперсии
Следующая статья: Как рассчитать доверительный интервал в Excel при неизвестной дисперсии
Не пропустите похожие инструкции:
Комментариев пока еще нет. Вы можете стать первым!
Популярное
Авторизация
Добавить комментарий!