Пузырьковая диаграмма в excel как построить
Большинство из тех, кто хоть раз строил графики в Microsoft Excel или PowerPoint, замечали необычный и забавный тип диаграмм - пузырьковые (bubble chart). Многие видели их в чужих файлах или презентациях. Однако, в 99 случаев из 100, при попытке самостоятельно построить такую диаграмму впервые, пользователи сталкиваются с рядом неочевидных сложностей. Обычно Excel или отказывается ее создавать вообще, или создает, но совершенно непонятного вида, без подписей и наглядности.
Давайте разберемся в этой теме.
Что такое пузырьковая диаграмма
По горизонтальной оси Х откладывается средний годовой доход на душу населения в 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 для Microsoft 365 Excel 2021 Power View удаляется 12 октября 2021 г. В качестве альтернативы вы можете использовать интерактивный визуальный эффект, предоставляемый Power BI Desktop,который можно скачать бесплатно. Вы также можете легко импортировать книги Excel в Power BI Desktop.
Точечная и пузырьковая диаграммы — это отличный способ отображения большого объема связанных данных в одной диаграмме. На точечная диаграмма на оси X отображается одно числическое поле, а на оси Y — другое, что делает отображение связи между двумя значениями для всех элементов диаграммы.
На пузырьковой диаграмме третье числическое поле управляет размером точек данных.
Создание пузырьковой диаграммы
Создайте таблицу со значением одной категории.
Совет: Выберите категорию, в которую не вмеется слишком много значений. Если категория имеет более 2000 значений, обратите внимание на то, что на диаграмме отображается представительная выборка, а не все категории. На самом деле, трудно увидеть отдельные пузырьки, если их много.
Добавьте в таблицу два или три числимые значения.
При значении двух значений вы получаете точечная диаграмма с небольшими кругами одинакового размера.
С тремя значениями вы получаете пузырьковую диаграмму. Каждый пузырек имеет другой цвет, а размер пузырька иллюстрирует третье значение.
Чтобы преобразовать таблицу в диаграмму, на вкладке Конструктор сформируем:
В Power View в Excel щелкните стрелку под кнопкой Другая диаграмма и выберите точечная диаграмма.
В Power View в SharePoint нажмите кнопку Точечная.
Power View автоматически помещает категорию в поле Сведения, а два числовых значения в поля Значение X и Значение Y.
Отслеживайте данные со временем
Чтобы просмотреть изменения данных с течением времени, можно добавить измерение времени к точечная и пузырьковая диаграммы с "осью воспроизведения".
Выберите пузырьковую или точечная диаграмму и перетащите поле со значениями времени в поле Ось воспроизведения.
В результате в нижнюю часть диаграммы будут добавлены кнопка воспроизведения и временная линия.
При нажатии кнопки воспроизведения пузырьки будут увеличиваться, увеличиваться и уменьшаться, чтобы показать, как значения меняются в зависимости от оси воспроизведения. Вы можете приостановить в любой момент, чтобы изучить данные более подробно. Щелкнув пузырек на диаграмме, вы увидите его историю в следе, за которым он следует с течением времени.
Цветовые категории для точековых и пузырьковых диаграмм
Вы можете добавить поле в поле Цвет для точечная или пузырьковой диаграммы и по-разному раскрасить пузырьки или точечная точка в соответствии с различными значениями в этом поле, переопределив цвета пузырьков. Например, если добавить поле "Категория" в пузырьковую диаграмму продуктов, пузырьки будут назначаться разными цветами в зависимости от того, являются ли они фруктами, овощи, напитки или напитки.
Поле не может быть вычисляемой и может иметь не более двадцати экземпляров.
Примечание: Вы можете щелкнуть цвет в легенде, чтобы выделить все пузырьки для этого цвета, но трассировка не для всех пузырьков этого цвета. Чтобы отследить каждый пузырек, необходимо щелкнуть его по отдельности. Вы можете выбрать несколько пузырьков одновременно, нажав CTRL+щелчок.
Как построить пузырьковую диаграмму в Excel?
Выделяем диапазон с данными B1:D5 и на панели вкладок выбираем Вставка -> Диаграмма -> Точечная -> Пузырьковая:
По умолчанию при построении диаграммы пузырьки раскрашиваются в один и тот же цвет.
Если такой вариант не подходит и хочется сделать цветовое разделение между пузырьками, то можно либо вручную поменять цвет для каждого пузырька отдельно, либо установить галочку напротив поля Разноцветные точки в настройке формата данных:
После настройки цветов пузырьков диаграмма принимает следующий вид:
Добавление подписей
Для добавления имен категорий можно добавить произвольную подпись, а затем вручную поменять подпись для каждого пузырька. Достаточно щелкнуть мышкой на поле с подписью и в поле формулы прописать новую.
Однако ручное добавление подписи придется делать для каждого ряда данных, что при большом количестве данных достаточно неудобно и увеличивает вероятность совершить где-нибудь ошибку.
Если же у вас версия Excel 2013 и старше, то подобной проблемы не возникает, так как появляется дополнительный вариант подписи Значения из ячеек, который позволяет добавить подпись из произвольного диапазона ячеек.
Нажимаем правой кнопкой мыши на ряд с пузырьками и выбираем Добавить подписи данных, а затем в настройках формата указываем нужный нам диапазон ячеек A2:A5 (в котором содержатся имена категорий):
Можно выбрать как какой-либо один из вариантов подписи, так и комбинировать различные варианты, в данном случае добавим на график наименование продукта и долю, занимаемому им на рынке:
В завершении построения также можно добавить названия осей, настроить параметры осей и т.д.:
Вторичная круговая диаграмма в Excel применяется для подробной детализации одного из секторов круговой диаграммы.
Данный способ визуализации помогает упростить и детализировать просмотр небольших областей диаграммы.
Предположим, что у нас имеется следующая таблица с данными:
Чтобы построить вторичную круговую диаграмму, выделяем диапазон с данными и на панели вкладок выбираем Вставка -> Диаграмма -> Круговая -> Вторичная круговая:
Получаем диаграмму следующего вида:
Для изменения вида подписи нажимаем правой кнопкой мыши на подпись и выбираем Формат подписи данных.
В открывшемся окне настраиваем формат подписи данных (в данном случае в качестве подписи показываем имя категории и доли):
При добавлении в подпись названия категории рассматриваемый сектор может быть подписан как "Другой":
Щелкаем по подписи правой кнопкой мышки и заменяем слово на нужное название категории:
Настраиваем внешний вид диаграммы на свое усмотрение (настраиваем размер шрифта подписей, выбираем цвет секторов круговой диаграммы и т.д.) и получаем окончательный вариант построения вторичной круговой диаграммы:
Читайте также: