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

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

Убираем маркеры точек:

Marker — Marker Options — None

Маркер — Параметры маркера — Нет

Настройка графика кумуляты

Отключаем легенду, и график практически готов.

Chart Elements — Legend

Элементы диаграммы — Легенда

Окончательный вид гистограммы и кумуляты

На графике можно обнаружить следующие особенности.

Появился дополнительный интервал, куда складывают всё, что не попало в наши интервалы:

More

Ещё

Горизонтальная ось в качестве текстовых меток содержит верхние границы интервалов группировки, причём эти метки расположены посередине интервалов. Получается, что значения накопленных частот привязаны к середине интервала, а не к его верхней границе.

По сути, здесь нет масштаба по оси Х, а использованы ПОРЯДКОВЫЕ НОМЕРА столбиков и ТЕКСТОВЫЕ ПОДПИСИ под ними — вместо числовых меток. Чтобы это увидеть, щёлкаем правой кнопкой по графику и выбираем пункт

Select Data

Выбрать данные

Выбор данных

Появляется диалоговое окно

Select Data Source

Выбор источника данных

Можно видеть два ряда данных в разделе

Legend Entries (Series)

Элементы легенды (ряды)

Первый набор данных — Frequency.

Второй набор данных — Cumulative %.

Кроме того, имеется раздел

Horizontal (Category) Axis Labels

Подписи горизонтальной оси (категории)

Здесь указаны метки для горизональной оси: 195, 200, 205 — весь столбец верхних границ.

Данные для графика и метки по горизонтальной оси

Выбираем ряд данных Frequency и нажимаем кнопку

Edit

Изменить

Ряд данных Frequency

Появляется диалоговое окно

Edit Series

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

Здесь есть возможность указать только имя ряда

Series Name

Имя ряда

В нашем примере это поле не заполнено.

Сами данные для графика

Series values

Значения

Здесь указан диапазон ячеек и первые несколько значений:

27, 27, 72, 10.

Здесь есть только «игреки», а «иксов» для графика нет. Та же картина наблюдается и для графика накопленных частот. В качестве «иксов» на графике использованы ПОРЯДКОВЫЕ НОМЕРА СТОЛБЦОВ, а по горизонтальной оси выводтся текстовые метки вместо масштаба.

Чтобы из полученных таблицы и графика сделать что-то осмысленное и вразумительное, придётся повозиться, и это будет темой следующего раздела.

Гистограмма. Числовые метки

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

Для начала создадим копию текущего листа, где нахдятся результаты группировки с помощью надстройки. Напомним, что в нижней части окна Excel расположены вкладки рабочих листов и средства перехода между листами в пределах рабочей книги. Щёлкнем по вкладке текущего листа правой кнопкой мыши и выберем пункт

Move or Copy

Переместить или скопировать

Копируем лист

Поставим галочку (флажок)

Create a copy

Создать копию

В разделе

Before sheet

перед листом

выберем место для копии листа:

move to end

переместить в конец

Нажимаем ОК.

Параметры копирования

В конце ряда вкладок появится ещё одна, соответствующая скопированному листу. Двойным щелком по вкладке перейдём к редактированию и переименуем новый лист.

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

Insert

Вставить

Новый столбец появится СЛЕВА от выбранного.

Добавление столбца

Скопируем столбец «Нижняя граница интервала» из нашей вспомогательной таблицы. Для этого выделим диапазон ячеек, нажмите правую кнопку мыши и выберем

Copy

Копировать

или комбинацию клавиш Ctrl + C.

Копирование диапазона

Выберем ячейку, начиная с которой расположится наш новый столбец, нажмём правую кнопку мыши и выберем вариант вставки — см. рисунок:

Paste

Вставить

или комбинацию клавиш Ctrl + V.

Вставка из буфера

Для построения графика нам потребуется столбец «Середина интервала». Добавим столбец между «ниж гр» и «верх гр» и озаглавим его «Середина». Для вычисления среднего значения введём формулу = (D4+F4) /2 и скопируем формулу в остальные ячейки столбца двойным щелчком по маркеру заполнения.