Как изменить цвет набора значков условного форматирования в Excel?

При работе с наборами значков условного форматирования в Excel вы можете заметить, что каждый значок по умолчанию связан с определенным цветом. Обычно, если значение больше 67% всех значений, значок отображается как зеленая стрелка вверх. Если значение находится между 33% и 67%, отображается желтая горизонтальная стрелка, а значения ниже 33% обозначаются красной стрелкой вниз. Однако эти стандартные цвета значков фиксированы и не могут быть легко изменены с помощью стандартных параметров Excel. Хотите ли вы изменить эти цвета значков, чтобы они лучше соответствовали вашим предпочтениям или стилю отчетов, как показано на скриншотах ниже?

Изменить набор значков условного форматирования

VBA пользовательское условное форматирование набора значков

Изменить набор значков условного форматирования

В настоящее время Excel не предлагает встроенного способа напрямую изменять цвета наборов значков условного форматирования. Однако вы можете использовать креативный обходной путь, вручную вставляя символы (например, стрелки) и затем применяя условное форматирование для изменения их цветов по мере необходимости. Этот гибкий подход позволяет вам подогнать цвета значков под свои предпочтения или стандарты отчетности. Этот метод особенно полезен в информационных панелях, визуальных отчетах или когда корпоративный бренд требует согласования цветов.

Вот как вы можете реализовать этот метод, который не только позволяет настраивать цвета, но также дает возможность выбирать конкретные формы значков, используя символы из шрифтов, таких как Wingdings3:

1. Выберите пустую ячейку рядом со списком данных, например, используйте F2. Введите 67% в F2, затем 33% в F3 и 0% в F4. Это создаст ваши процентильные пороги для условного отображения значков.

2. В ячейке G2 введите следующую формулу для расчета значения выше 67%. (в данном случае C1:C8 представляет собой ваш список значений), затем перетащите маркер заполнения вниз для расчета каждого значения строки.

=PERCENTILE($C$1:$C$8, F2)

3. Далее, в H2 вставьте свой первый значок, нажав Вставка > Символ. В диалоговом окне Символ выберите Wingdings3 в раскрывающемся меню Шрифт, затем выберите и вставьте символ стрелки вверх (например, код символа 232) в H2. Этот символ представляет высокие значения. См. скриншоты:

4. Повторите вышеуказанные шаги, чтобы вставить горизонтальную стрелку в H3 и стрелку вниз в H2 для промежуточных и низких значений соответственно. Проверьте выбор символов, чтобы он соответствовал вашему предполагаемому визуальному дизайну.

5. Теперь в столбце B (рядом со списком значений, начиная с C1) введите эту формулу в B1, чтобы назначить значок на основе каждого значения. После ввода формулы перетащите маркер заполнения вниз, чтобы заполнить формулу вдоль всего списка значений.

=IF(C1="","",IF(C1>=$G$2,$H$2,IF(C1>=$G$3,$H$3,$H$4)))

Совет:

• C1: Ссылка на вашу ячейку со значением.
• G2: это линия значений 67%
• H2: Символ (стрелка), показанный для самых высоких значений.
• G3: это линия значений 33%.
• H3: Стрелка для средних значений.
• G4: это значение ниже 33%.
• h2: Стрелка для низких значений.

6. Выделите диапазон, содержащий стрелки (например, ячейки B1:B8), затем установите шрифт этих ячеек в Wingdings3. Этот шаг гарантирует, что стрелки будут отображаться правильно как символы, а не случайные буквы или символы.

7. Чтобы раскрасить стрелки в соответствии с вашей желаемой цветовой схемой, выберите диапазон значков, перейдите в раздел Главная > Условное форматирование > Новое правило. Это откроет диалоговое окно для создания условий форматирования для ваших пользовательских значков.

8. В диалоговом окне Новое правило форматирования выберите Использовать формулу для определения форматируемых ячеек. Введите эту формулу, чтобы нацелиться на ваши самые высокие значения: =$C1>=$G$2. Нажмите Формат, перейдите на вкладку Шрифт и выберите желаемый цвет для стрелок вверх (например, красный для акцента).

Совет: еще раз проверьте, что ваши абсолютные и относительные ссылки на ячейки верны, чтобы гарантировать применение форматирования так, как задумано, особенно если вы копируете условия в другие диапазоны.

9. Нажмите ОК дважды, чтобы применить новый формат. Теперь ваши стрелки вверх должны отображаться в выбранном цвете.

10. Повторите процесс для горизонтальных и нижних стрелок, используя соответствующие формулы в диалоговом окне Нового правила:

Форматировать горизонтальные стрелки:

=AND($C1<$G$2,$C1>=$G$3)

Форматировать стрелки вниз:

=$C1<$G$3

Добавьте разные цвета для каждого типа стрелки по желанию.

 Кроме того, вы можете дополнительно точно настроить внешний вид, вручную меняя цвет шрифта стрелок в любое время через кнопку Цвет шрифта под вкладкой Главная . Это полезно для быстрой настройки цветов без изменения базовых условий форматирования.

Следуя этим шагам, вы можете имитировать наборы значков с настраиваемыми цветами, помогая вашим отчетам стать более визуально различимыми и лучше подходящими для ваших нужд. Этот обходной путь наиболее подходит, когда вам требуется полный контроль над цветами значков или нужно совпадение с эстетическими требованиями отчета. Однако помните, что этот подход требует дополнительных столбцов и тщательного управления формулами; выравнивание значков может отличаться от стандартных встроенных наборов значков.

Если вам нужна более гладкая работа с встроенными значками или если вы хотите получить расширенную настройку значков (например, использование изображений или полностью новых значков), обратитесь к описанной ниже альтернативе VBA.

Код VBA - Создание пользовательского набора значков условного форматирования (пользовательские изображения или цвета)

Ограничением стандартного Excel является то, что вы не можете напрямую изменить цвет встроенных наборов значков в условном форматировании. Для преодоления этого можно использовать VBA для программной вставки фигур или картинок (например, цветных значков или пользовательских изображений) в ячейки на основе значений ячеек. Этот метод особенно эффективен, когда вы хотите отображать значки в любом цвете или стиле, выходя за рамки встроенных возможностей Excel.

1. Нажмите Инструменты разработчика > Visual Basic, чтобы открыть редактор VBA. В новом окне Microsoft Visual Basic for Applications нажмите Вставка > Модуль, затем скопируйте и вставьте следующий код в новый модуль:

Sub CustomConditionalIcons()
    Dim rng As Range
    Dim cell As Range
    Dim ws As Worksheet
    Dim upIcon As String, midIcon As String, downIcon As String
    Dim upColor As Long, midColor As Long, downColor As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws = ActiveSheet
    Set rng = Application.InputBox("Select data range for custom icons", xTitleId, Selection.Address, Type:=8)
    
    ' Define Unicode symbols to use as icons (can replace with other symbols or images if desired)
    upIcon = ChrW(9650) ' ▲ Up triangle
    midIcon = ChrW(9651) ' △ Mid triangle
    downIcon = ChrW(9660) ' ▼ Down triangle
    
    ' Define colors (RGB)
    upColor = RGB(0, 176, 80) ' Green
    midColor = RGB(255, 192, 0) ' Yellow
    downColor = RGB(255, 0, 0) ' Red
    
    ' Remove existing shapes/icons from the range
    Dim sh As Shape
    For Each sh In ws.Shapes
        If Not Intersect(sh.TopLeftCell, rng) Is Nothing Then
            If Left(sh.Name, 13) = "CustomIconSet" Then
                sh.Delete
            End If
        End If
    Next
    
    ' Loop through cells and add custom icons
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            Dim percentile67 As Double, percentile33 As Double
            percentile67 = Application.WorksheetFunction.Percentile(rng, 0.67)
            percentile33 = Application.WorksheetFunction.Percentile(rng, 0.33)
            
            Dim iconText As String
            Dim iconColor As Long
            
            If cell.Value >= percentile67 Then
                iconText = upIcon
                iconColor = upColor
            ElseIf cell.Value >= percentile33 Then
                iconText = midIcon
                iconColor = midColor
            Else
                iconText = downIcon
                iconColor = downColor
            End If
            
            ' Insert icon as a TextBox shape
            Dim iconShape As Shape
            Set iconShape = ws.Shapes.AddTextbox(msoTextOrientationHorizontal, cell.Left + 2, cell.Top + 2, cell.Width - 4, cell.Height - 4)
            iconShape.TextFrame.Characters.Text = iconText
            
            With iconShape.TextFrame2.TextRange.Font
                .Size = cell.Font.Size
                .Fill.ForeColor.RGB = iconColor
                .Name = cell.Font.Name
            End With
            
            iconShape.Name = "CustomIconSet" & cell.Address(0, 0)
            iconShape.Line.Visible = msoFalse
            iconShape.TextFrame.HorizontalAlignment = xlHAlignCenter
            iconShape.TextFrame.VerticalAlignment = xlVAlignCenter
            iconShape.Placement = xlMoveAndSize
        End If
    Next
End Sub

2. Затем запустите макрос, нажав F5 (или нажмите кнопку кнопка). Когда вас попросят указать диапазон, где вы хотите отобразить свои пользовательские значки. Макрос вставит цветные значки, используя текстовые поля, в зависимости от значения каждой ячейки относительно процентилей группы. 

Этот подход VBA чрезвычайно универсален — значки, цвета и фигуры полностью настраиваются, что делает его подходящим для экспертных информационных панелей, где руководства по бренду или аудитория требуют очень специфических визуальных сигналов.

Подводя итог, хотя встроенные наборы значков Excel имеют фиксированные цвета, несколько альтернативных техник позволяют применять настраиваемые, цветные значки в ваших таблицах. Выберите комбинацию ручного символа и условного форматирования для полного контроля над компоновкой; используйте VBA для размещения любого изображения или символа с программируемыми цветами; Каждый подход имеет свои преимущества и особенности, предоставляя вам возможность найти лучший вариант для ваших конкретных потребностей в визуализации данных.

Раскройте магию Excel с Kutools AI

• Умное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это посредством простых команд.
• Пользовательские формулы: Создавайте индивидуальные формулы для оптимизации ваших рабочих процессов.
• Кодирование VBA: Пишите и внедряйте код VBA без особых усилий.
• Интерпретация формул: Легко разбирайтесь в сложных формулах.
• Перевод текста: Преодолейте языковые барьеры в ваших таблицах.

Улучшите возможности Excel с помощью инструментов на базе ИИ. Скачать сейчас и испытайте беспрецедентную эффективность!