Как сделать описательную статистику в Excel: пошаговая инструкция с расшифровкой результатов

Что такое описательная статистика и когда она нужна

Описательная статистика — раздел статистики, который сводит выборку к итоговым показателям. Она отвечает на вопрос «как устроены данные», но не сравнивает группы и не объясняет причины. Показатели образуют четыре группы: центральная тенденция (среднее, медиана, мода), разброс (стандартное отклонение, дисперсия, размах), форма распределения (асимметрия, эксцесс) и экстремальные значения (минимум, максимум). Применяется в дипломных работах в главе с анализом данных, в производственном контроле качества, в маркетинговых исследованиях — везде, где нужно охарактеризовать выборку перед началом сравнений.

Шаг 1 — Включите надстройку «Пакет анализа»

Пакет анализа (Analysis ToolPak) — надстройка Excel, отключённая по умолчанию. Без этого шага кнопки «Анализ данных» на ленте не будет, и запустить описательную статистику одной командой не получится.

Windows: Файл → Параметры → Надстройки → в поле «Управление» выбрать «Надстройки Excel» → Перейти → поставить галочку напротив «Пакет анализа» → ОК.

Mac: Средства → Надстройки для Excel → поставить галочку напротив «Пакет анализа» → ОК.

Если меню не найти, введите «Надстройки» в строку поиска Excel и перейдите по результату. После активации вкладка «Данные» покажет кнопку «Анализ данных» в группе «Анализ».

Шаг 2 — Запустите инструмент и настройте параметры

Путь запуска: вкладка Данные → группа «Анализ» → Анализ данных → в списке выбрать Описательная статистика → ОК. Откроется диалоговое окно с шестью параметрами. Каждый отвечает за свою часть настройки.

Входной интервал и группировка

Входной интервал — диапазон ячеек с исходными данными. Выделите один столбец (одна переменная) или несколько столбцов сразу (несколько переменных для одновременного расчёта). В параметре «Группировать по» оставьте значение «Столбцам» — стандарт для большинства задач. Если в первой строке диапазона есть заголовок, установите галочку «Метки в первой строке»: без неё Excel включит текст в расчёт и выдаст ошибку. При нескольких столбцах каждый набор данных получит отдельный блок показателей в выводе.

Параметры вывода и флажки показателей

Выходной диапазон — укажите свободную ячейку на текущем листе, задайте новый лист или новую книгу. На том же листе удобнее: данные и результат видны рядом. Флажок «Итоговая статистика» — обязателен: без него таблица не появится совсем. Флажок «Уровень надёжности» (по умолчанию 95%) выводит ±Δ — это полуширина доверительного интервала, а не сам интервал целиком (подробнее в разделе расшифровки). Флажки «K-й наибольший» и «K-й наименьший» добавляют конкретные порядковые значения — устанавливайте при необходимости. Нажимайте ОК.

После нажатия ОК Excel вставляет таблицу в указанный диапазон. Это статичные значения, не формулы: при изменении исходных данных расчёт придётся запустить заново.

Шаг 3 — Альтернатива: формулы вместо надстройки

Когда Пакет анализа недоступен, когда данные регулярно меняются и нужен автопересчёт, или когда квартили и коэффициент вариации нужны в той же таблице — используйте формулы. Каждая ячейка с формулой пересчитывается автоматически при любом изменении источника. Достаточно один раз составить блок из 15 формул, заменив ссылку на нужный диапазон.

Полная таблица 15 показателей с формулами

Замените B2:B31 на реальный диапазон своих данных.

Важно: СТАНДОТКЛОН.В() и ДИСП.В() делят на n−1, что корректно для выборочных данных. Устаревшие функции СТАНДОТКЛОН() и ДИСП() работают аналогично, но с Excel 2010 рекомендуются версии с суффиксом .В. Функции СТАНДОТКЛОНП() и ДИСПР() делят на n и занижают разброс: они подходят только для генеральной совокупности.

Чего нет в выводе Пакета анализа — и как добавить

Три показателя Пакет анализа не рассчитывает. Добавьте их формулами рядом с таблицей вывода: Q1 — =КВАРТИЛЬ(диапазон;1), Q3 — =КВАРТИЛЬ(диапазон;3), КВ — =СТАНДОТКЛОН.В(диапазон)/СРЗНАЧ(диапазон)*100. Кроме того, часть терминов в выводе расходится со стандартными обозначениями:

Что означают показатели: полная расшифровка

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

Среднее, медиана, мода — в чём разница

Среднее (M) — арифметическое среднее выборки. Оно чувствительно к выбросам: одно экстремально высокое значение сдвигает среднее вверх и искажает картину. Медиана (Me) — значение, ниже и выше которого ровно половина выборки. Устойчива к выбросам: при длинном хвосте распределения точнее отражает «типичное» значение, чем среднее. Классический пример — распределение зарплат: несколько очень высоких значений завышают среднее, тогда как медиана остаётся на уровне большинства. Мода (Mo) — наиболее частое значение. Осмыслена для дискретных данных (баллы, категории). На непрерывных выборках каждое наблюдение уникально, и функция МОДА.ОДН() вернёт ошибку #Н/Д — это норма, а не сбой программы.

Стандартное отклонение против стандартной ошибки

Стандартное отклонение (σ) показывает, насколько значения рассеяны вокруг среднего. Это мера однородности группы: чем меньше σ, тем компактнее данные. Стандартная ошибка среднего (m) — принципиально другой показатель. Она оценивает точность оценки самого среднего: насколько среднее по выборке близко к истинному среднему генеральной совокупности. Рассчитывается как σ/√n, поэтому всегда меньше σ. При n=30 и σ=4,32 стандартная ошибка составит 0,79. В тексте дипломной работы после символа ± обязательно поясняйте, что именно написано: M±σ или M±m — это разные смыслы. Дисперсия (σ²) — квадрат стандартного отклонения. В таблицу диплома её не включают: единицы измерения квадратные, интерпретировать неудобно.

Коэффициент вариации — мера однородности выборки

Коэффициент вариации (КВ) = σ/M × 100%. Пакет анализа его не выводит — нужна формула. Главное применение: оценка однородности в относительных единицах, независимо от масштаба данных. Пороговые значения: КВ < 10% — однородная выборка, параметрические критерии применимы; 10–20% — средняя однородность; > 20% — разнородная, среднее ненадёжно. В примере выше КВ = 9,1% говорит об однородной группе.

Асимметрия и эксцесс — форма распределения

Коэффициент асимметрии (функция СКОС) описывает перекос: значение больше 0 — правый хвост длиннее (выбросы вверх), меньше 0 — левый хвост. Значение, близкое к нулю, указывает на симметричность. Эксцесс (функция ЭКСЦЕСС) характеризует остроконечность распределения: Excel считает избыточный эксцесс, поэтому для нормального распределения результат равен 0, а не 3. Черновая прикидка нормальности: оба показателя в пределах ±1. Для формального вывода в дипломной работе этого недостаточно — потребуется статистический критерий.

Уровень надёжности — это не доверительный интервал

Строка «Уровень надёжности (95,0%)» в выводе Пакета анализа содержит только полуширину доверительного интервала (±Δ), а не сам интервал. Полный доверительный интервал строится как M ± Δ. Пример: среднее 47,40, значение уровня надёжности 2,02 → ДИ от 45,38 до 49,42. Это означает: с вероятностью 95% истинное среднее генеральной совокупности находится в этом диапазоне.

Описательная статистика для дипломной работы

В главу с эмпирическим анализом выносятся не все 15 показателей. Обязательный минимум: объём выборки (n), среднее (M), стандартное отклонение (σ) или стандартная ошибка (m), коэффициент вариации (КВ). При перекошенном распределении вместо M±σ указывается Me [Q1; Q3]. Дисперсию в таблицу не включают — квадратные единицы нечитаемы. Моду для непрерывных данных тоже не включают: она почти всегда отсутствует. Каждая группа описывается отдельно: данные контрольной и экспериментальной группы не суммируют в одну строку.

Минимальный набор и оформление записи M ± m

Без объёма выборки (n) таблица описательной статистики теряет смысл: невозможно оценить точность показателей. Обязательно указывайте n в шапке столбца или в отдельной строке. В шапке таблицы прописывайте формат записи: «M ± m» или «M ± σ» — выберите один вариант и придерживайтесь его по всей работе. При порядковой шкале или заметном перекосе распределения используйте «Me [Q1; Q3]» вместо среднего.

Пример готовой таблицы и вывод под ней

Шаблон вывода под таблицей: «Среднее значение в контрольной группе составило 47,40 ± 0,97 балла. Коэффициент вариации в обеих группах не превышает 10%, что свидетельствует об однородности выборок. Асимметрия близка к нулю — распределение симметрично, параметрические критерии применимы».

Что делать после — проверка нормальности и выбор критерия

Описательная статистика — первый, а не финальный шаг анализа. Прежде чем выбрать метод сравнения групп, нужно проверить нормальность распределения. Предварительная прикидка — по значениям асимметрии и эксцесса: оба в пределах ±1 указывают на приблизительно нормальное распределение. Это ориентир, не доказательство. При нормальности переходят к параметрическим критериям (t-критерий Стьюдента, дисперсионный анализ); при ненормальности — к непараметрическим (критерий Манна-Уитни, Вилкоксон).

Критерий Шапиро-Уилка для выборок до 50 наблюдений

Критерий Шапиро-Уилка — рекомендуемый формальный тест нормальности при объёме выборки до 50 наблюдений: он точнее визуальной оценки и критерия Колмогорова-Смирнова на малых данных. В Excel встроенной функции для него нет — используйте онлайн-калькуляторы статистики или специализированные надстройки. Логика интерпретации: если p-значение > 0,05 (или W-статистика выше критического значения), нормальность не отвергается и параметрические критерии применимы.

Частые ошибки при расчёте описательной статистики в Excel

Большинство ошибок повторяются: выбирают неверную функцию для стандартного отклонения, не замечают, что таблица Пакета анализа не обновляется при изменении данных, или пугаются ошибки #Н/Д у функции МОДА. Ниже — разбор каждой типичной ситуации.

СТАНДОТКЛОН.В против СТАНДОТКЛОНП — почему важен выбор

Функции СТАНДОТКЛОНП() и ДИСПР() делят на n, а не на n−1, и систематически занижают оценку разброса на выборочных данных. При n=20 занижение составит около 2,5% для σ и около 5% для σ². Небольшое, но стабильное отклонение. Для любых выборочных данных используйте СТАНДОТКЛОН.В() и ДИСП.В().

Данные обновились, а таблица Пакета анализа — нет

Пакет анализа записывает в ячейки статичные числа — они не реагируют на изменения исходных данных. Чтобы обновить результат, удалите старый лист вывода и заново запустите «Описательная статистика». Если данные меняются регулярно, удобнее переключиться на формулы: СРЗНАЧ(), МЕДИАНА() и другие функции пересчитываются автоматически.

Ошибка #Н/Д у функции МОДА — это норма

Функция МОДА.ОДН() возвращает #Н/Д, когда в диапазоне нет ни одного повторяющегося значения. На непрерывных данных с достаточным объёмом выборки это стандарт: каждое наблюдение уникально, мода как показатель отсутствует. Просто не включайте её в таблицу для таких данных.

Хотите глубже освоить работу с данными в Excel — сводные таблицы, формулы, аналитические инструменты? В ProfiFuture есть программа Excel для работы с данными: около месяца обучения онлайн, без отрыва от работы, с господдержкой — 75% стоимости компенсирует образовательная квота.

Часто задаваемые вопросы

Как включить описательную статистику в Excel?

Пакет анализа по умолчанию отключён. Windows: Файл → Параметры → Надстройки → Пакет анализа → Перейти → поставить галочку → ОК. Mac: Средства → Надстройки для Excel → поставить галочку напротив «Пакет анализа» → ОК. После активации кнопка «Анализ данных» появится на вкладке «Данные» в группе «Анализ». Без этого шага описательную статистику одной командой запустить невозможно.

Где находится описательная статистика в Excel?

Вкладка «Данные» → группа «Анализ» → «Анализ данных» → «Описательная статистика». Если кнопки «Анализ данных» нет, надстройка не активирована — используйте инструкцию выше. На Mac вместо «Файл» → «Параметры» используется меню «Средства».

Что такое «Анализ данных» в Excel и как он связан с описательной статистикой?

«Анализ данных» — кнопка на ленте, открывающая меню инструментов Пакета анализа: гистограмма, корреляция, регрессия и ещё около 20 инструментов. «Описательная статистика» — один из них. Нажмите «Анализ данных», выберите «Описательная статистика» в списке и нажмите ОК.

Как рассчитать квартили и коэффициент вариации, если их нет в Пакете анализа?

Пакет анализа не выводит Q1, Q3 и коэффициент вариации. Добавьте формулами рядом с таблицей: Q1 — =КВАРТИЛЬ(диапазон;1), Q3 — =КВАРТИЛЬ(диапазон;3), КВ — =СТАНДОТКЛОН.В(диапазон)/СРЗНАЧ(диапазон)*100. Пороговые значения КВ: < 10% — однородная выборка, 10–20% — средняя, > 20% — разнородная.

Чем отличается стандартное отклонение от стандартной ошибки среднего?

Стандартное отклонение (σ) описывает разброс значений в выборке. Стандартная ошибка среднего (m) — точность оценки среднего, равна σ/√n. При n=20 и σ=4,32 стандартная ошибка составит 0,97 — она всегда меньше σ. В тексте дипломной работы после символа ± обязательно указывайте, что именно стоит: M±σ или M±m.

Что писать под таблицей описательной статистики в дипломе?

Два-три предложения: (1) средний уровень показателя по группам с M±m; (2) вывод об однородности через КВ; (3) симметричность по асимметрии и применимость параметрических критериев. Пример: «Среднее значение в контрольной группе составило 47,40 ± 0,97. КВ 9,1% указывает на однородность выборки. Асимметрия 0,07 свидетельствует о симметричном распределении — параметрические критерии применимы».

Совпадут ли результаты Excel и SPSS?

Да, числа совпадут до сотых. Расхождение возможно только у квартилей Q1/Q3: Excel и SPSS используют разные методы интерполяции, поэтому значения могут отличаться на десятые доли. На статистические выводы это не влияет.

Нужно ли проверять нормальность после описательной статистики?

Да, если планируете параметрические критерии (t-критерий Стьюдента, ANOVA). Первая прикидка — асимметрия и эксцесс в пределах ±1. Для формальной проверки при n ≤ 50 используйте критерий Шапиро-Уилка. При нормальности — параметрические критерии; при ненормальности — непараметрические (Манна-Уитни, Вилкоксон).

Почему результаты Пакета анализа не обновляются при изменении данных?

Пакет анализа выводит статичные числа, не формулы. При изменении исходных данных нужно удалить старый лист вывода и заново запустить расчёт. Альтернатива — формулы СРЗНАЧ, МЕДИАНА и другие: они пересчитываются автоматически при любом изменении.

Как считать описательную статистику в Google Таблицах без Excel?

Основные функции работают так же: COUNT, AVERAGE, MEDIAN, STDEV, MIN, MAX, QUARTILE, SKEW, KURT доступны в Google Таблицах и дают те же результаты. Пакета анализа там нет — только отдельные формулы. Используйте STDEV.S как аналог СТАНДОТКЛОН.В: числа совпадут с Excel.