Как раскрасить диаграмму в Excel в соответствии с цветами ячеек?
При создании стандартной диаграммы в Excel — например, гистограммы или столбчатой диаграммы — ряды или точки данных автоматически получают цвета по умолчанию, которые могут не совпадать с цветом заливки ячеек в вашем диапазоне данных. Однако во многих случаях, особенно при разработке панелей мониторинга, отчётов или аналитических визуализаций, важно, чтобы столбцы диаграммы точно соответствовали цветам исходных ячеек. Это обеспечивает визуальную согласованность, ускоряет восприятие информации и усиливает группировку категорий, где цвет выступает ключевым признаком. Например, вы можете захотеть, чтобы каждый столбец на диаграмме отражал цветовую маркировку из сводной таблицы, как показано на снимке экрана ниже. К сожалению, Excel не предлагает встроенной функции для автоматического сопоставления цвета заливки ячеек (особенно заданного вручную) с элементами диаграммы. Поэтому подходящий метод зависит от того, как именно задан цвет: вручную, с помощью формулы или условного форматирования. Ниже приведены несколько практических решений, позволяющих эффективно добиться такого соответствия в различных сценариях.
Раскраска диаграммы с одним или несколькими рядами данных в соответствии с цветом ячеек с помощью кодов VBA
Раскраска диаграммы с одним рядом данных в соответствии с цветом ячеек
Если вы хотите, чтобы столбцы диаграммы автоматически наследовали цвета заливки соответствующих ячеек — при условии, что эти цвета заданы вручную (а не с помощью условного форматирования или формул), — вы можете использовать VBA для синхронизации цветов столбцов диаграммы с цветами исходных ячеек. Этот подход особенно эффективен для диаграмм с одним рядом данных, где точное визуальное соответствие играет ключевую роль в обеспечении ясности или соблюдении корпоративных стандартов отчётности.
1. Сначала выделите свои данные и создайте гистограмму или круговую диаграмму. Для этого выделите нужные ячейки и нажмите Вставка > Вставить гистограмму или круговую диаграмму. Вы увидите диаграмму по умолчанию, аналогичную приведённой ниже:
2. Нажмите ALT + F11, чтобы открыть редактор Microsoft Visual Basic для приложений (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. Когда появится запрос, просто нажмите ОК в открывшемся диалоговом окне.
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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек