Условное форматирование. Основы
Условное форматирование: создание и редактирование правил





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

Условное форматирование: создание и редактирование правил

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

Условное форматирование: создание правил и управление ими

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

 

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

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

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

При работе в Excel не забываем общего правила – сначала выделяем объект, потом применяем команду. Поэтому для начала выделите диапазон ячеек B5:B38, среди которых нужно найти ячейки для заливки желтым и после выполните команду: Главная – Стили – Условное форматирование – Создать правило [Home – Styles – Conditional_Formatting – New_Rule] и из списка правил выберите 2-ой. Вообще, всего существует 6 типов правил, но большинство типовых ситуаций уже сгруппированы под вторым типом правил, и если его недостаточно – используем шестой. Остальные типы правил смысл посмотреть конечно же имеет (мы разберем на занятиях первый, второй и шестой)

После выбора правила необходимо его настроить в нижней части окна. Для решения моей первой задачи я выбрала в первом списке позицию Текст [Text] вместо Значение ячейки [Cell_value] (ведь нас интересует только одно слово, а не все содержимое ячейки), далее оставила настройку содержит [contains] и вписала в поле ввода искомое слово брак. Если выполнится такое условие, то тогда – укажите при помощи кнопки Формат [Format] (она чуть ниже) как должны такие ячейки выглядеть, у меня это желтая заливка. Можно нажимать ОК

Excel осуществил требуемое форматирование выборочно, согласно условию, но при этом еще и наделил выбранный диапазон ячеек динамическими свойствами. Это означает, что инструмент Условное форматирование будет каждый раз пересчитывать свой результат (изменять оформление ячеек) при изменении исходных условий (их содержимого). Попробуйте в любой неоформленной ячейке этого диапазона написать слово брак и она тут же станет желтой. Или же наоборот – в любой желтой ячейке стереть хотя бы одну букву из слова брак и желтая заливка пропадет

 

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

Еще любопытен вот такой нюанс – попробуйте перекрасить любую желтую ячейку в какой-нибудь другой цвет заливки – у вас ничего не получится, но на самом деле заливка ложится слоями – желтая всегда верхним. Чтобы увидеть свой цвет – оставайтесь на подопытной ячейке и выполните команду: Главная – Стили – Условное форматирование – Удалить правила – Удалить правила из выбранных ячеек [Home – Styles – Conditional_Formatting – Clear_Rules – Clear_Rules_From_Selected_Cells]

 

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

Если к другому набору ячеек необходимо применить точно такое же правило (у меня это ячейки по столбцу D), то можно воспользоваться кистью Формат по образцу [Format_Painter] из Буфера обмена [Clip_board] вкладки Главная [Home]: выбираем любую ячейку диапазона, к которому правило уже применяется, нажимаем на «метелку» и выделяем теперь уже те ячейки, на которые данное правило нужно распространить (можно щелкнуть по заголовку столбца и правило будет применяться сразу ко всем ячейкам столбца, то есть и к тем записям, которые могут появится в этом списке позже)

 

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

Если правило изначально применялось к некоторому ограниченному списку, у которого стали появляться новые записи, копировать на них правило условного форматирования при помощи кисточки не советую, потому что кисть каждый раз создает копию правила, а это значит, что в случае необходимости внести изменения придется править уже не в одно правило, а в несколько. Редактирование диапазонов и других настроек (изменить условие, цвет и пр.) нужно осуществлять в Диспетчере правил: Главная – Стили – Условное форматирование – Диспетчер правил условного форматирования [Home – Styles – Conditional_Formatting – Manage_Rules]

Чтобы в этом окне увидеть все правила листа, нужно выбрать в верхнем же списке вместо Текущий фрагмент [Current_Selection] – Этот лист [This_Worksheet]. Далее выбираем нужное правило в нижней части окна и выбираем кнопку Изменить правило [Edit_Rule], если надумали внести в него изменения. А скорректировать диапазон можно при помощи поля Применяется к [Applies_to]

 

Типовые задачи

Если необходимо выделить все ячейки с ошибками – уже совершенными или потенциально возможными – может, привлечь к ним внимание, а быть может и наоборот – сделать их невидимками при помощи белого цвета шрифта (а именно так хочу поступить я) – можно использовать условное форматирование. На листе Ошибки выделите группу ячеек E7:Q87 и выполните команду Главная – Стили – Условное форматирование – Создать правило [Home – Styles – Conditional_Formatting – New_Rule] 2-ой тип правил. Настройки приведены на картинке ниже (первый список – Ошибки [Errors], а под кнопкой Формат [Format] выбран белый цвет шрифта):

Иногда может потребоваться выделить при помощи цветовой шкалы значения в от максимального к минимальному. В таком случае используйте уже готовые наборы правил: Гистограммы [Data_Bars], Цветовые шкалы [Color_Scales] или Наборы значков [Icon_Sets]. Эти инструменты определяют минимальное и максимальное значение в наборе данных и применяются заливку, шкалу или значок в зависимости от того, в какому из показателей окажется ближе каждое конкретное значение. Такие правила тоже можно редактировать через Диспетчер правил [Manager_Rules] – этой первый тип:

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

Связи между книгами. Управление внешними ссылками.
Внешние ссылки
Составление формул без переноса данных из одной книги Excel в другую возможно! Для этого нужно научиться создавать внешние ссылки (связи) и управлять ими.

Правила и закономерности, по которым работает Excel
Общие принципы, лежащие в основе любого инструмента программы Excel
Как понять программу Excel? Это несложно, список правил и настроек приведён в этом уроке.

Как разделить текст ячейки Excel по разным столбцам, используя числовую закономерность расположения знаков или символ-разделитель
Текст по столбцам
Как разделить текст по разным столбцам (колонкам), если в исходном документе разнородные фрагменты текста записаны в одной ячейке?

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

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



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