2.2.9. Выделение категории студентов с помощью условного форматирования

Для выделения результатов вычисления или других значений эти ячейки можно определить при помощиусловных форматов (формат (например, узор ячейки или цвет шрифта), который Microsoft Excelавтоматически применяет к ячейке, если выполняется указанное условие). Например, пусть ячейка содержит формулу, вычисляющую разницу между прогнозируемыми и реальными продажами. Если реальные продажи превышают прогнозируемые, Microsoft Excel может выделить ячейку зеленым цветом, а в обратном случае - красным. Если значение ячейки изменилось и больше не соответствует необходимым свойствам, Microsoft Excel временно скрывает форматы, выделяющие это свойство. Условные форматы остаются до тех пор, пока их не удалят, даже если ни одно из свойств не соответствует данным, и определенные форматы ячеек не выводятся на экран.

Мы с помощью условного форматирования будет выделять в таблице двоечников. Для выполнения условного форматирования необходимо выполнить следующие шаги:

Шаг 1. Выделение ячеек, формат которых необходимо изменить.

Выделите всю таблицу с фамилиями, оценками, средним баллом и стипендией. Вы можете это выполнить по-разному, либо мышью, либо клавишами Shift,стрелками иPageDown.

Напоминание. Быстрое выделение осуществляется так: встаньте на ячейку с фамилией "С1". Нажмите клавишиShift иCtrl. Удерживая их, нажмите клавиши "стрелка вправо", "стрелка вниз" (в любом порядке). Диапазон выделен. КлавишаShift производит выделение (со стрелками илиPageUp, PageDown), а клавишаCtrl быстрое перемещение к следующему пустому пространству (с теми же клавишами).

Шаг 2. Выбор в меню "Формат" команды "Условного форматирования".

Шаг 3.Чтобы в качестве условия форматирования использовать значения выделенных ячеек, выберите параметр "значение", выберите операцию сравнения, а затем в необходимое поле введите значение. Ввести можно заданное значение или формулу, но перед формулой необходимо поставить знак равенства (=).

Для оценки данных или условий, отличных от значений выделенных ячеек, в качестве критерия форматирования используйте формулу. Слева из поля выберите параметр "формула", а затем в поле справа введите формулу. Формула должна принимать логическое значениеИСТИНАилиЛОЖЬ.

Формулы в качестве критерия форматирования

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

Чтобы при выбранном параметре значение сравнить значения выделенных ячеек с результатом формулы, формула должна начинаться со знака равенства. Вычисления по данной формуле могут производиться только на активном листе. Чтобы оценить данные на другом листе или в другой книге, на активном листе необходимо определить имя для данных на другом листе или в другой книге либо ввести ссылку на данные в ячейке активного листа, затем сослаться на указанную ячейку или имя в формуле.

Ссылки на ячейки можно ввести в формулу, выделив ячейки непосредственно на листе. Выделение ячеек на листе вводит абсолютные ссылки. Если необходимо, чтобы были определены ссылки для каждой ячейки в выделенном диапазоне, используйте относительные ссылки.

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

=И($AS17="двоечник")

Логическая формула Иуказывает на то, что последующая формула (или выражение) должна выполняться с формулами, записанными в ячейках в выделенном диапазоне. Формула заключается в простом сравнении значений ячеек, стоящих в столбцеAS со значением "двоечник". Абсолютная адресация по столбцу необходима для того, чтобыExcelприменял эту формулу только для ячеек содержащих категорию студентов, а не для всех выделенных ячеек. Относительная адресация по строкам позволяет для каждой строки применять свою формулу.

Такая формула позволит выделить строки в таблице, которые удовлетворяют этой формуле.

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

Шаг 4.Нажмите на кнопку "Формат" в появившемся окне.

Шаг 5.Выберите шрифт, его цвет, подчеркивание, рамку и затенение ячеек или шаблоны.

Выбранные форматы будут применены, только если значение ячейки отвечает поставленному условию или если формула принимает значение ИСТИНА.

Шаг 6.Для добавления условия выберите команду "Добавить", а затем повторите шаги 3-5.

Указать можно до трех условий (если Вы форматируете с помощью формул, то можно расширить формулы). Добавление условий позволит выделить до четырех различных категорий (четвертая задается обычным форматом). Например, двоечников можно выделить красным цветом, троечников желтым, хорошистов синим, а отличников зеленым.

Задание №3. Выделите в таблице все строчки с двоечниками красным жирным шрифтом.

План лабораторной работы: Контрольные вопросы