Ад Условного Форматирования
Порядок необходим глупцам,
гений же властвует над хаосом.
(Альберт Эйнштейн)
Исходные данные
Если вы используете в своих таблицах 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. Вручную
Несмотря на кажущуюся запущенность, лечится весь этот адок достаточно легко. Идея в том, что правила УФ "ломаются", обычно, для строк ниже первой. Первая же строка, в большинстве случаев, остается в порядке. Поэтому, чтобы всё починить, нам нужно просто очистить все правила в таблице и заново распространить их с первой строки на все остальные.
Для этого делаем следующее:
- Выделяем в нашей таблице все строки кроме первой.
- Удаляем все правила условного форматирования с выделенных ячеек через Главная - Условное форматирование - Удалить правила - Удалить правила из выделенных ячеек (Home - Conditional formatting - Clear rules - Clear rules from selected cells).
- Выделяем первую строку, жмём кнопку-кисточку Формат по образцу на Главной (Home - Format Painter) и выделяем все остальные строки, копируя на них формат с первой.
Способ 2. Макросом
Если есть ощущение, что подобную процедуру вам придётся проделывать ещё не раз, то имеет смысл автоматизировать весь процесс с помощью макроса. Для этого:
- Жмём сочетание клавиш Alt+F11 или на вкладке Разработчик кнопку Visual Basic (Developer - Visual Basic).
- В открывшемся окне редактора макросов добавляем в нашу книгу новый модуль через меню Insert - Module.
- Вставляем в созданный пустой модуль наш макрос:
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 ;)
Ссылки по теме
- Как работает условное форматирование в Excel
- Подсветка наборов строк с помощью условного форматирования
- Разделительная линия между наборами строк
После опробации способа 1 и 2 на своей огромной таблице появились вопросы:
1) Можно ли к макросу прикрутить "выделить все строки в таблице (кроме шапки)"?.. Да, Ctrl+A внутри таблицы и так выделяет все строки в таблице (кроме шапки), и тем не менее - в макрос это можно встроить?
---------
2) Ещё имеется ..ммм.. особенность работы 1 и 2 способов: в некоторых ячейках первой строки имеется текст с гиперссылками. Соответственно, текст синий, как и положено автоформату для гиперссылок. Засада в том, что и 1 и 2 способы принудительно раскрашивают текст во всём столбце в синий цвет (хотя не везде есть гиперссылки, и текст в этих ячейках чёрный или другой, которым я его специально пометил и сменять этот цвет шрифта нельзя)....(( Можно как-то заставить - хотя бы макрос - копировать только УФ, не цепляя обычное форматирование?
Rows("2:2").Select Range(Selection, Selection.End(xlDown)).Selectа как быть, если УФ распространенно на хаотичный диапазон (где-то через строку, где-то через две)?
В тему условного форматирования такой вопрос:
- как на отдельный лист получить список всех "условий форматирования" на рабочем листе.
Например:
столбец 1 / столбец 2 / столбец 3
адрес / формула / диапазон на
ячейки, / условия / который
содержащей / / действует
условие / / условие
-------------------------------------------------
$F$10 / = $O10<6 / = $F$1:$F$11809
Пытался найти ответ на свой вопрос, зайдя в редактор VBA, полез в .Sheets.Item(1).Cells.FormatConditions.Item их там 256.
Внутри каждого - "темный лес, непролазный".
В общем - застрял.
Еще вопрос:
- как ненужные мне условия из полученного списка, удалить на рабочем листе.
С уважением,
Владислав
Если ещё актуально.
"Item" — это экземпляры правил. А вы хотите у каждого из них получить свойства. Значит, вам надо перебрать эти "Item" и извлечь нужные свойства.
Вот описание экземпляров объекта условного форматирования в справке Microsoft:
Вот его свойства:
Вот свойства, о которых вы спрашиваете:- Формула условия: , . В описаниях по ссылкам объясняется, когда используется одна формула, а когда две. Насколько я понимаю ваш вопрос, ячейка, содержащая условие, задаётся в этих же свойствах.
- Диапазон, на который действует условие: . Это объект типа Диапазон (Range). Чтобы вывести адрес диапазона в виде текста, нужно указать "...AppliesTo.Address".
Уже хотел писать вам на тему проблем с условным форматированием — и вуаля, как раз сегодня попался вот этот урок. Странно, конечно, что умное форматирование какое-то неумное.
К сожалению, до способа № 1 я дошёл своим умом. "К сожалению" — потому что вначале потратил уйму времени на попытки вручную удалять сотни дубликатов правил. Зато и решение у меня более тонкое и правильное: на последнем шаге изменять диапазон, к которому применяются правила, а не копировать форматирование ячеек, т. к. форматирование у них не только условное.
Попытался допилить в этом же направлении ваш макрос. Последний блок сделал таким:
В любом случае спасибо за ваши уроки — это действительно редкий клад. Даже то, что знал, послушал с удовольствием. Так всё раскладывать по полочкам не каждому дано.
Нигде не могу найти информацию. На листе, через УФ, шрифт в ячейке становится "зачеркнутый". На экране монитора все отлично, но при распечатки "зачеркнутый" шрифт становится обычным. Как решить данную проблему?