Цвет ячейки в зависимости от значения в Excel

Процедура изменения цвета ячеек в зависимости от содержимого

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

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

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

Способ 1: условное форматирование

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

Давайте посмотрим, как этот метод работает на конкретном примере. У нас есть таблица доходов компании, где данные разбиты по месяцам. Мы должны выделить разными цветами те статьи, где сумма дохода меньше 400 000 рублей, от 400 000 до 500 000 рублей и превышает 500 000 рублей.

1. Выбираем столбец, где размещена информация о доходах компании. Далее переходим на вкладку Главная. Нажмите кнопку «Условное форматирование», расположенную на ленте в панели инструментов «Стили». В открывшемся списке выберите пункт «Управление правилами…».
2. Откроется окно «Правила условного форматирования». Поле «Показать правила форматирования для» должно быть установлено на «Текущий фрагмент». По умолчанию он там должен быть указан, но на всякий случай проверьте и, в случае отклонения, измените настройки согласно приведенным выше рекомендациям. Затем нажмите на кнопку «Создать правило…».
3. Откроется окно «Создать правило форматирования». В списке типов правил выберите пункт «Форматировать только те ячейки, которые содержат». В блоке описания правила в первом поле переключатель должен стоять в положении «Значения». Во втором поле установите переключатель в положение «Меньше». В третьем поле укажите значение, элементы листа, содержащие значение меньше указанного, будут окрашены в определенный цвет. В нашем случае это значение будет 400 000. После этого нажмите на кнопку «Форматировать…».
4. Откроется окно Формат ячеек. Перейдите на вкладку «Заполнить». Выбираем цвет заливки, которым хотим выделить ячейки, содержащие значение меньше 400 000. После этого нажимаем кнопку «ОК» внизу окна.
5. Возвращаемся в окно создания правила форматирования и там же нажимаем кнопку «ОК».
6. После этого действия нас снова перенаправит в Менеджер правил условного форматирования. Как видите, одно правило уже добавлено, но нам нужно добавить еще два. Поэтому снова нажмите на кнопку «Создать правило…».
7. И снова мы приходим к окну создания правила. Перейдите в раздел «Форматировать только те ячейки, которые содержат». В первое поле этого раздела поставьте параметр «Значение ячейки», а во второе установите переключатель в положение «Между». В третьем поле необходимо указать начальное значение области форматирования элементов листа. В нашем случае это число 400 000. В четвертом вводим конечное значение для этого диапазона. Это будет 500 000. После этого нажмите кнопку «Форматировать…».
8. В окне форматирования снова переходим на вкладку «Заливка», но на этот раз уже выбираем другой цвет, а затем нажимаем кнопку «ОК».
9. Вернувшись в окно создания правила, также нажмите кнопку «ОК».
10. Как видите, мы уже создали два правила в Менеджере правил. Таким образом, осталось сделать третью. Нажмите на кнопку «Создать правило».
11. В окне создания правила снова перейдите к разделу «Форматировать только те ячейки, которые содержат». В первом поле оставьте опцию «Значение ячейки». Во втором поле установите переключатель на полицию «Еще». В третье поле вводим число 500000. Затем, как и в предыдущих случаях, нажимаем на кнопку «Форматировать…».
12. В окне «Формат ячеек» снова перейдите на вкладку «Заливка». На этот раз мы выбираем цвет, отличный от предыдущих двух случаев. Производим щелчок по кнопке «ОК».
13. В окне создания правила еще раз нажмите кнопку «ОК».
14. Откроется Диспетчер правил. Как видите, все три правила созданы, поэтому нажимаем кнопку «ОК».
15. Теперь элементы таблицы окрашиваются в соответствии с заданными условиями и ограничениями в настройках условного форматирования.
16. Если мы изменим содержимое одной из ячеек, выйдя при этом за пределы одного из заданных правил, этот элемент листа автоматически изменит цвет.

Вы также можете использовать условное форматирование немного иначе, чем раскрашивание элементов листа.

1. Для этого после перехода из менеджера правил в окно форматирования остаемся в разделе «Форматировать все ячейки на основе их значений». В поле «Цвет» можно выбрать цвет, оттенками которого должны быть заполнены элементы на листе. Затем следует нажать на кнопку «ОК».
2. В Диспетчере правил также нажимаете на кнопку «ОК».
3. Как видите, после этого ячейки в столбце окрашиваются разными оттенками одного цвета. Чем больше значение, содержащее элемент листа, тем светлее оттенок, чем меньше оттенок, тем темнее.

Читайте также: Горячие клавиши Windows 10 для упрощения работы в системе

Где находится условное форматирование в Эксель?

Кнопка «Условное форматирование» находится на панели инструментов, на вкладке «Главная”:

Как создать несколько правил условного форматирования с заданным приоритетом

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

=$C2>9

Чтобы оба созданных нами правила работали одновременно, вы должны расположить их в правильном приоритете.

1. На вкладке «Главная» в разделе «Стили» нажмите «Условное форматирование» > «Управление правилами
2. В раскрывающемся списке Показать правила форматирования для выберите Этот лист. Если вы хотите изменить только настройки правил выбора, выберите параметр Текущий выбор).
3. Выберите правило форматирования, которое следует применить первым, и с помощью стрелок переместите его в начало списка. Это должно быть так:
   Нажмите OK, и линии в указанном фрагменте сразу изменят цвет, согласно формулам в обоих правилах.

Изменение цвета ячейки по начальным буквам.

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

Давайте определим, где мы хотим выделить это открытие — в A3:F19. Затем используйте выражение в правиле условного форматирования:

=ЛЕВО(A3;ЛЕВО($A$1))= $A$1

Давайте объясним, как это работает. A1 содержит поисковый запрос — первые буквы слова. С помощью DLSTR($A$1) мы определяем длину этой строки, то есть сколько в ней букв. Затем с помощью функции ВЛЕВО в каждой из ячеек нашего диапазона берем рассчитанное ранее количество букв, начиная с первой слева, и сравниваем с А1. То есть «отрезаем» слева кусок такой же длины, как А1, и сравниваем с ним. Если есть совпадения, выберите их.

Как видите, это совсем не сложно, но может оказаться весьма полезным.

Условное форматирование по значению другой ячейки

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

Чтобы создать условие для значения другой ячейки, выполните следующие действия:

• Выберите первую ячейку, чтобы назначить правило. Нажмите «Условное форматирование» на панели инструментов. Выберем условие «Меньше чем”.
• Во всплывающем окне введите ссылку на ячейку, с которой эту ячейку нужно сравнить. Выбираем формат. Нажмите кнопку «ОК”.

• Выделим левой кнопкой мыши ячейку, которой мы присвоили формат. Нажмите «Условное форматирование». В выпадающем меню выберите «Управление правилами» => нажмите кнопку «Редактировать правило”:

• В поле слева от всплывающего окна «очистить» ссылку от знака «$». Нажмите кнопку «ОК», а затем кнопку «Применить”.

• Теперь нам нужно присвоить пользовательский формат остальным ячейкам таблицы. Для этого выделите ячейку с назначенным форматом, затем нажмите «ролик» в левом верхнем углу панели инструментов и назначьте формат остальным ячейкам:

На скриншоте ниже цветом выделены данные, где курс стал ниже по сравнению с предыдущим периодом:

Рекомендации

При вводе рабочего статуса важно не ошибиться. Например, если вместо слова «Готово» пользователь введет «Готово», условное форматирование работать не будет.

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

Чтобы быстро распространить правила условного форматирования на новую строку в таблице, выберите ячейки новой строки (A17:C17) и нажмите CTRL+D. Правила условного форматирования будут скопированы в 17 строку таблицы.

Решение1

Создадим небольшую таблицу рабочих статусов в диапазоне E6:E9

.

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

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

.

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

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

Аналогичные действия необходимо выполнить для выбора работы в статусе «Выполнено». Формула в этом случае будет выглядеть как =$C7=$E$9 и установить зеленый цвет заливки.

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

Примечание. Условное форматирование имеет приоритет над обычным форматированием ячеек. Поэтому, если работа в статусе Завершена, она будет окрашена в зеленый цвет, несмотря на то, что ранее мы установили красный фон через меню Цвет Главная/Заливка

Подсветка дат и сроков.

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

Начнем с простого способа.

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

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

Так что обратите внимание на дату доставки.

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

Таким образом имеем 3 группы условий, которые сразу записываем в виде правил условного форматирования:

1. Дата сегодня или завтра (1):

=ИЛИ($E5-$C$2=1;$E5=$C$2)

• Будущая дата (2):

=$E5>$C$2

• Предыдущая дата — все остальные.

Обратите внимание на порядок формул. Формула, которая выше, имеет более высокий приоритет, потому что она выполняется раньше. Поэтому вначале выбираем только сегодняшнюю и завтрашнюю даты, потом — остальные в будущем. Если ваш файл не похож на тот, что на картинке, используйте клавиши вверх и вниз (3), чтобы изменить порядок условий.

Ну а с уже пройденными сроками мы просто ничего не делаем. Они сохраняют «естественный» цвет.

И еще один пример. Отложим выходные.

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

=ЕСЛИ(ICEПУСТО(A1),ЛОЖЬ,ИЛИ(ДЕНЬНЕД(A1,2)=6,ДЕНЬНЕД(A1,2)=7))

Давайте разберемся, как это работает.

Если ячейка пуста, вернуть FALSE и не менять ее внешний вид. В этом нам поможет функция ЕСЛИ ПУСТО, которая становится условием в функции ЕСЛИ.

=ЕСЛИ(ISNULL(A1),FALSE,указать, если не пусто)

Если он не пустой, проверяем выполнение одного из двух условий:

ДЕНЬ НЕДЕЛИ(A1;2)=6 и ДЕНЬ НЕДЕЛИ(A1;2)=7

Функция DENNED с аргументом 2 помогает нам определить номер дня недели по дате. 6-й и 7-й дни в обычном исчислении у нас нерабочие (суббота и воскресенье).

Эти два выражения мы объединяем с помощью ИЛИ, а это значит, что нам достаточно выполнить хотя бы одно из них оказывается

ИЛИ(ДЕНЬ НЕДЕЛИ(A1;2)=6;ДЕНЬ НЕДЕЛИ(A1;2)=7)

В результате праздники будут выбраны так, как мы определяем с помощью кнопки Формат.

Чтобы перепроверить себя, давайте воспользуемся столбцом B. В B1 напишите формулу =A1 и скопируйте ее для всех дат. Для начала вы получите копию столбца А.

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

Выберите их и нажмите комбинацию клавиш CTRL и 1. В появившемся окне выберите «Все форматы» и в поле «Тип» введите 4 латинские буквы d (как на рисунке). И вот вы перевели дату в день недели без всяких формул!

Гистограммы

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

1. Наведите указатель мыши на правило «Гистограмма» и выберите соответствующий тип отображения. По умолчанию предлагается 12 вариантов.
2. Это правило не имеет дополнительных настроек, поэтому сразу после применения вы видите сформированные гистограммы — от минимального до максимального значения диапазона.
3. Если вы выберете опцию «Другое правило», вы можете самостоятельно настроить, какие значения следует трогать и какие отступы должны к ним применяться. Настройки интуитивно понятны, так что не стесняйтесь экспериментировать с разными стилями оформления.

Выделение пустых ячеек либо с ошибками.

В MS Excel бывает полезно изменить цвет ячейки не только в зависимости от значения, но и в случае отсутствия значения или возникновения какой-либо ошибки. Часто необходимо уделять особое внимание таким вопросам в таблице. Особенно это касается ошибок. Сделать это довольно просто.

Выберите всю таблицу, затем повторно примените формулу в правиле условного форматирования. Нам понадобятся две функции: ISError (IsError) и ISBLANK (IsBlank).

Вы можете создать два условия:

=ОШИБКА($B$2:$M$25)

=ICEBLANK($B$2:$M$25)

Или объедините их в один, используя ИЛИ:

=ИЛИ(ОШИБКА($B$2:$M$25),NULL($B$2:$M$25))

Естественно, здесь вы указываете свои области.

Нажмите кнопку «Формат» и выберите подходящий вам вариант оформления.

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

Не все компании покупают специальные программы для ведения бизнеса. Многие люди используют MS Excel, потому что это хо.

Не все компании покупают специальные программы для ведения бизнеса. Многие используют MS Excel, потому что он хорошо подходит для больших информационных баз. Практика показала, что дальше наполнения столов дело редко идет. Таблица растет, информации становится больше и есть необходимость быстро выбрать только то, что нужно. В такой ситуации возникает вопрос, как выделить ячейку цветом в Excel при определенном условии, применить цветовые градиенты к строкам в зависимости от типа или имени провайдера, сделать работу с информацией быстрой и удобной? Подробнее читайте ниже.

Закрасить ячейку по условию или формуле

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

Подсвечиваем строки с пустыми ячейками

Такие ситуации тоже часто имеют место. Как пометить строки с пустыми ячейками?

Пошаговые инструкции:

• Выделите свою тарелку;

• Нажмите «Главная» -> «Условное форматирование» -> «Создать правило…»;

• В открывшемся окне выберите последний вариант из списка;

• В поле описания функции введите =СЧЁТЕСЛИ($A2:$F2;»»)>0

• Установите оранжевый цвет в опции «Формат»;

• Подтверждать.

Вот результат:

Как можно использовать значки?

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

Итак, вернемся к меню. Думаю, мы уже знаем, как это сделать. Затем выберите первый элемент (1) — форматирование на основе значений. Затем в меню «Формат стиля» (2) ищем пункт «Набор иконок». Выберите тот набор, который вам больше всего нравится. (3) Затем установите правила для каждого значка. Думаю, здесь для вас не будет ничего сложного. Я установил поля на 25,50 и 75 процентов (4). Здесь вы можете выбрать индивидуальный дизайн каждой иконки.

Результат вы можете увидеть на картинке выше. При желании вы можете активировать соответствующий флажок, чтобы отображалась только иконка без номера.

Инструкция для Excel 2010

ВКЛЮЧИТЕ СУБТИТРЫ!

Наборы значков

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

1. откройте пакеты значков и проверьте все доступные. Существуют различные направляющие в виде стрелок, фигур разного цвета, индикаторов и рейтингов.
2. Для этого правила нет настройки по умолчанию, поэтому при выборе значки сразу появляются в ячейках. Я выбрал шкалу с несколькими делениями, которая заполняется в зависимости от размера значения в текущем диапазоне. Вы можете протестировать абсолютно все наборы иконок.
3. В окне настроек этого правила обязательно укажите тип, затем выберите один из типов значков и условия, при которых они будут появляться. Это позволит вам игнорировать любые значения, которые вы не хотите выделять.

Выделение диапазонов несколькими цветами по условию

Другой вариант: нам нужно выделить разные области разными цветами в столбце.

Например, меньше 30 — это один диапазон, а больше 40 — другой диапазон. Выделить столбец и перейти через пункт «условное форматирование» к «правилам выделения ячеек» — «меньше». В строке «форматировать ячейки меньше чем…» установите значение 30 и выберите цвет красного текста и выделения ячеек.

Нажимаем ОК. Теперь повторяем то же самое, но уже выделяем элемент не «меньше», а «больше».

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

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

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

Как это сделать в Excel 2007

ВКЛЮЧИТЕ СУБТИТРЫ!
Выделите ячейки с ценами ордеров и, нажав на стрелочку рядом с кнопкой «Условное форматирование», выберите «Создать правило».

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

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

Выберите ячейки со статусами заказов и создайте новое правило. На этот раз мы используем второй вариант, позволяющий проверить содержимое ячейки. Выберите «Текст», «содержит» и введите слово «Завершено». Выставляем зеленый цвет, подтверждаем, и выполненная работа становится зеленой.

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

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

Вы заметили, что статусы устанавливаются путем выбора из выпадающего списка значений? Как создавать такие списки, мы рассказывали в инструкции «Как создать выпадающий список в Excel».

Как изменить цвет строки на основании текстового значения одной из ячеек

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

• Если дата доставки заказа находится в будущем (значение Срок выполнения через X дней), заполнение таких ячеек должно быть оранжевым;
• Если заказ доставлен (значение доставлено), то заполнение таких ячеек должно быть зеленым;
• Если дата доставки заказа в прошлом (Due value), то заполнение таких ячеек должно быть красным.

И, конечно же, цвет заливки ячейки должен меняться при изменении статуса заказа.

С формулой для поставленной и подлежащей оплате стоимости все понятно, она будет аналогична формуле из нашего первого примера:

=$E2=»Доставлено»
=$E2=»Просрочено»

Задача кажется более сложной для заказов, которые должны быть доставлены в течение X дней (значение Срок выполнения через X дней). Мы видим, что срок доставки для различных заказов составляет 1, 3, 5 и более дней, а значит, вышеприведенная формула здесь не применима, так как нацелена на точное значение.

В этом случае удобно использовать функцию ПОИСК и написать следующую формулу для поиска частичного совпадения:

=ПОИСК(«Срок выполнения»,$E2)>0
=ПОИСК(«Срок выполнения»,$E2)>0

В этой формуле E2 — это адрес ячейки, исходя из значения которой мы будем применять правило условного форматирования; знак доллара $ необходим для применения формулы ко всей строке; условие «>0» означает, что правило форматирования будет применено, если указанный текст (в нашем случае «Срок выполнения») будет найден.

Подсказка: Если в формуле используется условие «>0», строка будет выделяться цветом всякий раз, когда указанный текст будет найден в ключевой ячейке, независимо от того, где он находится в ячейке. В примере таблицы на рисунке ниже столбец «Доставка» (столбец F) может содержать текст «Срочно, к оплате через 6 часов» (что означает «Срочно, к оплате в течение 6 часов»), и эта строка также будет окрашена.

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

=ПОИСК(«Срок выполнения»,$E2)=1
=ПОИСК(«Срок выполнения»,$E2)=1

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

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

Правила отбора первых и последних значений

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

1. Для этого в том же меню условного форматирования наведите курсор на второе правило и ознакомьтесь с его типами. В предыдущем пункте я уже перечислил их все, так что вам остается только выбрать тот, который вам подходит. Хочу уточнить, что под «первыми 10 элементами» подразумевается не их порядок в таблице, а сам результат, записанный в ячейку.
2. Хотя правила называются «Первые 10…», «Последние 10…», вы сами выбираете количество ячеек для форматирования и указываете правильное число в счетчике. После этого нужно расширить список вариантов оформления и выбрать среди них подходящий.
3. Я настроил подсветку первых шести элементов, а это значит, что теперь будут подсвечиваться первые 6 самых больших чисел в моей выборке.
4. Вариант «Выше среднего» или «Ниже среднего» не имеет дополнительных настроек, так как среднее значение диапазона определяется автоматически. Вам просто нужно выбрать стиль форматирования.
5. Как видно на изображении ниже, программа сама определила, какие значения в диапазоне выше среднего, и выделила их выбранным мной цветом.

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

Применение условного форматирования по другому столбцу

Точно так же вы можете сделать выбор на основе предварительно введенного значения в столбце рядом с ним.

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

В открывшемся окне в строке «формат ячеек меньше чем…» нужно ввести значение 50. Нажмите на это число и в этой строке появится формула ячейки.

При этом ячейки меньше 50 также будут заштрихованы. Выберите нужную визуализацию в строке рядом с ней и нажмите OK. В итоге получаем готовое форматирование.

Вставляем отделяющие линии между группами строк.

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

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

Для этого сначала выделите всю нашу выборку данных.

Обратите внимание на следующее! Мы не выбираем первый заголовок в таблице, мы начинаем с данных!

В нашем случае выберите A3:G33.

Далее действуем по уже подготовленной схеме. В меню условного форматирования выберите использование формулы (1). Затем пишем само правило:

=$B3<>$B2

Другими словами, мы проверяем, равна ли наша текущая дата предыдущей. Если не равны, мы перешли на новый день. Поэтому наше текущее положение должно быть выделено. Выберите формат (3). Тип границы — линия (4). Он будет использоваться вдоль верхнего края (5).

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

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

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

Подсвечиваем строки с нужным нам числами

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

Теперь рассмотрим числовое значение. В принципе здесь все почти так же.

Допустим, у нас есть одна и та же тарелка. Только теперь нам нужно выбрать строки, в которых количество товаров больше или равно 15.

Пошаговые инструкции:

• Выделите свою тарелку;

• Нажмите «Главная» -> «Условное форматирование» -> «Создать правило…»;

• В открывшемся окне выберите последний вариант из списка;

• В поле описания функции введите =$D2>=15;

• Нажмите «Формат…» и укажите цвет выделения;

• Подтверждать.

Посмотрим на результат:

Как видите, все получилось.

Аналогично, кстати, и с датами.

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

=$A2>ДАТА(2021;01;01)

Условное форматирование даты в Excel

Выберите диапазон дат.

Используем на нем «UV» — «Дата».

В открывшемся окне отображается список доступных условий (правил):

Выберите нужное (например, за последние 7 дней) и нажмите ОК.

Красным цветом выделены ячейки с датами за последнюю неделю (дата написания статьи 02.02.2016).

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

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

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

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

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

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

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

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

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

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

Затем перейдите в ячейку C1 и введите следующую формулу:

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

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

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

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

Условное форматирование для сравнения двух столбцов.

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

Как найти и закрасить совпадающие ячейки в столбцах.

Вы можете воспользоваться специальным пунктом в категории «Условное форматирование — Повторяющиеся значения».

На изображении видно, что дубликаты выделены зеленым цветом. Думаю, здесь все довольно просто.

Выделение совпадений двух столбцов построчно.

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

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

Выберите, в каких ячейках вы хотите отметить попадания — в первой или во второй таблице. Я выделил B3:B25. То есть в первой таблице мы будем закрашивать те ячейки, которые дублируются во второй таблице.

Обратите внимание, что в формуле используется абсолютная адресация столбцов. Это необходимо для последовательного перебора значений, двигаясь вниз от B3 до B25.

Как найти и закрасить совпадения в нескольких столбцах.

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

Раскрасим те клетки в столбце B, которые хотя бы раз встречаются в G, H и I.

Диапазон форматирования: B3:B25. Выберите его и в меню — «Создать правило» выберите «Использовать формулу…»

Напишем правило условного форматирования:

=ИЛИ($B3=$G3;$B3=$H3;$B3=$I3)

Двигаемся последовательно сверху вниз и сравниваем каждую ячейку в столбце B со значениями в той же горизонтальной строке в G, H и I.

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

А если столбцов не 3 а допустим 10? Формула будет слишком громоздкой. Ведь нужно указать 10 критериев соответствия.

Есть более простой способ. Измените правило форматирования и используйте функцию СЧЁТЕСЛИ:

=ЕСЛИ(СЧЁТЕСЛИ($G3:$I3,$B3)>0,1,0)

COUNTIF определяет, как часто заданное значение встречается в диапазоне. Считаем, сколько раз значение из B3 встречается в таблицах G, H и I, то есть в $G3:$I3. Если совпадений больше одного, то правило срабатывает. Функция возвращает 1. При этом 1 в логическом выражении соответствует ИСТИНА, 0 — ЛОЖЬ. То есть, если count равен нулю, текущая позиция нашего столбца содержит уникальное значение, не встречающееся больше нигде в диапазоне поиска. Согласитесь, это гораздо практичнее, чем писать множество однотипных критериев.

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

Вот новое правило:

=ЕСЛИ(СЧЁТЕСЛИ($G$3:$I$25,$B3)>0,1,0)

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

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