Статистика в Excel: все формулы для диплома в одном месте

У вас есть таблица с баллами или замерами, а в дипломе нужно «посчитать статистику и сравнить группы». Покупать SPSS ради этого не нужно: почти всё делает обычный Excel.

Это путеводитель по разделу. Ниже — какая формула решает вашу задачу, где чаще всего ошибаются и как получить готовые таблицы вообще без формул.

Начните с вопроса: что именно нужно посчитать

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

Описать выборку

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

• Среднее значение — функция СРЗНАЧ и три ловушки, из-за которых среднее выходит неверным.
• Медиана и мода — когда медиана честнее среднего и почему МОДА выдаёт ошибку.
• Стандартное отклонение — СТАНДОТКЛОН или СТАНДОТКЛОНП: здесь ошибаются чаще всего.
• Дисперсия — и чем она отличается от дисперсионного анализа.
• Ошибка среднего — та самая величина после знака ± в записи M ± m.
• Коэффициент вариации — однородна ли выборка.
• Квартили — КВАРТИЛЬ.ВКЛ и КВАРТИЛЬ.ИСКЛ дают разные числа.
• Описательная статистика целиком — готовый набор всех формул одним блоком.

Сравнить группы

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

• t-критерий Стьюдента — функция ТТЕСТ и её четвёртый аргумент, который ставят наугад.
• U Манна-Уитни — для баллов теста и двух разных групп. Функции в Excel нет, считаем через ранги.
• T Вилкоксона — для повторных замеров. Нулевые разности отбрасываются, и чем меньше T, тем сильнее сдвиг.
• χ² Пирсона — когда данные это количество людей в категориях, а не измерения.
• Дисперсионный анализ — если групп три и больше. Попарные t-тесты вместо него — грубая ошибка.

Найти связь между показателями

• Корреляция — КОРРЕЛ считает только Пирсона, Спирмена собираем через ранги.
• Регрессия — уравнение и R². В НАКЛОН сначала идёт Y, потом X.

Проверить условия и построить рисунки

• Нормальное распределение — от него зависит выбор критерия. Шапиро-Уилка в Excel нет, проверяют по асимметрии и эксцессу.
• Доверительный интервал — функция ДОВЕРИТ не годится для студенческих выборок.
• Гистограмма — в Excel так называют и распределение частот, и обычную столбчатую диаграмму.
• Ящик с усами — в Excel 2016 и новее есть готовый тип, в старых версиях строят вручную.

Справочные материалы

• Справочник статистических функций — все функции с русскими и английскими именами.
• Пакет анализа данных — надстройку надо включать вручную, а в Excel для Mac и в веб-версии её нет.

Всю статистику можно посчитать без формул

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

<!-- вкладки листов -->
<rect x="46" y="68" width="80" height="19" rx="4" fill="#2f5597"/>
<text x="86" y="81" fill="#ffffff" font-family="system-ui, sans-serif" font-size="10" font-weight="600" text-anchor="middle">Мои данные</text>
<rect x="130" y="68" width="80" height="19" rx="4" fill="#dbe5f1"/>
<text x="170" y="81" fill="#2f5597" font-family="system-ui, sans-serif" font-size="10" text-anchor="middle">Результаты</text>
<rect x="214" y="68" width="66" height="19" rx="4" fill="#dbe5f1"/>
<text x="247" y="81" fill="#2f5597" font-family="system-ui, sans-serif" font-size="10" text-anchor="middle">Графики</text>

<!-- таблица результатов -->
<rect x="46" y="98" width="128" height="20" fill="#f8fafc" stroke="#dbe2ea"/>
<text x="54" y="112" fill="#475569" font-family="system-ui, sans-serif" font-size="10">Количество (n)</text>
<rect x="174" y="98" width="60" height="20" fill="#f8fafc" stroke="#dbe2ea"/>
<text x="204" y="112" fill="#0f172a" font-family="system-ui, sans-serif" font-size="10" text-anchor="middle">20</text>

<rect x="46" y="118" width="128" height="20" fill="#f8fafc" stroke="#dbe2ea"/>
<text x="54" y="132" fill="#475569" font-family="system-ui, sans-serif" font-size="10">Среднее (M)</text>
<rect x="174" y="118" width="60" height="20" fill="#f8fafc" stroke="#dbe2ea"/>
<text x="204" y="132" fill="#0f172a" font-family="system-ui, sans-serif" font-size="10" text-anchor="middle">47,40</text>

<rect x="46" y="138" width="128" height="22" fill="#dcfce7" stroke="#22c55e" stroke-width="1.5"/>
<text x="54" y="153" fill="#166534" font-family="system-ui, sans-serif" font-size="10" font-weight="700">Отклонение (σ)</text>
<rect x="174" y="138" width="60" height="22" fill="#dcfce7" stroke="#22c55e" stroke-width="1.5"/>
<text x="204" y="153" fill="#166534" font-family="system-ui, sans-serif" font-size="11" font-weight="700" text-anchor="middle">4,32</text>

<rect x="46" y="160" width="128" height="20" fill="#f8fafc" stroke="#dbe2ea"/>
<text x="54" y="174" fill="#475569" font-family="system-ui, sans-serif" font-size="10">Дисперсия (σ²)</text>
<rect x="174" y="160" width="60" height="20" fill="#f8fafc" stroke="#dbe2ea"/>
<text x="204" y="174" fill="#0f172a" font-family="system-ui, sans-serif" font-size="10" text-anchor="middle">18,67</text>

<!-- диаграмма -->
<line x1="250" y1="176" x2="344" y2="176" stroke="#cbd5e1"/>
<rect x="256" y="140" width="16" height="36" rx="2" fill="#94a3b8"/>
<rect x="278" y="122" width="16" height="54" rx="2" fill="#2f5597"/>
<rect x="300" y="148" width="16" height="28" rx="2" fill="#94a3b8"/>
<rect x="322" y="130" width="16" height="46" rx="2" fill="#2f5597"/>

<!-- вывод -->
<rect x="46" y="192" width="292" height="36" rx="6" fill="#eaf4ec" stroke="#8cc49a"/>
<circle cx="64" cy="210" r="9" fill="#1e7b34"/>
<path d="M60 210l3 3 5.5-6" stroke="#ffffff" stroke-width="2" fill="none" stroke-linecap="round" stroke-linejoin="round"/>
<text x="80" y="207" fill="#1e6b34" font-family="system-ui, sans-serif" font-size="9.5">Разброс σ = 4,32 балла —</text>
<text x="80" y="219" fill="#1e6b34" font-family="system-ui, sans-serif" font-size="9.5">выборка однородная</text>

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

Excel, файл или калькулятор

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

Частые ошибки во всех расчётах

• Берут вариант функции «по генеральной совокупности». СТАНДОТКЛОНП и ДИСПР занижают разброс и портят все дальнейшие расчёты.В дипломе нужны выборочные версии — СТАНДОТКЛОН и ДИСП.
• Применяют t-критерий к баллам теста. Баллы — порядковая шкала, для неё нужны ранговые критерии.Для двух групп берите Манна-Уитни, для повторных замеров — Вилкоксона.
• Считают попарные сравнения вместо дисперсионного анализа. При трёх группах и трёх t-тестах вероятность ложного результата растёт с 5% до 14%.Для трёх и более групп применяйте ANOVA, а затем апостериорные сравнения.
• Приводят только p, без величины эффекта. На большой выборке значимым становится и мизерное различие.Рядом с p указывайте размер эффекта — d Коэна или r.
• Копируют данные из Word и получают текст вместо чисел. Формулы возвращают ноль или ошибку.Числа выровнены по левому краю — значит это текст. Лечится через «Данные → Текст по столбцам».

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

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

• Как выбрать статистический критерий — развилка по типу данных.
• Параметрические и непараметрические критерии — в чём разница.
• Как описать результаты статистики — формулировки для третьей главы.
• Как оформить таблицы и рисунки в дипломе — требования ГОСТ.
• Google Таблицы для статистики — если Excel нет вообще.

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