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

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

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

Эти инструкции относятся к Excel 2019, 2016, 2013, 2010 и Excel для Office 365.

Применение нескольких условий в Excel

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

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

Поиск данных, которые превышают 25% и 50% увеличивается

В следующем примере два пользовательских правила условного форматирования будут применяться к диапазону от ячеек B2 до B5 .

 

    • Первое правило проверяет, больше ли данные в ячейках A2: A5 , чем соответствующее значение в B2: B5 , более чем на 25%.
    • Второе правило проверяет, превышает ли те же данные в A2: A5 соответствующее значение в B2: B5 более чем на 50%.

 

Как видно на изображении выше, если выполняется любое из указанных выше условий, цвет фона ячейки или ячеек в диапазоне B1: B4 изменится.

 

    • Для данных, где разница составляет более 25%, цвет фона ячейки изменится на зеленый.
    • Если разница превышает 50%, цвет фона ячейки изменится на красный.

 

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

В заключительной части учебника мы добавим формулы в ячейки C2: C4 , которые показывают точную процентную разницу между значениями в ячейках A2: A5 и B2: B5 ; это позволит нам проверить точность правил условного форматирования.

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

Сначала мы применим условное форматирование, чтобы найти 25-процентное или более значительное увеличение.

Функция будет выглядеть так:

 = (А2-В2)/А2> 25%
  1. Выделите ячейки B2 на B5 на листе.
  2. Нажмите на вкладку “Главная” на ленте .
  3. Нажмите на значок Условное форматирование на ленте , чтобы открыть раскрывающийся список.
  4. Выберите Новое правило , чтобы открыть диалоговое окно Новое правило форматирования .
  5. В разделе Выберите тип правила выберите последний параметр: Используйте формулу, чтобы определить, какие ячейки форматировать.
  6. Введите формулу , отмеченную выше, в поле ниже Форматируйте значения, где эта формула верна:
  7. Нажмите кнопку Формат , чтобы открыть диалоговое окно. Перейдите на вкладку Fill и выберите цвет.
  8. Нажмите ОК , чтобы закрыть диалоговые окна и вернуться на лист.
  9. Цвет фона ячеек B3 и B5 должен измениться на выбранный вами цвет.

Теперь мы применим условное форматирование, чтобы найти увеличение на 50 или более процентов. Формула будет выглядеть так:

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

Цвет фона ячейки B3 должен оставаться прежним, что указывает на то, что процентная разница между числами в ячейках A3 и B3 превышает 25 процентов, но меньше или равно 50 процентам. Цвет фона ячейки B5 должен измениться на новый выбранный вами цвет, что указывает на большую разницу в процентах между числами в ячейках A5 и B5 . чем 50 процентов.

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

Чтобы убедиться в правильности введенных правил условного форматирования, мы можем ввести формулы в ячейки C2: C5 , которые будут вычислять точную процентную разницу между числами в диапазонах A2: A5 и B2: B5 .

Формула в ячейке C2 выглядит следующим образом:

 = (А2-В2)/А2
  1. Нажмите на ячейку C2 , чтобы сделать ее активной.
  2. Введите приведенную выше формулу и нажмите клавишу Enter на клавиатуре.
  3. Ответ 10% должен появиться в ячейке C2 , указывая, что число в ячейке A2 на 10% больше, чем в ячейке B2 .
  4. Возможно, потребуется изменить форматирование в ячейке C2 , чтобы отобразить ответ в процентах.
  5. Используйте маркер заполнения , чтобы скопировать формулу из ячейки C2 в ячейки C3 в C5 .
  6. Ответы для ячеек C3 на C5 должны составлять 30%, 25% и 60%.

Ответы в этих ячейках показывают, что правила условного форматирования точны, поскольку разница между ячейками A3 и B3 превышает 25 процентов, а разница между ячейками A5 и B5 превышают 50 процентов.

Ячейка B4 не изменила цвет, поскольку разница между ячейками A4 и B4 равна 25%, а наше правило условного форматирования указало, что процент больше 25 процентов требовалось для изменения цвета фона.

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

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

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

В случае, когда второе правило истинно (разница в значении составляет более 50 процентов между двумя ячейками), тогда первое правило (разница в значении превышает 25 процентов) также верно.

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

В порядке приоритета Excel указано, что правило, которое находится выше в списке в диалоговом окне «Диспетчер правил условного форматирования», применяется в первую очередь.

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

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

Применение не конфликтующих правил

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

Если первое правило условного форматирования в нашем примере форматирует диапазон ячеек B2: B5 с оранжевой рамкой вместо оранжевого цвета фона, два правила условного форматирования не будут конфликтовать, поскольку оба формата могут применяться без мешая другим.

Условное форматирование против обычного форматирования

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

Оцените статью
Solutics.ru
Добавить комментарий