Умовне форматування 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 = Діапазон (“A1: A10”)"Видалити існуюче умовне форматування з діапазонуMyRange.FormatConditions.Delete"Застосувати умовне форматуванняMyRange.FormatConditions.Add Type: = xlCellValue, Оператор: = xlBetween, _Формула1: = "= 100", Формула2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Зверніть увагу, що спочатку ми визначаємо діапазон MyRange застосувати умовне форматування.
Далі ми видаляємо будь -яке існуюче умовне форматування діапазону. Це гарна ідея, щоб запобігти додаванню одного і того ж правила кожного разу під час запуску коду (звичайно, це буде не доречно за всіх обставин).
Кольори задані числовими значеннями. Для цього непогано використовувати позначення RGB (червоний, зелений, синій). Для цього можна використовувати стандартні колірні константи, наприклад vbRed, vbBlue, але ви обмежені вісьмома варіантами кольору.
Доступно понад 16,7 млн кольорів, і за допомогою RGB можна отримати доступ до всіх. Це набагато простіше, ніж намагатися згадати, яке число відповідає якому кольору. Кожен з трьох колірних номерів RGB від 0 до 255.
Зауважте, що параметр "xlBetween" є включним, тому значення осередків 100 або 150 задовольнять умові.
Багатоумовне форматування
Можливо, вам захочеться встановити кілька умовних правил у вашому діапазоні даних, щоб усі значення в діапазоні охоплювалися різними умовами:
| 12345678910111213141516171819 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Створити об'єкт діапазонуВстановити MyRange = Діапазон (“A1: A10”)'Видалити попередні умовні форматиMyRange.FormatConditions.Delete"Додайте перше правилоMyRange.FormatConditions.Add Type: = xlCellValue, Оператор: = xlBetween, _Формула1: = "= 100", Формула2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)"Додайте друге правилоMyRange.FormatConditions.Add Type: = xlCellValue, Оператор: = xlLess, _Формула1: = "= 100"MyRange.FormatConditions (2) .Interior.Color = vbBlue"Додайте третє правилоMyRange.FormatConditions.Add Type: = xlCellValue, Оператор: = xlGreater, _Формула1: = "= 150"MyRange.FormatConditions (3) .Interior.Color = vbЖовтийEnd Sub |
У цьому прикладі встановлено перше правило, як і раніше, з кольором комірки червоним, якщо значення комірки знаходиться між 100 і 150.
Потім додаються ще два правила. Якщо значення комірки менше 100, то колір комірки синій, а якщо він більший за 150, то колір комірки жовтий.
У цьому прикладі вам потрібно переконатися, що всі можливості чисел охоплені, а правила не збігаються.
Якщо порожні клітинки знаходяться в цьому діапазоні, вони відображатимуться як сині, тому що Excel все ще приймає їх за значення менше 100.
Шляхом цього можна додати іншу умову як вираз. Це потрібно додати як перше правило умови в коді. Дуже важливо, якщо існує кілька правил, щоб правильно визначити порядок виконання, інакше результати можуть бути непередбачуваними.
| 1234567891011121314151617181920212223 | Sub MultipleConditionalFormattingExample ()Dim MyRange As Range'Створити об'єкт діапазонуВстановити MyRange = Діапазон (“A1: A10”)'Видалити попередні умовні форматиMyRange.FormatConditions.Delete"Додайте перше правилоMyRange.FormatConditions.Ad Type: = xlExpression, Formula1: = _"= LEN (TRIM (A1)) = 0"MyRange.FormatConditions (1) .Interior.Pattern = xlNone"Додайте друге правилоMyRange.FormatConditions.Ad Type: = xlCellValue, Оператор: = xlBetween, _Формула1: = "= 100", Формула2: = "= 150"MyRange.FormatConditions (2) .Interior.Color = RGB (255, 0, 0)"Додайте третє правилоMyRange.FormatConditions.Add Type: = xlCellValue, Оператор: = xlLess, _Формула1: = "= 100"MyRange.FormatConditions (3) .Interior.Color = vbBlue«Додайте четверте правилоMyRange.FormatConditions.Add Type: = xlCellValue, Оператор: = xlGreater, _Формула1: = "= 150"MyRange.FormatConditions (4) .Interior.Color = RGB (0, 255, 0)End Sub |
Це використовує тип xlExpression, а потім використовує стандартну формулу Excel, щоб визначити, чи порожня комірка, а не числове значення.
Об'єкт FormatConditions є частиною об'єкта Range. Він діє так само, як колекція з індексом, що починається з 1. Ви можете перебирати цей об’єкт, використовуючи For… Next або For… Кожен цикл.
Видалення правила
Іноді вам може знадобитися видалити окреме правило з набору кількох правил, якщо воно не відповідає вимогам даних.
| 12345678910111213 | Sub DeleteConditionalFormattingExample ()Dim MyRange As Range'Створити об'єкт діапазонуВстановити MyRange = Діапазон (“A1: A10”)'Видалити попередні умовні форматиMyRange.FormatConditions.Delete"Додайте перше правилоMyRange.FormatConditions.Ad Type: = xlCellValue, Оператор: = xlBetween, _Формула1: = "= 100", Формула2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Видалити правилоMyRange.FormatConditions (1). ВидалитиEnd Sub |
Цей код створює нове правило для діапазону A1: A10, а потім видаляє його. Ви повинні використовувати правильний номер індексу для видалення, тому перевірте «Керувати правилами» на передньому кінці програми Excel (це покаже правила в порядку виконання), щоб переконатися, що ви отримаєте правильний номер індексу. Зауважте, що в Excel немає можливості скасування, якщо ви видалите правило умовного форматування у VBA, на відміну від того, якщо ви це зробите через інтерфейс Excel.
Зміна правила
Оскільки правила - це сукупність об’єктів, заснованих на визначеному діапазоні, ви можете легко вносити зміни до певних правил за допомогою VBA. Фактичні властивості після додавання правила є лише для читання, але ви можете скористатися методом Modify, щоб змінити їх. Властивості, такі як кольори, читання / запис.
| 123456789101112131415 | Sub ChangeConditionalFormattingExample ()Dim MyRange As Range'Створити об'єкт діапазонуВстановити MyRange = Діапазон (“A1: A10”)'Видалити попередні умовні форматиMyRange.FormatConditions.Delete"Додайте перше правилоMyRange.FormatConditions.Ad Type: = xlCellValue, Оператор: = xlBetween, _Формула1: = "= 100", Формула2: = "= 150"MyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)'Змінити правилоMyRange.FormatConditions (1). Змінити xlCellValue, xlLess, "10""Змінити колір правилаMyRange.FormatConditions (1) .Interior.Color = vbGreenEnd Sub |
Цей код створює об’єкт діапазону (A1: A10) і додає правило для чисел від 100 до 150. Якщо умова істинна, колір осередку змінюється на червоний.
Потім код змінює правило на цифри, менші за 10. Якщо умова істинна, то колір комірки тепер змінюється на зелений.
Використання градуйованої колірної схеми
Умовне форматування Excel має можливість використання градуйованих кольорів у діапазоні чисел у порядку зростання або спадання.
Це дуже корисно, якщо у вас є дані, такі як показники продажів за географічною територією, температурою міста або відстанями між містами. Використовуючи VBA, ви маєте додаткову перевагу у тому, що ви можете обрати власну градуйовану колірну схему, а не стандартну, запропоновану в інтерфейсі Excel.
| 1234567891011121314151617181920212223242526272829 | Піддипломні кольори ()Dim MyRange As Range'Створити об'єкт діапазонуВстановити MyRange = Діапазон (“A1: A10”)'Видалити попередні умовні форматиMyRange.FormatConditions.Delete'Визначте тип шкалиMyRange.FormatConditions.AddColorScale ColorScaleType: = 3'Виберіть колір для найменшого значення в діапазоніMyRange.FormatConditions (1) .ColorScaleCriteria (1) .Type = _xlConditionValueLowestValueЗ MyRange.FormatConditions (1) .ColorScaleCriteria (1). FormatColor.Колір = 7039480Закінчити з'Виберіть колір для середніх значень у діапазоніMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Type = _xlConditionValuePercentileMyRange.FormatConditions (1) .ColorScaleCriteria (2) .Value = 50'Виберіть колір для середньої точки діапазонуЗ MyRange.FormatConditions (1) .ColorScaleCriteria (2). FormatColor.Колір = 8711167Закінчити з'Виберіть колір для найбільшого значення в діапазоніMyRange.FormatConditions (1) .ColorScaleCriteria (3) .Type = _xlConditionValueHighestValueЗ MyRange.FormatConditions (1) .ColorScaleCriteria (3). FormatColor.Колір = 8109667Закінчити зEnd Sub |
Коли цей код запущено, він буде поступово змінювати кольори комірок відповідно до висхідних значень у діапазоні A1: A10.
Це дуже вражаючий спосіб відображення даних і, безумовно, приверне увагу користувачів.
Умовне форматування для значень помилок
Якщо у вас величезна кількість даних, ви можете легко пропустити значення помилки у різних аркушах. Якщо це буде представлено користувачеві без вирішення, це може призвести до великих проблем і втрати довіри до цифр. Це використовує тип правила xlExpression та функцію Excel від IsError для оцінки комірки.
Ви можете створити код таким чином, щоб усі клітинки з помилками мали червоний колір:
| 1234567891011 | Sub ErrorConditionalFormattingExample ()Dim MyRange As Range'Створити об'єкт діапазонуВстановити MyRange = Діапазон (“A1: A10”)'Видалити попередні умовні форматиMyRange.FormatConditions.Delete'Додати правило помилокMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= IsError (A1) = true"'Встановіть колір інтер’єру на червонийMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Умовне форматування дат у минулому
Можливо, ви імпортуєте дані, де потрібно виділити дати, які були в минулому. Прикладом цього може бути звіт боржника, де ви хочете, щоб будь -які старі дати рахунків -фактур старше 30 днів виділялися.
Цей код використовує тип правила xlExpression та функцію Excel для оцінки дат.
| 1234567891011 | Sub DateInPastConditionalFormattingExample ()Dim MyRange As Range'Створити об'єкт діапазону на основі стовпця датВстановити MyRange = Діапазон (“A1: A10”)'Видалити попередні умовні форматиMyRange.FormatConditions.Delete'Додайте правило помилок для дат у минуломуMyRange.FormatConditions.Add Type: = xlExpression, Formula1: = "= Now ()-A1> 30"'Встановіть колір інтер'єру на червонийMyRange.FormatConditions (1) .Interior.Color = RGB (255, 0, 0)End Sub |
Цей код буде приймати діапазон дат в діапазоні A1: A10 і встановлюватиме колір клітинки на червоний для будь -якої дати, яка минула понад 30 днів.
У формулі, що використовується в умові, Now () дає поточну дату та час. Це буде постійно перераховувати кожного разу, коли аркуш буде перераховуватися, тому форматування буде змінюватися з одного дня на наступний.
Використання панелей даних у умовному форматуванні VBA
Ви можете використовувати VBA для додавання панелей даних до діапазону чисел. Вони майже схожі на міні -діаграми і дають миттєве уявлення про те, наскільки великі числа один щодо одного. Приймаючи значення за замовчуванням для панелей даних, код дуже легко писати.
| 123456 | Sub DataBarFormattingExample ()Dim MyRange As RangeВстановити MyRange = Діапазон (“A1: A10”)MyRange.FormatConditions.DeleteMyRange.FormatConditions.AddDatabarEnd Sub |
На аркуші ваші дані будуть виглядати так:
Використання значків у умовному форматуванні VBA
Ви можете використовувати умовне форматування, щоб розмістити значки поряд з номерами на робочому аркуші. Значки можуть бути стрілками або колами або різними іншими формами. У цьому прикладі код додає до чисел значки стрілок на основі їх відсоткових значень:
| 12345678910111213141516171819202122232425 | Sub IconSetsExample ()Dim MyRange As Range'Створити об'єкт діапазонуВстановити MyRange = Діапазон (“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Закінчити зEnd Sub |
Це дозволить миттєво переглянути, чи є число високим чи низьким. Після запуску цього коду ваш аркуш буде виглядати так:
Використання умовного форматування для виділення першої п’ятірки
Ви можете використовувати код VBA, щоб виділити 5 найкращих чисел у діапазоні даних. Ви використовуєте параметр "AddTop10", але ви можете налаштувати номер рангу в коді до 5. Користувач, можливо, побачить побачити найвищі числа в діапазоні без необхідності попереднього сортування даних.
| 1234567891011121314151617181920212223 | Sub Top5Example ()Dim MyRange As Range'Створити об'єкт діапазонуВстановити MyRange = Діапазон (“A1: A10”)'Видалити попередні умовні форматиMyRange.FormatConditions.Delete'Додайте умову Top10MyRange.FormatConditions.AddTop10З MyRange.FormatConditions (1)'Встановити параметр зверху вниз.TopBottom = xlTop10Top'Лише топ -5.Ранк = 5Закінчити зЗ MyRange.FormatConditions (1). Шрифт'Встановіть колір шрифту.Колір = -16383844Закінчити зЗ MyRange.FormatConditions (1) .Interior'Встановіть колір фону клітинки.Колір = 13551615Закінчити зEnd Sub |
Дані на вашому аркуші виглядатимуть так після запуску коду:
Зауважте, що значення 145 відображається двічі, тому виділено шість клітинок.
Значення параметрів StopIfTrue та SetFirstPriority
StopIfTrue має значення, якщо діапазон комірок має кілька правил умовного форматування. Одна клітинка в межах діапазону може задовольняти першому правилу, але також може задовольняти наступним правилам. Як розробник, ви можете захотіти, щоб він відображав форматування лише для першого правила, до якого воно стосується. Інші критерії правил можуть збігатися і можуть внести ненавмисні зміни, якщо їм дозволено продовжити список правил.
За умовчанням для цього параметра встановлено значення True, але ви можете змінити його, якщо хочете, щоб усі інші правила для цієї комірки були враховані:
| 1 | MyRange. FormatConditions (1) .StopIfTrue = Неправда |
Параметр SetFirstPriority визначає, чи буде це правило умови спочатку оцінено, якщо для цієї комірки існує кілька правил.
| 1 | MyRange. FormatConditions (1) .SetFirstPriority |
Це переміщує положення цього правила на позицію 1 у колекції умов форматування, а будь -які інші правила будуть переміщені вниз зі зміненими номерами індексів. Обережно, якщо ви вносите будь -які зміни до правил у коді, використовуючи номери індексів. Вам потрібно переконатися, що ви змінюєте або видаляєте правильне правило.
Ви можете змінити пріоритет правила:
| 1 | MyRange. FormatConditions (1). Пріоритет = 3 |
Це змінить відносні позиції будь -яких інших правил у списку умовного формату.
Використання умовного форматування з посиланням на інші значення клітинок
Це одне, чого не може зробити умовне форматування Excel. Однак для цього можна створити власний код VBA.
Припустимо, що у вас є стовпець даних, а в сусідній клітинці до кожного числа є текст, який вказує, яке форматування має відбутися для кожного числа.
Наступний код буде запускати ваш список чисел, шукати у сусідній комірці форматування тексту, а потім відформатувати номер за необхідністю:
| 123456789101112131415161718192021 | Sub ReferToAnotherCellForConditionalFormatting ()'Створіть змінні для утримання кількості рядків табличних данихDim RRow As Long, N As Long'Зафіксуйте кількість рядків у діапазоні табличних данихRRow = ActiveSheet.UsedRange.Rows.Count'Ітерація через усі рядки в діапазоні табличних данихДля N = 1 до RRow'Використовуйте оператор 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Кінець ВиберітьДалі NEnd Sub |
Після того, як цей код буде запущено, ваш робочий лист тепер буде виглядати так:
Осередки, на які посилається для форматування, можуть бути де завгодно на робочому аркуші або навіть на іншому аркуші робочої книги. Ви можете використовувати будь -яку форму тексту, щоб створити умову для форматування, і ви обмежені лише своєю уявою у використанні, до якого можна застосувати цей код.
Оператори, які можна використовувати в операторах умовного форматування
Як ви бачили в попередніх прикладах, оператори використовуються для визначення способу оцінки значень умов, наприклад. xlМіж.
Існує ряд цих операторів, які можна використовувати, залежно від того, як ви хочете вказати критерії правил.
| Назва | Цінність | Опис |
| xlПоміж | 1 | Між. Можна використовувати лише за наявності двох формул. |
| xl рівне | 3 | Рівні. |
| xlGreater | 5 | Більше ніж. |
| xlGreaterEqual | 7 | Більше або дорівнює. |
| xlМенше | 6 | Менше ніж. |
| xlLessEqual | 8 | Менше або дорівнює. |
| xlNotBetween | 2 | Не між. Можна використовувати лише за наявності двох формул. |
| xlNotEqual | 4 | Не рівний. |
