Как создать полярный график в Excel

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

Полярный график используется для определения точки в пространстве в так называемой полярной системе координат, где вместо использования стандартных координат x и y каждая точка на полярной плоскости выражается с использованием этих двух значений:

• Радиус (р) - Расстояние от центра участка
• Тета (θ) - Угол от опорного угла

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

В качестве примера на диаграмме ниже сравниваются оценки удовлетворенности клиентов (CSAT) - метрика, которая иллюстрирует удовлетворенность потребителя брендом или продуктом, двух организаций на протяжении 2022 года: Simpson Ltd и Griffin Ltd.

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

Однако вот в чем проблема:

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

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

Начиная

Поскольку оценки CSAT обычно выражаются в виде процентной шкалы, рассмотрите следующую таблицу:

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

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

Настройте отдельную фиктивную таблицу следующим образом:

Обратите внимание, как вспомогательная таблица начинается с фиктивной строки (E2: H2) - определяет опорный угол. Давайте подробнее поговорим о каждом элементе таблицы:

• Месяц - Этот столбец содержит качественные категории, полученные из ваших исходных данных. Введите «Старт» в первую ячейку (E2) и скопируйте категории (в нашем случае месяцы) прямо под ними (E3: E14).
• Угол (Тета) - Этот столбец содержит тета-значения, отвечающие за рисование спиц, в которые будут помещены фактические значения. Вы всегда должны вводить «0» в первую ячейку (F2) этого столбца.
• CSAT Simpson LTD (Радиус) и CSAT Griffin LTD (Радиус) - Эти столбцы содержат значения радиуса, иллюстрирующие производительность каждой компании в течение года.

Шаг № 2: Вычислить значения угла (тета).

Если у вас уже есть значения r и theta, пропустите эту часть и прокрутите вниз до Шаг # 4.

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

Затем складывайте это число по мере продвижения от нуля до 360. И здесь в игру вступает функция СЧЁТ. По сути, он подсчитывает количество непустых ячеек в указанном диапазоне.

Скопируйте эту формулу в ячейку F3:

1	= 360 / COUNTA (3 австралийских доллара: 14 австралийских долларов)

С этой формулой в ячейке F3, используйте эту другую формулу в ячейке F4 чтобы добавить заданное значение угла к сумме всех значений тета, которые идут перед ним в столбце:

1	= F3 + 360 / COUNTA (3 австралийских доллара: 14 австралийских долларов)

Важно заблокировать диапазон ячеек (A3: A14), чтобы легко скопировать формулу в оставшиеся ячейки.

Теперь выполните формулу для остальных ячеек в столбце (F5: F14), выбрав F4 и перетащите маркер заполнения вниз.

Шаг № 3: Вычислите значения радиуса.

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

Поскольку оценки CSAT также измеряются по процентной шкале, просто разделите каждую таблицу оценок CSAT на 10.

Вот как это сделать быстро и легко. Чтобы найти значения радиуса для первой компании (Simpson Ltd), введите эту крошечную формулу в ячейку G3 и скопируем в остальные ячейки (G4: G14):

1

= B3 / 10

Теперь точно так же вычислим радиусы для второй компании (Griffin Ltd):

1

= C3 / 10

В этот момент вы можете подумать: «Что, если мой тип данных отличается? Как вы корректируете, сравнивая, например, доход, полученный компаниями, с оценками CSAT? »

Проще говоря, вы должны проанализировать свои фактические данные, определить эквивалент одной радиальной точки (скажем, 50 000 долларов США) и разделить все значения в вашем наборе данных на это число. Предположим, что в мае какая-то компания заработала 250 000 долларов. Чтобы найти радиус, разделите 250 000 долларов на 50 000. Так просто.

Шаг №4: Скопируйте последние значения радиуса во вспомогательную строку.

Заполните таблицу, скопировав значения r в самом низу (G14: H14) каждого столбца в соответствующие фиктивные ячейки (G2: H2).

Шаг № 5: Рассчитайте значения по осям x и y для каждой компании.

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

Начнем сначала со значений оси X. В ячейке рядом со вспомогательной таблицей (I2) введите следующую формулу:

1	= G2 * SIN (F2 / 180 * PI ())

Скопируйте эту формулу в остальные ячейки под ней (I3: I14).

Точно так же вставьте эту формулу в ячейку J2 чтобы найти значения оси Y и выполнить его для остальных ячеек (J3: J14) также:

1	= G2 * COS (F2 / 180 * PI ())

Важная заметка: Помните, что ячейка строки заголовка (J1) столбца со значениями оси Y (столбец J) будет действовать как имя серии, то есть значение в этой ячейке перейдет в легенду диаграммы.

Повторите тот же процесс, чтобы вычислить значения X и Y для второй компании, корректируя формулу для использования данных в столбце Griffin Ltd:

1	= H2 * SIN (F2 / 180 * PI ())

1	= H2 * COS (F2 / 180 * PI ())

Шаг № 6: Настройте вторую вспомогательную таблицу для сетки полярных графиков.

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

Взгляните на это:

По сути, таблица состоит из трех элементов:

• Качественная шкала (желтая область или N2: N11) - Это отражает интервалы значений на основе ваших фактических данных. Заполните ячейки процентами, как показано на скриншоте. В качестве примера альтернативных данных, если бы мы проанализировали доход, упомянутый ранее, этот столбец увеличился бы с 50 000 до 500 000 долларов США.
• Строка заголовка (красная область или O1: Z1) - Он содержит все имена категорий, полученные из исходной таблицы данных, только что размещенные вертикально.
• Значения сетки (зеленая зона или O2: Z11) - Эти значения разделят будущие кольца данных на равные части, очерчивая сетку графика. Просто выберите число из ничего и скопируйте его во все ячейки в пределах диапазона.

Шаг № 7: Создайте набор кольцевых диаграмм.

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

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

1. Выделите все значения сетки из второй вспомогательной таблицы (O2: Z11).
2. Перейти к Вставлять таб.
3. Щелкните значок «Вставить круговую или кольцевую диаграмму" кнопка.
4. Выбирать "Пончик.”

В результате Excel должен дать вам набор из 10 колец.

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

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

Затем щелкните правой кнопкой мыши график и выберите «Выберите данные.”

После этого в Выберите источник данных диалоговом окне щелкните значок «Добавлять" кнопка.

в Редактировать серию выберите все значения сетки в первой строке (O2: V2) и нажмите «Ok.”

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

Шаг № 8: Уменьшите размер отверстия для пончика.

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

1. Щелкните правой кнопкой мыши любое кольцо данных.
2. Выбирать "Форматировать ряд данных.”

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

1. Переключитесь на Варианты серий таб.
2. Установить Размер отверстия для пончика к "10%.”

Шаг № 9: Настройте сетку диаграммы.

На той же панели задач преобразуйте кольца в сетку, выполнив следующие простые шаги:

1. Перейти к Заливка и линия таб.
2. Под "Наполнять," выбирать "Без заливки.”
3. Под "Граница," Выбрать "Сплошная линия.”
4. Щелкните значок «Цвет контура», Чтобы открыть цветовую палитру и выбрать светло-серый.
5. Установить Ширина к "5 пт.”

Промыть и повторить для остальных колец.

Шаг № 10: Добавьте данные диаграммы.

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

1. Выделите все значения по осям x и y, показывающие оценки CSAT первой компании (Simpson Ltd), а также ячейки строки заголовка (I1: J14) и скопируйте данные (щелкните правой кнопкой мыши и выберите Копировать).
2. Выберите область диаграммы.
3. Перейдите к Дом таб.
4. Щелкните значок «Вставить" кнопка.
5. Выбирать "Специальная вставка.”

В крошечном Специальная вставка В появившемся диалоговом окне выполните следующие действия:

1. Под "Добавить ячейки как," выбирать "Новая серия.”
2. Под "Значения (Y) в," Выбрать "Колонны.”
3. Проверить "Имена серий в первой строке" а также "Категории (метки X) в первом столбце»Коробки.
4. Нажмите «OK.”

Повторите процесс, чтобы добавить данные диаграммы, связанные со второй компанией (Griffin Ltd).

Шаг № 11: Измените тип диаграммы для вставленного ряда данных.

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

1. Щелкните правой кнопкой мыши любую из серий, представляющих фактические значения (либо Серия «Симпсон Лтд» или Серия «Гриффин Лтд.»).
2. Выбирать "Изменить тип диаграммы серии.”

Оказавшись там, измените тип диаграммы для Серия «Симпсон Лтд.» а также Серия «Гриффин Лтд.» к «Разброс с гладкими линиями и маркерами».

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

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

1. Щелкните правой кнопкой мыши вертикальную ось.
2. Выбирать "Ось формата.”

Когда появится панель задач, определите новые диапазоны шкалы оси:

1. Перейти к Параметры оси таб.
2. Установить Минимальные границы ценность для “-10.”
3. Изменить Максимальные границы ценность для “10.”

После этого перейдите к горизонтальной оси и проделайте то же самое.

Шаг № 13: Удалите линии сетки, оси и нерелевантные элементы легенды.

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

Для этого дважды щелкните каждый элемент, затем снова щелкните его правой кнопкой мыши и выберите «Удалить.”

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

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

Щелкните правой кнопкой мыши на внешнее кольцо (Серия «10») и выберите «Добавьте метки данных.”

Шаг № 15: Настройте метки данных.

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

Щелкните правой кнопкой мыши любую метку данных и выберите «Отформатируйте метки данных.”

Когда откроется панель задач, замените значения, выполнив следующие действия:

1. Перейти к Параметры метки таб.
2. Проверить "Значение из ячеек" коробка.
3. Выделите значения категорий из исходной таблицы данных (A3: A14).
4. Нажмите «OK.”
5. Снимите флажок "Ценить" коробка.
6. Снимите флажок "Показать линии выноски" коробка.

Шаг №16: переставьте этикетки.

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

Наконец, измените название диаграммы, и все готово!