Гистограмма. Числовые метки
Рассмотрим пример того, как можно улучшить результаты группировки данных с помощью надстройки и приблизить их к требованиям стандартов и здравого смысла.
Для начала создадим копию текущего листа, где нахдятся результаты группировки с помощью надстройки. Напомним, что в нижней части окна Excel расположены вкладки рабочих листов и средства перехода между листами в пределах рабочей книги. Щёлкнем по вкладке текущего листа правой кнопкой мыши и выберем пункт
Move or Copy
Переместить или скопировать
Копируем лист
Поставим галочку (флажок)
Create a copy
Создать копию
В разделе
Before sheet
перед листом
выберем место для копии листа:
move to end
переместить в конец
Нажимаем ОК.
Параметры копирования
В конце ряда вкладок появится ещё одна, соответствующая скопированному листу. Двойным щелком по вкладке перейдём к редактированию и переименуем новый лист.
Добавим к готовой таблице с результатами группировки новые столбцы. Для этого щёлкнем правой кнопкой мыши по названию столбца — в нашем примере это буква С в верхней части столбца — и выберем пункт
Insert
Вставить
Новый столбец появится СЛЕВА от выбранного.
Добавление столбца
Скопируем столбец «Нижняя граница интервала» из нашей вспомогательной таблицы. Для этого выделим диапазон ячеек, нажмите правую кнопку мыши и выберем
Copy
Копировать
или комбинацию клавиш Ctrl + C.
Копирование диапазона
Выберем ячейку, начиная с которой расположится наш новый столбец, нажмём правую кнопку мыши и выберем вариант вставки — см. рисунок:
Paste
Вставить
или комбинацию клавиш Ctrl + V.
Вставка из буфера
Для построения графика нам потребуется столбец «Середина интервала». Добавим столбец между «ниж гр» и «верх гр» и озаглавим его «Середина». Для вычисления среднего значения введём формулу = (D4+F4) /2 и скопируем формулу в остальные ячейки столбца двойным щелчком по маркеру заполнения.
Перед столбцом накопленных частот Cumulative добавим столбец для относительных частот. Озаглавим его «n %». Поскольку у нас уже есть кумулята (накопленные частоты), используем простой приём: ОТНОСИТЕЛЬНАЯ ЧАСТОТА — ЭТО РАЗНОСТЬ СОСЕДНИХ ЗНАЧЕНИЙ НАКОПЛЕННОЙ ЧАСТОТЫ. Это правило основано на теории вероятностей, и звучит оно так.
КУМУЛЯТА — ЭТО ИНТЕГРАЛ ОТ ГИСТОГРАММЫ. Другими словами, кумулята — это сумма относительных частот нарастающим итогом.
И наоборот.
ГИСТОГРАММА — ЭТО ПРОИЗВОДНАЯ ОТ КУМУЛЯТЫ. Другими словами, относительная частота равна разности соседних значений кумулятивной (накопленной) относительной частоты.
В первую ячейку столбца относительных частот копируем первое значение накопленной частоты (можно считать, что предыдущая накопленная частота равна нулю). Для этого вводим формулу =I4. Во вторую ячейку вводим формулу для вычисления разности =I5-I4. Затем заполняем остальные ячейки столбца двойным щелчком по маркеру заполнения.
Относительная частота
Построим первый график — гистограмму. Выбираем в верхнем меню
Insert — Charts — Inset Column or Bar Chart — 2-D Column — Clustered Column
Вставка — Диаграммы — Вставить гистограмму или линейчатую диаграмму — Гистограмма — Гистограмма с группировкой
В середине листа появится незаполненный прямоугольник, указывающий границы будущего графика.
Столбиковая диаграмма
Выберем данные для построения гистограммы. Щёлкнем правой кнопкой по пустому прямоугольнику графика и выберем пункт
Select Data
Выбрать данные
Выбор данных для графика
Появляется диалоговое окно
Select Data Source
Выбор источника данных
В группе
Legend Entries (Series)
Элементы легенды (ряды)
нажимаем кнопку
Add
Добавить
Добавление данных для графика
В диалоговом окне
Edit Series
Изменение ряда
в разделе
Series Values
Значения
выбираем диапазон ячеек накопленных частот. Последнюю ячейку
More
Ещё
не включаем, чтобы использовать числовой масштаб по горизонтальной оси.
Выбор относительных частот
На графике появляется изображение столбиков. Можно видеть, что при выборе данных для столбиковой диаграммы нам даже не предлагают ввести координаты по горизонтальной оси. На диаграмме под каждым столбиком просто указан его порядковый номер. Это и есть масштаб по горизонтальной оси — середина столбика соответствует номеру столбца.
Столбиковая диаграмма
Для указания масштаба по горизонтальной оси есть два пути.
Первый вариант: метки на столбиковой диаграмме соответствуют серединам интервалов (среднему значению между нижней и верхней границами интервала). Примерно так происходит при построении гистограммы с помощью надстройки (правда, там выводится верхняя граница). На таком графике можно делать расчёты, поскольку есть масштаб.
Второй вариант: указывать интервал группировки (нижнюю и верхнюю границы). Так делается при построении гистограммы с помощью готового статистического графика. На такой график приятно посмотреть, но использовать такой масштаб не очень удобно — придётся ориентироваться на границы интервалов вместо масштаба.
В обоих случаях это будут текстовые метки.
Рассмотрим первый вариант. Установим в качестве меток середины интервалов. В далоговом окне
Select Data Source
Выбор источника данных
в разделе
Horizontal (Category) Axis Labels
Подписи горизонтальной оси (категории)
нажмём
Edit
Изменить
Выбор меток
В диалоговом окне
Axis Labels
Подписи оси
выберем диапазон ячеек
Axis label range
Диапазон подписей оси
Выделяем столбец середин интервалов и нажимаем OK. На графике появляются нужные метки.
Середины интервалов
Убираем заголовок графика
Chart Elements — Chart Title
Элементы диаграммы — Название диаграммы
Настраиваем ширину столбиков, цвет и заливку — как описано выше.
Настроим масштаб по вертикальной оси. Можно видеть, что числа здесь указаны с точностью до сотых долей процента. Упростим картину и оставим только целые значения процентов. Дважды щёлкнем по вертикальной оси. В окне
Format Axis
Формат оси
установим настройку:
Axis Options — Number — Decimal places = 0.
Параметры оси — Число — Число десятичных знаков = 0
По вертикальной оси остались красивые круглые числа.
Масштаб
Для дальнейшего украшения диаграммы настроим отображение наших меток. Дважды щёлкнем по горизонтальной оси. В меню
Format Axis
Формат оси
выберем вкладку
Axis Options
Параметры оси
Установим вывод текстовых меток через одну:
Labels — Interval between labels — Specify interval unit = 2
Подписи — Интервал между подписями — Единица измерения интервала = 2
Прореживание меток
С такой настройкой текстовых меток будет поменьше. Они будут выводиться через одну.
Прореженные метки
Метки по-прежнему располагаются под своими столбиками, но теперь на них стало приятнее смотреть. Не считая, конечно, дробной части.
Уберём вывод дробной части в наших метках. Зададим числовые метки и настроим вывод целой части числа:
Format Axis — Axis Options — Number
Формат оси — Параметры оси — Число
Category — Number
Категория — Числовой
Decimal places = 0
Число десятичных знаков = 0
Округление меток
После такой настройки метки выводятся в виде целых чисел. Конечно, эти числа не совсем «красивые», но на них уже можно ориентироваться. В идеале масштаб должен быть оформлен в таком стиле: 230 — 240 — 250.
Целочисленные метки