Дисперсионный анализ (ANOVA) в Excel: пошаговый разбор

Три методики, три курса, три группы — и вопрос научрука: «А различия достоверны?» Критерий Стьюдента здесь не подходит, а функции вроде =ДИСПАНАЛИЗ() в Excel не существует.

Считать придётся через надстройку «Пакет анализа». Разберём весь путь: от кнопки на ленте до строки, которая уйдёт в третью главу.

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

Когда нужен дисперсионный анализ

Правило простое: групп три и больше — берите ANOVA. Две группы — это критерий Стьюдента, и никакой дисперсионный анализ там не нужен.

• Три и более независимые группы — в каждой свои люди, а не одни и те же в трёх замерах.
• Один фактор — группы различаются по одному признаку: методика, курс, квалификация.
• Количественные данные — сантиметры, секунды, баллы шкалы, а не «да/нет».
• Распределение близко к нормальному, дисперсии в группах примерно равны.

Соблазн понятный: посчитать три t-критерия попарно и выбрать самый красивый результат. Так делать нельзя.

Каждое сравнение с порогом 0,05 несёт 5% риска увидеть различие там, где его нет. Сравнений три — риски складываются, и вероятность хотя бы одного ложного вывода вырастает до 14%. При пяти группах сравнений уже десять, а риск — 40%.

ANOVA решает задачу одним тестом: он проверяет все группы разом и сохраняет заявленный уровень значимости 5%.

Как включить «Пакет анализа»

Готовой функции ANOVA в Excel нет — только надстройка. Путь в Windows: Файл → Параметры → Надстройки → «Управление: Надстройки Excel» → Перейти → галочка «Пакет анализа» → ОК.

На Mac иначе: Средства (Инструменты) → Надстройки для Excel → «Пакет анализа» → ОК. В Excel для Mac 2011 и старше, в веб-версии Excel и в Google Таблицах надстройки нет вовсе — там ANOVA не посчитать никак.

После «ОК» на вкладке «Данные» справа появится кнопка «Анализ данных». Подробный разбор с картинками — в статье «Пакет анализа данных в Excel».

Однофакторный ANOVA: пошагово

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

Результаты прыжка в длину с места после трёх месяцев подготовки (n = 24)

Данные лежат в трёх соседних столбцах A, B, C, в первой строке — названия групп, значения со 2-й по 9-ю строку.

1. Данные → Анализ данных → Однофакторный дисперсионный анализ → ОК.
2. Входной интервал — выделите A1:C9 вместе с заголовками.
3. Группирование — «по столбцам» (каждая группа в своём столбце).
4. Метки в первой строке — галочка, раз захватили названия.
5. Альфа — оставьте 0,05.
6. Параметры вывода — «Новый рабочий лист».

Excel создаст лист с двумя таблицами: «ИТОГИ» — счёт, сумма, среднее и дисперсия по каждой группе, и «Дисперсионный анализ» — та самая ANOVA-таблица.

Как читать вывод

Вот что выдаёт Excel на наших данных.

Таблица «Дисперсионный анализ» из вывода Пакета анализа

Разберём по столбцам.

SS — сумма квадратов, то есть разброс. «Между группами» — насколько разошлись средние трёх групп. «Внутри групп» — случайный разброс людей внутри своей группы. Вместе они дают общий разброс: 946,58 + 549,38 = 1495,96.

df — степени свободы. Между группами это число групп минус один (3 − 1 = 2), внутри групп — число человек минус число групп (24 − 3 = 21).

MS — средний квадрат, то есть SS, делённая на свои df. Это уже дисперсии: межгрупповая 473,29 и внутригрупповая 26,16.

F — отношение этих двух дисперсий: 473,29 / 26,16 = 18,09. Смысл прямой: разброс между группами в 18 раз крупнее случайного шума внутри них.

P-значение — вероятность получить такое F случайно. У нас 0,000027, то есть меньше 0,001. Порог обычный: p < 0,05 — различия достоверны.

F критическое — табличное значение для ваших степеней свободы. Наше F = 18,09 больше 3,47, вывод тот же. Достаточно смотреть на одно из двух: либо F > F крит, либо p < 0,05 — эти условия всегда совпадают.

Что делать после ANOVA: между какими группами различия

Вот главное ограничение метода. ANOVA сказал «различия есть», но не сказал, между какими группами. Три группы — три пары, и отличаться может как одна пара, так и все.

Ответ дают апостериорные (постхок) сравнения: критерий Тьюки или поправка Бонферрони. В «Пакете анализа» их нет вообще — ни в одном пункте меню.

Вот что даёт Тьюки на наших данных.

Картина сразу другая: круговая тренировка от традиционной достоверно не отличается, весь эффект дала плиометрика. Без постхока этот вывод не сделать — а именно он и есть результат работы.

Если групп две — есть готовый файл

Честно: дисперсионного анализа в нашем файле «Статистика для диплома» нет. Он рассчитан на сравнение двух выборок — там t-критерий Стьюдента во всех трёх вариантах и ранговые критерии. Для трёх и более групп нужен калькулятор.

Файл пригодится и тем, кто считает ANOVA: описательная статистика по каждой группе, проверка распределения и диаграммы для главы 3 в нём есть. Не хватает только самого дисперсионного анализа и постхока.

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

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

Само F в Пакете анализа и в калькуляторе получится одинаковым. Различается то, что остаётся делать руками: постхок, размер эффекта, проверка условий и оформление.

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

В третьей главе результат ANOVA приводят четырьмя числами: F, две степени свободы в скобках, p и размер эффекта η² (эта-квадрат). Считается η² просто: SS между группами делим на SS итого — 946,58 / 1495,96 = 0,63.

Однофакторный дисперсионный анализ выявил достоверные различия результата прыжка в длину с места между тремя группами: F(2; 21) = 18,09; p < 0,001; η² = 0,63.

Дальше — обязательно результат постхока, иначе вывод повисает в воздухе:

Апостериорные сравнения по критерию Тьюки показали, что плиометрическая группа превзошла традиционную (Δ = 15,25 см; p < 0,001) и группу круговой тренировки (Δ = 9,38 см; p = 0,004). Различия между круговой и традиционной методиками недостоверны (Δ = 5,88 см; p = 0,078).

Значение η² = 0,63 означает: 63% всей изменчивости результата объясняется методикой подготовки. Ориентиры: 0,01 — слабый эффект, 0,06 — средний, 0,14 и выше — сильный.

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

• Считают три попарных t-критерия вместо одного ANOVA. Риск ложного вывода растёт с 5% до 14%, а при пяти группах — до 40%.Три и более групп — один общий тест ANOVA, затем постхок.
• Останавливаются на «p < 0,05, различия есть». ANOVA не говорит, какие группы отличаются, и вывод получается бессодержательным.Добавьте критерий Тьюки — он покажет конкретные пары.
• Выбирают «Двухфакторный дисперсионный анализ» из списка. Пункт стоит рядом, но он для двух факторов сразу, и вывод будет другой.Один фактор (методика, курс) — берите «Однофакторный дисперсионный анализ».
• Складывают все данные в один столбец с кодом группы. Excel ждёт каждую группу в своём столбце и посчитает ерунду.Разложите значения по столбцам A, B, C — по одному на группу.
• Гоняют ANOVA на баллах анкеты с ненормальным распределением. Метод параметрический, на порядковой шкале выводы шаткие.Проверьте нормальность, при отклонении берите Краскела-Уоллиса.
• Правят данные и переписывают старый лист вывода. Пакет выдаёт числа, а не формулы, — они остаются от первого запуска.Удалите старый лист и запустите расчёт заново.

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

Есть ли в Excel формула дисперсионного анализа

Нет. Функции для ANOVA в Excel не предусмотрено — только надстройка «Пакет анализа». Отдельные куски можно собрать вручную через СУММКВРАЗН и FРАСП, но это долго и легко ошибиться в степенях свободы.

Можно ли посчитать ANOVA в Google Таблицах

Штатными средствами — нет, «Пакета анализа» там не существует. Есть функция FTEST, но она сравнивает дисперсии двух выборок, а не средние трёх групп. Проще посчитать онлайн.

Что делать, если F меньше F критического

Значит различия недостоверны: групповые средние разошлись не сильнее случайного разброса. Так и пишите — «различия между группами статистически незначимы (p > 0,05)». Постхок в этом случае не проводят.

Обязательно ли делать постхок

Если ANOVA дал p < 0,05 — да. Без него в работе останется только «где-то есть различия», и первый же вопрос на защите будет «между какими группами». В Excel постхока нет, считайте калькулятором Тьюки.

Подходит ли ANOVA для замеров «до, через месяц, через три»

Нет, это одни и те же люди — нужен дисперсионный анализ с повторными измерениями или критерий Фридмана. Обычный однофакторный ANOVA рассчитан на независимые группы.

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

1. Убедитесь, что групп три и больше, а люди в них разные.
2. Включите «Пакет анализа»: Файл → Параметры → Надстройки → Перейти → галочка.
3. Разложите данные по столбцам — по одной группе в столбец, названия в первой строке.
4. Данные → Анализ данных → Однофакторный дисперсионный анализ → интервал → «Метки в первой строке» → новый лист.
5. Из таблицы вывода возьмите F, обе df и P-значение; посчитайте η² = SS между / SS итого.
6. Проведите постхок по Тьюки — без него неизвестно, какие группы различаются.

Или пропустите шаги со второго по шестой: вставьте данные в калькулятор ANOVA — он посчитает и таблицу, и постхок, и размер эффекта.

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

• Дисперсионный анализ (ANOVA): полное руководство — теория, формулы и условия применения.
• ANOVA или t-критерий — как выбрать между ними и почему две группы это не ANOVA.
• Множественные сравнения и поправка Бонферрони — что делать после значимого F.
• Пакет анализа данных в Excel — все инструменты надстройки и как её включить.
• Критерий Стьюдента в Excel — если групп всё-таки две.

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