Валентин Арьков - Анализ распределения в Excel

Уберём вывод дробной части в наших метках. Зададим числовые метки и настроим вывод целой части числа:

Format Axis — Axis Options — Number

Формат оси — Параметры оси — Число

Category — Number

Категория — Числовой

Decimal places = 0

Число десятичных знаков = 0

Округление меток

После такой настройки метки выводятся в виде целых чисел. Конечно, эти числа не совсем «красивые», но на них уже можно ориентироваться. В идеале масштаб должен быть оформлен в таком стиле: 230 — 240 — 250.

Целочисленные метки

Построим второй вариант гистограммы и укажем интервалы группировки в качестве меток. Для начала скопируем предыдущую гистограмму и вставим этот график под первым: Ctrl + C, Ctrl + V. Настроим высоту графиков, что уместить их на одном листе в пределах видимой части экрана. Для этого передвинем нижнюю границу рамки графика.

Изменение размеров

Сформируем метки в виде интервалов — в стиле меток на нашей первой гистограмме, построенной с помощью статистической диаграммы. Для начала подготовим место для них. Вставим новый столбец перед столбцом Frequencyи назовём его Интервал. Отрегулируем ширину столбца, чтобы название умещалось целиком.

Каждая метка будет строкой символов. Будем объединять части нашей текстовой метки с помощью символа &(амперсанд). Этот символ часто используется для обозначения логической операции «И», а также для объединения текстовых строк.

Вот пример формулы для первого интервала, границы которого записаны в ячейках D4и F4, — см. рисунок.

В этой формуле знак амперсанда объединяет следующие части нашей метки:

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

Пример полученной метки для первого интервала: (190..195]. Как и раньше, круглая скобка означает, что нижняя граница не включается в интервал, а квадратная скобка — что верхняя граница включается.

Текстовая метка

Метка готова, и она нас устраивает. Копируем её в остальные элементы колонки двойным щелчком по маркеру заполнения. Удаляем лишнюю ячейку

More

Ещё

Готовые метки интервалов

Зададим для графика наш столбец интервальных меток:

Select Data — Horizontal (Category) Axis Labels — Edit

Выбрать данные — Подписи горизонтальной оси (категории) — Изменить

Выбор меток

Можно видеть, что такие текстовые метки больше подходят для украшения диаграммы. А вот использовать их в качестве масштаба будет гораздо труднее — по сравнению с не очень красивыми, но зато точными числами из предыдущего варианта.

Теперь построим кумуляту — график накопленных относительных частот. Расположим его под гистограммой.

Кумулята — это экспериментальная оценка формы графика функции распределения. Теоретическая кривая будет красивой и гладкой — мы познакомились с ней в начале работы, обсуждая свой вариант задания. Экспериментальная оценка — ломаная линия, да ещё и с погрешностями. Эти случайные ошибки вызваны ограниченным, не бесконечным объёмом выборки. В любом случае, эти графики начинаются в нуле и постепенно растут до 100%.

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

Построим график в виде ломаной линии:

Insert — Charts — Insert Scatter (X, Y) or Bubble Chart

Вставка — Диаграммы — Вставить точечную (X, Y) или пузырьковую диаграмму

Вставка графика Y (X)

Выбираем тип графика

Scatter — Scatter with Straight Lines

Точечная — Точечная с прямыми отрезками

Это просто ломаная линия без маркеров точек.

Ломаная линия

Выбираем данные для графика:

Select Data — Select Data Source — Legend Entries (Series) — Add

Выбрать данные — Выбор источника данных — Элементы легенды (ряды) — Добавить

В окне

Edit Series

Изменение ряда

выбираем следующие данные.

Столбец «иксов» — верхние границы:

Series X Values

Значения Х

Столбец «игреков» — накопленные частоты:

Series Y Values

Значения Y

Убираем заголовок диаграммы:

Chart Elements — Chart Title

Элементы диаграммы — Название диаграммы

Настраиваем цвет линии на графике.

Format Data Series — Series options — Fill & Line — Line

Формат ряда данных —Параметры ряда — Заливка и границы — Линия

Line — Solid line

Линия — Сплошная линия

Color — Black

Цвет — Чёрный

Width = 0.5 pt

Ширина — 0,5 пт

Если отрезков много, то ломаная линия выглядит как гладкая кривая.

Кумулята

Настроим числовые метки на вертикальной оси, чтобы выводились целые числа:

Format Axis — Axis Options — Number — Decimal places — 0

Формат оси — Параметры оси — Число — Число десятичных знаков — 0

Целочисленные метки

Установим диапазоны значений по осям.

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

Category — Percentage

Категория — Процентный

Axis Options — Bounds

Параметры оси — Границы

Minimum — 0

Минимум — 0

Maximum — 1

Максимум — 1

Горизонтальная ось — в соответствии с интервалами группировки — от 190 до 310.

Кумулята

Подгоняем размеры графика и размещаем его под гистограммой. Можно сделать это вручную.

Если захочется особой точности, поработаем через меню параметров графика (числа условные).

Format Chart Area — Chart Options — Size & Properties — Size

Формат области диаграммы — Параметры диаграммы — Размер и свойства — Размер

Height — 1.8 in

Высота — 7,62 см

Width — 5.3 in

Ширина — 12,7 см

В английской версии пакета размеры измеряются в дюймах. В русской версии — в сантиметрах. Можем установить точные значения размеров вручную.

Размер диаграммы

Окончательно совмещаем маштаб гистограммы и кумуляты: начало первого интервала 190, конец последнего интервала 310. Положения этих двух меток на обоих графиках должны совпадать.