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

Обновлено: 23.01.2025

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

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

В статье рассмотрим:

ГДЕ РАСПОЛОЖЕНО УСЛОВНОЕ ФОРМАТИРОВАНИЕ И КАК СОЗДАТЬ
Для создания условного форматирования необходимо:

Выделить ячейки для применения условного форматирования
В меню выбрать
- Excel 2003 : Формат (Format) -Условное форматирование (Conditional formatting) ;
- Excel 2007-2010 : вкладка Главная (Home) -Условное форматирование (Conditional formatting)
Выбрать одно из предустановленных правил (в Excel 2003 это значение (Cell Value Is) ) или создать свое (в Excel 2003 это возможно посредством пункта формула (Formula Is) );
Выбрать способ форматирования ячеек: цвет заливки, цвет шрифта, формат отображения, границы и т.д.
Подтвердить нажатием кнопки ОК

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

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

Условное форматирование в Excel 2007-2010:

Главный недостаток предустановленных правил - их нельзя применять к ячейкам на основании значений других ячеек. Они применяются исключительно для тех ячеек, в которых сами значения. Например, нельзя сделать отображение гистограмм в диапазоне А1:А10 , но значения для гистограмм брать из ячеек В1:В10 .

Для Excel 2003 предустановленные правила ограничиваются списком, имеющемся в пункте значение (Cell Value Is) , который в более поздних версиях называется Правила выделения ячеек:

Правила выделения ячеек (Highlight Cells Rules)

В Excel 2003 эти правила содержат условия:
Между, Вне, Равно, Не равно, Больше, Меньше, Больше или равно, Меньше или равно
between, not between, equal to, not equal to, greater than, less than, greater than or equal to, less than or equal to

В Excel 2007-2010: эти правила содержат условия:
Больше, Меньше, Между, Равно, Текст содержит, Дата, Повторяющиеся значения
Greater Than, Less Than, Between, Equal To, Text that Contains, A Date Occurring, Duplicate Values
Как видно, по большей части названия пунктов говорят за себя названиями, и не нуждаются в подробных описаниях их функционала. Чуть более подробно можно рассмотреть лишь Дата и Повторяющиеся значения из набора правил версий Excel 2007 и новее.
Дата:

Список содержит несколько значений: Вчера, Сегодня, Завтра, За последние 7 дней, На прошлой неделе, На текущей неделе, На следующей неделе, В прошлом месяце, В этом месяце, В следующем месяце
Yesterday, Today, Tomorrow, In the last 7 days, Last week, This week, Next week, Last month, This month, Next month
Соответственно, при выборе необходимого условия даты в указанном диапазоне, соответствующие условию, будут отформатированы.

Повторяющиеся значения:

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

Правила отбора первых и последних значений (Top/Bottom Rules)

Отсутствует в Excel 2003

Содержит условия:
Первые 10 элементов, Первые 10%, Последние 10 элементов, Последние 10%, Выше среднего, Ниже среднего
Top 10 Items, Top 10%, Bottom 10 Items, Bottom 10%, Above Average, Below Average

Гистограммы (Data Bars)

Отсутствует в Excel 2003

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

Что важно знать при применении данных условий. Они работают только при применении к диапазону ячеек с числовыми данными. 100%-му заполнению шкалы соответствует максимальное значение среди выделенных ячеек, 1%-му заполнению - ячейка с минимальным значением. Т.е. ячейка с максимальным значением будет заполнена полностью, ячейка с минимальным - едва будет видна полоска бара, а остальные ячейки будут заполнены относительно процентного отношения данных в самой ячейке к показателям минимального и максимального значения всех ячеек. Например, если выделено 4 ячейки с числами: 1, 25, 50 и 100, то ячейка с 1 будет едва заполнена, ячейка с 25 - заполнена на четверть, ячейка с 50 - на половину, а ячейка с 100 - полностью.

Цветовые шкалы (Color Scales)

Отсутствует в Excel 2003

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

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

В этом случае помимо насыщенности цвета значения будут различаться еще и самим цветом. Среди наборов шкал есть разбивка на два и на три цвета. При этом цвет назначается по принципу деления на кол-во цветов: первые 33% одним цветом, от 34% до 66% другим цветом, а оставшиеся - третьим. Если цвета два - то делится по 50%.

Наборы значков (Icon Sets)

Отсутствует в Excel 2003

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

Для отражения разницы между значениями так же очень хорошо подходят значки в виде мини-гистограмм:

Выделить ячейки для применения условного форматирования
В меню выбрать
- Excel 2003 : Формат (Format) -Условное форматирование (Conditional formatting) - формула;
- Excel 2007-2010 : вкладка Главная (Home) -Условное форматирование (Conditional formatting) -Создать правило (New rule) -Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)
Вписать в поле необходимую формулу (Сборник формул для условного форматирования)
Выбрать способ форматирования ячеек: цвет заливки, цвет шрифта, формат отображения, границы и т.д.
ОК

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то в пункте 1 выделяем не столбец, а всю таблицу, а ссылку на столбец с критерием закрепляем:
= $A1 =МАКС( $A$1:$A$20 )
при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.
Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: = B1 < A1 , то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше( B1<A1 , B3<A3 ). При этом если ячейки столбца D меньше ячеек столбца C в той же строке - они тоже будут выделены( D1<C1 , D5<C5 ).

ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ
Если к одной или нескольким ячейкам на листе применено условное форматирование, можно быстро найти их для копирования, изменения или удаления условного формата.

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

Выделить любую ячейку на листе;
Нажать F5- Выделить (Special) ; или же перейти на вкладку Главная (Home) - группа Редактирование (Editing) - Найти и выделить (Find & Select) - Выделение группы ячеек (Go To Special) ;
В появившемся окне выбрать Условные форматы (Conditional formats) ;
Нажать ОК.

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

Выделить ячейку с необходимым условным форматированием;
Нажать F5- Выделить (Special) ; или же перейти на вкладку Главная (Home) - группа Редактирование (Editing) - Найти и выделить (Find & Select) - Выделение группы ячеек (Go To Special) ;
В появившемся окне выбрать Условные форматы (Conditional formats) ;
Выбрать пункт этих же (Same) в группе Проверка данных (Data validation) ;
Нажать ОК.

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

Выделить диапазон ячеек, из которых требуется удалить условное форматирование;
Формат (Format) -Условное форматирование (Conditional formatting) ;
Изменить условие и нажать ОК.

Excel 2007-2010:

Выделить диапазон ячеек, таблицу или сводную таблицу, условное форматирование которых требуется изменить;
Вкладка Главная (Home) - группа Стили- Условное форматирование (Conditional formatting) - Управление правилами (Manage Rules) ;
Выбрать необходимое правило, условное форматирование которого необходимо изменить
Нажать кнопку Изменить правило (Edit Rule)

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

Удаление условного форматирования со всего листа

Вкладка Главная (Home) (Home) - группа Стили (Styles) - Условное форматирование (Conditional formatting) - Удалить правила (Clear Rules) - Удалить правила со всего листа (Clear Rules from Entire Sheet) .

Удаление условного форматирования из диапазона ячеек, таблицы или сводной таблицы
Excel 2003:

Выделить диапазон ячеек, из которых требуется удалить условное форматирование;
Формат (Format) -Условное форматирование (Conditional formatting) - кнопка Удалить (Delete) ;
Отметить галочками условное форматирование, которое необходимо удалить и нажать ОК.

Excel 2007-2010:

Выделить диапазон ячеек, таблицу или сводную таблицу, из которых требуется удалить условное форматирование;
Вкладка Главная (Home) - группа Стили- Условное форматирование (Conditional formatting) - Удалить правила (Clear Rules) ;
Выбрать элемент, условное форматирование из которого необходимо удалить: Удалить правила из выделенных ячеек (Clear Rules from Selected Cells) , Удалить правила из этой таблицы (Clear Rules from This Table) или Удалить правила из этой сводной таблицы (Clear Rules from This PivotTable) .

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

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

А если при движении активной ячейки по листу будет подсвечиваться текущая строка и столбец? Своего рода координатное выделение примерно такого вида:

Поудобнее, чем линейка, правда?

Есть несколько способов разной сложности, чтобы реализовать такое. Каждый способ - со своими плюсами и минусами. Давайте разберем их детально.

Способ 1. Очевидный. Макрос, выделяющий текущую строку и столбец

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

Откройте лист со таблицей, в которой хотите получить такое координатное выделение. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этих трех макросов:

Измените адрес рабочего диапазона на свой - именно в пределах этого диапазона и будет работать наше выделение. Затем закройте редактор Visual Basic и вернитесь в Excel.

Нажмите сочетание клавиш ALT+F8, чтобы открыть окно со списком доступных макросов. Макрос Selection_On, как нетрудно догадаться, включает координатное выделение на текущем листе, а макрос Selection_Off - выключает его. В этом же окне, нажав кнопку Параметры (Options) можно назначить этим макросам сочетания клавиш для удобного запуска.

Плюсы этого способа:

относительная простота реализации
выделение - операция безобидная и никак не изменяет содержимое или форматирование ячеек листа, все остается как есть

Минусы этого способа:

такое выделение некорректно работает в том случае, если на листе есть объединенные ячейки - выделяются сразу все строки и столбцы, входящие в объединение
если случайно нажать клавишу Delete, то очистится не только активная ячейка, а вся выделенная область, т.е. удалятся данные из всей строки и столбца

Способ 2. Оригинальный. Функция ЯЧЕЙКА + Условное форматирование

Этот способ хотя и имеет пару недостатков, мне представляется весьма изящным. Реализовать что-либо, используя только встроенные средства Excel, минимально влезая в программирование на VBA - высший пилотаж ;)

Способ основан на использовании функции ЯЧЕЙКА (CELL), которая может выдавать массу различной информации по заданной ячейке - высоту, ширину, номер строки-столбца, числовой формат и т.д.. Эта функция имеет два аргумента:

кодовое слово для параметра, например "столбец" или "строка"
адрес ячейки, для которой мы хотим определить значение этого параметра

Хитрость в том, что второй аргумент не является обязательным. Если он не указан, то берется текущая активная ячейка.

Вторая составляющая этого способа - условное форматирование. Эта крайне полезная функция Excel позволяет автоматически форматировать ячейки, если они удовлетворяют заданным условиям. Если соединить эти две идеи в одно целое, то получим следующий алгоритм реализации нашего координатного выделения через условное форматирование:

Выделяем нашу таблицу, т.е. те ячейки, в которых в будущем должно отображаться координатное выделение.
В Excel 2003 и более старших версиях открываем меню Формат - Условное форматирование - Формула (Format - Conditional Formatting - Formula) . В Excel 2007 и новее - жмем на вкладке Главная (Home) кнопку Условное форматирование - Создать правило (Conditional Formatting - Create Rule) и выбираем тип правила Использовать формулу для определения форматируемых ячеек (Use formula)
Вводим формулу для нашего координатного выделения:

Все почти готово, но остался один нюанс. Дело в том, что Excel не считает изменение выделения изменением данных на листе. И, как следствие, не запускает пересчет формул и перекраску условного форматирования только при изменении положения активной ячейки. Поэтому добавим в модуль листа простой макрос, который будет это делать. Щелкните правой кнопкой мыши по ярлычку листа и выберите в контекстном меню команду Исходный текст (Source Code). Должно открыться окно редактора Visual Basic. Скопируйте в него этот текст этого простого макроса:

Теперь при изменении выделения будет запускаться процесс пересчета формулы с функцией ЯЧЕЙКА в условном форматировании и заливаться текущая строка и столбец.

Плюсы этого способа:

Условное форматирование не нарушает пользовательское форматирование таблицы
Этот вариант выделения корректно работает с объединенными ячейками.
Нет риска удалить целую строку и столбец с данными при случайном нажатии Delete.
Макросы используются минимально

Минусы этого способа:

Формулу для условного форматирования надо вводить вручную.
Нет быстрого способа включить-выключить такое форматирование - оно включено всегда, пока не будет удалено правило.

Способ 3. Оптимальный. Условное форматирование + макросы

Золотая середина. Используем механизм отслеживания выделения на листе при помощи макросов из способа-1 и добавим к нему безопасное выделение цветом с помощью условного форматирования из способа-2.

Не забудьте изменить адрес рабочего диапазона на адрес своей таблицы. Закройте редактор Visual Basic и вернитесь в Excel. Чтобы использовать добавленные макросы, нажмите сочетание клавиш ALT+F8 и действуйте аналогично способу 1.

Способ 4. Красивый. Надстройка FollowCellPointer

Excel MVP Jan Karel Pieterse родом из Нидерландов раздает у себя на сайте бесплатную надстройку FollowCellPointer(36Кб), которая решает ту же задачу, отрисовывая с помощью макросов графические линии-стрелки для подсветки текущей строки и столбца:

Красивое решение. Не без глюков местами, но попробовать точно стоит. Качаем архив, распаковываем на диск и устанавливаем надстройку:

Условное форматирование позволяет автоматически изменять форматирование ячеек в зависимости от их содержимого.

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

Условное форматирование можно использовать определения ячеек, которые содержат недопустимые значения, или ячеек определенного типа. Затем согласно примененному форматированию можно определить, какие ячейки содержат значения определенного типа. Например, к искомым ячейкам можно применить ярко-красный фон.

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

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

Выделите диапазон или ячейку.
Выберите команду ФорматУсловное форматирование. На экране появится одноименное диалоговое окно, показанное на рис. 3.3.

Рис. 3.3. Диалоговое окно Условное форматирование

Теперь ячейка или диапазон будут отформатированы по заданным условиям. Такое форматирование является динамическим. Это значит, что, если содержимое ячейки изменится, Excel оценит новое значение и в соответствии с ним изменит форматирование ячейки.

Параметры форматирования

После нажатия на кнопке Формат диалогового окна Условное форматирование
откроется окно Формат ячеек (рис.3.4).

Рис. 3.4. Диалоговое окно Формат ячеек, используемое для условного форматирования

Оно несколько отличается от обычного окна Формат — в нем отсутствуют вкладки Число, Выравнивание и Защита, но появилась новая Очистить.

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

Определение условия

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

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

Простые условия

После выбора элемента значение в следующем раскрывающемся списке можно определить перечисленные ниже типы условий.

между. Указывается два значения.
вне. Указывается два значения.
равно. Указывается одно значение.
не равно. Указывается одно значение.
больше. Указывается одно значение.
меньше. Указывается одно значение.
больше или равно. Указывается одно значение.
меньше или равно. Указывается одно значение.

Ввести значения можно как вручную, так и с помощью ссылки на ячейку.

Условия с использованием формул

При выборе элемента Формула в поле, расположенном справа, нужно задать формулу. Это можно сделать, просто определив ячейку с формулой на рабочем листе, а затем указав ссылку на нее, или же введя ее непосредственно в диалоговом окне Условное форматирование. Как всегда, перед началом формулы должен стоять знак равенства (=).

Рекомендуется вводить формулу непосредственно в диалоговом окне Условное форматирование.

Формула должна возвращать только логические значения ИСТИНА или ЛОЖЬ. Если результат соответствует значению ИСТИНА, условие выполняется и к ячейке или диапазону применяется условное форматирование. Иначе (если результат принимает значение ЛОЖЬ) условное форматирование не применяется.

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

Например, если нужно задать условное форматирование, которое будет менять цвет заливки пустых ячеек в диапазоне С2:С10, выполните ряд действий.

Выделите диапазон С2:С10, начиная с ячейки С2, т.е. чтобы ячейка С2 была активной.
Выберите команду ФорматУсловное форматирование. Откроется диалоговое окно Условное форматирование.
Из левого раскрывающегося списка выберите формула.
В поле справа от выпадающего списка введите формулу =С2="" .
Щелкните на кнопке Формат, чтобы открыть диалоговое окно Формат ячеек.
В этом диалоговом окне определите цвет заливки и щелкните на кнопке ОК.
В диалоговом окне Условное форматирование щелкните на кнопке ОК, чтобы закрыть его.
Обратите внимание на то, что введенная формула содержит ссылку на верхнюю левую ячейку выделенного диапазона. Чтобы убедиться в том, что ссылка является относительной, нужно выделить одну из ячеек диапазона (например, С5) и посмотреть на формулу условного форматирования для этой ячейки. Формула будет иметь следующий вид: =С5=""

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

Например, в диапазоне А2:В10 необходимо применить условное форматирование только к тем ячейкам, значения которых больше значения ячейки С1. Для этого следует ввести формулу =А2>$С$1. В этом случае ссылка на ячейку С1 будет абсолютной и одинаковой во всех ячейках выделенного диапазона. Другими словами, формула условного форматирования, например, для ячейки А3 будет выглядеть следующим образом: =А3>$С$1. Следовательно, для всех ячеек выделенного диапазона относительные ссылки будут изменяться в соответствии с позицией ячейки в диапазоне, а абсолютные всегда будут ссылаться на одну и ту же ячейку.

Множественные условия

Щелкнув на кнопке А также диалогового окна Условное форматирование, можно определить дополнительное условие (трех условий).

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

Значение ячейки меньше 0
Значение ячейки равно 0
Значение ячейки больше 0

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

Если ни одно из условий не примет значение ИСТИНА, форматирование ячейки не
изменится. Если же будет выполняться более одного условия, Excel применит форматирование в соответствии с первым выполненным условием. Например, такая ситуация может произойти, если задать следующие условия:

Значение ячейки лежит между 1 и 12
Значение ячейки меньше 6

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

Копирование ячеек, содержащих условное форматирование

Информация об условном форматировании сохраняется в ячейке почти так же, как и стандартное форматирование. Это значит, что при копировании ячейки копируется и
примененное к ней условное форматирование (если оно есть).

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

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

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

Удаление условного форматирования

При удалении содержимого ячейки с помощью нажатия клавиши <Delete> условное форматирование, которое ранее было применено к этой ячейке, не удаляется. Чтобы удалить условное форматирование как и прочее форматирование, выберите команду Правка Очистить Форматы.

Для удаления содержимого ячейки (включая форматирование) выберите команду Правка Очистить Все.

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

Выделите ячейки. Затем выберите команду ФорматУсловноеформатирование; откроется одноименное диалоговое окно.
В диалоговом окне Условное форматирование нажмите кнопку Удалить. Откроется диалоговое окно Удаление условия форматирования (рис. 3.5).

Рис. 3.5. Диалоговое окно Удаление условия форматирования

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

Просто посмотрев на ячейки, невозможно сказать, какие из них содержат условное форматирование, а какие — нет. Но выделить ячейки с условным форматирование можно с помощью диалогового окна Переход. Для этого выполните ряд действий.

Выберите команду ПравкаПерейти (или нажмите F5 или <Ctrl>+<G> ). Откроется диалоговое окно Переход.
В диалоговом окне Переход щелкните на кнопке Выделить, чтобы открыть диалоговое окно Выделение группы ячеек, которое показано на рис. 3.6.

Рис. 3.6. Диалоговое окно Выделение группы ячеек

Использование ссылок на другие рабочие листы

Чтобы сослаться на ячейку другого рабочего листа, можно использовать два варианта:

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

Например, если необходимо, чтобы формула условного форматирования ссылалась на ячейку А1 рабочего листа ЛистЗ, нужно вставить в ячейку активного листа следующую формулу.
=ЛистЗ!А1
После этого можно использовать ссылку на данную ячейку в формуле условного форматирования.

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

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

Пример1. Определение нечисловых данных

Представленное на рис. 3.7. условное форматирование будет применять форматирование к ячейке А1 только в том случае, если в ней содержится текст. Чтобы применить такую формулу к диапазону, нужно сначала выделить необходимый диапазон. Аргументом функции ЕТЕКСТ должна быть активная ячейка (обычно это верхняя левая ячейка диапазона).

Рис.3.7. окно условно форматирования для примера 1

Пример 2. Выделение ячеек, значения которых выше среднего

В этом примере условное форматирование применяется к диапазону A1:С4, только к тем ячейкам, значения которых выше среднего (рис. 3.8).

Рис.3.8. Окно условно форматирования для примера 2

Стоит обратить внимание на то, что ссылка на ячейку А1 является относительной, в то время как аргументом функции СРЗНАЧ должна быть абсолютная ссылка. На рис. 3,9 представлен результат применения этого условного форматирования

В данной статье собран список формул, которые можно использовать в условном форматировании ячеек, заданным при помощи формулы:

Excel 2003 : Формат (Format) -Условное форматирование (Conditional formatting) - формула;
Excel 2007-2010 : вкладка Главная (Home) -Условное форматирование (Conditional formatting) -Создать правило (New rule) -Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)

Подробнее об условном форматировании можно прочитать в статье: Основные понятия условного форматирования и как его создать

Все условия приведены для диапазона A1:A20 . Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20 (столбцов может быть больше), начиная с ячейки A1 , после чего назначить условие.
Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона - A4 .

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
= $A1 =МАКС( $A$1:$A$20 )
при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.

Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: = B1 < A1 , то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше( B1<A1 , B3<A3 ). При этом если ячейки столбца D меньше ячеек столбца C в той же строке - они тоже будут выделены( D1<C1 , D5<C5 ).

Читайте также: