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

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

ИСПОЛЬЗОВАНИЕ ОСНОВНЫХ СРЕДСТВ MS EXCEL

В ТАБЛИЧНЫХ РАСЧЕТАХ

Цели работы:

1) закрепить навыки работы со стандартными функциями MS Excel;

2) изучить возможности использования условного форматирования;

3) закрепить навыки форматирования таблиц.

Задание:

1) изучите п.1 «Учебный материал»;

2) выполните задания, приведенные в п.2;

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

Учебный материал

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

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

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

 

Рис. 1. Меню команды Условное форматирование

 

Работу рассмотрим на примере. Введем в ячейку А1 число 1, в блок В1:В15 арифметическую прогрессию 1, 2, …, 15, в С1 введем формулу =$A$1*B1 и скопируем ее в С2:С15. Скроем столбец В (выделить столбец→ПКМ→Скрыть). Требуется наложить формат: 1) если число лежит в диапазоне от 10 до 19, то выводится курсивом; 2) если от 20 до 40, то выводится полужирным курсивом в рамке; 3) если больше 40, то выводится красным шрифтом на голубом фоне.

Решение. Выделить блок С1:С15.

1) для условия 1: вкладка Главная→группа Стили→Условное форматирование→Правила выделения ячеек→Между…. Далее, как на рисунке 2. В качестве пользовательского формата выбрать курсив. ОК.

 

Рис. 2. Задание первого критерия

 

2) для условия 2: вкладка Главная→группа Стили→Условное форматирование→Правила выделения ячеек→Между…. Далее, как на рисунке 3. В качестве пользовательского формата выбрать полужирныйкурсив, внешние границы. ОК.

 

Рис. 3. Задание второго критерия

 

3) для условия 3: вкладка Главная→группа Стили→Условное форматирование→Правила выделения ячеек→Больше…. Далее, как на рисунке 4. В качестве пользовательского формата выбрать красный шрифт, заливка. ОК.

 

Рис. 4. Задание третьего критерия

 

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

Отменим условие 2 («если от 20 до 40, то…»). Для этого выделим С1:С15, в списке команд Условное форматирование выберем команду Управление правилами…. Будет предложено новое диалоговое окно, где выделим удаляемое условие, нажмем кнопку Удалить правило и далее ОК (рис.5). Оцените результат.

 

Рис. 5. Удаление правила

 

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

Введите в ячейки D1:D10 произвольные значения, среди которых есть нули. Отметим значения, содержащие нули, используя условное форматирование. Для этого:

1) выделите диапазон D1:D10;

2) выберите команду Условное форматирование→Создать правило…;

3) в разделе Выберите тип правила выделите пункт Форматировать только ячейки, которые содержат и в нижней части окна заполните поля, как показано на рисунке 6, и нажмите ОК;

 

Рис. 6. Диалоговое окно Создание правила форматирования

 

4) результат форматирования показан на рисунке 7.

 

Рис. 7. Результат форматирования ячеек, содержащих нули

 

Условное форматирование можно задать формулой. Введем в Е1:Е15 прогрессию 1, 2, …, 15. Нужно выделить полужирным шрифтом и красным цветом числа, кратные трем, но не кратные двум. Порядок выполнения:

1) выделите диапазон E1:E15;

2) выберите команду Условное форматирование→Создать правило…;

3) в разделе Выберите тип правила выделите пункт Использовать формулу для определения форматируемых ячеек и в нижней части окна заполните поля, как показано на рисунке 8, и нажмите ОК. Будут выделены числа 3,9,15.

 

Рис. 8. Задание условного форматирования формулой