Условное форматирование что это
Условное форматирование в MS Excel с примерами
Условное форматирование в Эксель – этот тот инструмент, который делит работу на до и после его изучения. Суть в том, что при наступлении некоторого условия ячейки форматируются автоматически. Например, если число превышает значение 100, шрифт становится красным полужирным курсивом; когда до наступления платежа остается 2 дня, ячейка с датой подсвечивается желтым цветом; перевыполнение плана продаж на 5% и более окрашивается в зеленый цвет и т.д. и т.п.
Вот упрощенный, но реальный пример. Есть отчет о товарных запасах.
Менеджер по закупкам отслеживает те позиции, которые требуют пополнения. Для этого он смотрит в последнюю колонку, где рассчитывается товарный запас (ТЗ) в неделях. Если ТЗ меньше, скажем, 3-х, то нужно готовить заказ. Если меньше 2-х, то возникает риск дефицита и заказ нужно размещать срочно. Если в таблице десятки позиций, то просмотр каждой строки займет довольно много времени. А теперь та же таблица, где после применения условного форматирования значения ниже пороговых подсвечиваются некоторым цветом.
Согласитесь, так гораздо нагляднее. В реальности условия сложнее, а данные постоянно меняются. Поэтому эффект от применения условного форматирования – это многочасовая экономия времени ежедневно! Теперь для оценки запасов достаточно взглянуть на таблицу, а не анализировать каждую ячейку. Много желтого – пора действовать, много красного – ситуация критическая!
Для настройки условного формата следует воспользоваться соответствующей командой на вкладке Главная.
При ее нажатии открывается меню.
Верхние 5 команд – это готовые сценарии для быстрого условного форматирования. Чтобы ими воспользоваться достаточно выбрать нужный вариант и сделать минимальные настройки. Эти сценарии мы рассмотрим ниже.
В нижней части еще три команды, с помощью которых происходит ручное создание, удаление и управление правилами условного форматирования. О них также поговорим.
Все сценарии разбиты на категории:
– Правило выделения ячеек
– Правило отбора первых и последних значений
Правила выделения ячеек применяют для ячеек, которые сравниваются с определенным значением. Возможны различные варианты, которые показаны на рисунке ниже.
Больше… Если значение ячейки, к которой применяется правило выделения, больше указанного значения, то в силу вступает заданный формат.
Пороговое значение указывается в левой части окна (сейчас там 80), готовый формат выбирается из выпадающего списка справа. Можно, конечно, и самому задать. Диалоговые окна для других условий похожи, поэтому ниже приводятся только те, которые могут вызвать затруднения.
Меньше… Форматируются ячейки, у которых значение меньше заданного порога.
Между… Форматирование наступает, если содержимое ячейки находится внутри заданных границ.
Равно… если значение или текст в ячейке совпадает с условием.
Текст содержит… Если совпадает только часть текста (слово, код, комбинация символов и т.д).
Дата… Возможность форматировать периоды отстоящие от текущей даты, например, сегодня, вчера, последние 7 дней, следующий месяц и др. Условное форматирование даты полезно при контроле платежей, отгрузок и т.п.
Повторяющиеся значения… выделяются ячейки с одинаковым содержимым. Отличный способ найти дубликаты (повторы). В настройках можно выбрать и обратный вариант – выделить только уникальные значения.
Правила отбора первых и последних значений выделяют наибольшие или наименьшие значения. Помогают анализировать данные, показывая приоритеты и «слабые места».
Первые 10 элементов… Выделяются первые топ–10 ячеек. Количество регулируется в диалоговом окне (можно сделать топ-5, топ-20 и др.).
Первые 10%… Выделяются 10% наибольших значений. Долю можно изменить.
Последние 10 элементов… Аналогично с первым пунктом, только форматируются наименьшие значения.
Последние 10%… Наименьшие 10% или другая доля от всех элементов.
Выше среднего… Форматируются все значения, которые больше средней арифметической.
Ниже среднего… Ниже средней арифметической.
Гистограммы позволяют в каждую ячейку с числом добавить столбец линейной гистограммы, размер которой определяется относительно максимального значения в выделенном диапазоне.
Помогает визуализировать небольшой набор данных без использования отдельных диаграмм. После применения выглядит примерно так.
Цветные шкалы также автоматически определяют максимальное и минимальное значение в диапазоне и форматирует каждую ячейку по цвету, который соответствует значению, изображая что-то вроде тепловой карты.
Например, наибольшее значения – это красное, наименьшее – зеленое, а остальные ячейки – это плавный переход от одного цвета к другому через промежуточный белый.
Набор значков – эффектный, но не очень гибкий способ визуализации. Каждой ячейке присваивается свой значок в соответствии с выбранным стилем.
В ячейках Excel выглядит так.
Все картинки выше были сделаны с помощью стилей по умолчанию. Чтобы внести изменения, нужно выделить диапазон и перейти в управление правилами.
Откроется диалоговое окно, где можно создать новое, изменить или удалить правило. Часто используют сразу несколько правил.
После нажатия кнопки «Изменить правило…» откроется окно, вид которого зависит от редактируемого правила.
Здесь также есть куча настроек, но мы их пока опустим. В целом там все интуитивно понятно. Нужно только поэкспериментировать. Практика – лучший учитель.
Если какое-то правило условного форматирования нужно удалить, то после выделения диапазона следует выбрать команду удаления.
Условное форматирование – это три шага вперед на пути к профессиональному использованию 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, без нее невозможна даже любительская работа с электронными таблицами и базами данных.