Дополнительные возможности условного форматирования

Приведенные выше примеры показывают лишь небольшую часть возможностей условного форматирования. Оно упрощает выделение определенных ячеек или диапазона ячеек и обеспечивает визуализацию данных с помощью гистограммы, цветовых шкал и наборов значков, не прибегая к помощи Visual Basic for Applications. Оно изменяет внешний вид диапазона ячеек на основе указанного условия (или критерия). Если условие выполняется, то диапазон ячеек форматируется в соответствии с заданным для условия форматом. Например, можно выделить ячейку с текущей датой; ячейку с числом, входящим в указанный диапазон; ячейка с определенным текстом и т.п.

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

Для создания условного форматирования ячеек необходимо:

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

2. В меню выбрать: вкладка Главная - Условное форматирование.

3. Выбрать способ форматирования: цвет заливки, цвет шрифта, формат отображения, границы и т.д.

4. Подтвердить нажатием кнопки ОК

Условное форматирование в Excel 2007-2010 позволяет создавать мощные визуальные эффекты для ячеек, при этом количество условий для одной ячейки/диапазона практически неограничено.

ПРЕДУСТАНОВЛЕННЫЕ ПРАВИЛА.

1. Правила выделения ячеек. .

В Excel 2007-2010 эти правила содержат условия: Больше, Меньше, Между, Равно, Текст содержит, Дата, Повторяющиеся значения. Названия пунктов говорят за себя, и не нуждаются в подробных описаниях их функционала. Чуть более подробно можно рассмотреть лишь Дата и Повторяющиеся значения.

Дата:

Список содержит несколько значений: Вчера, Сегодня, Завтра, За последние 7 дней, На прошлой неделе, На текущей неделе, На следующей неделе, В прошлом месяце, В этом месяце, В следующем месяце.

Соответственно, при выборе необходимого условия даты в указанном диапазоне, соответствующие условию, будут отформатированы.

 

Повторяющиеся значения:
Если в выпадающем списке выбрать повторяющиеся, то выделены будут все значения, которые встречаются в диапазоне более одного раза.
Если выбрать уникальные, то выделены будут все значения, которые встречаются только один раз.

Правила отбора первых и последних значений.

Содержит условия: Первые 10 элементов, Первые 10%, Последние 10 элементов, Последние 10%, Выше среднего, Ниже среднего.

 

Гистограммы.

Сплошная заливка и Градиентная заливка. Отличаются между собой визуализацией. Для чего их можно применять: например, в столбце последовательно записаны данные по продажам за месяц и необходимо наглядно отобразить их разницу между собой.

Что важно знать при применении данных условий: они работают только при применении к диапазону ячеек с числовыми данными. 100%-му заполнению шкалы соответствует максимальное значение среди выделенных ячеек. Т.е. ячейка с максимальным значением будет заполнена полностью, а остальные ячейки будут заполнены относительно этой ячейки.

 

 

Цветовые шкалы.

Работает по тому же принципу, что и Гистограммы: на основе числовых значений выделенных ячеек, но закрашивает не часть ячейки методом шкалы, а всю ячейку, но с различной интенсивностью или цветом. Можно создать условие, при котором ячейка с максимальной продажей будет за крашена самым насыщенным цветом, а минимальная — практически незаметно (слева),

или добавить к этому еще различие по цветам (справа).

 

 

Наборы значков.

Служат для тех же целей, что и шкалы и гистограммы, но имеют менее гибкую систему отображения различий. Отражает различия между значениями ячеек по 2-х, 3-х, 4-х или 5-ти ступенчатой системе. Это значит, что если выбран набор из 3-х значков, то разница между минимальным и максимальным значением будет поделена на 3 и каждая третья часть будет со своим значком. Более наглядно можно увидеть, применив данное условие к числам от 1 до 9 (слева). Для отражения разницы между значениями так же очень хорошо подходят значки в виде мини-гистограмм (справа).

 

ИСПОЛЬЗОВАНИЕ ФОРМУЛЫ ДЛЯ ОПРЕДЕЛЕНИЯ ФОРМАТИРОВАНИЯ

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

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

2. В меню выбрать: вкладку ГлавнаяàУсловное форматированиеàСоздать правилоàИспользовать формулу для определения форматируемых ячеек.

3. Вписать в поле формулу (Сборник формул для условного форматирования - ниже).

4. Выбрать способ форматирования ячеек: цвет заливки, цвет шрифта, формат отображения, границы и т.д. Нажать ОК.

 

РЕДАКТИРОВАНИЕ УСЛОВИЙ УСЛОВНОГО ФОРМАТИРОВАНИЯ.

1. Выделить диапазон ячеек, таблицу, условное форматирование которых требуется изменить.
2. Вкладка Главная àгруппа Стили à Условное форматирование à Управление правилами.
3. Выбрать необходимое правило, которое необходимо изменить.
4. Нажать кнопку Изменить (Добавить) правило.

 

УДАЛЕНИЕ УСЛОВНОГО ФОРМАТИРОВАНИЯ

Вкладка Главная à группа Стили à Условное форматирование à Удалить правила à (Удалить правила со всего листа).

Также можно удалить только определенное правило из указанных ячеек: