Условное форматирование Excel
Условное форматирование Excel позволяет определять правила, определяющие форматирование ячеек.
Например, вы можете создать правило, выделяющее ячейки, соответствующие определенным критериям. Примеры включают:
- Числа, попадающие в определенный диапазон (например, меньше 0).
- 10 первых пунктов списка.
- Создание «тепловой карты».
- «Формульные» правила практически для любого условного форматирования.
В Excel условное форматирование можно найти на ленте в разделе Главная> Стили (ALT> H> L).
Чтобы создать собственное правило, нажмите «Новое правило», и откроется новое окно:
Условное форматирование в VBA
Доступ ко всем этим функциям условного форматирования можно получить с помощью VBA.
Обратите внимание, что при настройке условного форматирования из кода VBA новые параметры появятся в окне условного форматирования внешнего интерфейса Excel и будут видны пользователю. Пользователь сможет редактировать или удалять их, если вы не заблокировали рабочий лист.
Правила условного форматирования также сохраняются при сохранении рабочего листа.
Правила условного форматирования применяются конкретно к определенному листу и определенному диапазону ячеек. Если они нужны где-то еще в книге, то они также должны быть настроены на этом листе.
Практическое использование условного форматирования в VBA
У вас может быть большой кусок необработанных данных, импортированных на ваш рабочий лист из файла CSV (значения, разделенные запятыми), или из таблицы или запроса базы данных. Это может перетекать в информационную панель или отчет с изменением чисел, импортированных из одного периода в другой.
Если число изменяется и выходит за пределы допустимого диапазона, вы можете выделить это, например, цвет фона ячейки красный, и вы можете сделать это, настроив условное форматирование. Таким образом, пользователь будет немедленно привлечен к этому номеру и сможет выяснить, почему это происходит.
Вы можете использовать VBA для включения или выключения условного форматирования. Вы можете использовать VBA, чтобы очистить правила для диапазона ячеек или снова включить их. Может возникнуть ситуация, когда существует вполне веская причина для необычного числа, но когда пользователь представляет панель управления или отчет на более высокий уровень управления, он хочет иметь возможность убрать «тревожные звонки».
Кроме того, в необработанных импортированных данных вы можете выделить, где числа смехотворно велики или смехотворно малы. Импортируемый диапазон данных обычно имеет разный размер для каждого периода, поэтому вы можете использовать VBA для оценки размера нового диапазона данных и вставки условного форматирования только для этого диапазона.
У вас также может быть ситуация, когда есть отсортированный список имен с числовыми значениями для каждого, например. зарплата сотрудника, экзаменационные отметки. При условном форматировании вы можете использовать градуированные цвета для перехода от самого высокого до самого низкого, что выглядит очень впечатляюще для целей презентации.
Однако список имен не всегда будет статическим по размеру, и вы можете использовать код VBA для обновления шкалы градуированных цветов в соответствии с изменениями размера диапазона.
Простой пример создания условного формата для диапазона
В этом примере настраивается условное форматирование для диапазона ячеек (A1: A10) на листе. Если число в диапазоне от 100 до 150, то цвет фона ячейки будет красным, в противном случае он не будет иметь цвета.
| 1234567891011121314 | Sub ConditionalFormattingExample ()‘Определить диапазонDim MyRange As RangeУстановите MyRange = Range («A1: A10»)‘Удалить существующее условное форматирование из диапазонаMyRange.FormatConditions.Delete‘Применить условное форматированиеMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Конец подписки |
Обратите внимание, что сначала мы определяем диапазон MyRange применить условное форматирование.
Затем мы удаляем любое существующее условное форматирование для диапазона. Это хорошая идея, чтобы предотвратить добавление одного и того же правила при каждом запуске кода (конечно, это не подходит для всех обстоятельств).
Цвета представлены числовыми значениями. Для этого рекомендуется использовать обозначение RGB (красный, зеленый, синий). Для этого можно использовать стандартные цветовые константы, например. vbRed, vbBlue, но вы можете выбрать один из восьми цветов.
Доступно более 16,7 млн цветов, и с помощью RGB можно получить доступ ко всем. Это намного проще, чем пытаться вспомнить, какое число соответствует какому цвету. Каждый из трех номеров цветов RGB составляет от 0 до 255.
Обратите внимание, что параметр «xlBetween» является включительным, поэтому значения ячеек 100 или 150 будут удовлетворять условию.
Многовариантное форматирование
Вы можете настроить несколько условных правил в пределах своего диапазона данных, чтобы все значения в диапазоне охватывались разными условиями:
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Создать объект диапазонаУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Добавить первое правилоMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Добавить второе правилоMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue'Добавить третье правилоMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbYellowКонец подписки |
В этом примере устанавливается первое правило, как и раньше, с красным цветом ячейки, если значение ячейки находится в диапазоне от 100 до 150.
Затем добавляются еще два правила. Если значение ячейки меньше 100, то цвет ячейки синий, а если больше 150, то цвет ячейки желтый.
В этом примере вам нужно убедиться, что охвачены все возможности чисел и что правила не пересекаются.
Если в этом диапазоне находятся пустые ячейки, они будут отображаться синим цветом, потому что Excel по-прежнему считает их имеющими значение меньше 100.
Чтобы решить эту проблему, добавьте еще одно условие в качестве выражения. Это необходимо добавить в качестве первого правила условия в коде. При наличии нескольких правил очень важно установить правильный порядок выполнения, иначе результаты могут быть непредсказуемыми.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Создать объект диапазонаУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Добавить первое правилоMyRange.FormatConditions.Add Тип: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone'Добавить второе правилоMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)'Добавить третье правилоMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlLess, _Formula1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue'Добавить четвертое правилоMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlGreater, _Formula1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)Конец подписки |
При этом используется тип xlExpression, а затем используется стандартная формула Excel, чтобы определить, является ли ячейка пустой, а не числовым значением.
Объект FormatConditions является частью объекта Range. Он действует так же, как коллекция с индексом, начинающимся с 1. Вы можете перебирать этот объект, используя цикл For… Next или For… Each.
Удаление правила
Иногда вам может потребоваться удалить отдельное правило из набора из нескольких правил, если оно не соответствует требованиям к данным.
| 12345678910111213 | Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Создать объект диапазонаУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Добавить первое правилоMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Удалить правилоMyRange.FormatConditions (1) .DeleteКонец подписки |
Этот код создает новое правило для диапазона A1: A10, а затем удаляет его. Вы должны использовать правильный номер индекса для удаления, поэтому проверьте «Управление правилами» в интерфейсе Excel (это покажет правила в порядке выполнения), чтобы убедиться, что вы получили правильный номер индекса. Обратите внимание, что в Excel нет возможности отмены, если вы удаляете правило условного форматирования в VBA, в отличие от того, если вы делаете это через интерфейс Excel.
Изменение правила
Поскольку правила представляют собой набор объектов на основе указанного диапазона, вы можете легко вносить изменения в определенные правила с помощью VBA. Фактические свойства после добавления правила доступны только для чтения, но вы можете использовать метод Modify для их изменения. Доступны для чтения / записи такие свойства, как цвета.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Создать объект диапазонаУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Добавить первое правилоMyRange.FormatConditions.Add Тип: = xlCellValue, оператор: = xlBetween, _Formula1: = "= 100", Formula2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Изменить правилоMyRange.FormatConditions (1) .Modify xlCellValue, xlLess, "10"‘Изменить цвет правилаMyRange.FormatConditions (1) .Interior.Color = vbGreenКонец подписки |
Этот код создает объект диапазона (A1: A10) и добавляет правило для чисел от 100 до 150. Если условие истинно, цвет ячейки меняется на красный.
Затем код изменяет правило на числа меньше 10. Если условие истинно, цвет ячейки теперь меняется на зеленый.
Использование градуированной цветовой схемы
Условное форматирование Excel позволяет использовать градуированные цвета для диапазона чисел, идущих в возрастающем или убывающем порядке.
Это очень полезно, когда у вас есть данные, такие как данные о продажах по географическим регионам, температурам в городах или расстояниям между городами. Используя VBA, вы получаете дополнительное преимущество, заключающееся в возможности выбирать собственную градуированную цветовую схему вместо стандартных, предлагаемых в интерфейсе Excel.
| 1234567891011121314151617181920212223242526272829 | Sub GraduatedColors ()Dim MyRange As Range'Создать объект диапазонаУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Определить тип шкалыMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Выберите цвет для наименьшего значения в диапазонеMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueС MyRange.FormatConditions (1) .ColorScaleCriteria (1) .FormatColor.Color = 7039480.Конец с'Выберите цвет для средних значений в диапазонеMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Value = 50'Выберите цвет для средней точки диапазонаС MyRange.FormatConditions (1) .ColorScaleCriteria (2) .FormatColor.Color = 8711167.Конец с'Выберите цвет для максимального значения в диапазонеMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueС MyRange.FormatConditions (1) .ColorScaleCriteria (3) .FormatColor.Color = 8109667.Конец сКонец подписки |
Когда этот код запускается, он будет градуировать цвета ячеек в соответствии с возрастающими значениями в диапазоне A1: A10.
Это очень впечатляющий способ отображения данных, который наверняка привлечет внимание пользователей.
Условное форматирование значений ошибок
Когда у вас огромный объем данных, вы можете легко пропустить значение ошибки в различных таблицах. Если это будет представлено пользователю без разрешения, это может привести к большим проблемам, и пользователь потеряет уверенность в цифрах. Это использует тип правила xlExpression и функцию Excel IsError для оценки ячейки.
Вы можете создать код, чтобы все ячейки с ошибками имели красный цвет:
| 1234567891011 | Sub ErrorConditionalFormattingExample ()Dim MyRange As Range'Создать объект диапазонаУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Добавить правило ошибкиMyRange.FormatConditions.Add Тип: = xlExpression, Formula1: = "= IsError (A1) = true"'Установить красный цвет салона.MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Конец подписки |
Условное форматирование дат в прошлом
У вас могут быть импортированные данные там, где вы хотите выделить даты, которые были в прошлом. Примером этого может быть отчет по дебиторам, в котором вы хотите выделить любые старые даты в счетах-фактурах старше 30 дней.
Этот код использует тип правила xlExpression и функцию Excel для оценки дат.
| 1234567891011 | Подложка DateInPastConditionalFormattingExample ()Dim MyRange As Range'Создать объект диапазона на основе столбца датУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Добавить правило ошибки для прошлых датMyRange.FormatConditions.Add Тип: = xlExpression, Formula1: = "= Now () - A1> 30"'Установить красный цвет салона.MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)Конец подписки |
Этот код принимает диапазон дат в диапазоне A1: A10 и устанавливает красный цвет ячейки для любой даты, прошедшей более 30 дней.
В формуле, используемой в условии, Now () указывает текущую дату и время. Это будет продолжать пересчитывать каждый раз, когда рабочий лист пересчитывается, поэтому форматирование будет меняться от одного дня к другому.
Использование панелей данных в условном форматировании VBA
Вы можете использовать VBA для добавления гистограмм к диапазону чисел. Это почти как мини-диаграммы, и они дают мгновенное представление о том, насколько велики числа по отношению друг к другу. Принимая значения по умолчанию для гистограмм, код очень легко писать.
| 123456 | Sub DataBarFormattingExample ()Dim MyRange As RangeУстановите MyRange = Range («A1: A10»)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarКонец подписки |
Ваши данные на листе будут выглядеть так:
Использование значков в условном форматировании VBA
Вы можете использовать условное форматирование, чтобы поместить значки рядом с числами на листе. Значки могут быть в виде стрелок, кружков или различных других форм. В этом примере код добавляет значки стрелок к числам в зависимости от их процентных значений:
| 12345678910111213141516171819202122232425 | Sub IconSetsExample ()Dim MyRange As Range'Создать объект диапазонаУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Добавить набор иконок в объект FormatConditionsMyRange.FormatConditions.AddIconSetCondition'Установите набор иконок в стрелки - условие 1С MyRange.FormatConditions (1).IconSet = ActiveWorkbook.IconSets (xl3Arrows)Конец с'установить критерии значка для требуемого процентного значения - условие 2С MyRange.FormatConditions (1) .IconCriteria (2).Type = xlConditionValuePercent.Значение = 33.Operator = xlGreaterEqualКонец с'установить критерии значка для требуемого процентного значения - условие 3С MyRange.FormatConditions (1) .IconCriteria (3).Type = xlConditionValuePercent.Значение = 67.Operator = xlGreaterEqualКонец сКонец подписки |
Это даст мгновенное представление, показывающее, высокое или низкое число. После запуска этого кода ваш рабочий лист будет выглядеть так:
Использование условного форматирования для выделения пятерки лучших
Вы можете использовать код VBA, чтобы выделить 5 первых чисел в диапазоне данных. Вы используете параметр под названием «AddTop10», но вы можете настроить номер ранга в коде на 5. Пользователь может захотеть увидеть самые высокие числа в диапазоне без предварительной сортировки данных.
| 1234567891011121314151617181920212223 | Sub Top5Example ()Dim MyRange As Range'Создать объект диапазонаУстановите MyRange = Range («A1: A10»)'Удалить предыдущие условные форматыMyRange.FormatConditions.Delete'Добавить условие Top10MyRange.FormatConditions.AddTop10С MyRange.FormatConditions (1)'Установить параметр сверху вниз.TopBottom = xlTop10Top'Установить только топ 5.Ранг = 5Конец сС MyRange.FormatConditions (1) .Font'Установить цвет шрифта.Color = -16383844.Конец сС MyRange.FormatConditions (1) .Interior'Установить цвет фона ячейки.Color = 13551615Конец сКонец подписки |
Данные на вашем листе после запуска кода будут выглядеть так:
Обратите внимание, что значение 145 появляется дважды, поэтому выделяются шесть ячеек.
Значение параметров StopIfTrue и SetFirstPriority
StopIfTrue имеет значение, если диапазон ячеек имеет несколько правил условного форматирования. Одиночная ячейка в пределах диапазона может удовлетворять первому правилу, но также может удовлетворять последующим правилам. Как разработчик, вы можете захотеть, чтобы форматирование отображалось только для первого правила. Другие критерии правила могут перекрываться и вносить непреднамеренные изменения, если им разрешено продолжить работу по списку правил.
По умолчанию для этого параметра установлено значение True, но вы можете изменить его, если хотите, чтобы учитывались все остальные правила для этой ячейки:
| 1 | MyRange. FormatConditions (1) .StopIfTrue = False |
Параметр SetFirstPriority указывает, будет ли это правило условия оцениваться первым, если для этой ячейки существует несколько правил.
| 1 | MyRange. FormatConditions (1) .SetFirstPriority |
Это перемещает позицию этого правила в позицию 1 в наборе условий форматирования, а любые другие правила будут перемещены вниз с измененными номерами индексов. Будьте осторожны, если вы вносите какие-либо изменения в правила в коде, используя порядковые номера. Вам необходимо убедиться, что вы изменяете или удаляете правильное правило.
Вы можете изменить приоритет правила:
| 1 | MyRange. FormatConditions (1) .Priority = 3 |
Это изменит относительное положение любых других правил в списке условного формата.
Использование условного форматирования для ссылки на другие значения ячеек
Это то, чего нельзя сделать с помощью условного форматирования Excel. Однако для этого вы можете создать свой собственный код VBA.
Предположим, у вас есть столбец данных, а в ячейке рядом с каждым числом есть текст, указывающий, какое форматирование должно выполняться для каждого числа.
Следующий код выполнит поиск по вашему списку чисел, отыщет в соседней ячейке форматирование текста, а затем отформатирует число, как требуется:
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting ()'Создайте переменные для хранения количества строк для табличных данныхDim Row as long, N as long'Захватить количество строк в диапазоне табличных данныхRRow = ActiveSheet.UsedRange.Rows.Count'Перебрать все строки в диапазоне табличных данныхДля N = 1 в ряд'Используйте оператор Select Case для оценки форматирования на основе столбца 2Выберите Case ActiveSheet.Cells (N, 2) .Value.'Преврати цвет салона в синийКорпус "Синий"ActiveSheet.Cells (N, 1) .Interior.Color = vbBlue'Преврати цвет салона в красныйКейс "Красный"ActiveSheet.Cells (N, 1) .Interior.Color = vbRed'Преврати цвет салона в зеленыйКорпус "Зеленый"ActiveSheet.Cells (N, 1) .Interior.Color = vbGreenКонец ВыбратьСледующий NКонец подписки |
После того, как этот код был запущен, ваш рабочий лист теперь будет выглядеть так:
Ячейки, используемые для форматирования, могут находиться в любом месте рабочего листа или даже на другом листе в книге. Вы можете использовать любую форму текста, чтобы создать условие для форматирования, и вы ограничены только вашим воображением в использовании этого кода.
Операторы, которые можно использовать в операторах условного форматирования
Как вы видели в предыдущих примерах, операторы используются для определения того, как будут оцениваться значения условий, например xlBetween.
Существует ряд этих операторов, которые можно использовать в зависимости от того, как вы хотите указать критерии правила.
| Имя | Ценить | Описание |
| xlBetween | 1 | Между. Может использоваться только при наличии двух формул. |
| xlEqual | 3 | Равный. |
| xlGreater | 5 | Больше чем. |
| xlGreaterEqual | 7 | Больше или равно. |
| xl Меньше | 6 | Меньше, чем. |
| xlLessEqual | 8 | Меньше или равно. |
| xlNotBetween | 2 | Не между. Может использоваться только при наличии двух формул. |
| xlNotEqual | 4 | Не равный. |
