Создание пузырьковой диаграммы в Excel

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

• Создание простой пузырьковой диаграммы в Excel
• Динамическое изменение цвета пузырьковой диаграммы на основе категорий в Excel
• Создание простой пузырьковой диаграммы в Excel с помощью мощной функции
• Скачать пример файла пузырьковой диаграммы
• Видео: Создание пузырьковой диаграммы в Excel

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

Чтобы создать пузырьковую диаграмму, выполните следующие шаги:

1. Щелкните пустую ячейку вашего листа, затем нажмите Вставка > Вставить точечную (X,Y) или пузырьковую диаграмму, и выберите тип пузырьковой диаграммы, который вам нравится, см. скриншот:

2. Затем в лист будет вставлена пустая диаграмма. Щелкните, чтобы выбрать пустую диаграмму, а затем нажмите Выбрать данные в разделе вкладки Конструктор, см. скриншот:

3. В открывшемся диалоговом окне Выбор источника данных нажмите кнопку Добавить, см. скриншот:

4. В следующем диалоговом окне Изменение ряда укажите следующие действия:

• В текстовом поле Имя ряда выберите ячейку с именем, которую вы хотите использовать;
• В текстовом поле Значения X выберите столбец данных, которые вы хотите разместить по оси X;
• В текстовом поле Значения Y выберите столбец данных, которые вы хотите разместить по оси Y;
• В текстовом поле Размер пузырька выберите столбец данных, которые вы хотите отобразить как размер пузырька.

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

6. Как видите, есть лишние пробелы перед и после диаграммы, и пузырьки расположены слишком тесно, поэтому вы можете настроить ось X. Щелкните правой кнопкой мыши по оси X и выберите Формат оси из контекстного меню, см. скриншот:

7. В открывшейся панели Формат оси введите подходящие минимальное и максимальное значения на основе ваших данных, чтобы диаграмма отображалась, как показано на скриншоте ниже:

8. Затем, если вы хотите, чтобы пузырьки отображались разными цветами, щелкните правой кнопкой мыши по пузырькам и выберите Формат ряда данных. В открывшейся панели Формат ряда данных, на вкладке Заливка и линия, отметьте опцию Разные цвета по точкам, и теперь пузырьки будут заполнены разными цветами, см. скриншот:

9. Далее добавьте метки данных для пузырьков. Щелкните, чтобы выбрать пузырьки, затем нажмите Элементы диаграммы > Метки данных > По центру, см. скриншот:

10. Затем щелкните правой кнопкой мыши по меткам данных и выберите Формат меток данных, см. скриншот:

11. В открывшейся панели Формат меток данных, на вкладке Параметры меток, отметьте опцию Значение из ячеек, и в открывшемся диалоговом окне Диапазон меток данных выберите ячейки с метками данных, см. скриншоты:

12. Затем нажмите ОК, оставаясь в панели Формат меток данных, снимите флажок Значение Y, см. скриншот:

13. Теперь пузырьковая диаграмма полностью создана. См. скриншот:

Динамическое изменение цвета пузырьковой диаграммы на основе категорий в Excel

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

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

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

Затем во второй строке введите X, Y, Z, Y, Z, Y, Z, Y, Z под именами категорий, как показано на скриншоте ниже:

2. В столбце заголовка X введите эту формулу: =B3 в ячейку F3, а затем перетащите маркер заполнения вниз по ячейкам, чтобы заполнить эту формулу. Этот шаг получит ряд данных X для диаграммы, см. скриншот:

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

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

5. Затем выберите эти два столбца с формулами и перетащите маркер заполнения вправо, чтобы применить эти формулы к другим ячейкам, см. скриншот:

6. После создания вспомогательных столбцов щелкните пустую ячейку, затем нажмите Вставка > Вставить точечную (X,Y) или пузырьковую диаграмму и выберите тип пузырьковой диаграммы, который вам нравится, чтобы вставить пустую диаграмму. Затем щелкните правой кнопкой мыши по пустой диаграмме и выберите Выбрать данные из контекстного меню, см. скриншот:

7. Затем в открывшемся диалоговом окне Выбор источника данных нажмите кнопку Добавить, см. скриншот:

8. В открывшемся диалоговом окне Изменение ряда укажите имя ряда, значение X, значение Y и размер пузырька из вспомогательного столбца данных, см. скриншот:

9. Затем нажмите ОК, чтобы вернуться в диалоговое окно Выбор источника данных. Теперь вы должны повторить вышеуказанные шаги 7–8, чтобы добавить другие ряды данных в диаграмму, как показано на скриншоте ниже:

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

11. Наконец, добавьте легенду для диаграммы. Выберите диаграмму, затем нажмите Элементы диаграммы > Легенда, и вы получите полную диаграмму, как показано на скриншоте ниже:

Создание простой пузырьковой диаграммы в Excel с помощью мощной функции

Скачать пример файла пузырьковой диаграммы

Видео: Создание пузырьковой диаграммы в Excel