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

  • Excel 2003: Формат(Format)-Условное форматирование(Conditional formatting)- формула;
  • Excel 2007-2010: вкладка Главная(Home)-Условное форматирование(Conditional formatting)-Создать правило(New rule)-Использовать формулу для определения форматируемых ячеек(Use a formula to determine which cells to format)

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

Все условия приведены для диапазона A1:A20. Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20(столбцов может быть больше), начиная с ячейки A1, после чего назначить условие.
Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона - A4.

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
=$A1=МАКС($A$1:$A$20)
при выделенном диапазоне A1:F20(диапазон применения условного форматирования), будет выделена строка A7:F7, если в ячейке A7 будет максимальное число.

Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: =B1<A1, то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше(B1<A1, B3<A3). При этом если ячейки столбца D меньше ячеек столбца C в той же строке - они тоже будут выделены(D1<C1, D5<C5).


 

    ЧИСЛОВЫЕ ЗНАЧЕНИЯ

  1. Выделение ячеек с числами:
    =ЕЧИСЛО(A1)
  2. Выделение ячеек с числами, но не учитывая нули:
    =И(ЕЧИСЛО(A1);A1<>0)
  3. Выделение строк со значением больше 0:
    =A1>0
  4. Выделение строк со значением в диапазоне от 3 до 10:
    =И(A1>=3;A1<=10)
  5. Выделение в диапазоне $A$1:$A$20 ячейки с максимальным значением:
    =A1=МАКС($A$1:$A$20)
  6. Выделение в диапазоне $A$1:$A$20 ячейки с минимальным значением:
    =И(ЕЧИСЛО(A1);A1=МИН($A$1:$A$20))
  7. Выделение в диапазоне $A$1:$A$20 ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6:
    =A1=НАИБОЛЬШИЙ($A$1:$A$20;2)
  8.  
    ТЕКСТОВЫЕ ЗНАЧЕНИЯ

  9. Выделение ячеек с любым текстом:
    =ЕТЕКСТ(A1)
  10. Выделение ячеек с текстом Итог:
    =A1="Итог"
  11. Выделение ячеек, содержащих текст Итог:
    =СЧЁТЕСЛИ(A1;"*итог*")
    =НЕ(ЕОШ(ПОИСК("итог";A1)))
  12. Выделение ячеек, не содержащих текст Итог:
    =СЧЁТЕСЛИ(A1;"*итог*")=0
    =ЕОШ(ПОИСК("итог";A1))
  13. Выделение ячеек, текст которых начинается со слова Итог:
    =ЛЕВСИМВ(A1;4)="Итог"
  14. Выделение ячеек, текст которых заканчивается на слово Итог:
    =ПРАВСИМВ(A1;4)="Итог"
  15.  
    ДАТА / ВРЕМЯ

  16. Выделение текущей даты:
    =A1=СЕГОДНЯ()
  17. Выделение ячейки с датой, больше текущей:
    =A1>СЕГОДНЯ()
  18. Выделение ячейки с датой, которая наступит через неделю:
    =A1=СЕГОДНЯ()+7
  19. Выделение ячеек с датами текущего месяца(любого года):
    =МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ())
  20. Выделение ячеек с датами текущего месяца текущего года:
    =И(МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ());ГОД(A1)=ГОД(СЕГОДНЯ()))
    или
    =ТЕКСТ(A1;"ГГГГММ")=ТЕКСТ(СЕГОДНЯ();"ГГГГММ")
  21. Выделение ячеек с выходными днями:
    =ДЕНЬНЕД(A1;2)>5
  22. Выделение ячеек с будними днями:
    =ДЕНЬНЕД(A1;2)<6
  23. Выделение ячеек, входящих в указанный период(промежуток) дат:
    =И($A1>ДАТА(2015;9;1);$A1<ДАТА(2015;10;1))
  24.  
    ДРУГИЕ

  25. Выделение различий в ячейках по условию:
    =A1<>$B1
  26. Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше:
    =A1>B1
  27. Выделение строк цветом через одну:
    =ОСТАТ(СТРОКА();2)
  28. Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000:
    =СЧЁТЕСЛИ($F$1:$H$5000;A1)
  29. Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000:
    =СЧЁТЕСЛИ($F$1:$H$5000;A1)=0
  30. Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету:
    =СЧЁТЕСЛИ($A$1:$A1;A1)=2
  31. Выделение цветом повторяющихся значений в диапазоне A1:A20, даже если эти значения являются "числами" с более чем 15-ю знаками:
    =СУММПРОИЗВ(($A$1:$A$20=A1)*1)>1
  32. Выделение цветом уникальных значений в диапазоне A1:A20, даже если эти значения являются "числами" с более чем 15-ю знаками:
    =СУММПРОИЗВ(($A$1:$A$20=A1)*1)=1
  33. Выделение ячеек, содержащих ошибки (#ЗНАЧ!; #Н/Д; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
    =ЕОШИБКА(A)
  34. Выделение непустых ячеек в столбце A:
    =$A1<>""

Loading

91 комментарий

  1. Здравствуйте! У меня возник вопрос по "Выделение ячеек, содержащих текст Итог:
    =СЧЁТЕСЛИ(A1;"*итог*")
    =НЕ(ЕОШ(ПОИСК("итог";A1)))"
    На самом деле у меня есть большая таблица, в которой сведены определенные работы. У меня появилась необходимость выделять цветом строки, в которых содержатся определеные слова. Ну например есть строка "Устройство буронабивных свай...". При этом свои могут быть разного диаметра, т.е. после этих слов в остальных ячейках может идти отличающийся текст. Соответственно мне надо, что бы форматирование применялось ко всем строкам, в ячейках которых содержится фрагмент текста "Устройство буронабивных свай", ну или просто "буронабивных".
    Как в моем случае применить форматирование по формуле?! Важно, чтобы форматировались строки целиком. Отдельные ячейки (столбцы) легко форматируются Условным форматированием по парамерту "форматировать только ячейки, которые содержат текст".
    Спасибо

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

  3. Доброго Вам времени суток!
    Господа, помогите, пожалуйста, сам не могу никак разобраться...
    Мне необходимо, что бы в таблице при заполнении ячейки, допустим в столбце "A" вся строка меняла цвет

  4. Добрый день. Подскажите, пожалуйста, а как написать формулу УФ: если в диапазоне A1:B10 встречаются значения 1, 30, 45, то раскрасить ячейку (значения могут быть как числовыми, так и числами, сохраненными как текст)?
    Заранее спасибо за ответ.

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

  6. Спасибо)) я форматировала ячейки в колонке С при условии содержания в колонке А текста "Итого" с помощью формулы =ЛЕВСИМВ(A1;5)="Итого" Мне помогло)) спасибо!

  7. Подскажите, как с используя какую-нить формулу (возвращающую конкретное значение) определить применялось ли к ячейке условное форатирование. Т.е. если к ячейкам А1:A5 назначено условное форматирование в зависимости от значения ячейки А1 (к примеру, =$A$1=1) и оно не "сработало" (т.е. А1=0), то неизвестная мне функция возвращает одно значение, если А1=1, то "срабатывает" условное форматирование и ячейки А1:A5 окрашиваются в красный цвет, то неизвестная мне функция возвращает иное значение.
    Дело в том, что при "сработке" условного форматирование (при использовании в качестве действия изменения заливки) заливки не происходит (имеется ввиду Главное\Шрифт\Цвет заливки\Нет заливки), хотя визуально ячейки раскрашиваются.

  8. Добрый день, подскажите пожалуйста...
    Есть книга учета по сервису, в которой формулами рассчитывается всё, кроме конечной зарплаты сотрудников. Для облегчения подсчета сделали - когда выбираешь мастера, то в графе зарплата специалист подсвечивается нужным цветом специалиста, через условное форматирование - формула.
    И всё было бы хорошо, но для каждой следующей строки приходиться делать, все тоже самое в ручную, т.к. копирование ячейки с правилами, формулу копирует без сдвига на следующею строку.
    Для примера первые три строки сделал. В формуле приходиться менять первый параметр руками.
    Скрин
    https://yadi.sk/d/2Uav9WJFen4E5

  9. Дмитрий, добрый день,
    может, сталкивались с такой проблемой: как с помощью условного форматирования выделить ячейки, содержащие числа, введенные вручную, в массиве формул. Это удобно, если имеем модель (например, денежного потока), в которую мы допускаем ввод фиксированных данных, но при этом хотим, чтобы это было видно.
    В Excel есть формула =(ЕЧИСЛО(A1)), но она считает числом и собственно числа, и значения, рассчитанные по формуле.
    Как тут быть?

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

          1. Дмитрий, я не ставлю под сомнение, что функцию, созданную при помощи VBA можно легко использовать в УФ для любой ячейки листа. Я просто говорю о том, что отображение в ячейке Б результата проверки наличия в ячейке А формулы -- неудобно. Ненаглядно. Только и всего. Потому что если у вас есть модель с дизайном, обусловленным ее функциональностью, добавление к ней еще листа с аналитикой о наличии в ячейках формул или чисел является нагромождением. В отличие от условного форматирования.

Добавить комментарий для Вячеслав Отменить ответ

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.