Условное форматирование по условиям в других ячейках (формулами) в Excel
Условное форматирование в новых версиях Excel мы рассматривали в видео уроке. Стандартные приемы очень удобны и наглядны. Но иногда требуется применять формат ячеек, в зависимости от каких-нибудь условий в соседних ячейках.
К нам поступил вопрос:
Здравствуйте, а как сделать условное форматирование одного столбца относительно другого? при этом тот который задает форматирование имеет 3 текстовых признака, то есть главный столбец с кодами должен окрашиваться в соответствии с требуемым текстовым признаком?
Давайте и рассмотрим на этом примере условное форматирование с помощью формул. Оно так и называется, потому, что без формул тут не обойтись.
Представим себе следующий пример. У нас есть таблицам с ФИО, по каждому сотруднику есть результат в процентах и информация о наличии льгот. Нам необходимо выделить с помощью условного форматирования только тех сотрудников, которые имеют результат выше 75 и имеют льготы.
При соблюдении данных условий, нам необходимо закрасить ячейку в желтый цвет. Для начала нам необходимо выделить все фамилии, далее выбрать пункт “Условное форматирование”, “Создать правило”, из типа правил выбрать “Использовать формулу для определения форматируемых ячеек” и нажать “Ок”.
В открывшемся диалоговом окне настраиваем правило. Необходимо прописать формулу, которая при возвращении истины будет закрашивать наши ячейки.
Важно! Формула прописывается к первой ячейке (строке). Формула обязательно должна быть с относительными ссылками (без долларов), если мы хотим, чтобы она распространилась на все последующие строки.
Мы прописываем формулу:
=И(B2>75;C2="Да")
И – это означает, что мы проверяем два условия и они должны обе выполняться. Если бы нужно было, чтобы выполнялось одно из условий (либо результат больше 75 либо сотрудник – льготник), то нужно было бы использовать функцию ИЛИ, еще проще если условие одно.
В примере от нашей читательницы нужно использовать просто формулу C2=”Да”, но вместо “Да” там будет свой текст. Если таких признака три, то условное форматирование делается отдельно по всем признакам. То есть необходимо проделать эту процедуру три раза, просто меняя признак и соответствующий ему формат ячейки.
Вот так будет выглядеть формулу в нашем примере.
Не забудьте выбрать формат, в который необходимо закрашивать наши ячейки. Нажимаем “Ок” и проверяем.
Были закрашены Петров и Михайлов, у обоих результат выше 75 и они являются льготниками, что нам и требуется.
Надеюсь, что ответили на ваш вопрос по условному форматирования. Ставьте лайки и подписывайтесь на нашу группу в ВК.
Вложение
Спасибо большое – натолкнули на мысль. Нужно было закрасить желтым ячейки в столбце S при нулевом значении ячеек в столбце R, расположенных в том же ряду. Первый ряд данных у меня – четвертый (см.скан результатов). Вроде бы задача решается просто прописыванием по столбцу S формулы условного форматирования =R4=0. Но небольшая проблемка оказалась в том, что нуль и пустая ячейка воспринимаются программой при этом одинаково. Чтобы исключить закрашивание ненужных ячеек в столбце S (которым соответствуют не нули, а пустоты), пришлось применить формулу =AND(R4=0;R4″”), где – знак “не равно”, а кавычки без пробела означают пустой символ или, проще говоря, пустую ячейку. Тут, как говаривал, помнится, кот Матроскин, все и заработало!
Супер, спасибо, что поделились своим опытом
Спасибо огромное!!! Необходимо было окрасить соседние ячейки по истинности значения стоящей справа.
Добрый день.
Если надо чтоб цвет правописания менялся с прогнозом, который отображается в процентах. Если прогноз по сегодняшнему дню совпадает и выше то цвет правописания черный (на выполнения не надо обращать внимания), а если ниже процента прогноза то – красным.
Как это сделать не могу вспомнить.
Спасибо.
Интересно, просто и познавательно. А если такая задача: окрашивать в цвет ТОЛЬКО ячейки двух диапазонов, равные по значению (числовое значение). Диапазоны разных размеров, совпадений может быть несколько (или не быть). В каждом диапазоне равных чисел может быть разное количество. Спасибо!
разным цветом не целесообразно. А если будет 100 разных – использовать 100 разных цветов. Лучше формулами. Можно, например рядом с одним диапазоном прописать формулу =СЧЁТЕСЛИ(A:A;A1)
и протянуть формулу. И рядом появится цифра, сколько раз встречается она в другом диапазоне.