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

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

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

Условное форматирование с цветовыми шкалами

Условное форматирование: выбранные элементы

Условное форматирование с трехцветной шкалой

Примечание : До версии Excel 2007 условное форматирование приходилось создавать с помощью формулы, и поддерживались всего три уровня. Во всех версиях, начиная с Excel 2007, имеется множество готовых правил, которые легко применять, и поддерживается до 64 уровней. Если для вашей ситуации не подходит ни один из готовых форматов, вы можете по-прежнему использовать формулу для применения условного форматирования.

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

Вы можете выделить повторяющиеся элементы в столбце данных.

Условное форматирование: выделены повторяющиеся значения

  1. Выделите диапазон, в котором хотите выделить цветом повторяющиеся значения (в данном случае это столбец "Имя сотрудника").

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

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

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

    Параметры форматирования: выделение повторяющихся элементов

Диапазон отформатирован и повторяющиеся элементы выделены выбранным цветом.

Сортировка по цвету

Вы можете пойти еще дальше в поиске повторов — отсортировать список по цветам. Так будет проще применить массовые изменения к повторяющимся элементам (например, удалить их).

  1. Щелкните в любом месте диапазона данных, а затем на вкладке Данные в группе Сортировка и фильтр нажмите кнопку Сортировка.

  2. В поле Сортировать по выберите Имя сотрудника, в поле Сортировка – Цвет ячейки, в поле Порядок выберите цвет сортировки, а в следующем поле значение Сверху, после чего нажмите кнопку ОК.

Элементы "Данные" > "Сортировка" > "Сортировка по цвету"

В отсортированной таблице повторяющиеся значения будут сгруппированы сверху.

Условное форматирование с повторяющимися значениями, отсортированными в верхней части списка

Правило Первые 10 элементов позволяет быстро выявить лидеров в диапазоне, например 10 лучших клиентов в списке на основании показателей. Точно так же вы можете выбрать правило Последние 10 элементов, Первые/Последние 10% или Выше/Ниже среднего. Выбрав правило, вы можете изменить число 10 на другое значение. В примере ниже мы выделили 10 лидеров с наибольшим доходом.

Условное форматирование: к диапазону применен формат "Первые 10"
  1. Выделите диапазон, который хотите отформатировать. В примере выше это столбец "Доход".

  2. На вкладке Главная выберите Условное форматирование > Правила отбора первых и последних значений > Первые 10 элементов.

    Условное форматирование: параметры формата "Первые 10"
  3. Затем вы можете настроить количество элементов и цвет заполнения.

    Условное форматирование: выбор первых 10 элементов

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

Условное форматирование: к диапазону применен стиль "Гистограмма"
  1. Выделите диапазон, который хотите отформатировать. В данном случае это столбец "Доход".

  2. На вкладке Главная выберите Условное форматирование > Гистограммы > Градиентная заливка или Сплошная заливка.

    Условное форматирование: коллекция стилей "Гистограмма"
  3. Если после применения гистограмм вы хотите отсортировать список, выделите столбец, а затем на вкладке Данные в группе Сортировка и фильтр щелкните значок Быстрая сортировка по возрастанию или Быстрая сортировка по убыванию для сортировки по возрастанию или по убыванию.

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

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

  2. На вкладке Главная выберите Условное форматирование > Наборы значков > стиль набора значков.

    Условное форматирование: параметры стиля "Набор значков"
  3. Excel попытается интерпретировать и соответственно отформатировать ваши данные. Если вам нужно изменить эту интерпретацию, откройте вкладку Главная и выберите Условное форматирование > Управление правилами > правило Набор значков > Изменить правило. Настройте параметры в разделе Отображать каждый значок согласно этим правилам. В данном случае мы так настроили правило, чтобы все значения больше 45 000 считались положительным результатом, значения от 40 000 до 45 000 — нейтральным, а все значения меньше 40 000 — отрицательным.

    Условное форматирование: диалоговое окно параметров "Набор значков"

Условное форматирование с трехцветной шкалой

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

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

  2. На вкладке Главная выберите Условное форматирование > Цветовые шкалы > шкалу. В данном случае мы использовали вариант Цветовая шкала "Красный-желтый-зеленый".

    Цветовая шкала: красный, желтый, зеленый

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

Коллекция стилей таблиц Excel

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

Подробнее о таблицах Excel можно узнать здесь: Создание и удаление таблицы Excel на листе.

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

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

  2. На вкладке Главная нажмите кнопку Условное форматирование и выберите пункт Создать правило.

    Создание правила форматирования

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

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

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

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

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×