Как разделить цвета для положительных и отрицательных столбцов в столбчатой/полосовой диаграмме?
При работе с данными в Excel часто возникает необходимость визуализировать как прибыль, так и убытки, доходы и расходы или любые дихотомические значения с помощью столбчатых или полосовых диаграмм. Однако по умолчанию Excel представляет все столбцы в одном цвете независимо от того, являются их значения положительными или отрицательными. Такая однородная окраска затрудняет быстрое различение трендов или интерпретацию ключевых различий на первый взгляд.
Для лучшей ясности и профессиональной визуальной презентации вы можете захотеть установить разные цвета для положительных и отрицательных столбцов в вашей диаграмме. Это руководство всесторонне знакомит с несколькими эффективными методами разделения цветов положительных и отрицательных значений в столбчатых или полосовых диаграммах, помогая вам создавать визуально интуитивно понятные и легко читаемые диаграммы Excel. Каждый метод включает детали, сценарии, где он наиболее применим, потенциальные преимущества и ограничения, а также практические советы, чтобы избежать распространенных ошибок.
Разделение цветов для положительных и отрицательных столбцов в столбчатой/полосовой диаграмме
Макрос VBA: Автоматическая раскраска столбцов по положительным/отрицательным значениям
Формула Excel: Использование вспомогательных столбцов и рядов данных для двухцветной столбчатой диаграммы
Разделение цветов для положительных и отрицательных столбцов в столбчатой/полосовой диаграмме
Excel предоставляет встроенную функцию Инвертировать если отрицательное значение, которая предлагает быстрый и простой подход к визуальному различению положительных столбцов от отрицательных в столбчатых или полосовых диаграммах. Этот метод подходит для небольших или средних наборов данных и не требует формул или скриптов. Если ваша цель - создать простое и четкое разделение с минимальной настройкой, это рекомендуемый подход.
1. Щелкните правой кнопкой мыши по столбцу ряда данных в вашей диаграмме, затем выберите Формат ряда данных из контекстного меню, как показано на этом скриншоте:
2. В диалоговом окне Формат ряда данных выберите Заливка в левой панели. Затем поставьте галочку напротив опции Инвертировать если отрицательное значение. Это позволит Excel распознавать и визуально разделять положительные и отрицательные данные. См. скриншот:
3. Далее примените различные цвета для столбцов данных: отметьте опцию Однотонная заливка, затем выберите предпочтительные цвета для положительных и отрицательных значений в разделе Цвет заливки. Это обеспечивает полную настройку обеих категорий. См. скриншот:
4. После выбора цветов нажмите Закрыть, чтобы завершить. Ваша диаграмма теперь автоматически покажет положительные и отрицательные столбцы используя выбранные вами цвета, делая различия намного более четкими для анализа и отчетности.
Примечания и Советы:
1. В Excel 2013 и выше после щелчка правой кнопкой мыши по столбцам данных и выбора Формат ряда данных интерфейс открывается как боковая панель. Здесь нажмите значок Заливка и линия, отметьте Инвертировать если отрицательное значение, затем укажите цвета для положительных и отрицательных столбцов в опции Однотонная заливка. См. скриншот для справки:
2. Этот подход работает как для столбчатых диаграмм, так и для полосовых диаграмм.
3. Если у вас есть диаграмма с несколькими рядами данных или вам нужно индивидуально раскрашивать столбцы согласно определенной логике (выходящей за рамки положительных/отрицательных), могут потребоваться продвинутые опции, такие как VBA или вспомогательные столбцы.
4. Если вы заметили, что опция «Инвертировать если отрицательное значение» недоступна (затемнена), дважды проверьте, что вы редактируете один ряд данных, а не стековую диаграмму, поскольку эта функция не работает со стековыми столбчатыми/полосовыми диаграммами.
Это решение ценится за свою простоту и быстрые результаты, особенно для одно-рядных столбчатых или полосовых диаграмм. Для большего контроля или автоматических изменений в партии рассмотрите следующие продвинутые методы.
Макрос VBA: Автоматическая раскраска столбцов по положительным/отрицательным значениям
Если вам необходимо программно установить цвета столбцов в зависимости от значений (положительных или отрицательных), или вы хотите работать с диаграммами, содержащими несколько рядов данных или более сложной цветовой логикой, вы можете использовать макрос VBA. Этот метод обеспечивает гибкость для пакетной обработки и последовательного форматирования, особенно если вы регулярно обновляете свои диаграммы или управляете большими наборами данных.
1. Нажмите Alt + F11, чтобы открыть редактор Visual Basic for Applications. В окне VBA нажмите Вставка > Модуль, затем скопируйте и вставьте следующий код в новый модуль:
Sub ColorBarsPositiveNegative()
Dim cht As Chart
Dim srs As Series
Dim iPoint As Integer
Dim vValue As Variant
Dim posColor As Long
Dim negColor As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
' Set your preferred RGB colors below
posColor = RGB(91, 155, 213) ' Blue for positive
negColor = RGB(192, 80, 77) ' Red for negative
' Use currently selected chart
If ActiveChart Is Nothing Then
MsgBox "Select a chart first.", vbExclamation, xTitleId
Exit Sub
End If
Set cht = ActiveChart
For Each srs In cht.SeriesCollection
For iPoint = 1 To srs.Points.Count
vValue = srs.Values(iPoint)
If vValue >= 0 Then
srs.Points(iPoint).Format.Fill.ForeColor.RGB = posColor
Else
srs.Points(iPoint).Format.Fill.ForeColor.RGB = negColor
End If
Next iPoint
Next srs
End Sub2Закройте редактор VBA. Вернитесь в вашу рабочую книгу Excel, выберите вашу диаграмму, затем нажмите Alt + F8 чтобы открыть диалоговое окно «Макрос». Выберите ColorBarsPositiveNegative и нажмите ВыполнитьМакрос пройдет по всем столбцам и автоматически применит синий цвет для положительных и красный для отрицательных значений.
Советы и Примечания:
- Вы можете настроить
posColorиnegColorна любой цвет по вашему выбору, используя функциюRGB(RGB(R,G,B)). Для стандартной палитры Excel обратитесь к цветовому выбору для точных значений RGB. - Это решение работает как для кластеризованных столбчатых и полосовых диаграмм, для одного или нескольких рядов. Для стековых диаграмм могут потребоваться дополнительные изменения.
- Если макрос не производит никакого эффекта, дважды проверьте, что вы активно выбрали диаграмму перед запуском кода.
- Для массовой автоматизации по нескольким диаграммам или динамическим наборам данных вы можете проходить циклом через все диаграммы, модифицируя код.
Этот подход с использованием VBA идеален, если вы регулярно создаете сложные отчеты или требуете высокой настраиваемости правил окрашивания, которые не поддерживаются стандартными параметрами диаграмм. Однако настройки безопасности макросов могут ограничивать выполнение кода, поэтому включите макросы, если будет предложено.
Формула Excel: Использование вспомогательных столбцов и рядов данных для двухцветной столбчатой диаграммы
Еще одно практичное решение — особенно полезное, если вы хотите иметь полный ручной контроль над назначением цветов или хотите визуализировать больше, чем просто положительные против отрицательных значений (например, разные оттенки для величины) — это создание двух вспомогательных столбцов с использованием формул Excel. Один столбец представляет только положительные значения, другой — только отрицательные значения. Каждый из них затем строится как собственный ряд данных, и уникальные цвета устанавливаются для каждого ряда. Этот метод хорошо работает даже тогда, когда вам нужно применять продвинутую условную логику форматирования или включать метки данных только на определенные столбцы.
Ниже описано, как можно организовать ваши данные и настроить вашу диаграмму:
Предположим, ваши исходные значения находятся в столбце B (B2:B11).
1. Вставьте два вспомогательных столбца рядом с вашими исходными данными: «Положительные» и «Отрицательные».
2. В новом вспомогательном столбце «Положительные» введите следующую формулу в первую ячейку (например, C2):
=IF(B2>=0,B2,NA())Эта формула переносит значение из B2, если оно положительно или равно нулю; если оно отрицательное, она возвращает NA(), которое графики Excel будут игнорировать (не отображая столбец).
3. Во вспомогательном столбце «Отрицательные» введите следующую формулу в первую ячейку (например, D2):
=IF(B2<0,B2,NA())Эта формула отображает отрицательные значения и скрывает неотрицательные.
4. Перетащите формулы в обоих вспомогательных столбцах вниз по всему диапазону, чтобы соответствовать вашему набору данных.
5. Удерживая клавишу Ctrl, выберите ваши метки категорий и оба вспомогательных столбца, затем вставьте кластеризованную столбчатую или полосовую диаграмму. Вы увидите два ряда данных: один для положительных чисел (показывающий столбцы только для положительных значений), один для отрицательных чисел, оба с двумя разными цветами столбцов.
Советы и Примечания:
- Использование
NA()гарантирует, что не будет пустых/нулевых столбцов в нежелательных позициях, сохраняя визуальную ясность графика. - Этот подход может быть обобщен для дополнительных условий — например, раскраска столбцов на основе пользовательских порогов или диапазонов значений путем дальнейшего расширения числа вспомогательных столбцов и условной логики.
- Если вам нужно автоматически обновлять вашу диаграмму, обязательно расширьте диапазон диаграммы или преобразуйте ваши данные в таблицу 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек