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

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

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

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

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

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

2. На лист будет вставлена пустая диаграмма. Выделите её и нажмите Выбрать данные на вкладке Конструктор, см. снимок экрана:

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

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

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

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

6. Как видно, до и после диаграммы остаются лишние пробелы, а пузырьки расположены слишком плотно. Чтобы настроить ось 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