Создание Матричная пузырьковая диаграмма в Excel

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

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

Создание Матричная пузырьковая диаграмма в Excel

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

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

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

Примечание: В исходной таблице содержится 6 наборов значений, по 5 строк в каждом. Во вспомогательном диапазоне 1 создайте столбец с последовательными числами от 1 до 6, а во вспомогательном диапазоне 2 — 5 столбцов с последовательными числами в обратном порядке: от 6 до 1. Настройте вспомогательные диапазоны в соответствии со своими данными.

2. Выделите все строки исходной таблицы (за исключением заголовков столбцов и строк; в данном примере выделен диапазон B2:F7).

3. Щёлкните Вставка > Точечная (X, Y) или пузырьковая диаграмма > Пузырьковая, чтобы создать пузырьковую диаграмму.

4. Щёлкните правой кнопкой мыши по диаграмме «Пузырьковая» и выберите в контекстном меню пункт Выбрать данные.

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

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

7. Теперь окно Источник данных: Выбрать данные снова открыто; вы можете увидеть, что Ряд1 добавлен в поле Диапазон имени ряда. Повторите шаг 6, чтобы добавить остальные ряды, и нажмите кнопку ОК, чтобы сохранить изменения.

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

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

Теперь нужно заменить текущие оси X и Y на диаграмме на фактические наборы значений и имя ряда.

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

9. В области Формат оси разверните раздел Подписи на вкладке Параметры оси и выберите значение Нет в поле Расположение подписей раскрывающегося списка.

10. Повторите шаги 8 и 9, чтобы также скрыть ось Y и диапазон меток оси.

Теперь диаграмма выглядит так, как показано на снимке экрана ниже: оси X и Y на ней скрыты.

11. Чтобы добавить на диаграмму наборы значений и имя ряда, необходимо создать ещё два вспомогательных диапазона. Эти диапазоны выглядят следующим образом.

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

13. В диалоговом окне Источник данных Выбрать данные нажмите кнопку Добавить.

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

15. После возврата в диалоговое окно Источник данных Выбрать данные снова нажмите кнопку Добавить.

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

17. После возврата в диалоговое окно Источник данных: Выбрать данные вы увидите, что Ряд7 и Ряд8 добавлены в поле Диапазон имени ряда. Нажмите кнопку ОК, чтобы сохранить изменения.

18. Выделите Ряд7 (расположенный под всеми пузырьками) на диаграмме, нажмите кнопку Элементы диаграммы и установите флажок в поле Метки данных. См. снимок экрана:

19. Выделите только что добавленную метку данных, чтобы открыть область Формат метки данных. В этой области выполните следующие настройки.

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

20. Выделите Ряд8 (расположенный слева от пузырьков) на диаграмме, нажмите кнопку Элементы диаграммы и установите флажок в поле Метки данных.

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

22. Удалите легенду, выделив её и нажав клавишу Delete.

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

23. Теперь имя ряда и наборы значений можно рассматривать как оси X и Y диаграммы. Поскольку они находятся внутри линий сетки, их необходимо переместить за её пределы, как описано ниже.

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

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

26. При необходимости отформатируйте пузырьки: измените масштаб «Размер пузырька», добавьте к ним метки данных, настройте цвета и так далее. Выполните следующие действия.

Изменение масштаба Размер пузырька

Добавление Метки данных к пузырькам

Изменение цвета пузырьков

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

Удаление или редактирование Заголовок диаграммы

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

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

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

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