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

Лабораторная работа № 17

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

Время на выполнение – 2 часа

Цель работы: научиться условно форматировать ячейку документа Excel и распространять условное форматирование на целый диапазон.

Основные сведения по теме

Поставив курсор на ту ячейку, оформление которой должно меняться, следует вызвать этот пункт в меню Формат и в появившемся диалоговом окне ввести условия, при выполнении которых ячейка должна менять свой внешний вид, и описать её оформление (для этого служит кнопка Формат в том же диалоговом окне).

Если же нужно сделать так, чтобы цвет менялся у одной ячейки, а значение, в зависимости от которого он это делает, находилось в другой, то воспользуйтесь пунктом "Формула" в левом выпадающем списке диалогового окна задания условного форматирования. "Условно-оформляемая" ячейка может находиться сколь угодно далеко от той, на основании значения которой это оформление определяется – главное, чтобы формула была написана правильно. Сама же формула может быть любой: вплоть до расчета статистической достоверности или значения амортизации актива.

В диалоговом окне Условное форматирование можно указать до трех условий, если же вам нужно больше, то придется проявить максимум изобретательности при составлении формул.

Последовательность выполнения

Задача. Рабочий лист должен содержать сводную ведомость студенческих оценок по итогам сессии. Если экзамены сданы без троек, соответствующая строка таблицы должна подсвечиваться зеленым цветом, если у студента остались задолженности – красным.

1. Запустите программу Excel (Пуск ► Программы ► Microsoft Office ► Microsoft Excel) и откройте рабочую книгу Лабораторные работы по Excel.xls.

2. Выберите щелчком на ярлычке неиспользуемый рабочий лист или создайте новый (Вставка ► Лист). Дважды щелкните на ярлычке листа и переименуйте его как Ведомость.

3. В первую строку рабочего листа, начиная с ячейки В1, введите названия четырех экзаменов.

4. В первый столбец рабочего листа, начиная с ячейки А2, введите фамилии 15 студентов.

5. Заполните таблицу экзаменационными оценками по своему усмотрению. Оценки должны изменяться от 2 до 5 баллов.

6. Выделите ячейку В2 и выполните команду Формат ► Условное форматирование.

7. В раскрывающемся списке на панели Условие 1 выберите вариант значение.

8. Выберите условие сравнения равно и поставьте значение 2.

9. Щелкните на кнопке Формат. В открывшемся диалоговом окне Формат ячеек выберите вкладку Вид и щелкните на светло-сером цвете для его использования в качестве фона ячеек. Щелкните на кнопке ОК.

10. Щелкните на кнопке ОК. Фон ячейки В2 должен измениться, если оценка в ячейке В2 равна 2, иначе никаких изменений не произойдет.

11. Выделите весь диапазон ячеек ведомости с оценками и дайте команду Формат ► Условное форматирование. Диалоговое окно Условное форматирование должно содержать настройки, подготовленные для ячейки В2.

12. Щелкните на кнопке ОК. Условное форматирование распространится на всю выделенную область с автоматической коррекцией относительных ссылок. Убедитесь, что формат ведомости соответствует тому, что требовалось.

13. Выделите ячейку А2 и дайте команду Формат ► Условное форматирование.

14. В раскрывающемся списке на панели Условие 1 выберите вариант формула.

15. В поле для формулы введите следующую формулу: =МИН($В2:$Е2)>3. Обратите внимание на способ использования абсолютных и относительных ссылок в формуле, так как ее планируется распространить на всю таблицу. Эта формула рассчитана на четыре экзамена при другом их числе выбранный диапазон несколько изменится.

16. Щелкните на кнопке Формат. В открывшемся диалоговом окне Формат ячеек выберите вкладку Вид и щелкните на светло-зеленом цвете для его использования в качестве фона ячеек. Щелкните на кнопке ОК.

17. Щелкните на кнопке А также, чтобы задать второе условие форматирования. Далее действуйте аналогично тому, как указано в п. 8. Нужная формула должна иметь вид: =МИН($В2:$Е2)<3.

18. Подобно тому, как указано в п. 9, задайте светло-розовый фон для ячеек.

19. Щелкните на кнопке ОК. Фон ячейки А2 должен измениться, если соответствующий студент не имеет троек или, напротив, имеет задолженность.

20. Выделите весь диапазон ячеек столбца А и дайте команду Формат ► Условное форматирование. Диалоговое окно Условное форматирование должно содержать настройки, подготовленные для ячейки А2.

21. Щелкните на кнопке ОК. Условное форматирование распространится на всю выделенную область с автоматической коррекцией относительных ссылок. Убедитесь, что формат ведомости соответствует тому, что требовалось.

22. Сохраните рабочую книгу.

Содержание отчета

1. Выполнение всех пунктов задания.

2. Выполнение задания для самостоятельной работы.

3. Ответы на контрольные вопросы.

Контрольные вопросы

1. Для чего применяется условное форматирование?

2. Сколько условий можно указывать, применяя условное форматирование?

3. Как изменить формат ячейки, в зависимости от значения, находящегося в другой ячейке?

4. Какие варианты условий существуют при применении условного форматирования?

5. Что включает в себя изменение формата ячейки?

6. На сколько ячеек может быть распространено условное форматирование?

7. Как отменить условное форматирование?

Задание для самостоятельной работы

Откройте книгу Задания для самостоятельной работы. Скопируйте лист Задание 4 и переименуйте его как Задание 7. Найдите среднее значение Надбавки, используя статистическую функцию. Применяя условное форматирование, выделите любым цветом ячейки с надбавками больше среднего значения, а другим цветом – меньше среднего.

Дата добавления: 2014-10-15