Как изменить цвет пузырьковой диаграммы на основе категорий в Excel?
При визуализации данных с помощью пузырьковой диаграммы в Excel часто необходимо использовать цвет для различения различных категорий или групп данных, чтобы улучшить анализ и читаемость. Если вы хотите отобразить пузырьки разного цвета в зависимости от их категорий – как показано на скриншоте ниже – это руководство проведет вас через несколько эффективных решений, включая пошаговые инструкции, объяснение ключевых параметров и советы по устранению распространенных проблем.
Изменение цвета пузырьковой диаграммы на основе категорий
Изменение цвета пузырьковой диаграммы на основе категорий
Excel не позволяет напрямую назначать цвет пузырьковой диаграммы по категориям в одном простом действии. Однако внимательное структурирование данных с использованием дополнительных столбцов и строк поможет вам визуально различать категории с помощью цвета. Этот метод удобен, если у вас есть умеренное количество категорий и требуется гибкость в назначении цветов. Процесс включает подготовку данных таким образом, чтобы Excel рассматривал каждую категорию как отдельную серию данных, позволяя вам вручную или автоматически назначать уникальные цвета. Типичные случаи применения этого подхода включают сегментацию демографии, классификацию рынка или любой анализ, где требуется четкая визуальная группировка. Обратите внимание, что при большом количестве категорий или частых обновлениях поддержание такой структуры может стать сложным; в этом случае автоматизация с помощью VBA (см. ниже) может оказаться более эффективной.
Выполните следующие шаги для ручного назначения цветов пузырьков по категориям:
1. Скопируйте значения оси X и вставьте их в пустой столбец рядом с вашим исходным набором данных. Он будет использоваться как вспомогательный столбец для переформатирования данных для визуализации.
2. В строке над вновь организованными данными введите имя каждой категории, соответствующей данным, которые будут находиться под ней. Оставьте пустую ячейку между двумя разными категориями. Это разделение важно, так как оно помогает Excel позже различать категории как новые серии на диаграмме.
3. Под каждым именем категории заполните следующие ячейки меткой Y; под каждой пустой ячейкой введите Bubble. Этот шаг создает правильную структуру, чтобы Excel интерпретировал данные каждой категории отдельно для построения пузырьков с разными цветами.
4. В ячейке сразу под Y меткой для первой категории введите следующую формулу (предполагая, что ваш исходный столбец «Категория» находится в A, значения «Y» — в C, а вспомогательные заголовки — в F1, G1 и т. д. Настройте диапазоны по необходимости):
=ЕСЛИ($A3=F$1;$C3;НД())
Эта формула проверяет, совпадает ли категория в строке с заголовком текущего столбца. Если да, она возвращает значение Y, иначе возвращает #Н/Д (так Excel игнорирует его). Перетащите эту формулу по всему диапазону под всеми Y заголовками ячеек для каждой категории.
Объяснение параметров:
$A3: Ячейка, содержащая категорию текущей строки.
F$1: Имя категории в строке заголовка.
$C3: Значение Y из исходной строки данных.
Совет: Дважды проверьте, что ваши ссылки используют правильные смешанные/абсолютные стили ($A3, F$1, $C3), как показано, иначе результаты могут быть не согласованы при копировании формулы.
5. Далее, в ячейке под заголовком Bubble (непосредственно справа от предыдущей формулы столбца), введите эту формулу и перетащите ее вниз настолько, насколько это необходимо: =ЕСЛИ(ЕНЧ(F3);НД();$D3)
Она проверяет, является ли ячейка слева (F3) #Н/Д; если да, формула возвращает #Н/Д, иначе она возвращает значение размера пузырька.
Объяснение параметров:
F3: Ячейка с выходными данными предыдущей формулы Y.
$D3: Исходное значение 'Bubble' из ваших основных данных.
Совет: Постоянное использование $ для фиксированных ссылок гарантирует, что ваша формула останется корректной при копировании вправо и вниз.
Возможная ошибка: Если вы видите несогласованные данные или неправильно расположенные пузырьки, повторно проверьте, соответствуют ли вспомогательные столбцы и заголовки категорий вашему расположению данных.
6. После применения всех формул и отображения результатов во вспомогательной области выберите новую структуру данных (включая названия категорий, значения Y и значения пузырьков). Затем перейдите в Вставка вкладку, кликните Другие диаграммы, и выберите Bubble. Выберите предпочитаемый стиль пузырьковой диаграммы. В Excel 2013 или выше, перейдите в Вставка > Вставить точечную (X Y) или пузырьковую диаграмму > Bubble.
Примечание: Каждая отдельная категория, организованная как новая серия в ваших данных, теперь будет отображать свои пузырьки в другом цвете по умолчанию. При желании вы можете вручную изменить цвет каждой серии, щелкнув правой кнопкой мыши на пузырьках и выбрав Формат ряда данных > Заливка. Для множества категорий рекомендуется использовать четкую палитру цветов для лучшей ясности.
Теперь ваша диаграмма визуально различает категории по цвету пузырьков, что делает легче определить тренды, группы или выбросы одним взглядом. Этот метод рекомендуется для малых и средних наборов данных или когда вам нужен точный контроль над структурой и внешним видом данных.
Если вас интересует динамическое изменение цветов на основе значений, а не категорий, вы можете обратиться к этому подробному руководству: Как изменить цвет диаграммы на основе значений в Excel.
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек