Забыли пароль?


Хитрости »
Основные понятия (24)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (65)
Разное (42)
Баги и глюки Excel (4)

Сборник формул для условного форматирования

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

  • 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. Выделение ячеек, содержащих ошибки (#ЗНАЧ!; #Н/Д; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
    =ЕОШИБКА(A)
  32. Выделение непустых ячеек в столбце A:
    =$A1<>""

Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Обсуждение: 75 комментариев
  1. Вячеслав:

    Добрый день! Дмитрий
    Прошу помочь сделать условное форматирование так что бы, при пересечении данных формул, ячейки выделялись красным цветом.
    =ЕСЛИ(СУММЕСЛИМН(C4;C2;RC2;C3;R2C)>0;СУММЕСЛИМН(C4;C2;RC2;C3;R2C);RC[-1]-1)

    • Вячеслав, я вообще не понял что значит "пересечении данных формул". Это что за пересечение формул и где они должны пересекаться?

      • Александра:

        у меня такой же вопрос. есть массив данных. необходимо выделить цветом строку если в одна из ячеек больше нуля, а вторая равна нулю

  2. Михаил:

    Здравствуйте. Нужна помощь. Есть таблица, в которой в произвольном порядке в первый столбец заносятся производственные объекты, во второй столбец - начало простоя объекта, а в третий - окончание простоя объекта. Есть ещё четвёртый столбец, в котором отображено время простоя, но он, скорее всего, для выполнения требуемой задачи не нужен. В таблицу заносятся данные о простоях объектов в течение рабочих суток (с 8.00. до 8.00.). Объекты в первом столбце могут повторяться, т.к. в течение суток у каждого объекта могут быть простои по несколько раз. Вторая таблица представляет из себя подобие диаграммы. В первом столбце те же самые объекты, только по одному разу (объект № 1, объект № 2 и т.д.). А далее время (сутки, с 8.00. до 8.00.), в идеале поминутно, т.е. 1440 столбцов, но можно и через 5 минут, т.е. 288 столбцов. Ячейки в этих столбцах надо отформатировать так, чтобы каждая ячейка "заливалась" по данным первой таблицы - в зависимости от объекта и времени его простоя в течение суток. В итоге должна получиться диаграмма, на которой будет наглядно видно когда какой объект простаивал, какие были наложения (т.е. одновременный простой двух, трёх и более объектов). Надеюсь я понятно объяснил.

    • Михаил, понятно, но....Без примера файла тут даже вникать не хочется. Т.к. чтобы помочь Вам в решении проблемы, надо воссоздать Ваш файл, внести в него данные и потом придумать решение...Не очень это продуктивно. Обратитесь на форум, создайте тему и приложите файл - тогда получите решение проблемы точно в разы быстрее.

  3. Анна:

    Добрый день,
    подскажите, пожалуйста, а можно ли поставить выделение цветом всей строки (не зависимо от внесенных в ячейки данных или пустых ячеек в этой строке), если первая ячейка строки, к примеру, имеет значение +?
    Буду благодарна за помощь.

    • Анна, ну вроде есть такой пример:
      =A1="Итог"
      только вместо "Итог" ставим "+"
      =A1="+"
      Другой вопрос, что считать первой ячейкой строки...Если это может быть любой столбец - то без примера данных не обойтись, т.к. тут важна и структура и нюансы. В этом случае можно создать тему на форуме и приложить пример с пояснениями что да как и почему.

      • Анна:

        Когда делаю правило на 1 строку, оно работает, когда пытаюсь поставить диапазон из 9 строк ничего не работает. Выделяемый диапазон B2:G10. Первая ячейка каждой строки А2:А10. Значения Колонки А не все содержат "+".

        • Анна, тут вопрос в том, как делаете. Я ведь не вижу Вашу формулу. Скорее всего предисловие не читали и доллары где надо не поставили:
          =$A1="+"
          а это важное условие в таких случаях.

  4. Дмитрий:

    Здравствуйте, Дмитрий!
    Помогите, плиз)
    Хочу сделать условное форматирование ячейки О1, при условии, что хотя-бы одна из ячеек диапазона J1:N1 содержит хотя-бы одно из следующих значений: "акт", "ключ", "без счета".

  5. antonkurt2019:

    Здравствуйте, Дмитрий!
    Помогите, плиз)
    Хочу сделать условное форматирование ячейки О1, при условии, что хотя-бы одна из ячеек диапазона J1:N1 содержит хотя-бы одно из следующих значений: "акт", "ключ", "без счета".

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти