Excel изменение цвета ячейки по условию. Как в Excel изменять цвет строки в зависимости от значения в ячейке

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

Естественно это задание для Экселя. Программа должна автоматически найти таких контрагентов и соответственно выделить их цветом. Для этих условий будем использовать условное форматирование.

Автоматическое заполнение ячеек датами

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

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

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

Автоматическое заполнение ячеек актуальными датами

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


Как работает формула для автоматической генерации уходящих месяцев?

На рисунке формула возвращает период уходящего времени начиная даты написания статьи: 17.09.2017. В первом аргументе в функции DATA – вложена формула, которая всегда возвращает текущий год на сегодняшнюю дату благодаря функциям: ГОД и СЕГОНЯ. Во втором аргументе указан номер месяца (-1). Отрицательное число значит, что нас интересует какой был месяц в прошлом времени. Пример условий для второго аргумента со значением:

  • 1 – значит первый месяц (январь) в году указанном в первом аргументе;
  • 0 – это 1 месяца назад;
  • -1 – это 2 мес. назад от начала текущего года (то есть: 01.10.2016).

Последний аргумент – это номер дня месяца указано во втором аргументе. В результате функция ДАТА собирает все параметры в одно значение и формула возвращает соответственную дату.


Как видно теперь функция ДАТА использует значение из ячейки B1 и увеличивает номер месяца на 1 по отношению к предыдущей ячейки. В результате получаем 1 – число следующего месяца.

Теперь скопируйте эту формулу из ячейки C1 в остальные заголовки столбцов диапазона D1:L1.

Выделите диапазон ячеек B1:L1 и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Формат ячеек» или просто нажмите комбинацию клавиш CTRL+1. В появившемся диалоговом окне, на вкладке «Число», в разделе «Числовые форматы:» выберите опцию «(все форматы)». В поле «Тип:» введите значение: МММ.ГГ (обязательно буквы в верхнем регистре). Благодаря этому мы получим укороченное отображение значения дат в заголовках регистра, что упростит визуальный анализ и сделает его более комфортным за счет лучшей читабельности.


Обратите внимание! При наступлении января месяца (D1), формула автоматически меняет в дате год на следующий.



Как выделить столбец цветом в Excel по условию

Теперь необходимо выделить ячейки цветом, касающиеся текущего месяца. Благодаря этому мы с легкостью найдем столбец, в который нужно вводить актуальные данные в этом месяце. Для этого:


Столбец под соответствующим заголовком регистра автоматически подсвечивается зеленым цветом соответственно с нашими условиями:


Как работает формула выделения столбца цветом по условию?

Благодаря тому, что перед созданием правила условного форматирования мы охватили всю табличную часть для введения данных регистра, форматирование будет активно для каждой ячейки в этом диапазоне B2:L15. Смешанная ссылка в формуле B$1 (абсолютный адрес только для строк, а для столбцов – относительный) обусловливает, что формула будет всегда относиться к первой строке каждого столбца.

Автоматическое выделение цветом столбца по условию текущего месяца

Главное условие для заполнения цветом ячеек: если в диапазоне B1:L1 находиться та же дата, что и первого дня текущего месяца, тут же ячейки в целом столбце изменяют свой цвет на указанный в условном форматировании.

Обратите внимание! В условиях этой формулы, для последнего аргумента функции ДАТА указано значение 1, так же, как и для формул в определении дат для заголовков столбцов регистра.

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

Табличная часть сформатирована, теперь заполним ее текстовым значением «заказ» в смешанном порядке клиентов для текущего и прошлых месяцев.

Как выделить ячейки красным цветом по условию

Теперь нам необходимо выделить красным цветом ячейки с номерами клиентов, которые на протяжении 3-х месяцев не совершили ни одного заказа. Для этого:


Номера клиентов подсвечиваются красным цветом, если в их строке нет значения «заказ» в последних трех ячейках к текущему месяцу (включительно).

Анализ формулы для выделения цветом ячеек по условию:

Сначала займемся средней частью нашей формулы. Функция СМЕЩ возвращает ссылку на диапазон смещенного по отношении к области базового диапазона определенной числом строк и столбцов. Возвращаемая ссылка может быть одной ячейкой или целым диапазоном ячеек. Дополнительно можно определить количество возвращаемых строк и столбцов. В нашем примере функция возвращает ссылку на диапазон ячеек для последних 3-х месяцев.

Важная часть для нашего условия выделения цветом находиться в первом аргументе функции СМЕЩ. Он определяет, с какого месяца начать смещение. В данном примере – это ячейка D2, то есть начало года – январь. Естественно для остальных ячеек в столбце номер строки для базовой ячейки будет соответствовать номеру строки в котором она находиться. Следующие 2 аргумента функции СМЕЩ определяют на сколько строк и столбцов должно быть выполнено смещение. Так как вычисления для каждого клиента будем выполнять в той же строке, значение смещения для строк указываем –¬ 0.

В тоже время для вычисления значения третьего аргумента (смещение по столбцам) используем вложенную формулу МЕСЯЦ(СЕГОДНЯ()), Которая в соответствии с условиями возвращает номер текущего месяца в текущем году. От вычисленного формулой номера месяца отнимаем число 4, то есть в случаи Ноября получаем смещение на 8 столбцов. А, например, для Июня – только на 2 столбца.

Последнее два аргумента для функции СМЕЩ определяют высоту (в количестве строк) и ширину (в количестве столбцов) возвращаемого диапазона. В нашем примере – это область ячеек с высотой на 1-ну строку и шириной на 4 столбца. Этот диапазон охватывает столбцы 3-х предыдущих месяцев и текущий.

Первая функция в формуле СЧЕТЕСЛИ проверяет условия: сколько раз в возвращаемом диапазоне с помощью функции СМЕЩ встречается текстовое значение «заказ». Если функция возвращает значение 0 – значит от клиента с таким номером на протяжении 3-х месяцев не было ни одного заказа. А в соответствии с нашими условиями, ячейка с номером данного клиента выделяется красным цветом заливки.

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

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

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

Ручная установка цвета ячейки

Наиболее простым способом установки цвета ячейки в excel является инструмент «Цвет заливки» находящийся на главной панели инструментов.

Выделив определенный диапазон ячеек нужно нажать на инструмент «Цвет заливки» либо стрелочку рядом. Если нажать стрелочку рядом, то откроются цвета темы. Из предложенных цветов можно выбрать нужный цвет или нажать кнопку «Другие цвета». В этом случае откроется еще одно окно с возможностью выбора цвета.

Кроме описанного способа есть второй ручной способ. Нужно сначала выделить диапазон ячеек, а потом вызвать контекстное меню и нажать пункт меню «Формат ячеек».

Откроется диалоговое окно формата ячейки. После этого нужно будет нажать закладку «Заливка».

В данном диалоговом окне нужно выбрать цвет и все.

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

Установка цвета фона ячейки в зависимости от значения

Иногда случается так, что в зависимости от того какое значение записано в ячейке нужно установить цвет определенного фона. Рассмотрим как это сделать на примере. У нас есть торговые агенты, которым поставлена норма плана по определенным группам продуктов. Если норма не выполнена, то ячейка с продажами должна «загореться» красным. В качестве первичного условия скажем что продажи по недельно не должны быть ниже 185 тысяч рублей.

Для выполнения поставленной задачи нужно выделить диапазон ячеек и нажать на панели инструментов «Условное форматирование»

В открывшемся меню нас интересует пункт «Правила выделения ячеек». Если нажать на указанный пункт, то можно увидеть, что разработчики excel реализовали множество возможностей выделения ячеек. Выделить фон можно по следующим критериям: больше, меньше, между, равно, текст содержит, дата, повторяющиеся значения. Для решения нашей задачи нам нужно использовать «Меньше».

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

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

Установка цвета ячейки при помощи VBA

Способ выделения ячеек в excel о котором пойдет речь далее является самым универсальным. Так как с его помощью можно построить самые сложные правила для выделения ячеек. Но к сожалению его могут использовать только продвинутые пользователи. Причина проста – нужно программировать, а значит обладать более сложными навыками.

Установить цвет ячейки при помощи VBA достаточно просто, программный код выделения будет выглядеть так:

Range("O6").Select
Selection.Interior.Color = QBColor(10)

После выполнения данного кода ячейка О6 станет салатовой.

Использование VBA для выделения цвета ячейки позволяет решать широкий круг задач, например выделять ячейки определенным цветом:

  • в зависимости от типа введенного значения;
  • в зависимости от времени редактирования документа;
  • в зависимости от пользователя компьютера, на котором редактируется документ и т.д.

Человек так уж устроен, что цвета воспринимает лучше, чем голые цифры — недаром наиболее важные участки диаграмм или строки таблицы на которые стоит обратить внимание, принято выделять контрастным цветом. Однако отмечать «хорошие» и «плохие» ячейки таблицы в Excel вручную, занятие не самое веселое. Вот бы MS Excel умел делать это самостоятельно, да ещё и автоматически менять эти самые цвета, в случае изменения ситуации…

А ведь он итак прекрасно умеет это делать — нам с вами остается только ему слегка помочь!

Давайте решим такую вот прикладную задачу: в нашей таблице «фрукты» указан вес того или иного наименования в килограммах. Чтобы было проще ориентироваться в том, чего у нас не хватает, а чего наоборот — в избытке, мы раскрасим все значения меньше 20 красным цветом, а все, что выше 50 — зеленым. При этом всё, что осталось в этом диапазоне цветом помечаться не будет совсем. А чтобы усложнить задачу пойдем ещё дальше и сделаем присвоение цвета динамическим — при изменении значения в соответствующей ячейке, будет меняться и её цвет.

Сначала выделяем диапазон данных, то есть содержимое второго столбца таблицы MS Excel, а затем идем на вкладку «Главная «, где в группе «Стили» активируем инструмент «Условное форматирование «, и в раскрывшемся списке выбираем «Создать правило «.

В появившемся окне «Создание правила форматирования» выбираем Тип правила: «Форматировать только ячейки которые содержат», а в конструкторе ниже, устанавливаем параметры: «Значение ячейки», «Меньше» и вручную вписываем наш «край»: число 20.

Нажимаем кнопку «Формат» ниже, переходим на вкладку «Заливка» и выбираем красный цвет. Нажимаем «Ок».

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

Посмотрите на таблицу — яблок и мандаринов у нас явно осталось совсем мало, пора делать новый закуп!

Отлично, данные уже выделяются цветом!

Теперь, по аналогии, создадим ещё одно правило — только на этот раз с параметрами «Значение ячейки», «Больше», 20. В качестве заливки укажем зеленый цвет. Готово.

Мне этого показалось мало — черный текст на красном и зеленом фоне читается плохо, поэтому я решил немного украсить наши правила, и заменить цвет текста на белый. Чтобы проделать это, откройте инструмент «Условное форматирование», но выберите не пункт «Создать правило», а «Управление правилами «, ниже.

В появившемся окне выделяем оба правила по очереди, и нажимаем кнопку «Изменить «, после чего, в уже знакомом окне «Формат » переходим на вкладку «Шрифт » и меняем цвет текста на белый. Вот что у меня получилось в итоге:

Теперь я изменил не только фон ячеек таблицы, но и цвет шрифта

Попробуем изменить «плохие» значения на «хорошие»? Раз и готово — цвет автоматически изменился, как только в соответствующих ячейках появились значения, попадающие под действие одного из правил.

Меняем в нашей excel-таблице значения… все работает!

Выбирая инструменты на закладке: «ГЛАВНАЯ» в разделе «Стили» из выпадающего меню «Условное форматирование» нам доступна целая группа «Правила отбора первых и последних значений». Однако часто необходимо сравнить и выделить цветом ячейки в Excel, но ни один из вариантов готовых решений не соответствует нашим условиям. Например, в конструкции условия мы хотим использовать больше критериев или выполнять более сложные вычисления. Всегда можно выбрать последнюю опцию «Другие правила» она же является опцией «Создать правило». Условное форматирование позволяет использовать формулу для создания сложных критериев сравнения и отбора значений. Создавая свои пользовательские правила для условного форматирования с использованием различных формул мы себя ничем не ограничиваем.

Как сравнить столбцы в Excel и выделить цветом их ячейки?

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

Чтобы создать новое пользовательское правило делаем следующее:


Обратите внимание! В данной формуле мы используем только относительные ссылки на ячейки – это важно. Ведь нам нужно чтобы формула анализировала все ячейки выделенного диапазона.



Как выделить цветом ячейку в Excel по условию?

Теперь оранжевым цветом выделим те суммы магазинов, которые в текущем году меньше чем в прошлом и с отрицательной прибылью. Создадим второе правило для этого же диапазона D2:D12:


Мы видим, что получили не совсем ожидаемый результат, так как созданное новое правило всегда имеет высший приоритет по сравнению со старыми правилами условного форматирования в Excel. Необходимо снизить приоритет для нового правила. Чтобы проанализировать данную особенность наглядно и настроить соответствующим образом необходимо выбрать инструмент: ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

Выберите новое оранжевое правило в появившемся окне «Диспетчер правил условного форматирования» и нажмите на кнопку «Вниз» (CTRL+стрелка вниз), как показано на рисунке:


Как видите последовательность правил очень важна если их много присвоено для одного и того же диапазона ячеек:

На первый взгляд может показаться что несколько правил могут форматировать одну и туже ячейку одновременно. В принципе это так, но при определенном условии, что все правила будут использовать разные типы форматирования. Например, правило 1 – изменяет шрифт, 2 – меняет заливку, 3 – добавляет границу, 4 – узор и т.д. Но если после выполнения любого правила, когда его условие выполнено, было проверено следующее правило для данной ячейки, тогда следует в окне диспетчера отметить галочкой в колонке «Остановить если истина»:

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


Этим ячейкам будет присвоен зеленый цвет и жмем везде ОК.

Примечание. В формуле можно использовать любые ссылки для текущего листа. В версии Excel 2010 можно ссылаться и на другие листы. А в Excel 2007 к другим листам можно обращаться только через имена диапазонов. Мы рекомендуем во всех версиях Excel ссылаться на другие листы через имена, так как это позволяет избежать множество ошибок при создании пользовательских правил для условного форматирования.

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

Пусть в диапазоне А6:С16 имеется таблица с перечнем работ, сроками выполнения и статусом их завершения (см. файл примера ).

Задача1 - текстовые значения

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

Решение1

Создадим небольшую табличку со статусами работ в диапазоне Е6:Е9 .

Выделим диапазон ячеек А7:С17 , содержащий перечень работ, и установим через меню Главная/ Цвет заливки фон заливки красный (предполагаем, что все работы изначально находятся в статусе Не начата ).

Убедимся, что выделен диапазон ячеек А7:С17 (А7 должна быть ). Вызовем команду меню Условное форматирование/ Создать правило / Использовать формулу для определения форматируемых ячеек .

  • в поле «Форматировать значения, для которых следующая формула является истинной » нужно ввести =$C7=$E$8 (в ячейке Е8 находится значение В работе ). Обратите внимание на использоване ;
  • нажать кнопку Формат ;
  • выбрать вкладку Заливка ;
  • выбрать серый цвет;
  • Нажать ОК.

ВНИМАНИЕ : Еще раз обращаю внимание на формулу =$C7=$E$8 . Обычно пользователи вводят =$C$7=$E$8 , т.е. вводят лишний символ доллара.

Нужно проделать аналогичные действия для выделения работ в статусе Завершена . Формула в этом случае будет выглядеть как =$C7=$E$9 , а цвет заливки установите зеленый.

В итоге наша таблица примет следующий вид.

Чтобы быстро расширить правила Условного форматирования на новую строку в таблице, выделите ячейки новой строки (А17:С17 ) и нажмите . Правила будут скопированы в строку 17 таблицы.

Задача2 - Даты

Предположим, что ведется журнал посещения сотрудниками научных конференций (см. файл примера лист Даты ).

К сожалению, столбец Дата посещения не отсортирован и необходимо выделить дату первого и последнего посещения каждого сотрудника. Например, сотрудник Козлов первый раз поехал на конференцию 24.07.2009, а последний раз - 18.07.2015.

Сначала создадим формулу для условного форматирования в столбцах В и E. Если формула вернет значение ИСТИНА, то соответствующая строка будет выделена, если ЛОЖЬ, то нет.

В столбце D создана =МАКС(($A7=$A$7:$A$16)*$B$7:$B$16)=$B7 , которая определяет максимальную дату для определенного сотрудника.

Примечание: Если нужно определить максимальную дату вне зависимости от сотрудника, то формула значительно упростится =$B7=МАКС($B$7:$B$16) и формула массива не понадобится.

Теперь выделим все ячейки таблицы без заголовка и создадим правило . Скопируем формулу в правило (ее не нужно вводить как формулу массива!).

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

Для этого используйте формулу =И($B23>$E$22;$B23<$E$23)

Для ячеек Е22 и Е23 с граничными датами (выделены желтым) использована $E$22 и $E$23. Т.к. ссылка на них не должна меняться в правилах УФ для всех ячеек таблицы.

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

Таким образом, правило УФ например для ячейки А27 будет выглядеть =И($B27>$E$22;$B27<$E$23) , т.е. А27 будет выделена, т.к. в этой строке дата из В27 попадает в указанный диапазон (для ячеек из столбца А выделение все равно будет производиться в зависимости от содержимого столбца В из той же строки - в этом и состоит "магия" смешанной адресации $B23).

А для ячейки В31 правило УФ будет выглядеть =И($B31>$E$22;$B31<$E$23) , т.е. В31 не будет выделена, т.к. в этой строке дата из В31 не попадает в указанный диапазон.