Большинство из тех, кто хоть раз строил графики в Microsoft Excel или PowerPoint, замечали необычный и забавный тип диаграмм — пузырьковые (bubble chart). Многие видели их в чужих файлах или презентациях. Однако, в 99 случаев из 100, при попытке самостоятельно построить такую диаграмму впервые, пользователи сталкиваются с рядом неочевидных сложностей. Обычно Excel или отказывается ее создавать вообще, или создает, но совершенно непонятного вида, без подписей и наглядности.
Давайте разберемся в этой теме.
Что такое пузырьковая диаграмма
Пузырьковая диаграмма — это специфический тип диаграмм, способных отображать трехмерные данные в двумерном пространстве. Рассмотрим для примера вот такую диаграмму, отображающую статистику по странам с известного сайта-конструктора диаграмм http://www.gapminder.org/ :
Скачать полноразмерный PDF можно отсюда http://www.gapminder.org/downloads/gapminder-world-map/
По горизонтальной оси Х откладывается средний годовой доход на душу населения в 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 эту недостающую функцию.
Скачать надстройку можно здесь http://appspro.com/Utilities/ChartLabeler.htm
После установки у вас появится новая вкладка (в старых версиях Excel — панель инструментов) XY Chart Labels:
Выделив пузырьки и воспользовавшись кнопкой Add Labels можно быстро и удобно добавить подписи сразу ко всем пузырькам в диаграмме, просто задав диапазон ячеек с текстом для подписей:
Способ 3. Excel 2013
В новой версии Microsoft Excel 2013 появилась наконец-таки возможность добавлять подписи к элементам данных диаграммы из любых произвольно выделенных ячеек. Дождались 
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Пузырьковая диаграмма — это разновидность точечной диаграммы, в которой точки данных заменены пузырьками, причем их размер служит дополнительным измерением данных. На пузырьковой диаграмме, как и на точечной, нет оси категорий — и горизонтальная, и вертикальная оси являются осями значений. В дополнение к значениям X и значениям Y, наносимым на точечную диаграмму, на пузырьковой диаграмме показаны также значения Z (размер).
Вы можете использовать пузырьковую диаграмму вместо точечной, если данные состоят из трех рядов, каждый из которых содержит набор значений. Размеры пузырьков определяются значениями третьего ряда данных. Пузырьковые диаграммы часто используются для представления финансовых данных. Пузырьки разных размеров позволяют визуально выделить конкретные значения.
Чтобы создать пузырьковую диаграмму, расположите данные на листе в строках или столбцах так, чтобы значения X перечислялись в первой строке или столбце, а соответствующие значения Y и значения размеров (Z) — в соседних строках или столбцах. Например, разместите данные на листе, как показано на рисунке:
На данной диаграмме число товаров показано вдоль горизонтальной оси, суммы продаж — вдоль вертикальной оси, а процентные доли рынка представлены разными размерами пузырьков.
Пузырьковую диаграмму целесообразно использовать, если данные включают следующее:
-
Три значения для каждой точки данных. Для каждого пузырька необходимы три значения. Они могут быть заданы в строках или столбцах листа, но должны располагаться в строгом порядке: значение X, значение Y и затем значение Z.
-
Несколько рядов данных. Нанесение нескольких рядов данных на пузырьковую диаграмму (несколько рядов пузырьков) аналогично нанесению нескольких рядов данных на точечную диаграмму (несколько рядов точек). На точечных диаграммах используются наборы значений X и значений Y, а на пузырьковых — наборы значений X, значений Y и значений Z.
При создании пузырьковой диаграммы можно выбрать отображение пузырьков в виде 2-D или с эффектом 3D.
К началу страницы
Создание сложной пузырьковой диаграммы
Итак, как была создана эта пузырьковая диаграмма? Следующая процедура поможет вам создать аналогичную пузырьковую диаграмму. Для этой диаграммы мы использовали данные из листа примера. Вы можете скопировать эти данные на свой лист или использовать собственные.
-
Скопируйте данные примера на пустой лист или откройте лист, содержащий данные, которые вы хотите отобразить в виде пузырьковой диаграммы.
Копирование данных примера
-
Создайте пустую книгу или лист.
-
Выделите пример в разделе справки.
Примечание: Не выделяйте заголовки строк или столбцов.
-
Нажмите клавиши CTRL+C.
-
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
A
B
C
1
Количество товаров
Продажи
Процент доли рынка
2
5
5500 ₽
3%
3
14
12 200 ₽
12%
4
20
60 000 ₽
33%
5
18
24 400 ₽
10%
6
22
32 000 ₽
42 %
Примечание: Убедитесь, что на листе есть по крайней мере четыре строки или столбца данных. Если попытаться построить пузырьковую диаграмму по трем строкам или столбцам данных, пузырьки будут отображены неправильно.
-
-
Выделите данные, которые вы хотите отобразить на пузырьковой диаграмме.
Примечание: Не рекомендуется включать в выделенный фрагмент заголовки строк или столбцов. Если вместе с данными выделить заголовки, диаграмма может быть построена неправильно.
-
На вкладке Вставка в группе Диаграммы щелкните стрелку рядом с кнопкой Точечная диаграмма.
-
Выберите пузырек со значком 3-D effect.
-
Щелкните область диаграммы. Откроется вкладка Работа с диаграммами.
-
В группе Инструментыдля диаграмм на вкладке Конструктор в группе Стили диаграмм выберите нужный стиль диаграммы.
-
Если на диаграмме есть легенда, щелкните ее и нажмите кнопку DELETE.
-
Чтобы изменить формат диаграммы, на вкладке Формат в группе Размер задайте нужный размер фигуры в полях Высота фигуры и Ширина фигуры, а затем нажмите клавишу ВВОД.
Для данной пузырьковой диаграммы мы присвоили высоте и ширине фигуры значение 8,9 см.
-
Чтобы отформатеть и расположить название диаграммы на диаграмме, щелкните область диаграммы и сделайте следующее:
-
Щелкните название диаграммы и введите нужный текст.
Для данной пузырьковой диаграммы мы ввели Определение доли рынка в отрасли.
-
Чтобы уменьшить размер названия диаграммы, щелкните его правой кнопкой мыши, выберите шрифт ивведите нужный размер.
Для нашей пузырьковой диаграммы использовался размер 12.
-
Чтобы выровнять название диаграммы относительно области построения, щелкните его и перетащите в нужное место.
-
-
Чтобы добавить название горизонтальной оси, щелкните в области диаграммы и сделайте следующее:
-
Щелкните знак «плюс» рядом с диаграммой
-
Выберите Названия осей, а затем — Основной горизонтальный.
-
Выберите текстовое поле Название оси и введите текст.
Для этой пузырьковой диаграммы мы ввели Количество товаров.
-
-
Щелкните вертикальную ось или выберите ее в списке элементовдиаграммы (вкладка Формат, группа Текущий выделение, поле Элементы диаграммы).
-
На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного.
-
В разделе Параметры оси сделайте следующее:
-
Для минимальногозначение введите 0 (ноль).
-
Для максимальногочисла введите нужное число.
Для этой пузырьковой диаграммы мы ввели 80 00.
-
-
В диалоговом окне Формат оси щелкните вкладку Число.
-
В полеЧисло в поле Число десятичных знаков введите 0 (ноль), а затем щелкните стрелку вниз в правом верхнем углу и нажмите кнопку Закрыть.
-
Чтобы применить специальный эффект форматирования к области диаграммы, области диаграммы, заголовку диаграммы или вертикальной оси диаграммы, щелкните этот элемент диаграммы или выберите его в спискеэлементов диаграммы (вкладка Формат, группа Текущий выделение, поле Элементы диаграммы) и сделайте следующее:
-
На вкладке Формат в группе Стили фигур нажмите кнопку Дополнительные действия рядом с кнопкой Эффекты фигур.
-
Щелкните нужный эффект.
-
-
Если вы не хотите использовать цвета темы, которая по умолчанию применяется к книге, сделайте следующее:
-
На вкладке Разметка страницы в группе Темы нажмите кнопку Темы.
-
Выберите тему, которую вы хотите использовать. (Наведите курсор на каждую тему, чтобы просмотреть, как она будет выглядеть при применении к диаграмме.)
-
Диаграмму можно сохранить как шаблон, который можно использовать в качестве основы для других похожих диаграмм. Дополнительные сведения можно найти в меню Сохранение пользовательской диаграммы в качестве шаблона.
К началу страницы
Создание сложной пузырьковой диаграммы
Итак, как была создана эта пузырьковая диаграмма? Следующая процедура поможет вам создать аналогичную пузырьковую диаграмму. Для этой диаграммы мы использовали данные из листа примера. Вы можете скопировать эти данные на свой лист или использовать собственные.
-
Скопируйте данные примера на пустой лист или откройте лист, содержащий данные, которые вы хотите отобразить в виде пузырьковой диаграммы.
Копирование данных листа примера
-
Создайте пустую книгу или лист.
-
Выделите пример в разделе справки.
Примечание: Не выделяйте заголовки строк или столбцов.
-
Нажмите клавиши CTRL+C.
-
Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.
A
B
C
1
Количество товаров
Продажи
Процент доли рынка
2
5
5500 ₽
3%
3
14
12 200 ₽
12%
4
20
60 000 ₽
33%
5
18
24 400 ₽
10%
6
22
32 000 ₽
42 %
Примечание: Убедитесь, что на листе есть по крайней мере четыре строки или столбца данных. Если попытаться построить пузырьковую диаграмму по трем строкам или столбцам данных, пузырьки будут отображены неправильно.
-
-
Выделите данные, которые вы хотите отобразить на пузырьковой диаграмме.
Примечание: Не рекомендуется включать в выделенный фрагмент заголовки строк или столбцов. Если вместе с данными выделить заголовки, диаграмма может быть построена неправильно.
-
На вкладке Вставка в группе Диаграммы нажмите кнопку Другие диаграммы.
-
В разделе Пузырьковая выберите вариант Объемная пузырьковая.
-
Щелкните область диаграммы.
Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.
-
На вкладке Конструктор в группе Стили диаграмм выберите стиль, который требуется использовать.
Для нашей пузырьковой диаграммы использовался Стиль 29.
-
На диаграмме щелкните легенду и нажмите клавишу DELETE.
-
Чтобы изменить формат диаграммы, на вкладке Формат в группе Размер задайте нужный размер фигуры в полях Высота фигуры и Ширина фигуры, а затем нажмите клавишу ВВОД.
Для данной пузырьковой диаграммы мы присвоили высоте и ширине фигуры значение 8,9 см.
-
Чтобы добавить, отформатировать и расположить требуемым образом название диаграммы, щелкните область диаграммы и сделайте следующее:
-
На вкладке Макет в группе Подписи нажмите кнопку Название диаграммы и выберите пункт Над диаграммой.
-
Щелкните название диаграммы и введите нужный текст.
Для данной пузырьковой диаграммы мы ввели Определение доли рынка в отрасли.
-
Чтобы уменьшить размер названия диаграммы, щелкните его правой кнопкой мыши и введите нужное значение в контекстном меню в поле Размер.
Для нашей пузырьковой диаграммы использовался размер 12.
-
Чтобы выровнять название диаграммы относительно области построения, щелкните его и перетащите в нужное место.
-
-
Чтобы добавить название горизонтальной оси, щелкните в области диаграммы и сделайте следующее:
-
На вкладке Макет в группе Подписи нажмите кнопку Названия осей, щелкните Название основной горизонтальной оси и выберите Название под осью.
-
Щелкните название горизонтальной оси, введите нужный текст и нажмите клавишу ВВОД.
Для этой пузырьковой диаграммы мы ввели Количество товаров.
-
-
Щелкните вертикальную ось или выберите ее из списка элементов диаграммы (вкладка Макет, группа Текущий фрагмент, поле Элементы диаграммы).
-
На вкладке Формат в группе Текущий фрагмент нажмите кнопку Формат выделенного.
-
В разделе Параметры оси сделайте следующее:
-
Установите переключатель минимальное значение в положение фиксированное и введите 0 (нуль) в поле фиксированное.
-
Установите переключатель максимальное значение в положение фиксированное и введите нужное число в поле фиксированное.
Для этой пузырьковой диаграммы мы ввели 80 00.
-
-
В диалоговом окне Формат оси щелкните вкладку Число.
-
Выберите формат Числовой и в поле Число десятичных знаков введите 0 (нуль), после чего нажмите кнопку Закрыть.
-
Чтобы применить специальное форматирование к области построения, области диаграммы, названию диаграммы или ее вертикальной оси, щелкните требуемый элемент диаграммы или выберите его в списке элементов диаграммы (вкладка Макет, группа Текущий фрагмент, поле Элементы диаграммы), а затем сделайте следующее:
-
На вкладке Формат в группе Стили фигур нажмите кнопку Дополнительные
. -
Щелкните нужный эффект.
Для данной пузырьковой диаграммы мы использовали стиль Слабый эффект — Акцент 4 для области построения, Сильный эффект — Акцент 4 для области диаграммы, Слабый эффект — Акцент 4 для названия диаграммы и Четкая линия — Акцент 6 для вертикальной оси.
-
-
Если вы не хотите использовать цвета темы, которая по умолчанию применяется к книге, сделайте следующее:
-
На вкладке Разметка страницы в группе Темы нажмите кнопку Темы.
-
В группе Встроенные выберите тему, которую вы хотите использовать.
Для нашей пузырьковой диаграммы использовалась тема Литейная.
-
.К началу страницы
Сохранение диаграммы в качестве шаблона
Если вы хотите создавать аналогичные диаграммы на основе созданной диаграммы, можно сохранить ее в качестве шаблона.
-
Щелкните диаграмму, которую требуется сохранить как шаблон.
-
На вкладке Конструктор в группе Тип нажмите кнопку Сохранить как шаблон.
-
В поле Имя файла введите имя шаблона.
Совет: Если не указать другую папку, файл шаблона (CRTX) будет сохранен в папке Диаграммы, а шаблон станет доступен в группе Шаблоны в диалоговом окне Вставка диаграммы(вкладка Вставка, группа Диаграммы,
) и диалоговое окно Изменение типа диаграммы(вкладкаКонструктор, группа Тип,Изменение типа диаграммы).Дополнительные сведения о том, как применить шаблон диаграммы, см. в статье Сохранение диаграммы как шаблона.
) и диалоговое окно Изменение типа диаграммы(вкладкаКонструктор, группа Тип,Изменение типа диаграммы).Примечание: Шаблон диаграмма содержит форматирование диаграммы и цвета, которые использовались при его сохранении. В диаграмме, которая создается в другой книге на основе шаблона, используются цвета из шаблона, а не цвета темы документа, примененной к книге. Чтобы использовать цвета темы документа вместо цветов шаблона, щелкните область диаграммы правой кнопкой мыши и выберите в контекстном меню команду Удалить форматирование, отсутствующее в стиле.
К началу страницы
Нужна дополнительная помощь?
Пузырьковая диаграмма в Excel используется достаточно редко, но достаточно часто ее используют в «Инфографике». Поэтому каждому пользователю Excel стоит знать о ее бесспорных преимуществах.
Как построить пузырьковую диаграмму в Excel
На практическом примере отобразим пользу и построение пузырьковой диаграммы в Excel.
В районном центре, в который входят 3 населенных пункта, наступили выборы мера. На главную кандидатуру представлен лидер партии, о котором люди разного мнения во всех 3-х населенных пунктах. Так же в них разное количество жителей и желающих участвовать в выборах. О чем свидетельствуют данные записанные по каждому населенному пункту.
Команда пиар-менеджмента лидера партии хочет спрогнозировать результаты выборов при текущем отношении избирателей к своему клиенту-кандидату.
Отобразим данные об избирателях на пузырьковой диаграмме, чтобы легче было понять, где нужно наиболее активно проводить агитацию. Составьте таблицу данных об избирателях как показано на рисунке:
- Выделите диапазон B2:D4 и выберите инструмент: «Вставка»-«Диаграммы»-«Другие»-«Пузырьковая».
- Наш диапазон данных B2:D4 обладает одинаковым количеством строк и столбцов. Поэтому Excel может неправильно расположить данные. В таком случаи используйте инструмент: «Работа с диаграммами»-«Конструктор»-«Строка/столбец».
- Отредактируйте диаграмму. Сначала выберите: «Работа с диаграммами»-«Конструктор»-«Макеты диаграмм»-«Макет 7».
- Сделайте двойной щелчок левой кнопкой мышки по любой подписи справа возле любого пузырька. И в появившемся окне «Формат подписи данных» выберите 2 опции в разделе «Параметры подписи». Первая опция – «размеры пузырьков», вторая – «В центре». Галочки в «значения X» и «значения Y» можно убрать.
Полезный совет! Чтобы добавить названия осей на диаграмму используйте инструмент: «Работа с диаграммами»-«Макет»-«Название осей»-«Название основной горизонтальной оси»-«Название под осью» и «Название основной вертикальной оси»-«Повернутое название».
Как видно на диаграмме, наиболее важную роль для нашего кандидата в меры играют первые 2 населенных пункта: Коровьи Валы и Долинская. Они принесут наиболее весомое значение в результат выборов. Так же там проживает большая часть жителей готовых голосовать за нашего кандидата. Нерентабельными будут агитационные вложения в Плютово, так как там люди неохотно желают голосовать. А те, которые и придут на выборы, то большая их часть проголосует за конкурентов.
Пузырьковая диаграмма в Excel это разновидность точечной диаграммы. Но, в отличие от нее, у пузырьковой диаграммы есть третий параметр — размер пузырьков.
Пузырьковая диаграмма пример построения
1. Для построения пузырьковой диаграммы в excel нужно подготовить 3 ряда данных: данные по оси Х, данные по оси Y и размер пузырьков.
Для примера имеем таблицу с продажами по регионам:
По умолчанию данных из первого столбца расположатся по оси Х, из второго — по Y, и третий столбец даст размер пузырьков.
2. Выделим числовые данные с заголовками и перейдем на вкладку Вставка — блок Диаграммы — Точечные диаграммы — Пузырьковая диаграмма.
3. Получили заготовку диаграммы.
Пока что на ней мало что можно понять, поэтому давайте ее доработаем.
4. Прежде всего, выделим пузырьки, щелкнем на любом из них правой кнопкой мыши и выберем Формат ряда данных. Установим параметр Значениям соответствует — диаметр пузырьков.
Это делать, в принципе, не обязательно. Но так при плоской диаграмме можно сделать пузырьки более контрастными по размеру. А наша цель — именно показать различия в размере пузырьков.
5. Далее снова правая кнопка мыши на пузырьке — Добавить подписи данных.
Снова правой кнопкой, но уже на подписи данных, и выбираем Формат подписей данных.
Добавим названия регионов к пузырькам. По умолчанию нет такой “галочки”, но можно выбрать пункт значения из ячеек и выделить ячейки с названиями регионов.
А “галочку” Значения Y снимем, чтобы они не загромождали диаграмму.
6. Здесь же сделаем Положение подписи — в центре.
Все эти настройки индивидуальны, вы можете выводить те данные, которые вам нужны.
7. Диаграмма готова.
Как читать пузырьковую диаграмму
Теперь нужно понять, что же мы можем увидеть из построенной пузырьковой диаграммы в excel.
Посмотрим на предыдущий рисунок.
- Размер пузырьков в нашем примере соответствует показателю Выручка. Значит, самую большую выручку приносят регионы Сибирь и Центр. Самую маленькую — Юг и Урал.
- Пузырь Сибирь находится в левом верхнем углу диаграммы: значит, что количество сделок у него небольшое (по оси Х), а выручку он делает за счет высокой цены сделки (высоко по оси Y).
- Пузырь Центр, наоборот, находится внизу справа: высокая выручка в него за счет большого количества сделок (ось Х) при низкой цене сделки (ось Y).
- Пузырь Юг самый маленький — этот регион приносит самую низкую выручку. При этой количество сделок у него даже больше, чем у рекордсмена Сибирь (находится правее по оси Х). Однако за счет того, что сделки имеют низкую стоимость (пузырь низко по оси Y) — выручки рекордно низкие.
и так далее. При анализе пузырьковой диаграммы важно учитывать все три составляющих: положение по оси Х, положение по оси Y и величину пузырьков.
В этой статье мы узнали, как строится пузырьковая диаграмма в Excel.
Сообщество Excel Analytics | обучение Excel
Канал на Яндекс.Дзен
Вам может быть интересно:
Пузырьковая диаграмма в Excel
Пузырьковая диаграмма — двумерная диаграмма для сравнения трех и более наборов значений или пар данных.
Подробно разберем как построить пузырьковую диаграмму в Excel.
В простом понимании пузырьковая диаграмма является более сложной версией точечного графика.
Точечный график лучше всего подходит для сравнения пар значений или нахождения взаимозависимости между ними (при этом значения откладываются по осям координат). Если же добавляется третий параметр, то для этих целей отлично подойдет пузырьковая диаграмма (третий параметр отвечает за размер пузырька).
Другими словами, пузырьковая диаграмма — разновидность точечного графика, где вместо точек используются пузырьки, которые также являются еще одним параметром измерения данных.
Как построить пузырьковую диаграмму в Excel?
Рассмотрим построение на конкретном примере, предположим у нас имеются таблица с данными по трем параметрам: цена продуктов, объем продаж и доля в продажах на рынке.
При этом, чтобы корректно построить пузырьковую диаграмму, расположим столбцы с данными в следующем порядке: первый столбец с данными по оси X, второй — по оси Y, третий — по размеру пузырька.
Выделяем диапазон с данными B1:D5 и на панели вкладок выбираем Вставка -> Диаграмма -> Точечная -> Пузырьковая:
По умолчанию при построении диаграммы пузырьки раскрашиваются в один и тот же цвет.
Если такой вариант не подходит и хочется сделать цветовое разделение между пузырьками, то можно либо вручную поменять цвет для каждого пузырька отдельно, либо установить галочку напротив поля Разноцветные точки в настройке формата данных:
После настройки цветов пузырьков диаграмма принимает следующий вид:
В результате мы получили диаграмму, где по горизонтали (ось X) отображается стоимость продуктов, по вертикали (ось Y) — объем продаж данных продуктов, а размером пузырька показывается доля продукта на рынке.
Остался последний шаг — добавление подписей, однако именно с ним зачастую возникает больше всего проблем.
Добавление подписей
Для версий Excel 2010 и младше при попытке добавить подписи данных на диаграмму возникает проблема — мы не можем добавить имена категорий, чтобы была возможность отличать пузырьки друг от друга.
При добавлении подписи на выбор предлагаются 4 основных варианта: имя ряда, значения X, значения Y и размеры пузырьков.
Для добавления имен категорий можно добавить произвольную подпись, а затем вручную поменять подпись для каждого пузырька. Достаточно щелкнуть мышкой на поле с подписью и в поле формулы прописать новую.
Однако ручное добавление подписи придется делать для каждого ряда данных, что при большом количестве данных достаточно неудобно и увеличивает вероятность совершить где-нибудь ошибку.
Если же у вас версия Excel 2013 и старше, то подобной проблемы не возникает, так как появляется дополнительный вариант подписи Значения из ячеек, который позволяет добавить подпись из произвольного диапазона ячеек.
Нажимаем правой кнопкой мыши на ряд с пузырьками и выбираем Добавить подписи данных, а затем в настройках формата указываем нужный нам диапазон ячеек A2:A5 (в котором содержатся имена категорий):
Можно выбрать как какой-либо один из вариантов подписи, так и комбинировать различные варианты, в данном случае добавим на график наименование продукта и долю, занимаемому им на рынке:
В завершении построения также можно добавить названия осей, настроить параметры осей и т.д.:
Пузырьковая диаграмма в Excel и Инфографике
Пузырьковая диаграмма в Excel используется достаточно редко, но достаточно часто ее используют в «Инфографике». Поэтому каждому пользователю Excel стоит знать о ее бесспорных преимуществах.
Как построить пузырьковую диаграмму в Excel
На практическом примере отобразим пользу и построение пузырьковой диаграммы в Excel.
В районном центре, в который входят 3 населенных пункта, наступили выборы мера. На главную кандидатуру представлен лидер партии, о котором люди разного мнения во всех 3-х населенных пунктах. Так же в них разное количество жителей и желающих участвовать в выборах. О чем свидетельствуют данные записанные по каждому населенному пункту.
Команда пиар-менеджмента лидера партии хочет спрогнозировать результаты выборов при текущем отношении избирателей к своему клиенту-кандидату.
Отобразим данные об избирателях на пузырьковой диаграмме, чтобы легче было понять, где нужно наиболее активно проводить агитацию. Составьте таблицу данных об избирателях как показано на рисунке:
- Выделите диапазон B2:D4 и выберите инструмент: «Вставка»-«Диаграммы»-«Другие»-«Пузырьковая».
- Наш диапазон данных B2:D4 обладает одинаковым количеством строк и столбцов. Поэтому Excel может неправильно расположить данные. В таком случаи используйте инструмент: «Работа с диаграммами»-«Конструктор»-«Строка/столбец».
- Отредактируйте диаграмму. Сначала выберите: «Работа с диаграммами»-«Конструктор»-«Макеты диаграмм»-«Макет 7».
- Сделайте двойной щелчок левой кнопкой мышки по любой подписи справа возле любого пузырька. И в появившемся окне «Формат подписи данных» выберите 2 опции в разделе «Параметры подписи». Первая опция – «размеры пузырьков», вторая – «В центре». Галочки в «значения X» и «значения Y» можно убрать.
Полезный совет! Чтобы добавить названия осей на диаграмму используйте инструмент: «Работа с диаграммами»-«Макет»-«Название осей»-«Название основной горизонтальной оси»-«Название под осью» и «Название основной вертикальной оси»-«Повернутое название».
Как видно на диаграмме, наиболее важную роль для нашего кандидата в меры играют первые 2 населенных пункта: Коровьи Валы и Долинская. Они принесут наиболее весомое значение в результат выборов. Так же там проживает большая часть жителей готовых голосовать за нашего кандидата. Нерентабельными будут агитационные вложения в Плютово, так как там люди неохотно желают голосовать. А те, которые и придут на выборы, то большая их часть проголосует за конкурентов.
Пузырьковая диаграмма
Пузырьковая диаграмма – это разновидность точечной диаграммы, в которой точки данных заменены пузырьками, причем их размер служит дополнительным (третьим) измерением данных. На пузырьковой диаграмме, как и на точечной, нет оси категорий — и горизонтальная, и вертикальная оси являются осями значений. В дополнение к значениям X и значениям Y, наносимым на точечную диаграмму, на пузырьковой диаграмме показаны также значения Z (размер). Пример пузырьковой диаграммы приведен на рис. 1 (см. также лист «Пример1» в Excel-файле).
Рис. 1. Пример пузырьковой диаграммы
Скачать статью в формате Word, дополнительные материалы в формате Excel
Наиболее полно методы построения и оформления пузырьковых диагрмм рассмотрены на официальном сайте MS Office.
Чтобы создать пузырьковую диаграмму в Excel, расположите данные на листе в столбцах так, чтобы значения X размещались в первом столбце, значения Y – во втором, а значения Z (размеры пузырьков) — в третьем. Например, данные, на основе которых построена пузырьковая диаграмма, изображенная на рис. 1, размещены следующим образом:
В пузырьковых диаграммах можно использовать и более одного набора данных (рис. 2 и лист «Пример2» в Excel-файле). Создайте второй набор данных:
выберите диаграмму, кликните на ней правой кнопкой мыши и нажмите «Выбрать данные»:
В открывшемся окне нажмите «Добавить»:
Заполните имя ряда, значения Х, значения Y, размеры пузырьков:
На нашей пузырьковой диаграмме появился второй набор данных:
Рис. 2. Пузырьковая диаграмма с двумя наборами данных (внутри пузырьков указаны значения Х).
Вообще-то говоря, использование тех или иных видов диаграмм, это не очень сложная наука, весьма доступно и наглядно изложенная в книге Джина Желязны «Говори на языке диаграмм». Если времени на прочтение книжки нет, рекомендую краткое изложение основных ее идей.
Так вот, точечные диаграммы используются для демонстрации корреляционных зависимостей двух переменных. Пузырьковые диаграммы позволяют наглядно представить и анализировать взаимозависимости трех переменных.
Широкое распространение пузырьковые диаграммы получили для представления матриц типа BCG (Boston Consulting Group). Рассмотрим несколько примера на эту тему.
Рис. 3. Классическая BCG-матрица – анализ продуктового портфеля
Рис. 4. Вариация на тему BCG-матрицы
Кроме того, пузырьковые диаграммы, точнее, принцип их построения используется для создания инфографики – графического способа подачи информации, данных и знаний (рис. 5).
Рис. 5. Крупнейшие геологические катастрофы последних десятилетий (РИА НОВОСТИ)
С примером построения такого рода диаграммы можно ознакомиться в Excel-файле лист «Пример3». Карта России с разбиением на федеральные округа была найдена в Интернете, сохранена как рисунок и импортирована в качестве фона области построения диаграммы. Координаты (X и Y) каждого «пузыря» были подобраны вручную.
Рис. 6. Распределение населения России по федеральным округам
Поскольку доля населения определялась размером пузырей, мне хотелось вывести на диаграмме в качестве подписей названия округов. К сожалению, стандартными средствами Excel это сделать нельзя. Воспользуйтесь макросом, и вы сможете вывести рядом с пузырями практически любые подписи.
Представление данных в виде пузырьковой диаграммы
Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).
Пузырьковая диаграмма — это разновидность точечной диаграммы, в которой точки данных заменены пузырьками, причем их размер служит дополнительным измерением данных. На пузырьковой диаграмме, как и на точечной, нет оси категорий — и горизонтальная, и вертикальная оси являются осями значений. В дополнение к значениям X и значениям Y, наносимым на точечную диаграмму, на пузырьковой диаграмме показаны также значения Z (размер).
Вы можете использовать пузырьковую диаграмму вместо точечной, если данные состоят из трех рядов, каждый из которых содержит набор значений. Размеры пузырьков определяются значениями третьего ряда данных. Пузырьковые диаграммы часто используются для представления финансовых данных. Пузырьки разных размеров позволяют визуально выделить конкретные значения.
Чтобы создать пузырьковую диаграмму, расположите данные на листе в строках или столбцах так, чтобы значения X перечислялись в первой строке или столбце, а соответствующие значения Y и значения размеров (Z) — в соседних строках или столбцах. Например, разместите данные на листе, как показано на рисунке:
На данной диаграмме число товаров показано вдоль горизонтальной оси, суммы продаж — вдоль вертикальной оси, а процентные доли рынка представлены разными размерами пузырьков.
Пузырьковую диаграмму целесообразно использовать, если данные включают следующее:
Три значения для каждой точки данных. Для каждого пузырька необходимы три значения. Они могут быть заданы в строках или столбцах листа, но должны располагаться в строгом порядке: значение X, значение Y и затем значение Z.
Несколько рядов данных. Нанесение нескольких рядов данных на пузырьковую диаграмму (несколько рядов пузырьков) аналогично нанесению нескольких рядов данных на точечную диаграмму (несколько рядов точек). На точечных диаграммах используются наборы значений X и значений Y, а на пузырьковых — наборы значений X, значений Y и значений Z.
Когда вы создаете пузырьковую диаграмму, вы можете выбрать отображение пузырьков в плоском формате или объемном эффекте.
Пузырьковая диаграмма (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 появилась наконец-таки возможность добавлять подписи к элементам данных диаграммы из любых произвольно выделенных ячеек. Дождались 🙂