Как изменить значок «Использовать условное форматирование — Установить цвет» в 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Добавьте разные цвета для каждого типа стрелок по своему усмотрению.
 |
 |
Кроме того, вы можете в любой момент дополнительно настроить внешний вид, вручную изменив цвет шрифта стрелок с помощью кнопки Цвет шрифта на вкладке Главная. Это удобно для быстрой корректировки цвета без изменения базовых условных правил.
Следуя этим шагам, вы можете имитировать наборы значков с настраиваемыми цветами, делая свои отчёты более визуально выразительными и лучше соответствующими вашим задачам. Этот обходной путь наиболее подходит, когда требуется полный контроль над цветами значков или необходимо соблюсти эстетические требования отчёта. Однако учтите, что такой подход требует дополнительных столбцов и тщательного управления формулами; выравнивание значков может отличаться от встроенных наборов значков Excel.
Если вам требуется решение, которое обеспечивает более прозрачную работу со встроенными значками, или если вы стремитесь к расширенной настройке (например, использовать собственные изображения или полностью новые значки), обратите внимание на альтернативное решение на VBA, описанное ниже.
Код VBA — создание пользовательского набора значков Использовать условное форматирование (пользовательские изображения или цвета)
Стандартный Excel не позволяет напрямую изменять цвет встроенных наборов значков при использовании условного форматирования. Однако эту ограничение можно обойти с помощью VBA: программно вставлять в ячейки фигуры или изображения — например, цветные значки или специально созданные картинки — в зависимости от значений ячеек. Такой подход особенно эффективен, когда нужно отображать значки любого цвета или стиля, выходя за рамки встроенных возможностей Excel.
1. Щёлкните Инструменты разработчика > Visual Basic, чтобы открыть редактор VBA. В новом окне Microsoft Visual Basic для приложений нажмите Вставка > Модуль и скопируйте приведённый ниже код в новый модуль:
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 Sub2. Затем запустите макрос, нажав F5(или кнопку)
). Когда появится запрос, выберите диапазон, в котором хотите разместить пользовательские значки. Макрос вставит цветные значки с помощью Текстового поля — в зависимости от значения каждой ячейки относительно процентилей группы.
- Если ваши данные изменятся, повторно запустите макрос для обновления или перерисовки значков. Пользователи должны включить макросы, чтобы эта функция работала. Фигуры можно удалить вручную или с помощью кода макроса для повторного применения.
- Имейте в виду: выполнение этого кода заменит ваши значения значками, поэтому обязательно создайте резервную копию данных перед продолжением.
Этот подход на VBA отличается исключительной универсальностью: значки, цвета и фигуры полностью настраиваются, что делает его идеальным выбором для экспертных дашбордов, где фирменные руководства или особенности аудитории требуют строго определённых визуальных сигналов.
Подводя итог, хотя встроенные наборы значков Excel имеют фиксированные цвета, существуют альтернативные методы для применения настраиваемых цветных значков в ваших таблицах. Выберите ручной способ с символами и условным форматированием — он обеспечит полный контроль над компоновкой; используйте VBA, чтобы размещать любые изображения или символы с программируемыми цветами. Каждый подход обладает своими преимуществами и особенностями, позволяя подобрать оптимальное решение для ваших конкретных задач визуализации данных.
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек