Как посчитать статистику в Excel: формулы

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

Ниже — какие формулы вбивать, как разложить данные по столбцам и как включить пакет «Анализ данных», чтобы получить t-тест и корреляцию в пару кликов.

В двух словах

• Описательная статистика (среднее, медиана, разброс) считается встроенными формулами: =СРЗНАЧ(), =МЕДИАНА(), =СТАНДОТКЛОН.В(), =КВАРТИЛЬ.ВКЛ(), =СЧЁТ().
• Сравнение групп и связи (t-тест, корреляция) удобнее считать через надстройку «Анализ данных» — её нужно один раз включить.

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

Как разложить данные по столбцам

Прежде чем считать, наведите порядок в таблице — это экономит часы и спасает от ошибок.

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

Пример. Вы измерили тревожность у 30 студентов до и после тренинга. Делаете три столбца: «№», «Тревожность до», «Тревожность после». Каждая строка — один студент. Никаких объединённых ячеек и пустых строк внутри данных.

Так устроены почти все статистические программы: они ждут «длинную» аккуратную таблицу, а не красиво раскрашенную сводку.

Полезный приём — дать диапазону имя. Выделите столбец с числами, в поле слева от строки формул впишите, например, ДО — и тогда формула станет читаемой: =СРЗНАЧ(ДО) вместо =СРЗНАЧ(B2:B31). Меньше шансов промахнуться диапазоном.

Базовые формулы описательной статистики

Все формулы вводятся в пустую ячейку: ставите =, пишете имя функции, в скобках указываете диапазон с данными (например, B2:B31). Разберём пять, которые нужны в дипломе чаще всего.

Среднее — =СРЗНАЧ(B2:B31). Складывает все значения и делит на их количество. Это «центр» ваших данных. Подробнее о том, чем среднее отличается от медианы, — в статье «Среднее, медиана и мода».

Медиана — =МЕДИАНА(B2:B31). Значение ровно посередине, если выстроить все числа по возрастанию. Медиана не боится выбросов: один человек с огромным баллом утянет среднее, но не медиану.

Стандартное отклонение — =СТАНДОТКЛОН.В(B2:B31). Показывает, насколько в среднем значения разбросаны вокруг среднего. Маленькое — группа однородная, большое — мнения «кто в лес, кто по дрова». Что это за число и зачем оно, разбираем в статье «Стандартное отклонение и дисперсия».

Квартили — =КВАРТИЛЬ.ВКЛ(B2:B31; 1). Делят упорядоченные данные на четыре равные части. Первый квартиль (аргумент 1) — граница нижних 25%, третий (аргумент 3) — граница верхних 25%. Удобно для порядковых шкал. Подробно — в статье «Медиана и квартили».

Количество — =СЧЁТ(B2:B31). Считает, сколько в диапазоне чисел. Это ваше n — объём выборки. Функция игнорирует пустые и текстовые ячейки, поэтому удобно проверять, не потерялись ли данные.

Мини-таблица: что вводить и что получите

Таблица 1 — Базовые формулы Excel для описательной статистики

В диплом обычно выносят среднее и стандартное отклонение для числовых данных, медиану и квартили — для баллов и оценок. Какой набор выбрать под вашу шкалу, подскажет статья «Описательная статистика в дипломе».

Пакет «Анализ данных»: t-тест и корреляция

Сравнить две группы или найти связь между признаками формулами можно, но муторно. Гораздо проще включить встроенную надстройку.

Как включить (делается один раз). Вкладка «Файл» → «Параметры» → «Надстройки» → внизу в списке «Управление» выберите «Надстройки Excel» → «Перейти» → поставьте галочку «Пакет анализа» → «ОК». После этого на вкладке «Данные» справа появится кнопка «Анализ данных».

На Mac путь чуть другой: меню «Сервис» → «Надстройки Excel» → галочка «Пакет анализа». Если кнопки всё равно нет (бывает в веб-версии и в некоторых лицензиях), расчёт за минуту сделают наши онлайн-калькуляторы — вкладку настроек открывать не придётся.

t-тест для двух групп. Нажмите «Анализ данных» → выберите нужный вариант:

• «Парный двухвыборочный t-тест для средних» — когда измеряли одних и тех же людей дважды (до/после).
• «Двухвыборочный t-тест с одинаковыми дисперсиями» — когда сравниваете две разные группы (контрольная и экспериментальная).

Дальше укажите два диапазона с данными, поставьте «Метки», если выделили вместе с заголовками, и нажмите «ОК». В отчёте смотрите строку «P(T<=t) двухстороннее» — это и есть ваше p-значение. Как его читать — в статье «Что такое p-значение».

Корреляция. «Анализ данных» → «Корреляция» → выделите сразу несколько столбцов с признаками → «ОК». Excel выдаст таблицу коэффициентов корреляции Пирсона между всеми парами. Значение около 0 — связи нет, ближе к ±1 — связь сильная.

Пример. Хотите проверить, связан ли уровень мотивации с успеваемостью у 40 студентов. Два числовых столбца, оба распределены нормально → «Анализ данных» → «Корреляция». Получили r = 0,52 — умеренная положительная связь.

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

Excel выдаёт «сырые» числа — их нужно завернуть в правильные фразы. Готовые формулировки:

• «Описательная статистика рассчитана в Microsoft Excel: для каждого показателя определены среднее арифметическое (M), стандартное отклонение (σ), медиана (Me) и квартили».
• «Средний уровень тревожности в экспериментальной группе составил M = 42,3 (σ = 6,8)».
• «Для порядковых данных приведены медиана и квартили: Me = 18 (Q₁ = 14; Q₃ = 23)».
• «Сравнение групп выполнено с помощью t-критерия Стьюдента средствами пакета “Анализ данных”; различия значимы (t = 2,41; p = 0,019)».
• «Связь между показателями оценена коэффициентом корреляции Пирсона (r = 0,52; p < 0,05)».

Как красиво оформить итоговую таблицу с этими числами по ГОСТу — в статье «Как оформить таблицы и рисунки в дипломе». А превратить цифры в график для главы — в статье «Диаграммы в Excel для диплома».

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

• Десятичная запятая или точка. В русском Excel дробь пишется через запятую (4,5). Если данные с точкой (4.5), они воспринимаются как текст, и формулы выдают ошибку или ноль. Замените точки на запятые через «Найти и заменить».
• СУММ вместо СРЗНАЧ. Классическая опечатка: посчитали сумму баллов вместо среднего. Всегда проверяйте имя функции.
• Захватили заголовок в диапазон. Если в =СРЗНАЧ() попала ячейка с текстом-названием, результат может «съехать». Считайте только по числовым строкам.
• Корреляция Пирсона для баллов анкеты. Для порядковых шкал и ненормальных данных нужен Спирмен, а Excel по умолчанию даёт Пирсона.
• Проверка значимости «на глаз». Среднее «после» меньше — ещё не доказательство. Нужен критерий и p-значение, иначе вывод не примут.

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

Какую функцию брать для стандартного отклонения — с точкой или без?

Берите современную =СТАНДОТКЛОН.В() (для выборки). Старая =СТАНДОТКЛОН() без точки работает так же, но считается устаревшей — в новых версиях Excel её оставили только для совместимости.

Excel считает p-значение или его надо искать по таблицам?

Считает. Пакет «Анализ данных» в отчёте t-теста сразу выдаёт строку «P(T<=t) двухстороннее» — это готовое p-значение, сверять с бумажными таблицами критических значений не нужно.

Можно ли в Excel проверить нормальность распределения?

Напрямую — нет, отдельной кнопки для критерия Шапиро-Уилка в нём нет. Косвенно можно построить гистограмму или посчитать асимметрию (=СКОС()) и эксцесс (=ЭКСЦЕСС()). Полноценную проверку удобнее делать в калькуляторе или по инструкции из статьи «Проверка нормальности: SPSS и Excel».

Подойдёт ли Excel для непараметрических критериев — Манна-Уитни, Вилкоксона?

Стандартного готового инструмента для них в Excel нет, их пришлось бы собирать формулами вручную, а это легко сделать с ошибкой. Проще посчитать в наших калькуляторах: Манна-Уитни, Вилкоксона. А выбрать нужный критерий поможет статья «Как выбрать статистический критерий».

Что выбрать для расчётов — Excel или SPSS?

Для описательной статистики, t-теста и корреляции Пирсона хватает Excel. Для непараметрических критериев, надёжной проверки нормальности и сложных моделей удобнее SPSS онлайн или специализированные калькуляторы.

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

1. Разложите данные: один признак — столбец, один испытуемый — строка, заголовки в первой строке.
2. Описательная статистика: формулы СРЗНАЧ, МЕДИАНА, СТАНДОТКЛОН.В, КВАРТИЛЬ.ВКЛ, СЧЁТ.
3. Включите надстройку «Пакет анализа» (один раз).
4. Сравнение групп: «Анализ данных» → нужный t-тест → смотрите p-значение.
5. Связь признаков: «Анализ данных» → «Корреляция» (это Пирсон; для баллов — Спирмен в калькуляторе).
6. Опишите словами: программа, показатель, статистика и p-значение.

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

• Описательная статистика в дипломе — какие показатели выбрать под вашу шкалу.
• Проверка нормальности: SPSS и Excel — нужно для выбора между Пирсоном и Спирменом, t-тестом и его аналогами.
• Диаграммы в Excel для диплома — как сделать график из тех же данных.
• Как выбрать статистический критерий — общая схема, чем считать гипотезу.
• Калькулятор описательной статистики — посчитать всё онлайн без формул.

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