Описать задачу
Формулник › Для учёбы › Расчёты по шагам › Доверительный интервал

Доверительный интервал в Excel — по шагам, с графиком и отчётом

Редакция Формулника · обновлено · как мы проверяем

Доверительный интервал для среднего по распределению Стьюдента: x̄ ± t·s/√n. Полуширина проверяется функцией ДОВЕРИТ.СТЬЮДЕНТ.

0,95 или 0,99

Функции Excel: ДОВЕРИТ.СТЬЮДЕНТ, СТЬЮДЕНТ.ОБР.2Х. В примере — учебные данные; замените их своими.

Считаем и оформляем — работу не пишем. Вывод в отчёте — заготовка: допишите его своими словами.

Другие расчёты

Метод наименьших квадратов (МНК)Линия тренда и прогноз (ЛИНЕЙН)Коэффициент корреляцииМатрица корреляцийПарная линейная регрессияОписательная статистикаДисперсия и стандартное отклонениеt-критерий СтьюдентаПогрешности измеренийГрафик функцииСистема линейных уравнений (матрицы)Подбор параметраИнтервальный ряд и гистограмма

Вопросы

Это готовая лабораторная?

Нет. Формулник считает и оформляет: книга Excel с формулами по шагам, график и отчёт с таблицами. Вывод — заготовка: допишите своими словами, что показали расчёты. Работу за вас мы не пишем.

Можно ли верить числам?

Каждую формулу книги пересчитывают два движка — LibreOffice и второй независимый, — и результат сверяется с расчётом по определению. Если хоть одно число не сходится, файл не выдаётся.

Как вставить данные?

Скопируйте столбцы из Excel или Word и вставьте: по столбцу на величину, по строке на измерение. Запятая или точка в дробях — неважно. Первая строка с названиями — по желанию.

Откроется ли в моём Excel?

Да: Excel 2010–365, LibreOffice, Р7-Офис и МойОфис. Формулы обычные, без макросов; в листе «По шагам» каждая формула записана и по-русски.

ДОВЕРИТ.СТЬЮДЕНТ и ДОВЕРИТ.НОРМ: доверительный интервал в Excel

Полуширину доверительного интервала для среднего по выборке считает =ДОВЕРИТ.СТЬЮДЕНТ(0,05;СТАНДОТКЛОН.В(A2:A11);СЧЁТ(A2:A11)): 0,05 — это 1 минус доверительная вероятность 0,95. Нижняя граница — среднее минус полуширина, верхняя — среднее плюс. ДОВЕРИТ.НОРМ с теми же аргументами годится, когда стандартное отклонение генеральной совокупности известно или выборка большая (от 30–50 значений).

Пример: 10 измерений 4,8; 5,1; 5,0; 4,9; 5,3; 5,2; 4,7; 5,0; 5,1; 4,9 — среднее 5,00, s = 0,183, полуширина по Стьюденту 0,131, интервал от 4,87 до 5,13. По нормальному закону вышло бы 0,113 — уже, чем на самом деле.

По шагам

  1. Сложите значения в один столбец без пустых строк и подписей внутри диапазона.
  2. Найдите среднее СРЗНАЧ и выборочное стандартное отклонение СТАНДОТКЛОН.В (не .Г: оно для всей совокупности и занижает разброс).
  3. Выберите α: для вероятности 0,95 это 0,05, для 0,99 — 0,01. В функции пишется именно α, а не 0,95.
  4. Посчитайте полуширину ДОВЕРИТ.СТЬЮДЕНТ — для учебных измерений почти всегда нужна она.
  5. Нижняя граница — =СРЗНАЧ(A2:A11)-ДОВЕРИТ.СТЬЮДЕНТ(0,05;СТАНДОТКЛОН.В(A2:A11);СЧЁТ(A2:A11)), верхняя — то же со знаком плюс.
  6. Запишите результат с согласованными знаками: 5,00 ± 0,13 при P = 0,95. Погрешность округляют до 1–2 значащих цифр, среднее — до того же разряда.

Если интервал нужен для доли, а не для среднего

Например, 62 % опрошенных из 200 ответили «да». Если доля в B1 (0,62), а объём в B2, полуширина при 95 % — =НОРМ.СТ.ОБР(0,975)*КОРЕНЬ(B1*(1-B1)/B2), около 0,067. Формула приближённая и работает, когда и «да», и «нет» больше 5–10 ответов.

Если это погрешность прямых измерений в физике

Случайная погрешность — это та же полуширина по Стьюденту. Если у прибора есть своя погрешность, её складывают со случайной в квадратах: Δ = √(Δсл² + Δпр²). Формулник ведёт такой расчёт по шагам с подписями, откуда взялась каждая величина, — останется вписать вывод.

Если старая версия Excel

До Excel 2010 была только функция ДОВЕРИТ — она считает по нормальному закону, как ДОВЕРИТ.НОРМ. Интервал по Стьюденту в ней собирают вручную: СТЬЮДЕНТ.ОБР.2Х или старая СТЬЮДРАСПОБР, умноженная на s/√n.

Какую функцию выбрать

СитуацияФункцияМножитель
Выборка до 30 значений, σ неизвестнаДОВЕРИТ.СТЬЮДЕНТt при n − 1 степени свободы
σ известна (паспорт прибора, большой архив)ДОВЕРИТ.НОРМz = 1,96 при 0,95
Большая выборка, больше 50любая — разница меньше 3 %t ≈ z
Доля (процент ответов)формула вручную=НОРМ.СТ.ОБР(0,975)
Вероятность 0,99те же, α = 0,01z = 2,576

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

Вопросы и ответы

Как найти 95 доверительный интервал в Excel?

Посчитайте среднее, затем полуширину ДОВЕРИТ.СТЬЮДЕНТ с α = 0,05, и отнимите и прибавьте её к среднему. Для 99 % замените 0,05 на 0,01.

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

Множителем: нормальный закон всегда берёт 1,96, а Стьюдент — t, который растёт при маленькой выборке (2,262 при 10 измерениях). Поэтому интервал Стьюдента шире и честнее для малых n.

Почему доверительный интервал получился широким?

Из-за большого разброса или малого числа измерений: полуширина убывает как 1/√n. Чтобы сузить интервал вдвое, измерений нужно примерно вчетверо больше.

Что значит доверительная вероятность 0,95?

Если повторять эксперимент много раз, интервалы, построенные так же, накроют истинное среднее в 95 % случаев. Это не вероятность того, что среднее лежит именно в вашем интервале.

Можно ли построить интервал по одному измерению?

Нет: при n = 1 стандартное отклонение не определено, функция вернёт ошибку #ДЕЛ/0!. Нужно хотя бы 3–5 повторов.