Как создать график Ogive в Excel

В этом руководстве будет показано, как создать оживляющий график во всех версиях Excel: 2007, 2010, 2013, 2016 и 2022.

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

Другими словами, фраза «совокупная частота» означает, что вы складываете значения по мере продвижения.

Цель диаграммы - показать общее количество точек данных в наборе данных, которые меньше или равны определенному значению. Это помогает нам быстро определить, сколько наблюдений (частот) попадает в определенный диапазон во всем наборе данных.

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

В этом руководстве вы узнаете, как рассчитать совокупные частоты и создать этот график оживления в Excel с нуля:

Начиная

В целях иллюстрации предположим, что вы работаете статистиком в крупной корпорации с филиалами по всему миру.

Вам было поручено проанализировать годовую зарплату 100 глобальных менеджеров службы поддержки клиентов во всех филиалах - очевидно, размер вознаграждения различается от страны к стране.

После этого вы приступили к построению графика оживления, используя данные из следующей таблицы:

Несколько слов по каждому элементу:

• Годовой диапазон заработной платы: Эта категориальная переменная представляет пределы классов - или, говоря статистическим языком, корзины, - которые устанавливают границы между диапазонами заработной платы. Другими словами, так мы классифицируем наблюдения.
• Частота: Эта количественная переменная показывает, сколько раз данное наблюдение имеет место в наборе данных. В нашем случае он показывает, сколько работников компании получают годовую зарплату, которая попадает в определенный диапазон заработной платы.

А теперь приступим к работе.

Шаг №1: Создайте вспомогательную таблицу.

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

Столбцы в этой вспомогательной таблице выглядят следующим образом:

• Пределы класса: В этом столбце будут определены интервалы оживления на основе ваших фактических ограничений по классам.
• Накопленная частота: Этот столбец будет содержать все совокупные частоты, которые вы рассчитаете в дальнейшем.

Шаг № 2: Определите пределы класса.

Сразу же заполним столбец с надписью Пределы класса (столбец D).

По соглашению первая пустая ячейка в столбце (D2) должен соответствовать самому низкому пределу класса во всем наборе данных (в нашем случае это $0-вы не можете пойти ниже этого).

Двигаясь вниз по столбцу, последующие ячейки должны быть заполнены предельными значениями высшего класса (наибольшее число) каждого диапазона заработной платы, включая первое значение, используемое для получения нижнего предела класса (A2: A11). Например, возьмем диапазон заработной платы от 0 до 10 000 долларов (A2).

В этом случае предел для верхнего класса составляет 10000 долларов, а предел для нижнего класса равен 0 долларов (что мы помещаем в D2). Затем вы поместите 10000 долларов в следующую ячейку вниз (D3). Следующий диапазон заработной платы - от 10 000 до 20 000 долларов (A3). Вы возьмете верхний предел в 20 000 долларов и введете его в D4. Продолжайте процесс вниз по списку.

Это может звучать как ракетостроение, но на самом деле алгоритм до смешного прост. Вот как это выглядит на практике:

Шаг № 3: Вычислите совокупные частоты.

Установив интервалы, пора вычислить совокупные частоты для столбца E.

Опять же, по соглашению, поскольку вы всегда должны начинать счет с нуля, введите «0»В первую пустую ячейку столбца (E3).

Что касается остального, вот универсальная формула, которую нужно скопировать в ячейку E3 для определения остальных значений:

1	= СУММ ($ B $ 2: B2)

Эта формула блокирует ячейку Би 2 и вычисляет сумму значений в указанном диапазоне, экономя ваше время на сложение значений самостоятельно. Вот как это должно выглядеть:

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

Шаг №4: Постройте график оживления.

Наконец, теперь вы можете сложить все части головоломки, чтобы построить график оживления.

1. Выделите все значения в вспомогательной таблице (столбцы D и E).
2. Перейти к Вставлятьтаб.
3. Выберите «Вставить точечную (X, Y) или пузырьковую диаграмму" кнопка.
4. Выбирать "Скаттер с прямыми линиями и маркерами.”

Шаг № 5: Измените масштаб горизонтальной оси.

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

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

Щелкните правой кнопкой мыши горизонтальную ось (числа внизу) и выберите «Ось формата»В появившемся меню.

На всплывающей панели задач выполните следующие действия:

1. Перейдите к Параметры оси таб.
2. Установить Минимальные границы значение к числу, представляющему самый низкий предел класса в наборе данных (0).
3. Изменить Максимальные границы значение на число, равное наивысшему пределу класса в наборе данных (100,000).
4. Установить Крупный значение единицы измерения ширины класса на основе ваших фактических данных, расстояние между верхним и нижним пределами любого класса в наборе данных (10,000).

Шаг № 6: Измените масштаб вертикальной оси.

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

Шаг № 7: Добавьте метки данных.

По мере того, как мы приступаем к полировке графика, следующим логическим шагом будет добавление меток данных.

Для этого просто щелкните правой кнопкой мыши линию графика и выберите «Добавить метки данных.”

Шаг № 8: Переставьте метки данных.

Важно переместить метки вверх, чтобы они не перекрывали линию диаграммы. Щелкните правой кнопкой мыши любую метку данных и выберите «Форматирование меток данных.”

Оттуда можно всего за пару кликов изменить положение ярлыков:

1. Щелкните значок «Параметры метки" значок.
2. Под Положение ярлыка, выбирать "Выше.”

Также сделайте надписи жирными (Вкладка "Главная"> Шрифт) так они выделяются.

Шаг № 9: Добавьте заголовки осей.

А вот и последний шаг, прежде чем он закончится: добавление заголовков осей.

1. Выберите график диаграммы.
2. Перейти к Дизайн таб.
3. Щелкните значок «Добавить элемент диаграммы" кнопка.
4. Выбирать "Названия осей.”
5. Выберите оба "Первичный горизонтальный" а также "Первичная вертикаль»В появившемся меню.

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

Поздравляем с созданием собственного графа ogive!

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