Как разделить цвета столбцов для положительных и отрицательных значений в гистограмме?
При работе с данными в Excel часто возникает необходимость визуализировать прибыль и убытки, доходы и расходы или любые другие противоположные значения с помощью гистограмм. Однако по умолчанию Excel отображает все столбцы одним и тем же цветом — независимо от того, положительное значение или отрицательное. Такой единообразный стиль затрудняет мгновенное распознавание тенденций и скрывает ключевые различия на первый взгляд.
Чтобы повысить наглядность и придать диаграмме профессиональный вид, рекомендуется использовать разные цвета для положительных и отрицательных столбцов. В этом руководстве подробно описаны несколько эффективных способов разделения цветов для положительных и отрицательных значений в гистограммах Excel. Вы научитесь создавать визуально интуитивные и легко читаемые диаграммы. Каждый метод сопровождается пошаговым объяснением, примерами применения, преимуществами и ограничениями, а также практическими советами по избежанию типичных ошибок.
Разделение цветов положительных и отрицательных столбцов в гистограмме / Гистограмма
Макрос VBA: автоматическая раскраска столбцов по знаку значений
Формула Excel: использование вспомогательных столбцов и рядов данных для двухцветных Гистограмма
Разделение цветов положительных и отрицательных столбцов в гистограмме / Гистограмма
Excel предлагает встроенную функцию «Инвертировать при отрицательном значении», которая позволяет быстро и легко визуально различать положительные и отрицательные столбцы на гистограммах. Этот метод идеально подходит для небольших и средних наборов данных и не требует ни формул, ни скриптов. Если ваша цель — простое и чёткое разделение с минимальными усилиями по настройке, рекомендуем использовать именно этот подход.
1. Щёлкните правой кнопкой мыши по столбцу ряда данных на диаграмме и выберите в контекстном меню пункт «Формат ряда данных», как показано на этом снимке экрана:
2. В диалоговом окне «Формат ряда данных» выберите раздел «Заливка» на левой панели. Затем установите флажок «Инвертировать при отрицательном значении» — это позволит Excel распознавать и визуально разделять положительные и отрицательные значения. См. снимок экрана:
3. Затем задайте разные цвета для столбцов данных: включите переключатель «Сплошная заливка», после чего выберите нужные цвета для положительных и отрицательных значений в разделе Цвет заполнения. Так вы полностью настроите обе категории. См. снимок экрана:
4. После выбора цветов нажмите кнопку «Закрыть», чтобы выйти. Теперь ваша диаграмма автоматически отобразит положительные и отрицательные столбцы выбранными вами цветами, что значительно повысит наглядность при анализе и составлении отчётов.
Примечания и советы:
1. В Excel 2013 и более поздних версиях после щелчка правой кнопкой мыши по столбцам данных и выбора пункта «Формат ряда данных» интерфейс открывается в виде боковой панели. Здесь нажмите значок «Заливка и границы», установите флажок «Инвертировать при отрицательном значении», затем укажите цвета для положительных и отрицательных столбцов в разделе «Сплошная заливка». См. снимок экрана для справки:
2. Этот подход работает как для Круговой диаграммы, так и для Гистограммы.
3. Если у вас диаграмма с несколькими рядами данных или требуется индивидуальная раскраска столбцов в соответствии с определённой логикой (выходящей за рамки положительных и отрицательных значений), могут понадобиться Дополнительные параметры, такие как VBA или вспомогательные столбцы.
4. Если вы обнаружите, что опция «Инвертировать при отрицательном значении» недоступна, убедитесь, что вы редактируете Одну серию, а не составную диаграмму, поскольку эта функция не поддерживается в составных гистограммах/Гистограмме.
Это решение предпочтительно благодаря своей простоте и быстрому результату, особенно для гистограмм с одним рядом данных или Гистограмма. Для большего контроля или автоматизированных пакетных изменений рассмотрите следующие продвинутые методы.
Макрос VBA: автоматическая раскраска столбцов по знаку значений
Если вы хотите управлять раскраской If you need to set bar colors according to value (positive or negative) программно или работать с диаграммами, содержащими несколько рядов данных или более сложную логику раскраски, можно использовать макрос 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)). Чтобы получить точные значения RGB для стандартной палитры Excel, воспользуйтесь средством выбора цвета. - Это решение работает как для групповых гистограмм, так и для Гистограмма, с одним или несколькими рядами данных. Для составных диаграмм могут потребоваться дополнительные изменения.
- Если макрос не даёт никакого эффекта, дважды проверьте, что вы активно выбрали диаграмму перед запуском кода.
- Для массовой автоматизации сразу для нескольких диаграмм или динамических наборов данных можно организовать цикл по всем диаграммам, изменив код.
Этот подход на основе VBA идеален, если вы регулярно создаёте сложные отчёты или нуждаетесь в высоко настраиваемых правилах раскраски, которые не поддерживаются стандартными возможностями диаграмм. Однако настройки безопасности макросов могут ограничивать выполнение кода, поэтому при появлении запроса разрешите выполнение макросов.
Формула Excel: использование вспомогательных столбцов и рядов данных для двухцветных Гистограмма
Другое практичное решение — особенно полезное, если вы хотите полностью контролировать назначение цветов вручную или визуализировать не только положительные и отрицательные значения, но и, например, различные оттенки в зависимости от величины, — состоит в создании двух вспомогательных столбцов с помощью формул Excel. Один столбец содержит только положительные значения, другой — только отрицательные. Каждый из них затем строится как отдельный ряд данных, и для каждого ряда задаётся уникальный цвет. Этот метод хорошо работает даже при необходимости применения продвинутой логики Использовать условное форматирование или отображении Метки данных только для определённых столбцов.
Ниже приведено описание организации данных и настройки диаграммы:
Предположим, что исходные данные расположены в столбце B (диапазон B2:B11).
1. Добавьте два вспомогательных столбца рядом с исходными данными: «Положительные» и «Отрицательные».
2. В первую ячейку нового вспомогательного столбца «Положительные» (например, C2) введите следующую формулу:
=IF(B2>,=0,B2,NA())Эта формула копирует значение из ячейки B2, если оно положительное или равно нулю; если же значение отрицательное, возвращается НА(), которое диаграммы Excel игнорируют (столбец не отображается).
3. В первую ячейку вспомогательного столбца «Отрицательные» (например, D2) введите следующую формулу:
=IF(B2<,0,B2,NA())Эта формула отображает отрицательные значения и скрывает неотрицательные.
4. Протяните формулы в обоих вспомогательных столбцах до конца всего диапазона, охватывающего ваш набор данных.
5. Удерживая клавишу Ctrl, выделите метки категорий и оба вспомогательных столбца, затем вставьте кластеризованную гистограмму или гистограмму. Вы увидите два ряда данных: один — для положительных чисел (отображающий столбцы только с положительными значениями), другой — для отрицательных. Столбцы в этих рядах будут окрашены в разные цвета.
Советы и примечания:
- Использование
НА()гарантирует отсутствие пустых или невидимых столбцов в нежелательных позициях, обеспечивая визуальную чёткость графика. - Этот подход можно обобщить для Дополнительное условие — например, раскрашивать столбцы на основе пользовательских пороговых значений или диапазонов, дополнительно увеличив количество вспомогательных столбцов и условную логику.
- Если вам нужно обновлять диаграмму автоматически, обязательно расширьте диапазон диаграммы или преобразуйте данные в таблицу 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек