Как изменить цвет ячейки на основе значения в Excel?
В этой статье вы найдете 13 примеров того, как изменить цвет фона ячеек на основе значения в Excel 2016, 2013 и 2010. Также вы узнаете, как использовать формулы Excel для изменения цвета пустых ячеек или ячеек с ошибками цвета формула.
Всем известно, что легко изменить цвет фона отдельной ячейки или диапазона данных в Microsoft Excel, просто нажав кнопку «Цвет заливки». Но что, если вы хотите изменить цвет фона всех ячеек с определенным значением? Кроме того, что, если вы хотите, чтобы он автоматически менялся вместе с изменением значения ячейки? Далее в этой статье вы найдете ответы на эти вопросы и несколько полезных советов, которые помогут выбрать правильный метод для каждой конкретной задачи.
Мы уже рассмотрели, как создать правило условного форматирования выше. Рекомендую прочитать этот материал. Вы можете найти ссылку в конце этой статьи.
А теперь на простых примерах мы увидим, как в Excel можно автоматически выделить интересующие нас данные.
Цветовые шкалы и гистограммы.
Ваши индикаторы на графике могут быть намного нагляднее, если вы сопроводите их графиком. Просто выберите нужный стиль, как показано на рисунке ниже.
Я думаю, что вы и сами откроете для себя настройки, несмотря на их большое количество. Но согласитесь, что получается очень познавательно и красиво. Гораздо проще ориентироваться в большом количестве цифр.
Еще одной полезной функцией является использование цветовой шкалы. Здесь вы можете установить, как фоновый тон будет меняться с низкого на высокий.
Чем насыщеннее и темнее фон, тем выше число в таблице. Как видите, легко определить, какие продукты продаются лучше всего и когда.
Как можно использовать значки?
А вот еще одна интересная опция, с помощью которой можно визуально отображать динамику изменения индикатора. Например, у нас есть отчет о статусе выполнения некоторых проектов или задач. Как обычно, завершение отображается в процентах. Вы можете попытаться сделать эти проценты более заметными.
Итак, вернемся к меню. Думаю, мы уже знаем, как это сделать. Затем выберите первый пункт (1) — форматирование по значению. Далее в меню «Формат стиля» (2) найдите пункт «Наборы иконок». Выберите набор, который вам больше всего нравится. (3) Затем установите правила для каждого значка. Думаю, здесь для вас не будет ничего сложного. Я установил поля на 25,50 и 75 процентов (4). Здесь вы можете выбрать индивидуальный дизайн каждой иконки.
Вы можете увидеть результат на изображении выше. При желании вы можете установить соответствующий флажок, чтобы отображать только значок без номера.
Как выделить с ячейки с текстом?
Что, если нам нужно изменить цвет ячейки в зависимости от того, содержит ли она текст или число? Работаем по вышеописанной схеме.
Выберите A1:B10, затем нажмите Главная — Создать правило — вкладка «Использовать формулу.
Это будет выглядеть так:
=ЭТЕКСТ(A1)
Функция ISTEXT проверяет наличие текста. Если условие выполнено, ячейка будет заполнена.
Просто, верно?
Изменяем цвет ячеек с числами.
Как раскрасить ячейку с числом по условию? Нам нужно изменить цвет ячейки на основе значения в конкретной ячейке в электронной таблице Excel.
Естественно, действуем по стандартной схеме: выделяем область форматирования, затем используем выражение:
=СЧЁТЕСЛИ(A3,$A$1)=1
Содержание A1 заменяется условием в формуле СЧЁТЕСЛИ. Результат может быть только ноль или один. А единице соответствует логическое ИСТИНА. Именно тогда применяется установленное нами форматирование (в нашем случае белый шрифт на синем фоне). И так классифицируем все содержимое нашего ассортимента.
Это выделение можно сделать по столбцам, чтобы привлечь внимание к определенным цифрам (например, процент выполненного плана продаж). И, что более важно, условие может быть изменено динамически; для этого не нужно корректировать никакую формулу, достаточно изменить текст условия в нужной ячейке таблицы.
Изменение цвета ячейки по начальным буквам.
С помощью этой функции можно даже организовать своеобразный поиск слов в таблице. Чаще всего мы ищем фамилию по начальным буквам. Давайте сделаем позиции с текстом, который начинается с выделенных определенных букв.
Определим, где будем подсвечивать найденное: в A3:F19. Далее в правиле условного форматирования используйте выражение:
=ЛЕВО(A3,ЛЕВО($A$1))= $A$1
Мы объясним, как это работает. A1 содержит поисковый запрос: первые буквы слова. С помощью DLSTR($A$1) мы определяем длину этой строки, т.е сколько в ней букв. Затем с помощью функции ВЛЕВО на каждой из ячеек нашего диапазона берем рассчитанное ранее количество букв, начиная с первой слева, и сравниваем с А1. То есть «отрезаем» слева кусок такой же длины, как А1, и сравниваем с ним. Если есть совпадение, выберите его.
Как видите, это совсем не сложно, но может оказаться весьма полезным.
Выделяем ячейки, содержащие более 1 слова.
Немного усложним предыдущую задачу. Начальная процедура такая же, как и раньше. Весь фокус заключается в применении специальной формулы, с помощью которой мы будем выделять текст, состоящий более чем из 2-х слов.
Правило условного форматирования здесь будет таким:
=ДЛИНА(ОТРЕЗАТЬ ПРОБЕЛЫ(A1))-ДЛИНА(ПОДСТАВИТЬ(A1,» «,»»))>0
Давайте посмотрим, как это работает. Логика тут не сложная:
- Между словами всегда есть пробелы.
- Но, так как нам не нужны пробелы в начале и конце текста, мы удаляем их с помощью функции CLIMP(A1).
- Посчитаем количество символов в тексте, полученном в пункте 2, с помощью DLSTR. Напишем DLSTR(TRIMSPACES(A1)).
- Убираем из текста все пробелы с помощью функции ПОДСТАВИТЬ, которая заменит пробел на пустой символ «», то есть удалит его: ПОДСТАВИТЬ(A1;» «;»»)
- Мы подсчитываем количество символов в тексте, полученном на шаге 4, с помощью функции DLSTR: DLSTR(SUBSTITUTE(A1;» «;»»)).
- Сравниваем результаты, полученные в пункте 3 и пункте 5, путем их вычитания. Если результат равен нулю, то текст состоял из одного слова. Если вы получили число больше нуля, значит, между словами были пробелы. И поэтому текст состоял более чем из одного слова.
Что нам и было нужно. Давайте раскрасим их.
Выделение лишних пробелов.
Еще одна маленькая хитрость для проверки правильности ввода данных. Довольно распространенная проблема — лишние пробелы в начале или конце текста. Это затрудняет поиск нужной информации, ее группировку и сравнение данных. Лишний пробел незаметен, но на самом деле это уже другой текст. В результате они могут казаться похожими внешне, но на самом деле это разные названия продуктов и т д.
Итак, попробуем указать на такие неточности привычным для нас способом — изменением внешнего вида.
Так как мы будем отмечать в D4, D6, D8 и D10, то выделим их и запишем правило формата в виде формулы
=ОБРЕЗАТЬ ПРОБЕЛЫ(D4)D4
Функция TRIM удаляет начальные и конечные пробелы из текста. А если после такого «сжатия» оказалось, что текст «до» не совпадает с текстом «после», то в начале или в конце есть дополнительные пробелы. Такие данные будут помечены соответствующим образом.
Довольно просто, но очень полезно.
Подсветка дат и сроков.
Очень часто приходится бронировать определенные даты, например, чтобы не пропустить предстоящие мероприятия, подготовить будущие отправки и т д. И, конечно же, делать это нужно с помощью программы, а не вручную.
Начнем с простой формы.
У нас есть куча дат, из которых нужно выбрать и выделить те, которые относятся к последнему месяцу. Для этого, как и прежде, выбираем инструмент — Правила выделения ячеек — Дата. А затем из выпадающего списка выбираем нужный нам вариант, а также стиль макета.
Все довольно просто, но список опций здесь очень ограничен. Так что попробуем второй, более сложный, но и более универсальный способ. Как вы, наверное, уже догадались, мы снова будем использовать формулы.
Поэтому, пожалуйста, обратите внимание на дату доставки.
Если это в прошлом и товар уже доставлен, вам не о чем беспокоиться. Если это в будущем, мы должны держать проблему под контролем и не забыть организовать доставку на указанную дату. И, наконец, если дата отгрузки выпадает на сегодня или завтра, то следует все бросить и заняться именно этой партией прямо сейчас (высший приоритет).
Таким образом имеем 3 группы условий, которые сразу записываем в виде правил условного форматирования:
- Сегодняшняя или завтрашняя дата (1):
=ИЛИ($E5-$C$2=1; $E5=$C$2)
- Дата в будущем (2):
=$E5>$C$2
- Срок выполнения: все остальные.
Обратите внимание на порядок формул. Формула с более высоким приоритетом имеет более высокий приоритет, поскольку она выполняется первой. Поэтому сначала выбираем только даты сегодня и завтра, потом все остальное в будущем. Если ваш файл не похож на тот, что на картинке, используйте клавиши вверх и вниз (3), чтобы просто изменить порядок условий.
Ну, с уже прошедшими датами мы просто ничего не делаем. Они по-прежнему имеют «естественный» цвет.
И еще один пример. Оставим выходные в стороне.
Столбец A нашей таблицы содержит даты. Выделяем их все мышью, затем привычным способом определяем по формуле условие форматирования.
= ЕСЛИ (ПУСТО (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 и скопируйте ее для всех дат. Изначально вы получите копию столбца A.
Чтобы не усложнять таблицу формулами, воспользуемся числовым форматом для представления этих дат по-другому.
Выделите их и нажмите комбинацию клавиш CTRL и 1. В появившемся окне выберите «Все форматы» и в поле «Тип» введите 4 латинские буквы d (как на рисунке). И теперь у вас есть дата, преобразованная в день недели без формул!
И, как видите, мы выделили субботу и воскресенье.
Как скрыть содержимое ячейки по условию?
Часто при заполнении различных форм необходимо дождаться полного ввода всех данных, и только после этого отображать результат. Это можно сделать с помощью логических выражений и функции ЕСЛИ, когда вычисления будут производиться только при определенном условии.
Функция ЕСЛИ в Excel — примеры использования
Возьмем небольшой пример:
Результат появляется только тогда, когда все ячейки заполнены. И вот как делается общая строка:
Обратите внимание, что шрифт по умолчанию — белый на белом фоне. А потом после того, как заполним данные по всем кварталам, изменим так, чтобы номер стал виден.
Для диапазона B6:C6 установите желаемый фон ячейки и установите цвет шрифта на «Авто» или принудительно белый.
Формула правила формата:
=СЧЁТ($C$2:$C$5) = 4
он подсчитывает только количество чисел в диапазоне. Если все четыре будут введены, макет результатов изменится, и они станут видимыми. А в обычном состоянии вы их просто не увидите из-за белого цвета шрифта.
Подсветка ячеек с формулами.
Рассмотрим еще один подход, который поможет контролировать точность ввода данных. Предположим, у нас есть числовые данные. Вам нужно проверить, не являются ли какие-либо из них производными от формул, поскольку все они должны быть введены вручную.
Проверим наличие формулы в ячейке с помощью функции ЭФОРМУЛА (ИСФОРМУЛА).
=ФОРМУЛА(B2:D13)
Обратите внимание, что абсолютные ссылки здесь не нужны.
В результате будут выделены ячейки, содержащие формулы.
Возможно, больший интерес представляет альтернативная версия этой формулы, которая позволит вам выделить места, где некоторые числа или текст вводятся вручную, без использования формул.
Используем функцию НЕ (НЕ), которая изменит логический результат на противоположный:
= НЕ (ЭФОРМУЛА (B2: D13))
Мы сейчас выделили области, где нет формул, а данные просто вводятся вручную. Возможно, это полезно в таблицах с большим количеством формул и ссылок. И если один из них заменить числом, то при последующем изменении данных таблицы многие расчеты окажутся неверными.
Для того, чтобы попытка пользователя заменить формулу числом была обнаружена быстро, этот метод будет работать как нельзя лучше.
Выделение пустых ячеек либо с ошибками.
В MS Excel может быть полезно изменить цвет ячейки не только на основе значения, но и в случае отсутствия значения или возникновения какой-то ошибки. На эти случаи часто приходится обращать особое внимание в таблице. Особенно это касается ошибок. Сделать это довольно просто.
Выберите всю таблицу, а затем повторно используйте формулу в правиле условного форматирования. Нам понадобятся две функции: ISError (IsError) и ISBLANK (IsBlank).
Вы можете создать два условия:
=ЕОШИБКА($B$2:$M$25)
=БЕЛЫЙ($B$2:$M$25)
Или объедините их в один, используя ИЛИ:
=ИЛИ(ЕОШИБКА($B$2:$M$25),NULL($B$2:$M$25))
Естественно, здесь вы указываете свои диапазоны.
Нажмите кнопку «Формат» и выберите наиболее подходящий вариант макета.
Подсветка недопустимых значений.
Когда вам часто приходится вводить повторяющиеся данные в таблицу, обычно прибегают к использованию выпадающего списка.
Как создать выпадающий список в Excel — подробнее.
Вы можете применить защиту листа и запретить ввод каких-либо данных в определенные позиции. Однако можно просто закрасить ячейку, где было введено неверное значение. Тогда мы укажем пользователю, что информация была зарегистрирована с ошибкой.
Для этого нам сначала нужен список допустимых значений. Думаю, его лучше вынести на отдельный лист рабочей тетради, чтобы он не мешал основным данным и в то же время всегда был под рукой. Назовем этот лист City и запишем туда допустимые названия городов. Мне это нравится:
Итак, на втором листе City есть список допустимых имен.
Мы должны изменить цвет D5 на красный, если выполняются 2 условия:
1 значение не совпадает с допустимым;
2 не пусто.
Вы можете использовать функцию СЧЁТЕСЛИ в правиле условного форматирования для проверки первого условия).
Определяет, сколько раз введенное значение появляется в списке ссылок. Если ноль раз, значит его нет, а то что введено неверно и нужно исправлять.
=СЧЁТЕСЛИ(D5;Город!A1:A6)
Для проверки второго условия воспользуемся функцией ISBLASNK, которая проверяет, пуста ли ячейка или там что-то написано.
=ПУСТО(D5)
Но это пустой чек. Чтобы проверить, что он не пустой, мы используем функцию НЕ. Изменяет результат выполнения логической функции: TRUE на FALSE и наоборот. Обратная проверка выглядит так:
= НЕ (ПУСТО (D5))
а для выполнения обоих условий объединяем их функцией И (AND)
=И(НЕ(ПУСТО(D5)),СЧЕТ.ЕСЛИ(Город!A1:A6,D5)=0)
Если мы введем правильное имя в D5, красная заливка исчезнет.
Хороший способ контролировать правильность ввода. Но как по мне, выпадающий список лучше бы контролировал правильность ввода. Рекомендую:
Как создать выпадающий список в Excel с помощью формул
Меняем вид ячейки в зависимости от прочих ячеек.
До сих пор мы видели, как изменить внешний вид относительно информации в ячейке таблицы. Что делать, если нам нужно изменить цвет или отступ ячейки в Excel в зависимости от того, как меняются данные в соседних ячейках?
Давайте посмотрим еще один интересный вариант выделения столбца в таблице. Итак, у нас есть данные о заказах от разных покупателей в течение года.
Обратите внимание: в первой строке даты вписываем точно, а название месяца и года руками не пишем. Затем мы применяем специальный формат даты к B1:M1.
Для этого выделите этот диапазон мышкой, а затем в меню Главная — Число — Формат откройте выпадающий список со всевозможными форматами. Выберите последний пункт «Другие числовые форматы». Откроется окно, как показано на изображении. Или просто нажмите комбинацию клавиш CTRL+1.
Слева в поле «Числовые форматы» внизу выберите «все форматы». А затем в поле «Тип» введите:
мммм аааа
Нажмите OK и получите новый формат даты.
Теперь давайте выберем текущий месяц.
Выделяем все столбцы нашей таблицы с датами. В данном случае это диапазон B2:M6. Затем открываем уже знакомое меню функции «Условное форматирование».
Снова воспользуемся формулой для определения условий:
=МЕСЯЦ(B$1)=МЕСЯЦ(ДАТА())
Кстати, текущий месяц на момент написания этого материала — декабрь, то есть 12 число.
Обратите внимание, что используется абсолютная ссылка на первую строку, содержащую даты каждого месяца. И если месяц от этой даты соответствует номеру текущего месяца, мы закрасим этот столбец синим цветом.
Теперь перейдем к более сложной задаче. Проверим в каждой из строк таблицы, были ли у покупателя заказы за последние 3 месяца. Если заказов не было, мы выделим имя этого покупателя.
Посмотрите, как это будет выглядеть в декабре.
Мы изменим отступы в A2: A6. Но проверять выполнение условия будем в диапазоне B2:M6.
Итак, выбираем мышью A2:A6 и открываем меню — Создать правило — Использовать формулу.
Напишем так:
=СЧЁТЕСЛИ(СМЕЩ($A2,0,МЕСЯЦ(ДАТА())-2,1,3),»>0″)=0
Сразу отметим, что вы можете использовать СЕГОДНЯ() вместо функции СЕГОДНЯ(). Оба они возвращают текущую дату, только СЕЙЧАС также возвращает время.
=СЧЁТЕСЛИ(СМЕЩ($A2,0,МЕСЯЦ(СЕГОДНЯ())-2,1,3),»>0″)=0
Как видите, мы используем функцию СЧЁТЕСЛИ для подсчёта количества заказов. Вам нужно указать диапазон в качестве первого аргумента и условие в качестве второго. И посчитает, сколько раз это условие выполнялось в указанном диапазоне данных.
Мы используем «>0» как условие, потому что если был заказ, то в нем должно быть указано количество. Кстати, не забудьте заключить это условие в двойные кавычки, чтобы не было ошибки.
Теперь займемся первым условием. Для наглядности напишем это здесь:
СМЕЩ($A2,0,МЕСЯЦ(ДАТА())-2,1,3)
Чтобы вернуть диапазон данных, используйте функцию OFFSET. Схема их работы показана на рисунке выше. В первую очередь нужно указать начальную точку, с которой мы будем начинать все действия. Пусть это будет $A2. Опять же, мы делаем абсолютную ссылку на столбец, чтобы при проверке условия следующим был A3, затем A4 и так далее.
Далее нам нужно указать, сколько строк спуститься. Поскольку нас интересует текущая строка, мы устанавливаем 0.
Теперь нам нужно сдвинуться на определенное количество столбцов вправо, где будет начало (верхний левый угол) нашего диапазона. Так как кроме текущего месяца нас интересуют заказы в двух предыдущих (то есть всего будет 3 месяца), то листаем вправо на количество столбцов, равное номеру текущего месяца минус 2 Если сейчас у нас 12-й месяц, то мы делаем 10 шагов вправо и добираемся до октября, то есть столбца K. Это начало нашего диапазона данных.
Далее – укажите высоту диапазона. Поскольку нас интересует только текущая строка, мы устанавливаем ее в 1.
И, наконец, нам нужно определить, на сколько столбцов справа будет продолжаться наш отсчет порядка. Ответ следует из нашего условия: на 3 месяца, то есть берем К, Л, М. С октября по декабрь включительно будет К2:М2. Так как ордеров нет, COUNTIF вернет 0. В результате условие сработает и A2 станет красным. И третья строка в K3:M3 содержит данные, поэтому A3 останется прежним.
И еще одно замечание: в январе и феврале наше аналогичное правило не сработает, так как еще не прошли необходимые 3 месяца для проверки.
Итак, мы разобрались, как изменить цвет ячейки в Excel на основе значения. Если у вас есть вопросы, пишите их в комментариях. Я постараюсь ответить.