Условное форматирование всей строки в Excel

Недавно мой коллега попросил меня помочь с интересным случаем с условным форматированием в Excel. Решение оказалось довольно необычным, и я решил им поделиться. Это может быть полезно и в других ситуациях..

Дело

У нас есть отчет, который выглядит примерно так (данные абсолютно сфабрикованы)

В последнем столбце применяется условное форматирование, которое окрашивает в разные цвета значения в нем. Задача — окрасить каждую строку таблицы в соответствующий цвет из последнего столбца..

Прежде чем мы увидим решение, несколько предложений об этом типе условного форматирования, который называется

Цветовые шкалы

Это готовая схема условного форматирования, в которой числа окрашены в разные цвета в зависимости от их относительного веса по сравнению с другими числами. В этом случае меньшие числа, такие как 146 и 163, находятся на темно-красном фоне, немного большие числа, такие как 272 и 282, находятся на более светлом красном фоне. Чем больше числа, тем больше фон меняется с оранжевого на желтый, светло-зеленый, а для самых больших чисел, таких как 893, он становится темно-зеленым..

Идея этого типа условного форматирования заключается в том, чтобы пользователь отчета мог легко заметить те значения, которые не являются высокими или достаточно низкими. Например, если это продажи, темно-красным цветом «подсвечиваются» строки с самыми низкими продажами, а темно-зеленым — строки с самыми высокими продажами..

Читать также:  Сводные таблицы в Excel - первые шаги

Установить такое условное форматирование очень просто:

Перейдите на главную — Условное форматирование — Цветовые шкалы и выберите один из предопределенных форматов..

Если эти стандартные форматы в Excel нас не устраивают, мы можем изменить их в разделе «Условное форматирование — Управление правилами».

А затем изменить правило

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

Например, мы можем выбрать полностью синюю цветовую схему, в которой столбец будет выглядеть так:

Эта проблема

Сначала подумал, что решение связано с изменением настроек. Я подумал, что этого будет достаточно, чтобы изменить область условного форматирования. Вместо применения только к столбцу E, он охватывает всю таблицу, т.е. область $ B $ 16: $ E $ 37

Однако результат очень неутешительный..

Просто Excel сравнивает все числа в поле условного форматирования и присваивает им цвет в зависимости от того, насколько они большие или маленькие. А поскольку даты на самом деле являются числами и больше 43 000, они попадают в высшую категорию, а значения в столбце E, которые меньше 1000, попадают в низшую. А текстовые данные вообще не форматируются, потому что это не число..

Читать также:  Файл Excel слишком большой - 6 причин проблемы

Но неважно, что это за данные — текст или числа и насколько они велики. Проблема в том, что цветовые шкалы определяют цвет, сравнивая все числа в поле, и наша задача — определить цвет в столбце E и распространить его на другие столбцы..

И тут мы ударились о камень, потому что как бы мы его ни повернули, врать ему без VBA было невозможно..

Решение

С небольшой помощью дяди Google мы нашли довольно простое решение, которое отлично поработало..

Вам просто нужно скопировать столбец E

Затем перейдите в… Word и вставьте туда.

На следующем этапе мы копируем столбец в Word и вставляем его обратно в Excel где-нибудь по бокам таблицы. Теперь это только значения, окрашенные в какой-то цвет, но без условного форматирования. Он теряется, когда мы вставляем значения в Word, потому что Word — это программа для обработки текста, а не программа для обработки таблиц чисел..

Читать также:  20 правил эффективной работы с Excel

Наконец, все, что нам нужно сделать, это вставить формат в другие столбцы таблицы (с помощью функции «Вставить специальные значения» или «Формат по образцу»)..

И результат, однако, не совсем такой, как мы ожидали:

В столбце даты формат чисел также теряется при переходе с Excel на Word и наоборот. Так что это должно быть исправлено, но все равно нет идеальных вещей, и альтернатива — написать макрос, что, безусловно, займет больше времени..

Вместо заключения

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

Но решение кейса — хороший пример нестандартного мышления, поэтому я рад им поделиться..

Более интересные и практические применения условного форматирования можно найти здесь или здесь..

Или, может быть, оформление и форматирование отчетов — ненужная роскошь для вас, потому что сбор и обработка ваших данных занимает слишком много времени? В этом случае обязательно зайдите в раздел Power query..

Понравилась статья? Поделиться с друзьями:
Что нужно знать пользователю?