Условное форматирование по условиям в других ячейках (формулами) в Excel

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

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

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

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

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

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

 

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

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

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

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

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

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

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

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

условное форматирование с помощью формулы

 

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

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

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

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

Поделиться:
6 Комментариев
  • Константин says:

    Вложение

    Спасибо большое – натолкнули на мысль. Нужно было закрасить желтым ячейки в столбце S при нулевом значении ячеек в столбце R, расположенных в том же ряду. Первый ряд данных у меня – четвертый (см.скан результатов). Вроде бы задача решается просто прописыванием по столбцу S формулы условного форматирования =R4=0. Но небольшая проблемка оказалась в том, что нуль и пустая ячейка воспринимаются программой при этом одинаково. Чтобы исключить закрашивание ненужных ячеек в столбце S (которым соответствуют не нули, а пустоты), пришлось применить формулу =AND(R4=0;R4″”), где – знак “не равно”, а кавычки без пробела означают пустой символ или, проще говоря, пустую ячейку. Тут, как говаривал, помнится, кот Матроскин, все и заработало!

    • excel says:

      Супер, спасибо, что поделились своим опытом

  • Дмитрий says:

    Спасибо огромное!!! Необходимо было окрасить соседние ячейки по истинности значения стоящей справа.

  • Петр says:

    Добрый день.
    Если надо чтоб цвет правописания менялся с прогнозом, который отображается в процентах. Если прогноз по сегодняшнему дню совпадает и выше то цвет правописания черный (на выполнения не надо обращать внимания), а если ниже процента прогноза то – красным.
    Как это сделать не могу вспомнить.
    Спасибо.

  • Василий says:

    Интересно, просто и познавательно. А если такая задача: окрашивать в цвет ТОЛЬКО ячейки двух диапазонов, равные по значению (числовое значение). Диапазоны разных размеров, совпадений может быть несколько (или не быть). В каждом диапазоне равных чисел может быть разное количество. Спасибо!

    • excel says:

      разным цветом не целесообразно. А если будет 100 разных – использовать 100 разных цветов. Лучше формулами. Можно, например рядом с одним диапазоном прописать формулу =СЧЁТЕСЛИ(A:A;A1)
      и протянуть формулу. И рядом появится цифра, сколько раз встречается она в другом диапазоне.

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.

×
Рекомендуем посмотреть