Применение условного форматирования в 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.

См. также

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

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

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

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

×