В данной статье собран список формул, которые можно использовать в условном форматировании ячеек, заданным при помощи формулы:
- 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 )) - Выделение в диапазоне
$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<>""
ТЕКСТОВЫЕ ЗНАЧЕНИЯ
ДАТА / ВРЕМЯ
ДРУГИЕ
Добрый день! Дмитрий
Прошу помочь сделать условное форматирование так что бы, при пересечении данных формул, ячейки выделялись красным цветом.
=ЕСЛИ(СУММЕСЛИМН(C4;C2;RC2;C3;R2C)>0;СУММЕСЛИМН(C4;C2;RC2;C3;R2C);RC[-1]-1)
Вячеслав, я вообще не понял что значит "пересечении данных формул". Это что за пересечение формул и где они должны пересекаться?
у меня такой же вопрос. есть массив данных. необходимо выделить цветом строку если в одна из ячеек больше нуля, а вторая равна нулю
Здравствуйте. Нужна помощь. Есть таблица, в которой в произвольном порядке в первый столбец заносятся производственные объекты, во второй столбец - начало простоя объекта, а в третий - окончание простоя объекта. Есть ещё четвёртый столбец, в котором отображено время простоя, но он, скорее всего, для выполнения требуемой задачи не нужен. В таблицу заносятся данные о простоях объектов в течение рабочих суток (с 8.00. до 8.00.). Объекты в первом столбце могут повторяться, т.к. в течение суток у каждого объекта могут быть простои по несколько раз. Вторая таблица представляет из себя подобие диаграммы. В первом столбце те же самые объекты, только по одному разу (объект № 1, объект № 2 и т.д.). А далее время (сутки, с 8.00. до 8.00.), в идеале поминутно, т.е. 1440 столбцов, но можно и через 5 минут, т.е. 288 столбцов. Ячейки в этих столбцах надо отформатировать так, чтобы каждая ячейка "заливалась" по данным первой таблицы - в зависимости от объекта и времени его простоя в течение суток. В итоге должна получиться диаграмма, на которой будет наглядно видно когда какой объект простаивал, какие были наложения (т.е. одновременный простой двух, трёх и более объектов). Надеюсь я понятно объяснил.
Михаил, понятно, но....Без примера файла тут даже вникать не хочется. Т.к. чтобы помочь Вам в решении проблемы, надо воссоздать Ваш файл, внести в него данные и потом придумать решение...Не очень это продуктивно. Обратитесь , создайте тему и приложите файл - тогда получите решение проблемы точно в разы быстрее.
Добрый день,
подскажите, пожалуйста, а можно ли поставить выделение цветом всей строки (не зависимо от внесенных в ячейки данных или пустых ячеек в этой строке), если первая ячейка строки, к примеру, имеет значение +?
Буду благодарна за помощь.
Анна, ну вроде есть такой пример:
=A1="Итог"
=A1="+"
только вместо "Итог" ставим "+"
Другой вопрос, что считать первой ячейкой строки...Если это может быть любой столбец - то без примера данных не обойтись, т.к. тут важна и структура и нюансы. В этом случае можно создать тему и приложить пример с пояснениями что да как и почему.
Когда делаю правило на 1 строку, оно работает, когда пытаюсь поставить диапазон из 9 строк ничего не работает. Выделяемый диапазон B2:G10. Первая ячейка каждой строки А2:А10. Значения Колонки А не все содержат "+".
Анна, тут вопрос в том, как делаете. Я ведь не вижу Вашу формулу. Скорее всего предисловие не читали и доллары где надо не поставили:
=$A1="+"
а это важное условие в таких случаях.
Здравствуйте, Дмитрий!
Помогите, плиз)
Хочу сделать условное форматирование ячейки О1, при условии, что хотя-бы одна из ячеек диапазона J1:N1 содержит хотя-бы одно из следующих значений: "акт", "ключ", "без счета".
Здравствуйте, Дмитрий!
Помогите, плиз)
Хочу сделать условное форматирование ячейки О1, при условии, что хотя-бы одна из ячеек диапазона J1:N1 содержит хотя-бы одно из следующих значений: "акт", "ключ", "без счета".
Помогите, есть 5 столбцов. А, В, Cи X Y Z (к примеру) и нужно форматирование построчно... Если x = a ; у = в ; с <0, то z красный... Как сделать?
Выделяете столбец Z -Условное форматирование -на основании формулы. Вписываете формулу:
=И(X1=A1;Y1=B1;C1<0)
Здравствуйте, подскажите как выделить ячейки по следующим условиям
1. если значения ячейки В5 Х5 - выделения красным цветов
3. если значения ячейки В5 находится в промежутке значений от W5 до Х5 - выделения зеленым цветов
При этом правила выделений должны распространяться на диапазон ячеек В5-В28 и выполнение условий находящихся в диапазоне W5-W28 и X5-X28.
пробовал через УФ предустановленных правил и между условия работают только для столбца В. в других столбцах правила не работают
vita_79, Вам лучше задать вопрос и приложить пример. По описанию "ячейка такая-то в зависимости от такой-то" сложно что-то конкретное подсказать без примера данных. Могу только дать направление - смотрите в самом начале статьи ссылку на статью по условному форматированию: там подробно описано, что для закрашивания строки таблицы или ячеек на основании других, надо использовать формулу, а не встроенные условия.
Добрый день! Подскажите, пжл, можно ли при сравнении двух таблиц сравнивать только форматы ячеек? Т.е. если в одной таблице ячейка желтая, а в другой без цвета то выдать красный цвет. Нужно сравнить две таблицы, но не по значениям, а именно по форматам ячеек (по наличию или отсутствию заливки).
Александр, при помощи только УФ нельзя. Нет встроенных функций, которые могли бы определять цвета шрифта и заливки. Только путем написания собственного кода на VBA.
Дмитрий, понял, спасибо! Жаль, что УФ так не умеет. Думал, цвет - это же тоже значение и его можно использовать.
Добрый день, подскажите как выделить защищаемые ячейки.
Спасибо.
Если опираться на диапазон от А1 и далее, то при помощи такой формулы:
=ЯЧЕЙКА("protect";A1)
Здравствуйте, подскажите, пожалуйста, как использовать условное форматирование, если существует два раздела А и В и пять блоков 1-5, в которых нужно просуммировать результат, чтобы он был больше 3 и подсвечивался соответственно.
Мария, совершенно ничего не понятно по описанию. Думаю, правильнее будет , где создать тему и приложить файл. Плюс более подробно описать что за разделы и что за блоки и где там что нужно суммировать и искать больше 3.