Анализ распределения в Excel

Перед столбцом накопленных частот 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

Формат оси

выберем вкладку