Excel: как создать пузырьковую диаграмму с метками
В этом руководстве представлен пошаговый пример того, как создать следующую пузырьковую диаграмму с метками в Excel:
Шаг 1: введите данные
Во-первых, давайте введем в Excel следующие данные, которые показывают различные атрибуты для 10 разных баскетболистов:
Шаг 2: Создайте пузырьковую диаграмму
Затем выделите ячейки в диапазоне B2:D11.Затем щелкните вкладку « Вставка » на верхней ленте, а затем выберите параметр « Пузырьковая диаграмма » в группе « Диаграммы »:
Автоматически будет создана следующая пузырьковая диаграмма:
По оси X отображаются очки , по оси Y — передачи , а размер каждого пузырька — количество подборов .
Однако трудно понять, какие пузыри представляют каких игроков, потому что нет меток.
Шаг 3: Добавьте ярлыки
Чтобы добавить метки к пузырьковой диаграмме, щелкните в любом месте диаграммы, а затем щелкните зеленый значок плюса « + » в правом верхнем углу.
Затем щелкните стрелку рядом с Метки данных , а затем выберите Дополнительные параметры в раскрывающемся меню:
На панели, которая появляется в правой части экрана, установите флажок рядом с « Значение из ячеек » в группе « Параметры метки »:
В появившемся новом окне выберите A2:A11 в качестве диапазона ячеек, который содержит наши метки:
Затем нажмите « ОК » и на панели « Формат меток данных » в правой части экрана снимите флажок рядом с « Значение Y » и выберите «По центру» в качестве положения метки.
Следующие метки будут автоматически добавлены к пузырьковой диаграмме:
Шаг 4. Настройте пузырьковую диаграмму
Наконец, не стесняйтесь нажимать на отдельные элементы диаграммы, чтобы добавить заголовок, добавить метки осей, изменить размер шрифта меток и удалить линии сетки:
Окончательная пузырьковая диаграмма легко читается, и мы точно знаем, какие пузырьки представляют каких игроков.
Дополнительные ресурсы
В следующих руководствах объясняется, как создавать другие распространенные визуализации в Excel:
Пузырьковая диаграмма в excel как построить
При попытке построить «Пузырьковую» диаграмму на основе сводной таблицы — вылетает ошибка: «Невозможно изменить тип диаграммы. Выберите другой тип диаграммы». MS Excel не умеет строить такие диаграммы на основе сводных таблиц. А что делать если очень нужно?
| Файл | Описание | Размер файла: | Скачивания |
|---|---|---|---|
| Пример | 106 Кб | 1237 |
Делается все очень просто. Берем сводную таблицу, выделяем необходимые столбцы и строки, в главном меню переходим во вкладку ЁXCEL, нажимаем кнопку «Диаграммы», в выпавшем меню выбираем команду «Матричная диаграмма»:
В открывшемся диалоговом окне устанавливаем необходимые параметры и нажимаем «ОК»:
Диаграмма будет построена. Если необходимо отредактируйте размер «пузырьков»:
Это конечно не полноценная сводная диаграмма — у нее нет привязки к сводной таблице, но за то ее можно быстро удалить и перестроить.
Пузырьковая диаграмма (Bubble Chart)
Большинство из тех, кто хоть раз строил графики в 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 эту недостающую функцию.
После установки у вас появится новая вкладка (в старых версиях Excel — панель инструментов) XY Chart Labels:
Выделив пузырьки и воспользовавшись кнопкой Add Labels можно быстро и удобно добавить подписи сразу ко всем пузырькам в диаграмме, просто задав диапазон ячеек с текстом для подписей:
Способ 3. Excel 2013
В новой версии Microsoft Excel 2013 появилась наконец-таки возможность добавлять подписи к элементам данных диаграммы из любых произвольно выделенных ячеек. Дождались 🙂
Пузырьковая диаграмма
Пузырьковая диаграмма – это многомерный график, который находится на пересечении диаграммы рассеяния и Диаграмма с пропорциональными областями.
Так же, как и диаграмма рассеяния, пузырьковая диаграмма использует декартову систему координат для нанесения на график точек, где оси X и Y – это отдельные переменные. Однако, в отличие от диаграммы рассеяния, каждая точка имеет собственное название или является собственной категорией (подписанной рядом или в сопроводительном тексте). Соответственно, каждая точка представляет областью своей окружности третью переменную. На графике можно использовать цвета для разграничения категорий или для введения дополнительной переменной. Время отображается либо в качестве одной из переменных на осях, либо через динамическое изображение изменяющихся во времени данных.
Пузырьковые диаграммы, как правило, используются для сравнения и отображения взаимосвязей между отмеченными/классифицированными окружностями с помощью определения их местоположения и пропорций. Общий вид пузырьковых диаграмм можно использовать для анализа паттернов/корреляций.
Слишком большое количество пузырьков может сделать график плохо читаемым, поэтому пузырьковые диаграммы имеют ограничения по объему отображаемых данных. Это в некоторой степени корректируется за счет использования интерактивных средств, как-то: нажатие или наведение мыши на пузырьки для отображения скрытой информации, а также функция реорганизации или фильтрации категорий.
Подобно диаграммам с областями размеры окружностей необходимо обозначать на основе площади окружностей, а не их радиуса или диаметра. Иначе размер окружностей не только будет меняться в геометрической прогрессии, но и послужит причиной неверного толкования данных зрительной системой человека.