Условное форматирование позволяет выделить важные данные и применить определенное форматирование к ячейкам, которые соответствуют определенным критериям.
Условное форматирование Excel - это мощная функция, которая позволяет изменять формат ячеек на основе условия (или критериев). Условное форматирование помогает выделить, выделить или различить данные, хранящиеся в электронной таблице Excel.
Все правила условного форматирования основаны на простой логике: если условия верны, то будет применено определенное форматирование; если условия ложны, то форматирование применяться не будет.
Условное форматирование помогает выделить важные данные в наборе данных, выделить аномалии и визуализировать данные с помощью гистограмм, цветов и наборов значков. В этом руководстве мы узнаем, как применить условное форматирование для выделения ячеек или диапазонов ячеек на основе условия.
Как применить условное форматирование Excel
Например, вы отвечаете за управление большим списком запасов с несколькими товарами и их соответствующим количеством на складе. И если количество определенного товара на складе становится ниже, скажем, 50, важно знать это, чтобы вы могли пополнить запасы.
Если в этом инвентаризационном списке есть набор данных с сотнями строк, не очень эффективно искать строку за строкой, чтобы увидеть, есть ли в этом столбце какие-либо числа, которые меньше '50'. Вот здесь и пригодится функция условного форматирования. Несколькими щелчками мыши вы можете выделить все значения в столбце, который меньше "50".
Условное форматирование с правилами выделения
В нашем примере у нас есть рабочий лист, содержащий записи о продажах некоторых продуктов. мы хотим выделить сумму, которая меньше 500 продаж.
Для этого сначала выберите диапазон ячеек, к которому вы хотите применить правило (условие). В нашем примере мы хотим выделить сумму меньше 500 в столбце «Сумма», поэтому выберите столбец D. Вы можете выделить значения в диапазоне ячеек или нескольких диапазонах ячеек или даже на всем листе.
Затем перейдите на вкладку «Главная» и нажмите «Условное форматирование». В раскрывающемся списке выберите «Highlight Cells Rules» и, поскольку мы хотим найти менее 500 значений, нажмите «Less Than». Excel предлагает семь предустановленных правил выделения. Вы можете использовать любой из них для выделения данных.
Затем появится диалоговое окно «Меньше». При этом введите «500» в поле «Форматировать ячейки МЕНЬШЕ, ЧЕМ» и выберите форматирование для выделения в раскрывающемся списке рядом с ним.
Теперь ячейки, содержащие менее 500 значений, будут выделены в выбранном форматировании.
Давайте посмотрим, как выделить значения, содержащие определенную текстовую строку. В следующем примере мы хотим выделить всех сотрудников из Нового Южного Уэльса (NSW).
Для этого перейдите на вкладку «Главная» -> «Условное форматирование» -> «Правила выделения ячеек» -> «Текст, который содержит».
Вы можете использовать другие параметры в горизонтальном раскрывающемся меню «Правила выделения ячеек», чтобы выделить повторяющиеся значения, даты, больше, чем, равно или между значениями.
В диалоговом окне "Текст, который содержит" введите в поле «NSW», выберите форматирование и нажмите «ОК».
Результат:
Условное форматирование с помощью правил сверху / снизу
Правила сверху / снизу - еще одно полезное встроенное предустановленное правило условного форматирования, доступное в Excel. Эти правила позволяют привлечь внимание к верхнему (n) числу элементов, верхнему (n) числу процентов, нижнему (n) числу элементов, нижнему (n) числу процентов или значениям ячеек, которые находятся выше средний или ниже среднего.
Допустим, у вас есть запись об успеваемости учащегося в таблице, и вы хотите найти 10 лучших исполнителей (10 лучших рангов) из этого списка. С помощью условного форматирования вы можете выделить 10 или 15 самых популярных элементов или любое (n) количество элементов с высоким рейтингом. Чтобы выделить первые 10 элементов в диапазоне ячеек, сначала выберите диапазон (Всего) в таблице.
Затем перейдите к «Условное форматирование», разверните «Правила сверху / снизу» и выберите вариант «10 первых элементов ..».
В диалоговом окне «10 лучших элементов» измените количество рангов с помощью крошечных стрелок в левом поле. Если вы хотите выделить 20 лучших рангов (общую оценку) в своем списке оценок, установите число 20. По умолчанию уже 10, поэтому мы его сохраняем. Выберите форматирование ячейки в правом поле и нажмите «ОК».
10 лучших оценок из столбца «Всего» выделены, как показано ниже.
Допустим, вы хотите найти оценки ниже среднего в столбце «Экзамен 1» той же таблицы оценок. Для этого перейдите на вкладку «Главная» -> «Условное форматирование» -> «Правила сверху / снизу» -> Ниже среднего.
Вы можете настроить собственное условное форматирование вместо выбора одного из предопределенных условий. Чтобы создать собственное форматирование, выберите параметр «Пользовательский формат» в окне «Ниже среднего».
Откроется новое окно «Форматирование ячеек», в котором вы можете настроить форматирование ячеек. Мы выбираем оранжевый цвет, чтобы выделить оценки ниже среднего. После того, как вы закончите, дважды нажмите «ОК», чтобы увидеть результат.
Результат:
Применить панели данных
Панели данных - это просто горизонтальные полосы в ячейках. Размер полосы зависит от значения ячейки, соответствующей значению других ячеек в выбранном диапазоне. Это означает, что значение короткой полосы низкое по сравнению со значениями других ячеек, а длинная полоса означает, что значение высокое по сравнению со значениями других ячеек.
Сначала выберите диапазон ячеек, который вы хотите визуализировать с помощью гистограмм.
Затем перейдите к «Условное форматирование» на вкладке «Главная», затем разверните «Панели данных» в раскрывающемся списке и выберите свой выбор панели данных.
Разница между форматом панели данных и другими форматами заключается в том, что он отображается во всех ячейках, а не соответствует определенному условию.
Применить цветовые шкалы
Цветовые шкалы очень похожи на гистограммы, поскольку они обе коррелируют значение отдельной ячейки со значением других ячеек в выбранном диапазоне. Однако гистограммы визуализируют значение ячейки по длине полосы, а цветовые шкалы - с цветовыми градиентами.
В каждом варианте цветовой шкалы используются двух- или трехцветные градиентные узоры. Один цвет назначается наивысшим значениям, другой цвет - к наименьшим значениям, а все остальные значения между ними представляют собой смесь этих двух цветов.
Для этого мы будем использовать тот же пример, что и для гистограмм. Выберите диапазон ячеек, перейдите в раздел «Главная» -> «Условное форматирование» -> «Цветовые шкалы». Затем выберите цветовую гамму в горизонтальном раскрывающемся меню «Цветовые шкалы».
Когда мы выбрали первый цветовой диапазон, красный цвет назначается самому низкому значению, зеленый цвет - самому высокому значению, а все значения между ними - это цвета, которые представляют собой смесь красного и зеленого цветов (как показано ниже). Поскольку желтый цвет получается путем объединения красного и зеленого с одинаковой интенсивностью, ему присваиваются ячейки со средними значениями.
Применить наборы значков
Наборы значков - это еще один метод визуализации данных внутри каждой ячейки и сопоставления ячеек друг с другом.
Выделите ячейки и нажмите Главная -> Условное форматирование -> Наборы значков. Вы можете выбрать любой из этих наборов значков, который больше подходит для ваших нужд. В нашем примере мы выберем первый вариант в разделе «Направленный».
Красные, желтые и зеленые стрелки, которые указывают на товары с низкой, средней или высокой ценой соответственно.
Удалить условное форматирование
Чтобы удалить условное форматирование, вернитесь к параметру «Условное форматирование» на вкладке «Главная» и нажмите «Очистить правила». Затем выберите, какие правила вы хотите очистить.
Мы выберем «Очистить правила со всего листа», чтобы удалить с листа все условное форматирование.
Теперь вы узнали, как использовать условное форматирование для форматирования ячеек с предварительно заданным условием Excel.