Пузырьковая диаграмма (Bubble Chart)

Большинство из тех, кто хоть раз строил графики в Microsoft Excel или PowerPoint, замечали необычный и забавный тип диаграмм - пузырьковые (bubble chart). Многие видели их в чужих файлах или презентациях. Однако, в 99 случаев из 100, при попытке самостоятельно построить такую диаграмму впервые, пользователи сталкиваются с рядом неочевидных сложностей. Обычно Excel или отказывается ее создавать вообще, или создает, но совершенно непонятного вида, без подписей и наглядности.

Давайте разберемся в этой теме.

Что такое пузырьковая диаграмма

Пузырьковая диаграмма - это специфический тип диаграмм, способных отображать трехмерные данные в двумерном пространстве. Рассмотрим для примера вот такую диаграмму, отображающую статистику по странам с известного сайта-конструктора диаграмм http://www.gapminder.org/ :

bubble1.png

Скачать полноразмерный PDF можно отсюда http://www.gapminder.org/downloads/gapminder-world-map/

По горизонтальной оси Х откладывается средний годовой доход на душу населения в USD. По вертикальной оси Y откладывается средняя продолжительность жизни в годах. Размер же (диаметр или площадь) каждого пузырька пропорционален населению каждой страны. Таким образом, на одной плоской диаграмме удается отобразить трехмерную информацию.

Дополнительную информационную нагрузку несет еще и цвет, отображающий региональную принадлежность каждой страны к определенному континенту.

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

Самый важный момент при построении пузырьковой диаграммы - правильно подготовленная таблица с исходными данными. А именно - таблица должна состоять строго из трех столбцов в следующем порядке (слева-направо):

  1. Параметр для откладывания по оси Х
  2. Параметр для откладывания по оси Y
  3. Параметр, определяющий размер пузырька

Возьмем для примера вот такую таблицу с данными по игровым приставкам:

bubble2.png

Чтобы построить по ней пузырьковую диаграмму, нужно выделить диапазон C3:E8 (строго - только оранжевые и серые ячейки без столбца с названиями) и затем:

  • В Excel 2007/2010 - перейти на вкладку Вставка - группа Диаграммы - Другие - Пузырьковая (Insert - Chart - Bubble)
  • В Excel 2003 и старше - выбрать в меню Вставка - Диаграмма - Пузырьковая (Insert - Chart - Bubble)

bubble3.png

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

bubble4.png

После создания пузырьковой диаграммы имеет смысл настроить подписи к осям - без названий осей трудно понять, что по какой из них откладывается. В 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 эту недостающую функцию.

Скачать надстройку можно здесь http://appspro.com/Utilities/ChartLabeler.htm

После установки у вас появится новая вкладка (в старых версиях Excel - панель инструментов) XY Chart Labels:

bubble5.png

Выделив пузырьки и воспользовавшись кнопкой Add Labels можно быстро и удобно добавить подписи сразу ко всем пузырькам в диаграмме, просто задав диапазон ячеек с текстом для подписей:

bubble6.png

Способ 3. Excel 2013

В новой версии Microsoft Excel 2013 появилась наконец-таки возможность добавлять подписи к элементам данных диаграммы из любых произвольно выделенных ячеек. Дождались :)

bubble7.png


13.12.2013 12:53:02
Большое спасибо за инструкцию. А то мучался 2 дня с этими пузырьками :)
18.01.2014 14:42:56
Спасибо!!!
но, возник вопрос: не могу установить "XYChartLabeler". Устанавливается неверно :{}
18.01.2014 19:30:58
Что такое в вашем понимании "неверно"?
26.01.2014 15:29:03
Николай, доброго времени суток! Как оказалось, вопроса то и нет :{}
Попробовала на другом компе - все установилось!
26.01.2014 16:40:44
Ну и замечательно. Бывает :)
11.02.2014 10:57:27
Николай, добрый день! Спасибо за отличную диаграмму! Но у меня возник вопрос... Поскольку мои данные очень разного порядка то получается наслоение одних данных на другие и данные с большим значением перекрывают меньшие. Я имею в виду данные которые отвечают за диаметр пузырька.  Я частично решил эту проблему изменив прозрачность больших значений, но хотелось бы расположить таким образом что бы большие значения были на заднем плане. Пробовал перемещать данные в таблице, но не заметил логики в построении, может есть какой то прием который позволит это сделать?
08.05.2014 14:47:48
Вот спасибо огромное за надстройку! У меня это процесс был автоматизирован макросом еще времен гражданской войны.
24.04.2015 16:47:36
На относительно большом количестве строк (400) и при скрытии части строк фильтром надстройка путает все подписи... Даже не знаю, что и делать, все надежды были на эту диаграмму...:o Может, подскажете какой-нибудь другой способ подписать точки/пузырьки?
27.04.2015 09:38:31
Как вариант - поставить Excel 2013 - там функция подписывания пузырьков названиями из произвольного диапазона встроена в базовый функционал.
27.04.2015 12:04:51
спасибо, но не поможет, так как все это на рабочем компьютере, и программное обеспечение у всех одно, выделяться нельзя.

Но вроде справился: сделал сводную, в которую подтягиваются те значения, что были в таблице, а поскольку из сводной точечная диаграмма не строится, данные для нее просто копируются  (A5=E5, A6=E6 и тп) в отдельный диапазон, по которому и строится диаграмма. В итоге фильтры применяются к сводной таблице, а поскольку она не скрывает строки при фильтрации, а просто заменяет значения, подписи к диаграмме не плывут.  Немного колхозно получается, но в целом мои нужды покрывает.  Жить будем :)
22.06.2015 11:20:00
Добрый день!
У меня по Оси У все ок, и размер тоже в процентах. Вот ось х никак неполучается. Стоит по порядку 1,2,3,4,5,6.
Добрый день! подскажите как можно разделить эту диаграмму на 4 квадрата с выделением цвета - например, игры с высокой скоростью и большим кол-м программ попадают в зеленый квадрат, и те, у которых маленькая скорость и мало программ в красный? возникла потребность на работе - построить такую диаграмму по офисам продаж с выделением худших и лучших. спасибо
22.09.2015 13:41:07
Николай, у меня установлен Microsoft Office профессиональный плюс 2013
Excel 2013 (15.0.4737.1000)
но у меня почему то не работает способ №3
В формате подписей данных у меня первая строчка "Включать в подпись имя ряда"
а включать в подпись значения из ячеек (как у Вас) нет.

Office обновленный.
Может быть есть какие то различия в версиях Excel - не знаете?
23.03.2016 23:19:49
Добрый вечер! У меня такая же проблема, что и у Дархан. По оси у все корректно, по оси х набор чисел по порядку от 1 до 6, соответсвенно диаграмма показывает неверную информацию. Пробовала менять параметры осей, ставила минимальные и максимальные значения в соответствии с данными по столбцам, все равно ничего не выходит: пузыри съезжают влево.
22.05.2016 07:13:46
Диаметр (или площадь) пузырьков рассчитывается Exсel как некое соотношение. А как сделать что бы диаметры (или площадь) пузырьков соответствовали фактическим размерам (исходным данным)?
13.11.2016 03:21:51
здравствуйте, подскажите, пожалуйста, как покрасить "пузырьки" диаграммы? Я строю диаграмму для трехсот оттенков краски. Для каждого оттенка есть изображение в jpeg. Хотелось бы чтобы цвет "пузырька" соответствовал оттенку краски. Я готов заказать платный динамический отчет. И еще один вопрос - есть ли в данный момент теоретическая возможность создавать в Excel трехмерные отчеты и использовать 3D-очки для их просмотра?