Доверительный интервал в Excel. 2 способа расчета доверительного интервала в Excel

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

Вычисление доверительного интервала с помощью оператора CONFID.NORM

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

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

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

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

Существует два способа расчета дополнительного уровня:

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

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

Обращать внимание! Оператор TRUST все еще можно найти в Excel. Если вам нужно его использовать, ищите его в разделе «Совместимость».

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

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

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

2. Выберите раздел «Статистика» и найдите среди пунктов списка оператор TRUST.NORM. Вам необходимо нажать на него и нажать «ОК».

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

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

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

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

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

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

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

Определение доверительного интервала через CONFIDENCE.STUDENT

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

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

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

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

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

4. Требуется оператор STDDEV.B (по выборке) в разделе статистики. Выберите его и нажмите ОК.

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

6. Вернемся к аргументам TRUST.STUDENT, дважды щелкнув эту надпись в строке формул. В поле «Размер» установите оператор COUNT, как и в прошлый раз.

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

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

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

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

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

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

4. Чтобы определить левую границу, доверительный интервал необходимо вычесть из среднего значения.

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

Заключение

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