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

  • 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. Здравствуйте! у меня вопрос: Допустим, есть два листа с разными данными за одни и те же периоды. необходимо в третьем листе вывести наибольшее значение, при этом чтобы было видно из какого листа взяты данные, например, раскрасив в один цвет если данные из первого листа и в другой - если данные из второго листа...
    Благодарю за советы!

    1. Али, если прочитать статью не через строку, а полностью, то можно найти и формулу для этого, и как её применить. Не сочтите за труд прочитать статью с самого начала. Осознать и попробовать применить. Далее перейти к списку формул и посмотреть на 6-ю по счету.

  2. Добрый день!
    Пробую выделить УФ в сводной таблице строку, если итог по строке больше из связанной сводной таблицы ниже 4х.
    Выделяю ячейку в сводной, пишу формулу УФ
    =SUM(B2405:BM2405)>=5
    копирую форматирование на всю сводную. Работает.

    Проблема: при использовании срезов (или фильтров), УФ пропадает.
    Я что-то не так делаю, или такой функционал не доступен?

    Спасибо заранее за помощь!

  3. Добрый день. Подскажите, а если нужно сделать условное форматирование максимального и минимального значения для такого случая:
    A B C D E
    1 559980 - 606000 - 824000
    2 559980 - - - -
    Если в первой строке понятно, что Максим это Е1, а миним А1, то как быть со второй строкой. Можно ли сделать такое условие, если в строке, например более 4 штук "-" форматирование не проставлялось? Спасибо

  4. Добрый день.
    В ячейке А1 стоит условие УФ- окрашивать ячейку А1, если в этой ячейке стоит число, большее, чем в ячейке В1. Но, нужно, чтобы не окрашивалась ячейка А1, если в ячейке В1 будет ноль или пустая ячейка. Подскажите, как решить такую проблему? Можно ли так сделать без макросов?
    Спасибо.

  5. Привет.
    Задача: из массива чисел в одном столце (-1, 5, 8, -9) нужно подсветить ячейку с максимальным модулем числа.
    При таком раскладе =МАКС($B$4:$B$1000)=B4 подсвечивается максимальное значение, а при таком (добавил модуль) =МАКС(ABS($B$4:$B$1000))=B4 ни чего не подсвечивается.
    Что не правильно?

    1. Татьяна, как это слишком просто, чтобы невозможно было сделать самостоятельно... Может есть какой-то подвох? ;)
      Выделяете ячейку А1, переходите в создание условного форматирования -Использовать формулу. Вписываете такую:
      =A1=B1

  6. Подскажите, пожалуйста, как выделит УФ месяц, который следует через месяц за текущим (не следующий :(, на это есть заготовочка...).
    Заранее благодарна.

  7. Здравствуйте. Пожалуйста помогите сделать правильно условное форматирование.
    Пытаюсь применить формулу из вашей таблицы - не получается! Видимо, что-то не так делаю.
    Задача, выделить цветом строку, в одной из ячеек которой максимальная цифра из столбика, где эта цифра находится.
    Задача для ситуации: список водителей и их показатели работы. Одна из колонок показывает результат эффективности водителя из показателей (максимальное количество заработанных рублей за прошедший километраж ("цена 1 км")

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

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