Как использовать условное форматирование в Microsoft Excel

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

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

Содержание

• Правила выделения ячеек
• Применение правил для верхних или нижних значений
• Использование полос данных
• Добавление цветовых шкал
• Вставка наборов значков
• Как редактировать правило условного форматирования
• Как удалить правило условного форматирования
• Часто задаваемые вопросы

Правила выделения ячеек

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

Выделение значений

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

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

1. Введите значение для сравнения в поле слева от всплывающего окна. В качестве альтернативы введите ссылку на ячейку, содержащую это значение. Например, мы выделяем ячейки в нашем выбранном диапазоне, которые больше “3,000”.

1. Выберите выделение, которое нужно применить, из выпадающего списка справа. Мы выбираем “Светло-красная заливка”.

1. Нажмите “ОК”, чтобы применить и сохранить форматирование.

Выделение текста

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

1. Выберите ячейки, которые вы хотите проверить, перейдите на вкладку “Главная”, откройте выпадающее меню “Условное форматирование”, выберите “Правила выделения ячеек” и выберите “Текст, который содержит”.

1. Введите текст в поле слева от всплывающего окна. Например, мы выделяем ячейки, которые содержат оценку “A”.

1. Выберите выделение в списке справа. Мы выбираем “Зеленая заливка с темно-зеленым текстом”.

1. Нажмите “ОК”, чтобы применить ваше форматирование.

Выделение дат

Еще один способ выделить значения - это использование дат. Вы можете выбрать определенный цвет для сроков платежей по предстоящим счетам или задачам с датами из прошлого.

1. Выберите ячейки, которые вы хотите проверить, перейдите на вкладку “Главная”, откройте выпадающее меню “Условное форматирование”, выберите “Правила выделения ячеек” и выберите “Дата, происходящая”.

1. Выберите временной интервал во всплывающем окне. В качестве примера мы выбираем “Этот месяц”.

1. Выберите выделение из списка справа. Мы выбираем “Желтая заливка с темно-желтым текстом”.

1. Нажмите “ОК”, чтобы сохранить форматирование.

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

Применение правил для верхних или нижних значений

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

Выделение верхнего процента

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

1. Выберите ячейки, которые вы хотите протестировать, перейдите на вкладку “Главная”, откройте выпадающее меню “Условное форматирование”, перейдите к “Верхним/Нижним правилам” и выберите “Верхние 10%”.

1. Оставьте число “10” в поле слева от всплывающего окна или измените этот процент, введя число или используя стрелки. Например, мы изменяем его, чтобы увидеть верхние “20” процентов.

1. Выберите форматирование, которое вы хотите применить, из списка справа. Мы выбираем “Красная рамка”.

1. Нажмите “ОК”, чтобы сохранить и применить форматирование после просмотра предварительного просмотра.

Выделение ниже среднего

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

1. Выберите ячейки, перейдите на вкладку “Главная”, откройте выпадающее меню “Условное форматирование”, перейдите к “Верхним/Нижним правилам” и выберите “Ниже среднего”.

1. Выберите форматирование, которое вы хотите применить, затем нажмите “ОК”. Мы выбираем “Пользовательский формат”, затем выбираем “Жирный курсив” в следующем окне.

1. Выберите “ОК”, чтобы сохранить форматирование, которое вы видите в предварительном просмотре.

Совет: знали ли вы, что вы также можете использовать условное форматирование в Microsoft Outlook, чтобы выделить определенные электронные письма? Ознакомьтесь с нашим руководством по настройке этого.

Использование полос данных

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

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

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

1. Нажмите стиль и цвет, которые вы хотите использовать.

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

Добавление цветовых шкал

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

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

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

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

Первый цвет в шкале представляет наивысшее значение в группе. Большие группы элементов будут показывать разные оттенки цветов в зависимости от различий между значениями. В качестве примера мы выбираем опцию “Красный Желтый Зеленый”.

Наивысшие значения красные, средние значения желтые, а низкие значения зеленые, с промежуточными значениями в разных оттенках этих цветов.

Совет: если вам трудно получить правильные результаты, потому что ваша таблица беспорядочна, попробуйте сначала очистить ваши данные.

Вставка наборов значков

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

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

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

В качестве примера у нас есть диапазон оценок для наших городов, поэтому мы выбираем один из наборов “Оценок”. Поскольку наши оценки находятся в диапазоне от одного до трех, набор “3 Звезды” идеален.

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

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

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

1. Перейдите на вкладку “Главная”, откройте меню “Условное форматирование” и выберите “Управление правилами”.

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

1. Если вы хотите только изменить диапазон ячеек, сделайте это в соответствующем поле под “Применяется к”. В противном случае нажмите “Редактировать правило” вверху.

1. Внесите изменения в окне “Редактировать правило форматирования”, которое откроется, затем нажмите “ОК”. В нашем примере мы изменяем стиль значков.

1. Нажмите “ОК”, затем “Применить”, чтобы сохранить ваши изменения.

1. Ваш лист обновится с вашими правками.

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

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

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

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

1. Перейдите на вкладку “Главная”, откройте меню “Условное форматирование” и выберите “Управление правилами”.

2. Выберите правило, выберите “Удалить правило” вверху, затем нажмите “ОК” и “Применить”.

Используйте опцию очистки правил

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

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

1. Ваше правило будет удалено.

Часто задаваемые вопросы

Могу ли я иметь два правила условного форматирования для одних и тех же ячеек?

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

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

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

Помимо предустановленных правил условного форматирования, показанных здесь, вы можете настроить свои собственные. Выберите ячейки, которые вы хотите использовать, перейдите на вкладку “Главная” и нажмите “Условное форматирование -> Новое правило”. Выберите тип правила вверху, настройте остальную часть правила в разделе внизу, затем нажмите “ОК”, чтобы сохранить его.

Могу ли я использовать условное форматирование для выделения целой строки или столбца?

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

Кредит изображения: Pixabay. Все скриншоты сделаны Сэнди Уритенхаус.