Описательная статистика в Excel: формулы и готовый файл

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

Разберём два пути: набор формул, который копируется целиком, и расчёт всех показателей одной кнопкой. И отдельно — что из полученного идёт в таблицу диплома, а что остаётся черновиком.

Что входит в описательную статистику

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

Дальше по всей статье работаем на одном наборе данных — ситуативная тревожность 20 студентов: 48, 52, 45, 41, 54, 47, 50, 43, 46, 51, 44, 49, 53, 42, 47, 55, 40, 46, 50, 45. Значения лежат в столбце B, строки со 2-й по 21-ю.

Готовый набор формул

Скопируйте этот блок в столбец рядом с данными — он посчитает всё сразу. Диапазон B2:B21 замените на свой.

=СЧЁТ(B2:B21)
=СРЗНАЧ(B2:B21)
=МЕДИАНА(B2:B21)
=МОДА(B2:B21)
=СТАНДОТКЛОН(B2:B21)
=ДИСП(B2:B21)
=СТАНДОТКЛОН(B2:B21)/КОРЕНЬ(СЧЁТ(B2:B21))
=МИН(B2:B21)
=МАКС(B2:B21)
=МАКС(B2:B21)-МИН(B2:B21)
=КВАРТИЛЬ(B2:B21;1)
=КВАРТИЛЬ(B2:B21;3)
=СТАНДОТКЛОН(B2:B21)/СРЗНАЧ(B2:B21)*100
=СКОС(B2:B21)
=ЭКСЦЕСС(B2:B21)

Вот что каждая формула означает и что получилось на наших двадцати значениях.

Пятнадцать показателей описательной статистики на данных о тревожности (n = 20)

Проверьте себя: КОРЕНЬ(18,67) = 4,32 и 0,97 × КОРЕНЬ(20) = 4,32. Не сходится — в диапазон попали лишние ячейки.

Английские имена функций: COUNT, AVERAGE, MEDIAN, MODE, STDEV, VAR, MIN, MAX, QUARTILE, SKEW, KURT.

Как посчитать всё разом через «Пакет анализа»

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

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

Дальше: Данные → Анализ данных → Описательная статистика. Входной интервал B1:B21 вместе с заголовком, галочки «Метки в первой строке» и «Итоговая статистика», вывод — на новый лист.

Excel создаст лист с двумя столбцами: слева названия, справа значения. Числа совпадут с нашей таблицей — 47,40, 4,32, 18,67, 0,97. Названия только звучат иначе: «Стандартная ошибка» — это m, «Интервал» — размах, «Асимметричность» — асимметрия.

Чего в выводе нет совсем: квартилей и коэффициента вариации. Их дописывают формулами КВАРТИЛЬ и σ / M × 100 — те самые две строки из блока выше.

Какие показатели нужны в дипломе, а какие нет

Посчитать пятнадцать показателей — пять минут. Вынести все пятнадцать в таблицу третьей главы — верный способ услышать на защите «а зачем здесь эксцесс?».

Разберём по пунктам, зачем нужен каждый показатель из зелёной и жёлтой зон.

• n — объём выборки. Без него таблица бессмысленна: «47,40 ± 4,32» на трёх и на трёхстах испытуемых весят по-разному.
• M — среднее. Основной показатель уровня, если шкала количественная и распределение близко к нормальному.
• σ — стандартное отклонение. Насколько однородна группа. Идёт в запись «M ± σ».
• m — ошибка среднего. Альтернатива σ в записи «M ± m». Всегда меньше σ, поэтому поясняйте в подписи, что стоит после знака ±.
• Me, Q1, Q3 — медиана и квартили. Нужны при порядковой шкале (баллы теста, ранги) или перекошенном распределении. Тогда пишут «Me [Q1; Q3]».
• V — коэффициент вариации. Одна цифра вместо абзаца рассуждений: до 10 % — выборка однородная, 10–20 % — средняя, выше 20 % — разнородная. У нас 9,1 %.
• Асимметрия и эксцесс. Черновая прикидка: оба в пределах от −1 до +1 — распределение похоже на нормальное. Для главы 3 этого мало, нормальность проверяют критерием.

Дисперсия в таблицу не идёт: «квадратные баллы» читать невозможно. Мода на непрерывных данных бесполезна — при двадцати разных числах она покажет случайное значение.

Всё это уже посчитано в готовом файле

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

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

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

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

Числа во всех трёх колонках совпадут до сотых. Разница в том, сколько показателей придётся дописывать руками и что делать с результатом дальше.

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

Описательную статистику приводят по каждой группе отдельно, а не общей кучей: контрольная и экспериментальная — две колонки.

Описательная статистика ситуативной тревожности до эксперимента, баллы

Под таблицей — два-три предложения, которые её объясняют:

Средний уровень ситуативной тревожности в контрольной группе составил 47,40 ± 0,97 балла (M ± m), в экспериментальной — 46,85 ± 1,08 балла. Коэффициенты вариации 9,1 % и 10,3 % указывают на однородность обеих выборок. Медианы близки к средним, распределение симметрично (асимметрия 0,07), что позволяет применять параметрические критерии.

При порядковой шкале ту же мысль записывают через медиану: «Me = 47,0 балла [44,8; 50,3]». Разбор форматов — в статье как записывать M, SD, Me и квартили, а что писать вокруг таблицы — в отдельном руководстве.

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

• Выносят в таблицу все пятнадцать показателей. Таблица на пол-листа, читать её невозможно, а на защите спросят про каждую колонку.Оставьте n, M, σ (или m) и при необходимости Me с квартилями. Остальное — в приложение.
• Не поясняют, что стоит после знака ±. «47,40 ± 0,97» и «47,40 ± 4,32» — одни и те же данные, разница в четыре с половиной раза.В шапке таблицы или в подписи прямо напишите «M ± m» либо «M ± σ».
• Берут «по совокупности» вместо «по выборке». СТАНДОТКЛОНП и ДИСПР занижают разброс, и различия между группами выглядят значимее, чем есть.В дипломе нужны СТАНДОТКЛОН и ДИСП.
• Считают описательную статистику по всей выборке разом. Смешанные группы дают среднее, которого нет ни в одной из них.Считайте по каждой группе отдельно, а общие цифры приводите только для описания выборки во второй главе.
• Правят данные, а таблицу из «Пакета анализа» оставляют старую. Вывод надстройки — статичные числа, они не пересчитываются.После правки данных удалите лист вывода и запустите расчёт заново. Или используйте формулы — они живые.
• Копируют данные из Word и получают текст. СРЗНАЧ молча пропустит текстовые ячейки, и n окажется меньше реального.Сверьте СЧЁТ с числом испытуемых. Не сходится — «Данные → Текст по столбцам».

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

Как сделать описательную статистику в экселе, если нет «Анализа данных»

Формулами из блока выше — они работают в любой версии, включая веб-Excel и Google Таблицы. Результат будет тот же, плюс квартили и коэффициент вариации, которых в выводе надстройки нет.

Сколько показателей приводить в таблице

Три-четыре на группу: n, M, σ или m. Медиану и квартили добавляют при порядковой шкале или перекошенном распределении. Полный набор из пятнадцати чисел уместен только в приложении.

Почему МОДА выдаёт ошибку #Н/Д

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

Совпадут ли числа Excel и SPSS

Да. Расхождение бывает только у квартилей: КВАРТИЛЬ и метод SPSS по умолчанию по-разному интерполируют, поэтому Q1 может отличаться на десятые доли. На выводы это не влияет.

Достаточно ли асимметрии и эксцесса, чтобы доказать нормальность

Нет, это только прикидка: оба показателя в пределах от −1 до +1 — распределение похоже на нормальное. Рецензент ждёт формального критерия, поэтому проверяйте Шапиро-Уилком — на выборках до 50 человек он точнее.

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

1. Соберите значения каждой группы в отдельный столбец — без заголовков и пустых строк внутри.
2. Скопируйте набор из пятнадцати формул рядом с данными, поменяв диапазон на свой.
3. Или включите «Пакет анализа» и запустите «Описательную статистику», а квартили и коэффициент вариации допишите формулами.
4. Проверьте: СЧЁТ совпадает с числом испытуемых, корень из дисперсии равен σ.
5. В таблицу диплома вынесите n, M, σ (или m) по каждой группе, остальное оставьте в приложении.
6. Под таблицей напишите два-три предложения об уровне показателя и однородности выборки.

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

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

• Описательная статистика в дипломе — что писать в тексте вокруг посчитанной таблицы.
• Среднее значение в Excel — разбор функции СРЗНАЧ и её ловушек.
• Стандартное отклонение в Excel — развилка СТАНДОТКЛОН и СТАНДОТКЛОНП.
• Пакет анализа данных в Excel — как включить надстройку и что ещё она умеет.
• Как записывать M, SD, Me и квартили — правила оформления записи в работе.
• Как проверить нормальность распределения — следующий шаг после описательной статистики.

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