Сборник формул для условного форматирования
В данной статье собран список формул, которые можно использовать в условном форматировании ячеек, заданным при помощи формулы:
- Excel 2003: Формат(Format)-Условное форматирование(Conditional formatting)- формула;
- Excel 2007-2010: вкладка Главная(Home)-Условное форматирование(Conditional formatting)-Создать правило(New rule)-Использовать формулу для определения форматируемых ячеек(Use a formula to determine which cells to format)
Подробнее об условном форматировании можно прочитать в статье: Основные понятия условного форматирования и как его создать
Все условия приведены для диапазона
Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон
Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
при выделенном диапазоне
Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона
-
ЧИСЛОВЫЕ ЗНАЧЕНИЯ
- Выделение ячеек с числами:
=ЕЧИСЛО( A1 ) - Выделение ячеек с числами, но не учитывая нули:
=И(ЕЧИСЛО( A1 );A1 <>0) - Выделение строк со значением больше 0:
= A1 >0 - Выделение строк со значением в диапазоне от 3 до 10:
=И( A1 >=3;A1 <=10) - Выделение в диапазоне
ячейки с максимальным значением:$A$1:$A$20
= A1 =МАКС($A$1:$A$20 ) - Выделение в диапазоне
ячейки с минимальным значением:$A$1:$A$20
=И(ЕЧИСЛО( A1 );A1 =МИН($A$1:$A$20 )) - Выделение в диапазоне
ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6:$A$1:$A$20
= A1 =НАИБОЛЬШИЙ($A$1:$A$20 ;2) - Выделение ячеек с любым текстом:
=ЕТЕКСТ( A1 ) - Выделение ячеек с текстом Итог:
= A1 ="Итог" - Выделение ячеек, содержащих текст Итог:
=СЧЁТЕСЛИ(A1;"*итог*")
=НЕ(ЕОШ(ПОИСК("итог";A1))) - Выделение ячеек, не содержащих текст Итог:
=СЧЁТЕСЛИ(A1;"*итог*")=0
=ЕОШ(ПОИСК("итог";A1)) - Выделение ячеек, текст которых начинается со слова Итог:
=ЛЕВСИМВ(A1;4)="Итог" - Выделение ячеек, текст которых заканчивается на слово Итог:
=ПРАВСИМВ(A1;4)="Итог" - Выделение текущей даты:
=A1=СЕГОДНЯ() - Выделение ячейки с датой, больше текущей:
=A1>СЕГОДНЯ() - Выделение ячейки с датой, которая наступит через неделю:
=A1=СЕГОДНЯ()+7 - Выделение ячеек с датами текущего месяца(любого года):
=МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ()) - Выделение ячеек с датами текущего месяца текущего года:
=И(МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ());ГОД( A1 )=ГОД(СЕГОДНЯ()))
или
=ТЕКСТ(A1 ;"ГГГГММ")=ТЕКСТ(СЕГОДНЯ();"ГГГГММ") - Выделение ячеек с выходными днями:
=ДЕНЬНЕД(A1;2)>5 - Выделение ячеек с будними днями:
=ДЕНЬНЕД(A1;2)<6 - Выделение ячеек, входящих в указанный период(промежуток) дат:
=И( $A1 >ДАТА(2015;9;1);$A1 <ДАТА(2015;10;1)) - Выделение различий в ячейках по условию:
=A1<>$B1 - Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше:
=A1>B1 - Выделение строк цветом через одну:
=ОСТАТ(СТРОКА();2) - Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000:
=СЧЁТЕСЛИ($F$1:$H$5000;A1) - Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000:
=СЧЁТЕСЛИ($F$1:$H$5000;A1)=0 - Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету:
=СЧЁТЕСЛИ( $A$1:$A1 ;A1 )=2 - Выделение цветом повторяющихся значений в диапазоне A1:A20, даже если эти значения являются "числами" с более чем 15-ю знаками:
=СУММПРОИЗВ(( $A$1:$A$20 =A1 )*1)>1 - Выделение цветом уникальных значений в диапазоне A1:A20, даже если эти значения являются "числами" с более чем 15-ю знаками:
=СУММПРОИЗВ(( $A$1:$A$20 =A1 )*1)=1 - Выделение ячеек, содержащих ошибки (#ЗНАЧ!; #Н/Д; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
=ЕОШИБКА(A) - Выделение непустых ячеек в столбце A:
=$A1<>""
ТЕКСТОВЫЕ ЗНАЧЕНИЯ
ДАТА / ВРЕМЯ
ДРУГИЕ
Статья помогла? Поделись ссылкой с друзьями!
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
Дмитрий, можно УФ выделить определенное слово в ячейке. Например:" Мама мыла раму"
Слово "мыла" - выделить. Макросом знаю.Формулой нет.
Условное форматирование не может применять форматирование к части текста - только к ячейке целиком.
подскажите как с помощью условного форматирования выделить ячейки, соответствующие одновременному выполнению двух условий
A>B И C>100 ????
Оля,
=И(A1>B1;C1>100)
при необходимости закрепить по столбцам.
Дмитрий, спасибо за ответ.
Мой вопрос адресован корифеям этого форума. Помогите разобраться. Офис 2010. Экселевская таблица. Есть необходимость закрасить строку при определенном условии, условие одно в определенном столбце (F) если в какой либо ячейке ставятся числа 6 или 15, то строка зажигается, если в этой строке в ячейке (J) ставим любой символ ( в моем случае любую дату) то строка гаснет. Я решил эту задачу через условное форматирование создав правило =И(ИЛИ($F$4=6;$F$4=15);$J$4=0) (правило на примере строки 4)но столкнулся с проблемой. Таблица это год, таблица разделена на 12 секторов по месяцам, в каждом месяце по умолчанию 15 строк с указанным выше правилом. Итого получаем 180 правил в таблице (которые приходиться править руками по номерам строк) В течении месяца возможно возникнет необходимость добавления строк и при добавлении строки с уже прописанным правилом правило просто дублирует предыдущее и тогда при выполнении правила в одной строке зажигается не одна а уже 2 строки. Научите правильно тиражировать правило. Если подскажите как смогу выложить саму таблицу для наглядности. Заранее спасибо.
Alex, раз вопрос к корифеям форума - может имеет смысл именно в форум обратиться? Здесь не форум - это всего лишь комментарии к статье.
Ну и на этом спасибо.
Подскажите, пожалуйста, какую формулу вписать, чтобы выделялись в таблице строки, где дата менее текущей на 7 дней.
Здравствуйте!
Помогите пожалуйста!никак не могу редактировать таблицу, хотя она очень простая.
если в ячейке слева есть минус (его можно заменить на любую букву), то ячейка справа должна окрашиваться.
скорей всего дело в английской версии Excel 2003.
заранее благодарна!
VSE, SPASİBO BOLWOE! STOİLO TOLKO VAM NAPİSAT, KAK VSE POLY4İLOS!