Условное форматирование позволяет автоматически изменять форматирование ячеек в зависимости от их содержимого.
Например, нужно отформатировать диапазон ячеек таким образом, чтобы отрицательные значения отображались на светло-желтом фоне. При вводе или изменении содержимого ячейки Excel проверит введенное значение и сравнит с правилами условного форматирования. Если введенное значение отрицательное, фон поменяется на светло-желтый, в противном случае ничего не произойдет.
Условное форматирование можно использовать определения ячеек, которые содержат недопустимые значения, или ячеек определенного типа. Затем согласно примененному форматированию можно определить, какие ячейки содержат значения определенного типа. Например, к искомым ячейкам можно применить ярко-красный фон.
Для задания условного форматирования в ячейке или диапазоне выполните ряд действий.
1. Выделите диапазон или ячейку.
2. Выберите команду Формат=>Условное форматирование. На экране появится одноименное диалоговое окно, показанное на рис. 3.
Рис. 3. Диалоговое окно Условное форматирование
3. Из раскрывающегося списка выберите либо значение (для простого условного форматирования), либо формула (для задания формата с использованием формул).
4. Определите условие (или введите формулу).
5. Щелкните на кнопке Формат и задайте параметры форматирования, которые следует применить, если условие выполняется (т.е. результат вычисления условия — ИСТИНА).
6. Чтобы задать дополнительные условия, щелкните на кнопке А также и повторите
пп. 3-5. Всего можно задавать до трех условий.
7. Когда все будет сделано, щелкните на кнопке ОК.
Теперь ячейка или диапазон будут отформатированы по заданным условиям. Такое форматирование является динамическим. Это значит, что, если содержимое ячейки изменится, Excel оценит новое значение и в соответствии с ним изменит форматирование ячейки.
После нажатия на кнопке Формат диалогового окна Условное форматирование
откроется окно Формат ячеек (рис.4).
Рис. 4. Диалоговое окно Формат ячеек, используемое для условного форматирования
Оно несколько отличается от обычного окна Формат — в нем отсутствуют вкладки Число, Выравнивание и Защита, но появилась новая Очистить.
С помощью диалогового окна Найти и заменить можно выделить на рабочем листе ячейки, к которым было применено определенное форматирование. Но с помощью этого окна нельзя определить ячейки, к которым было применено условное форматирование.
В левой части диалогового окна Условное форматирование есть раскрывающийся список (см. рис. 3), позволяющий выбрать одну из двух опций.
- значение. Используется для определения простых условий.
- формула. Используется для определения более сложных условий, в которых применяются формулы.
После выбора элемента значение в следующем раскрывающемся списке можно определить перечисленные ниже типы условий.
- между. Указывается два значения.
- вне. Указывается два значения.
- равно. Указывается одно значение.
- не равно. Указывается одно значение.
- больше. Указывается одно значение.
- меньше. Указывается одно значение.
- больше или равно. Указывается одно значение.
- меньше или равно. Указывается одно значение.
Ввести значения можно как вручную, так и с помощью ссылки на ячейку.
При выборе элемента Формула в поле, расположенном справа, нужно задать формулу. Это можно сделать, просто определив ячейку с формулой на рабочем листе, а затем указав ссылку на нее, или же введя ее непосредственно в диалоговом окне Условное форматирование. Как всегда, перед началом формулы должен стоять знак равенства (=).
Рекомендуется вводить формулу непосредственно в диалоговом окне Условное форматирование.
Формула должна возвращать только логические значения ИСТИНА или ЛОЖЬ. Если результат соответствует значению ИСТИНА, условие выполняется и к ячейке или диапазону применяется условное форматирование. Иначе (если результат принимает значение ЛОЖЬ) условное форматирование не применяется.
Если в формуле, введенной в диалоговом окне Условное форматирование, используется ссылка на ячейку, то эта ссылка будет относительной по отношению к верхней левой ячейке выделенного диапазона.
Например, если нужно задать условное форматирование, которое будет менять цвет заливки пустых ячеек в диапазоне С2:С10, выполните ряд действий.
1. Выделите диапазон С2:С10, начиная с ячейки С2, т.е. чтобы ячейка С2 была активной.
2. Выберите команду Формат => Условное форматирование. Откроется диалоговое окно Условное форматирование.
3. Из левого раскрывающегося списка выберите формула.
4. В поле справа от выпадающего списка введите формулу =С2=”” .
5. Щелкните на кнопке Формат, чтобы открыть диалоговое окно Формат ячеек.
6. В этом диалоговом окне определите цвет заливки и щелкните на кнопке ОК.
7. В диалоговом окне Условное форматирование щелкните на кнопке ОК, чтобы закрыть его.
Обратите внимание на то, что введенная формула содержит ссылку на верхнюю левую ячейку выделенного диапазона. Чтобы убедиться в том, что ссылка является относительной, нужно выделить одну из ячеек диапазона (например, С5) и посмотреть на формулу условного форматирования для этой ячейки. Формула будет иметь следующий вид: =С5=””
Обычно при вводе формулы условного форматирования для диапазона ячеек используется ссылка на активную ячейку, которой, как правило, является верхняя левая ячейка выделенного диапазона. Исключение составляют случаи, когда нужно использовать ссылку на определенную ячейку.
Например, в диапазоне А2:В10 необходимо применить условное форматирование только к тем ячейкам, значения которых больше значения ячейки С1. Для этого следует ввести формулу =А2>$С$1. В этом случае ссылка на ячейку С1 будет абсолютной и одинаковой во всех ячейках выделенного диапазона. Другими словами, формула условного форматирования, например, для ячейки А3 будет выглядеть следующим образом: =А3>$С$1. Следовательно, для всех ячеек выделенного диапазона относительные ссылки будут изменяться в соответствии с позицией ячейки в диапазоне, а абсолютные всегда будут ссылаться на одну и ту же ячейку.
Щелкнув на кнопке А также диалогового окна Условное форматирование, можно определить дополнительное условие (трех условий).
Например, для ячейки или диапазона можно определить следующие условия (а также параметры форматирования, которые будут применяться при выполнении тех или иных условий):
Значение ячейки меньше 0
Значение ячейки равно 0
Значение ячейки больше 0
В этом случае форматирование будет зависеть от знака содержащегося в ячейке значения (отрицательное, положительное или ноль).
Если ни одно из условий не примет значение ИСТИНА, форматирование ячейки не
изменится. Если же будет выполняться более одного условия, Excel применит форматирование в соответствии с первым выполненным условием. Например, такая ситуация может произойти, если задать следующие условия:
Значение ячейки лежит между 1 и 12
Значение ячейки меньше 6
В этом случае, если ячейка будет содержать, например, число 4, выполнятся оба условия, но ячейка будет отформатирована в соответствии с параметрами, установленными для первого условия.
Информация об условном форматировании сохраняется в ячейке почти так же, как и стандартное форматирование. Это значит, что при копировании ячейки копируется и
примененное к ней условное форматирование (если оно есть).
Чтобы копировать только условное форматирование, в диалоговом окне Специальная вставка нужно выбрать опцию форматы.
Добавление строк и столбцов в диапазон, в котором не используется условное форматирование, создаст новые ячейки с теми же параметрами условного форматирования.
Копирование или вставка ячейки в диапазон, который содержат условное форматирование, приводят к его удалению. Если нужно вставить скопированные данные в ячейку, в которой используется условное форматирование, можно воспользоваться диалоговым окном Специальная вставка и выбрать опцию значения.
При удалении содержимого ячейки с помощью нажатия клавиши <Delete> условное форматирование, которое ранее было применено к этой ячейке, не удаляется. Чтобы удалить условное форматирование как и прочее форматирование, выберите команду Правка => Очистить=>Форматы.
Для удаления содержимого ячейки (включая форматирование) выберите команду Правка=> Очистить=> Все.
Чтобы удалить только условное форматирование (и оставить содержимое и другие параметры форматирования ячейки), выполните ряд действий:
1. Выделите ячейки. Затем выберите команду Формат=>Условное форматирование; откроется одноименное диалоговое окно.
2. В диалоговом окне Условное форматирование нажмите кнопку Удалить. Откроется диалоговое окно Удаление условия форматирования (рис. 5).
Рис. 5. Диалоговое окно Удаление условия форматирования
3. В этом диалоговом окне установите флажки напротив условий, которые нужно удалить. Обратите внимание: в этом окне всегда отображается три условия, даже если в действительности определено меньше условий.
4. Щелкните на кнопке ОК, чтобы удалить выбранные условия и закрыть диалоговое окно Удаление условия форматирования. Затем в диалоговом окне Условное форматирование вновь щелкните на кнопке ОК, чтобы закрыть его.
Просто посмотрев на ячейки, невозможно сказать, какие из них содержат условное форматирование, а какие — нет. Но выделить ячейки с условным форматирование можно с помощью диалогового окна Переход. Для этого выполните ряд действий.
1. Выберите команду Правка => Перейти (или нажмите F5 или <Ctrl>+<G> ). Откроется диалоговое окно Переход.
2. В диалоговом окне Переход щелкните на кнопке Выделить, чтобы открыть диалоговое окно Выделение группы ячеек, которое показано на рис. 3.6.
Рис. 6. Диалоговое окно Выделение группы ячеек
3. Чтобы выделить все ячейки, содержащие условное форматирование, установите
переключатель всех. Если установить переключатель в положение этих же, можно выделить ячейки, к которым применено такое же условное форматирование, как и к активной ячейке.
4. Щелкните на кнопке ОК. Excel выделит необходимые ячейки.
При попытке использовать условное форматирование, которое содержит ссылки на другие листы, Excel выдает сообщение об ошибке.
Чтобы сослаться на ячейку другого рабочего листа, можно использовать два варианта:
Первый вариант. Необходимо создать ссылку на эту ячейку в том рабочем листе, в котором будет использоваться условное форматирование.
Например, если необходимо, чтобы формула условного форматирования ссылалась на ячейку А1 рабочего листа ЛистЗ, нужно вставить в ячейку активного листа следующую формулу.
=ЛистЗ!А1
После этого можно использовать ссылку на данную ячейку в формуле условного форматирования.
Второй вариант. Можно присвоить имя ячейке, на которую нужно создать ссылку . Тогда это имя можно использовать вместо ссылки в диалоговом окне Условное форматирование. Поименованная ячейка может находиться на любом рабочем листе активной рабочей книги.
Пример1. Определение нечисловых данных
Представленное на рис. 7. условное форматирование будет применять форматирование к ячейке А1 только в том случае, если в ней содержится текст. Чтобы применить такую формулу к диапазону, нужно сначала выделить необходимый диапазон. Аргументом функции ЕТЕКСТ должна быть активная ячейка (обычно это верхняя левая ячейка диапазона).
Рис. 7. окно условно форматирования для примера 1
Пример 2. Выделение ячеек, значения которых выше среднего
В этом примере условное форматирование применяется к диапазону A1:С4, только к тем ячейкам, значения которых выше среднего (рис. 3.8).
Рис. 8. Окно условно форматирования для примера 2
Стоит обратить внимание на то, что ссылка на ячейку А1 является относительной, в то время как аргументом функции СРЗНАЧ должна быть абсолютная ссылка. На рис. 3,9 представлен результат применения этого условного форматирования
Рис. 9. Результат применения условного форматирования примера 2
