Дисперсия в Excel: формула ДИСП, ДИСПР и готовый файл

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

Разберём по порядку — формулу, развилку ДИСП/ДИСПР и разницу между дисперсией и дисперсионным анализом.

Что такое дисперсия

Каждое значение чем-то отличается от среднего. Эта разница — отклонение. Если сложить все отклонения, получится ноль: плюсы и минусы сократят друг друга, и мера разброса выйдет бесполезной.

Поэтому отклонения возводят в квадрат — так они все становятся положительными — а потом усредняют.

Дисперсия — это средний квадрат отклонений от среднего. Чем она больше, тем сильнее значения разбегаются от центра.

Обозначают её σ² или s² — с квадратом. Квадрат не для красоты: раз отклонения возводили в квадрат, то и единицы возвелись тоже. Мерили баллы — дисперсия в «баллах в квадрате», мерили секунды — в «квадратных секундах».

Прочитать такое число по-человечески нельзя, поэтому в выводах работы приводят корень из дисперсии — стандартное отклонение. Откуда берётся формула, подробно разобрано в статье «Стандартное отклонение и дисперсия простыми словами».

Формула в Excel

Пусть значения лежат в столбце B со второй по двадцать первую строку:

=ДИСП(B2:B21)

Рядом полезно посчитать среднее — без него дисперсию не с чем сопоставить:

=СРЗНАЧ(B2:B21)

В английском Excel это VAR и AVERAGE.

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

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

Что получается на этих двадцати значениях

Проверьте себя: 4,32 — это ровно корень из 18,67. Если два числа не сходятся, где-то захвачен лишний диапазон.

Обратный переход тоже работает: =СТАНДОТКЛОН(B2:B21)^2 даст ту же дисперсию 18,67. Отдельная функция для этого не нужна, но иногда так удобнее проверять расчёт.

ДИСП или ДИСПР — какую выбрать

Это та же развилка, что и у стандартного отклонения в Excel: Excel не знает, выборка у вас или вся совокупность, и спрашивает об этом именем функции.

ДИСП — по выборке. Вы обследовали часть, а выводы делаете шире: измерили 20 студентов, а пишете о первокурсниках вообще. Сумма квадратов делится на n − 1. В дипломе почти всегда нужна она.

ДИСПР — по генеральной совокупности. Годится, только если вы измерили всех, о ком говорите: всю команду, весь класс, всех сотрудников отдела. Делит на n.

Четыре функции дисперсии в Excel и когда какая нужна

Функции с точкой в имени считают ровно то же самое — это переименование 2010 года. Запомнить пары просто: В — выборка, Г — генеральная совокупность. Старый Excel и некоторые версии Google Таблиц на них отвечают #ИМЯ?, поэтому в файле, который вы отдаёте руководителю, безопаснее оставить ДИСП.

Есть ещё ДИСПА и ДИСПРА (VARA, VARPA) — они считают текст за ноль, а «ИСТИНА» за единицу. В студенческих расчётах они не нужны и только портят результат, если в столбец затесался текст.

Дисперсия и дисперсионный анализ — это разное!

Самая частая путаница в теме. Общее у них — только слово «дисперсия».

Дисперсия — мера разброса одной выборки. Результат — одно число: 18,67. Считается функцией ДИСП, отвечает на вопрос «насколько данные раскиданы вокруг среднего».

Дисперсионный анализ (ANOVA) — метод сравнения трёх и более групп. Результат — F-критерий и p-значение. Отвечает на вопрос «различаются ли группы между собой». Дисперсия внутри него — лишь рабочий материал: метод сопоставляет разброс между группами с разбросом внутри них, отсюда и название.

Понять, что от вас хотят, помогает формулировка задания:

• «Рассчитайте дисперсию показателя» — нужна функция ДИСП, одна ячейка, одно число.
• «Оцените однородность выборки» — тоже дисперсия, а точнее производный от неё коэффициент вариации.
• «Сравните три группы», «примените ANOVA», «оцените влияние фактора» — это дисперсионный анализ, отдельный метод с F и p.

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

Формулы можно не вбивать: возьмите готовый файл

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

Дисперсия считается на листе «5. Отклонение и дисперсия»: там рядом стоят вариант по выборке и вариант по генеральной совокупности, стандартное отклонение и готовая формулировка для работы. Дисперсионного анализа в файле нет — для него нужны три группы, это отдельная задача.

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

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

Число везде получится одно и то же — 18,67. Разница в том, сколько работы остаётся на вас после того, как это число появилось.

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

Что писать в дипломе

Главное правило: в таблицах и выводах приводят σ, а не σ². «Тревожность 47,40 ± 18,67 квадратных балла» — бессмыслица, которую заметит любой рецензент.

Стандартная запись:

Средний уровень ситуативной тревожности в группе составил 47,40 ± 4,32 балла (M ± σ).

Дисперсия при этом остаётся промежуточным расчётом — её не прячут, но и не выносят в итоговую таблицу. Есть три случая, когда она нужна прямо в тексте:

• Так требует методичка кафедры. Некоторые вузы просят колонку σ² в таблице описательной статистики — тогда добавьте её рядом с σ, а не вместо неё.
• Вы проверяете равенство дисперсий. Перед t-критерием для независимых выборок и перед ANOVA проверяют, одинаков ли разброс в группах — критериями Левена или Фишера. Там дисперсия и есть предмет разговора.
• Тема работы — сама изменчивость. В спортивных исследованиях иногда изучают не уровень показателя, а его стабильность: разброс времени реакции, вариативность техники.

Если приводите дисперсию, обязательно укажите, по какой формуле она получена — по выборочной с делением на n − 1.

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

• Берут ДИСПР вместо ДИСП. Разброс занижается — на выборке из 20 человек на 5%, на выборке из 10 уже на 10%.В дипломе почти всегда нужна ДИСП — вариант «по выборке».
• Пишут дисперсию туда, где ждут отклонение. «47,40 ± 18,67» выглядит как гигантский разброс, хотя реальный разброс 4,32.В строку «M ± …» ставьте σ. Дисперсию — только отдельной колонкой с подписью σ².
• Считают дисперсию, а в задании — дисперсионный анализ. Функция ДИСП не сравнивает группы и p-значения не даёт.Три и более групп — это ANOVA, отдельный метод.
• Захватывают в диапазон лишние ячейки. Номер испытуемого или итоговая строка попадут в расчёт и раздуют дисперсию в разы.Выделяйте только столбец со значениями, без заголовка и итогов.
• Копируют данные из Word и получают текст. ДИСП молча пропустит текстовые ячейки, и дисперсия посчитается по трём числам из двадцати.Числа выровнены по левому краю — значит это текст. Лечится через «Данные → Текст по столбцам».

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

Какую функцию дисперсии выбрать для курсовой и диплома

ДИСП (или ДИСП.В — это то же самое). Вы обследовали выборку и делаете выводы шире неё, а значит нужна формула с делением на n − 1.

Чем ДИСП отличается от ДИСП.В

Ничем. ДИСП.В появилась в Excel 2010 как новое имя старой ДИСП, обе считают выборочную дисперсию. Старая работает во всех версиях, поэтому в файлах для руководителя надёжнее она.

Дисперсия и дисперсионный анализ — это одно и то же

Нет. Дисперсия — мера разброса одной выборки, одно число. Дисперсионный анализ — метод сравнения трёх и более групп, его результат F и p. Совпадает только слово в названии.

Почему дисперсия такая большая

Потому что она в квадратных единицах: при разбросе в 4,32 балла дисперсия равна 18,67. Извлеките корень — число сразу станет понятным. Ещё вариант — в диапазон попали лишние ячейки.

Можно ли посчитать дисперсию без функции ДИСП

Да: =СТАНДОТКЛОН(B2:B21)^2 даёт тот же результат. Обратный ход тоже работает — =КОРЕНЬ(ДИСП(B2:B21)) вернёт стандартное отклонение.

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

1. Соберите значения в один столбец — без заголовков и пустых строк внутри.
2. Введите =ДИСП(диапазон) — это σ² по выборке.
3. Рядом посчитайте =СРЗНАЧ(диапазон) и =СТАНДОТКЛОН(диапазон).
4. Убедитесь, что σ — это корень из σ². Не сходится — проверьте диапазон.
5. В таблицу диплома вынесите «M ± σ», дисперсию оставьте промежуточным расчётом.

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

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

• Стандартное отклонение в Excel — соседняя формула и та же развилка «выборка или совокупность».
• Стандартное отклонение и дисперсия простыми словами — откуда берётся формула и что такое n − 1.
• Руководство по дисперсионному анализу (ANOVA) — если в задании всё-таки сравнение групп.
• Как посчитать статистику в Excel: формулы — остальные показатели для третьей главы.
• Описательная статистика в дипломе — как оформить итоговую таблицу.

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