Условное форматирование в Excel
Опубликовано 13 Ноя 2013
Рубрика: Справочник Excel | 7 комментариев
Условное форматирование ячеек листа 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, функция найдет второе по величине значение в заданном массиве.)
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 предлагает пользователю целый ряд встроенных правил форматирования. В ячейках вместо заливки можно поместить маленькие гистограммы, оформленные цветовыми градиентами или применить цветовые шкалы с плавным переходом от одного цвета к другому, где конечные цвета – это соответственно максимальное и минимальное значения форматируемого диапазона. В ячейки с числовыми значениями могут быть добавлены различные значки – стрелки, «огни светофора», разноцветные флажки, столбчатые маленькие диаграммы и другие дополнительные визуальные эффекты. Правил-условий форматирования может быть не три, а сколь угодно много. Приоритет «верхних» правил над «нижними» сохранен так, как и в Excel 2003.
Выглядеть условное форматирование в Excel 2007 может, например, так, как на рисунке ниже этого текста, где показана таблица выпуска и реализации некой продукции в разные годы.
Иногда, разбираясь в возможностях различных программ, начинаешь понимать, как сделать то или иное действие, но не понимаешь, для чего этот результат может быть с пользой применен. В этой статье рассмотрен лишь один из десятков или даже сотен вариантов использования условного форматирования при работе с файлами Excel. Жизненные ситуации и опыт помогут вам постепенно лучше освоить и использовать эту одну из массы возможностей Excel! Уверен, что инструмент «Условное форматирование» станет вашим повседневным удобным и мощным помощником при анализе табличных данных!
Ссылка на скачивание файла с примером: uslovnoe-formatirovanie (xls 56,5KB).
Статьи с близкой тематикой
Отзывы
7 комментариев на «Условное форматирование в Excel»
Ваш отзыв
Посетители: 2,1 млн
Добрый день! Ваши статьи очень полезные. Спасибо. Помогите решить проблему. Необходимо пометить неиспользуемые фамилии справочника, которые выбираются из списка. Какое надо наложить условие.
Татьяна , добрый день!
Как программа «узнает», что вы не используете некоторые фамилии, если вы сами ей об этом не «скажите»? Excel не может сам отследить ваши действия и понять, чего вы хотите.
Закрашивайте вручную или ставьте в строке напротив фамилии в дополнительном столбце, например, 1 и применяйте для всей строки или нужных ячеек условное форматирование с условием типа: «если равно 1 — залить ячейки красным».
Возможно я не совсем понял условие вашей задачи. Если так — напишите мне на почту и вышлите ваш файл.
Ваши статьи станут еще полезнее, если вы будете создавать их в незначительно отличающихся вариантах для разных близкородственных программ, например для Open Office, Libre Office, таблицы Gnumeric, KOffice (он же Calligra Suite).
Это объясняется тем, что пользователи привыкают к конкретной программе, и с некоторым трудом мысленно «переводят», приспосабливают, к своим программам полезные материалы ваших статей.
Спасибо, Вячеслав, за совет. Постараюсь его начать реализовывать в ближайшее время.
Есть некая таблица. Грубо говоря 3 первых столбца в ней это данные опр. формата заносимы вручную. 4-й столбец состоит из ячеек типа список. При помощи условного форматирования сделано что при выборе определенного значения ячейки 4-го столбца, соответствующая строка закрашивается в определенный цвет.(Правила форматирования сделаны для одной строки, вся таблица использует одну вспомогательную таблицу для создания списка 4-го столбца ) ВОПРОС: как это форматирование применить(скопировать) к другим строкам. Вообще не чего не выходит. Не форматировать же каждую строку отдельно...их там немерено. Подскажите как решить данную задачу...Заранее благодарен за ответ
Алексей, пришлите Ваш файл с примером.
А вообще-то, действуйте по обычной процедуре — выделите всю область ячеек, для которых необходимо применить условное форматирование, примените к ним условное форматирование, написав условия.
Александр,доброго времени суток, пробовал...но то ли форматирование сделано не совсем верно то ли я чего то не доучил...заливает цветом копируемой строки и получается полная несуразиться.(куда прислать файл)
Заранее благодарен за ответ.