Среднее значение в Excel: формула СРЗНАЧ и готовый файл

Данные собраны, протоколы в столбик — а в третьей главе нужна строка «средний балл по группе составил…». Это и есть среднее значение, и в Excel оно считается одной функцией.

Разберём формулу, три ловушки, из-за которых среднее выходит неверным, и способ получить всю описательную статистику вообще без формул.

В двух словах

• Формула — =СРЗНАЧ(B2:B21), в английском Excel AVERAGE.
• Диапазон — только ячейки со значениями, без заголовка и строки «Итого».
• Главная ловушка — нули. Текст и пустые ячейки функция пропускает, а нули считает как настоящие результаты.
• В диплом идёт запись «M ± m» или «M ± σ» с указанием, что стоит после знака.
• Если данные с выбросом — среднее врёт, берите медиану.

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

Что такое среднее арифметическое

Сложить все значения и разделить на их количество — вот и всё среднее арифметическое. У 20 студентов сумма баллов 1565, делим на 20, получаем 78,25.

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

В работах его обозначают буквой M (от mean) или чертой над буквой — «икс с чертой». Это одно и то же число.

Формула в Excel

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

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

Диапазон можно не набирать руками: поставьте открывающую скобку и выделите столбец мышкой. В английском Excel и в Google Таблицах функция называется AVERAGE, аргументы те же.

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

Баллы итогового теста по теории физической культуры у 20 студентов: 72, 85, 64, 90, 78, 81, 69, 95, 74, 88, 60, 83, 77, 91, 66, 79, 86, 70, 82, 75.

Что даёт Excel на этих двадцати значениях

Среднее и медиана почти совпали — 78,25 и 78,50. Это признак того, что распределение симметричное и среднее честно описывает группу.

Три ловушки функции СРЗНАЧ

Нули она считает. Текст и пустые ячейки СРЗНАЧ пропускает — они не попадают ни в сумму, ни в количество. А вот ноль для неё полноценный результат.

Двое студентов на тест не пришли. Если оставить их ячейки пустыми, среднее по 18 работам — 78,22 балла. Если вместо пропуска поставить нули, Excel честно поделит ту же сумму на 20 и выдаст 70,40. Разница в 8 баллов возникла из двух символов.

СРЗНАЧА считает текст как ноль. Похожая по имени функция ведёт себя иначе: любой текст в диапазоне она приравнивает к нулю, ИСТИНА — к единице. Напишете в пропущенных ячейках «н/д» — СРЗНАЧ вернёт 78,22, а СРЗНАЧА те же 70,40. В студенческих расчётах СРЗНАЧА практически не нужна.

СРЗНАЧЕСЛИ — среднее по условию. Когда в одной таблице лежат обе группы, не нужно растаскивать их по разным листам. Пусть в столбце C стоит метка группы:

=СРЗНАЧЕСЛИ(C2:C21;"Э";B2:B21)

По экспериментальной группе получится 79,60 балла, по контрольной ("К") — 76,90. Для нескольких условий сразу есть СРЗНАЧЕСЛИМН, а условие можно писать и сравнением: ">70".

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

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

Среднее считается на листе «4. Описательная статистика»: рядом с ним медиана, мода, минимум и максимум, объём выборки и готовая строка «M ± m» — та самая, которую нужно перенести в таблицу третьей главы.

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

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

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

Калькулятор делает то, чего СРЗНАЧ не умеет в принципе: проверяет форму распределения, находит выбросы, показывает медиану рядом со средним и пишет развёрнутый вывод для третьей главы. Бесплатно и без регистрации.

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

Записать вместе с ошибкой среднего — «78,25 ± 2,14 балла (M ± m)». Одно среднее без второго числа рецензент считает неполным результатом: непонятно, насколько оно устойчиво.

Или вместе с отклонением — «78,25 ± 9,56 балла (M ± σ)». Отклонение показывает разброс людей в группе, ошибка среднего — точность самой оценки. Что именно стоит после знака ±, обязательно поясните в подписи к таблице. Подробнее — в статье про стандартное отклонение в Excel.

Сравнить со средним второй группы. Само по себе «79,60 против 76,90» ничего не доказывает: разницу нужно проверять критерием. Для двух независимых групп это t-критерий Стьюдента или Манна-Уитни.

Когда среднее врёт

Среднее чувствительно к крайним значениям. Десять студентов бежали 1000 метров: девять уложились в 218–240 секунд, десятый потянул мышцу и дошёл шагом за 610.

Среднее по группе — 267 секунд. Такого результата не показал никто: девять быстрее, один намного медленнее. Медиана в той же группе — 230 секунд, и она честно описывает типичного студента.

Правило простое: среднее и медиана разошлись заметно — распределение перекошено, описывайте группу медианой. Разбор обоих показателей — в статье среднее, медиана и мода.

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

Средний балл итогового теста в экспериментальной группе составил 78,25 ± 2,14 балла (M ± m) при разбросе от 60 до 95 баллов.

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

Средний балл составил 78,25 ± 9,56 балла (M ± σ), медиана — 78,50 балла.

При сравнении двух групп добавьте критерий и p-значение — иначе вывод о «повышении показателя» ничем не подкреплён.

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

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

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

Как посчитать среднее в экселе без формулы

Выделите столбец с числами и посмотрите на строку состояния внизу окна — Excel сразу показывает «Среднее», «Количество» и «Сумма». Число можно переписать в работу, но в ячейке его не останется.

Почему СРЗНАЧ выдаёт #ДЕЛ/0!

В диапазоне нет ни одного числа: все ячейки пустые или содержат текст. Проверьте формулой =СЧЁТ(B2:B21) — она покажет, сколько чисел Excel вообще видит.

Чем СРЗНАЧ отличается от СРЗНАЧА

СРЗНАЧ пропускает текст и пустые ячейки, СРЗНАЧА считает текст как ноль, а ИСТИНА как единицу. Для студенческих расчётов нужна первая.

Как посчитать среднее только по одной группе

Функцией =СРЗНАЧЕСЛИ(C2:C21;"Э";B2:B21), где в столбце C стоит метка группы. Для нескольких условий — СРЗНАЧЕСЛИМН.

Среднее арифметическое и среднее значение — это одно и то же

В студенческих работах да. «Среднее значение» — общее слово, и по умолчанию под ним понимают именно арифметическое. Есть ещё среднее геометрическое и гармоническое, но в дипломах они встречаются редко.

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

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

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

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

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

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