Что такое условное форматирование в Эксель и как его использовать

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

Что такое условное форматирование

Условное форматирование в Excel 2003, 2007, 2010, 2013 – это инструмент, созданный для визуализации данных таблицы. Его удобно использовать, чтобы выделить определенные цифры, которые будут сразу бросаться в глаза. Также этот инструмент имеет набор условий, которые уже встроены в программу. Помимо этого, пользователь может самостоятельно задать параметры для визуализации информации Excel.

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

Чтобы использовать данный инструмент в Эксель, необходимо сделать следующее:

  1. Кнопка для активации инструмента находится на верхней панели во вкладке стилей.image
  2. При клике на стрелку появится выпадающее меню, в котором можно выбрать уже существующие функции или создать собственную.image
  3. Для примера можно сравнить числовые значения ячеек с константой. Такой тип функции наиболее востребован и включен в пункт выделения значений. Необходимо ввести некоторые числа в столбец.
  4. Для визуализации требуется выделить диапазон и использовать его. К примеру, можно выбрать условие «больше».
  5. Откроется окно для ввода параметра, по которому будут отобраны числа. В этом окошке необходимо вставить число, цифры больше которого будут выделены цветом, его также можно задать.

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

Варианты

К вариантам визуализации относятся следующие решения:

  • гистограммы;
  • цветовая шкала;
  • значки.

Создание правил

В программе есть определенный набор условий, по которому можно выделять числа:

  1. Выделение (больше, меньше, равно, между, повторяющееся).
  2. Отбор первых и последних.

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

  Excel — подготовка к печати и варианты печати документа

При клике на подпункт «Создать правило» появится окно, в котором можно выбрать один из типовых вариантов.

А также в этом окошке возможно задать и другие функции, как, например, стиль формата (знаки или цвет) и отбор.

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

В окне будет показано, какое правило применено к соответствующим ячейкам. Интересно то, что, если задать противоречащие друг другу условия, будет использовано только последнее.

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

Форматирование по значению другой ячейки

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

  1. Выделить значение и задать правило «меньше».
  2. В появившемся окошке необходимо вставить ссылку на предыдущую ячейку, выбрать цвет и кликнуть на ОК.
  3. Нужно опять выделить число с заданным условием и перейти к его управлению. Нужно его изменить.
  4. В появившемся окне необходимо удалить знак доллара.
  5. Остается только присвоить функцию ко всей таблице с помощью инструмента «Формат по образцу».

Использование нескольких правил

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

  1. Выделить таблицу и применить условие «Больше» и задать каждому значению соответствующие параметры. Т.е. необходимо задать три параметра (для 10, 20 и 30 градусов). Но несмотря на то, что было задано сразу три правила для всей таблицы, значения будут закрашены зеленым, поскольку появляется противоречие, все числа всегда будут больше 10.
  2. Требуется кликнуть по любому числу и опять перейти к меню условного форматирования. Но на этот раз нужно выбрать управление. Появится окно со всеми ранее заданными параметрами. Их нужно поставить в убывающем порядке и на первых двух поставить галочки «остановить, если истина». Это позволит не применять их, если первое верно.
  3. Остается сохранить изменения и применить их. После этого таблица окрасится согласно трем условиям.

  Способы сортировки данных в Microsoft Excel

Формат даты

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

Это откроет окно в котором можно выбрать день недели (сегодня, завтра, через 7 дней и т.д.) и цвет заливки определенной ячейки.

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

Удаление форматирования

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

  1. Выделить ячейки с форматированием.
  2. Кликнуть по кнопке условного формирования.
  3. Выбрать удаление либо с выделенной части, либо со всего документа.

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

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

Достаточно часто эта функция не работает по простой причине — в книге стоит запрет на изменения. Также возможен вариант, что документ доступен только для чтения. Это можно решить путем создания нового документа и копирования информации, либо пересохранением текущего с изменением расширения.

Читайте также:

Условное форматирование в новых версиях Excel мы рассматривали в видео уроке. Стандартные приемы очень удобны и наглядны. Но иногда требуется применять формат ячеек, в зависимости от каких-нибудь условий в соседних ячейках.

К нам поступил вопрос:

Здравствуйте, а как сделать условное форматирование одного столбца относительно другого? при этом тот который задает форматирование имеет 3 текстовых признака, то есть главный столбец с кодами должен окрашиваться в соответствии с требуемым текстовым признаком?

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

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

При соблюдении данных условий, нам необходимо закрасить ячейку в желтый цвет. Для начала нам необходимо выделить все фамилии, далее выбрать пункт «Условное форматирование», «Создать правило», из типа правил выбрать «Использовать формулу для определения форматируемых ячеек» и нажать «Ок».

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

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

Мы прописываем формулу:

 =И(B2>75;C2="Да") 

И — это означает, что мы проверяем два условия и они должны обе выполняться. Если бы нужно было, чтобы выполнялось одно из условий (либо результат больше 75 либо сотрудник — льготник), то нужно было бы использовать функцию ИЛИ, еще проще если условие одно.

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

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

Не забудьте выбрать формат, в который необходимо закрашивать наши ячейки. Нажимаем «Ок» и проверяем.

Были закрашены Петров и Михайлов, у обоих результат выше 75 и они являются льготниками, что нам и требуется.

Надеюсь, что ответили на ваш вопрос по условному форматирования. Ставьте лайки и подписывайтесь на нашу группу в ВК.

Поделиться:

Правила в условном форматировании. Написание формул

Categories: Оформление/ Работа с ячейками

На днях столкнулся с проблемой. Человек хорошо знал возможности условного форматирования, но не догадывался, что можно задать формулу, в зависимости от которой будут меняться цвета. А ведь это удобно. Задал правила в условном форматировании, поставил в ячейке нужное число или текст, а строка подсвечивается по формуле. Визуально с файлом сразу легче работать. Хотел дать свою статью прочитать, а оказывается по этой теме статьи и нет. Исправляюсь.

Сначала «два слова» о том, что такое условное форматирование. Это функция Excel, позволяющая выделять ячейки цветом или форматированием текста по условиям. Очень подробно об этом я написал в этой статье. Как использовать формулы сложнее, напишу ниже.

Правила в условном форматировании

Начнем с простой формулы. Перейдем в Условное форматирование — Управление правилами

В открывшемся окне жмем Создать правило, затем находим самый нижний пункт Использовать формулу для…

В окне ниже (Форматировать значения, для которых…) уже записываем формулу. После задаем нужный формат, я выбрал зеленый фон.

Окно изменения правил в условном форматировании

Жмем ОК и возвращаемся в Диспетчер правил. Здесь уже мы видим список созданных условий:

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

Нажимаем Применить. Так можно проверить, что нужные ячейки подсветились зеленым.

Кстати, формулу можно написать и проверить в Excel заранее. Например, так:

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

Зачастую необходимо изменить форматирование по правилу во всей строке таблицы.

Для этого в диспетчере правил нужно выбрать нужный диапазон в столбце «Применяется к:»

Обратите внимание, что если форматирование распространяется от сроки к строке, то перед номером строки в формуле (в нашем случае ЕСЛИ) не ставим $.

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

Набор основных формул, которые я использую

=ОСТАТ(СТРОКА();2) - самое популярное, наверно. Зебра для выделения строк через одну  =$A1="" - подкрашивание пустых ячеек  =ЕЧИСЛО(A1) - изменение формата числовых ячеек  =ЕТЕКСТ(A1) - изменение формата текстовых ячеек  =ЕОШИБКА(A1) - изменение формата ячеек с ошибкой в расчетах.  =ДЕНЬНЕД(A1;2)>5 - выделяем выходные дни

Важно добавить

— Как вы заметили по моим правилам, я не использую абсолютные ссылки на диапазон при условии. Если вы делаете условие в диапазоне построчно, то номера строк нельзя делать абсолютными, т.е. ставить знак $

— В написании правил нельзя ссылаться на другие листы.

— Не забудьте, что если работаете с датами и временем, то они воспринимаются Excel’ем как число.

Файл приложил.

Поделитесь нашей статьей в ваших соцсетях:

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

Как это сделать? В Excel имеется набор инструментов, который позволит это выполнить быстро – буквально за пару-тройку щелчков мыши. Самый простой из таких инструментов – условное форматирование. Давайте разберем ситуации, в которых пригодятся условные форматы.

1. Выделение ячеек с определенными значениями

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

Сумма более 150 тыс.

Cлово «материал»

Повторения

Если начать искать нужные ячейки, выделяя их мышкой, то потратим время и, скорее всего, допустим ошибки. Да и в работе обычно используются таблицы гораздо больших размеров. Для решения такой задачи в Условных форматах есть специальная опция – Правила выделения ячеек. Чтобы настроить форматирование ячеек, перейдите в Excel на вкладку меню Главная → выберите Условное форматирование.

Пример 1. Найти значения больше заданного числа. Итак, чтобы найти все ячейки с цифрами более 150 тысяч, достаточно:

  • выделить столбец с данными;
  • перейти в меню Главная → Условное форматирование → Правила выделения ячеек → Больше…
  • в открывшемся окне ввести число, данные больше которого хотим увидеть. И указать, каким цветом должны быть выделены ячейки.

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

  • выделить столбец с данными;
  • перейти в меню Главная → Условное форматирование → Правила выделения ячеек → Текст содержит
  • в появившемся окне указать искомый текст (слово «материал») и, если нужно, задать формат.

Пример 3. Найти повторения. Такой же принцип и с повторяющимися значениями:

  • выделить ячейки, где нужно найти повторения;
  • перейти в меню Главная → Условное форматирование → Правила выделения ячеек → Повторяющиеся значения
  • выбрать формат.

В итоге за несколько щелчков мышкой получаем уйму сэкономленного времени и результат – маркированные данные!

Сумма более 150 тыс.

Cлово «материал»

Повторения

2. Анализ числовых данных

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

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

  • выделите ячейки с цифрами, кроме строки итогов (чтобы исходные данные на фоне итогов не смотрелись заниженными);
  • перейдите в меню Главная → Условное форматирование → Цветовые шкалы и выберите правило форматирования, например «зеленый-белый-красный».

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

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

Еще в меню или в окне «Диспетчер правил условного форматирования» можно добавить новые правила или очистить условные форматы с помощью кнопки Удалить правила.

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

  • выделить столбец с данными по полугодию;
  • перейти на вкладку Главная → Условное форматирование → Гистограммы, выберите любую понравившуюся;
  • если нужно, поменять цвет гистограмм в окне «Диспетчер правил условного форматирования».

Пример 3. Анализ данных с помощью наборов значков. Теперь строка итогов. Ее мы разметим с помощью значков. Путь тот же:

  • выделяем строку итогов по месяцам (без полугодия);
  • меню Главная → Условное форматирование → Наборы значков → любой подходящий;
  • чтобы не перегружать таблицу значками, в меню Главная → Условное форматирование → Управление правилами задаем, чтобы в строке выводился только один значок рядом с наибольшими значениями.

В результате получится таблица, как на рисунке ниже:

Инструменты быстрого анализа

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

3. Визуальный анализ в таблицах

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

Пример 1. Анализ структуры показателей.

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

Используем условные форматы, как в предыдущих примерах:

  • выделить столбец «% к итогу» по количеству заказов, без итоговой строки;
  • меню Главная → Условное форматирование → Гистограммы, выберите любую понравившуюся;
  • в окне настройки правил форматирования выберите не слишком яркий цвет и поставьте галочку «Показывать только столбец» — после этого в столбце останутся только гистограммы, а цифры «исчезнут».

Таким же образом настраиваются гистограммы для каждого столбца «% к итогу». Готово! Структура показателей выглядит понятнее, чем в предыдущем варианте таблицы.

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

Пример 2. Анализ рейтингов.

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

Такое форматирование настраивается с помощью двухцветной шкалы, как на рисунке:

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

Так, используя простые инструменты Excel, за 3–5 кликов мышкой можно проанализировать большие объемы информации и сделать отчеты интуитивно понятными.

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

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

Выберите вкладку «Главная» »Группа стилей» Раскрывающееся меню «Условное форматирование» .

Различные параметры условного форматирования

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

Предположим, вы хотите найти ячейку с суммой 0 и пометить их как красный. Выбрать диапазон ячейки »Главная вкладка» Условное форматирование DropDown »Выделить правила ячейки» Equal To.

image

После нажатия ОК ячейки со значением ноль помечаются как красные.

image

  • Top / Bottom Rules — открывает меню продолжения с различными опциями для определения правил форматирования, которые выделяют верхние и нижние значения, проценты, а также значения выше и ниже среднего при выборе ячейки.

Предположим, что вы хотите выделить 10% верхних строк, вы можете сделать это с помощью этих правил Top / Bottom.

image

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

С этим условным форматированием данных бары появятся в каждой ячейке.

image

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

Смотрите скриншот ниже с применением цветовых шкал с условным форматированием.

image

  • Наборы значков — открывает палитру с различными наборами значков, которые можно применить к выбору ячеек, чтобы указать их значения относительно друг друга, щелкнув набор значков.

Смотрите скриншот ниже с примененным условным форматированием Icon Sets.

image

  • Новое правило — открывается диалоговое окно «Новое правило форматирования», где вы определяете пользовательское правило условного форматирования, которое будет применяться к выбору ячейки.

  • Очистить правила — открывается меню продолжения, в котором можно удалить правила условного форматирования для выбора ячейки, выбрав параметр «Выбранные ячейки», для всей рабочей таблицы, щелкнув параметр «Весь лист», или только для текущей таблицы данных, щелкнув эту кнопку. Настольный вариант.

  • Управление правилами. Открывается диалоговое окно «Диспетчер правил условного форматирования», в котором можно редактировать и удалять определенные правила, а также настраивать их приоритетность, перемещая их вверх или вниз в списке правил.

Оцените статью
Рейтинг автора
5
Материал подготовил
Илья Коршунов
Наш эксперт
Написано статей
134
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий