Расчет доверительного интервала в Excel

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

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

Расчет доверительного интервала

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

Существует два метода выполнения этой задачи в Microsoft Excel:

• оператор НОРМА УВЕРЕННОСТИ – используется в случаях, когда дисперсия известна;
• оператор ТРАСТ.СТУДЕНТкогда дисперсия неизвестна.

Ниже мы поэтапно разберем оба метода на практике.

Способ 1: оператор TRUST.NORM

Впервые данная функция была введена в арсенал программы в редакции Excel 2010 (до этой версии ее заменял оператор «ДОВЕРЕННЫЕ»). Оператор включен в категорию «статистический».

Формула функции НОРМА УВЕРЕННОСТИ выглядит так:

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

Как мы видим, функция имеет три аргумента:

• "Альфа" – показатель уровня значимости, который принимается за основу расчета. Уровень доверия рассчитывается следующим образом:
  • 1-"Альфа". Это выражение применимо, если значение "Альфа" представлено в виде коэффициента. Например, 1-0,7 0,3 =, где 0,7=70%/100%.
  • (100-"Альфа")/100. Это выражение будет применяться, если рассматривать уровень достоверности со значением "Альфа" в процентах. Например, (100-70)/100 = 0,3.
• "Среднеквадратичное отклонение" – соответственно стандартное отклонение анализируемой выборки данных.
• "Размер" — размер выборки данных.

Примечание: Для этой функции наличие всех трёх аргументов является обязательным условием.

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

Формула функции ДОВЕРЕННЫЕ следующим образом:

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

В самой формуле отличий нет, отличается только название оператора. В Excel 2010 и более поздних выпусках этот оператор находится в категории «Совместимость». В старых версиях программы он находится в разделе статических функций.

Граница доверительного интервала определяется по следующей формуле:

X+(-)ДОВЕРИТ.НОРМ

в котором Х — среднее значение в указанном диапазоне.

Теперь посмотрим, как применить эти формулы на практике. Итак, у нас есть таблица с различными данными по 10 проведенным измерениям. В этом случае стандартное отклонение набора данных равно 8.

Наша задача — получить значение доверительного интервала с уровнем достоверности 95%.

1. Прежде всего выберите ячейку для отображения результата. Затем нажимаем на кнопку «Вставить функцию» (слева от строки формул).
2. Откроется окно Мастера функций. Нажав на текущую категорию функций, разверните список и нажмите на строку в нем. «Статистический».
3. В предложенном списке нажмите на оператора «НОРМА ДОВЕРИЯ», Затем нажмите OK.
4. Перед нами появится окно с настройками аргументов функции, заполнив которое нажимаем кнопку OK.
   • в поле "Альфа" указать уровень значимости. Наша задача предполагает уровень достоверности 95%. Подставив это значение в формулу расчета, которую мы рассмотрели выше, получим выражение: (100-95)/100. Записываем его в поле аргумента (а можно сразу написать результат расчета равный 0,05).
   • в поле «std_off» по нашим условиям пишем цифру 8.
   • в поле «Размер» укажите количество проверяемых элементов. В нашем случае было снято 10 измерений, поэтому пишем цифру 10.
5. Чтобы избежать необходимости перенастраивать функцию при изменении данных, ее можно автоматизировать. Для этого мы используем функцию «ПРОВЕРЯТЬ". Поместите указатель в область ввода информации об аргументе. "Размер", затем щелкните значок треугольника в левой части строки формул и щелкните элемент "Больше возможностей…".
6. В результате откроется еще одно окно Мастера функций. Выбрав категорию «Статистический», нажмите на функцию «ПРОВЕРЯТЬ", тогда хорошо.
7. На экране появится еще одно окно с настройками аргументов функции, которая используется для определения количества ячеек в заданном диапазоне, содержащих числовые данные.

   Формула функции ПРОВЕРИТЬ написано так: =СЧЁТ(Значение1;Значение2;...).

   Количество доступных аргументов этой функции может достигать 255. Здесь можно писать либо конкретные цифры, либо адреса ячеек, либо диапазоны ячеек. Мы воспользуемся последним вариантом. Для этого кликните по области ввода информации для первого аргумента, затем, зажав левую кнопку мыши, выделите все ячейки одного из столбцов нашей таблицы (не считая заголовка), а затем нажмите кнопку OK.

8. В результате выполненных действий результат расчетов для оператора отобразится в выбранной ячейке. НОРМА УВЕРЕННОСТИ. В нашей задаче его значение оказалось равным 4,9583603.
9. Но это еще не окончательный результат в нашей задаче. Далее необходимо вычислить среднее значение за заданный интервал. Для этого вам нужно использовать функцию «СЕРДЦЕ"Объект A, выполняющий задачу вычисления среднего значения по заданному диапазону данных.

   Формула оператора записывается так: =СРЗНАЧ(число1;число2;...).

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

10. В категории «Статистический» выбери скучного оператора "СЕРДЦЕ" и нажмите OK.
11. В аргументах функции в значении аргумента "Число" укажите диапазон, в который входят все ячейки со значениями всех измерений. Затем мы нажимаем ХОРОШО.
12. В результате предпринятых действий среднее значение будет автоматически рассчитано и отображено в ячейке с вновь вставленной функцией.
13. Теперь нам нужно рассчитать границы CI (доверительного интервала). Начнем с расчета значения правой границы. Выбираем ячейку, в которой хотим отобразить результат, и выполняем сложение полученных результатов с помощью операторов «СЕРДЦЕ" и "НОРМЫ УВЕРЕННОСТИ». В нашем случае формула выглядит так: A14+A16. После ввода нажмите Enter.
14. В результате будет произведен расчет и результат сразу отобразится в ячейке с формулой.
15. Затем аналогичным образом выполняем расчет для получения значения левой границы CI. Только в этом случае значение результата «НОРМЫ УВЕРЕННОСТИ» из полученного результата нужно не прибавлять, а вычитать с помощью оператора «СЕРДЦЕ". В нашем случае формула выглядит так: =A16-A14.
16. После нажатия Enter мы получим результат в данной ячейке по формуле.

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

• Для определения правой границы КИ общая формула будет выглядеть так:

  =СРЗНАЧ(B2:B11)+ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

• Аналогично и для левой границы, только вместо плюса нужно поставить минус:

  =СРЗНАЧ(B2:B11)-ДОВЕРИТ.НОРМ(0,05;8;СЧЁТ(B2:B11)).

Способ 2: оператор TRUST.STUDENT

Теперь давайте познакомимся со вторым оператором для определения доверительного интервала — ТРАСТ.СТУДЕНТ. Данная функция была введена в программу сравнительно недавно, начиная с версии Excel 2010, и направлена ​​на определение КИ выбранного набора данных с использованием распределения Стьюдента с неизвестной дисперсией.

Формула функции ТРАСТ.СТУДЕНТ следующим образом:

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

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

1. Сначала выделим ячейку, в которой мы планируем отобразить результат. Затем нажмите на значок «Вставить функцию» (слева от строки формул).
2. Откроется уже знакомое окно Мастера функций. Выберите категорию «Статистический», затем из предложенного списка функций нажмите на оператор «ДОВЕРЕННЫЙ СТУДЕНТ», затем - OK.
3. В следующем окне нам нужно настроить аргументы функции:
   • В "Альфа" как и в первом способе, укажите значение 0,05 (или «100-95)/100»).
   • Перейдем к аргументации. «std_off». Поскольку по условиям задачи ее значение нам неизвестно, необходимо произвести соответствующие расчеты, в которых используется оператор «СТАНДОТКЛОН.Б». Нажмите на кнопку добавления функции, а затем на элемент "Больше возможностей…".
   • В следующем окне Мастера функций выберите оператор «СТАНДОТКЛОН.Б» в категории «Статистический» и нажмите OK.
   • Попадаем в окно настройки аргументов функции, формула которого выглядит следующим образом: =СТАНДОТКЛОН.В(число1;число2;...). В качестве первого аргумента мы указываем диапазон, включающий все ячейки столбца «Значение» (не считая заголовка).
   • Теперь вам нужно вернуться в окно с аргументами функции»ТРАСТ.СТУДЕНТ». Для этого нажмите на одноименную надпись в поле ввода формулы.
   • Теперь перейдем к последнему аргументу «Размер». Как и в первом способе, здесь можно либо просто указать диапазон ячеек, либо вставить оператор "ПРОВЕРИТЬ". Выбираем последний вариант.
   • После того, как все аргументы заполнены, нажмите кнопку OK.
4. В выбранной ячейке отобразится значение доверительного интервала согласно указанным нами параметрам.
5. Далее нам необходимо рассчитать значения границ CI. А для этого вам необходимо получить среднее значение для выбранного диапазона. Для этого снова применим функцию «СЕРДЦЕ". Алгоритм действий аналогичен описанному в первом способе.
6. Получив значение «СЕРДЦЕ", можно приступить к расчету границ ЭК. Сами формулы ничем не отличаются от тех, что используются с «НОРМЫ УВЕРЕННОСТИ»:
   • Правая граница CI=СРЕДНИЙ+УВЕРЕННОСТЬ СТУДЕНТОВ
   • Левая граница CI = СРЕДНЯЯ УВЕРЕННОСТЬ СТУДЕНТОВ

Заключение

Арсенал инструментов Excel невероятно велик, и наряду с обычными функциями программа предлагает множество специальных функций, которые значительно облегчат работу с данными. Возможно, описанные выше шаги могут показаться некоторым пользователям на первый взгляд сложными. Но после детального изучения вопроса и последовательности действий все станет намного проще.