В данной статье собран список формул, которые можно использовать в условном форматировании ячеек, заданным при помощи формулы:
- 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<>""
ТЕКСТОВЫЕ ЗНАЧЕНИЯ
ДАТА / ВРЕМЯ
ДРУГИЕ
Здравствуйте! у меня вопрос: Допустим, есть два листа с разными данными за одни и те же периоды. необходимо в третьем листе вывести наибольшее значение, при этом чтобы было видно из какого листа взяты данные, например, раскрасив в один цвет если данные из первого листа и в другой - если данные из второго листа...
Благодарю за советы!
Здравствуйте. Подскажите, можно ли с помощью УФ закрашивать ячейки с минимальным значением в каждой строке для всей таблицы?
Али, если прочитать статью не через строку, а полностью, то можно найти и формулу для этого, и как её применить. Не сочтите за труд прочитать статью с самого начала. Осознать и попробовать применить. Далее перейти к списку формул и посмотреть на 6-ю по счету.
А если нужно исключить нули в этом диапазоне?
Значит надо подучитьформулы массива :)
=МИН(ЕСЛИ(A1:A10<>0;A1:A10))
Добрый день!
Пробую выделить УФ в сводной таблице строку, если итог по строке больше из связанной сводной таблицы ниже 4х.
Выделяю ячейку в сводной, пишу формулу УФ
=SUM(B2405:BM2405)>=5
копирую форматирование на всю сводную. Работает.
Проблема: при использовании срезов (или фильтров), УФ пропадает.
Я что-то не так делаю, или такой функционал не доступен?
Спасибо заранее за помощь!
Добрый день. Подскажите, а если нужно сделать условное форматирование максимального и минимального значения для такого случая:
A B C D E
1 559980 - 606000 - 824000
2 559980 - - - -
Если в первой строке понятно, что Максим это Е1, а миним А1, то как быть со второй строкой. Можно ли сделать такое условие, если в строке, например более 4 штук "-" форматирование не проставлялось? Спасибо
Добрый день.
В ячейке А1 стоит условие УФ- окрашивать ячейку А1, если в этой ячейке стоит число, большее, чем в ячейке В1. Но, нужно, чтобы не окрашивалась ячейка А1, если в ячейке В1 будет ноль или пустая ячейка. Подскажите, как решить такую проблему? Можно ли так сделать без макросов?
Спасибо.
Привет.
Задача: из массива чисел в одном столце (-1, 5, 8, -9) нужно подсветить ячейку с максимальным модулем числа.
При таком раскладе =МАКС($B$4:$B$1000)=B4 подсвечивается максимальное значение, а при таком (добавил модуль) =МАКС(ABS($B$4:$B$1000))=B4 ни чего не подсвечивается.
Что не правильно?
Не дописал. То есть при первом УФ подсвечивается 8.
При втором УФ ни чего не подсвечивается а должно быть 9.
Подскажите, какую формулу использовать: если дата в ячейке А1 равна дате в ячейке В1, то применяется условное форматирование к А1
Татьяна, как это слишком просто, чтобы невозможно было сделать самостоятельно... Может есть какой-то подвох? ;)
=A1=B1
Выделяете ячейку А1, переходите в создание условного форматирования -Использовать формулу. Вписываете такую:
Подскажите, пожалуйста, как выделит УФ месяц, который следует через месяц за текущим (не следующий :(, на это есть заготовочка...).
Заранее благодарна.
не успела спросить, как дошло :)
вот формула:
=МЕСЯЦ(A1)=МЕСЯЦ(СЕГОДНЯ())+2
Здравствуйте. Пожалуйста помогите сделать правильно условное форматирование.
Пытаюсь применить формулу из вашей таблицы - не получается! Видимо, что-то не так делаю.
Задача, выделить цветом строку, в одной из ячеек которой максимальная цифра из столбика, где эта цифра находится.
Задача для ситуации: список водителей и их показатели работы. Одна из колонок показывает результат эффективности водителя из показателей (максимальное количество заработанных рублей за прошедший километраж ("цена 1 км")