Как сделать условное форматирование в excel
Условное форматирование в Excel — подробно с примерами
Если необходимо наглядно представить информацию, то это очень удобно сделать с помощью условного форматирования. Пользователь, который пользуется этим сервисом, экономит огромное количество энергии и времени. Чтобы получить необходимые сведения, хватает быстрого просмотра файла.
Как осуществить условное форматирование в Эксель
Найти «Условное форматирование» можно на самой первой вкладке ленты, перейдя в раздел «Стили».
Далее нужно найти глазом значок стрелочки немного правее, передвинуть курсор на нее. Затем откроются настройки, где можно гибко настроить все необходимые параметры.
2
Далее требуется выбрать соответствующий оператор, чтобы сравнить нужную переменную с числом. Есть четыре оператора сравнения – больше, меньше, равно и между. Они перечислены в меню с правилами.
Далее мы указали ряд чисел в диапазоне A1:A11.
3
Осуществим форматирование подходящего набора данных. Затем открываем меню настройки «Условного форматирования» и выставляем требуемые критерии выделения данных. В случае с нами мы для примера выберем критерий «Больше».
4
Откроется окно с набором параметров. В левой части окна есть поле, в котором нужно указать число 15. В правой же части указывается способ выделения информации при условии, что она соответствует заданному ранее критерию. Результат будет отображен сразу.
5
Далее завершаем настройку с помощью кнопки ОК.
Для чего нужно условное форматирование
В определенной степени можно сказать, что оно является инструментом, после изучения которого работа с Excel никогда не будет прежней. Все потому, что он помогает значительно упростить жизнь. Вместо того, чтобы каждый раз вручную задавать форматирование ячейки, подходящей под определенное условие, так еще и самому пытаться проверить ее на предмет соответствия этому критерию, достаточно просто один раз задать настройки, а дальше Excel все сделает сам.
Например, можно сделать так, чтобы все ячейки, которые содержат число, выше 100, окрашивались красный цвет. Или же определить, сколько дней осталось до следующего платежа, после чего окрасить в зеленый цвет те ячейки, в которых срок истекает еще достаточно нескоро.
Давайте приведем такой пример.
6
В этой таблице указываются товарные запасы, которые имеются на складе. В ней есть такие колонки. как товар, средние продажи (измеряются в штуках за неделю), остаток и на сколько недель этого товара осталось.
Далее задача менеджера по закупкам – определить те позиции, пополнение которых необходимо. Чтобы это сделать, необходимо посмотреть на четвертую слева колонку, где записывается запас товаров по неделям.
Предположим, критерием для определения повода для паники служит товарный запас в размере меньшем, чем 3 недели. Это говорит о том, что нам нужно готовить заказ. Если же запас товаров составляет меньше двух недель, то это говорит о необходимости срочно размещать заказ. Если в таблице приводится огромное количество позиций, то вручную проверять, сколько недель осталось, довольно проблематично. Даже если воспользоваться поиском. А теперь давайте посмотрим, как выглядит таблица, которая подсвечивает красным цветом дефицитные товары.
7
И ведь действительно, так значительно проще сориентироваться.
Правда, данный пример учебный, который несколько упрощен по сравнению с реальной картиной. И сэкономленные секунды и минуты при регулярном использовании подобных таблиц превращаются в часы. Теперь просто достаточно посмотреть на таблицу, чтобы понять, какие товары дефицитные, а не тратить часы на то, чтобы анализировать каждую ячейку (если подобных товарных позиций тысячи).
Если есть «желтые» товары, то необходимо начать их закупку. Если же соответствующая позиция красная, то нужно это делать немедленно.
По значению другой ячейки
Теперь давайте разберем следующий практический пример.
Предположим, у нас есть такая таблица, и перед нами появилась задача выделить строки, содержащие определенные значения.
8
Итак, если проект пока что исполняется (то есть, еще не был доведен до конца, поэтому отмечен буквой «Р»), то нам необходимо его фон сделать красным. Завершенные же проекты помечаются зеленым.
Наша последовательность действий в этой ситуации будет следующей:
Далее заполняем строки так, как показано на этой картинке.
9
Важно! На строки обязательно давать абсолютную ссылку. Если же мы ссылаемся на ячейку, то в таком случае она является смешанной (с закреплением колонки).
Точно так же создается правило для тех рабочих проектов, которые не были завершены к текущему моменту.
Вот так выглядят наши критерии.
10
Ну и наконец, такая база данных у нас получается в итоге.
11
Несколько условий
Возьмем для наглядности диапазон А1:А11 из таблицы, чтобы продемонстрировать, как можно на реальном практическом примере использовать несколько условий для определения форматирования ячейки.
Сами условия такие: если число в ячейке превышает значение 6, то оно выделяется красным цветом. Если оно зеленое, то цвет тогда зеленый. Ну и наконец, наибольшие числа, больше 20, будут выделяться желтым.
Есть несколько способов, как осуществляется условное форматирование по нескольким правилам.
После этого Далее выбираем пункт «Использовать формулу…» (выделен на рисунке красной рамочкой) и задаем первое условие. После этого кликаем на ОК. А потом цикл повторяется для последующих условий аналогично описанному выше, но при этом используется формула.
14
Важно учесть такой момент. В нашем примере некоторые ячейки соответствуют сразу нескольким критериям одновременно. Данный конфликт Эксель разрешает следующим образом: то правило, которое выше, применяется в первую очередь.
И вот скриншот для лучшего понимания.
15
Например, у нас есть число 24. Оно одновременно соответствует всем трем условиям. В таком случае будет заливка, соответствующая первому условию, несмотря на то, что оно больше подпадает под третье. Поэтому если важно, чтобы числа, выше 20 были залиты именно желтым цветом, нужно третье условие ставить на первое место.
Условное форматирование даты
А теперь посмотрим, как условное форматирование работает с датами. У нас есть такой классный диапазон.
16
В этом случае нужно задавать такое правило условного форматирования, как «Дата».
17
После этого открывается диалоговое окно с целым набором условий. Ознакомиться с ними подробно вы можете на этом скриншоте (они перечислены в левой части экрана в виде списка).
18
Нам осталось только выбрать подходящее и нажать на клавишу «ОК».
19
Видим, что у нас красным цветом были выделены те даты, которые относятся к последней неделе на момент отрисовки этого диапазона.
С использованием формул
Набор стандартных правил условного форматирования довольно большой. Но ситуации бывают разные, и стандартного перечня может не хватать. В таком случае можно создать собственное правило форматирование, используя формулу.
Для этого необходимо нажать кнопку «Создать правило» в меню условного форматирования, а потом выбрать пункт, отображенный на скриншоте.
20
Строки (по значению ячейки)
Предположим, нам нужно выделить ту строку, которая содержит ячейку с определенным значением. В этом случае нам нужно выполнить ту же последовательность действий, что и выше, но применительно к строкам. То есть, можно самому выбирать, на какой диапазон должно распространяться условное форматирование.
Как создать правило
Чтобы создать правило, необходимо воспользоваться соответствующей опцией в разделе «Условное форматирование». Давайте рассмотрим конкретную последовательность действий.
Сначала необходимо найти на ленте на вкладке «Главная» найти пункт «Условное форматирование». Его можно узнать по характерным разноцветным ячейкам разного размера со стоящей рядом картинкой с перечеркнутым знаком равно.
21
Там есть кнопка «Создать правило». Также можно нажать кнопку «Управление правилами», после чего откроется диалоговое окно, через которое также можно создать правило, нажав соответствующую кнопку в левом верхнем углу.
22
Также можно ознакомиться в этом окне с теми правилами, которые уже применены.
23
Правила выделения ячеек
Хорошо, мы столько говорим о том, как создавать правила, но что это такое? Под правилом выделения ячеек подразумеваются признаки, которые берутся в учет программой для принятия решения, каким образом осуществлять форматирование ячейки, которые подходят под них. Например, условия могут быть больше определенного числа, меньше, формула и так далее. Их целый набор. С ними можно ознакомиться самостоятельно и потренироваться в их применении «в песочнице».
Форматирование всех ячеек на основании их значений
Человек может самостоятельно задавать диапазон: целая строчка, весь документ или отдельная ячейка. Ориентироваться программа может только на значение одной ячейки. Это добавляет гибкости в процесс.
Форматирование только уникальных или повторяющихся ячеек
Существует большое количество правил, непосредственно зашитых в программу. Ознакомиться с ними вы можете в меню «Условное форматирование». В частности, чтобы форматирование распространялось исключительно на повторяющиеся значения или только на уникальные, существует специальная опция – «Повторяющиеся значения».
Если выбрать этот пункт, то открывается окно с настройками, где возможен выбор и обратного варианта – выделять лишь уникальные значения.
Форматирование значений в диапазоне ниже и выше среднего
Чтобы форматировать значения ниже или выше среднего, также существует специальная опция в том же меню. Но подменю нужно выбрать другое – «Правила отбора первых и последних значений».
Форматирование только первых и последних значений
В том же подменю есть возможность выделять особым цветом, шрифтом и другими способами форматирования лишь первые и последние значения. По стандарту есть вариант выделять первые и последние десять элементов, а также 10% от общего количества ячеек, входящих в этот диапазон. Но пользователь может самостоятельно определять, сколько ячеек ему необходимо выделить.
Форматирование только ячеек с определенным содержимым
Чтобы форматировать только ячейки с определенным содержимым, необходимо выбрать правило форматирование «Равно» или «Текст содержит». Отличие между ними в том, что в первом случае строка должна совпадать с критерием полностью, а во втором – лишь частично.
Как видим, условное форматирование – это многофункциональная возможность программы Excel, дающая огромное количество преимуществ человеку, который ее освоил. Знаем, что на первый взгляд все это может показаться сложным. Но на самом деле, руки сами тянутся к ней, как только появляется выделить определенный кусок текста красным цветом (или отформатировать его любым другим способом), основываясь на его содержимом или в соответствии с другим критерием. Эта функция входит в базовый комплект знаний Excel, без нее невозможна даже любительская работа с электронными таблицами и базами данных.
Обучение условному форматированию в Excel с примерами
Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.
Как сделать условное форматирование в Excel
Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».
При нажатии на стрелочку справа открывается меню для условий форматирования.
Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Условное форматирование по значению другой ячейки
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Условное форматирование – несколько условий
Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Условное форматирование даты в Excel
Выделяем диапазон с датами.
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Условное форматирование в Excel с использованием формул
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Условное форматирование строки по значению ячейки
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Порядок заполнения условий для форматирования «завершенных проектов»:
Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Условное форматирование в EXCEL
history 26 октября 2012 г.
Условное форматирование – один из самых полезных инструментов EXCEL. Умение им пользоваться может сэкономить пользователю много времени и сил.
Начнем изучение Условного форматирования с проверки числовых значений на больше /меньше /равно /между в сравнении с числовыми константами.
Рассмотрим несколько задач:
СРАВНЕНИЕ С ПОСТОЯННЫМ ЗНАЧЕНИЕМ (КОНСТАНТОЙ)
СРАВНЕНИЕ СО ЗНАЧЕНИЕМ В ЯЧЕЙКЕ (АБСОЛЮТНАЯ ССЫЛКА)
Чуть усложним предыдущую задачу: вместо ввода в качестве критерия непосредственно значения (4), введем ссылку на ячейку, в которой содержится значение 4.
ПОПАРНОЕ СРАВНЕНИЕ СТРОК/ СТОЛБЦОВ (ОТНОСИТЕЛЬНЫЕ ССЫЛКИ)
Теперь будем производить попарное сравнение значений в строках 1 и 2.
Теперь каждое значение в строке 1 будет сравниваться с соответствующим ему значением из строки 2 в том же столбце! Выделены будут значения 1 и 5, т.к. они меньше соответственно 2 и 6, расположенных в строке 2.
Внимание! В случае использования относительных ссылок в правилах Условного форматирования необходимо следить, какая ячейка является активной в момент вызова инструмента Условное форматирование .
Примечание-отступление : О важности фиксирования активной ячейки при создании правил Условного форматирования с относительными ссылками
Теперь посмотрим как это влияет на правило условного форматирования с относительной ссылкой.
УСЛОВНОЕ ФОРМАТИРОВАНИЕ и ФОРМАТ ЯЧЕЕК
ОТЛАДКА ПРАВИЛ УСЛОВНОГО ФОРМАТИРОВАНИЯ
Чтобы проверить правильно ли выполняется правила Условного форматирования, скопируйте формулу из правила в любую пустую ячейку (например, в ячейку справа от ячейки с Условным форматированием). Если формула вернет ИСТИНА, то правило сработало, если ЛОЖЬ, то условие не выполнено и форматирование ячейки не должно быть изменено.
Вернемся к задаче 3 (см. выше раздел об относительных ссылках). В строке 4 напишем формулу из правила условного форматирования =A1
ИСПОЛЬЗОВАНИЕ В ПРАВИЛАХ ССЫЛОК НА ДРУГИЕ ЛИСТЫ
ПОИСК ЯЧЕЕК С УСЛОВНЫМ ФОРМАТИРОВАНИЕМ
Будут выделены все ячейки для которых заданы правила Условного форматирования.
ДРУГИЕ ПРЕДОПРЕДЕЛЕННЫЕ ПРАВИЛА
В меню Главная/ Стили/ Условное форматирование/ Правила выделения ячеек разработчиками EXCEL созданы разнообразные правила форматирования.
Чтобы заново не изобретать велосипед, посмотрим на некоторые их них внимательнее.
Теперь посмотрим на только что созданное правило через меню Главная/ Стили/ Условное форматирование/ Управление правилами.
Советую также обратить внимание на следующие правила из меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений.
Слова «Последние 3 значения» означают 3 наименьших значения. Если в списке есть повторы, то будут выделены все соответствующие повторы. Например, в нашем случае 3-м наименьшим является третье сверху значение 10. Т.к. в списке есть еще повторы 10 (их всего 6), то будут выделены и они.
К сожалению, в правило нельзя ввести ссылку на ячейку, содержащую количество значений, можно ввести только значение от 1 до 1000.
В этом правиле задается процент наименьших значений от общего количества значений в списке. Например, задав 20% последних, будет выделено 20% наименьших значений.
Задавая проценты от 1 до 33% получим, что выделение не изменится. Почему? Задав, например, 33%, получим, что необходимо выделить 6,93 значения. Т.к. можно выделить только целое количество значений, Условное форматирование округляет до целого, отбрасывая дробную часть. А вот при 34% уже нужно выделить 7,14 значений, т.е. 7, а с учетом повторов следующего за 10-ю значения 11, будет выделено 6+3=9 значений.
ПРАВИЛА С ИСПОЛЬЗОВАНИЕМ ФОРМУЛ
Предположим, что необходимо выделять ячейки, содержащие ошибочные значения:
Того же результата можно добиться по другому: