Excel: Условное форматирование (часть 2)
Гораздо более мощный и красивый вариант применения Условного форматирования — это возможность проверять не значение выделенных ячеек, а заданную формулу.
К примеру, можно легко использовать условное форматирование для проверки сроков оплат или выполнения задач.
Рассмотрим ситуацию, когда необходимо выделить даты просроченных оплат красным цветом, а тех, что предстоят в ближайшую неделю, – желтым.
- Выделите диапазон, к котором будет применяться Условное форматирование.
- Выберите вкладку Главная > Условное форматирование > Создать правило.
- В диалоговом окне Создание правила форматирования выберите пункт Использовать формулу для определения форматируемых ячеек .
- В разделе Форматировать значения, для которых следующая формула является истинной введите формулу:
Функция СЕГОДНЯ() отображает текущую дату.
Таким образом, формула служит для определения дат в столбце B, которые «меньше» чем сегодня, т.е. предшествующих сегодняшней дате.
- Нажмите кнопку Формат . Выберите необходимые шрифт и заливку.
- Нажмите кнопку ОК несколько раз, чтобы закрыть все диалоговые окна.
Первое правило сформировано. Просроченные даты оплат будут выделяться красным цветом.
Как создать второе правило
Снова проведите действия, как в пунктах 1-3, т.е. выделите тот же диапазон, откройте опцию Создать правило , выберите пункт Использовать формулу для определения форматируемых ячеек .
Теперь необходимо ввести другую формулу.
Мы хотим найти даты, которые будут больше или равны сегодняшней ( B3>=СЕГОДНЯ() ), но не более чем на неделю, т.е. разница между датами должна быть меньше 7-и дней ( (B3-СЕГОДНЯ())
Эти два условия должны выполняться одновременно, поэтому применяем функцию И().
Формула будет выглядеть так:
Нажмите кнопку Формат . Выберите необходимые шрифт и заливку.
Теперь к диапазону будут применяться сразу два правила. Даты просроченных оплат будут выделены красным цветом, а тех, что предстоят в ближайшую неделю, – желтым.
Условное форматирование текста
Используйте средство быстрого анализа для условного форматирования ячеек в диапазоне с повторяющимся текстом, уникальным текстом и текстом, который совпадает с указанным текстом. Можно даже условно отформатировать строку на основе текста в одной из ячеек в строке.
Применение условного форматирования на основе текста в ячейке
- Выберите ячейки, к которым нужно применить условное форматирование. Щелкните первую ячейку в диапазоне и перетащите ее в последнюю ячейку.
- Щелкните ГЛАВНАЯ >условное форматирование >выделение правил ячеек >текст, который содержит. В поле Текст, содержащий слева, введите текст, который нужно выделить.
- Выберите цветовый формат текста и нажмите кнопку ОК.
Гайд по использованию условного форматирования в Excel
Что такое «Условное форматирование» и для чего оно нужно?
Очень часто, работая в таблицах MS Excel, мы сталкиваемся с большими объемами информации. Согласитесь, работа с данными становится гораздо проще и приятней, если эти данные выделены визуально. Не обязательно вчитываться в текст или цифры, достаточно бросить взгляд и глаз отделит нужные строки по цвету.
Но что самое важное, условное форматирование может в разы Вашу облегчить работу при использовании правил фильтрации, которые также очень популярны при работе с большими объемами данных.
Как создать правило?
Чтобы создать правило, щелкните на главной странице меню на иконку «Условное форматирование».
Затем нужно выбрать вид правила, с которым будем работать. Каждый вид правила преследует определенную цель. Чтобы было наглядней, давайте с ними ознакомимся на примере.
Пример выполнен в MS Excel 2013.
Студенты сдают тест по теме «Рыночная экономика», оценка за тест ставится в формате зачет/незачет. При этом «зачет» ставится, если набрано не менее 80 баллов. Необходимо выделить оранжевым цветом строки со студентами, которые провалили тестирование.
Рассмотрим, какими правилами можно воспользоваться для решения данной задачи.
Правила выделения ячеек
При нажатии на иконку «Условное форматирование» мы видим выпадающий список, первым в нём находится раздел «Правила выделения ячеек». С помощью этих правил можно выделить числовые значения (больше, меньше, между, равно), текстовые (текст содержит) или даты. Также правило даёт возможности найти повторяющиеся значения (все значения, которые встречаются в указанном диапазоне больше одного раза, но это правило не будет выделять разные значения разными цветами).
В данном примере у нас есть числовое значение – количество баллов. Давайте выделим цветом те ячейки, где количество баллов не дотягивает до зачета.
Для этого выделяем диапазон значений, для которого будем применять правило, и выбираем «Правила выделения ячеек» – «Меньше».
После этого видим открывшееся окошко для ввода данных. Вводим количество баллов, необходимое для зачета – 80.
Теперь осталось выбрать формат.
Можно выбрать из предложенного списка, а можно нажать на «Пользовательский формат» и задать его самостоятельно. Для этого в открывшемся окне нужно поменять параметры, в нашем случае – выбрать оранжевую заливку. При необходимости здесь же можно изменить формат текста, шрифт (цвет, начертание и т.д.), границы (цвет, тип линии).
Нажимаем «Ок» и видим результат: ячейки, значение которых было меньше 80, выделены оранжевым цветом.
Ту же процедуру можно повторить и с текстовыми значениями, в нашем случае с отметками зачет/незачет. Для этого необходимо выделить новый диапазон, зайти снова в «Правила выделения ячеек» и «Текст содержит».
В открывшемся окошке вводим текст, который нам необходимо выделить – слово «незачет» и задаем нужный формат точно так же, как делали ранее.
В итоге мы имеем подсвеченные ячейки с нужной отметкой.
Так мы посмотрели наипростейшее применение правил условного форматирования, которые Вы сможете использовать без особых затруднений. Но давайте всё-таки вернёмся к исходному заданию. Нас просили выделить строки со студентами, не сдавшими тест, а нам пока удалось выделить только отдельные ячейки.
Для того, чтобы выделить строку целиком, зайдём в раздел «Управление правилами».
В открывшемся окне выберемся из выпадающего списка «Этот лист» (чтобы увидеть, какие правила у нас применены на листе, а не только к ячейке, на которой в данный момент стоит выделение), и нажмём кнопку «Создать правило».
Здесь мы также видим список правил, которые нам предлагается применить.
Форматировать все ячейки на основании их значений
Первое правило в списке – «Форматировать все ячейки на основании их значений». Здесь можно выбрать двухцветную или трехцветную шкалу, гистограмму или набор значков. В нашем случае в этом нет необходимости, но посмотрим для себя на будущее, как это будет выглядеть.
Двухцветная шкала – от минимального значения в выделенном диапазоне к максимальному. Цветовую схему шкалы при необходимости можно изменить.
Трехцветная шкала выглядит поинтересней, здесь можно указать разные цветовые решения для минимальных, промежуточных и максимальных значений.
Гистограмма тоже вполне наглядна. Берет максимальное значение диапазона за 100% и пропорционально заполняет ячейку цветом (цвет также можно изменить).
Наборы значков – тоже интересное решение. Рядом с текстом в ячейке появляется иконка (или вместо текста если поставить галочку в поле «Показать только значок»). Стили значков можно поменять, а также задать для них параметры (какой значок за какой интервал значений отвечает).
Главное не забывайте указывайте диапазон, для которого данное правило будет применяться (это касается любого правила).
И еще важный момент: если в дальнейшем Вы будете использовать правила фильтрации, то важно выбрать такие правила условного форматирования, которые облегчат Вам дальнейшую работу. Например, есть возможность поставить фильтр по цвету или по значку ячейки, но по гистограмме отфильтровать не получится.
Примечание: о том, как правильно и продуктивно работать с правилами фильтрации, читайте в нашей статье «Правила фильтрации в MS Excel».
Форматировать только ячейки, которые содержат
Здесь мы не будем подробно останавливаться, так как это те же самые правила для числовых, которые мы рассматривали вначале: больше, меньше, между, равно и т.д.
Форматировать только первые или последние значения
Это правило не так часто применяется, но если Вам нужно выделить, например, 5 ячеек с наивысшим результатом (значения, которые относятся к первым 5), или, наоборот, 10 ячеек с наименьшим результатом (значения, которые относятся к последним 10), то используйте его.
Форматировать только значения, которые находятся выше или ниже среднего
Аналогично, выбираем нужный параметр: выше, ниже, равно или ниже и т.п. Среднее значение для диапазона правило определит само, нам нужно только задать необходимый формат (и не забыть про диапазон, к которому будет применяться условие).
Форматировать только уникальные или повторяющиеся значения
Это правило, как понятно из его названия, покажет либо все уникальные, либо все повторяющиеся значения в диапазоне на ваш выбор. Например, применим его к столбцу с количеством баллов и увидим, с каким результатом прошли тест более одного человека.
Использовать формулу для определения форматируемых ячеек
Ну вот и добрались до последнего пункта в этом меню, и, на наш взгляд – самого универсального. С помощью этого правила мы и выполним условие поставленной задачи.
Нам необходимо выделить всех студентов, у которых стоит «незачет». Для этого пишем формулу: выбираем первую ячейку в столбце «Оценка», пишем «равно» и нужное значение, т.е. «незачет». Настраиваем нужный формат.
Примечание: Если ячейка имеет текстовый формат, но значение ячейки в формуле нужно писать в кавычках.
И обязательно выбираем диапазон. Для этого меняем в выпадающем списке «Текущий фрагмент» на «Этот лист» и выбираем диапазон для созданного правила в графе «Применяется к». В качестве диапазона выбираем строки таблицы целиком, от порядкового номера до оценки. Нажимаем «Применить».
Примечание: Знак $ закрепляет столбец или строку, в зависимости от того, перед буквой (столбец) или цифрой (строка) он стоит. Написание $D$5 показывает, что в формуле будет использоваться только конкретная ячейка.
Так как нам необходимо форматировать всю таблицу, т.е. использовать в формуле весь столбец D, перед строкой символ $ убираем (перед столбцом убирать не нужно). В итоге остается $D5.
Примечание: Сразу убирать этот знак не стоит, т.к. после применения правила диапазон сдвинется по строкам. Самое оптимальное – применить, потом убрать его, затем применить снова.
И теперь мы видим результат: оранжевым цветом выделены строки со студентами, у которых оценка за тест – незачет. Задача выполнена!
Как изменить или удалить правило?
На одном листе может применяться более одного правила на один и тот же, либо на разные диапазоны.
По кнопке «Изменить правило» откроется меню, в котором можно отредактировать формулу, изменить параметры форматирования и т.д.
Кнопка «Удалить правило» удалит то, на которым в данный момент стоит выделение.
Также правила можно менять местами, нажимая на стрелочки в этом же меню «вверх» или «вниз». Выполняются правила снизу-вверх, т.е. то, которое сверху, перекрывает нижние (выполняется последним).
Галочка «Остановить, если истина» означает, что при выполнении условия этого правила, другие правила к этим ячейкам применяться не будут.
Вы можете скачать файл с примером, который мы разобрали, и потренироваться на нем самостоятельно.
Имя файла: —MS-Excel
Размер файла: 13 kb
Примечание: в данном примере так же используется формула ЕСЛИ() для автоматического проставления оценки в зависимости от набранного количество баллов. Подробнее о том, как применять эту формулу, читайте в статье «Логические формулы в MS Excel».
Условное форматирование в Excel: ничего сложного
В Excel вы можете использовать условное форматирование для реорганизации содержимого ячеек по своему усмотрению. Например, можно отобразить совершенно другой текст или изменить шрифт. Мощный набор инструментов включает множество опций, а также предопределенные правила. В этой статье мы расскажем о наиболее важных типах правил условного форматирования и рассмотрим их на примерах.
Условное форматирование может сделать работу в MS Excel 2010 значительно удобнее. Собственно, затем оно и нужно. О самых популярных его функциях мы рассказываем в данной заметке.
Отформатируйте все ячейки на основе их значений
Это правило Excel предоставляет пользователю графические параметры, например, для визуального оформления числовых значений. Оно облегчит анализ ваших данных.
- Выберите область в электронной таблице Excel и нажмите вкладку «Главная».
- Теперь кликните «Условное форматирование», а затем нажмите «Создать правило …».
- В новом окне выберите тип «Форматировать все ячейки на основании их значений».
- Выделите цветом те ячейки, значения которых на единицу больше или меньше среднего значения
- Значения, которые не входят в определенную область, могут быть легко найдены с помощью Excel. Эта функция отмечает цветом ячейки, которые не соответствуют значению.
- Выберите область в электронной таблице Excel и нажмите вкладку «Главная».
Снова перейдите в «Условное форматирование», а затем в «Создать правило …». - Если вы теперь выберете «Стандарт», то зададите настройки, по которым ячейки со значениями выше или ниже среднего будут выделены цветом.
Двух- и трехцветная шкала в Excel
Вы можете добавить наглядности вашим данным с помощью градиента. Например, покажите по шкале от одного до десяти, насколько высоко излучение в определенной области.
- В нижней части окна теперь выберите стиль формата «двухцветная шкала».
- В качестве типа выберите «минимальное значение» для минимального и «максимальное значение» для максимального.
- При необходимости измените цвета и закройте окно нажатием на «ОК». Для трехцветной шкалы можно просто установить форматирование для среднего значения.
Гистограммы
Вы также можете использовать гистограммы при форматировании. Например, это нужно, чтобы сделать график ваших расходов за месяц.
- Просто выберите «Гистограмма» в качестве стиля.
- В качестве типа выберите «Минимум» и «Автоматический».
- Оформите нижеуказанные столбцы и закройте, нажав на «ОК».
Символы
Наборы символов можно использовать, например, для ввода силы сигнала сети в процентах и тому подобных задач.
- В нижней части окна выберите стиль.
- В таблице выберите подходящий символ.
- Для первого параметра «ЕСЛИ ЗНАЧЕНИЕ:» установите «>». Остальное можно оставить без изменений. Нажатие на «ОК» сохраняет правило Excel.
«Форматировать только ячейки, которые содержат…»
Этот тип форматирования особенно полезен для выделения отдельных специальных значений. Например, все числа ниже 0 могут быть отформатированы красным цветом.
- Выберите область в таблице, для которой вы хотите применить форматирование, и создайте новое правило с типом «Форматировать только ячейки, содержащие».
- Установите следующий параметр: «Значение ячейки меньше 0»
- Нажмите кнопку «Формат …» и выберите красный цвет в новом окне как цвет выделения.
- Дважды нажмите «ОК», чтобы создать правило.
Представить числовые данные в виде текста
Если у вас есть предопределенные повторяющиеся текстовые данные, этот тип форматирования идеален для них. Он не только экономит ваше время, но и предотвращает опечатки.
- Выберите область в электронной таблице Excel, к которой вы хотите применить форматирование, и создайте новое правило с помощью «Форматировать только ячейки, содержащие».
- Установите следующий параметр: «Значение ячейки, равное 1»,
- Нажмите кнопку «Формат …» и выберите вкладку «Числа» в новом окне.
- В левой части боковой панели нажмите «Пользовательский».
- В текстовом поле под меткой «Тип:» введите следующее: «Hello World!» — кавычки при вводе обязательны.
- Дважды нажмите «ОК», чтобы завершить действия.
- Если вы вводите единицу в ячейку, находящуюся в определенной вами области, вместо этого появится текст «Hello World!». Таким же образом вы можете указать дополнительные тексты для других чисел.
Используйте формулу, чтобы определить ячейки для форматирования
- Как следует из названия, данный тип отформатирует любое значение, к которому применяется конкретная формула. В этом примере все данные Excel, которые касаются будущих дат, будут выделены красным цветом.
- Выберите область в таблице, для которой вы хотите применить форматирование, и создайте новое правило с типом «Использовать формулу для определения форматируемых ячеек».
- Введите формулу = B2> СЕГОДНЯ ().
- Нажмите кнопку «Формат …» и выберите красный цвет в новом окне.
- Подтвердите выбор дважды нажав «ОК».
В этой статье показано лишь несколько вариантов условного форматирования. Функция может использоваться чрезвычайно универсально, и немного набив руку вы можете добиться отличных результатов.
Фото: компании-производители, pixabay.com
Читайте также:
- Как добавить комментарии в формулы Excel
- Как в Excel вставить кнопку для запуска макроса