Условное форматирование в Excel 2003

105673 23.10.2012 Скачать пример

Основы

Все очень просто. Хотим, чтобы ячейка меняла свой цвет (заливка, шрифт, жирный-курсив, рамки и т.д.) если выполняется определенное условие. Отрицательный баланс заливать красным, а положительный - зеленым. Крупных клиентов делать полужирным синим шрифтом, а мелких - серым курсивом. Просроченные заказы выделять красным, а доставленные вовремя - зеленым. И так далее - насколько фантазии хватит. 

Чтобы сделать подобное, выделите ячейки, которые должны автоматически менять свой цвет, и выберите в меню Формат - Условное форматирование (Format - Conditional formatting).

В открывшемся окне можно задать условия и, нажав затем кнопку Формат (Format), параметры форматирования ячейки, если условие выполняется. В этом примере отличники и хорошисты заливаются зеленым, троечники - желтым, а неуспевающие - красным цветом:

logic1.gif

Кнопка А также>> (Add) позволяет добавить дополнительные условия. В Excel 2003 их количество ограничено тремя, в Excel 2007 и более новых версиях - бесконечно.

Если вы задали для диапазона ячеек критерии условного форматирования, то больше не сможете отформатировать эти ячейки вручную. Чтобы вернуть себе эту возможность надо удалить условия при помощи кнопки Удалить (Delete) в нижней части окна.

Другой, гораздо более мощный и красивый вариант применения условного форматирования - это возможность проверять не значение выделенных ячеек, а заданную формулу: 

condformat1.gif

 

Если заданная формула верна (возвращает значение ИСТИНА), то срабатывает нужный формат. В этом случае можно задавать на порядок более сложные проверки с использованием функций и, кроме того, проверять одни ячейки, а форматировать - другие. 

Выделение цветом всей строки

Главный нюанс заключается в знаке доллара ($) перед буквой столбца в адресе - он фиксирует столбец, оставляя незафиксированной ссылку на строку - проверяемые значения берутся из столбца С, по очереди из каждой последующей строки:

condformat2.gif

Выделение максимальных и минимальных значений

Ну, здесь все достаточно очевидно - проверяем, равно ли значение ячейки максимальному или минимальному по диапазону - и заливаем соответствующим цветом:

condformat3.gif

В англоязычной версии это функции MIN и MAX, соответственно.

Выделение всех значений больше(меньше) среднего

Аналогично предыдущему примеру, но используется функция СРЗНАЧ (AVERAGE) для вычисления среднего:

logic6.gif

Скрытие ячеек с ошибками

Чтобы скрыть ячейки, где образуется ошибка, можно использовать условное форматирование, чтобы сделать цвет шрифта в ячейке белым (цвет фона ячейки) и функцию ЕОШ (ISERROR), которая выдает значения ИСТИНА или ЛОЖЬ в зависимости от того, содержит данная ячейка ошибку или нет:

condformat4.gif

Скрытие данных при печати

Аналогично предыдущему примеру можно использовать условное форматирование, чтобы скрывать содержимое некоторых ячеек, например, при печати - делать цвет шрифта белым, если содержимое определенной ячейки имеет заданное значение ("да", "нет"):

condformat5.gif

Заливка недопустимых значений

Сочетая условное форматирование с функцией СЧЁТЕСЛИ (COUNTIF), которая выдает количество найденных значений в диапазоне, можно подсвечивать, например, ячейки с недопустимыми или нежелательными значениями:

condformat6.gif

Проверка дат и сроков

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

condformat7.gif

P.S.

Счастливые обладатели последних версий Excel 2007-2010 получили в свое распоряжение гораздо более мощные средства условного форматирования - заливку ячеек цветовыми градиентами, миниграфики и значки:

 condformat8.gif 

Вот такое форматирование для таблицы сделано, буквально, за пару-тройку щелчков мышью... :)

Ссылки по теме

 



12.04.2013 15:12:26
Коллеги, подскажите, как задать правило для форматирования ячейки, на которую перехожу по гиперссылке????
03.07.2013 09:27:32
Вы хотите подсвечивать цветом ячейку, куда приводит гиперссылка? Не думаю, что это возможно.
Доброго времени суток. Подскажите пожалуйста, с помощью условного форматирования задаю след. условия: - если значение меньше 14г.00мес.00дн - выделяем зеленым (например) цветом, если значение больше либо равно 14г.00меч.00дн - выделяем красным (например). Значение на которое создаю условное форматирование - расчетное. Получается следующее:
1. если значение равно формату 00г.00мес.00дн. (10г.03мес.15дн.) - все условия выполняются
2. если значение равно формату 0г.00мес.00дн. (3г.03мес.15дн.) - выделяет красным

Как сделать так, чтобы формат расчетного значения получался всегда 00г.00мес.00дн. (03г.03мес.15дн.)

Спасибо.
03.07.2013 09:26:48
Не видя вашего файла - трудно что-то определенное сказать. Лучше сделайте тему на форуме (предварительно прочитав правила), приложите свой файл - поможем!
01.08.2013 11:06:07
Добрый день, подскажите делаю все так как написано в примере   (Проверка дат и сроков
Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков выполнения задач. Например, для выделения просроченных элементов красным, а тех, что предстоят в ближайшую неделю - желтым:)

все равно не выходит(((((((((
подскажите как добавить сюда файл, чтоб показать как у меня получается?
10.08.2013 01:28:42
Сюда - никак, лучше создайте тему на Форумеи приложите ваш файл.
07.08.2013 13:04:32
не могу в 10 офисе найти позиции, что бы при одном условии окрашено было в один цвет, а при невыполнении условия в другой цвет
10.08.2013 01:27:52
Нужно создавать два разных правила с двумя разными цветами - одно на выполнение, и другое - на невыполнение условия.
12.08.2013 12:10:43
Спасибо, Николай

А можете еще в таком вопросе помочь:
в условном форматировании задаю Гистограммы, с помощью которых хочу сделать заливку ячеек (например столбец А) цветом в зависимости от сравнения данных в этой ячейке с данными ячеек из другого столбца (назовем столбец В).
Для первой ячейки срабатывает (назовем ее А1) , но для других ячеек (А2, А3...) в этом же столбце идет сравнение только ячейкой, с которой сравнивается самая первая (то есть В1). Убираю в А1 знак доллара перед единицей, а Excel мне отвечает, что в гистограммах работать с относительными ссылками нельзя
Подскажите как выйти из ситуации, как сделать заливку в ячейки в зависимости от ячейки, находящейся на той же строке
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=50302&PAGEN_1=166
14.09.2013 16:32:16
Николай добрый день! помогите мне! мне надо в excel сделать вот так ФИО столбец, ДАТА столбец и еще ДАТА !столбец! на втором столбце где дата например сегодня дата 14,09,2013, на третьем столбце где дата например  30 дней прошла (или 7 дней) и она высвечивался или цветом красным выделялось ячейка полностью! как это можно сделать? помогите !!!
ФИОДАТАДАТА
ИВАНОВ ИВАН 14.09.201314.10.2013
24.11.2013 10:53:49
Тимур, последний пункт прочитайте ("Проверка дат и сроков") - это ж оно один в один.
19.09.2013 08:06:05
День добрый!
Подскажите, пожалуйста, как правильно задать правила условного форматирования для выделения минимального значения в каждой строке "умной таблицы"?
24.11.2013 10:53:02
Также, как и обычной. Только используйте обычные адреса ячеек (вводите их с клавиатуры) вместо названий столбцов (типа [@цена]), которые Excel будет пытаться подставить в формулу, т.к. это умная таблица.
26.11.2013 14:09:19
Dobrij denj. mne nuwno na neskolkih sheetah zakrasit opredelenuju datu vezde odnim cvetom mowet ( naprimer 27.11. krasnim,28.11. sinim podskawite kak jeto sdelatj?
18.01.2014 19:28:32
Посмотрите это видео для начала.
Думаю, что вопрос отпадет :)
18.01.2014 16:51:19
Добрый День.
не получается Спрятать ошибку (#Н/Д) формулы ВПР.
18.01.2014 19:26:17
Как вам помочь, не видя вашего файла? Телепатически? :)
Создайте тему на форуме, приложите ваш файл - поможем.
11.02.2014 18:25:57
если просто спрятать - то функция =ЕСЛИОШИБКА(ВПР(......);"--"), в таком случае при возврате ошибки отобразится "--"
а если нужно разобраться почему возникает ошибка, то без примера - никак
28.08.2015 14:40:30
в очередной раз облегчили жизнь!
Спасибо!
20.03.2016 12:15:35
Коллеги, подскажите, как использовать логические ИСТИНА и ЛОЖЬ для условного форматирования значками?
CheckBox (флажок) генерирует ИСТИНА и ЛОЖЬ. Считал тривиальным действием связав CheckBox с А1 показать вместо ИСТИНА И ЛОЖЬ графический значок. Но это не работает.  
03.01.2017 16:39:50
подскажите, а как сделать так чтобы закрашивалось количество ячеек в строке в зависимости от числа ?? ну например, число 5 - закрасилось пять ячеек в сторке, 1 - одна ??  
04.01.2017 09:27:47
Выделить ячейки, Главная - Условное форматирование - Создать правило - Использовать формулу и ввести что-то похожее:




Т.е. используем функцию СТОЛБЕЦ (COLUMN), чтобы определить номер очередной ячейки, и если он меньше заданного - заливаем.
04.01.2017 12:28:42
:(спасибо за ответ, но что-то у меня ничего не меняется
24.05.2017 09:48:39
Добрый день! Подскажите как можно выделить всю строку в зависимости от того, содержит ли ячейка в первом столбце ЧАСТЬ текста или нет.
Например, когда вся ячейка должна была содержать текст sales, то было просто - =$A1="sales". А как быть если текст разный и совпадает только слово sales?
Спасибо заранее!
26.07.2017 09:02:34
Можно использовать функцию ПОИСК для проверки вхождения одного текста в другой. Будет примерно так:
=ПОИСК("sales";$A1)>0
23.07.2017 20:42:31
Здравствуйте, как сделать так, чтобы при значении в ячейке сегодняшней даты, строка окрашивалась в красный цвет, при значении меньше -в сирий, а больше в зелёный? Есть четыре столбца, ФИО телефон, время и дата, вот как сделать чтобы строка опрашивалась, а не только дата?
26.07.2017 08:58:01
Мурад, посмотрите статью про подсветку дат- как раз ответ на ваш вопрос
01.09.2017 17:36:43
Добрый день. Помогите пожалуйста, есть ряд в котором указаны названия дней сокращенно ( деньв каждой ячейке отдельно - пн вт ср чт пт сб вс пн вт ср чт пт сб вс.......) и так далее. Как с помощью формулы в условном форматировании выделить нужные мне 3 дня (например вс пн вт). Я хочу чтобы все эти дни в ряду были выделены одним цветом.
Наверх