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. Ирина:

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

  2. Али:

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

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

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

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

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

  4. Маргарита:

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

  5. Светлана:

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

  6. Алексей:

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

    • Алексей:

      Не дописал. То есть при первом УФ подсвечивается 8.
      При втором УФ ни чего не подсвечивается а должно быть 9.

  7. Подскажите, какую формулу использовать: если дата в ячейке А1 равна дате в ячейке В1, то применяется условное форматирование к А1

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

  8. Наталия:

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

  9. Наталия:

    не успела спросить, как дошло :)
    вот формула:
    =МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ())+2

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

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

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

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


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