Excel умеет почти всю статистику, которая нужна в третьей главе. Проблема одна: список функций в справке идёт по алфавиту, а студент ищет не по алфавиту, а по задаче — «чем сравнить две группы», «как посчитать разброс».
Собрали справочник по задачам: русское имя, английское, что делает и готовый пример. Плюс разбор ловушки, из-за которой файл открывается с ошибкой #ИМЯ? у научного руководителя.
Что такое статистические функции и где они в Excel
Статистическая функция — это встроенная формула, которая берёт диапазон ячеек и возвращает одно число: среднее, разброс, коэффициент связи, p-значение.
Любая формула устроена одинаково: знак равенства, имя функции, скобки и аргументы внутри.
Вставить функцию можно тремя способами — результат одинаковый, отличается только скорость.
- Набрать руками — самый быстрый путь, если имя вы помните. Excel подсказывает продолжение уже после трёх букв.
- Кнопка
fxслева от строки формул — открывает мастер функций. В списке «Категория» выберите «Статистические» и листайте описания. - Вкладка «Формулы» → «Другие функции» → «Статистические» — то же меню, но в ленте.
Разделитель аргументов зависит от локали, а не от функции. В русском Excel это точка с запятой: =КВАРТИЛЬ(B2:B21;1). В английском и в Google Таблицах — запятая: =QUARTILE(B2:B21,1). Если формула ругается на аргументы, сначала проверьте разделитель.
Функции для описательной статистики
Это база третьей главы: чем описать группу до того, как сравнивать её с другой. Подробный разбор каждой формулы — в отдельных статьях про среднее, стандартное отклонение и дисперсию.
Центр распределения
Функции, которые отвечают на вопрос «где середина данных»
| Функция (рус.) | Англ. | Что делает | Пример |
|---|---|---|---|
СЧЁТ |
COUNT |
Сколько чисел в диапазоне — это ваше n | =СЧЁТ(B2:B21) |
СРЗНАЧ |
AVERAGE |
Среднее арифметическое, оно же M | =СРЗНАЧ(B2:B21) |
МЕДИАНА |
MEDIAN |
Значение ровно посередине упорядоченного ряда | =МЕДИАНА(B2:B21) |
МОДА |
MODE |
Самое частое значение | =МОДА(B2:B21) |
МИН / МАКС |
MIN / MAX |
Наименьшее и наибольшее значение | =МИН(B2:B21) |
КВАРТИЛЬ |
QUARTILE |
Квартиль: 1 — нижний, 2 — медиана, 3 — верхний | =КВАРТИЛЬ(B2:B21;1) |
ПЕРСЕНТИЛЬ |
PERCENTILE |
Процентиль, доля задаётся числом от 0 до 1 | =ПЕРСЕНТИЛЬ(B2:B21;0,9) |
Медиана и квартили нужны, когда распределение далеко от нормального или данные в баллах теста: тогда группу описывают как «Me [Q1; Q3]», а не «M ± σ».
Разброс значений
Функции, которые показывают, насколько люди в группе отличаются друг от друга
| Функция (рус.) | Англ. | Что делает | Пример |
|---|---|---|---|
СТАНДОТКЛОН |
STDEV |
Стандартное отклонение по выборке — нужный вариант для диплома | =СТАНДОТКЛОН(B2:B21) |
СТАНДОТКЛОНП |
STDEVP |
Отклонение по генеральной совокупности — только если измерены все | =СТАНДОТКЛОНП(B2:B21) |
ДИСП |
VAR |
Дисперсия по выборке — квадрат отклонения | =ДИСП(B2:B21) |
ДИСПР |
VARP |
Дисперсия по генеральной совокупности | =ДИСПР(B2:B21) |
СКОС |
SKEW |
Асимметрия: в какую сторону перекошено распределение | =СКОС(B2:B21) |
ЭКСЦЕСС |
KURT |
Эксцесс: острота пика | =ЭКСЦЕСС(B2:B21) |
Ошибки среднего отдельной функции нет — её собирают из двух: =СТАНДОТКЛОН(B2:B21)/КОРЕНЬ(СЧЁТ(B2:B21)).
Ранги и подсчёт по условию
Вспомогательные функции: без них не собрать Спирмена и не разложить выборку по подгруппам
| Функция (рус.) | Англ. | Что делает | Пример |
|---|---|---|---|
РАНГ |
RANK |
Место значения в ряду; третий аргумент 1 — по возрастанию | =РАНГ(B2;$B$2:$B$21;1) |
СЧЁТЕСЛИ |
COUNTIF |
Сколько значений подходит под условие | =СЧЁТЕСЛИ(B2:B21;">50") |
СЧЁТЕСЛИМН |
COUNTIFS |
То же по нескольким условиям сразу | =СЧЁТЕСЛИМН(B2:B21;">=10";B2:B21;"<20") |
СРЗНАЧЕСЛИ |
AVERAGEIF |
Среднее только по нужной подгруппе | =СРЗНАЧЕСЛИ(A2:A21;1;B2:B21) |
СУММПРОИЗВ |
SUMPRODUCT |
Сумма произведений, заменяет формулы массива | =СУММПРОИЗВ((B2:B21>50)*1) |
ОКРУГЛ |
ROUND |
Округление до нужного числа знаков | =ОКРУГЛ(B2;2) |
ЕСЛИОШИБКА |
IFERROR |
Что показать вместо ошибки | =ЕСЛИОШИБКА(B2/C2;"—") |
СРЗНАЧЕСЛИ экономит больше всего времени: если в столбце A стоит номер группы, среднее по экспериментальной и контрольной считается двумя формулами, а не двумя отдельными таблицами.
Функции для сравнения групп
Здесь Excel уже выдаёт готовое p-значение — то самое число, которое идёт в вывод третьей главы. Как его записывать, разобрано в статье про оформление p-значения.
Функции проверки гипотез: на выходе сразу вероятность
| Функция (рус.) | Англ. | Что делает | Пример |
|---|---|---|---|
ТТЕСТ |
TTEST |
Готовое p для t-критерия Стьюдента | =ТТЕСТ(B2:B21;C2:C21;2;1) |
ФТЕСТ |
FTEST |
Сравнение двух дисперсий — проверка равенства разброса | =ФТЕСТ(B2:B21;C2:C21) |
ХИ2ТЕСТ |
CHITEST |
p для χ² по фактическим и ожидаемым частотам | =ХИ2ТЕСТ(B5:C6;B10:C11) |
ZТЕСТ |
ZTEST |
p для сравнения среднего с заданным значением | =ZТЕСТ(B2:B21;50) |
У ТТЕСТ два аргумента-переключателя, и путаница в них — самая частая причина неверного p. Третий задаёт число хвостов: 2 — двусторонний, его и берут в дипломе. Четвёртый задаёт тип: 1 — парный (одни и те же люди до и после), 2 — независимые группы с равными дисперсиями, 3 — независимые с неравными.
ТТЕСТ возвращает только p и молча считает даже там, где t-критерий применять нельзя. Проверку нормальности и равенства дисперсий Excel не делает — это остаётся на вас. Если распределение не нормальное, вместо Стьюдента нужен Манна-Уитни или Вилкоксон, а таких функций в Excel нет вообще.
Распределения: критические значения и вероятности
Функции, которые заменяют таблицы критических значений из учебника
| Функция (рус.) | Англ. | Что делает | Пример |
|---|---|---|---|
СТЬЮДРАСП |
TDIST |
p по известному t и числу степеней свободы | =СТЬЮДРАСП(2,45;28;2) |
СТЬЮДРАСПОБР |
TINV |
Критическое t по уровню значимости | =СТЬЮДРАСПОБР(0,05;28) |
ХИ2ОБР |
CHIINV |
Критическое значение χ² | =ХИ2ОБР(0,05;1) |
FРАСПОБР |
FINV |
Критическое F для дисперсионного анализа | =FРАСПОБР(0,05;2;27) |
НОРМСТРАСП |
NORMSDIST |
Вероятность по значению z | =НОРМСТРАСП(1,96) |
НОРМСТОБР |
NORMSINV |
Значение z по вероятности | =НОРМСТОБР(0,975) |
Эти функции пригодятся, когда критерий вы считали руками и нужно сравнить эмпирическое значение с критическим — не листая приложения учебника.
Функции для связей и предсказания
Корреляция и регрессия: что связано и как одно предсказывает другое
| Функция (рус.) | Англ. | Что делает | Пример |
|---|---|---|---|
КОРРЕЛ |
CORREL |
Коэффициент корреляции Пирсона | =КОРРЕЛ(B2:B21;C2:C21) |
ПИРСОН |
PEARSON |
То же самое, второе имя той же функции | =ПИРСОН(B2:B21;C2:C21) |
КВПИРСОН |
RSQ |
Коэффициент детерминации R² | =КВПИРСОН(C2:C21;B2:B21) |
НАКЛОН |
SLOPE |
Коэффициент b в уравнении регрессии; сначала y, потом x | =НАКЛОН(C2:C21;B2:B21) |
ОТРЕЗОК |
INTERCEPT |
Свободный член a в уравнении | =ОТРЕЗОК(C2:C21;B2:B21) |
СТОШYX |
STEYX |
Стандартная ошибка предсказания | =СТОШYX(C2:C21;B2:B21) |
ПРЕДСКАЗ |
FORECAST |
Прогноз y для нового значения x | =ПРЕДСКАЗ(75;C2:C21;B2:B21) |
В КОРРЕЛ порядок диапазонов не важен — коэффициент симметричный. В регрессионных функциях важен: первым идёт зависимый показатель y, вторым — независимый x. Перепутаете — получите другое уравнение.
Корреляции Спирмена в Excel нет: её собирают через РАНГ и КОРРЕЛ по рангам, это разобрано в статье про корреляцию в Excel.
Старые и новые имена: какие брать
С Excel 2010 у половины статистических функций появились двойники с точкой в имени: СТАНДОТКЛОН.В, ДИСП.В, СТЬЮДЕНТ.ТЕСТ, ХИ2.ТЕСТ. Считают они то же самое, отличие только в написании.
Классическое имя и его современный двойник
| Классическое имя | Новое имя (с 2010) | Что считает |
|---|---|---|
СТАНДОТКЛОН |
СТАНДОТКЛОН.В |
Отклонение по выборке |
СТАНДОТКЛОНП |
СТАНДОТКЛОН.Г |
Отклонение по генеральной совокупности |
ДИСП |
ДИСП.В |
Дисперсия по выборке |
ТТЕСТ |
СТЬЮДЕНТ.ТЕСТ |
p для t-критерия |
ХИ2ТЕСТ |
ХИ2.ТЕСТ |
p для χ² |
КВАРТИЛЬ |
КВАРТИЛЬ.ВКЛ |
Квартиль |
МОДА |
МОДА.ОДН |
Мода |
Разница вылезает при обмене файлами. Новые имена не понимают Excel 2007 и более ранние версии, часть корпоративных сборок и Google Таблицы. Вместо числа там появится #ИМЯ? — а заметить это можете уже не вы, а рецензент.
Внутри файла Excel всё равно хранит английские имена и подставляет нужный язык при открытии. Поэтому русский и английский варианты одной функции — не разные формулы, а один и тот же расчёт.
Всё это уже собрано в готовом файле
Держать справочник открытым в браузере необязательно: он есть внутри нашей книги Excel — отдельный лист «Справочник формул» с теми же таблицами. А на остальных листах формулы уже стоят в ячейках: вы вставляете свои числа, результат считается сам.
- Справочник формул Excel
- Среднее, медиана, мода
- Отклонение и дисперсия
- Ошибка среднего, M ± m
- Коэффициент вариации
- Проверка распределения
- Доверительный интервал
- t-критерий Стьюдента
- Манна-Уитни и Вилкоксон
- Корреляции и регрессия
- χ² Пирсона
- Готовые диаграммы
В файле намеренно использованы классические имена без точки: он одинаково открывается и в Excel 2007, и в свежем Office, и в бесплатных офисных пакетах.
Три способа посчитать — что выбрать
| Функции рукамиклассический путь | Готовый файлнаш, бесплатно | StatBlankОнлайн-калькуляторпрямо в браузереСАМЫЙ ПРОСТОЙ | |
|---|---|---|---|
| Считает описательную статистику | |||
| Не нужно помнить имена функций | |||
| Подсказывает, какой метод взять | |||
| Готовый вывод словами для диплома | |||
| Ранговые критерии (Манна-Уитни) | на своих листах | ||
| Проверка распределения | по асимметрии | по асимметрии | |
| Готовый рисунок для главы 3 | базовый | ||
| Не зависит от версии Excel | |||
| Открывается с телефона | |||
| Бесплатно | |||
| Скачать файл | Открыть калькулятор |
- Считает описательную статистику
- Имена нужно помнить
- Ранговых критериев нет
- Вывод пишете сами
- Зависит от версии Excel
- Бесплатно
- Формулы уже стоят в ячейках
- Лист-справочник внутри
- Готовый вывод словами
- Открывается в любой версии
- Нужен установленный Excel
- Нет поиска выбросов
- Вставил данные — готово
- Имена функций не нужны
- Ранговые критерии тоже есть
- Развёрнутый вывод для главы 3
- Готовый рисунок по ГОСТ
- Открывается с телефона
← листайте вправо, чтобы сравнить →
Числа во всех трёх колонках получатся одинаковые. Разница в том, сколько шагов остаётся на вас и что делать с задачами, которых в Excel нет.Половину критериев Excel не умеет в принципе: Манна-Уитни, Вилкоксона, Краскела-Уоллиса, Шапиро-Уилка среди функций нет. В калькуляторах StatBlank они есть, вместе с проверкой условий применимости и готовой формулировкой.
Рабочая связка: данные держите в Excel, описательную статистику считайте функциями, а критерии и рисунки отдайте калькулятору. Так не придётся собирать ранговые формулы вручную.
Частые ошибки
- Пишут функции с точкой в имени. У научрука на Excel 2007 или в Google Таблицах вместо чисел появится
#ИМЯ?.Берите классические имена: СТАНДОТКЛОН вместо СТАНДОТКЛОН.В, ТТЕСТ вместо СТЬЮДЕНТ.ТЕСТ. - Путают аргументы в ТТЕСТ. Парный тест вместо независимых групп даёт совсем другое p — иногда «значимо» вместо «незначимо».До и после у одних людей — тип 1. Разные группы — тип 2 или 3.
- Меняют местами x и y в регрессии. В НАКЛОН и ОТРЕЗОК первым идёт зависимый показатель, а не независимый.Запомните порядок: сначала то, что предсказываем, потом то, чем предсказываем.
- Считают КОРРЕЛ на баллах теста. КОРРЕЛ — это только Пирсон, для порядковой шкалы он не годится.Проранжируйте данные функцией РАНГ и посчитайте КОРРЕЛ по рангам — получится Спирмен.
- Захватывают в диапазон заголовок и строку «Итого». Функция молча посчитает итоговую сумму как ещё одного испытуемого.Выделяйте только ячейки со значениями и проверяйте n через СЧЁТ.
- Копируют данные из Word и получают текст. Числа выравниваются по левому краю, СРЗНАЧ выдаёт ноль или ошибку.«Данные → Текст по столбцам» превращает текст обратно в числа.
Частые вопросы
Какие статистические функции есть в Excel
Около сотни, но в дипломе используют полтора десятка: СЧЁТ, СРЗНАЧ, МЕДИАНА, МОДА, МИН, МАКС, КВАРТИЛЬ, СТАНДОТКЛОН, ДИСП, СКОС, ЭКСЦЕСС, ТТЕСТ, ХИ2ТЕСТ, КОРРЕЛ, НАКЛОН, РАНГ. Их и стоит выписать себе на листок.
Где найти статистические функции в Excel
Нажмите fx слева от строки формул и выберите категорию «Статистические» — откроется список с описанием каждой. Тот же список лежит во вкладке «Формулы» → «Другие функции» → «Статистические».
Чем СТАНДОТКЛОН отличается от СТАНДОТКЛОН.В
Ничем в расчёте: обе считают отклонение по выборке. Отличие в совместимости — имя с точкой появилось в Excel 2010 и не работает в более ранних версиях и в Google Таблицах. Для диплома надёжнее классическое имя.
Считает ли Excel Манна-Уитни и Вилкоксона
Нет. Ни функций, ни процедур в «Пакете анализа» для ранговых критериев не предусмотрено — их собирают через РАНГ вручную или считают в калькуляторе Манна-Уитни.
Почему функция возвращает #ЗНАЧ! или #ДЕЛ/0!
#ЗНАЧ! обычно означает текст в диапазоне или неверный тип аргумента, #ДЕЛ/0! — пустой диапазон, где нечего усреднять. Проверьте, что все ячейки числовые и выделен нужный столбец.
Короткий алгоритм
- Сформулируйте задачу: описать группу, сравнить группы или найти связь.
- Найдите функцию в таблице выше — там же готовый пример со скобками.
- Наберите формулу с классическим именем, без точки.
- Проверьте n через
=СЧЁТ(диапазон): если число не совпало с количеством испытуемых, диапазон захвачен неверно. - Для критериев, которых в Excel нет, откройте калькулятор или подберите метод по данным.
Что ещё почитать
- Как посчитать статистику в Excel: формулы — пошаговый расчёт от таблицы данных до вывода.
- Пакет анализа данных в Excel — надстройка, которая считает t-тест и регрессию без формул.
- Корреляция в Excel — как собрать Спирмена, которого нет среди функций.
- Гистограмма в Excel — рисунок распределения для третьей главы.
- Статистика в Google Таблицах — что из этого списка работает бесплатно в браузере.
Если не уверены, какая функция нужна именно в вашей работе, загляните в базу методов или напишите нам — поможем с расчётами.
Не хотите разбираться со статистикой сами?
Эксперт подберёт метод, посчитает и оформит таблицы по ГОСТ под вашу тему.
Заказать консультацию