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

13

Posted by Виктория Павловна Дончик | Posted in MS Excel | Posted on 30-10-2012

Метки:

Условное форматирование в Excel 2003Условное форматирование в 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;
  • как сделать условие на значение;
  • как в условии форматирования использовать формулу;
  • как сделать несколько условий форматирования в одном запросе;
  • как удалить условное форматирования.

Comments (13)

Отличный сайт, теперь свой бухотдел буду посылать сюда ))

Да, вы правы, методы работы с таблицами, описанные в статье очень пригодятся бухгалтерам, т.к. именно им нужно работать с таблицами больших объемов и быстро искать нужную информацию и условное форматирование им в этом очень поможет!
Виктория Павловна Дончик Как использовать функции Excel?My Profile

Интересная функция в excel. Кажется, я ею еще не пользовалась. Теперь буду знать, как она работает, спасибо!

Да, функция интересная, думаю, Вы найдете ей достойное применение.
Виктория Павловна Дончик Как использовать функции Excel?My Profile

Здорово всё расписано. Спасибо, а то с Excel есть некоторые проблемы.
Юрий Как защитить стены и фундамент дома от сырости и влаги ?My Profile

Спасибо, Юрий.
Виктория Павловна Дончик Условное форматирование в Excel 2003My Profile

запуталась я маненечко, хоть и на курсы ходила… Но думаю, если чуток подумать, то прояснится, не боги горшки обжигают… Уроки просто отличные!
Laralaram МOРТАДЕЛЛАMy Profile

Лара, не спорю, материал не для новичков, здесь и формулы знать нужно, и на кнопки правильные нажимать, а главное иметь очень большое желание разобраться (мотивацию) в условном форматировании, чтобы потом легче было ориентироваться в таблицах большого размера.
Виктория Павловна Дончик Ошибки в формулах ExcelMy Profile

Спасибо за наглядный урок по Excel. Сколько лет с ним работаю, а многого ещё не знаю!

очень интересно!) спасибо) Офис надо знать)
Леонид «Советы, для того, что бы не уснуть за рулем»My Profile

Виктория, на днях узнала, что в Excel можно делать кроссворды. Вы знаете как это делать?
Ирина Колотова Комментарий на Оборудование физкультурного уголка от ОлегMy Profile

Да, Ирина, я знаю. как это делается, постараюсь написать видео по этой теме. Если есть готовый материал, давайте, я на нем покажу, как делается кроссворд.
Виктория Павловна Дончик Как сделать таблицу в Access?My Profile

вот здесь можно посмотреть, как сделать кроссворд в Word http://informatikurok.ru/word/kak-narisovat-krossvord-v-word
Если будут вопросы пишите, будем разбираться.
Виктория Павловна Дончик Как создать сборник клипов в Movie MakerMy Profile

Write a comment

x