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

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

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

Ещё

не включаем, чтобы использовать числовой масштаб по горизонтальной оси.

Выбор относительных частот

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

Столбиковая диаграмма

Для указания масштаба по горизонтальной оси есть два пути.

Первый вариант: метки на столбиковой диаграмме соответствуют серединам интервалов (среднему значению между нижней и верхней границами интервала). Примерно так происходит при построении гистограммы с помощью надстройки (правда, там выводится верхняя граница). На таком графике можно делать расчёты, поскольку есть масштаб.

Второй вариант: указывать интервал группировки (нижнюю и верхнюю границы). Так делается при построении гистограммы с помощью готового статистического графика. На такой график приятно посмотреть, но использовать такой масштаб не очень удобно — придётся ориентироваться на границы интервалов вместо масштаба.