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

  • 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<>""

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

  1. Здравствуйте, хотел узнать можно ли сделать условное форматирование ячейки с такими условиями. В ячейке постоянно меняются значения, например 10%,25%,50%,35%,100%,-10%,-25%,-125%,50%,-30%, как сделать, что бы заливка менялась только при значениях 100% или более 100%, допустим становилась зеленой, а при значениях -100% или менее -100% становилась красной и оставалась красной до 100% и более. Т.е. если значение в ячейке в моменте было 100%, то она не меняет цвет с зеленого до достижения -100% в не зависимости от значений если они больше -100%, и так же обратно, если в моменте было -100% и менее ячейка не меняет цвет с красного, пока значение не достигнет 100% . Извиняюсь за сумбур, думаю понятно объяснил.

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

      1. Попробую еще раз сформулировать. В ячейке в режиме реального времени с периодичностью раз в секунду изменяются значения, например 10%,25%,50%,35%,100%,-10%,-25%,-125%,50%,-30% Если значение в ячейке в моменте было "100%"или более "110%", 250%, то заливка должна стать зеленой и оставаться зеленой (независимо от того какие будут в ячейке промежуточные значения, если значения больше "-100") до тех пор, пока значение в ячейке в моменте не станет "-100%" или менее "-155%", "-300". Когда в моменте значение ячейки становится "-100" цвет заливки меняется на красный, и остается красной, в не зависимости от промежуточных значений, если они меньше "100%". И так циклически.

        1. Либо я чего-то не понимаю, либо Ваши пояснения лучше не стали.
          Если надо сделать одну заливку для значений меньше 100% - почему не создать такое условие? Тоже самое и со значением больше 100%. В общем, если хотите дальше пояснять описанием без примера - то придется ждать того, кто это все поймет.

          1. Смотрите, значения в ячейке меняются в диапазоне от "1000%" до "-1000%", нужно изменять заливку с зеленого на красный только в двух случаях. Первый - значение в ячейке стало "100", заливка должна становиться зеленой и оставаться зеленой, пока не наступит второй случай. Второй случай - значение в ячейке стало "-100", заливка из зеленого становится красной, и остается красной, пока не наступит первый случай.

Добавить комментарий

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