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

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

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

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

Когда вы вводите формулу для доверительного интервала в Excel, вы применяете синтаксис =ДОВЕРИТЕЛЬНОСТЬ(альфа,стандартное_отклонение,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 студентов. Они хотят рассчитать доверительный интервал в электронной таблице, чтобы определить, близки ли эти оценки к нормативному среднему. Перечисление этих данных в электронной таблице дает советнику:

Расчет результатов теста ABC 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 Доверительный интервал 82,516 – 87,85 7 89,4 Размер выборки 6

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

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