Условное форматирование в Excel

Условное форматирование ячеек листа MS Excel либо OOo Calc дозволяет создателю электрической таблицы значительно сделать лучше зрительное представление инфы. Юзеры не считая завышенного эстетического восприятия получают инструмент контроля.

. конфигураций инфы в ячейках, происходящих в итоге пересчета.

Термин «условное» не значит, что форматирование «вроде бы есть, и вроде бы его нет». «Условное» – это форматирование по условиям, которые задал создатель таблицы для определенных ячеек рабочего листа. Сиим инвентарем почему-либо не весьма нередко пользуются, хотя он весьма и эффективен, и эффектен! (Практически как «масло — масляное»!)

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

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

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

В качестве примера будем применять файл с расчетной программкой из статьи «Расчет усилия листогиба».

Работать с файлом-примером будем в программке MS Excel 2003. Аналогичного результата можно достигнуть, работая в программке OOo Calc из пакета Open Office. Условное форматирование в MS Excel 2007 имеет еще больше увлекательных и различных способностей. Мы их мало коснемся в конце статьи.

Наша основная задачка – разобраться с понятием «условное форматирование» и усвоить, что дает юзеру применение этого инструмента.

В файле примера производится расчет усилия развиваемого листогибочным прессом при вольной гибке деталей из листового металлопроката в «V»-образной матрице. Расчет ведется по двум разным методикам, и результаты сравниваются в конце. В качестве результатов расчета представлена таблица, показывающая зависимость усилия гибки от угла.

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

Использовать условное форматирование будем лишь к результатам, приобретенным по формуле №1 для зрительного сопоставления с плодами формулы №2, которые форматировать не будем.

Формулировка критерий:

1. Наибольшее значение усилия гибки обязано быть выделено жирным шрифтом белоснежного цвета на оранжевом фоне.

2. Если усилие гибки превзойдет 80 тонн, ячейка обязана стать залитой розовым цветом.

3. Если усилие гибки превзойдет 100 тонн, ячейка обязана стать залитой красноватым цветом.

Предназначение критерий форматирования:

1. Становимся курсором мыши на ячейку G12 (активируем ячейку).

2. В строке меню жмем «Формат» > «Условное форматирование…».

3. В выпавшем окне «Условное форматирование» назначаем условия, которые мы определили чуток выше. На снимке экрана ниже показан итог, который нужно достигнуть! Я уверен, что затруднений ни у кого не обязано появиться. Все интуитивно довольно понятно!

Функция «НАИБОЛЬШИЙ($G$12:$P$12;1)» находит в обозначенном спектре G12:P12 наибольшее значение. (Если в конце выражения в скобках поставить 2 заместо 1, функция отыщет 2-ое по величине значение в данном массиве.)

4. Закрываем окно «Условное форматирование» нажатием на клавишу «ОК».

5. Для распространения форматирования на остальные ячейки спектра, копируем содержимое совместно с форматированием ячейки G12 в ячейки H12…P12. (Условное форматирование можно назначать так же, как и обыденное, выделив нужный спектр ячеек либо с помощью специальной вставки, выбрав для копирования лишь форматы.)

Результаты работы условного форматирования:

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

Изменим длину сгибаемого листа в ячейке D3 с 1000 мм на 1700 мм. Заливка ячеек I12 и K12 стала розовой, усилие пресса затмило 80 тонн! Программка цветом ячеек предупреждает: «Внимание. Осторожно. »

Увеличим еще длину сгибаемого листа в ячейке D3 с 1700 мм до 2140 мм. Заливка ячеек I12 и K12 автоматом здесь же перевоплотился в красноватую, усилие пресса превысило 100 тонн! Программка, вроде бы, орет юзеру: «Внимание. Недопустимая операция. »

Итоги.

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

В Excel 2007 все смотрится привлекательнее, изящнее, разнообразнее, но сущность остается той же! Не считая способности сотворения собственных правил, Excel 2007 дает юзеру целый ряд интегрированных правил форматирования. В ячейках заместо заливки можно поместить мелкие гистограммы, оформленные цветовыми градиентами либо применить цветовые шкалы с плавным переходом от 1-го цвета к другому, где конечные цвета – это соответственно наибольшее и малое значения форматируемого спектра. В ячейки с числовыми значениями могут быть добавлены разные значки – стрелки, «огни светофора», разноцветные флажки, столбчатые мелкие диаграммы и остальные доп зрительные эффекты. Правил-условий форматирования быть может не три, а сколь угодно много. Ценность «верхних» правил над «нижними» сохранен так, как и в Excel 2003.

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

Время от времени, разбираясь в способностях разных программ, начинаешь осознавать, как создать то либо другое действие, но не понимаешь, для чего же этот итог быть может с полезностью использован. В данной статье рассмотрен только один из 10-ов либо даже сотен вариантов использования условного форматирования при работе с файлами Excel. Актуальные ситуации и опыт посодействуют для вас равномерно лучше освоить и применять эту одну из массы способностей Excel! Уверен, что инструмент «Условное форматирование» станет вашим ежедневным комфортным и массивным ассистентом при анализе табличных данных!

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

Не запамятовывайте подтвердить подписку кликом по ссылке в письме, которое придет к для вас на обозначенную почту!

Оставляйте Ваши комменты и отзывы, почетаемые читатели.

Ссылка на скачка файла с примером: uslovnoe-formatirovanie (xls 56,5KB).

КАК: Внедрение формул для условного форматирования в Excel — 2021

Excel 2013 Применение условного форматирования при помощи формул (Октябрь 2021).

Table of Contents:

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

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

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

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

Применение нескольких правил в Excel

Для одних и тех же данных можно использовать наиболее 1-го правила для проверки разных критерий. К примеру, в экономных данных могут быть установлены условия, которые используют форматирование конфигураций, когда расходуются определенные уровни, такие как 50%, 75% и 100% от общего бюджета.

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

Поиск данных, превосходящих 25% и 50%

В последующем примере два особых правила условного форматирования будут применяться к спектру ячейки B2 в B5.

• 1-ое правило инспектирует, были ли данные в ячейки A2 в A5 больше соответственного значения в Би 2 в B5 наиболее чем на 25%.
• 2-ое правило инспектирует, не совпадают ли те же данные в A2: A5 превосходит соответственное значение в B2: B5 наиболее чем на 50%.

Как видно на изображении выше, если выполнено одно из обозначенных выше критерий, цвет фона ячейки либо клеток в спектре B1: B4 поменяется.

• Для данных, где разница превосходит 25%, цвет фона ячейки поменяется на зеленоватый.
• Если разница превосходит 50%, цвет фона ячейки поменяется на красноватый.

Правила, применяемые для выполнения данной задачки, будут введены с внедрением условного форматирования Новое правило форматирования диалоговое окно. Начните с ввода данных эталона в ячейки A1-C5, как показано на изображении выше

в 3-ий шаг учебника добавят формулы к ячейкам С2: С4 которые демонстрируют точную процентную разницу меж значениями в ячейках A2: A5 также B2: B5 чтоб проверить точность условных правил форматирования.

Настройка правил условного форматирования

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

Условное форматирование для поиска наиболее 25 процентов роста

1. главный момент ячейки B2 в B5 на листе.
2. Нажми на Основная вкладка из лента.
3. Нажми на Условное форматирование значок в лента чтоб открыть раскрывающийся перечень.
4. изберите Новое правило для открытия Новое правило форматирования диалоговое окно.
5. От Стиль: , изберите традиционный вариант.
6. Во 2-м раскрывающемся перечне нажмите на последнюю опциюИспользуйте формулу, чтоб найти, какие ячейки необходимо форматировать.
7. Введите формула приведенные выше в предоставленное место.
8. Нажми на Формат с: выпадающий перечень, потом изберите Красный наполнить красным текстом вариант.
9. Нажмите Отлично закрыть диалоговые окна и возвратиться на рабочий лист.
10. На этом шаге цвет фона ячейки B3 также B5 должен быть зеленоватым.

Условное форматирование для поиска наиболее 50 процентов роста

главный момент ячейки B2 в B5 на листе.

1. Нажми на Основная вкладка из лента.
2. Нажми на Условное форматирование значок в лента чтоб открыть раскрывающийся перечень.
3. изберите Новое правило для открытия Новое правило форматирования диалоговое окно.
4. От Стиль: , изберите традиционный вариант.
5. Во 2-м раскрывающемся перечне нажмите на последнюю опциюИспользуйте формулу, чтоб найти, какие ячейки необходимо форматировать.
6. Введите формула приведенные выше в предоставленное место.
7. Нажми на Формат с: выпадающий перечень, потом изберите Зеленый Заполнить темным зеленоватым текстом вариант.
8. Нажмите Отлично закрыть диалоговые окна и возвратиться на рабочий лист.

Цвет фона ячейка B3 должен все таки быть зеленоватым, указывая, что процентная разница меж числами в ячейки A3 также B3 составляет наиболее 25 процентов, но меньше либо равно 50 процентам. Цвет фона ячейка B5 следует поменять на красноватый, указав, что процентная разница меж числами в ячейки A5 также B5 составляет наиболее 50 процентов.

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

Чтоб проверить корректность введенных правил форматирования, мы можем вводить формулы в ячейки С2: С5 который высчитает точную процентную разницу меж числами в спектрах A2: A5 также B2: B5.

1. Нажмите на ячейка C2 чтоб создать его активной ячейкой.
2. Введите приведенную выше формулу и нажмите Войти на клавиатуре.
3. Ответ 10% должен показаться в ячейка C2, указывая, что число в ячейка A2 на 10% больше, чем число в ячейка B2.
4. Может быть, будет нужно поменять форматирование на ячейка C2 чтоб показать ответ в процентах.
5. Применять ручка наполнения скопировать формулу из ячейка C2 в ячейки C3 в C5.
6. Ответы для ячейки C3 в C5 должен составлять 30%, 25% и 60%.

Ответы в этих ячейках демонстрируют, что сделанные условные правила форматирования верны, так как разница меж ячейки A3 также B3 превосходит 25 процентов, а разница меж ячейки A5 также B5 составляет наиболее 50 процентов.

Cell B4 не изменил цвет, так как разница меж ячейки A4 также B4 равен 25 процентам, и в нашем правиле условного форматирования обозначено, что для цвета фона для зеленоватого цвета требуется процент больше 25%.

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

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

В примере, применяемом в этом руководстве, конфликты правил, так как оба правила употребляют один и этот же параметр форматирования — изменение цвета фона.

В ситуации, когда 2-ое правило поистине (разница в значении больше 50 процентов меж 2-мя ячейками), то правильно и 1-ое правило (разница в значении больше 25 процентов).

Приказ Excel о приоритете

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

Используемое правило определяется порядком приоритета Excel, в каком обозначено, что правило, которое выше в перечне в диалоговом окне «Диспетчер правил условного форматирования» имеет ценность.

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

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

Применение неконфликтных правил

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

Если 1-ое правило условного форматирования в нашем примере отформатировало спектр ячеек B2: B5 с зеленоватой рамкой заместо зеленоватого цвета фона, два условных правила форматирования не будут конфликтовать, потому что оба формата могут применяться без вмешательства в другое.

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

Условное форматирование и постоянное форматирование

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

Если желтоватый цвет фона был сначало использован к ячейки B2 в B5 в примере, как были добавлены условные правила форматирования, лишь ячейки B2 также B4 будет оставаться желтоватым. Так как введенные правила условного форматирования используются к ячейкам B3 также B5, их цвета фона будут изменяться от желтоватого до зеленоватого и красноватого соответственно.

Внедрение форматирования форматированного HTML-текста в AIM Mail либо AOL Mail

Ах так применять богатое форматирование HTML в ваших почтовых ящиках AIM Mail либо AOL Mail, выделение жирным шрифтом, подчеркивание либо выделение курсивом того, что вы пишете.

Внедрение меток для упрощения ваших формул Excel 2003

Внедрение меток в функциях и формулах в Excel 2003 — это обычной метод ссылаться на блок данных в электрической таблице Excel.

Внедрение пользовательских правил условного форматирования для дат в Excel

Настройте наружный вид ячеек в Excel на базе арифметики даты — к примеру, чтоб выделить прошлые счета-фактуры.

Гайд по использованию условного форматирования в Excel

Что такое «Условное форматирование» и для чего же оно необходимо?

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

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

Как сделать правило? ​

Студенты сдают тест по теме «Рыночная экономика», оценка за тест ставится в формате зачет/незачет. При всем этом «зачет» ставится, если набрано не наименее 80 баллов. Нужно выделить оранжевым цветом строчки со студентами, которые провалили тестирование.

Разглядим, какими правилами можно пользоваться для решения данной задачки.

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

При нажатии на иконку «Условное форматирование» мы лицезреем выпадающий перечень, первым в нём находится раздел «Правила выделения ячеек». При помощи этих правил можно выделить числовые значения (больше, меньше, меж, равно), текстовые (текст содержит) либо даты. Также правило даёт способности отыскать повторяющиеся значения (все значения, которые встречаются в обозначенном спектре больше 1-го раза, но это правило не будет выделять различные значения различными цветами).

В данном примере у нас есть числовое значение – количество баллов. Давайте выделим цветом те ячейки, где количество баллов не дотягивает до зачета.

Для этого выделяем спектр значений, для которого будем использовать правило, и избираем «Правила выделения ячеек» – «Меньше».

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

Сейчас осталось избрать формат.

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

Жмем «Ок» и лицезреем итог: ячейки, значение которых было меньше 80, выделены оранжевым цветом.

Ту же функцию можно повторить и с текстовыми значениями, в нашем случае с отметками зачет/незачет. Для этого нужно выделить новейший спектр, зайти опять в «Правила выделения ячеек» и «Текст содержит».

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

В итоге мы имеем подсвеченные ячейки с подходящей отметкой.

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

Для того, чтоб выделить строчку полностью, зайдём в раздел «Управление правилами».

В открывшемся окне выберемся из выпадающего перечня «Этот лист» (чтоб узреть, какие правила у нас использованы на листе, а не только лишь к ячейке, на которой на этот момент стоит выделение), и нажмём клавишу «Сделать правило».

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

Форматировать все ячейки на основании их значений ​

1-ое правило в перечне – «Форматировать все ячейки на основании их значений». Тут можно избрать двуцветную либо трехцветную шкалу, гистограмму либо набор значков. В нашем случае в этом нет необходимости, но поглядим себе на будущее, как это будет смотреться.

Двуцветная шкала – от малого значения в выделенном спектре к наибольшему. Цветовую схему шкалы по мере необходимости можно поменять.

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

Гистограмма тоже полностью наглядна. Берет наибольшее значение спектра за 100% и пропорционально заполняет ячейку цветом (цвет также можно поменять).

Наборы значков – тоже увлекательное решение. Рядом с текстом в ячейке возникает иконка (либо заместо текста если поставить галочку в поле «Показать лишь значок»). Стили значков можно поменять, также задать для их характеристики (какой значок за какой интервал значений отвечает).

Основное не запамятовывайте указывайте спектр, для которого данное правило будет применяться (это касается хоть какого правила).

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

Примечание: о том, как верно и продуктивно работать с правилами фильтрации, читайте в нашей статье «Правила фильтрации в MS Excel».

Форматировать лишь ячейки, которые содержат

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

Форматировать лишь 1-ые либо крайние значения ​

Это правило не так нередко применяется, но если Для вас необходимо выделить, к примеру, 5 ячеек с наивысшим результатом (значения, которые относятся к первым 5), либо, напротив, 10 ячеек с минимальным результатом (значения, которые относятся к крайним 10), то используйте его.

Форматировать лишь значения, которые находятся выше либо ниже среднего ​

Аналогично, избираем подходящий параметр: выше, ниже, равно либо ниже и т.п. Среднее значение для спектра правило обусловит само, нам необходимо лишь задать нужный формат (и не запамятовать про спектр, к которому будет применяться условие).

Форматировать лишь неповторимые либо повторяющиеся значения ​

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

Применять формулу для определения форматируемых ячеек ​

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

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

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

И непременно избираем спектр. Для этого меняем в выпадающем перечне «Текущий фрагмент» на «Этот лист» и избираем спектр для сделанного правила в графе «Применяется к». В качестве спектра избираем строчки таблицы полностью, от порядкового номера до оценки. Жмем «Применить».

Примечание: Символ $ закрепляет столбец либо строчку, зависимо от того, перед буковкой (столбец) либо цифрой (строчка) он стоит. Написание $D$5 указывает, что в формуле будет употребляться лишь определенная ячейка.

Потому что нам нужно форматировать всю таблицу, т.е. применять в формуле весь столбец D, перед строчкой знак $ убираем (перед столбцом убирать не надо). В итоге остается $D5.

Примечание: Сходу убирать этот символ не стоит, т.к. опосля внедрения правила спектр двинется по строчкам. Самое среднее – применить, позже убрать его, потом применить опять.

И сейчас мы лицезреем итог: оранжевым цветом выделены строчки со студентами, у каких оценка за тест – незачет. Задачка выполнена!

Как поменять либо удалить правило? ​

На одном листе может применяться наиболее 1-го правила на один и этот же, или на различные спектры.

По кнопочке «Поменять правило» раскроется меню, в каком можно отредактировать формулу, поменять характеристики форматирования и т.д.

Клавиша «Удалить правило» удалит то, на которым на этот момент стоит выделение.

Также правила можно поменять местами, нажимая на стрелочки в этом же меню «ввысь» либо «вниз». Производятся правила снизу-вверх, т.е. то, которое сверху, перекрывает нижние (производится крайним).

Галочка «Приостановить, если правда» значит, что при выполнении условия этого правила, остальные правила к сиим ячейкам применяться не будут.

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