Статистика в Excel: среднее, медиана и стандартное отклонение

Описательная статистика в Excel: функции на русском и английском, СТАНДОТКЛОН.В или .Г, пакет анализа, гистограмма, выбросы и как описать результат словами.

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

Данные для примера

Двенадцать студентов решали тест, в столбце A записано время в минутах: заголовок в A1, числа в A2:A13.

34  41  38  45  39  36  42  40  37  95  43  39

По возрастанию: 34, 36, 37, 38, 39, 39, 40, 41, 42, 43, 45, 95. Одно значение сильно выбивается, и на нём хорошо видно, чем отличаются показатели.

Функции: русские и английские названия

Показатель Русский Excel Английский Excel Результат
Среднее СРЗНАЧ AVERAGE 44,08
Медиана МЕДИАНА MEDIAN 39,5
Мода МОДА.ОДН MODE.SNGL 39
Минимум и максимум МИН, МАКС MIN, MAX 34 и 95
Количество чисел СЧЁТ COUNT 12
Стандартное отклонение выборки СТАНДОТКЛОН.В STDEV.S 16,32
Стандартное отклонение совокупности СТАНДОТКЛОН.Г STDEV.P 15,63
Дисперсия выборки ДИСП.В VAR.S 266,45
Первый и третий квартили КВАРТИЛЬ.ВКЛ QUARTILE.INC 37,75 и 42,25
Урезанное среднее УРЕЗСРЕДНЕЕ TRIMMEAN 40

Формулы пишутся так:

=СРЗНАЧ(A2:A13)
=СТАНДОТКЛОН.В(A2:A13)
=КВАРТИЛЬ.ВКЛ(A2:A13;1)
=УРЕЗСРЕДНЕЕ(A2:A13;0,2)

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

=AVERAGE(A2:A13)
=STDEV.S(A2:A13)
=QUARTILE.INC(A2:A13,1)
=TRIMMEAN(A2:A13,0.2)

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

Пара деталей. МОДА.ОДН возвращает ошибку #Н/Д, если ни одно значение не повторяется. УРЕЗСРЕДНЕЕ с долей 0,2 отбрасывает по одному крайнему значению с каждой стороны: 20 % от 12 это 2,4, и функция округляет число отброшенных точек вниз до чётного (справка Microsoft).

Среднее или медиана

Среднее 44,08 минуты, медиана 39,5. Разница больше четырёх минут, и вся она из-за одного студента с 95 минутами. Без него среднее было бы 39,45, а медиана 39.

Правило простое: среднее чувствительно к выбросам, медиана нет. Если данные несимметричны или в них есть сильно отличающиеся значения (зарплаты, время реакции, цены), главным показателем берите медиану. Если распределение примерно симметричное, среднее и медиана близки, и обычно указывают среднее со стандартным отклонением.

СТАНДОТКЛОН.В или СТАНДОТКЛОН.Г

Обе функции считают корень из среднего квадрата отклонений, разница в знаменателе:

СТАНДОТКЛОН.В:  s = √( Σ(x - x_ср)² / (n - 1) )    выборка
СТАНДОТКЛОН.Г:  σ = √( Σ(x - x_ср)² / n )          вся генеральная совокупность

СТАНДОТКЛОН.В нужна, когда данные это выборка, а выводы вы делаете о большей группе: двенадцать студентов как пример всех, кто будет решать этот тест. Это почти все учебные задачи. СТАНДОТКЛОН.Г берут, когда в таблице вся совокупность и выводы только о ней (справка Microsoft).

На наших данных 16,32 против 15,63. Отношение двух чисел равно √(n/(n − 1)): при n = 12 это 4 %, при n = 100 уже 0,5 %. На маленьких выборках выбор функции заметен, поэтому в отчёте пишите, какую формулу использовали.

Пакет анализа: всё одной кнопкой

Если нужно сразу много показателей, включите надстройку «Пакет анализа» (справка Microsoft):

  1. «Файл», «Параметры», «Надстройки».
  2. Внизу в списке «Управление» выберите «Надстройки Excel» и нажмите «Перейти».
  3. Отметьте «Пакет анализа» и нажмите «ОК».

На Mac надстройка включается через меню «Сервис», пункт «Надстройки Excel».

После этого на вкладке «Данные» появится кнопка «Анализ данных». Выберите «Описательная статистика», укажите входной интервал A1:A13, отметьте, что в первой строке метки, выберите, куда вывести результат, и включите итоговую статистику. Excel выведет таблицу со средним, медианой, модой, стандартным отклонением, минимумом, максимумом и другими показателями.

Учтите: пакет анализа вставляет готовые числа, а не формулы. Поменяли данные, запустите анализ заново.

Гистограмма

Гистограмма показывает форму распределения: симметричное ли оно, есть ли хвост, отдельные значения. В Excel 2016 и новее она строится как диаграмма: выделите данные, на вкладке «Вставка» откройте статистические диаграммы и выберите «Гистограмма» (справка Microsoft).

Войлочный кот Тиль сидит рядом с бумажными столбиками разной высоты и держит в лапе маленькую лупу

Интервалы настраиваются правым щелчком по горизонтальной оси, «Формат оси»:

  • «Длина интервала»: ширина одного столбца, например 2 минуты;
  • «Количество интервалов»: сколько столбцов всего;
  • «Выход за верхнюю границу интервала»: все значения выше порога попадают в один крайний столбец.

Для наших данных удобно взять длину 2 и верхнюю границу 50. Тогда основная масса значений разложится по столбцам, а 95 уйдёт в отдельный крайний столбец и не растянет ось.

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

Как найти выбросы

Распространённое правило: выброс лежит ниже Q1 − 1,5·IQR или выше Q3 + 1,5·IQR, где IQR = Q3 − Q1 это межквартильный размах.

Q1  = 37,75
Q3  = 42,25
IQR = 42,25 - 37,75 = 4,5
нижняя граница = 37,75 - 1,5·4,5 = 31
верхняя граница = 42,25 + 1,5·4,5 = 49

95 больше 49, значит, это выброс. В Excel верхнюю границу можно посчитать одной формулой:

=КВАРТИЛЬ.ВКЛ(A2:A13;3)+1,5*(КВАРТИЛЬ.ВКЛ(A2:A13;3)-КВАРТИЛЬ.ВКЛ(A2:A13;1))

Выброс не удаляют молча. Сначала проверьте, не опечатка ли это: может, в журнале было 59 или 35. Если значение настоящее, у него есть причина, и она интересна. В отчёте дайте показатели с выбросом и без него или опирайтесь на медиану и квартили.

Квартили в разных программах считаются немного по-разному. КВАРТИЛЬ.ВКЛ и КВАРТИЛЬ.ИСКЛ дают разные числа на одних данных. Это нормально, просто указывайте, какой функцией считали.

Как описать результат словами

Одних чисел в отчёте мало, нужен вывод. Пример описания для наших данных:

Время решения теста у 12 студентов составило от 34 до 95 минут. Медиана 39,5 мин, половина студентов уложилась в интервал от 37,75 до 42,25 мин. Одно значение, 95 мин, выше границы выбросов в 49 мин. Без него среднее время 39,5 мин при стандартном отклонении 3,2 мин, с ним среднее 44,1 мин и отклонение 16,3 мин. Поэтому для описания группы лучше подходит медиана.

Что здесь есть: объём выборки, единицы, центр (медиана или среднее), разброс, выброс с обоснованием и вывод. Округляйте до одного-двух знаков после запятой: «44,083333» в отчёте выглядит как непонимание, а не как точность.

Как с этим помогает Tilpi

Приложите к сообщению в Tilpi файл с данными и условие задания. Чат задания объяснит, какие показатели здесь нужны, посчитает их и разберёт по шагам, чем отличаются похожие функции и что значат полученные числа. Чат по своим файлам работает бесплатно: нужна подтверждённая почта, действуют лимиты на день и месяц.

ИИ для учёбы. По вашим лекциям.

Пришлите в чат лекцию, задачу или свой текст. Тилпи объяснит тему со ссылкой на страницу, разберёт решение по шагам и проверит оформление.

Разобрать свои данные в Tilpi