Стандартное отклонение в Excel: формула и готовый файл

В дипломе редко просят просто среднее — почти всегда нужна запись «M ± σ». Вторая часть и есть стандартное отклонение.

Разберём по порядку: что это за число, какой формулой посчитать его в Excel и как сделать то же самое вообще без формул.

Что такое стандартное отклонение

Среднее показывает, где находится «центр» ваших данных. Но оно ничего не говорит о том, насколько люди в группе похожи друг на друга.

Представьте две группы, где средний балл одинаковый — 47. В первой все набрали от 45 до 49, во второй разброс от 20 до 70. Среднее одно, а группы совершенно разные.

Стандартное отклонение — это и есть мера того, насколько значения разбросаны вокруг среднего. Маленькое σ — группа однородная, все примерно одинаковые. Большое — люди сильно отличаются друг от друга.

Записывают его рядом со средним: «47,40 ± 4,32 балла». Это стандарт оформления результатов в любой студенческой работе.

У показателя несколько имён — стандартное отклонение, среднеквадратичное отклонение (СКО), сигма (σ), s. Это одна и та же величина, просто разные учебники называют её по-разному.

Формула в Excel

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

=СТАНДОТКЛОН(B2:B21)

Рядом посчитайте среднее — оно нужно для записи «M ± σ»:

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

Всё. В английском Excel эти функции называются STDEV и AVERAGE.

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

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

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

В диплом идёт запись «47,40 ± 4,32 балла» — и обязательно поясните в подписи, что стоит после знака ±: отклонение или ошибка среднего. Это разные числа.

Одна ловушка: СТАНДОТКЛОН или СТАНДОТКЛОНП

В Excel две похожие функции, и выбор между ними — не мелочь.

СТАНДОТКЛОН — по выборке. Вы обследовали часть людей, а выводы делаете шире: измерили 20 студентов, а пишете о студентах первого курса. Это ваш случай в дипломе.

СТАНДОТКЛОНП — по генеральной совокупности. Только если измерили всех, о ком говорите: всю команду, весь класс.

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

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

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

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

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

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

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

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

Что делать с полученным числом

Записать вместе со средним — «47,40 ± 4,32 балла».

Оценить однородность группы через коэффициент вариации: отклонение делим на среднее и умножаем на 100%. В нашем примере 4,32 / 47,40 × 100 = 9,1%.

Найти тех, кто выбивается. При нормальном распределении около 68% значений попадают в интервал «M ± σ», 95% — в «M ± 2σ». Всё, что за пределами «M ± 3σ», — кандидаты в выбросы.

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

Средний уровень ситуативной тревожности в группе составил 47,40 ± 4,32 балла (M ± σ). Коэффициент вариации 9,1% свидетельствует об однородности выборки.

Если приводите ошибку среднего, а не отклонение:

Средний уровень ситуативной тревожности составил 47,40 ± 0,97 балла (M ± m).

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

• Берут СТАНДОТКЛОНП вместо СТАНДОТКЛОН. Занижает разброс и портит все последующие расчёты.В дипломе почти всегда нужен СТАНДОТКЛОН — вариант «по выборке».
• Захватывают в диапазон лишние ячейки. Номер испытуемого или итоговая строка попадут в расчёт.Выделяйте только столбец со значениями, без заголовка и итогов.
• Пишут в диплом дисперсию вместо отклонения. Она измеряется в квадратных единицах: «квадратные баллы» — бессмыслица.В таблицах приводите σ, дисперсию оставьте промежуточным расчётом.
• Сравнивают отклонения показателей с разными единицами. Сантиметры и килограммы несопоставимы.Для сравнения разброса берите коэффициент вариации в процентах.
• Копируют данные из Word и получают текст. Формула вернёт ноль или ошибку.Числа выровнены по левому краю — значит это текст. Лечится через «Данные → Текст по столбцам».

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

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

СТАНДОТКЛОН. Вы обследовали выборку и делаете выводы шире — это ровно её случай.

Среднеквадратичное отклонение считается другой формулой

Нет, это то же самое. СТАНДОТКЛОН считает и «стандартное», и «среднеквадратичное» отклонение — отдельной функции не существует.

Почему получился ноль

Все значения одинаковые, либо в диапазоне одно число, либо данные вставлены как текст.

Нужно ли считать σ, если данные в баллах теста

Формально порядковую шкалу правильнее описывать медианой и квартилями. На практике «M ± σ» в дипломах приводят, но сравнивать группы по баллам нужно ранговыми критериями — Манна-Уитни или Вилкоксона.

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

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

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

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

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

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