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

Пузырьковая диаграмма

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

Читать еще: Как сделать лэпбук в детском саду

Пузырьковая диаграмма в Excel

Пузырьковая диаграмма — двумерная диаграмма для сравнения трех и более наборов значений или пар данных.
Подробно разберем как построить пузырьковую диаграмму в Excel.

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

Другими словами, пузырьковая диаграмма — разновидность точечного графика, где вместо точек используются пузырьки, которые также являются еще одним параметром измерения данных.

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

Рассмотрим построение на конкретном примере, предположим у нас имеются таблица с данными по трем параметрам: цена продуктов, объем продаж и доля в продажах на рынке.
При этом, чтобы корректно построить пузырьковую диаграмму, расположим столбцы с данными в следующем порядке: первый столбец с данными по оси X, второй — по оси Y, третий — по размеру пузырька.

Выделяем диапазон с данными B1:D5 и на панели вкладок выбираем Вставка -> Диаграмма -> Точечная -> Пузырьковая:

По умолчанию при построении диаграммы пузырьки раскрашиваются в один и тот же цвет.
Если такой вариант не подходит и хочется сделать цветовое разделение между пузырьками, то можно либо вручную поменять цвет для каждого пузырька отдельно, либо установить галочку напротив поля Разноцветные точки в настройке формата данных:

После настройки цветов пузырьков диаграмма принимает следующий вид:

В результате мы получили диаграмму, где по горизонтали (ось X) отображается стоимость продуктов, по вертикали (ось Y) — объем продаж данных продуктов, а размером пузырька показывается доля продукта на рынке.
Остался последний шаг — добавление подписей, однако именно с ним зачастую возникает больше всего проблем.

Добавление подписей

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

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

Читать еще: Как сделать каркас для куклы

Если же у вас версия Excel 2013 и старше, то подобной проблемы не возникает, так как появляется дополнительный вариант подписи Значения из ячеек, который позволяет добавить подпись из произвольного диапазона ячеек.

Нажимаем правой кнопкой мыши на ряд с пузырьками и выбираем Добавить подписи данных, а затем в настройках формата указываем нужный нам диапазон ячеек A2:A5 (в котором содержатся имена категорий):

Можно выбрать как какой-либо один из вариантов подписи, так и комбинировать различные варианты, в данном случае добавим на график наименование продукта и долю, занимаемому им на рынке:

В завершении построения также можно добавить названия осей, настроить параметры осей и т.д.:

Пузырьковая диаграмма в excel

Видео: Пузырьковая диаграмма Power View Excel 2013

Можно построить много разных диаграмм в Excel. Основной принцип построения диаграммы описан в статье «Как сделать диаграмму в Excel».

Здесь рассмотрим нюансы, которые помогут построить и настроить пузырьковую диаграмму в Excel. Можно сделать диаграмму, которая будет сравнивать три разных значения.

Итак, пузырьковая диаграмма в Excel.

Например, у нас есть таблица с данными по месяцам, в каком месяце какого товара продали больше в рублях и процентах.

Внимание! Размещать данные в таблице для пузырьковой диаграммы нужно в определенной последовательности.

Столбец B – здесь размещаем данные для горизонтальной оси (оси X).
Столбец С – здесь разместим данные для вертикальной оси (оси Y).
Столбец D – здесь разместим данные для пузырьков.

Совет. В столбце B ставим дату в формате «Дата», чтобы в диаграмме были написаны даты. Выделяем столбцы в таблице диапазоном В2:D7.

В Word 2013 на закладке «Вставка» в разделе «Диаграммы» нажимаем на кнопку «Вставить точечную или пузырьковую диаграмму». Выбираем — «Объемная пузырьковая». Можно выбрать — «Пузырьковая». В

Word 2007 на закладке «Вставка» в разделе «Диаграммы» нажимаем на кнопку «Другие диаграммы». В появившемся окне выбираем диаграмму «Пузырьковая» или «Объемная пузырьковая». Получилась такая пузырьковая диаграмма. Размер пузырька соответствует числам в столбце D.

Теперь настраиваем эту диаграмму.

Как настраивать разные области диаграммы, смотрите в статье «Как сделать график в Excel». Здесь рассмотрим другие нюансы работы с диаграммами. Нажимаем на любой пузырек в диаграмме правой мышкой. Из появившегося контекстного меню выбираем функцию «Формат ряда данных». Появится диалоговое окно. В этом окне в разделе «Параметры ряда» можно настроить масштаб пузырьков.

Поставить галочку у слов «диаметр пузырьков» и оставить число «100» % или изменить, еще, и число процентов. Можно сделать пузырьки разноцветными. В этом же окне «Формат ряда данных» переходим в раздел «Заливка и границы» (в Word2007 – «Заливка»).

Ставим галочку у функции. «Разноцветные точки». Подписи данных в диаграмме Excel. Можно разместить данные на пузырьках диаграммы, рядом с пузырьками, сделать выноски. В Word 2013 нажимаем на пузырек. Справа от диаграммы появится значок «Плюс».

Читать еще: Как сделать смузи дома

Нажимаем на него. В появившемся окне можно поставить галочку у слов «Подписи данных». На пузырьках появятся числа из столбца C. Если мы нажмем на стрелку у функции «Подписи данных», то появится следующее окно. В нем нажимаем на функцию «Дополнительные параметры».

Появится диалоговое окно «Формат подписи данных». Здесь мы можем изменить числа на пузырьках. Например, нам нужно, чтобы на пузырьках были написаны проценты из столбца D. Убираем галочку у слов «значения Y», ставим галочку у слов «размеры пузырьков».

Можно оставить несколько галочек, чтобы были размещены на пузырьках данные из нескольких столбцов. «Положение метки» — мы поставили «в центре». Можно установить сбоку, с выноской, т.д. В Word 2007, чтобы подписать пузырьки, нажимаем на любой пузырек диаграммы. На закладке «Макет» В разделе «Подписи» нажимаем на кнопку «Подписи данных».

Появится диалоговое окно «Формат подписи данных». В разделе этого окна «Параметры подписи» ставим и убираем нужные галочки.

Как подписать оси на графике, диаграмме Excel. Изменить название диаграммы, осей можно в самой диаграмме.

Нажимаем на поле подписи диаграммы и пишем другое название. Если нет поля для названия оси (как в нашем пример), то добавляем это поле и пишем название. На закладке «Конструктор» в разделе «Макеты диаграмм» нажимаем на кнопку «Добавить элемент диаграммы».

В появившемся окне выбираем функцию «Название осей», затем выбираем нужную функцию. Здесь появилось поле для названия вертикальной оси. Чтобы вставить поле для названия оси в Word 2007 на закладке «Макет» в разделе «Подписи» нажимаем на кнопку «Названия осей».

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

Чтобы подписать, изменить имеющееся название диаграммы, нажимаем на него мышкой и исправляем. Чтобы вставить на диаграмму поле для названия диаграммы, поступаем, как при добавлении подписи оси (описано выше).

Только в диалоговом окне выбираем функцию «Название диаграммы». Все перечисленные выше и другие функции можно найти на закладке «Работа с диаграммами». Эта закладка появляется, когда щелкаем мышкой по диаграмме.

На этой закладке «Работа с диаграммами» находится три вкладки с различными функциями. Как линейчатым графиком сравнить две позиции, построив диаграмму Торнадо, смотрите в статье «Как сделать сравнительную диаграмму в Excel».

У нас есть большая таблица с данными. Нам нужно добавить, к имеющимся данным в каждую ячейку дополнительные данные, слова, знаки, числа, т. д.