Задание звучит коротко: «Проведите описательный анализ выборки». Нужно посчитать среднее, медиану, разброс, найти выбросы, построить гистограмму и написать пару фраз о том, что видно в данных. Всё это делается в 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):
- «Файл», «Параметры», «Надстройки».
- Внизу в списке «Управление» выберите «Надстройки Excel» и нажмите «Перейти».
- Отметьте «Пакет анализа» и нажмите «ОК».
На 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 файл с данными и условие задания. Чат задания объяснит, какие показатели здесь нужны, посчитает их и разберёт по шагам, чем отличаются похожие функции и что значат полученные числа. Чат по своим файлам работает бесплатно: нужна подтверждённая почта, действуют лимиты на день и месяц.
