Ад Условного Форматирования

Порядок необходим глупцам,
гений же властвует над хаосом.
(Альберт Эйнштейн)

Исходные данные

Если вы используете в своих таблицах Excel подсветку ячеек или целых строк правилами условного форматирования, то вы, скорее всего, уже сталкивались с этой проблемой. Если ещё нет, то она поджидает вас в самом ближайшем будущем - гарантирую.

Чтобы проще было понять, в чём, собственно, дело - давайте рассмотрим простой пример. Предположим, что мы работаем вот с такой несложной таблицей, где фиксируются продажи:

Исходная таблица

Для наглядности к таблице добавлены три правила условного форматирования:

Правила условного форматирования

Первое правило делает синие гистограммы на столбце с суммами сделок. Создается через Главная - Условное форматирование - Гистограммы (Home - Conditional formatting - Data bars).

Второе - подсвечивает желтым ячейки с именами менеджеров, которые не выполнили план, т.е. сумма их сделки меньше, чем зелёная ячейка H2.

Третье - делает нижнюю границу всей строки красной, если день меняется на следующий, т.е. дата в текущей строке не равна дате в следующей.

Второе и третье правила создаются через Главная - Условное форматирование - Создать правило - Использовать формулу для определения форматируемых ячеек (Home - Conditional formatting - Create rule - Use formula to determine which cells to format) с вводом соответствующей формулы (2) и настройкой формата ячеек (3):

Создание правила условного форматирования с формулой

Пока что, надеюсь, всё просто и понятно. Таблица хранит данные, а условное форматирование наглядно подсвечивает негодяев-менеджеров, разделяет даты и визуализирует стоимость.

Путь к катастрофе

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

После удаления строки из середины таблицы

Красная линия между 2 и 3-м марта почему-то исчезла, а наше правило условного форматирования для разделения дат развалилось на два, причем одно из них с ошибкой #ССЫЛКА (т.е. не работает), а другое применяется к двум несмежным диапазонам A2:E8 и A10:E29 (не ко всей таблице!).

Шикарно, правда?!

Теперь представим, что Кирилл Краснов повторил свою сделку в Тольятти с магазином "Лента" (строка 25) и вам нужно внести эти данные в таблицу.

Как вы поступите?

Скорее всего, как любой нормальный человек, вы скопируете 25-ю строчку и вставите её в конец таблицы, верно?

Копируем строчку в конец таблицы

Ага, и получите в наследство вот такой бардак в правилах условного форматирования:

Продублированные правила для добавленной строки

Excel зачем-то продублировал те же правила для добавленной строки вместо того, чтобы просто растянуть диапазон в поле Применяется к (Applied to).

Ну, и на десерт давайте попробуем ещё что-нибудь безобидное - например, вставить пустую строку в середину таблицы, между 4 и 5-й строчками:

Вставляем пустую строку

В списке правил условного форматирования это приведёт к появлению еще одного дубликата и раздроблению диапазона уже существующего 5-го правила на кучу фрагментов:

Еще больше проблем с условным форматированием

Продолжать можно долго, но, думаю, вы уже уловили идею или вспомнили, как сталкивались с этой бедой ранее (эта проблема существует в Excel ещё с 2007 года). Выполнение совершенно безобидных и естественных операций с таблицей (вставка и удаление строк, копирование, вырезание и перенос) приводят к:

  • появлению бесчисленных дубликатов одних и тех же правил
  • фрагментации диапазонов применения этих правил
  • появлению неработающих правил с ошибками #ССЫЛКА!

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

На англоязычных Excel-форумах в интернете такую картину называют иногда "адом" или "кошмаром условного форматирования" ("Conditional Formatting Nightmare" или "Conditional Formatting Hell").

Причем весь этот быстро разрастающийся бардак очень скоро начнёт нещадно тормозить. Условное форматирование, само по себе, весьма ресурсоёмкая штука, т.к. Excel пересчитывает правила УФ гораздо чаще, чем те же формулы. А когда этих правил несколько десятков, то даже самый мощный ПК начнёт "тупить".

Ну, и вишенкой на торте будет невозможность изменить размеры окна Диспетчера правил условного форматирования, чтобы увидеть весь этот хаос (в приведенных выше скриншотах я это сделал в графическом редакторе). Вам придется долго и мучительно прокручивать весь список в маленьком окошке полосой прокрутки.

Как же всё исправить?

Способ 1. Вручную

Несмотря на кажущуюся запущенность, лечится весь этот адок достаточно легко. Идея в том, что правила УФ "ломаются", обычно, для строк ниже первой. Первая же строка, в большинстве случаев, остается в порядке. Поэтому, чтобы всё починить, нам нужно просто очистить все правила в таблице и заново распространить их с первой строки на все остальные.

Для этого делаем следующее:

  1. Выделяем в нашей таблице все строки кроме первой.
  2. Удаляем все правила условного форматирования с выделенных ячеек через Главная - Условное форматирование - Удалить правила - Удалить правила из выделенных ячеек (Home - Conditional formatting - Clear rules - Clear rules from selected cells).
  3. Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home - Format Painter) и выделяем все остальные строки, копируя на них формат с первой.

Способ 2. Макросом

Если есть ощущение, что подобную процедуру вам придётся проделывать ещё не раз, то имеет смысл автоматизировать весь процесс с помощью макроса. Для этого:

  1. Жмём сочетание клавиш Alt+F11 или на вкладке Разработчик кнопку Visual Basic (Developer - Visual Basic).
  2. В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert - Module.
  3. Вставляем в созданный пустой модуль наш макрос:
Sub Fix_СF_Hell()

    'создаем ссылки на диапазоны
    Set rngAll = Selection
    Set rngRow1 = Selection.Rows(1)
    Set rngRow2 = Selection.Rows(2)
    Set rngRowLast = Selection.Rows(rngAll.Rows.Count)

    'удаляем все правила форматирования со всех строк кроме первой
    Range(rngRow2, rngRowLast).FormatConditions.Delete
    
    'копируем форматы с первой строки на все остальные
    rngRow1.Copy
    Range(rngRow1, rngRowLast).PasteSpecial Paste:=xlPasteFormats
    Application.CutCopyMode = False
    
End Sub

Теперь можно будет просто выделить все строки в таблице (кроме шапки) и запустить макрос через Разрабочик - Макросы (Developer - Macros) или сочетанием клавиш Alt+F8.

И всё будет хорошо :)

P.S.

И не забудьте сохранить файл в формате с поддержкой макросов (xlsm).

Если нужно применять этот макрос в других файлах, то имеет смысл поместить его в Личную Книгу Макросов (Personal Macro Workbook).

Немного улучшенная версия этого макроса уже встроена в последнюю версию моей надстройки PLEX ;)

Исправление УФ-ада в надстройке PLEX

Ссылки по теме



07.04.2020 22:18:35
Видео хорошее))  А где пример про использование настройки PLEX в данной ситуации?
08.04.2020 23:35:54
Вот именно поэтому я как можно чаще стараюсь избегать этих "красивых" примочек. Тем более, что на бумаге их как бы и не видно.
10.04.2020 22:30:22
Раскрашивать лучше отчеты в формате сводных таблиц. Там такой дикости нету. Применил ко всему полю, и бед не знаешь само растягивается, само стягивается - красота!
11.04.2020 14:40:57
Спасибо за решение.
После опробации способа 1 и 2 на своей огромной таблице появились вопросы:
1) Можно ли к макросу прикрутить "выделить все строки в таблице (кроме шапки)"?.. Да, Ctrl+A внутри таблицы и так выделяет все строки в таблице (кроме шапки), и тем не менее - в макрос это можно встроить?
---------
2) Ещё имеется ..ммм.. особенность работы 1 и 2 способов: в некоторых ячейках первой строки имеется текст с гиперссылками. Соответственно, текст синий, как и положено автоформату для гиперссылок. Засада в том, что и 1 и 2 способы принудительно раскрашивают текст во всём столбце в синий цвет (хотя не везде есть гиперссылки, и текст в этих ячейках чёрный или другой, которым я его специально пометил и сменять этот цвет шрифта нельзя)....((  Можно как-то заставить - хотя бы макрос - копировать только УФ, не цепляя обычное форматирование?
30.07.2020 16:03:01
Можно ли к макросу прикрутить "выделить все строки в таблице (кроме шапки)"?
Добавьте в самом начале макроса код ниже, и будет вам счастье :)
Rows("2:2").Select  
Range(Selection, Selection.End(xlDown)).Select
15.04.2020 08:25:10
Николай,
а как быть, если УФ распространенно на хаотичный диапазон (где-то через строку, где-то через две)?
11.09.2020 15:13:34
Доброго дня.

В тему условного форматирования такой вопрос:
- как на отдельный лист получить список всех "условий форматирования" на рабочем листе.
Например:
столбец 1          / столбец 2             / столбец 3
адрес                / формула              / диапазон на
ячейки,             /  условия               /  который
содержащей    /                               /  действует
условие            /                               /  условие
-------------------------------------------------
$F$10               /   = $O10<6            /  = $F$1:$F$11809

Пытался найти ответ на свой вопрос, зайдя в редактор VBA, полез в .Sheets.Item(1).Cells.FormatConditions.Item их там 256.
Внутри каждого - "темный лес, непролазный".
В общем - застрял.

Еще вопрос:
- как ненужные мне условия из полученного списка, удалить на рабочем листе.

С уважением,
Владислав            
Наверх