Доверительный интервал в Excel: формула и готовый файл

Среднее по вашей группе — 47,40 балла. Но это среднее двадцати человек, а в выводах вы пишете о студентах вообще. Доверительный интервал показывает, в каких пределах лежит их истинное среднее.

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

🧮Онлайн-калькулятор доверительного интервалаПосчитайте свои данные за пару минут — нажмите, чтобы открыть→

Что показывает доверительный интервал (и чего НЕ показывает)

Интервал отвечает на один вопрос: насколько точно вы измерили среднее. Он не описывает людей — он описывает вашу оценку.

Это место путают чаще всего. Запись «M ± σ» и доверительный интервал выглядят похоже, но говорят о разном:

• M ± σ — где лежат сами люди. У нас 47,40 ± 4,32, то есть примерно две трети студентов набрали от 43 до 52 баллов.
• 95% ДИ — где лежит истинное среднее всех студентов, которых вы обобщаете. У нас это [45,38; 49,42] — коридор вдвое у́же.

Отсюда правило: фраза «95% студентов попали в интервал 45,38–49,42» — ошибка. В интервал с надёжностью 95% попадает среднее, а не отдельные студенты. Подробный разбор смысла — в статье доверительный интервал простыми словами.

Формула в Excel через t-множитель (правильный путь)

Готовой функции, которая сразу выдаст обе границы, в Excel нет. Интервал собирают из четырёх кирпичей.

• Среднее M — =СРЗНАЧ(B2:B21), центр интервала.
• Стандартное отклонение σ — =СТАНДОТКЛОН(B2:B21), разброс данных.
• Объём выборки n — =СЧЁТ(B2:B21), сколько человек реально измерено.
• Ошибка среднего m — =СТАНДОТКЛОН(B2:B21)/КОРЕНЬ(СЧЁТ(B2:B21)), половина дела уже сделана.
• t-множитель — =СТЬЮДРАСПОБР(0,05;СЧЁТ(B2:B21)-1), поправка на маленькую выборку.

Дальше арифметика: погрешность = t · m, а границы = M ± погрешность.

Пример на реальных данных

Ситуативная тревожность 20 студентов: 48, 52, 45, 41, 54, 47, 50, 43, 46, 51, 44, 49, 53, 42, 47, 55, 40, 46, 50, 45.

Данные лежат в столбце B со второй по двадцать первую строку, результаты складываем в столбец E: строка E1 — первый шаг, E2 — второй и так далее.

Расчёт 95% доверительного интервала по шагам

Результат: 95% ДИ для среднего — [45,38; 49,42].

Если промежуточные строки не нужны, обе границы считаются одной формулой. Нижняя:

=СРЗНАЧ(B2:B21)-СТЬЮДРАСПОБР(0,05;СЧЁТ(B2:B21)-1)*СТАНДОТКЛОН(B2:B21)/КОРЕНЬ(СЧЁТ(B2:B21))

Верхняя — то же самое, только минус перед СТЬЮДРАСПОБР меняется на плюс.

Первый аргумент СТЬЮДРАСПОБР — это уровень значимости: 0,05 для 95% интервала, 0,01 для 99%. Второй — число степеней свободы, всегда n − 1.

Функция ДОВЕРИТ и почему она не подходит для диплома

В Excel есть функция, которая якобы решает задачу целиком:

=ДОВЕРИТ(0,05;4,32;20)

Она возвращает 1,89 — половину ширины интервала, границы получились бы [45,51; 49,29]. Числа похожи на правильные, но они другие.

Причина в устройстве формулы. ДОВЕРИТ умножает ошибку среднего на z-множитель 1,96 — значение из нормального распределения. Оно верно, когда стандартное отклонение генеральной совокупности известно точно, то есть на очень больших выборках. Вы же оценили σ по двадцати студентам, и эта оценка сама неточная. Плату за неточность берёт распределение Стьюдента: вместо 1,96 нужно 2,093.

Чем меньше выборка, тем сильнее расходятся результаты.

Насколько ДОВЕРИТ занижает границы интервала при разном объёме выборки

На сотне человек разница исчезает — там ДОВЕРИТ работать можно. На типичной дипломной выборке в 15–30 человек она даёт результат, который рецензент вправе назвать ошибкой.

Как записать интервал в диплом

Границы приводят рядом со средним, обязательно с указанием уровня надёжности:

Средний уровень ситуативной тревожности в группе составил 47,40 балла (95% ДИ: 45,38–49,42).

Развёрнутый вариант для текста главы:

Среднее значение ситуативной тревожности — 47,40 ± 4,32 балла (M ± σ). Границы 95% доверительного интервала для среднего составили от 45,38 до 49,42 балла, что говорит о достаточной точности оценки при объёме выборки n = 20.

Если интервал нужен для вывода о различиях с нормативом:

Нормативное значение 44 балла не входит в 95% доверительный интервал [45,38; 49,42], следовательно, средний уровень тревожности в обследованной группе достоверно выше нормы (p < 0,05).

Округляйте границы до того же числа знаков, что и среднее — обычно до двух.

Что влияет на ширину интервала

Три вещи, и все они видны прямо в формуле t · σ / √n.

Объём выборки n. Главный рычаг. Корень в знаменателе означает, что для сужения интервала вдвое выборку нужно увеличить вчетверо. Плюс с ростом n уменьшается сам t-множитель.

Разброс данных σ. Чем однороднее группа, тем у́же интервал. Иногда разброс раздут одним выбросом — стоит проверить крайние значения до расчёта.

Уровень доверия. За уверенность платят шириной: 90% ДИ на наших данных — [45,73; 49,07], 95% — [45,38; 49,42], 99% — [44,64; 50,16]. Стандарт студенческих работ — 95%.

Широкий интервал — не ошибка расчёта, а честный сигнал: данных мало либо группа разнородная.

Всё считается в готовом файле

Если считать нужно не одно число, а всю статистику для третьей главы — есть готовая книга Excel. Вы вставляете свои данные в жёлтые ячейки, и всё считается само.

Интервал считается на листе «7. Доверительный интервал»: t-множитель подставляется по вашему n автоматически, уровень доверия переключается между 90, 95 и 99%, а внизу листа стоит готовая формулировка для работы.

Три способа посчитать — что выбрать

← листайте вправо, чтобы сравнить →

Границы получатся одинаковые везде, где взят t-множитель. Разница в том, сколько работы остаётся на вас и легко ли ошибиться.

Калькулятор делает то, чего формулы Excel не умеют в принципе: проверяет нормальность распределения по Шапиро-Уилку, находит выбросы, раздувающие σ, и выдаёт готовый рисунок с усами интервала для третьей главы.

Частые ошибки

• Считают ДОВЕРИТ на выборке из 15–30 человек. Функция берёт z = 1,96, интервал получается у́же настоящего на 5–15%.Соберите формулу через СТЬЮДРАСПОБР(0,05;n-1) или возьмите ДОВЕРИТ.СТЬЮДЕНТ.
• Пишут «в интервал попали 95% студентов». Интервал описывает точность оценки среднего, а не разброс людей.Про разброс — запись M ± σ, про точность оценки — 95% ДИ.
• Делят σ не на корень из n, а на n. Ошибка среднего выходит в 4–5 раз меньше, интервал схлопывается.В знаменателе только КОРЕНЬ(СЧЁТ(...)).
• Подставляют в СТЬЮДРАСПОБР сам n вместо n − 1. Число степеней свободы всегда на единицу меньше объёма выборки.Пишите СЧЁТ(B2:B21)-1 — тогда при добавлении данных ничего не сломается.
• Приводят границы без уровня надёжности. «Интервал 45,38–49,42» непонятен: 90 это процентов или 99.Всегда указывайте «95% ДИ» рядом с числами.
• Строят интервал на явно ненормальных данных. При сильном перекосе или выбросе границы смещаются.Сначала проверьте распределение, при перекосе описывайте группу медианой и квартилями.

Частые вопросы

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

Готовой функции «сразу две границы» нет. Правильный путь — =СРЗНАЧ(...)±СТЬЮДРАСПОБР(0,05;n-1)*СТАНДОТКЛОН(...)/КОРЕНЬ(СЧЁТ(...)). Функция ДОВЕРИТ даёт только половину ширины и берёт z = 1,96, что подходит лишь для больших выборок.

Чем ДОВЕРИТ отличается от ДОВЕРИТ.СТЬЮДЕНТ

Множителем. ДОВЕРИТ умножает ошибку среднего на 1,96, ДОВЕРИТ.СТЬЮДЕНТ — на t-значение для ваших степеней свободы. На n = 20 это 1,89 против 2,02. Для студенческой работы нужна вторая.

Excel пишет #ИМЯ? на формулу СТЬЮДРАСПОБР

Имя функции набрано в другой раскладке или файл открыт в английском Excel. Проверьте написание и попробуйте TINV(0,05;19). Ошибка #ЧИСЛО! означает неверные аргументы: вероятность должна быть от 0 до 1, а степени свободы — не меньше единицы.

Как построить доверительный интервал на диаграмме

Постройте столбчатую диаграмму средних, затем «Конструктор → Добавить элемент диаграммы → Предел погрешностей → Дополнительные параметры». В настройках выберите «Пользовательская» и укажите столбец с погрешностью — в нашем примере это 2,02. Усы появятся на столбцах.

Нужен ли доверительный интервал, если уже посчитан t-критерий

Не обязательно, но полезно. p-значение говорит только «значимо или нет», а интервал показывает, насколько велик эффект и насколько точно вы его измерили. Многие научные руководители просят приводить оба числа.

Короткий алгоритм

1. Соберите значения в один столбец — без заголовков и пустых строк внутри.
2. Посчитайте =СРЗНАЧ(диапазон), =СТАНДОТКЛОН(диапазон) и =СЧЁТ(диапазон).
3. Ошибка среднего: отклонение разделите на =КОРЕНЬ(СЧЁТ(диапазон)).
4. t-множитель: =СТЬЮДРАСПОБР(0,05;СЧЁТ(диапазон)-1).
5. Погрешность = t × ошибка среднего; границы = среднее ± погрешность.
6. Запишите «95% ДИ: нижняя–верхняя» с двумя знаками после запятой.

Или пропустите все шаги: скачайте файл выше либо вставьте данные в калькулятор.

Что ещё почитать

• Доверительный интервал простыми словами — что он означает и как его толковать.
• Среднее значение в Excel — центр интервала, функция СРЗНАЧ и её ловушки.
• Стандартное отклонение в Excel — откуда берётся σ в формуле.
• Описательная статистика в Excel — все показатели выборки разом.
• Калькулятор описательной статистики — среднее, отклонение и интервал в одном расчёте.

Если не уверены, какой интервал нужен именно в вашей работе, загляните в базу методов или напишите нам — поможем с расчётами.