Гистограмма в Excel: распределение и столбики «до/после»

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

Разберём обе: как построить настоящую гистограмму распределения и как сделать привычные столбики «до/после» с планками погрешностей.

Что такое гистограмма — и почему в Excel их две

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

Столбчатая диаграмма, которую Excel называет «Гистограмма» в меню «Вставка», — это рисунок сравнения. По горизонтали — категории (До, После, КГ, ЭГ), по вертикали — значение показателя. Между столбиками зазор, потому что категории не связаны. Она отвечает на вопрос «где больше».

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

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

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

Пример. В дипломе по физической культуре у 60 первокурсников измерили прыжок в длину с места. Минимум — 178 см, максимум — 248 см. Задача: показать, как распределился результат по группе.

Шаг 1. Определите количество интервалов

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

=ОКРУГЛ(1+3,322*LOG10(СЧЁТ(B2:B61));0)

Для 60 человек она даёт 7. Ширина интервала — размах, делённый на это число:

=(МАКС(B2:B61)-МИН(B2:B61))/7

Получается 10 см. Значит, интервалы будут 178–188, 189–198 и так далее.

Шаг 2. Посчитайте частоты

Выпишите в столбец D верхние границы интервалов: 188, 198, 208, 218, 228, 238, 248. Дальше — функция ЧАСТОТА (в английском Excel FREQUENCY):

=ЧАСТОТА(B2:B61;D2:D8)

В Excel 2021, 365 и в Google Таблицах достаточно нажать Enter — результат «разольётся» по столбцу сам. В версиях до 2019 выделите сразу восемь ячеек и нажмите Ctrl+Shift+Enter: это формула массива.

Если формулы массива пугают, тот же результат даёт СЧЁТЕСЛИМН — по одной формуле на интервал:

=СЧЁТЕСЛИМН($B$2:$B$61;">=189";$B$2:$B$61;"<=198")

Частотная таблица результата прыжка в длину с места, n = 60

Сумма частот обязана совпасть с объёмом выборки: 3 + 7 + 12 + 17 + 12 + 6 + 3 = 60. Не совпало — часть значений выпала за границы.

Шаг 3. Постройте и уберите зазор

Выделите два столбца — подписи интервалов и частоты — и нажмите Вставка → Гистограмма → Гистограмма с группировкой. Excel нарисует столбики с промежутками, и это ещё не гистограмма.

Правый клик по любому столбику → Формат ряда данных → Боковой зазор: 0%. Столбики сомкнутся. Добавьте тонкий белый контур, чтобы границы интервалов остались видны.

Что показывает форма

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

Как построить столбчатую «до/после» с планками погрешностей

Это второй смысл слова «гистограмма» — тот самый рисунок сравнения в эмпирической главе. Его сильная версия — с «усами» M ± m, которые показывают точность оценки среднего.

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

=СТАНДОТКЛОН(B2:B61)/КОРЕНЬ(СЧЁТ(B2:B61))

Подготовка данных для диаграммы с планками погрешностей

Дальше по шагам:

1. Выделите столбцы с подписями и средними → Вставка → Гистограмма с группировкой.
2. Конструктор → Добавить элемент диаграммы → Предел погрешностей → Дополнительные параметры.
3. Выберите Пользовательская → Указать значение и в оба поля — плюс и минус — подставьте столбец с ошибками среднего.
4. Уберите легенду (ряд один) и линии сетки, добавьте подписи данных.

Откуда берутся σ и m и чем они отличаются — в статье «Стандартное отклонение в Excel». Какой вообще тип диаграммы взять под ваши данные и как довести его до вида «можно вставлять в работу» — в статье «Диаграммы для диплома в Excel».

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

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

Гистограмма живёт на листе «20. Графики»: там уже стоит частотная таблица на семь интервалов с автоматическими границами и обе диаграммы — распределение и «до/после». Границы пересчитываются под ваш размах, зазор между столбиками уже убран.

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

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

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

Как оформить рисунок в дипломе

• Подпись — снизу, без заголовка внутри картинки. «Рисунок 3 — Распределение результата прыжка в длину с места у студентов первого курса (n = 60)». Заголовок-заглушку, который Excel ставит сверху, удалите.
• Ось Y — что считаем. Для гистограммы распределения это «число студентов» или «частота», а не «количество». Для столбчатой — показатель с единицами: «см», «баллы», «с».
• Ось X — интервалы с единицами. Подписывайте «178–188», «189–198», а не «1», «2», «3»: комиссия должна читать рисунок без текста вокруг.
• Объём выборки — в подписи. «n = 60» отвечает на первый же вопрос: по скольким людям построено.
• Ч/б-дружелюбность. Дипломы печатают на чёрно-белом принтере. Дайте столбикам узорную заливку и тонкий контур: «Формат ряда данных → Заливка → Узорная заливка».
• Перенос в Word. Копируете диаграмму в Excel и вставляете через «Специальная вставка → Рисунок (метафайл)» — так она не поедет при правках.

Требования к нумерации, ссылке на рисунок в тексте и расположению подписи — в статье «Как оформить таблицы и рисунки в дипломе».

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

• Оставляют зазор между столбиками гистограммы распределения. Рисунок перестаёт быть гистограммой и превращается в столбчатую диаграмму по интервалам.Формат ряда данных → Боковой зазор 0%, плюс белый контур для границ.
• Строят гистограмму по средним значениям. Из двух чисел «до» и «после» распределение не получится — выйдут два столбика.Гистограмма распределения строится по исходному столбцу всех значений выборки.
• Берут слишком много интервалов. При 20 карманах на 30 человек в каждом окажется по одному значению, и форма исчезнет.Считайте по Стёрджесу: для 30 человек — 6 интервалов, для 60 — 7, для 100 — 8.
• Забывают, что ЧАСТОТА возвращает на одно число больше. Последняя ячейка — это всё, что выше верхней границы; её теряют, и сумма частот не сходится.Выделяйте под формулу на одну ячейку больше, чем границ, и сверяйте сумму с n.
• Судят о нормальности только по виду гистограммы. «Похоже на колокол» — не аргумент для комиссии, особенно на выборке до 50 человек.Приложите расчёт: критерий Шапиро-Уилка или асимметрию и эксцесс.
• Подписывают гистограммой любую диаграмму подряд. Excel называет так все столбики, но в тексте диплома термин читают по-статистически.В подписи пишите по смыслу: «распределение …» или «средние значения … до и после».

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

Гистограмма и столбчатая диаграмма — это одно и то же?

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

Какие типы гистограмм есть в Excel

Три основных: с группировкой (столбики рядом — сравнение групп), с накоплением (столбики друг на друге — структура целого) и нормированная с накоплением (то же, но всё приведено к 100%). Отдельно в новых версиях есть тип «Гистограмма» из раздела «Статистические диаграммы» — он сам разбивает данные на интервалы.

Когда нужна гистограмма с накоплением

Когда один столбик — это целое, состоящее из частей. Например, по горизонтали КГ и ЭГ, а внутри каждого столбика доли высокого, среднего и низкого уровня мотивации. Для сравнения «до/после» одного показателя она не подходит: части там не складываются в осмысленное целое.

Сколько интервалов брать для гистограммы

По формуле Стёрджеса: 1 + 3,322 · lg(n). Для 30 человек — 6, для 60 — 7, для 100 — 8. Округляйте до целого и берите круглую ширину интервала: 5, 10, 25 — так подписи читаются легче.

Почему ЧАСТОТА выдаёт одно число вместо столбца

Это формула массива. В Excel 2019 и старше её надо вводить в заранее выделенный диапазон и подтверждать сочетанием Ctrl+Shift+Enter, а не просто Enter. Либо посчитайте частоты по одной через СЧЁТЕСЛИМН — работает в любой версии.

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

1. Решите, какая гистограмма нужна: распределение частот или сравнение «до/после».
2. Для распределения — сложите все значения в один столбец и посчитайте число интервалов по Стёрджесу.
3. Выпишите верхние границы интервалов и посчитайте частоты через ЧАСТОТА или СЧЁТЕСЛИМН, сверьте сумму с n.
4. Выделите границы и частоты → Вставка → Гистограмма с группировкой → Боковой зазор 0%.
5. Для сравнения «до/после» постройте столбики по средним и добавьте предел погрешностей со значениями m.
6. Подпишите оси с единицами, уберите заголовок внутри картинки, вставьте в Word и подпишите рисунок снизу.

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

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

• Диаграммы для диплома в Excel — какой тип диаграммы выбрать под ваши данные и как оформить по ГОСТ.
• Как проверить нормальность распределения — что делать после того, как гистограмма показала форму.
• Стандартное отклонение в Excel — откуда берутся σ и ошибка среднего для планок погрешностей.
• Как оформить таблицы и рисунки в дипломе — подписи, нумерация и ссылки по ГОСТу.
• Статистика в Excel: формулы — остальные расчёты для третьей главы.

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