Пузырьковая диаграмма (Bubble Chart)
Большинство из тех, кто хоть раз строил графики в Microsoft Excel или PowerPoint, замечали необычный и забавный тип диаграмм — пузырьковые (bubble chart). Многие видели их в чужих файлах или презентациях. Однако, в 99 случаев из 100, при попытке самостоятельно построить такую диаграмму впервые, пользователи сталкиваются с рядом неочевидных сложностей. Обычно Excel или отказывается ее создавать вообще, или создает, но совершенно непонятного вида, без подписей и наглядности.
Давайте разберемся в этой теме.
Что такое пузырьковая диаграмма
Пузырьковая диаграмма — это специфический тип диаграмм, способных отображать трехмерные данные в двумерном пространстве. Рассмотрим для примера вот такую диаграмму, отображающую статистику по странам с известного сайта-конструктора диаграмм http://www.gapminder.org/ :
По горизонтальной оси Х откладывается средний годовой доход на душу населения в USD. По вертикальной оси Y откладывается средняя продолжительность жизни в годах. Размер же (диаметр или площадь) каждого пузырька пропорционален населению каждой страны. Таким образом, на одной плоской диаграмме удается отобразить трехмерную информацию.
Дополнительную информационную нагрузку несет еще и цвет, отображающий региональную принадлежность каждой страны к определенному континенту.
Как построить пузырьковую диаграмму в Excel
Самый важный момент при построении пузырьковой диаграммы — правильно подготовленная таблица с исходными данными. А именно — таблица должна состоять строго из трех столбцов в следующем порядке (слева-направо):
- Параметр для откладывания по оси Х
- Параметр для откладывания по оси Y
- Параметр, определяющий размер пузырька
Возьмем для примера вот такую таблицу с данными по игровым приставкам:
Чтобы построить по ней пузырьковую диаграмму, нужно выделить диапазон C3:E8 (строго — только оранжевые и серые ячейки без столбца с названиями) и затем:
- В Excel 2007/2010 — перейти на вкладку Вставка— группа Диаграммы—Другие—Пузырьковая (Insert — Chart — Bubble)
- В Excel 2003 и старше — выбрать в меню Вставка — Диаграмма — Пузырьковая (Insert — Chart — Bubble)
Получившаяся в итоге диаграмма будет отображать быстродействие приставок по оси X, число программ для них по оси Y и долю рынка, занимаемого каждой приставкой — как размер пузырька:
После создания пузырьковой диаграммы имеет смысл настроить подписи к осям — без названий осей трудно понять, что по какой из них откладывается. В Excel 2007/2010 это можно сделать на вкладке Макет (Layout) , а в старых версиях Excel — щелкнув по диаграмме правой кнопкой мыши и выбрав команду Параметры диаграммы (Chart options) — вкладка Заголовки (Titles) .
К сожалению, Excel не позволяет автоматически привязывать цвет пузырьков к исходным данным (как в примере выше со странами), но для наглядности можно быстро отформатировать все пузырьки в разные цвета. Для этого щелкните правой кнопкой мыши на любом пузырьке, выберите команду Формат ряда данных (Format series) из контекстного меню и включите опцию Разноцветные точки (Vary colors) .
Проблема с подписями
Общей трудностью, с которой сталкиваются абсолютно все пользователи при построении пузырьковых (и точечных, кстати, тоже) диаграмм, являются подписи к пузырькам. Стандартными средствами Excel можно вывести в качестве подписей только значения X, Y, размер пузырька или называние ряда (общее для всех). Если вспомнить что при построении пузырьковой диаграммы вы не выделяли столбец с подписями, а только три столбца с данными X, Y и размер пузырьков, то все оказывается в общем-то логично: то, что не выделено — в диаграмму само никак попасть не может.
Решить проблему подписей можно тремя путями:
Способ 1. Вручную
Вручную переименовывать (менять) подписи для каждого пузырька. Можно просто щелкнуть мышью по контейнеру с подписью и ввести с клавиатуры новое название вместо старого. Очевидно, что при большом количестве пузырьков этот способ начинает напоминать мазохизм.
Способ 2. Надстройка XYChartLabeler
Нетрудно предположить, что с подобной проблемой до нас с вами сталкивались и другие пользователи Excel. И один из них, а именно легендарный Rob Bovey (дай бог ему здоровья) написал и выложил в открытый доступ бесплатную надстройку XYChartLabeler, добавляющую в Excel эту недостающую функцию.
После установки у вас появится новая вкладка (в старых версиях Excel — панель инструментов) XY Chart Labels:
Выделив пузырьки и воспользовавшись кнопкой Add Labels можно быстро и удобно добавить подписи сразу ко всем пузырькам в диаграмме, просто задав диапазон ячеек с текстом для подписей:
Способ 3. Excel 2013
В новой версии Microsoft Excel 2013 появилась наконец-таки возможность добавлять подписи к элементам данных диаграммы из любых произвольно выделенных ячеек. Дождались 🙂
Пузырьковая диаграмма в Excel и Инфографике
Пузырьковая диаграмма в Excel используется достаточно редко, но достаточно часто ее используют в «Инфографике». Поэтому каждому пользователю Excel стоит знать о ее бесспорных преимуществах.
Как построить пузырьковую диаграмму в Excel
На практическом примере отобразим пользу и построение пузырьковой диаграммы в Excel.
В районном центре, в который входят 3 населенных пункта, наступили выборы мера. На главную кандидатуру представлен лидер партии, о котором люди разного мнения во всех 3-х населенных пунктах. Так же в них разное количество жителей и желающих участвовать в выборах. О чем свидетельствуют данные записанные по каждому населенному пункту.
Команда пиар-менеджмента лидера партии хочет спрогнозировать результаты выборов при текущем отношении избирателей к своему клиенту-кандидату.
Отобразим данные об избирателях на пузырьковой диаграмме, чтобы легче было понять, где нужно наиболее активно проводить агитацию. Составьте таблицу данных об избирателях как показано на рисунке:
- Выделите диапазон B2:D4 и выберите инструмент: «Вставка»-«Диаграммы»-«Другие»-«Пузырьковая».
- Наш диапазон данных B2:D4 обладает одинаковым количеством строк и столбцов. Поэтому Excel может неправильно расположить данные. В таком случаи используйте инструмент: «Работа с диаграммами»-«Конструктор»-«Строка/столбец».
- Отредактируйте диаграмму. Сначала выберите: «Работа с диаграммами»-«Конструктор»-«Макеты диаграмм»-«Макет 7».
- Сделайте двойной щелчок левой кнопкой мышки по любой подписи справа возле любого пузырька. И в появившемся окне «Формат подписи данных» выберите 2 опции в разделе «Параметры подписи». Первая опция – «размеры пузырьков», вторая – «В центре». Галочки в «значения X» и «значения Y» можно убрать.
Полезный совет! Чтобы добавить названия осей на диаграмму используйте инструмент: «Работа с диаграммами»-«Макет»-«Название осей»-«Название основной горизонтальной оси»-«Название под осью» и «Название основной вертикальной оси»-«Повернутое название».
Как видно на диаграмме, наиболее важную роль для нашего кандидата в меры играют первые 2 населенных пункта: Коровьи Валы и Долинская. Они принесут наиболее весомое значение в результат выборов. Так же там проживает большая часть жителей готовых голосовать за нашего кандидата. Нерентабельными будут агитационные вложения в Плютово, так как там люди неохотно желают голосовать. А те, которые и придут на выборы, то большая их часть проголосует за конкурентов.
Пузырьковая диаграмма — двумерная диаграмма для сравнения трех и более наборов значений или пар данных.
Подробно разберем как построить пузырьковую диаграмму в Excel.
В простом понимании пузырьковая диаграмма является более сложной версией точечного графика.
Точечный график лучше всего подходит для сравнения пар значений или нахождения взаимозависимости между ними (при этом значения откладываются по осям координат). Если же добавляется третий параметр, то для этих целей отлично подойдет пузырьковая диаграмма (третий параметр отвечает за размер пузырька).
Другими словами, пузырьковая диаграмма — разновидность точечного графика, где вместо точек используются пузырьки, которые также являются еще одним параметром измерения данных.
Как построить пузырьковую диаграмму в Excel?
Рассмотрим построение на конкретном примере, предположим у нас имеются таблица с данными по трем параметрам: цена продуктов, объем продаж и доля в продажах на рынке.
При этом, чтобы корректно построить пузырьковую диаграмму, расположим столбцы с данными в следующем порядке: первый столбец с данными по оси X, второй — по оси Y, третий — по размеру пузырька.
Выделяем диапазон с данными B1:D5 и на панели вкладок выбираем Вставка -> Диаграмма -> Точечная -> Пузырьковая:
По умолчанию при построении диаграммы пузырьки раскрашиваются в один и тот же цвет.
Если такой вариант не подходит и хочется сделать цветовое разделение между пузырьками, то можно либо вручную поменять цвет для каждого пузырька отдельно, либо установить галочку напротив поля Разноцветные точки в настройке формата данных:
После настройки цветов пузырьков диаграмма принимает следующий вид:
В результате мы получили диаграмму, где по горизонтали (ось X) отображается стоимость продуктов, по вертикали (ось Y) — объем продаж данных продуктов, а размером пузырька показывается доля продукта на рынке.
Остался последний шаг — добавление подписей, однако именно с ним зачастую возникает больше всего проблем.
Добавление подписей
Для версий Excel 2010 и младше при попытке добавить подписи данных на диаграмму возникает проблема — мы не можем добавить имена категорий, чтобы была возможность отличать пузырьки друг от друга.
При добавлении подписи на выбор предлагаются 4 основных варианта: имя ряда, значения X, значения Y и размеры пузырьков.
Для добавления имен категорий можно добавить произвольную подпись, а затем вручную поменять подпись для каждого пузырька. Достаточно щелкнуть мышкой на поле с подписью и в поле формулы прописать новую.
Однако ручное добавление подписи придется делать для каждого ряда данных, что при большом количестве данных достаточно неудобно и увеличивает вероятность совершить где-нибудь ошибку.
Если же у вас версия Excel 2013 и старше, то подобной проблемы не возникает, так как появляется дополнительный вариант подписи Значения из ячеек, который позволяет добавить подпись из произвольного диапазона ячеек.
Нажимаем правой кнопкой мыши на ряд с пузырьками и выбираем Добавить подписи данных, а затем в настройках формата указываем нужный нам диапазон ячеек A2:A5 (в котором содержатся имена категорий):
Можно выбрать как какой-либо один из вариантов подписи, так и комбинировать различные варианты, в данном случае добавим на график наименование продукта и долю, занимаемому им на рынке:
В завершении построения также можно добавить названия осей, настроить параметры осей и т.д.: