Как сделать условие в excel если значение в ячейке равно то закрасить

Создание условных формул

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

Например, функция ЕСЛИ использует следующие аргументы.

Формула, использующая функцию IF

logical_test: условие, которое необходимо проверить.

value_if_true: возвращаемое значение, если условие имеет значение True.

value_if_false: возвращаемое значение, если условие имеет значение False.

Дополнительные сведения о создании формул см. в разделе «Создание или удаление формулы».

Что вы хотите сделать?

• Создание условной формулы, которая приводит к логическому значению (TRUE или FALSE)
• Создание условной формулы, которая приводит к другому вычислению или значениям, отличным от TRUE или FALSE

Создание условной формулы, которая приводит к логическому значению (TRUE или FALSE)

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

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

1. Выделите пример, приведенный в этой статье. Выделение примера в справке
2. Нажмите клавиши CTRL+C.
3. В Excel создайте пустую книгу или лист.
4. Выделите на листе ячейку A1 и нажмите клавиши CTRL+V.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

1. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Описание (результат)

Определяет, больше ли значение в ячейке A2, чем значение в A3, а также если значение в A2 меньше значения в A4. (FALSE)

Определяет, больше ли значение в ячейке A2 значения В3 или значение в A2 меньше значения В4. (TRUE)

Определяет, не равна ли сумма значений в ячейках A2 и A3 24. (FALSE)

Определяет, не равно ли значение в ячейке A5 «Spets». (FALSE)

Определяет, не равно ли значение в ячейке A5 «Spets» или значение в A6 равно «Мини-приложениям». (TRUE)

Дополнительные сведения о том, как использовать эти функции, см. в разделах «Функции AND», «OR» и «НЕ».

Создание условной формулы, которая приводит к другому вычислению или значениям, отличным от TRUE или FALSE

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

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

Выделите пример, приведенный в этой статье.

Важно: Не выделяйте заголовки строк или столбцов.

Важно: Чтобы пример правильно работал, его нужно вставить в ячейку A1.

1. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (знак ударения) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показывать формулы.

Скопировав пример на пустой лист, вы можете настроить его так, как вам нужно.

Описание (результат)

=ЕСЛИ(A2=15, «ОК», «Не ОК»)

Если значение в ячейке A2 равно 15, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (ОК)

=ЕСЛИ(A2<>15, «ОК», «Не ОК»)

Если значение в ячейке A2 не равно 15, возвращается значение «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)

Если значение в ячейке A2 не меньше или равно 15, возвращается значение «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)

=ЕСЛИ(A5<>«SPROCKETS», «ОК», «Не ОК»)

Если значение в ячейке A5 не равно «SPETS», возвращается значение «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)

Если значение в ячейке A2 больше значения В3, а значение в A2 также меньше значения в A4, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)

=ЕСЛИ(И(A2<>A3, A2<>A4), «ОК», «Не ОК»)

Если значение в ячейке A2 не равно A3, а значение в A2 также не равно значению в A4, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (ОК)

Если значение в ячейке A2 больше значения в A3 или значение в A2 меньше значения в A4, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (ОК)

=IF(OR(A5<>«Sprockets», A6<>«Widgets»), «OK», «Not OK»)

Если значение в ячейке A5 не равно «Spets» или значение в A6 не равно «Widgets», возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (Не ОК)

=ЕСЛИ(ИЛИ(A2<>A3, A2<>A4), «ОК», «Не ОК»)

Если значение в ячейке A2 не равно значению в A3 или значение в A2 не равно значению в A4, возвращается «ОК». В противном случае возвращается сообщение «Не ОК». (ОК)

Дополнительные сведения о том, как использовать эти функции, см. в разделах «Функция ЕСЛИ», «И» и » ИЛИ».

Как сделать условие в excel если значение в ячейке равно то закрасить

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Пользователь
Сообщений: 248 Регистрация: 03.01.2016
14.01.2020 06:00:53

Я пробовал с ИЛИ, ЕСЛИОШИБКА и еще с чем то — не дало результата. Премного Благодарен!

Все равно не срабатывает УФ в B4, когда Н/Д

Прикрепленные файлы

• Лист Microsoft Excel (15).xlsx (10.02 КБ)

Пользователь
Сообщений: 14577 Регистрация: 01.01.1970
14.01.2020 06:13:40
было бы странно, если бы сработала формула с вывихнутой логикой

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Пользователь
Сообщений: 11251 Регистрация: 01.01.1970
14.01.2020 06:51:39
словами опишите что вы хотите
Лень двигатель прогресса, доказано.
Пользователь
Сообщений: 248 Регистрация: 03.01.2016
14.01.2020 21:16:49

Хочу первую и вторую формулу вставить в одно правило УФ, чтобы ячейка меняла цвет когда выполняется условие первой формулы или условие второй (ошибка: Н/Д). У меня только работает когда для каждой формулы отдельное правило.

Пользователь
Сообщений: 14577 Регистрация: 01.01.1970
14.01.2020 21:46:16

Сергей,
задача вообще-то уже решена, но она такая:
есть ДИАПАЗОН из 7-и колонок, есть ячейка содержащая ЗНАЧЕНИЕ
нужно найти ЗНАЧЕНИЕ в первой колонке ДИАПАЗОНА и проверить, что в 6 ячейках правее найденного ЗНАЧЕНИЯ находятся только 1, 2 или «»

OSA913,
Сергей вас спрашивает не сколько и каких формул вы хотите, а что за задачу вы решаете
OSA913, и еще вам небольшая подсказка:
— если нужно сорвать яблочко с дерева — используйте руки
— если нужно убегать от собак — используйте ноги
— если нужно решать задачу — эффективнее всего использовать для этого мозг

Изменено: Ігор Гончаренко — 14.01.2020 21:46:53

Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Тема: EXcel. Есть ли формула: «Если цвет ячейки-такой-то, то другая ячейка такого же цвета»

EXcel. Есть ли формула: «Если цвет ячейки-такой-то, то другая ячейка такого же цвета»

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

Поделиться с друзьями
05.05.2008, 13:37 #2

Клерк Регистрация 12.12.2002 Адрес Ярославль Сообщений 66,413

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

Ну так и попробуйте его. Что-то типа для воскресенья:
[Формула] :: =ДЕНЬНЕД(ЯчейкаСлева)=1
Формат\Вид\ЦветЗаливки

Best regards, Михаил
05.05.2008, 15:22 #3

Клерк Регистрация 01.10.2007 Адрес Питер Сообщений 1,188

Спасибо за ответ.
Не совсем подходит.

«Данная функция Возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 1 (воскресенье) до 7 (суббота).»

У меня данные от 1 до 31 (дни), цветом я закрашиваю нерабочие дни.
А ниже напротив этих дней проставляются часы работы или вых. дни.
Просто, ради интереса, (ну и облегчения работы), хотела узнать,
возможно ли, чтобы от цвета ячейки наверху внизу тоже перекрашивалось в тот же цвет и проставлялось «В».
В принципе, можно просто скопировать формат-цвета ячеек,
буквы уж думаю, что сама поставлю.
Спасибо.

08.05.2008, 06:39 #4

Клерк Регистрация 01.08.2007 Сообщений 101

можно составить сложную формулу. У вас вверху дни недели пишутся или нет? Просто я пишу и от этого пляшу использую формулу с если и тогда все что вам нужно там будет.

08.05.2008, 11:04 #5

Клерк Регистрация 01.10.2007 Адрес Питер Сообщений 1,188

Да, вверху.
Могу на мыло прислать образец.

08.05.2008, 11:30 #6

инспектор Регистрация 27.04.2004 Адрес Уфа Сообщений 477

можно, но это и не нужно. Проставляйте значение формулой, а закрашивайте от значения в ячейке. Больше элементов, но проще реализовать.
Анализировать цвет можно только макросами по значениям атрибутов ячейки. Но это уже VBA.

18.04.2009, 09:22 #7
Аноним

Без МАКРОСА

Например, если нужно, чтобы в том случае, когда в первой колонке листа Excel стояло число, кратное 7, цвет соседней ячейки во второй колонке был бы красный, а если кратное 6, то желтый, то порядок действий будет выглядеть так:
1) поставить курсор мыши на верхнюю ячейку во второй колонке;
2) вызвать диалоговое окно «Формат» — «Условное форматирование»;
3) в этом диалоговом окне в качестве первого условия ввести формулу «=ОСТАТ(A1;7)=0» (она возвращает True, если остаток от деления числа в А1 на 7 равен 0);
4) указать кнопкой «Формат» для первого условия, что при его выполнении требуется заливать ячейку красным;
5) нажав кнопку «А также», добавить еще одно условие;
6) вторым условием ввести формулу «=ОСТАТ(A1;6)=0»;
7) указать кнопкой «Формат» для второго условия, что при его выполнении требуется заливать ячейку желтым.
8) путем копирования и вставки распространить это форматирование на весь второй столбец. Указанный алгоритм верен, если в настройках Excel (вкладка «Общие» диалогового окна «Сервис — Параметры») выключен «стиль ссылок R1C1» — в противном случае координаты ячеек следует соответственно изменить.
В диалоговом окне «Условное форматирование» можно указать до трех условий, если же вам нужно больше, то придется либо проявить максимум изобретательности при составлении формул.

21.04.2009, 12:51 #8

Клерк Регистрация 21.04.2009 Сообщений 1

Помогите, пожалуйста, решить задачу в excel: даны 2 списка — страны и столицы, в ячейке А1 ввести название страны, в ячейке В1 — появляется столица. Я сделала раскрывающийся список, но незнаю какое сделать условие. Спасибо.

18.05.2009, 00:55 #9
Аноним

Обратите взор или на функцию ВПР, или СМЕЩ, или связку ИНДЕКС-ПОИСКПОЗ.
Все зависит от размещения исходных данных и лдичных предпочтений

20.05.2009, 13:44 #10

Клерк Регистрация 19.12.2008 Сообщений 5

Можно вместо перечисления чисел от 1 до 31 вверху табеля вставить дату в первую ячеку (начало месяца), а в остальные формулу «=ПредыдущаяЯчека+1″, а дальше отформатировать ячейку так чтобы показывалось только день (ФорматЯчейки-Все форматы-Тип=»Д»)
После этого можно пользоваться условным форматированием.
Я выставляю так Выходные и Восьмерки:
=ЕСЛИ(ДЕНЬНЕД(AU$18;2)<6;8;"В")

17.03.2011, 17:29 #11
Аноним

формула для анализа по цвету ячейки

Доброго времени суток!
Каким образом можно использовать функцию ячейка цвет в логической формуле?

=ЕСЛИ(ЯЧЕЙКА(цвет;A1) чтобы дальше выводило заданое правдивое и неправдивое значение?

Другими словами если цвет ячейки Б1 такой же как в А1 то 1 если нет то 2

17.03.2011, 18:30 #12

Клерк-клерик Регистрация 04.12.2008 Адрес Пермь Сообщений 2,187

Аноним, в стандартном наборе таких функций нет. Используйте пользовательские.

Если бы я не был программистом, я б наверное хирургом стал. Люблю, знаете ли, покопаться во всякой фигне непонятной.

18.03.2011, 08:26 #13

Клерк Регистрация 17.12.2010 Сообщений 169

Сообщение от Аноним

Доброго времени суток!
Каким образом можно использовать функцию ячейка цвет в логической формуле?

Octopus прав. Функциями листа можно только в случае заливки условным форматированием, т.е. фактически анализ не цвета, а условия, по которому закрашена ячейка.

18.05.2012, 06:15 #14

выборочная окраска ячеек из области, если значение больше заданного

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

Пожалуйста подскажите как?

Можно не ячейку окрашивать инфм цветом, а шрифт в ячейке другим цветом.

18.05.2012, 07:31 #15

Клерк Регистрация 12.12.2002 Адрес Ярославль Сообщений 66,413

Условное форматирование — Правила выделения ячеек — Больше — 50 .

Best regards, Михаил
12.01.2015, 11:14 #16

Клерк Регистрация 12.01.2015 Сообщений 1

Добрый день! Напишу сюда. Возможно в экселе сделать вот что. Надо реализовать такую формулу: в зависимости от цвета ячейки А1, в ячейке В1 будут вычисляться разные формулы.
если конкретно, то если цвет ячейки красный то от ставки отнимается 10%,ЕСЛИ СИНИЙ ТО 15%

12.01.2015, 11:38 #17

Клерк Регистрация 17.12.2010 Сообщений 169

Нет, формулы не умеют определять форматирования ячейки.
Можно, если форматирование задано условием (условное форматирование) — использовать то же условие в формуле:

=ставка*(1-ВЫБОР(условие[значение 1, 2, 3. ];0,1;0,15)) =ставка*(1-ЕСЛИ(условие;0,1;0,15))

19.02.2016, 20:31 #18
Аноним

Спасибо. )))

Сообщение от Аноним

Например, если нужно, чтобы в том случае, когда в первой колонке листа Excel стояло число, кратное 7, цвет соседней ячейки во второй колонке был бы красный, а если кратное 6, то желтый, то порядок действий будет выглядеть так:
1) поставить курсор мыши на верхнюю ячейку во второй колонке;
2) вызвать диалоговое окно «Формат» — «Условное форматирование»;
3) в этом диалоговом окне в качестве первого условия ввести формулу «=ОСТАТ(A1;7)=0» (она возвращает True, если остаток от деления числа в А1 на 7 равен 0);
4) указать кнопкой «Формат» для первого условия, что при его выполнении требуется заливать ячейку красным;
5) нажав кнопку «А также», добавить еще одно условие;
6) вторым условием ввести формулу «=ОСТАТ(A1;6)=0»;
7) указать кнопкой «Формат» для второго условия, что при его выполнении требуется заливать ячейку желтым.
8) путем копирования и вставки распространить это форматирование на весь второй столбец. Указанный алгоритм верен, если в настройках Excel (вкладка «Общие» диалогового окна «Сервис — Параметры») выключен «стиль ссылок R1C1» — в противном случае координаты ячеек следует соответственно изменить.
В диалоговом окне «Условное форматирование» можно указать до трех условий, если же вам нужно больше, то придется либо проявить максимум изобретательности при составлении формул.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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