Использование условного форматирования для выделения сведений в Excel

Применение условного форматирования в отчете сводной таблицы

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

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

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

• Область действия условного формата для полей в области "Значения" может быть основана на иерархии данных и определяется всеми видимыми дочерними элементами (нижестоящим уровнем в иерархии) родительского объекта (вышестоящим уровнем в иерархии) в строках для одного или нескольких столбцов или в столбцах для одной или нескольких строк.

  Примечание

  В иерархии данных дочерние элементы не наследуют условное форматирование от родительских, а родительские — от дочерних.

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

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

Использование быстрого анализа для применения условного форматирования в Excel

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

1. Выделите данные, к которым необходимо применить условное форматирование. В правом нижнем углу выделенной области появится кнопка Экспресс-анализ.
   

2. Нажмите анализ или нажмите клавиши CTRL+Q.

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

   Примечание

   • Варианты форматирования, которые отображаются на вкладке Форматирование, зависят от выбранных данных. Если выделенный фрагмент содержит только текст, будут доступны параметры Текст содержит, Повторяющиеся, Уникальные значения, Равно и Очистить формат. Если выделенный диапазон содержит только числа или числа и текст, доступны параметры Гистограмма, Цветовая шкала, Набор значков, Больше, Первые 10 % и Очистить формат.
   • Динамический просмотр будет работать только для тех вариантов, которые поддерживаются для данных. Например, если выделенный диапазон не содержат повторяющихся значений и выбрать Повторяющиеся, динамический просмотр не будут работать.

4. Если откроется диалоговое окно Текст, который содержит, задайте нужные параметры форматирования и нажмите кнопку ОК.

Скачивание образца книги

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

Скачать: примеры условного форматирования в Excel

Форматирование ячеек с помощью двухцветной шкалы

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

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Цветовые шкалы.
   
3. Выберите двухцветную шкалу.
   Просмотрите значки цветовой шкалы и определите значок, соответствующий двухцветной шкале. Верхний цвет соответствует высоким значениям, нижний цвет — низким значениям.

Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью кнопки Параметры форматирования рядом с полем сводной таблицы, к которому применено условное форматирование.

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
   

3. Выполните одно из указанных ниже действий.

   • Чтобы добавить совершенно новый вариант условного форматирования, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.

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

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

     1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

   • к выделенным ячейкам, чтобы выбрать ячейки по выделению;
   • Все ячейки для метки значения. Щелкните Все ячейки со значениями <метки> значения.
   • Все ячейки для метки значения, за исключением промежуточных итогов и общего итога: щелкните Все ячейки, отображающие <значения метки> значения значения для <метки> строки.

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

6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Двухцветная шкала.

7. В полях Тип параметров Минимальное значение и Максимальное значение выберите их типы.

   • Форматирование минимального и максимального значений. Выберите минимальное значение и максимальное значение.
     В этом случае не вводится минимальное и максимальное значение.

   • Форматирование числа, даты или времени: Выберите Число , а затем введите минимальное и максимальное значение.

   • Отформатируйте процент в процентах: Введите минимальное значение и максимальное значение.
     Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
     Рекомендуется использовать процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений является пропорциональным.

   • Форматирование процентиля: Выберите Процентиль , а затем введите минимальное и максимальное значение. Допустимыми являются значения процентилей от 0 (нуля) до 100.
     Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) с одним оттенком, а группу низких значений (например, нижнюю 20ю процентиль) — с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.

   • Форматирование результата формулы.  Выберите элемент Формула, а затем введите минимальное и максимальное значения.

     • Формула должна возвращать число, дату или время.

     • Начинайте ввод формулы со знака равенства (=).

     • Недопустимая формула не позволит применить форматирование.

     • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

       Примечание

       • Убедитесь, что минимальное значение меньше, чем максимальное.
       • Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

8. Чтобы выбрать параметры Минимальное значение и Максимальное значение цветовой шкалы, щелкните элемент Цвет для каждого значения, а затем выберите цвет.
   Если необходимо выбрать дополнительные цвета или создать пользовательский цвет, нажмите кнопку Другие цвета. Выбранная цветовая шкала появится в окне предварительного просмотра.

Форматирование ячеек с помощью трехцветной шкалы в Excel

Цветовые шкалы — это визуальные элементы, которые помогают понять распределение и разброс данных. Трехцветная шкала помогает сравнить диапазон ячеек путем использования градации трех цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, для красно-желто-зеленой шкалы можно указать, что ячейки с высокими значениями будут зелеными, ячейки со средними значениями — желтыми, а ячейки с низкими значениями — красными.

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Цветовые шкалы.
   
3. Выберите трехцветную шкалу. Верхний цвет соответствует высоким значениям, центральный цвет — средним значениям, а нижний цвет — низким значениям.
   Просмотрите значки цветовой шкалы и определите значок, соответствующий трехцветной шкале.

Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью кнопки Параметры форматирования рядом с полем сводной таблицы, к которому применено условное форматирование.

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
   

3. Выполните одно из указанных ниже действий.

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

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

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

     1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

   • чтобы выбрать поля по выделению, выберите только эти ячейки;
   • Соответствующее поле: Щелкните Все <ячейки поля> значений с одинаковыми полями.
   • Поле значения: Щелкните Все <ячейки поля значений>.

5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.

6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Трехцветная шкала.

7. Выберите тип параметров Минимальное значение, Среднее значение и Максимальное значение. Выполните одно из указанных ниже действий.

   • Форматирование минимального и максимального значений. Выберите элемент Среднее значение.
     В этом случае вы не вводите наименьшие и наивысшие значения.

   • Форматирование числового значения, значения даты или времени. Выберите элемент Число, а затем введите минимальное, среднее и максимальное значения.

   • Отформатируйте процент: Выберите Процент , а затем введите значение Минимум, Средняя точка и Максимум. Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
     Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.

   • Форматирование процентиля. Выберите элемент Процентиль, а затем введите Минимальное значение, Среднее значение и Максимальное значение.
     Допустимыми являются значения процентилей от 0 (нуля) до 100.
     Используйте процентиль, если необходимо визуализировать группу высоких значений (например, верхнюю 20юпроцентиль) с одним оттенком, а группу низких значений (например, нижнюю 20юпроцентиль) — с другим, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.

   • Форматирование результата формулы. Выберите элемент Формула, а затем введите минимальное, среднее и максимальное значения.
     Формула должна возвращать число, дату или время. Начните формулу со знаком равенства (=). Недопустимая формула не позволит применить форматирование. Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

     Примечание

     • Можно задать минимальное, среднее и максимальное значения для диапазона ячеек. Убедитесь, что минимальное значение меньше среднего, а оно, в свою очередь, меньше максимального.
     • Для параметров Минимальное значение, Среднее значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения, процентиль для среднего значения и процентный тип для максимального значения.
     • Во многих случаях среднее значение по умолчанию, равное 50 процентам, оптимально, но его можно изменить с учетом конкретных требований.

8. Для выбора цветовой шкалы Минимальное значение, Среднее значение и Максимальное значение щелкните элемент Цвет для каждого из них, а затем выберите цвет.

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

Форматирование ячеек с помощью гистограмм

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

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

Быстрое форматирование

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

Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к.

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
   

3. Выполните одно из указанных ниже действий.

   • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
   • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
   • Для изменения условного форматирования выполните указанные ниже действия.
   • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

   • чтобы выбрать поля по выделению, выберите только эти ячейки;
   • Соответствующее поле: Щелкните Все <ячейки поля> значений с одинаковыми полями.
   • Поле значения: Щелкните Все <ячейки поля значений>.

5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.

6. В группе Измените описание правила в поле со списком Стиль формата выберите пункт Гистограмма.

7. Выберите минимальное и максимальное тип. Выполните одно из следующих действий:

   • Форматирование минимального и максимального значений. Выберите минимальное значение и максимальное значение.
     В этом случае не вводите Минимальное значение и Максимальное значение.

   • Форматирование числа, даты или времени: Выберите Число , а затем введите минимальное и максимальное значение.

   • Форматирование процентного значения. Выберите элемент Процент, а затем введите минимальное и максимальное значения.
     Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
     Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.

   • Форматирование процентиля. Выберите элемент Процентиль, а затем введите значения параметров Минимальное значение и Максимальное значение.
     Допустимыми являются значения процентилей от 0 (нуля) до 100.
     Используйте процентили, если необходимо визуализировать группу высоких значений (например, верхнюю 20ю процентиль) в одной пропорции гистограммы и группу низких значений (например, нижнюю 20ю процентиль) — в другой, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.

   • Форматирование результата формулы Выберите Формула, а затем введите значение минимума и максимума.

     • Формула должна возвращать число, дату или время.
     • Начните формулу со знаком равенства (=).
     • Недопустимая формула не позволит применить форматирование.
     • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

   Примечание

   • Убедитесь, что минимальное значение меньше, чем максимальное.
   • Для параметров Минимальное значение и Максимальное значение можно выбрать разные типы. Например, можно выбрать числовой тип для минимального значения и процентный тип для максимального значения.

8. Для выбора цветовых шкал Минимальное значение и Максимальное значение выберите пункт Цвет столбца.
   Если необходимо выбрать дополнительные цвета или создать пользовательский цвет, нажмите кнопку Другие цвета. Выбранный цвет столбца появится в окне предварительного просмотра.

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

10. Чтобы применить к столбцам гистограммы сплошную границу, выберите в списке Граница вариант Сплошная граница, а затем выберите цвет границы.

11. Чтобы сделать столбцы сплошными или градиентными, выберите в списке Заливка вариант Сплошная заливка или Градиентная заливка.

12. Чтобы задать формат столбцов для отрицательных значений, щелкните элемент Отрицательные значения и ось, а затем в диалоговом окне Настройка отрицательных значений и оси настройте параметры заливки и цвета границы для столбцов, соответствующих отрицательным значениям. Для оси можно настроить расположение и цвет. Выбрав параметры, нажмите кнопку ОК.

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

Форматирование ячеек с помощью набора значков

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

В показанном здесь примере в условном форматировании используются несколько наборов значков.

Вы можете настроить показ значков только для ячеек, соответствующих определенным условиям (например, показ значка предупреждения для ячеек, значения которых находятся ниже критического уровня, и отсутствие значка для ячеек, превышающих этот уровень). Чтобы сделать это, при настройке условий скройте ячейки, выбрав вариант Нет значка ячейки в раскрывающемся списке напротив нужного значка. Кроме того, можно создавать собственные наборы значков, например зеленую "галочку", желтый "светофор" и красный "флажок".

Быстрое форматирование

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

Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к.

Расширенное форматирование

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

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
   

3. Выполните одно из указанных ниже действий.

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

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

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

     1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

   • чтобы выбрать поля по выделению, выберите только эти ячейки;
   • Соответствующее поле: Щелкните Все <ячейки поля> значений с одинаковыми полями.
   • Поле значения: Щелкните Все <ячейки поля значений>.

5. В группе Выберите тип правила выберите пункт Форматировать все ячейки на основании их значений.

6. В разделе Измените описание правила в списке Формат стиля выберите пункт Набор значков.

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

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

   3. Выполните одно из указанных ниже действий.

      • Форматирование числового значения, значения даты или времени. Выберите элемент Число.

      • Форматирование процентного значения. Выберите элемент Процент.
        Допустимыми являются значения от 0 (нуля) до 100. Не вводите знак процента.
        Используйте процентные значения, если необходимо визуализировать все значения пропорционально, поскольку распределение значений в этом случае является пропорциональным.

      • Форматирование процентиля: Выберите Процентиль. Допустимыми являются значения процентилей от 0 (нуля) до 100.
        Используйте процентили, если необходимо визуализировать группу максимальных значений (например, верхнюю 20ю процентиль) с помощью одного значка, а группу минимальных значений (например, нижнюю 20ю процентиль) — с помощью другого, поскольку они соответствуют экстремальным значениям, которые могут сместить визуализацию данных.

      • Форматирование результата формулы. Выберите элемент Формула, а затем введите формулы в каждое поле Значение.

        • Формула должна возвращать число, дату или время.
        • Начинайте ввод формулы со знака равенства (=).
        • Недопустимая формула не позволит применить форматирование.
        • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

   4. Чтобы первый значок соответствовал меньшим значениям, а последний — большим, выберите параметр Обратный порядок значков.

   5. Для отображения только значка, но не значения в ячейке, выберите параметр Показать только значок.

      Примечание

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

Форматирование ячеек, содержащих текст, числовые значения, дату или время

Чтобы удобнее находить определенные ячейки, можно отформатировать их с помощью оператора сравнения. Например, в инвентарном перечне, отсортированном по категориям, можно выделить желтым цветом товары, которых осталось менее 10 штук. В сводном листе розничного магазина можно выделить все магазины с доходом более 10 %, с объемом продаж менее 100 000 долларов и с регионом "Юго-восток".

В приведенных здесь примерах демонстрируются встроенные критерии условного форматирования, такие как "больше чем" и "%_лучших". Города с численностью населения более 2 000 000 выделяются зеленым цветом фона, а 30 % лучших (самых высоких) показателей среднемесячной максимальной температуры — оранжевым.

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила выделения ячеек.
   
3. Выберите нужную команду, например Между, Текст содержит или Дата.
4. Введите значения, которые необходимо использовать, и выберите формат.

Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к.

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
   

3. Выполните одно из указанных ниже действий.

   • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
   • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
   • Для изменения условного форматирования выполните указанные ниже действия.
   • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на этом или других листах и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

   • чтобы выбрать поля по выделению, выберите только эти ячейки;
   • Соответствующее поле: Щелкните Все <ячейки поля> значений с одинаковыми полями.
   • Поле значения: Щелкните Все <ячейки поля значений>.

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

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

   • Форматирование по числу, дате или времени.  Выберите элемент Значение ячейки и задайте оператор сравнения, а затем введите число, дату или время.
     Например, можно выбрать команду Между, а затем ввести числа 100 и 200 или выбрать команду Равно и ввести дату 01.01.2009.
     Вы также можете ввести формулу, которая возвращает число, дату или время.

     • Начинайте ввод формулы со знака равенства (=).
     • Недопустимая формула не позволит применить форматирование.
     • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

   • Форматирование по тексту. Выберите элемент Текст, задайте оператор сравнения и введите текст.
     Например, можно выбрать команду Содержит и ввести значение Серебро или выбрать команду Начиная с и ввести значение Три.
     Кавычки включаются в строку поиска, допускается использование подстановочных знаков. Максимальная длина строки — 255 знаков.
     Можно также ввести формулу, которая возвращает текст.

     • Начинайте ввод формулы со знака равенства (=).
     • Недопустимая формула не позволит применить форматирование.
     • Проверьте формулу, чтобы убедиться, что она не возвращает значение ошибки.

   • Форматирование по дате. Выберите элемент Даты, а затем выберите сравнение данных.
     Например, можно выбрать значение Вчера или На следующей неделе.

   • Форматирование ячеек с пустыми значениями или без них. Выберите элемент Пустые или Непустые.
     Пустое значение представляет собой ячейку, не содержащую данных, и отличается от ячейки, содержащей один или несколько пробелов (которые считаются текстом).

   • Форматируйте ячейки с ошибками или без ошибок: Выберите Ошибки или Нет ошибок.
     Значения ошибок включают #####, #ЗНАЧЕНИЕ!, #ДЕЛ/0!, #ИМЯ?, #Н/Д, #ССЫЛКА!, #ЧИСЛО!, и #ПУСТО!.

7. Чтобы задать формат, нажмите кнопку Формат. Отобразится диалоговое окно Формат ячеек.

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

Форматирование только первых или последних значений

Можно найти максимальное и минимальное значения в диапазоне ячеек на основе указанного порогового значения. Например, можно найти 5 самых популярных продуктов в региональном отчете, 15 % наименее популярных продуктов в клиентском опросе или 25 самых высоких зарплат в отчете по персоналу отдела.

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила отбора первых и последних значений.
   
3. Выберите нужную команду, например Первые 10 элементов или Последние 10 %.
4. Введите значения, которые необходимо использовать, и выберите формат.

Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к.

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
   

3. Выполните одно из указанных ниже действий.

   • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
   • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
   • Для изменения условного форматирования выполните указанные ниже действия.
   • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

   • чтобы выбрать поля по выделению, выберите только эти ячейки;
   • Соответствующее поле: Щелкните Все <ячейки поля> значений с одинаковыми полями.
   • Поле значения: Щелкните Все <ячейки поля значений>.

5. В группе Выберите тип правила выберите пункт Форматировать только первые или последние значения.

6. В группе Измените описание правила в поле со списком Форматировать значения, которые относятся к выберите пункт Сверху или Снизу.

7. Выполните одно из указанных ниже действий.

   • Чтобы указать верхнее или нижнее пороговое значение, введите число, а затем снимите флажок % от выделенного диапазона. Допустимыми являются значения от 1 до 1000.
   • Чтобы указать верхнее или нижнее процентное значение, введите число, а затем установите флажок % от выделенного диапазона. Допустимыми являются значения от 1 до 100.

8. Кроме того, можно настроить применение формата к полям в области "Значения" отчета сводной таблицы, область которых определяется соответствующим полем.
   По умолчанию условное форматирование основано на всех видимых значениях. Однако при определении области по соответствующему полю можно применить условное форматирование не ко всем видимым значениям, а к каждому из следующих сочетаний:

   • столбец и поле его родительской строки (вариант каждой группы столбцов);
   • строка и поле ее родительского столбца (вариант каждой группы строк).

9. Чтобы задать формат, нажмите кнопку Формат. Отобразится диалоговое окно Формат ячеек.

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

Форматирование только значений, находящихся выше или ниже среднего

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

Быстрое форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила отбора первых и последних значений.
   
3. Выберите нужную команду, например Выше среднего или Ниже среднего.
4. Введите значения, которые необходимо использовать, и выберите формат.

Вы можете изменить способ определения области для полей из области "Значения" в отчете сводной таблицы с помощью переключателя Применить правило форматирования к.

Расширенное форматирование

1. Выделите одну или несколько ячеек в диапазоне, таблице или отчете сводной таблицы.

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
   

3. Выполните одно из указанных ниже действий.

   • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
   • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
   • Для изменения условного форматирования выполните указанные ниже действия.
   • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

4. В разделе Применить правило выберите один из следующих вариантов, чтобы изменить выбор полей в области значений отчета сводной таблицы:

   • чтобы выбрать поля по выделению, выберите только эти ячейки;
   • Соответствующее поле: Щелкните Все <ячейки поля> значений с одинаковыми полями.
   • Поле значения: Щелкните Все <ячейки поля значений>.

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

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

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

7. Кроме того, можно настроить применение формата к полям в области "Значения" отчета сводной таблицы, область которых определяется соответствующим полем.
   По умолчанию условное форматирование основано на всех видимых значениях. Однако при определении области по соответствующему полю можно применить условное форматирование не ко всем видимым значениям, а к каждому из следующих сочетаний:

   • столбец и поле его родительской строки (вариант каждой группы столбцов);
   • строка и поле ее родительского столбца (вариант каждой группы строк).

8. Нажмите кнопку Формат для отображения диалогового окна Формат ячеек.

9. Выберите формат чисел, шрифта, рамки или заполнения, который необходимо применять, если значение в ячейке соответствует условию, а затем нажмите кнопку ОК.
   Можно выбрать несколько форматов. Выбранные форматы появятся в поле Просмотр.

Форматирование только уникальных или повторяющихся значений

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

Быстрое форматирование

1. Выделите ячейки, к которым необходимо применить условное форматирование.
2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование, а затем — элемент Правила выделения ячеек.
   
3. Выберите вариант повторяющиеся значения.
4. Введите значения, которые необходимо использовать, и выберите формат.

Расширенное форматирование

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

2. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами. Откроется диалоговое окно Диспетчер правил условного форматирования.
   

3. Выполните одно из указанных ниже действий.

   • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
   • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
   • Для изменения условного форматирования выполните указанные ниже действия.
   • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист или таблица.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно. Затем выделите новый диапазон ячеек на листе и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

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

5. В разделе Изменение описания правила в списке Форматировать все выберите уникальное или повторяющееся.

6. Нажмите кнопку Формат для отображения диалогового окна Формат ячеек.

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

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

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

1. Выделите ячейки, которые нужно отформатировать.
2. На вкладке Главная щелкните Новоеправилоусловного форматирования>.
   
3. Создайте правило и задайте для него параметры форматирования, а затем нажмите кнопку ОК.
   Если нужные варианты отсутствуют, можно определить форматируемые ячейки с помощью формулы — инструкции см. в следующем разделе.

Выбор ячеек для форматирования с помощью формулы

Если при создании собственного правила условного форматирования отсутствуют нужные варианты, можно использовать логическую формулу, чтобы задать условия форматирования. Например, можно сравнить значения в выбранных ячейках с результатом, возвращенным функцией, или оценить данные в ячейках за пределами выделенного диапазона, которые могут находиться на другом листе этой же книги. Формула должна возвращать значение Истина или Ложь (1 или 0), но вы можете использовать условную логику для объединения набора соответствующих условных форматов, таких как различные цвета для каждого из небольших наборов текстовых значений (например, названия категорий товаров).

1. На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите пункт Управление правилами.
   
   Откроется диалоговое окно Диспетчер правил условного форматирования.
   

2. Выполните одно из указанных ниже действий.

   • Чтобы добавить условное форматирование, нажмите кнопку Создать правило. Откроется диалоговое окно Создание правила форматирования.
   • Чтобы создать новый вариант условного форматирования на основе одного из перечисленных, выберите нужное правило и щелкните Дублировать правило. Скопированный дубликат правила появится в диалоговом окне. Выберите его и нажмите Изменить правило. Откроется диалоговое окно Изменение правила форматирования.
   • Для изменения условного форматирования выполните указанные ниже действия.
   • 1. Убедитесь, что в поле со списком Показать правила форматирования для выбран соответствующий лист, таблица или отчет сводной таблицы.
     2. При необходимости вы можете изменить диапазон ячеек. Для этого нажмите кнопку Свернуть диалоговое окно в поле Применяется к, чтобы временно скрыть диалоговое окно, а затем выделите новый диапазон ячеек на этом или других листах и нажмите кнопку Развернуть диалоговое окно.
     3. Выберите правило, а затем нажмите кнопку Изменить правило. Откроется диалоговое окно Изменение правила форматирования.

3. В разделе Применить правило выполните одно из следующих действий, чтобы изменить выбор полей в области значений отчета сводной таблицы:

   • Чтобы область по выделенному фрагменту, щелкните Выбранные ячейки.
   • Чтобы область по соответствующему полю: щелкните Все ячейки со значениями <полей> значений.
   • Чтобы область по значению: щелкните Все ячейки с полем <> Значения для <строки>.

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

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

      Формула должна начинаться со знака равенства (=) и возвращать логическое значение ИСТИНА (1) или ЛОЖЬ (0).

   2. Нажмите кнопку Формат для отображения диалогового окна Формат ячеек.

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

      Можно выбрать несколько форматов. Выбранные форматы появятся в окне предварительного просмотра.
      Пример 1. Применение двух условных форматов с условиями, использующими проверку с операторами И и ИЛИ
      В приведенном ниже примере показано использование двух правил условного форматирования. Если не применяется первое правило, то применяется второе.
      Первое правило: покупатель дома выделил до 75 000 долларов в качестве первого взноса и 1500 в месяц в качестве выплат по закладной. Если сумма первого взноса и ежемесячные платежи отвечают требованиям, ячейки B4 и B5 будут отформатированы зеленым цветом.
      Второе правило: если первый взнос или ежемесячный платеж не укладываются в бюджет покупателя, ячейки B4 и B5 будут отформатированы красным цветом. Измените некоторые значения, например годовую процентную ставку, срок кредитования, сумму первого взноса или сумму покупки, чтобы увидеть, что произойдет в ячейках, к которым применено условное форматирование.

      Формула для первого правила (применяется зеленый цвет)	=AND(IF($B$4<=75000,1),IF(ABS($B$5)<=1500,1))
      Формула для второго правила (применяется красный цвет)	=OR(IF($B$4>=75000,1),IF(ABS($B$5)>=1500,1))

      
      Пример 2. Заливка каждой второй строки с помощью функций ОСТАТ и СТРОКА 
      Применение условного форматирования ко всем ячейкам листа выполняет заливку синим цветом каждой второй строки в диапазоне. Функция ОСТАТ возвращает остаток от деления числа (первый аргумент) на делитель (второй аргумент). Функция СТРОКА возвращает номер текущей строки. При делении номера текущей строки на 2 в остатке всегда будет получаться 0 для четных чисел и 1 для нечетных чисел. Поскольку 0 — это ЛОЖЬ, а 1 — ИСТИНА, форматирование будет применяться к каждой нечетной строке. Правило использует такую формулу: =MOD(ROW(),2)=1.
      

      Примечание

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

В следующем видео показаны основы использования формул с условным форматированием.

Копирование и вставка условного форматирования

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

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

2. Щелкните Главная>страница Формат по образцу.
   
   Указатель примет вид кисти.

   Совет

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

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

4. Чтобы выйти из режима вставки условного форматирования, нажмите клавишу ESC.

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

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

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

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

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

1. Щелкните любую ячейку с условным форматированием, которое необходимо найти.
2. На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой Найти и выделить, а затем выберите пункт Выделить группу ячеек.
3. Выберите параметр Условные форматы.
4. Выберите пункт этих же в группе Проверка данных.
   

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

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

• Сведения о приоритете обработки правил условного форматирования
  Создание, редактирование, удаление и просмотр всех правил условного форматирования в книге выполняются в диалоговом окне Диспетчер правил условного форматирования. (Для этого на вкладке Главная выберите команду Условное форматирование и выберите пункт Управление правилами.)
  
  Откроется диалоговое окно Диспетчер правил условного форматирования.
  
  При применении нескольких правил условного форматирования приоритет их проверки определяется порядком их расположения в этом диалоговом окне (сверху вниз).
  Ниже приведен пример с датами окончания срока действия для удостоверений личности. Необходимо изменить фон удостоверений, срок действия которых завершается через 60 дней, но еще не завершился, на желтый цвет, а цвет фона удостоверений с завершенным сроком действия — на красный.
  
  В данном примере ячейки с идентификационными номерами сотрудников, у которых даты сертификации истекают через 60 дней, отформатированы желтым, а идентификационные номера сотрудников с истекшим сроком сертификации отформатированы красным (см. рис. ниже).
  
  Первое правило (которое задает красный цвет фона ячейки, если значение — "истина") проверяет значение даты в столбце B напротив текущей даты (вычисленной с помощью функции СЕГОДНЯ в формуле). Формула для этого правила назначается первому значению в столбце B — B2 Формула для этого правила : =B2<TODAY(). Эта формула проверяет ячейки в столбце B (ячейки B2:B15). Если формула для какой-либо ячейки в столбце B оценивается как "истина", ее соответствующая ячейка в столбце A (например, A5 соответствует B5, а A11 — B11) форматируется красным цветом фона. После оценивания с помощью этого первого правила всех ячеек, указанных в группе Применяется к, проверяется второе правило. Эта формула проверяет, являются ли значения в столбце B менее 60 дней от текущей даты (например, предположим, что сегодняшняя дата — 11.08.2010). Ячейка в столбце B4 "4.10.2010" отстает от сегодняшней даты более чем на 60 дней, поэтому оценивается как "истина" и форматируется желтым цветом фона. Формула для этого правила : =B2<TODAY()+60. Все ячейки, сначала отформатированные красным по правилу с наивысшим приоритетом в списке, остаются без изменений.
  Правило, расположенное выше по списку, имеет более высокий приоритет, чем правило, расположенное ниже по списку. По умолчанию новые правила всегда добавляются в начало списка и поэтому обладают более высоким приоритетом, поэтому вам нужно следить за их порядком. Порядок правил можно изменить в диалоговом окне при помощи кнопок со стрелками Вверх и Вниз.
  

• Что происходит при одновременном использовании нескольких правил условного форматирования
  Иногда к диапазону ячеек можно применить несколько правил условного форматирования. Вот примеры применения правил: сначала тех, которые не конфликтуют друг с другом, а затем — конфликтующих.
  Если правила не конфликтуют Например, если одно правило форматирует ячейку полужирным шрифтом, а другое правило форматирует одну и ту же ячейку красным цветом, ячейка будет отформатирована как полужирным шрифтом, так и красным цветом. В данном случае между двумя форматами нет конфликта, поэтому применяются оба правила.
  При конфликте правил Например, одно правило задает красный цвет шрифта ячейки, а другое — зеленый цвет шрифта ячейки. В данном случае два правила противоречат друг другу, и применить можно только одно из них. Будет применено правило с более высоким приоритетом (находящееся выше по списку в диалоговом окне).

• Влияние операций вставки, заполнения и применения формата по образцу на правила условного форматирования
  В процессе редактирования листа проводится копирование и вставка значений ячеек с условными форматами, заполнение диапазона ячеек с условными форматами и применение формата по образцу. Эти действия могут оказывать влияние на приоритет правил условного форматирования: для конечных ячеек создается новое правило условного форматирования на основе исходных ячеек.
  При копировании и вставке значений ячеек с условным форматированием в лист, открытый в другом экземпляре Excel (другой процесс Excel.exe одновременно выполняется на компьютере), правило условного форматирования не создается в другом экземпляре и форматирование не копируется в этот экземпляр.

• Что происходит при конфликте условного форматирования и форматирования вручную
  Если какое-либо правило условного форматирования проверяется с результатом "Истина", оно имеет приоритет перед любым установленным вручную форматом для этих же выделенных ячеек. Это означает, что в случае конфликта условное форматирование применяется, а ручное форматирование не применяется. При удалении правила условного форматирования форматирование, вручную примененное к диапазону ячеек, остается.
  Форматирование вручную не указывается в диалоговом окне Диспетчер правил условного форматирования и не используется для установки приоритета правил.

• Контроль остановки обработки правил с помощью флажка "Остановить, если истина"
  Для обратной совместимости выберите в диалоговом окне Управление правилами поле Остановить, если true проверка, чтобы имитировать, как условное форматирование может появиться в более ранних версиях Excel, которые не поддерживают более трех правил условного форматирования или нескольких правил, применяемых к одному диапазону.
  Например, если вы используете более трех правил условного форматирования для диапазона ячеек и работаете с более ранней версией Excel, эта версия Excel:

  • выполняется оценивание только трех первых правил;
  • применяется первое истинное правило в последовательности;
  • пропускаются истинные правила с более низким приоритетом.

  В представленной ниже таблице собраны все возможные условия для первых трех правил.

  Если правило	Равно	И правило	Равно	И правило	Равно	Тогда
  Один	Истина	Два	Истина или ложь	Три	Истина или ложь	Первое правило применяется, второе и третье пропускаются.
  Один	Ложь	Два	Истина	Три	Истина или ложь	Второе правило применяется, третье правило пропускается.
  Один	Ложь	Два	Ложь	Три	Истина	Третье правило применяется.
  Один	Ложь	Два	Ложь	Три	Ложь	Ни одно правило не применяется.

  Можно установить или снять флажок Остановить, если истина для изменения поведения по умолчанию:

  • для обработки только первого правила установите флажок Остановить, если истина для первого правила;
  • для обработки только первого и второго правил установите флажок Остановить, если истина для второго правила;

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

Изменение порядка проверки правил условного форматирования

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

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

2. Выберите правило. Допускается одновременный выбор только одного правила.

3. Чтобы повысить приоритет выбранного правила, щелкните стрелку Вверх, а чтобы понизить его приоритет, щелкните стрелку Вниз.

   

4. Если вы хотите остановить дальнейшую проверку правил после выполнения определенного правила, установите флажок Остановить, если истина.

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

Очистка условного форматирования на листе

• На вкладке Главная щелкните Условное форматирование>Очистить правила>Очистить правила со всего листа.

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

Для всего рабочего листа

• На вкладке Главная щелкните Условное форматирование>Очистить правила>Очистить правила со всего листа.

В диапазоне ячеек

1. Выделите ячейки, содержащие условное форматирование.

2. Нажмите "Объектив быстрого анализа", которая отображается в правом нижнем углу выбранных данных.

   Примечание

   Кнопка Экспресс-анализ не отображается в следующих случаях:

   • все ячейки в выделенном диапазоне пусты;
   • значение содержится только в левой верхней ячейке выделенного диапазона, а все остальные ячейки пусты.

3. Нажмите кнопку Очистить формат.
   

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

1. Щелкните ячейку с условным форматированием, которое вы хотите удалить со всего листа.
2. На вкладке Главная щелкните стрелку рядом с кнопкой Найти и выделить и выберите команду Выделить группу ячеек.
3. Выберите параметр Условные форматы.
4. Выберите пункт этих же в группе Проверка данных. Будут выделены все ячейки с одинаковыми правилами условного форматирования.
5. На вкладке Главная щелкните Условное форматирование>Очистить правила>Очистить правила из выбранных ячеек.

Добавление правила условного форматирования

1. Откройте электронную таблицу в Excel для Интернета.

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

   

3. Проверьте или измените ячейки в поле Применить к диапазону.

4. Выберите Тип правила и настройте параметры под свои потребности.

5. По завершении нажмите Готово. Правило будет применено к вашему диапазону.

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

1. Выделите ячейку с условным форматированием, которое нужно изменить. Вы также можете выбрать Домашние > стили > Управление условным форматированием > , чтобы открыть область задач Условное форматирование и выбрать существующее правило.

2. В области задач Условное форматирование отображаются все правила, которые применяются к определенным ячейкам или диапазонам ячеек.

   

3. Наведите курсор на правило и выберите Изменить, щелкнув значок карандаша При этом откроется область задач для редактирования правила.

4. Измените параметры правила и нажмите кнопку Готово , чтобы применить изменения.

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

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

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

Здесь вы можете:

• Выберите другую область в меню Управление правилами. Например, если выбрать этот лист, excel будет искать все правила на текущем листе.
• Добавить правило, выбрав пункт Создать правило (знак "плюс").
• Удалить все правила в области, выбрав пункт Удалить все правила (корзина).

Выбор ячеек для форматирования с помощью формулы

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

В раскрывающемся списке Тип правила выберите Формула.

Введите формулу в поле. Вы можете использовать любую формулу, которая возвращает логическое значение TRUE (1) или FALSE (0), но вы можете использовать операторы AND и OR для объединения набора логических проверок.

Например, =AND(B3="Grain",D3<500) имеет значение true для ячейки в строке 3, если оба значения B3="Grain" и D3<500 имеют значение true.

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

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

• Чтобы удалить условное форматирование в выделенных ячейках, выделите ячейки на листе. Затем выберите Стили > дома > Условное форматирование > Очистить правила > очистки правил из выбранных ячеек.
• Чтобы очистить условное форматирование на всем листе, выберите Главная > Стили > Условное форматирование > Очистить правила > Очистка правил очистки из всего листа.
• Чтобы удалить правила условного форматирования, выберите Главная > стили > Управление условным форматированием > и используйте удаление (корзину) для определенного правила или кнопку Удалить все правила.

Форматирование ячеек с помощью цветовой шкалы

Цветовые шкалы — это визуальные элементы, которые помогают понять распределение и разброс данных. Excel предлагает как двухцветные, так и трехцветные шкалы.

Двухцветная шкала помогает сравнить диапазон ячеек путем использования градации двух цветов. Уровень яркости цвета соответствует высоким или низким значениям. Например, для желто-зеленой шкалы можно указать, что ячейки с более высокими значениями будут насыщенного зеленого цвета, а ячейки с меньшими значениями — насыщенного желтого.

Трехцветная шкала помогает сравнить диапазон ячеек путем использования градации трех цветов. Уровень яркости цвета соответствует высоким, средним или низким значениям. Например, для красно-желто-зеленой шкалы можно указать, что ячейки с высокими значениями будут зелеными, ячейки со средними значениями — желтыми, а ячейки с низкими значениями — красными.

1. Выделите ячейки, к которым необходимо применить условное форматирование с цветовыми шкалами.
2. Щелкните Главная > стили > Условное форматирование > Цветовая шкала и выберите цветовую шкалу.

Форматирование ячеек с помощью гистограмм

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

1. Выделите ячейки, к которым необходимо применить условное форматирование.
2. Выберите Домашние > стили > Условное форматирование > гистограммы данных и выберите стиль.

Форматирование ячеек с помощью набора значков

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

1. Выделите ячейки, к которым необходимо применить условное форматирование.
2. Выберите Главная > стили > Условные наборы значков форматирования > и выберите набор значков.

Форматирование только ячеек с текстовыми, числовыми значениями или значениями даты и времени

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

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

1. Выделите ячейки, к которым необходимо применить условное форматирование.
2. Выберите Стили домашней страницы >> Условное форматирование > : правила выделения ячеек.
3. Выберите вариант сравнения, например Между, Равно, Текст содержит или Дата.

Форматирование только первых или последних значений

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

Некоторые примеры: выделение пяти самых продаваемых продуктов в региональном отчете, 15 % наименее популярных продуктов в клиентском опросе или 25 самых высоких зарплат в отделе.

1. Выделите ячейки, к которым необходимо применить условное форматирование.
2. Выберите Стили > дома > Условное форматирование > вверху или снизу.
3. Выберите нужную команду, например Первые 10 элементов или Последние 10 %.
4. Введите значения, которые нужно использовать, и выберите формат (цвет заливки, текста или границы).

Форматирование только значений, находящихся выше или ниже среднего

Вы также можете выделить значения выше или ниже среднего значения или стандартного отклонения в диапазоне ячеек.

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

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

Форматирование только уникальных или повторяющихся значений

1. Выделите ячейки, к которым необходимо применить условное форматирование.
2. Выберите Стили домашней страницы >> Условное форматирование > : выделение повторяющихся значений правил > ячеек.
3. Введите значения, которые нужно использовать, и выберите формат (цвет заливки, текста или границы).

Копирование и вставка условного форматирования

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

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

2. Щелкните Главная>страница Формат по образцу.
   
   Указатель превратится в кисть.

   Совет

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

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

4. Чтобы выйти из режима вставки условного форматирования, нажмите клавишу ESC.