Как раскрасить диаграмму в Excel на основе цвета ячеек?
Когда вы создаете стандартную диаграмму в Excel, например, гистограмму или столбчатую диаграмму, серии данных или точки данных назначаются цветами Excel по умолчанию, которые могут не соответствовать цветам заливки в вашем диапазоне данных. Однако существует множество сценариев — таких как дашборды, отчеты или визуализация данных — где вы хотите, чтобы столбцы диаграммы точно соответствовали цветам, примененным к исходным ячейкам. Это может помочь поддерживать визуальную согласованность, облегчить интерпретацию данных на первый взгляд или усилить группировки категорий, использующие цвет как сигнал значения. Например, вы можете захотеть, чтобы каждый столбец в вашей диаграмме отражал цветовую кодировку, примененную в сводной таблице, как показано на скриншоте ниже. Excel не предоставляет прямой встроенной функции для автоматического сопоставления цветов заливки ячеек (особенно ручных) с элементами диаграммы, поэтому требуются различные методы в зависимости от того, применяется ли цвет ячейки вручную или на основе формулы или правила. Ниже представлено несколько практических решений, которые помогут вам эффективно достичь этого соответствия в различных сценариях.
Раскраска диаграммы с одной или несколькими сериями данных на основе цвета ячеек с помощью кодов VBA
Раскраска диаграммы с одной или несколькими сериями данных на основе цвета ячеек с помощью кодов VBA
Раскраска диаграммы с одной серией данных на основе цвета ячеек
Если вы хотите, чтобы столбцы диаграммы наследовали цвет заливки соответствующих ячеек и ваши цветовые назначения применяются вручную (не через условное форматирование или формулы), вы можете использовать VBA для синхронизации цветов столбцов диаграммы с исходными цветами ячеек. Этот метод особенно полезен для односерийных диаграмм, где визуальное соответствие является ключевым для ясности или стандартов отчетности.
1. Сначала выберите свои данные и создайте гистограмму или столбчатую диаграмму. Для этого выберите соответствующие ячейки и нажмите Вставка > Вставить гистограмму или столбчатую диаграмму. Вы должны увидеть диаграмму по умолчанию, аналогичную скриншоту ниже:
2. Нажмите ALT + F11, чтобы открыть редактор Microsoft Visual Basic for Applications (VBA).
3. В окне VBA нажмите Вставка > Модуль. Затем скопируйте и вставьте следующий код в окно модуля. Этот скрипт обновит каждый столбец диаграммы, чтобы он соответствовал цвету заливки соответствующей ячейки.
Код VBA: Раскраска столбцов диаграммы с одной серией данных на основе цвета ячеек:
Sub ColorChartColumnsbyCellColor()
'Updateby Extendoffice
Dim xChart As Chart
Dim I As Long, xRows As Long
Dim xRg As Range, xCell As Range
On Error Resume Next
Set xChart = ActiveSheet.ChartObjects("Chart 1").Chart
If xChart Is Nothing Then Exit Sub
With xChart.SeriesCollection(1)
Set xRg = ActiveSheet.Range(Split(Split(.Formula, ",")(1), "!")(1))
xRows = xRg.Rows.Count
Set xRg = xRg(1)
For I = 1 To xRows
.Points(I).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(xRg.Offset(I - 1, 0).Interior.ColorIndex)
Next
End With
End Sub
4. После ввода кода нажмите F5, чтобы запустить макрос. Столбцы диаграммы теперь должны отражать цвета заливки исходных ячеек, обеспечивая немедленное визуальное соответствие, как показано на следующем скриншоте:
Этот метод полезен для диаграмм, где цвета заливки ячеек задаются вручную и ожидается частая ручная корректировка. Имейте в виду, что если цвета ячеек изменяются, вам нужно повторно выполнить VBA для обновления диаграммы, поскольку связь не является динамической. Также помните, что нужно сохранить вашу книгу как файл с поддержкой макросов (.xlsm), чтобы код сохранился.
Раскраска диаграммы с несколькими сериями данных на основе цвета ячеек
Если ваша диаграмма содержит несколько серий данных (например, несколько продуктов за определенный период времени или разные категории), вы можете использовать аналогичный подход с использованием VBA для сопоставления каждого сегмента столбца или точки данных с цветом заливки исходной ячейки. Это поможет поддерживать визуальную согласованность ваших отчетов и сделает легким для зрителей перекрестное сравнение данных между листом и диаграммой.
1. Настройте свои данные и создайте многосерийную гистограмму или столбчатую диаграмму, как показано ниже:
2. Нажмите ALT + F11, чтобы открыть редактор VBA.
3. В окне VBA нажмите Вставка > Модуль и вставьте следующий код:
Код VBA: Раскраска столбцов диаграммы с несколькими сериями данных на основе цвета ячеек:
Sub CellColorsToChart()
'Updateby Extendoffice
Dim xChart As Chart
Dim I As Long, J As Long
Dim xRowsOrCols As Long, xSCount As Long
Dim xRg As Range, xCell As Range
On Error Resume Next
Set xChart = ActiveSheet.ChartObjects("Chart 1").Chart
If xChart Is Nothing Then Exit Sub
xSCount = xChart.SeriesCollection.Count
For I = 1 To xSCount
J = 1
With xChart.SeriesCollection(I)
Set xRg = ActiveSheet.Range(Split(Split(.Formula, ",")(2), "!")(1))
If xSCount > 4 Then
xRowsOrCols = xRg.Columns.Count
Else
xRowsOrCols = xRg.Rows.Count
End If
For Each xCell In xRg
.Points(J).Format.Fill.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
.Points(J).Format.Line.ForeColor.RGB = ThisWorkbook.Colors(xCell.Interior.ColorIndex)
J = J + 1
Next
End With
Next
End Sub
4. Запустите этот код, нажав F5. Серии данных вашей диаграммы будут обновлены, чтобы отразить цвета заливки в вашем диапазоне данных, как показано ниже:
- Код ссылается на диаграмму как на Chart1 по умолчанию. Пожалуйста, скорректируйте это, чтобы оно соответствовало фактическому имени вашей диаграммы.
- Этот подход также поддерживает линейчатые диаграммы, а не только гистограммы или столбчатые типы.
- Если вы столкнетесь с какими-либо проблемами (например, отсутствие обновлений или ошибки), проверьте, что серии данных диаграммы и диапазон цветов ячеек совпадают один к одному.
Хотя эта техника дает вам полный контроль и гибкость для данных, окрашенных вручную, она не справляется со случаями, когда цвет генерируется через условное форматирование или автоматически формулами. В таких ситуациях обратитесь к решениям на основе формул и условного форматирования ниже для более динамических вариантов.
Раскраска диаграммы с одной или несколькими сериями данных на основе цвета ячеек с использованием удивительной функции
Пока VBA может синхронизировать цвета диаграммы с заливками ячеек, требуется запуск кода вручную, и некоторые пользователи могут быть неудобны с макросами или запросами безопасности VBA. Если вы ищете более удобный и интерактивный подход, функция Изменить цвет диаграммы в соответствии с цветом ячейки в Kutools для Excel предлагает эффективное решение. Этот инструмент автоматически применяет цвета заливки ячеек к соответствующим элементам диаграммы, будь то одна или несколько серий данных в вашей диаграмме, и работает даже если вы позже обновите цвета ячеек (простое повторное применение обновит сопоставление).
После установки Kutools для Excel действуйте следующим образом:
1. Вставьте диаграмму, которую вы хотите раскрасить. Выберите диаграмму, затем перейдите к Kutools > Диаграммы > Инструменты диаграммы > Изменить цвет диаграммы в соответствии с цветом ячейки, как показано на изображении ниже:
2. При появлении запроса просто нажмите OK в диалоговом окне.
3. Диаграмма немедленно обновится, чтобы соответствовать вашим цветам ячеек, как показано в следующих примерах:
Раскраска диаграммы с одной серией данных на основе цвета ячеек
Раскраска диаграммы с несколькими сериями данных на основе цвета ячеек
Эта функция идеально подходит для тех, кто регулярно должен автоматически сопоставлять цвета диаграммы и хочет универсальное решение независимо от обновлений данных. Она экономит значительное время по сравнению с ручным форматированием или запуском макросов и особенно полезна в совместной среде, где несколько человек редактируют данные или презентации диаграмм.
Загрузите и попробуйте бесплатно Kutools для Excel прямо сейчас!
Больше статей о диаграммах:
- Создание гистограммы поверх другой гистограммы в Excel
- Когда мы создаем группированную гистограмму или столбчатую диаграмму с двумя сериями данных, столбцы двух серий данных будут показаны рядом. Но иногда нам нужно использовать наложенную или перекрывающуюся гистограмму для более четкого сравнения двух серий данных. В этой статье я расскажу, как создать наложенную гистограмму в Excel.
- Копирование формата одной диаграммы на другие в Excel
- Предположим, у вас есть несколько разных типов диаграмм на листе, вы отформатировали одну диаграмму так, как вам нужно, и теперь вы хотите применить это форматирование к другим диаграммам. Конечно, вы можете форматировать каждую вручную одну за другой, но это займет много времени. Есть ли быстрые или удобные способы для вас скопировать формат одной диаграммы на другие в Excel?
- Выделение максимальных и минимальных точек данных на диаграмме
- Если у вас есть столбчатая диаграмма, и вы хотите выделить наибольшие или наименьшие точки данных другими цветами, чтобы они выделялись, как показано на скриншоте ниже. Как можно определить наибольшие и наименьшие значения, а затем быстро выделить точки данных на диаграмме?
- Создание ступенчатой диаграммы в Excel
- Ступенчатая диаграмма используется для показа изменений, происходящих на нерегулярных интервалах, это расширенная версия линейной диаграммы. Но в Excel нет прямого способа создать ее. В этой статье я расскажу, как пошагово создать ступенчатую диаграмму на рабочем листе Excel.
- Создание прогрессивной гистограммы в Excel
- В Excel прогрессивная гистограмма может помочь вам отслеживать прогресс к цели, как показано на скриншоте ниже. Но как можно создать прогрессивную гистограмму на рабочем листе 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек