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

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

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

Выделение ячеек

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

Пример

Сравним все числа в выбранном диапазоне и если есть повторы, закрасим блоки с ними в определенный цвет. Нажимаем «Условное форматирование» – «Правила выделения ячеек» – «Повторяющиеся значения» . В списке выбираем «повторяющиеся» и тип заливки. Теперь все повторы в столбце выделены цветом. Как видите, в примере несколько раз встречаются шестерки и восьмерки.

Теперь давайте сравним данные в первом диапазоне со вторым, и если число в первом будет меньше, выделим прямоугольничек цветом. Выбираем из списка «Меньше» . Дальше делаем относительную ссылку на второй столбец: кликаем мышкой по первому числу. Доллар перед F значит, что сравнивать будем именно с этим столбцом, но в разных ячейках. В результате, все блоки в первом столбце, где числа записаны меньше, чем во втором, выделены цветом.

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

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

Разберём следующий способ условного форматирования — Правила отбора первых и последних значений.
И далее Первые 10 элементов… .

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

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

Панели данных

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

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

Выберите диапазон для условного форматирования.

Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

Нажмите Панели данных в раскрывающемся меню. Появятся параметры градиентной заливки и заливки .

Выберите диапазон для условного форматирования.

Нажмите « Условное форматирование» в группе « Стили » на вкладке « Главная ».

Нажмите Панели данных в раскрывающемся меню. Появятся параметры градиентной заливки и заливки .

Как получить доступ к условному форматированию в Google Таблицах

Доступ к условному форматированию можно получить, перейдя в Формат → Условное форматирование.

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

Теперь при использовании условного форматирования в Google Таблицах вам доступны два варианта:

• Один цвет
• Цветовая шкала

«Один цвет» можно использовать, если вы хотите выделить все ячейки в зависимости от значения. Например, я могу использовать это, чтобы выделить все ячейки с оценками меньше 35. В этом случае, независимо от того, будет ли оценка 34 или 10, обе будут выделены одним цветом.

«Цветовая шкала» может использоваться, когда вы также хотите визуально представить разницу между значениями в ячейках. Например, оценка 34 означает более светлый оттенок красного, а 10 — более темный оттенок красного.

Метод 3: использование фильтра

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

1. Как обычно, выделяем все ячейки таблицы.
2. Во вкладке “Данные” в разделе инструментов “Сортировка и фильтр” ищем кнопку “Фильтр” (иконка напоминает воронку) и кликаем на нее.
3. После этого в строке с названиями столбцов таблицы появятся значки перевернутых треугольников (это значит, что фильтр включен). Чтобы перейти к расширенным настройкам, жмем кнопку “Дополнительно“, расположенную справа от кнопки “Фильтр“.
4. В появившемся окне с расширенными настройками:
   • как и в предыдущем способе, проверяем адрес диапазон ячеек таблицы;
   • отмечаем галочкой пункт “Только уникальные записи“;
   • жмем ОК.
5. После этого все задвоенные данные перестанут отображаться в таблицей. Чтобы вернуться в стандартный режим, достаточно снова нажать на кнопку “Фильтр” во вкладке “Данные”.

2. Проверка дат и сроков

Поскольку даты в Excel представляют собой те же числа (один день = 1), то можно легко использовать условное форматирование для проверки сроков оплат или выполнения задач.

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

1. Выделите таблицу, к которой будет применяться Условное форматирование

2. Выберите в меню Формат — Условное форматирование

3. Задайте условие 1:

• выберите — «формула»
• введите формулу =B2<$D$1

4. Нажмите «А также >>» для ввода второго условия

5. Задайте условие 2:

• выберите — «формула»
• введите формулу =(B2-$D$1) < 7 , (разница между датами меньше 7 дней)
• выберите необходимый формат

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