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

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

Создание точечной диаграммы для одного ряда данных

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

Горизонтальная точечная диаграмма для ряда данных

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

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

=(ROWS($A$2:$A$12)-ROW()+ROW($A$2:$A$12)-0,5)/ROWS($A$2:$A$12)

2. Выделите исходный диапазон данных (без столбца вспомогательных значений), щёлкните Вставить > Вставить столбец или гистограмму > Сгруппированные столбцы. См. скриншот:

3. Затем скопируйте и вставьте значения ряда данных и вспомогательного столбца (B1:C12) на диаграмму, как показано ниже.

4. В появившемся диалоговом окне Вставить специально выберите опции Новый ряд и Столбцы соответственно в разделах Добавить ячейки как и Значения (Y) в, установите флажки Имя ряда в первой строке и Категории (X метки) в первом столбце, затем нажмите кнопку ОК.

Диаграмма отображается следующим образом.

5. Щёлкните правой кнопкой мыши по синему ряду и выберите в контекстном меню Формат ряда данных.

6. В области Формат ряда данных выберите опцию Вторичная ось в разделе Параметры ряда на вкладке.

7. Выделите диаграмму, перейдите на вкладку Конструктор(в группе вкладок)Инструменты диаграммы) и выберите Элементы диаграммы > Оси > Вторичная вертикальная. См. скриншот:

После этого шага диаграмма будет выглядеть следующим образом.

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

9. В диалоговом окне Изменение типа диаграммы выберите тип Точечная для ряда вспомогательного столбца и нажмите кнопку ОК.

10. Щёлкните правой кнопкой мыши по оси Y на диаграмме и выберите в контекстном меню Формат оси.

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

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

12. Щёлкните правой кнопкой мыши по оси X и выберите в контекстном меню пункт Формат оси.

13. В области Формат оси выберите опцию Максимальное значение оси, как показано на скриншоте ниже.

14. Щёлкните, чтобы выбрать вторичную ось X (в верхней части диаграммы), и в области Формат осивыберите опцию Автоматически.

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

15. Скройте вторичную ось Y на диаграмме: выделите её и выберите опцию Нет в поле Положение подписей раскрывающегося списка в разделе Подписи на вкладке Параметры оси.

Совет: Выполните ту же операцию, чтобы скрыть вторичную ось X на диаграмме.

16. Теперь необходимо скрыть синие столбцы и оставить на диаграмме только точки.

Щёлкните любой из синих столбцов, чтобы выделить их все, перейдите на вкладку Заливка и линии в области Формат ряда данных и выберите опцию Без заливки в разделе Заливка.

17. Щёлкните правой кнопкой мыши по оси Y и выберите в контекстном меню Формат оси. В области Формат оси на вкладке Параметры оси установите флажок Обратный порядок категорий.

Горизонтальная точечная диаграмма готова!

Вертикальная точечная диаграмма для ряда данных

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

1. Выделите исходную таблицу данных (диапазон A1:B2).

2. Затем вставьте диаграмму «Линия с маркерами», щёлкнув Вставить > Вставить линейную или площадную диаграмму > Линия с маркерами. См. скриншот:

3. Щёлкните правой кнопкой мыши по линии на диаграмме, выберите в раскрывающемся списке Контур и затем — Без контура в выпадающем меню.

Вертикальная точечная диаграмма готова!

Создание точечной диаграммы для нескольких рядов данных

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

Горизонтальная точечная диаграмма для нескольких рядов

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

1. Сначала создайте три вспомогательных столбца с приведёнными ниже формулами.

Примечание: В этих формулах число 3 обозначает количество рядов, на основе которых строится вертикальная точечная диаграмма. Замените его на значение, соответствующее вашим данным.

2. Теперь нужно создать сгруппированную гистограмму. Выделите первые две строки исходных данных (A1:C2) и последовательно выберите: Вставить > Вставить столбец или гистограмму > Сгруппированные столбцы.

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

4. В диалоговом окне источника выберите Ряд1 в поле Диапазон имени ряда, затем нажмите кнопку Изменить.

5. Затем появится диалоговое окно «Изменение ряда». В поле Значения ряда удалите исходное значение и введите 0,0,0. Наконец, нажмите кнопку ОК.

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

7. Теперь все столбцы скрыты на диаграмме. Выделите ось Y, щёлкните по ней правой кнопкой мыши и выберите в контекстном меню Формат оси.

8. В области «Формат оси» выберите параметр «В максимальной категории» и установите флажок «Категории в обратном порядке» в разделе «Параметры оси» на вкладке.

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

10. В диалоговом окне источника нажмите кнопку Добавить в поле Диапазон имени ряда.

11. В появившемся диалоговом окне «Изменение ряда» выполните следующие настройки.

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

Теперь диаграмма отображается следующим образом.

13. Щёлкните правой кнопкой мыши по любому из рядов на диаграмме и выберите Изменить тип ряда — Тип диаграммы в контекстном меню.

14. В диалоговом окне «Изменение типа диаграммы» выберите Точечная в раскрывающемся списке Тип диаграммы для всех рядов, кроме Ряда 1, и нажмите ОК.

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

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

16. В диалоговом окне источника выберите первый ряд (в данном случае — KTE) под Рядом 1 и нажмите кнопку Изменить.

17. В диалоговом окне «Изменение ряда» необходимо:

18. Теперь повторите описанный выше шаг 16 и 17, чтобы изменить остальные ряды (в данном случае остались только KTO и KTW). Ряды изменены, как показано на приведённых ниже снимках экрана.

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

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

20. Щёлкните правой кнопкой мыши по вспомогательной оси и выберите в контекстном меню Формат оси.

21. В области «Формат оси» необходимо:

22. Выделите диаграмму, нажмите кнопку Элементы диаграммы, перейдите к параметрам Линии сетки и установите флажок только напротив Основные вертикальные линии сетки (основная ось). См. снимок экрана:

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

Вертикальная точечная диаграмма для нескольких рядов

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

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

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

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

4. В диалоговом окне источникавыберите Ряд1в поле Диапазон имени ряда, а затем нажмите кнопку Изменить.

5. Затем появится диалоговое окно «Изменение ряда». В поле Значения ряда удалите исходное значение и введите 0,0,0, после чего нажмите кнопку ОК.

6. Когда вы вернётесь в диалоговое окно источника, нажмите кнопку Добавить.

7. В диалоговом окне «Изменение ряда» выберите первую ячейку (A1) для поля «Имя серии», в поле «Значения ряда» введите 1, а затем нажмите кнопку «ОК».

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

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

10. В диалоговом окне «Изменение типа диаграммы» выберите Точечная в списке Тип диаграммы для ряда KTE и нажмите ОК.

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

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

12. В диалоговом окне источника выберите ряд, созданный на шаге 7 (в данном случае — ряд «KTE»), и нажмите кнопку Изменить.

13. В диалоговом окне «Изменение ряда» выполните следующие настройки.

14. Когда вы вернетесь в диалоговое окно Выбрать данные Source, нажмите кнопку Добавить.

15. В диалоговом окне Изменение ряда выполните следующие настройки.

16. Повторите шаги 14 и 15, чтобы добавить третий ряд (KTW) в диалоговое окно Выбрать источник данных.

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

Теперь график с вертикальными точками для нескольких рядов данных готов.

Легко создайте горизонтальный график с точками для одного ряда в Excel

Скачать образец файла

Видео: Создание графика с точками в Excel