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





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

Конспекты

Курсы

Практикум


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

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

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

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

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

 

Как быстро убрать ошибки

Начнем с первого листа Ошибки. Вам попался в руки файл (сделали сами или получили от коллег по почте) и в находящейся в нем таблице среди значений есть также и значения ошибок (очевидно, что значения высчитаны по некоторой формуле), от которых конечно же нужно избавиться. Напомню: вкладка Разметка страницы [Page_Layout] – группа Параметры страницы [Page_Setup] – кнопка вызова одноименного окна настроек в нижнем правом углу группы (серая диагональная стрелочка) и далее в окне Параметры страницы [Page_Setup] переходим на вкладку Лист [Sheet] и в правой центральной части находим список команд Ошибки ячеек как: [Cell error as:], из которого нужно выбрать <нет> [<blank>]. В электронной версии документа значения ошибок останутся, а на печать выводится не будут. Но если нужно поработать с документом в электронном виде, и при этом не хотелось бы утяжелять формулы (вкладывать их в ЕСЛИОШИБКА [IFERROR]) – тогда условное форматирование в помощь

Выделяем диапазон данных, содержащий ошибки, и выполняем команду: Главная – Стили – Условное форматирование – Создать правило [HomeStylesConditional_FormattingNew_Rule], выбираем второй тип Форматировать только ячейки, которые содержат [Format_only_cells_that_contain], и далее в нижней части окна в первом же списке вместо Значение ячейки [Cell_value] выбираем Ошибки [Errors],после чего не забываем под кнопкой Формат [Format] выбрать оформление ячейки с ошибкой: если хотим спрятать их с глаз долой, то устанавливаем белый цвет шрифта (я выбрала именно такую настройку), если же наоборот нужно выделить значения ошибок, чтобы они сразу обращали на себя внимание, тогда выбираем яркий цвет шрифта и заливки:

 

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

Часто требуется сравнить два идентичных набора данных и значения, которые увеличились, выделить одним цветом, а которые стали меньше – другим. В моем примере (лист Цена больше-меньше) будем составлять прогноз цен на следующий год и сравнивать их с текущими ценами: те плановые цены, которые будут выше текущих, хочу выделить синим цветом, а если же уменьшились – красным

Итак, сначала выделяем объект, потом применяем команду: выделяем набор ячеек C4:C8 и выполняем команду Главная – Стили – Условное форматирование – Создать правило [HomeStylesConditional_FormattingNew_Rule], выбираем второй тип Форматировать только ячейки, которые содержат [Format_only_cells_that_contain], и далее в нижней части окна в первом списке оставляем Значение ячейки [Cell_value]  которое сравниваем при помощи команды второго списка больше чем [greater_than] со значением из предыдущего столбца – ссылку на ячейку, с которой осуществляем сравнение, нужно ввести в последнем окне настроек – поле ввода в правой части окна. При этом не забудьте убрать все знаки долларов (можно нажать клавишу F4 несколько раз – это проще, потому как стрелками двигать курсор в этом окошке не получится). Осталось выбрать под кнопкой Формат [Format] синий цвет шрифта (или заливки) и можно нажить ОК

Обратите внимание – ссылку даем не на весь столбец предыдущих цен, а только на одну первую ячейку (потому что она соответствует активной ячейке выделенного диапазона); после нажатия на ОК это правило проверки скопируется на все ячейки выделенного диапазона

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

Вот такой результат должен получиться – к одному диапазону ячеек применяется два правила условного форматирования:

 

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

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

  1. значения цен больше либо равных 30 ден.ед. выделим оранжевой заливкой

  1. значения цен больше либо равных 20 ден.ед. выделим зеленой заливкой

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

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

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

 

Форматирование целой строки по значению одной ячейки

До сих пор мы работали с одной ячейкой, но как выделить цветом всю строку?

Задача аналогична предыдущей, только на этот раз будем выделять всю строку с ценой больше 30 ден.ед. оранжевым цветом, а для этого выделяем весь диапазон данных, среди которых предполагается найти такие строки и выделить, и выполняем команду Главная – Стили – Условное форматирование – Создать правило [HomeStylesConditional_FormattingNew_Rule], выбираем на этот раз шестой тип Использовать формулу для определения форматируемых ячеек [Use_a_formula…]

Шестой тип правила нужно применять в том случае, если нужно оформить ячейку в зависимости от данных совсем другой (если проверяемая и форматируемая ячейка одна и та же, то стоит поискать нужную ситуацию во втором типе правил) – в моем примере буду делать заливку ячеек по столбцам A, B, C в зависимости от данных ячейки по столбцу D. В нижней части окна нужно написать условие проверки (как для функции ЕСЛИ [IF]) для активной ячейки (после нажатия на ОК оно скопируется на все ячейки выделенного диапазона): =$D4>=30. Обратите внимание, что один знак доллара я оставила – он должен располагаться перед тем элементом, который является константой. В нашем случае из всех форматируемых ячеек ориентируемся только на ячейки по столбцу D, поэтому перед буквой столбца знак доллара оставляем, а вот в каждой отдельной строке проверяемая ячейка также должна оказаться в этой же строке (то есть «спускаться» вместе с форматируемыми строчками), поэтому никакого знака доллара перед номером строки не требуется

Аналогичным же образом можно создать второе правило (закрасить зеленым цветом строчки с ценами больше 20) и указать порядок выполнения правил при помощи стрелок в Диспетчере правил условного форматирования (разобрано в предыдущем вопросе этого урока). Итоговое решение выглядит следующим образом:

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

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

Функции для вычислений по дням
Функции даты и времени
Как посчитать календарные или рабочие дни? Можно ли прибавить к дате несколько месяцев? Как это делать правильно, используя функции РАБДЕНЬ, ЧИСТРАБДНИ, ДАТАМЕС, СЕГОДНЯ и пр.

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

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

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

Заказчики

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

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

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

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



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