Создайте диаграмму гантелей в Excel

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

Легко создайте диаграмму гантелей с помощью замечательный инструмент.
Загрузите файл-образец
Видео: создание диаграммы с гантелями в Excel


Создание диаграммы гантелей в Excel

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

1. Во-первых, вам нужно создать три вспомогательных столбца.

Пожалуйста, создайте первый вспомогательный столбец следующим образом.
Выберите пустую ячейку, которая находится рядом с исходную таблицу данных, в данном случае я выбираю D2, ввожу в нее приведенную ниже формулу и нажимаю клавишу Enter .
= 1 /4/2
Выберите ячейку D3, введите в нее приведенную ниже формулу и нажмите клавишу Enter . Выберите ячейку D3, а затем перетащите ее Fill Handle полностью вниз, чтобы получить другие результаты.
= D2 + 1/4
Советы. В формулах число 4 представляет количество строк (исключая строку заголовка) в исходной таблице данных. Если в ваших данных 8 строк (исключая строку заголовка), измените число с 4 на 8.

Создайте второй вспомогательный столбец следующим образом.
Выберите пустую ячейку (здесь я выбираю E2), введите приведенную ниже формулу и нажмите клавишу Enter . Выберите ячейку результата, перетащите ее Fill Handle полностью вниз, чтобы получить другие результаты.
= IF (B2> C2, B2- C2,)

Создайте третий вспомогательный столбец следующим образом.
Выберите пустую ячейку, например F2, введите в нее приведенную ниже формулу и нажмите клавишу Enter . . Выберите ячейку результата, перетащите ее Fill Handle полностью вниз, чтобы получить другие результаты.
  = IF (B2   

2. Выберите диапазон столбцов оси и первую серию данных (в данном случае я выбираю диапазон A2: B5), а затем нажмите Вставить > Вставить столбец или гистограмму > Кластерная панель .

Затем на текущий лист вставляется столбчатая диаграмма с кластерами.

3. Щелкните правой кнопкой мыши любую полосу серий, а затем нажмите Выбрать данные из контекстного меню.

4. В диалоговом окне Выбор источника данных нажмите кнопку Добавить в разделе Записи легенды (серии) и затем нажмите кнопку OK прямо в следующем всплывающем окне Редактировать серию .

Примечание: На этом этапе будет создана Series2 .

5. Повторите шаг 4, чтобы создать серию 3.

6. Когда он вернется в диалоговое окно Выбрать источник данных , вы увидите, что Series2 и Series3 перечислены в Записи легенды (серии) , нажмите OK , чтобы сохранить изменения.

7. Щелкните правой кнопкой мыши любую серию на диаграмме, а затем в контекстном меню выберите Изменить тип диаграммы серии .

8. В диалоговом окне Изменить тип диаграммы измените тип диаграммы Series2 и Series3 на Scatter. , а затем нажмите OK , чтобы сохранить изменения.

9. Щелкните правой кнопкой мыши любую серию, выберите Выбрать данные из контекстного меню.

10. В диалоговом окне Выбор источника данных щелкните, чтобы выбрать Series2 в поле Legend Entries , а затем щелкните Изменить .

11. В открывающемся поле Редактировать серию необходимо настроить следующее.

11.1) В Имя серии выберите заголовок значений первой серии (в данном случае я выбираю B1);
11.2) В поле Значения серии X выберите значения первой серии (здесь я выбираю B2: B5);
11.3) В поле Значения серии Y выберите данные первого вспомогательного столбца (D2: D5) ;
11.4) Нажмите

12. Когда он вернется в диалоговое окно Выбор источника данных , выберите Series3 , а затем нажмите кнопку Изменить .

13. В открывающемся окне Edit Series вам необходимо настроить следующее.

13. 1) В поле Название серии выберите заголовок значений второй серии (в данном случае я выбираю C1);
13.2) В Окно значений серии X , выберите значения второй серии (здесь я выбираю C2: C5);
13.3) В значениях серии Y выберите данные первого вспомогательного столбца (D2: D5);
13.4) Нажмите

14. Нажмите OK в диалоговом окне Выбор источника данных , чтобы сохранить изменения.

Теперь диаграмма отображается, как показано на скриншоте ниже. .

15. Щелкните правой кнопкой мыши вертикальную ось на диаграмме, а затем выберите Ось формата в контекстном меню.

16. На открывшейся панели Ось формата установите флажок Категории в обратном порядке на вкладке Параметры оси .

17. Щелкните дополнительную ось на диаграмме, а затем установите флажок Категории в обратном порядке на панели Ось формата .

18. Теперь вам нужно скрыть синие полосы на графике. Щелкните любую из полос, перейдите на панель Форматировать ряд данных , щелкните значок Заливка и линия , а затем выберите Нет. fill и Без линии отдельно в разделах Fill и Line .

19. Выберите серию расходов (оранжевые точки) на диаграмме, нажмите Дизайн > Добавить элемент диаграммы > Полосы ошибок > Стандартная ошибка .

Теперь диаграмма отображается, как показано на скриншоте ниже.

20. Выберите вертикальные полосы погрешностей, нажмите клавишу Delete , чтобы удалить их с диаграммы.

21. Выберите горизонтальные полосы ошибок, перейдите на панель Форматировать полосы ошибок и затем настройте следующим образом.

21.1) Выберите Минус в разделе Direction ;
21.2) Выберите No Cap в End Раздел «Стиль «;
21.3) Выберите Custom в разделе Количество ошибок ;
21.4) Нажмите кнопку Указать значение . См. Снимок экрана:

22. Во всплывающем диалоговом окне Настраиваемые полосы ошибок выберите данные второго вспомогательного столбца в поле Отрицательное значение ошибки , а затем нажмите ОК. .

23. Выберите ряд доходов (синие точки) на диаграмме, нажмите Дизайн > Добавить элемент диаграммы > Полосы ошибок > Стандартная ошибка .

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

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

24.1) Выберите Minus в разделе Direction ;
24.2) Выберите No Cap в разделе End Style ;
24.3) Выберите Custom в Сумма ошибки section;
24.4) Нажмите кнопку Указать значение . См. Снимок экрана:

25. В открывшемся диалоговом окне Настраиваемые полосы ошибок выберите данные третьего вспомогательного столбца в поле Отрицательное значение ошибки , а затем нажмите ОК.

Теперь диаграмма гантелей отображается как ниже.

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

Удалить вторичную ось

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

Добавьте легенду в диаграмму. диаграмма

Выберите диаграмму, нажмите Дизайн > Добавить элемент диаграммы > Легенда > Внизу (или другое положение, если вам нужно).

Затем на диаграмме отобразится легенда, как показано на скриншоте ниже. Вы можете удалить ненужное название серии из легенды, выделив его и нажав клавишу Delete . (здесь я удалю Series1 из поля легенды).

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

Как видите, горизонтальная ось расположена в верхней части ряда. См. Снимок экрана:

Если вы хотите найти его на внизу ряда, щелкните его правой кнопкой мыши и выберите Ось формата в контекстном меню.. На панели Ось формата разверните раздел Ярлыки , а затем выберите High в Ярлыке. Раскрывающийся список .

Теперь горизонтальная ось перемещена в конец ряда, как показано на скриншоте ниже.

Добавить метки данных в серию

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

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

3. Выполните настройку следующим образом.

3.1) На панели Форматировать метки данных проверьте Значение из ячеек в разделе Параметры метки ;
3.2) В открывшемся диалоговом окне Диапазон меток данных выберите фактические значения выбранной серии, а затем нажмите
3.3) Снимите флажок Y Value ;
3.4) Снимите флажок Показывать линии выноски . См. Снимок экрана:

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

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

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

Теперь диаграмма с гантелями готова.


Простое создание диаграммы с гантелями в Excel

Утилита Dumbbell Chart в Kutools for Excel может помочь вам быстро создать диаграмму с гантелями в Excel с помощью нескольких щелчков мышью, как показано ниже.
Загрузите и попробуйте прямо сейчас! 30-дневная бесплатная пробная версия


Загрузить образец файла


Видео: создание диаграммы гантелей в Excel


-> Лучшие инструменты для повышения производительности в офисе


Оцените статью
Frestage.ru
Добавить комментарий