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

Сделаем заготовку таблицы для границ интервалов. Первый интервал от 190 до 195, второй — от 195 до 200.

Заготовка границ

Выделяем две строки таблицы и перетаскиваем маркер заполнения, то есть «растягиваем» таблицу вниз. Удаляем лишние ячейки.

Границы интервалов

Снова вызываем гистограмму в надстройке

Data — Analysis — Data Analysis — Histogram

Данные — Анализ — Анализ данных — Гистограмма

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

Histogram

Гистограмма

Выбираем диапазон ячеек, где находятся наши исходные данные:

Input — Input Range

Входные данные — Входной интервал

Нажимаем кнопку выбора диапазона со стрелкой, нарпавленной вверх — см. рисунок.

Выбор диапазона

Появляется окно выбора диапазона.

Выбор диапазона

Переходим на нужный лист нашей рабочей книги и выделяем столбец округлённых данных — вместе с заголовком. В этом случае имя переменной — заголовок столбца — автоматически появится в таблицах и на графиках. Поэтому щёлкаем по первой ячейке столбца Bи нажимаем комбинацию клавиш Ctrl + Shift + ↓, а затем клавишу Enterили кнопку со стрелкой, направленной вниз. Кроме диапазона адресов указывается название листа, на котором находятся наши данные — см. рисунок.

Здесь 04 — указание на лист с названием 04.

$B$1:$B$10001 — диапазон ячеек на указанном листе

Выбранный диапазон

Чтобы использовать заголовок столбца в качестве названия переменной, поставим галочку в разделе

Labels

Метки

Следующий шаг — выбираем диапазон ячеек, в котором мы сформировали ВЕРХНИЕ (ПРАВЫЕ) ГРАНИЦЫ интервалов группировки. Не перепутайте, именно верхние границы!

Input — Bin Range

Входные данные — Интервал карманов

Щёлкаем кнопку выбора диапазона и выделяем диапазон верхних границ — тоже вместе с заголовком.

Диапазон верхних границ

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

Output options — Output Range

Параметры вывода — Выходной интервал

Настройки функции Гистограмма

Отмечаем ещё две позиции

Накопленные (кумулятивные) относительные частоты:

Cumulative Percentage

Интегральный процент

Автоматическое построение графика по результатам группировки:

Chart Output

Вывод графика

Настройка закончена. Нажимаем OK.

Рассматриваем результаты группировки данных.

Результаты группировки

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

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

Аналогично настроим ширину остальных «пострадавших» столбцов. Если слова и числа в столбце короткие, то ширина столбца может даже уменьшиться.

Столбцы разной ширины

Рассмотрим полученную таблицу. В ней всего три столбца.

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

Второй столбец — абсолютные частоты, то есть число попаданий в интервал:

Frequency

Частота

Третий столбец — накопленные (кумулятивные) относительные чатоты в процентах:

Cumulative %

Интегральный %

Теперь обратим внимание на график. Здесь есть столбики, отдалённо напоминающие стандартную гистограмму. Ширину столбиков придётся дополнительно настроить.

Есть график накопленных частот, который называется КУМУЛЯТА. Про кумуляту мы подробно поговорим чуть позже, когда будем строить её вручную. Пока примем к сведению, что кумулята — это график накопленных относительных частот в процентах. Кумулята — это оценка формы функции распределения по результатам группировки данных.