InformatikUrok

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

Сегодня на занятии мы узнаем, как автоматически выделять цветом ячейки (строки), которые удовлетворяют определенному условию, научимся создавать условное форматирование в Excel 2003 для ячеек с помощью значений и формул, научимся удалять условное форматирование.

Представим себе, что мы создали таблицу, в которой много строк и столбцов. Удобно в ней разбираться? Можно ли быстро найти ответы на вопросы, просматривая таблицу? Нет, трудно читать не отформатированную таблицу. Что предлагает нам MS Excel? Конечно же, форматирование. И сегодня  мы рассмотрим не просто форматирование ячеек, а условное форматирование в Excel 2003.

Что такое условное форматирование? Это форматирование ячеек или строк (выделение цветом, узором, начертанием шрифта, выделением границ ячейки), которое выполняется только при определенном условии. Например, используя условное форматирование, можно

• выделить разным цветом хорошистов, отличников и троечников;
• выделить цветом минимальную оценку по предмету;
• выделить всех, кто имеет оценки, не ниже среднего балла по группе;
• выделить цветом тех, кто имеет задолженность по оплате на 01.10.2012 и тех, кто обещал в десятидневный срок погасить долг по оплате и т.д.

Для создания условного форматирования в Excel 2003, нужно:

1. Выделить диапазон ячеек, к которому нужно применить это форматирование.
2. Выполнить команду Формат-Условное форматирование.
3. Задать условие (значение равно, меньше, больше и т.д. или ввести конкретную формулу).
4. Нажать кнопку Формат и выбрать стиль форматирования: изменить начертание шрифта, или задать границы для ячейки, или установить цвет (узор) в ячейке.

А теперь, когда мы знаем, что такое условное форматирование, и как его добавить к готовой таблице, давайте выполним несколько заданий.

Задание 1. Создать в Excel 2003 на Листе 1 таблицу успеваемости для группы из 10 чел по предмету Математика (оценивание по 12 бальной системе). Выделить всех отличников, например, желтым цветом, троечников – красным, а хорошистов – зеленым.

После того, как таблица создана, выделить диапазон ячеек С2:С11, выполнить команду Формат-Условное форматирование, выбрать для Условия 1 вариант значения Между 4 и 6 (троечники), нажав кнопку Формат на вкладке вид выбрать красный цвет.

Далее нажать кнопку "А также>>", чтобы добавить еще условия форматирования. Обратите внимание, что в Excel 2003 таких условий может быть не больше трех.

Задание 2. Скопировать таблицу на Лист2. Выделить цветом минимальные значения оценки.

Перед тем, как выполнить новое форматирование, нужно удалить старое, для этого:

• выделить диапазон С2:С11 скопированной таблицы;
• выполнить команду Формат-Условное форматирование;
• нажать кнопку Удалить, и в открывшемся окне установить флажки Условие 1, Условие 2, Условие 3, потом нажать Ок.

В данном примере будем использовать ФОРМУЛУ.

ОБРАТИТЕ ВНИМАНИЕ: формула всегда начинается со знака равно (повторить работу с формулами поможет статья "Простейшие вычисления в  таблицах Excel"). Если нужно выделить, например, минимальные значения, то формула будет иметь вид: =C2=МИН($C$2:$C$11). В этой формуле каждое значение сравнивается с минимальным значением по диапазону, и если есть совпадение, то ячейка заливается цветом.

Задание 3. Скопировать таблицу на Лист3. Выделить цветом всю строку для тех, у кого оценки по предмету не меньше средней по группе.

Для решения этой задачи сначала нужно выделить диапазон А1:С11, а потом Формат-Условное форматирование и ввести формулу. Кстати, для записи диапазона в формуле можно использовать кнопочку с красной стрелкой и обязательно диапазон взять в скобки!!!

В формуле =$C2>=СРЗНАЧ($C$2:$C$11) обратите внимание на знак доллара. Адрес ячейки $C2 означает, что столбец С зафиксирован, а строка незафиксирована, это означает, что каждое значение $C2, $C3 и т.д. будет сравниваться со средним значением в диапазоне С1:С11, и если оно >= среднего, то ячейка будет заливаться цветом.

Задание 4. Скопировать таблицу на Лист4. Удалить третий столбец с оценками. Ввести столбец Срок оплаты (даты). В ячейку D1 ввести дату 01.10.2012. Выделить красным цветом всех, кто обещал оплатить услуги до 01.10.2012. Выделить синим цветом тех, кто оплатит с 01.10.2012 по 10.10.2012г., т.е. в десятидневный срок.

Даты – это тоже числа и их можно использовать в формулах для условного форматирования ячеек.

Формула =C10<$D$1 определяет всех задолжников на 01.10.2012г.

Формула =(C10-$D$1)<10 – определяет тех, кто оплатит услуги в десятидневный срок.

Сегодня на занятии узнали:

• как применить условное форматирование в Excel 2003;
• как сделать условие на значение;
• как в условии форматирования использовать формулу;
• как сделать несколько условий форматирования в одном запросе;
• как удалить условное форматирования.