Методические материалы по информатике

Электронные таблицы: Excel, условное форматирование

Данный термин (Conditional formating) был не совсем корректно переведен с английского языка. Было бы правильнее говорить о форматировании по условию.

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

Функция крайне важна, так как позволяет выделить ячейки определенным образом

Диалог выглядит как показано на рисунке.

Первый список позволяет указать, относительно чего будет формироваться условие: значений (Cell value is) или формул (Formula is).

В случае значений, нужно указать одно из условий сравнения

Для формулы –

Некоторые из них далее предполагают ввод двух (например, см. выше: между 5 и 10), а другие – одного значения (например, больше 5).

Кнопка справа от поля ввода () переводит программу в режим выбора координат ячеек при помощи мыши:

Помеченные ячейки выделяются пунктиром, а в поле вводятся правильные координаты:

Разумеется, что координаты можно и набрать, но при этом возрастает вероятность ошибки.

В большинстве случаев поля для ввода условий заполняются не координатами, а значениями (числовыми или текстовыми).

Если условие представляет собой текст, то его можно ввести не задумываясь. Excel сам превратит его в нужный вид. Примером может служить наш электронный журнал, где ячейка с пропусками помечается светло-коричневым фоном. В поле значения условия записано ="н".

После задания условия, необходимо разъяснить программе, каким образом отформатировать ячейку, когда это условие в ней выполняется. Нажимаем кнопку Формат... и переходим к усеченному диалогу задания параметров ячейки:

Кнопка А также >> (Add >>) позволит добавить дополнительные условия. К сожа­лению, их количество ограничено только тремя.

Ошибки в задании условий

1. Условие 1: от 10 до 1000.
   Условие 2: равно 200. Оно не будет выполняться: уже сработало условие 1.
   Условие 3: равно 500. Оно также не выполняется: уже сработало условие 1.
   Выход: первое условие надо сделать третьим. Как? Удалить условие 1 и создать условие 3 с таким же содержанием. Вряд ли такая работа кому-либо понравится.
   Вывод: прежде чем задавать условия, необходимо продумать, не перекроется ли последующее предыдущим.
2. Условие задается, а форматирование не указывается. Надо внимательно смотреть на диалог, оценивая будущий внешний вид ячеек в образце. Слова «Формат не задан» могут привлечь внимание :)
3. Форматирование, указанное в диалоге, делает текст нечитабельным. Например, черный текст по коричневой заливке. См. предыдущий пункт.
4. Условия, разбивающие значения на диапазоны пересекаются или наоборот, оставляют «дыры».
   Пример: надо выделить отрицательные и неотрицательные числа. Условия <0 и >0 оставляют ноль вне рассмотрения. Второе условие должно было быть «не меньше 0» (то есть ≥0).

Редактирование условий

Выделяем ячейки, в которых нужно изменить условие форматирования и меняем его. Казалось бы всё очень просто, НО!

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

Чтобы полностью удалить условное форматирование, можно (предварительно выделив нужные ячейки) зайти в основной диалог и нажать кнопку Удалить... (Delete...). Появится запрос:

После пометки необходимых условий и подтверждения в двух диалогах (кнопкой OK), условия будут удалены.