Green-sell.info

Новые технологии
6 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

В этом уроке мы рассмотрим основы применения условного форматирования в Excel.

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

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

Используя условное форматирование, мы можем:

  • закрашивать значения цветом
  • менять шрифт
  • задавать формат границ

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

Где находится условное форматирование в Эксель?

Кнопка “Условное форматирование” находится на панели инструментов, на вкладке “Главная”:

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

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

  • Каким ячейкам вы хотите задать формат;
  • По каким условиям будет присвоен формат.

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

  • В таблице с данными выделим диапазон, для которого мы хотим применить выделение цветом:

  • Перейдем на вкладку “Главная” на панели инструментов и кликнем на пункт “Условное форматирование”. В выпадающем списке вы увидите несколько типов формата на выбор:
    • Правила выделения
    • Правила отбора первых и последних значений
    • Гистограммы
    • Цветовые шкалы
    • Наборы значков
  • В нашем примере мы хотим выделить цветом данные с отрицательным значением. Для этого выберем тип “Правила выделения ячеек” => “Меньше”:

Также, доступны следующие условия:

  1. Значения больше или равны какому-либо значению;
  2. Выделять текст, содержащий определенные буквы или слова;
  3. Выделять цветом дубликаты;
  4. Выделять определенные даты.
  • Во всплывающем окне в поле “Форматировать ячейки которые МЕНЬШЕ” укажем значение “0”, так как нам нужно выделить цветом отрицательные значения. В выпадающем списке справа выберем формат отвечающих условиям:

  • Для присвоения формата вы можете использовать пред настроенные цветовые палитры, а также создать свою палитру. Для этого кликните по пункту:

  • Во всплывающем окне формата укажите:
    • цвет заливки
    • цвет шрифта
    • шрифт
    • границы ячеек

  • По завершении настроек нажмите кнопку “ОК”.

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

Как создать правило

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

  • Выделим диапазон данных. Кликнем на пункт “Условное форматирование” в панели инструментов. В выпадающем списке выберем пункт “Новое правило”:

  • Во всплывающем окне нам нужно выбрать тип применяемого правила. В нашем примере нам подойдет тип “Форматировать только ячейки, которые содержат”. После этого зададим условие выделять данные, значения которых больше “57”, но меньше “59”:

  • Кликнем на кнопку “Формат” и зададим формат, как мы это делали в примере выше. Нажмите кнопку “ОК”:

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

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

Для создания условия по значению другой ячейки выполним следующие шаги:

  • Выделим первую ячейку для назначения правила. Кликнем на пункт “Условное форматирование” на панели инструментов. Выберем условие “Меньше”.
  • Во всплывающем окне указываем ссылку на ячейку, с которой будет сравниваться данная ячейка. Выбираем формат. Нажимаем кнопку “ОК”.

  • Повторно выделим левой клавишей мыши ячейку, которой мы присвоили формат. Кликнем на пункт “Условное форматирование”. Выберем в выпадающем меню “Управление правилами” => кликнем на кнопку “Изменить правило”:

  • В поле слева всплывающего окна “очистим” ссылку от знака “$”. Нажимаем кнопку “ОК”, а затем кнопку “Применить”.

  • Теперь нам нужно присвоить настроенный формат на остальные ячейки таблицы. Для этого выделим ячейку с присвоенным форматом, затем в левом верхнем углу панели инструментов нажмем на “валик” и присвоим формат остальным ячейкам:

На скриншоте ниже цветом выделены данные, в которых курс валюты стал ниже к предыдущему периоду:

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

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

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

Для применения нескольких условий к одной ячейке выполним следующие действия:

  • Выделим диапазон с данными, к которым мы хотим применить условное форматирование => кликнем по пункту “Условное форматирование” на панели инструментов => выберем условие выделения “Больше…” и укажем первое условие (если больше 10, то зеленая заливка). Такие же действия повторим для каждого из условий (больше 20 и больше 30). Не смотря на то, что мы применили три правила, данные в таблице закрашены зеленым цветом:

  • Кликнем на любую ячейку с присвоенным форматированием. Затем, снова кликнем по пункту “Условное форматирование” и перейдем в раздел “Управление правилами”. Во всплывающем окне, распределим правила от большего к меньшему и напротив первых двух поставим галочку “Остановить, если истина”. Этот пункт позволяет не применять остальные правила к ячейке, при соответствии первому. Затем кликнем кнопку “Применить” и “ОК”:

Применив их, наша таблица с данными температуры “подсвечена” корректными цветами, в соответствии с нашими условиями.

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

Для редактирования присвоенного правила выполните следующие шаги:

  • Выделить левой клавишей мыши ячейку, правило которой вы хотите отредактировать.
  • Перейдите в пункт меню панели инструментов “Условное форматирование”. Затем, в пункт “Управление правилами”. Щелкните левой клавишей мыши по правилу, которое вы хотите отредактировать. Кликните на кнопку “Изменить правило”:

  • После внесения изменений нажмите кнопку “ОК”.

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

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

  • Выделим диапазон данных с примененным условным форматированием. Кликнем по пункту на панели инструментов “Формат по образцу”.
  • Левой клавишей мыши выделим диапазон, к которому хотим применить скопированные правила формата:

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

Для удаления формата проделайте следующие действия:

  • Выделите ячейки;
  • Нажмите на пункт меню “Условное форматирование” на панели инструментов. Кликните по пункту “Удалить правила”. В раскрывающемся меню выберите метод удаления:

Сборник формул для условного форматирования

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

  • Excel 2003 : Формат (Format)Условное форматирование (Conditional formatting)формула;
  • Excel 2007-2010 : вкладка Главная (Home)Условное форматирование (Conditional formatting)Создать правило (New rule)Использовать формулу для определения форматируемых ячеек (Use a formula to determine which cells to format)
Читать еще:  Конвертировать mxl в excel

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

Все условия приведены для диапазона A1:A20 . Это означает, что для корректного выполнения условия необходимо выделить диапазон A1:A20 (столбцов может быть больше), начиная с ячейки A1 , после чего назначить условие.
Если выделять необходимо не с первой строки, а скажем, с 4-ой, то и выделить надо будет диапазон A4:A20 и в формуле для условия указывать в качестве критерия первую ячейку выделенного диапазона — A4 .

Если необходимо выделять форматированием не только конкретную ячейку, удовлетворяющую условию, а всю строку таблицы на основе ячейки одного столбца, то перед установкой правила необходимо выделить всю таблицу, строки которой необходимо форматировать, а ссылку на столбец с критерием закрепить:
= $A1 =МАКС( $A$1:$A$20 )
при выделенном диапазоне A1:F20 (диапазон применения условного форматирования), будет выделена строка A7:F7 , если в ячейке A7 будет максимальное число.

Так же можно применять не к конкретно одному столбцу, а к полностью диапазону. Но в этом случае надо знать принцип смещения ссылок в формулах, чтобы условия применялись именно к нужным ячейкам. Например, если задать условие для диапазона B1:D10 в виде формулы: = B1 , то цветом будут выделены ячейки столбца B, если значение ячейки столбца А в той же строке меньше( B1, B3). При этом если ячейки столбца D меньше ячеек столбца C в той же строке — они тоже будут выделены( D1 , D5 ).

  • Выделение ячеек с числами:
    =ЕЧИСЛО( A1 )
  • Выделение ячеек с числами, но не учитывая нули:
    =И(ЕЧИСЛО( A1 ); A1 <>0)
  • Выделение строк со значением больше 0:
    = A1 >0
  • Выделение строк со значением в диапазоне от 3 до 10:
    =И( A1 >=3; A1
  • Выделение в диапазоне $A$1:$A$20 ячейки с максимальным значением:
    = A1 =МАКС( $A$1:$A$20 )
  • Выделение в диапазоне $A$1:$A$20 ячейки с минимальным значением:
    =И(ЕЧИСЛО( A1 ); A1 =МИН( $A$1:$A$20 ))
  • Выделение в диапазоне $A$1:$A$20 ячейки со вторым по величине числом. Т.е. из чисел 1,2,3,4,5,6,7 будет выделено число 6:
    = A1 =НАИБОЛЬШИЙ( $A$1:$A$20 ;2)

  • Выделение ячеек с любым текстом:
    =ЕТЕКСТ( A1 )
  • Выделение ячеек с текстом Итог:
    = A1 =»Итог»
  • Выделение ячеек, содержащих текст Итог:
    =СЧЁТЕСЛИ( A1 ;»*итог*»)
    =НЕ(ЕОШ(ПОИСК(«итог»; A1 )))
  • Выделение ячеек, не содержащих текст Итог:
    =СЧЁТЕСЛИ( A1 ;»*итог*»)=0
    =ЕОШ(ПОИСК(«итог»; A1 ))
  • Выделение ячеек, текст которых начинается со слова Итог:
    =ЛЕВСИМВ( A1 ;4)=»Итог»
  • Выделение ячеек, текст которых заканчивается на слово Итог:
    =ПРАВСИМВ( A1 ;4)=»Итог»

  • Выделение текущей даты:
    = A1 =СЕГОДНЯ()
  • Выделение ячейки с датой, больше текущей:
    = A1 >СЕГОДНЯ()
  • Выделение ячейки с датой, которая наступит через неделю:
    = A1 =СЕГОДНЯ()+7
  • Выделение ячеек с датами текущего месяца(любого года):
    =МЕСЯЦ( A1 )=МЕСЯЦ(СЕГОДНЯ())
  • Выделение ячеек с датами текущего месяца текущего года:
    =И(МЕСЯЦ( A1 )=МЕСЯЦ(СЕГОДНЯ());ГОД( A1 )=ГОД(СЕГОДНЯ()))
    или
    =ТЕКСТ( A1 ;»ГГГГММ»)=ТЕКСТ(СЕГОДНЯ();»ГГГГММ»)
  • Выделение ячеек с выходными днями:
    =ДЕНЬНЕД( A1 ;2)>5
  • Выделение ячеек с будними днями:
    =ДЕНЬНЕД( A1 ;2)
  • Выделение ячеек, входящих в указанный период(промежуток) дат:
    =И( $A1 >ДАТА(2015;9;1); $A1

  • Выделение различий в ячейках по условию:
    = A1 <> $B1
  • Выделение ячейки, если ячейка следующего столбца(B) этой же строки меньше:
    = A1 > B1
  • Выделение строк цветом через одну:
    =ОСТАТ(СТРОКА();2)
  • Выделение строк цветом, если значение ячейки столбца A присутствует в диапазоне $F$1:$H$5000 :
    =СЧЁТЕСЛИ( $F$1:$H$5000 ; A1 )
  • Выделение строк цветом, если значение ячейки столбца A отсутствует в диапазоне $F$1:$H$5000 :
    =СЧЁТЕСЛИ( $F$1:$H$5000 ; A1 )=0
  • Выделение цветом ячейки, если её значение в диапазоне A1:A20 второе по счету:
    =СЧЁТЕСЛИ( $A$1:$A1 ; A1 )=2
  • Выделение ячеек, содержащих ошибки (#ЗНАЧ!; #Н/Д; #ССЫЛКА! и т.п.). Помимо просто выявления ячеек с ошибками можно применять, когда необходимо скрыть ошибочные значения в ячейках(назначив цвет шрифта таким же, как и цвет заливки):
    =ЕОШИБКА( A )
  • Выделение непустых ячеек в столбце A :
    = $A1 <>«»
  • Статья помогла? Поделись ссылкой с друзьями!

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

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

    Располагается эта полезная возможность на вкладке «Главная» в области «Стили» под одноименной пиктограммой:

    Создать правило

    Для создания правила условного форматирования в Excel кликните по соответствующей кнопке на ленте, раскрыв следующее меню:

    Выбрав пункт «Создать правило…», приложение отобразит окно:

    В нем Вы можете выбрать тип правила и настроить его описание (подробнее читайте далее в статье).

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

    Форматировать все ячейки на основании их значений

    Этот вид правила применяется для сравнения числовых значений в диапазоне. В описании можно выбрать стиль формата и соответствующие этому стилю параметры.

    Гистограмма

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

    Ширина ячейки принимается за 100%, что соответствует максимальному значению диапазона правила. Т.е. ячейка, содержащая максимальное значение будет залита полностью, а ячейка со значением в 2 раза меньшим максимальному – наполовину. В случае отрицательного значения, столбец будет окрашен другим цветом и иметь другую направленность (это можно изменить).

    • Показывать только столбец – установив флажок на данном поле, Вы сообщаете, что для диапазона ячеек правила необходимо скрывать содержимое и оставлять только формат;
    • Параметры значений – здесь устанавливаются максимальные и минимальные значения и их типы. В качестве типа может выступать число, процент, формула, процентиль либо по умолчанию (авто). Значение может быть только числовым. Все числа, меньше минимального (включая отрицательные), приравниваются к нулю, т.е. не содержат столбца. А те, которые больше максимального, приравниваются к 100% и закрашиваются полностью.
    • Внешний вид столбца – устанавливает способ заливки (сплошной или градиентный), границу и их цвета;
    • Направление столбца – определяет способ направленности (слева направо либо наоборот);
    • Кнопка «Отрицательные значения и ось…» – настройки отображения столбцов для отрицательных чисел. Что они позволяют:
      • Установить свой цвет заливки столбца и его границу или сделать их одинаковыми для всех значений (положительных и отрицательных. По умолчанию они различаются);
      • Задать положение оси или одинаковую направленность для всех значений.

    Цветовые шкалы

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

    В качестве примера, рассмотрим настройку трехцветной шкалы, хотя она мало чем отличается от настройки двухцветной.

    Здесь Вы можете установить, что считать минимальным значением, что средним, а что максимальным. Также возможно задать предпочтительный цвет и тип показателя.
    Разберем установки, представленные на изображении:

    • Минимальным числом задан ноль, а значения меньше его, будут иметь такие же цвет и насыщенность;
    • Средним значением указана единица и желтый цвет. Это значит, что переход шкалы от красного к желтому будет осуществлен между 0 и 1;
    • 4 является максимальным значением. Все, что превышает его, получает те же установки. Переход от желтого к зеленому происходит между 1 и 4.
    Читать еще:  Видео обрабатывается повторите попытку позже

    Наборы значков (флажков)

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

    Как и в случаях, описанных выше, за 100% принимается максимальное число, а остальные составляют от него какую-то долю. Весь диапазон разделяется на определенное количество частей, которое равно количеству значков в выбранном наборе. Каждой такой части соответствует свой флажок. Если диапазон нужно разделить не по долям, а по конкретным значениям, то поменяйте тип значения для значка.

    Форматировать только ячейки, которые содержат

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

    Рассмотрим правила, которые имеются в этом пункте:

    • Значение ячейки. Предполагает работу с числами и текстом. Сравнение производится по шкале сортировки.
    • Текст. Позволяет проверить наличие или отсутствие подстроки в тексте.
    • Даты. С его помощью легко создать правила типа «вчера», «сегодня», «завтра», «на прошлой неделе», «в следующем месяце» и т.п.
    • Пустые. Форматирует пустые ячейки. Пробелы не учитываются.
    • Непустые. Противоположное предыдущему правилу.
    • Ошибки. Истинно, когда значением ячейки является ошибка.
    • Без ошибки. Противоположное предыдущему правилу.

    Форматировать только первые и последние значения

    Из названия понятно, что правило срабатывает для тех ячеек, которые идут первыми (наибольшими) или последними (наименьшими) в указанном диапазоне. Количество таких ячеек указывается в виде числа или процента.

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

    Когда имеющихся правил недостаточно, можно создать свое, задав ему практически любую логику, на основе формул, результатом выполнения которой должно быть логическое значение. Эти тип называется «Использовать формулу для определения форматируемых ячеек».

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

    На изображении показан вариант, где уже применено условное форматирование ячеек. Рассмотрим, как его создать.

    Используем 2 условия со следующими формулами:

    • Если на складе нет товара, т.е. равен 0, то подсвечиваем позицию заказа красным – =ВПР(D3;A:B;2;ЛОЖЬ)=0;
    • Если на складе есть товар, но его количество меньше, чем указано в позиции заказа, то последнюю подсвечиваем желтым – =И(ВПР(D3;$A:$B;2;ЛОЖЬ) 0).

    Теперь необходимо выделить требуемый диапазон и создать нужные нам правила.

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

    Остальные правила

    Ничего не было сказано о еще двух видах правил, а именно:

    • Форматирование на основе среднего значения – полное название «Форматировать только значения, которые находятся выше или ниже среднего»;
    • Форматирование уникальных или повторяющихся значений.

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

    Управление правилами

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

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

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

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

    На изображение приведено 2 правила: значение равно трем и значение больше двух. Представьте, что они применены к ячейке со числом 3. Какое из них сработает? В этом случае оба, так как между ними нет конфликта в форматировании, одно отвечает за заливку, а второе за границу. Но если бы они оба отвечали за один и тот же стиль, то выполнилось правило, которое стоит выше, потому что имеет больший приоритет.

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

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

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

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

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

    Как применить условное форматирование в Excel 2016, 2013, 2010, 2007?

    Все нужные нам составляющие находятся в разделе меню «Условное форматирование» в категории «Стили» на вкладке «Главная».

    Вот какие опции тут доступны:

    • правила выделения ячеек. Тут можно задать условия форматирования на основе значений ячеек и их величин. Наиболее часто используемая опция
    • правила отбора первых и последних значений. Предоставляет возможности, связанные с заданием формата ячеек на основе их вхождения в топ первых или последних величин
    • гистограммы. Пожалуй, наиболее мощное из средств визуализации форматирования, наряду с цветовыми шкалами и наборами значков. Предоставляет палитру гистограмм всевозможных оттенков, которые вы можете задавать для величин ячеек
    • цветовые шкалы. Обеспечивает возможность задания двух- и трехцветовых шкал, доступных для цвета фона ячейки на базе ее величины в сравнении с другими ячейками в диапазоне
    • наборы значков. Позволяет отобразить в ячейке значок. Рисунок отображаемого значка зависит от величины ячейки в сравнении с другими ячейками. На выбор пользователя предоставляется до 20 наборов значков, которые вы можете комбинировать между собой.

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

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

    Читать еще:  Описание функции если в excel

    Итак, первое правило: если значение ячейки меньше нуля, ее фон должен окрашиваться в красный. Реализуем это на практике. Выбираем на ленте опцию «Условное форматирование» -> «Правила выделения ячеек» -> «Меньше».

    Появляется мини-форма с указанием условия, на основе которого будет сформировано правило. Поскольку нам нужно условие «меньше 0», то вставляем цифру 0 в левой части формы. Справа же выбираем расцветку фона, которую мы хотели бы видеть на соответствующих ячейках. Также тут в дополнение можно указать цвет текста.

    Создадим еще одно правило: если значение ячейки больше 0, окрашиваем ее в желтый цвет. На этот раз выберем правило «Больше».

    Наконец, последнее третье правило: если значение равно 0, преобразуем цвет в зеленый.

    Теперь вводим числа в указанный диапазон и созерцаем результат.

    Как применить условное форматирование в Excel 2003?

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

    Опция условного форматирования в Excel 2003 скрыта в разделе главного меню «Формат».

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

    Видеоинструкция

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

    Условное форматирование в Эксель – этот тот инструмент, который делит работу на до и после его изучения. Суть в том, что при наступлении некоторого условия ячейки форматируются автоматически. Например, если число превышает значение 100, шрифт становится красным полужирным курсивом; когда до наступления платежа остается 2 дня, ячейка с датой подсвечивается желтым цветом; перевыполнение плана продаж на 5% и более окрашивается в зеленый цвет и т.д. и т.п.

    Вот упрощенный, но реальный пример. Есть отчет о товарных запасах.

    Менеджер по закупкам отслеживает те позиции, которые требуют пополнения. Для этого он смотрит в последнюю колонку, где рассчитывается товарный запас (ТЗ) в неделях. Если ТЗ меньше, скажем, 3-х, то нужно готовить заказ. Если меньше 2-х, то возникает риск дефицита и заказ нужно размещать срочно. Если в таблице десятки позиций, то просмотр каждой строки займет довольно много времени. А теперь та же таблица, где после применения условного форматирования значения ниже пороговых подсвечиваются некоторым цветом.

    Согласитесь, так гораздо нагляднее. В реальности условия сложнее, а данные постоянно меняются. Поэтому эффект от применения условного форматирования – это многочасовая экономия времени ежедневно! Теперь для оценки запасов достаточно взглянуть на таблицу, а не анализировать каждую ячейку. Много желтого – пора действовать, много красного – ситуация критическая!

    Для настройки условного формата следует воспользоваться соответствующей командой на вкладке Главная.

    При ее нажатии открывается меню.

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

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

    Все сценарии разбиты на категории:

    – Правило выделения ячеек

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

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

    Больше… Если значение ячейки, к которой применяется правило выделения, больше указанного значения, то в силу вступает заданный формат.

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

    Меньше… Форматируются ячейки, у которых значение меньше заданного порога.

    Между… Форматирование наступает, если содержимое ячейки находится внутри заданных границ.

    Равно… если значение или текст в ячейке совпадает с условием.

    Текст содержит… Если совпадает только часть текста (слово, код, комбинация символов и т.д).

    Дата… Возможность форматировать периоды отстоящие от текущей даты, например, сегодня, вчера, последние 7 дней, следующий месяц и др. Условное форматирование даты полезно при контроле платежей, отгрузок и т.п.

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

    Правила отбора первых и последних значений выделяют наибольшие или наименьшие значения. Помогают анализировать данные, показывая приоритеты и «слабые места».

    Первые 10 элементов… Выделяются первые топ–10 ячеек. Количество регулируется в диалоговом окне (можно сделать топ-5, топ-20 и др.).

    Первые 10%… Выделяются 10% наибольших значений. Долю можно изменить.

    Последние 10 элементов… Аналогично с первым пунктом, только форматируются наименьшие значения.

    Последние 10%… Наименьшие 10% или другая доля от всех элементов.

    Выше среднего… Форматируются все значения, которые больше средней арифметической.

    Ниже среднего… Ниже средней арифметической.

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

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

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

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

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

    В ячейках Excel выглядит так.

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

    Откроется диалоговое окно, где можно создать новое, изменить или удалить правило. Часто используют сразу несколько правил.

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

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

    Если какое-то правило условного форматирования нужно удалить, то после выделения диапазона следует выбрать команду удаления.

    Условное форматирование – это три шага вперед на пути к профессиональному использованию Excel. Поэтому рекомендую незамедлительно внедрить в практику.

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

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

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector