В этой статье вы найдете 13 примеров как изменить цвет фона ячеек в зависимости от значения в Excel. Кроме того, вы узнаете, как использовать формулы Excel для изменения цвета пустых ячеек или ячеек с ошибками в формулах.
Всем известно, что изменить цвет фона отдельной ячейки или диапазона данных в Microsoft Excel легко, просто нажав кнопку « Цвет заливки » 
Ранее мы уже рассмотрели, как создать правило условного форматирования. Рекомендую ознакомиться с этим материалом. Ссылку на него вы также можете найти в конце этой статьи.
А сейчас на простых примерах мы рассмотрим, как в Excel можно автоматически выделить цветом интересующие нас данные.
Цветовые шкалы и гистограммы.
Ваши показатели в таблице могут быть гораздо более наглядными, если сопроводить их диаграммой. Просто выберите необходимый стиль, как это показано на рисунке ниже.
Думаю, с настройками вы сами разберетесь, несмотря на их большое количество. Но согласитесь, что получается весьма информативно и красиво. Гораздо легче ориентироваться в большом количестве цифр.
Еще одна полезная возможность – использование цветовой шкалы. Здесь вы можете установить, как будет меняться оттенок фона по направлению от меньшего к большему.
Чем более насыщенный и темный фон, тем больше число в таблице. Как видите, здесь легко определить, какие товары и когда продаются лучше всего.
Как можно использовать значки в ячейках?
А вот еще один интересный вариант, при помощи которого можно наглядно показать динамику изменений показателей. К примеру, у нас есть отчет о состоянии исполнения каких-то проектов или заданий. Как обычно, выполнение показано в процентах. Можно попробовать этим процентам придать больше наглядности.
Итак, вновь переходим в меню. Думаю, мы это уже умеем делать 😊. Далее выбираем первый пункт (1) – форматировать на основании значений. Далее в меню «Стиль формата» (2) ищем пункт «Наборы значков». Выберите тот набор, который вам больше нравится. (3) Далее устанавливаем правила для каждого значка. Думаю, здесь для вас ничего сложного не будет. Я установил границы в 25,50 и 75 процентов (4). Здесь же вы можете выбрать индивидуальное оформление каждого значка.
Результат вы видите на картинке вверху. При желании можете активировать соответствующий чекбокс, чтобы показывать только значок без числа.
Как выделить цветом с ячейки с текстом?
Если нам нужно изменить цвет ячейки в зависимости от того, содержится в ней текст или число? Действуем по прежней схеме.
Выделяем A1:B10, затем щелкнем на вкладке Главная — 
– Создать правило – Использовать формулу.
Она будет выглядеть так:
=ЕТЕКСТ(A1)
Функция ЕТЕКСТ проверяет на наличие именно текста. Если условие выполнено, клетка будет закрашена.
Просто, не правда ли?
Изменяем цвет ячеек с числами.
Как закрасить ячейку с числом по условию? Мы должны изменить цвет ячейки в зависимости от значения в определенной клетке таблицы Эксель.
Естественно, действуем по стандартной схеме: выделяем область форматирования, затем используем выражение:
=СЧЁТЕСЛИ(A3;$A$1)=1
Содержимое A1 подставляется в качестве условия в формулу СЧЕТЕСЛИ. Результатом может быть только ноль или единица. А единица соответствует логическому ИСТИНА. Вот тогда-то и применяется установленный нами формат (в нашем случае – белый шрифт на синем фоне). И так перебираем все содержимое нашего диапазона.
Подобное выделение можно производить по столбцу, чтобы привлечь внимание к определенным цифрам (например, процент выполнения плана продаж). И, что важно, условие можно менять динамически – для этого не нужно корректировать никакие формулы, достаточно просто изменить текст условия в нужной клетке таблицы (в нашем случае - в A1).
Изменение цвета ячейки по ее начальным буквам.
При помощи этой функции даже можно организовать своего рода поиск по словам в таблице. Чаще всего фамилию мы ищем по начальным буквам. Сделаем так, чтобы позиции с текстом, начинающимся с определенных букв, подсвечивались.
Определим, где мы будем выделять найденное – в A3:F19. Далее в правиле условного форматирования используем выражение:
=ЛЕВСИМВ(A3;ДЛСТР($A$1))= $A$1
Поясним, как это работает. В A1 находится условие поиска – первые несколько букв слова. При помощи формулы ДЛСТР($A$1) определяем длину этой строки, то есть сколько букв в ней. Затем при помощи функции ЛЕВСИМВ в каждой из ячеек нашего диапазона берем рассчитанное ранее количество букв начиная с первой слева, и сравниваем с A1. То есть, «отрезаем» слева кусочек такой же длины, как А1, и сравниваем с ним. Если есть совпадение, тогда выделяем их.
Как видите, совершенно не сложно, но может быть достаточно полезным.
Выделяем ячейки, содержащие более 1 слова.
Немного усложним предыдущее задание. Первоначальный порядок действий – то же, что и ранее. Вся хитрость заключается в применении специальной формулы, при помощи которой мы выделим текст, состоящий более чем из 2 слов.
Правило условного форматирования здесь будет такое:
=ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(A1;" ";""))>0
Разберем, как это работает. Логика здесь не сложная:
- Между словами всегда есть пробелы.
- Но, поскольку пробелы в начале и в конце текста нам не нужны, избавляемся от них при помощи функции СЖПРОБЕЛЫ(А1).
- Подсчитаем количество символов в получившемся в п.2 тексте при помощи ДЛСТР. Запишем ДЛСТР(СЖПРОБЕЛЫ(A1)).
- Удалим все пробелы из текста при помощи функции ПОДСТАВИТЬ, которая заменит пробел на пустой символ “”, то есть фактически удалит его: ПОДСТАВИТЬ(A1;" ";"")
- Считаем количество символов в тексте, получившемся в п.4, используя функцию ДЛСТР: ДЛСТР(ПОДСТАВИТЬ(A1;" ";"")).
- Сравниваем результаты, получившиеся в п.3 и п.5 путем их вычитания. Если результатом будет ноль, значит текст состоял из одного слова. Если получится число больше нуля, значит, были пробелы между словами. И, следовательно, текст состоял более чем из одного слова.
Что нам и было необходимо. Их и закрасим.
Выделение лишних пробелов.
Еще один небольшой лайфхак для проверки правильности ввода данных. Довольно распространенная проблема – лишние пробелы в начале или в конце текста. Это затрудняет поиск нужной информации, ее группировку и сравнение данных. Лишний пробел незаметен, но на самом деле это уже иной текст. В результате может появиться насколько одинаковых с виду, но на самом деле разных наименований товаров и т.п.
Итак, давайте постараемся указать на подобные неточности обычным для нас способом – изменением внешнего вида.
Поскольку проверять мы будем в D4,D6, D8 и D10, выделим их и правило форматирования запишем в виде формулы
=СЖПРОБЕЛЫ(D4)<>D4
Функция СЖПРОБЕЛЫ убирает из текста начальные и конечные пробелы. И если после такого «сжатия» оказалось, что текст «до» не совпадает с текстом «после», значит, имеются лишние пробелы в начале или в конце. Такие данные и будут соответствующим образом помечены.
Достаточно просто, но весьма полезно.
Подсветка дат и сроков.
Довольно часто приходится выделять определенные даты, чтобы, к примеру, не пропустить предстоящие мероприятия, подготовить будущие отгрузки и т.д. И, конечно же, нужно делать это при помощи программы, а не вручную.
Начнем с простого способа. У нас имеется множество дат, из которых нам нужно выбрать и выделить те, которые принадлежат к прошлому месяцу. Для этого, как и ранее, выбираем инструмент - Правила выделения ячеек – Дата. И далее из раскрывающегося списка выбираем нужную нам опцию, а также стиль оформления.
Все достаточно просто, но список вариантов здесь весьма ограничен. Поэтому попробуем второй, более сложный, но зато и более универсальный способ. Как вы уже, наверное, догадались, вновь будем использовать формулы.
Итак, обратите внимание на дату отгрузки.
Если она в прошлом и товар уже поставлен – можно не волноваться. Если она в будущем – значит, мы должны держать вопрос на контроле и не забыть организовать поставку к указанному сроку. И, наконец, если дата отгрузки совпадает с сегодняшней или с завтрашней, то надо бросать все дела и заниматься именно этой партией в данный момент (наивысший приоритет).
Таким образом, мы имеем 3 группы условий, которые сразу же запишем в виде правил условного форматирования:
- Сегодняшняя или завтрашняя дата (1):
=ИЛИ($E5-$C$2=1;$E5=$C$2)
- Будущая дата (2):
=$E5>$C$2
- Прошедшая дата – все остальные.
Обратите внимание на порядок следования формул. Формула, находящаяся выше, имеет более высокий приоритет, поскольку выполняется раньше. Поэтому мы в начале выделяем только сегодняшнюю и завтрашнюю даты, затем – все остальные в будущем. Если ваш файл выглядит не так, как на рисунке, при помощи клавиш «вверх-вниз» (3) просто измените порядок расположения условий.
Ну а с датами, которые уже прошли, мы просто ничего не делаем. Они остаются «естественного» цвета.
И еще один пример. Выделим выходные дни.
В столбце А нашей таблицы находятся даты. Выделяем все их при помощи мыши, затем уже знакомым нам способом используем формулу для определения условия форматирования.
=ЕСЛИ(ЕПУСТО(A1);ЛОЖЬ;ИЛИ(ДЕНЬНЕД(A1;2)=6;ДЕНЬНЕД(A1;2)=7))
Давайте разбираться, как это работает.
Если клетка пустая, то возвращаем ЛОЖЬ и не меняем ее вид. В этом нам поможет функция ЕПУСТО, которая станет условием в функции ЕСЛИ.
=ЕСЛИ(ЕПУСТО(A1);ЛОЖЬ;[условие если не пустая])
В случае, коли она не пустая, тогда проверяем выполнение одного из двух условий:
ДЕНЬНЕД(A1;2)=6 и ДЕНЬНЕД(A1;2)=7
Функция ДЕННЕД с аргументом 2 помогает нам определить номер дня недели по дате. 6 и 7 день – выходные в привычном для нас исчислении (суббота и воскресенье).
Объединяем эти два выражения при помощи ИЛИ, что означает, что нам достаточно выполнения хотя бы одного из них. Получается
ИЛИ(ДЕНЬНЕД(A1;2)=6;ДЕНЬНЕД(A1;2)=7)
В итоге, выходные дни будут выделены так, как мы определим при помощи кнопки Формат.
Чтобы перепроверить себя, давайте используем столбец В. В B1 запишите формулу =А1 и скопируйте ее вниз для всех дат. Первоначально получится копия столбца А.
Чтобы не усложнять таблицу формулами, давайте используем формат чисел, чтобы как-то иначе представить эти даты.
Выделите их и нажмите комбинацию клавиш CTRL и 1. В появившемся окне выберите «Все форматы» и в поле «Тип» впишите 4 латинских буквы d (как на рисунке). И вот у вас дата преобразована в день недели без всяких формул!
И, как можете сами убедиться, что нами выделены именно субботы и воскресенья.
Как скрыть содержимое ячейки по условию?
Частенько при заполнении различных форм необходимо дождаться полного ввода всех данных, и только после этого показывать результат. Это можно сделать при помощи логических выражений и функции ЕСЛИ, когда вычисления будут выполняться только при определенном условии.
Разберем небольшой пример:
Итог появляется только тогда, когда заполнены все клетки. И вот как сделана строка итогов:
Обратите внимание, что изначально применен белый шрифт на белом фоне. А затем после заполнения данных по всем кварталам мы его изменим, чтобы число стало видимым.
Для диапазона B6:C6 установите желаемый фон ячеек, а цвет шрифта установите на «Авто» либо принудительно установите белый.
Формула правила форматирования:
=СЧЁТ($C$2:$C$5)=4
просто подсчитывает количество чисел в диапазоне. Если введены все четыре, то изменится оформление итогов и они станут видны. А в обычном состоянии вы их просто не увидите из-за белого цвета шрифта.
Подсветка ячеек с формулами.
Рассмотрим еще один подход, который поможет проконтролировать правильность ввода данных. Предположим, у нас есть числовые данные. Необходимо проверить, не получены ли какие-либо из них при помощи формул, поскольку все они должны быть введены вручную.
Проверим наличие формулы в ячейке при помощи функции ЕФОРМУЛА (ISFORMULA).
=ЕФОРМУЛА(B2:D13)
Обратите внимание, что абсолютные ссылки здесь не нужны.
В результате клетки, содержащие формулы, будут выделены.
Возможно, больший интерес представляет альтернативный вариант этой формулы, который позволит выделить места, где руками введены какие-то цифры или текст без использования формул.
Используем функцию НЕ (NOT), которая изменит логический результат на противоположный:
=НЕ(ЕФОРМУЛА(B2:D13))
Теперь мы выделили области, где нет формул, а данные просто введены вручную. Возможно, это будет полезно в таблицах с большим количеством формул и ссылок. И если одна из них будет заменена числом, то при последующем изменении данных в таблице многие расчеты окажутся неверными.
Чтобы попытка пользователя заменить формулу числом была быстро обнаружена, такой метод подойдет очень неплохо.
Выделение пустых ячеек либо с ошибками.
В MS Excel цвет ячейки бывает полезно изменить не только в зависимости от значения, но и если никакого значения нет либо вовсе возникла какая-то ошибка. Часто бывает необходимо, чтобы на подобные случаи в таблице было обращено особое внимание. В особенности это касается ошибок. Сделать это достаточно просто.
Выделяем всю таблицу, и затем вновь используем формулу в правиле условного форматирования. Нам будут нужны две функции: ЕОШИБКА (IsError) и ЕПУСТО (IsBlank).
Можно создать два условия:
=ЕОШИБКА($B$2:$M$25)
=ЕПУСТО($B$2:$M$25)
Либо объединить их в одно при помощи ИЛИ:
=ИЛИ(ЕОШИБКА($B$2:$M$25);ЕПУСТО($B$2:$M$25))
Естественно, диапазоны здесь вы указываете свои.
Нажмите кнопку «Формат» и выберите подходящий для вас вариант оформления.
Подсветка недопустимых значений.
Когда часто приходится вводить в таблицу повторяющиеся данные, то обычно прибегают к помощи выпадающего списка.
Как создать выпадающий список в Excel - читайте подробнее.
Можно применить защиту листа и запретить вводить какие-либо данные в определенные позиции. Однако, можно просто закрасить клетку, в которую введено неверное значение. Так мы подадим сигнал пользователю о том, что информация записана с ошибкой.
Для этого нам в первую очередь понадобится список допустимых значений. Думаю, лучше его разместить на отдельном листе рабочей книги, чтобы он не мешал основным данным и при этом всегда был под рукой. Назовем этот лист City и запишем там допустимые названия городов. Вот примерно так:
Итак, на втором листе City есть список допустимых названий.
Мы должны изменить цвет D5 на красный, если выполняется 2 условия:
1. значение не совпадает с допустимыми;
2. она не пустая.
Чтобы проверить первое условие, в правиле условного форматирования можно использовать функцию СЧËТЕСЛИ (COUNTIF).
Она определяет, сколько раз записанное значение встречается в эталонном списке. Если ноль раз, то значит его там нет, и то, что введено, является недопустимым, нужно исправить.
=СЧËТЕСЛИ(D5;City!A1:A6)
Чтобы проверить второе условие, используем функцию ЕПУСТО (ISBLASNK), которая проверяет, является ли ячейка пустой, либо там что-то записано.
=ЕПУСТО(D5)
Но это проверка на то, что она пустая. Чтобы сделать проверку на то, что непустая, используем функцию НЕ (NOT). Она меняет результат выполнения логической функции на противоположное: ИСТИНА на ЛОЖЬ и наоборот. Выглядит проверка "от обратного" так:
=НЕ(ЕПУСТО(D5))
и чтобы оба эти условия выполнялись, объединим их функцией И (AND)
=И(НЕ(ЕПУСТО(D5));СЧЁТЕСЛИ(City!A1:A6;D5)=0)
Если мы введем в D5 правильное название, заливка красным исчезнет.
Хороший способ, чтобы контролировать правильность ввода. Но как по мне, то выпадающий список для контроля правильности ввода будет получше. Рекомендую:
Меняем вид ячейки в зависимости от прочих ячеек.
До сих пор мы разбирали, как изменить оформление во связи с информацией в самой ячейке таблицы. А если нам необходимо изменить цвет или заливку ячейки в Excel в зависимости от того, как меняются данные в соседних клетках?
Давайте разберем еще один интересный вариант выделения столбца в таблице. Итак, у нас есть данные о заказах от нескольких покупателей в течении года.
Обратите внимание: в первую строку мы вводим именно даты, а не пишем руками название месяца и года. Затем применяем к B1:M1 специальный формат представления даты.
Для этого выделите этот диапазон при помощи мыши и затем в меню Главная – Число – Формат откройте выпадающий список с всевозможными форматами. Выберите последний пункт «Другие числовые форматы». Откроется окно, как показано на картинке. Или же просто нажмите комбинацию клавиш CTRL+1.
Слева в окошке «Числовые форматы» выберите в самом низу «все форматы». И затем в поле «Тип» впишите:
mmmm yyyy
Нажимаем ОК и получаем новый формат даты.
Теперь давайте выделим текущий месяц.
Выбираем все столбцы нашей таблицы с датами. В данном случае это диапазон B2:M6. Затем открываем уже знакомое нам меню функции "Условное форматирование".
Вновь используем формулу, чтобы определить условия:
=МЕСЯЦ(B$1)=МЕСЯЦ(ТДАТА())
Кстати, текущий месяц на момент написания этого материала – декабрь, то есть 12-й.
Обратите внимание на то, что используется абсолютная ссылка на первую строку, в которой записаны даты из каждого месяца. И если месяц из этой даты соответствует номеру текущего месяца, то этот столбец мы закрасим в голубой.
Теперь пойдем дальше, к более сложной задаче. Проверим в каждой из строк таблицы, были ли у покупателя заказы в последние 3 месяца. Если заказов не было, то имя такого покупателя выделим.
Посмотрите, как это будет выглядеть в декабре.
Изменять заливку мы будем в A2:A6. Но проверять выполнение условия будем в диапазоне B2:M6.
Итак, выделяем мышью A2:A6 и открываем меню – Создать правило – Использовать формулу.
Её запишем так:
=СЧЁТЕСЛИ(СМЕЩ($A2;0;МЕСЯЦ(ТДАТА())-2;1;3);">0")=0
Здесь при помощи функции СМЕЩ (OFFSET) мы получаем диапазон данных нужного месяца, смещаясь от ячейки А2 справо на число столбцов, определяемое текущей датой.
Сразу заметим, что можно вместо функции ТДАТА() использовать СЕГОДНЯ(). Обе они возвращают текущую дату, только ТДАТА еще возвращает и время.
=СЧЁТЕСЛИ(СМЕЩ($A2;0;МЕСЯЦ(СЕГОДНЯ())-2;1;3);">0")=0
Как видите, для подсчета количества заказов мы использовали функцию СЧЕТЕСЛИ. В качестве первого аргумента ей нужно указать диапазон, а в качестве второго – условие. И она подсчитает, сколько раз в указанном диапазоне данных выполнялось это условие.
В качестве условия используем “>0”, поскольку, если заказ был, то какая-то сумма должна быть по нему указана. Кстати, не забудьте это условие заключить в двойные кавычки, чтобы не было ошибки.
Теперь разберемся с первым условием. Для наглядности запишем его здесь:
СМЕЩ($A2;0;МЕСЯЦ(ТДАТА())-2;1;3)
Чтобы возвратить диапазон данных, используем функцию СМЕЩ. Схема ее работы показана на рисунке выше. Перво-наперво нужно указать начальную точку, с которой мы начнем все действия. Пусть это будет $A2. Вновь мы делаем абсолютную ссылку на столбец, чтобы при проверке условия следующей была A3, затем A4 и так далее.
Далее мы должны указать, на сколько строк вниз нужно опуститься. Поскольку нас интересует текущая строка, то ставим 0.
Теперь нужно переместиться вправо на какое-то количество столбцов, где и будет начало (верхний левый угол) нашего диапазона. Поскольку кроме текущего месяца нас интересуют заказы еще в двух предыдущих (так всего будет 3 месяца), то смещаемся вправо на количество столбцов, равное номеру текущего месяца минус 2. Если у нас сейчас 12-й месяц, то делаем 10 шагов вправо и попадаем на октябрь, то есть столбец К. Это и есть начало нашего диапазона данных.
Следующее – указываем высоту диапазона. Поскольку интересует только текущая строка, то ставим 1.
Ну и, наконец, нужно определить, на сколько столбцов вправо будет продолжаться наш подсчет заказов. Ответ следует из нашего условия – на 3 месяца, то есть берем К, L, M. С октября по декабрь включительно – будет K2:M2. Поскольку заказов нет, то СЧЕТЕСЛИ возвратит 0. В результате условие сработает, и А2 окрасится красным. А в третьей строке в K3:M3 есть данные, поэтому А3 останется в прежнем виде.
И еще одно замечание: в январе и феврале подобное наше правило работать не будет, поскольку необходимые для проверки 3 месяца еще не прошли.
Итак, мы с вами разобрали, как в Экселе менять цвет ячейки в зависимости от значения. Если остались вопросы - пишите их в комментариях. Постараюсь ответить.
а как сделать, чтоб клиент который заказал на завтра продукцию закрашивался по условию в столбце завтра?
https://docs.google.com/spreadsheets/d/1YAjiBms2paIEEKHEF4lWHA8Z6ygcKzGkPveA7a6c0_A/edit?usp=sharing
Попробуйте добавить в формулу правила условного форматирования проверку на дату
ЕСЛИ(B$3=(СЕГОДНЯ()+1),ИСТИНА,ЛОЖЬ)