Lost your password?


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

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

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

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

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

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

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

    • Виктор:

      Дмитрий, все-таки это я затупил.
      Должен сразу сказать, что я только начал пользоваться макросами, до сих пор обходился формулами. Информации в статье (плюс на мой скудный опыт в данном вопросе) не хватало, чтобы сделать перескок с использования функции IsFormula на листе на использование ее же в УФ.
      После серии экспериментов добился окрашивания ячеек с формулой в заданный формат. При этом в формуле для условного форматирования в скобках нужно указать левую верхнюю ячейку диапазона, к которому применяется форматирование. Например, =IsFormula(G4) если форматирование применяется к диапазону (G4:R78)
      (это я пишу для таких же чайников, как я)
      Дмитрий, еще раз спасибо. Совершенно очевидно, что передо мной поле непаханое новых возможностей. Найти бы время для изучения вашего ресурса!

  2. Алексей:

    Добрый день!
    Подскажите пожалуйста, как сделать так, чтобы цвет в ячейке менялся, в зависимости от времени. Приведу пример:
    Есть два столбца с временами, "0:20(A1) и 0:35(B1)"; Для изменения цвета ячейки B1(0:35), я создаю формулу =$A$1. Т.е. если время B1 больше А1, то цвет меняется на красный; если меньше - на зеленый. Работает.
    Но как сделать, если значений гораздо больше. Табличка вида А1-А300, В1-В300.
    Спасибо.

  3. Сергей:

    Доброго дня...
    Спасибо за познавательную статью, есть чему поучиться и седым..., но никак не могу решить вопрос с применением УФ при итоговом суммировании отработанного времени сотрудниками при посменной работе... Формат ячеек [h]:00 позволяет корректно отображать время и проводить простейшие вычисления... В основном суммарное значение по участкам работы и по сотрудникам должно быть равным "полному" часу, т.е. ХХ:00, но случаются перерабтки(недоработки) что приводит к "появлению" МИНУТ ... Как можно применить УФ только по сравнению значений после : ...?

    • Сергей, не совсем понятна суть. После : - это минуты? И с чем сравнивать? Я думаю лучше будет обратиться в форум(http://www.excel-vba.ru/forum/index.php) и приложить там файл с примером данных. Помочь будет легче.

      • Сергей:

        Дмитрий, спасибо за внимание... Обязательно воспользуюсь советом, (только берут сомнения - стар стал в свои 60.. но попробую разобраться...) Дабы не оставалось белых пятен ... Да после : "МИНУТЫ". Обстоятельства - наработка в месяц на участках "ТОЛЬКО ПОЛНЫЕ ЧАСЫ", к примеру может быть и 2400:00. Есть желание применить УФ для "засветки" ячеек в которых "МИНУТЫ" после : будут отличаться (будут больше) чем "00". Еще раз спасибо...

  4. 17е правило можно упростить: =ТЕКСТ(A1;"ГГГГММ")=ТЕКСТ(СЕГОДНЯ();"ГГГГММ")

  5. FreeOwl:

    Скажите, а можно окрасить строку, на основании одной из ячеек, которая в свою очередь принимает цвет в соответствии с УФ "цветовая шкала" (пример)

  6. Андрей:

    Доброго времени. Подскажите пожалуйста, бьюсь в экселе с формулой, ни как не получается. Надо что бы до наступления определенного срока(например за 10 дней до даты внесенный в файл) выделялась ячека. Т.е. в реестре дата 12.12.2015, к примеру, со 02.12.2015 выделялась ячека. Кто знает как это сделать, напишите пожалуйста решение моей проблемы. Буду очень благодарен.

    • Кирилл:

      Если еще актуально:
      =ЕСЛИ(СЕГОДНЯ()=A1-10;1;0)
      где A1 - ссылка на необходимую дату, с которой должен идти отчет

  7. Стас:

    Нужна помощь.
    Как подставить значение:
    Если ячейка имеет значение А2, то В2. Если А3, то В3. Но не все так просто)
    Если номер строки в столбце "А" имеет порядковый № 2, то значение выдается того же порядкового № 2 строки, но в столбце В.
    Делал для каждого значения, но понял что в будущем могу столкнуться с проблемой:
    IF(D2=!$A$2;$B$2;IF(D2=$A$3;$B$3))
    Спасибо.

    • Стас, неужели так сложно прочитать то, что красным шрифтом написано? Вы свою проблему озвучиваете в комментариях к статье, которая ничего общего с Вашей проблемой не имеет.
      Непосредственно по вопросу: найдите в разделе Хитрости статью - Как найти значение в другой таблице или сила ВПР. Это Ваш случай.

  8. Наталья:

    Добрый день!
    Сразу извиняюсь, если ответ на мой вопрос уже где-то тут был, а я не нашла.
    Ситуация следующая: в таблице нужно применить УФ только к ячейкам без заливки. это возможно?
    Спасибо!

  9. ПАвел:

    Добрый день. Нужна следующая помощь. Есть много листов с протоколами, имеющих порядковые номер 1-... На каждом листе идентичная форма с различными параметрами. Создал страницу со сводными данными, где в каждой строчке последовательно отображены эти данные, чтобы можно было легко фильтровать по параметрам. НО не знаю пока как сделать так, чтобы при добавлении очередного нового протокола при копировании предыдущей строки на странице со сводными данными автоматически формулы привязывались к новому листу. Все стандартные ячейки я зафиксировал $A$11, а вот в листах пока приходится в вручную в каждой новой строке присваивать новый номер.

  10. Александр:

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

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 Яндекс.Метрика
© 2024 Excel для всех   Войти