Доверительный интервал в Excel — по шагам, с графиком и отчётом
Доверительный интервал для среднего по распределению Стьюдента: x̄ ± t·s/√n. Полуширина проверяется функцией ДОВЕРИТ.СТЬЮДЕНТ.
Функции Excel: ДОВЕРИТ.СТЬЮДЕНТ, СТЬЮДЕНТ.ОБР.2Х. В примере — учебные данные; замените их своими.
Другие расчёты
Вопросы
Это готовая лабораторная?
Нет. Формулник считает и оформляет: книга 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 — уже, чем на самом деле.
По шагам
- Сложите значения в один столбец без пустых строк и подписей внутри диапазона.
- Найдите среднее
СРЗНАЧи выборочное стандартное отклонениеСТАНДОТКЛОН.В(не.Г: оно для всей совокупности и занижает разброс). - Выберите α: для вероятности 0,95 это 0,05, для 0,99 — 0,01. В функции пишется именно α, а не 0,95.
- Посчитайте полуширину
ДОВЕРИТ.СТЬЮДЕНТ— для учебных измерений почти всегда нужна она. - Нижняя граница —
=СРЗНАЧ(A2:A11)-ДОВЕРИТ.СТЬЮДЕНТ(0,05;СТАНДОТКЛОН.В(A2:A11);СЧЁТ(A2:A11)), верхняя — то же со знаком плюс. - Запишите результат с согласованными знаками: 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,01 | z = 2,576 |
Частые ошибки
- В первый аргумент вписано 0,95 — функции ждут уровень значимости α = 0,05. С 0,95 вы получите интервал всего на 5 % доверия — очень узкий.
- Получена полуширина, а записана как интервал —
ДОВЕРИТ.СТЬЮДЕНТвозвращает только ±Δ. Границы — среднее минус и плюс это число. - ДОВЕРИТ.НОРМ на 8 измерениях — при малой выборке нормальный закон занижает интервал на 15–30 %. Для лабораторной берите Стьюдента.
- Результат 5,0034 ± 0,1306 — лишние знаки создают видимость точности. Пишите 5,00 ± 0,13.
Вопросы и ответы
Как найти 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 повторов.