Как в excel посмотреть условное форматирование

Условное форматирование в Excel: он работает, вы отдыхаете

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

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

Как это работает? Вкратце это делается так:

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

1. Выбираете нужное правило, вводите дополнительные параметры, нажимаете ОК. Вот и все, теперь выделенным ячейкам задан условный формат, и при изменении значения в ней – формат тоже изменится.

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

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

В этой группе команд содержатся наиболее популярные правила, применяемые пользователями Microsoft Excel. Они проверяют значение в ячейке на следующие условия:

1. Больше заданного значения
2. Меньше заданного значения
3. Между заданными значениями
4. Содержит заданный текст
5. Дата соответствует заданной (или промежутку)
6. Повторяющиеся значения
7. Другие правила – откроет окно «Создание правил форматирования», о котором я расскажу чуть ниже в этой статье

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

Когда вы определились с условием для форматирования, вам предложат выбрать один из стандартных стилей форматирования, или создать свой собственный. Чтобы создать свой — выберите в списке форматов «Пользовательский формат…». Откроется уже известное нам окно «Формат ячеек», в котором и задается собственное оформление.

На картинке ниже массиву чисел задано условное форматирование «Между 50 и 70». Как видим, соответствующие ячейки выделены красной цветовой палитрой.

Правила отбора первых и последних значений

При использовании этих команд, программа в своей памяти (не на листе) сортирует массив и выводит по вашему желанию:

1. Первые N элементов (N – задаете сами)
2. Первые N процентов
3. Последние N элементов
4. Последние N процентов
5. Значения выше среднего
6. Значения ниже среднего

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

Гистограммы

Гистограммы – очень удобный способ оценить соотношение данных без построения диаграммы. Ячейки будут залиты цветом в пропорции, соответствующей числу в ней. Полностью будет залита ячейка с наибольшим числом, наименьшая заливка – в ячейке с минимальным числом. Посмотрите, как это выглядит на моем примере (столбец С):

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

По умолчанию, программа предлагает 6 цветовых тем со сплошной заливкой и еще шесть – с градиентной. Нажав кнопку «Другие правила», вы можете настроить свою уникальную цветовую схему.

Цветовые шкалы

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

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

Как и в предыдущем пункте, вы можете выбрать из стандартных наборов тем, либо нажать «Другие правила» и выбрать цвета вручную.

Наборы значков

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

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

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

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

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

Создать правило

Чтобы создать правило условного форматирования с наибольшим числом настроек – используем пункт «Создать правило».

Водите в это меню и выберите тип правила:

1. Форматировать все ячейки на основании их значений. Название пункта говорит само за себя. Отформатированы будут все ячейки выделенного диапазона в зависимости от их значения.

В раскрывающемся списке «Стиль формата» можно выбрать нужный вид оформления:

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

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

• Гистограмма. Здесь есть множество настроек, в том числе, можно выбрать свои цвета, тип и направление заливки, способ заливки отрицательных чисел. Поставьте галку «Показывать только столбец», чтобы скрыть числовые данные

• Наборы значков – выбирайте виды значков и пороги их переключения, отключайте значки для бесполезных данных и отображение числовых данных

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

1. Форматировать только первые или последние значения. Позволяет форматировать только заданное количество наименьших или наибольших значений в массиве
2. Форматировать только значения, которые находятся выше, или ниже среднего. Вычисляет среднее значение в выделенном массиве и форматирует только те значения, которые больше или меньше среднего (на ваш выбор)
3. Форматировать только уникальные или повторяющиеся значения. Думаю, название пункта отлично характеризует его функционал
4. Использовать формулу для определения форматируемых значений. Запишите логическую формулу сами, будут отформатированы лишь те значения, для которых результат формулы – истина. В моем примере отформатированы будут лишь те ячейки, для которых первое значение столбца больше 50.

Удалить правила

Чтобы удалить условное форматирование из выделенного диапазона, или со всего листа – нажмите на ленте Главная – Стили – Условное форматирование – Удалить правила , и выберите где именно нужно удалять

Управление правилами

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

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

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

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

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

1. Основы условного форматирования в Excel
2. Где находится условное форматирование в Эксель?
3. Как сделать условное форматирование в Excel?
4. Как создать правило
5. Условное форматирование по значению другой ячейки
6. Как применить несколько правил условного форматирования к одной ячейке
7. Как редактировать правило условного форматирования
8. Как копировать правило условного форматирования
9. Как удалить условное форматирование

Основы условного форматирования в Excel

Используя условное форматирование, мы можем:

• закрашивать значения цветом
• менять шрифт
• задавать формат границ

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

Где находится условное форматирование в Эксель?

Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:

Как сделать условное форматирование в Excel?

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

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

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

• В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

• Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:

• Правила выделения
• Правила отбора первых и последних значений
• Гистограммы
• Цветовые шкалы
• Наборы значков

В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:

Также, доступны следующие условия:

1. Значения больше или равны какому-либо значению;
2. Выделять текст, содержащий определенные буквы или слова;
3. Выделять цветом дубликаты;
4. Выделять определенные даты.

• Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

• Для присвоения формата вы можете использовать пред настроенные цветовые палитры, а также создать свою палитру. Для этого кликните по пункту:

• Во всплывающем окне формата укажите:

• цвет заливки
• цвет шрифта
• шрифт
• границы ячеек

• По завершении настроек нажмите кнопку “ОК”.

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

Как создать правило

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

• Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:

• Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:

• Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:

Условное форматирование по значению другой ячейки

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

Для создания условия по значению другой ячейки выполним следующие шаги:

• Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
• Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.

• Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:

• В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.

• Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:

На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

Как применить несколько правил условного форматирования к одной ячейке

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

Например, в таблице с прогнозом погоды мы хотим закрасить разными цветами показатели температуры. Условия выделения цветом: если температура выше 10 градусов – зеленым цветом, если выше 20 градусов – желтый, если выше 30 градусов – красным.

Для применения нескольких условий к одной ячейке выполним следующие действия:

• Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

• Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

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

Для редактирования присвоенного правила выполните следующие шаги:

• Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
• Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:

• После внесения изменений нажмите кнопку “ОК”.

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

Для копирования формата на другие ячейки выполним следующие действия:

• Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
• Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

Как удалить условное форматирование

Для удаления формата проделайте следующие действия:

• Выделите ячейки;
• Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:

Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!