Как прописать формулу в excel с если: Функция ЕСЛИ — вложенные формулы и типовые ошибки

Формула условного форматирования Excel с IF

Получите БЕСПЛАТНЫЕ расширенные упражнения Excel с решениями!

Мы можем использовать формулу условного форматирования с ЕСЛИ в Excel для легкого вычисления и привлекательного рабочего листа. В этой статье мы узнаем, как это сделать, на нескольких красивых примерах и объяснениях.

Практическая рабочая тетрадь

4 быстрых метода формулы условного форматирования Excel IF

1. Формула условного форматирования с ЕСЛИ в Excel

2. Формула условного форматирования Excel с несколькими операторами IF

3. Формула Excel с функциями ЕСЛИ и СЧЁТЕС в условном форматировании

4. Формула условного форматирования Excel с комбинацией функций IF и AND

Заключение

Похожие чтения

Рабочая тетрадь

Загрузите следующую рабочую тетрадь и выполните упражнение.

4 Быстрые методы формулы условного форматирования Excel IF

1.

Формула условного форматирования с ЕСЛИ в Excel

Мы знаем, что функция Excel ЕСЛИ помогает нам запустить логический тест, а также возвращает ИСТИНА для одного значения и ЛОЖЬ для другого, а условное форматирование Excel помогает нам применить определенное форматирование к диапазону. Предположим, у нас есть набор данных ( B4:E9 ) о продуктах с их суммами покупки и продажи. Мы собираемся рассчитать убыток или прибыль продуктов в диапазоне ячеек 9.0022 E5:E9 и примените условное форматирование, чтобы выделить значения.

ШАГ 1:

  • Сначала выберите Cell E5 .
  • Далее введите формулу:

=ЕСЛИ(D5>C5;"Прибыль","Убыток")

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

Это вернет “ Прибыль », если ячейка D5 больше, чем C5 . В противном случае будет возвращено « Loss ».

ШАГ 2:

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

ШАГ 3:

  • Нажмите « Используйте формулу, чтобы определить, какие ячейки форматировать ».
  • В поле формулы введите формулу:

=E5="Прибыль"

  • Выберите параметр Формат .

ШАГ 4:

  • Затем из окна Формат ячеек перейдите в раздел Заполнить .
  • Выберите любой цвет фона. Мы можем увидеть образец цвета в коробке для образцов.
  • Нажмите OK .

ШАГ 5:

  • Снова нажмите OK .
  • Наконец, мы видим, что ячейки « Прибыль » выделены цветом.

Мы можем сделать тот же процесс для выделения ячеек « Loss ».

Подробнее: Условное форматирование, если ячейка не пуста

2. Формула условного форматирования Excel с несколькими операторами IF

Допустим, у нас есть набор данных ( B4:D9 ) имен учащихся и их оценок. Мы собираемся использовать несколько операторов IF , чтобы узнать оценку учащегося, и условное форматирование, чтобы выделить ячейки на основе оценки.

ШАГ 1:

  • В начале выберите Cell D5 .
  • Теперь введите формулу:

=ЕСЛИ(С5<40,"F",ЕСЛИ(С5<70,"В","А"))

ШАГ 2:

  • Нажмите Введите и используйте инструмент Fill Handle для следующих ячеек.

🔎 Как работает формула?

  • IF(C5<70,B”,A”): Это вернет “ B ”, если метки меньше 70 , иначе “ A ”.
  • IF(C5<40,"F",IF(C5<70,B",",A")): Будет возвращено " F ", если метка меньше 40 , в противном случае результат вышеописанной процедуры.

ШАГ 3:

  • Затем перейдите на вкладку Главная > раскрывающийся список Условное форматирование > Новое правило .

ШАГ 4:

  • В окне Новое правило форматирования выберите параметр « Форматировать только те ячейки, которые содержат ».
  • Выберите параметр Особый текст в раскрывающемся списке Форматировать только ячейки с полем . Также введите « Ф ».
  • Выберите параметр Формат .

ШАГ 5:

  • Появится окно Формат ячеек .
  • Перейдите в раздел Заливка и выберите цвет фона. Образец цвета мы можем увидеть в коробке Sample .
  • Выберите ОК .

ШАГ 6:

  • Выберите OK еще раз.
  • В итоге мы видим, что ячейка, содержащая « F », окрашена.

ШАГ 7:

  • Мы также можем выбрать разные цвета для разных текстов в одной и той же процедуре.

Подробнее: Как выполнить условное форматирование с несколькими критериями (11 способов)

Аналогичные показания

  • Как выполнить условное форматирование для нескольких условий (8 способов)
  • Условное форматирование нескольких текстовых значений в Excel (4 простых способа)
  • Условное форматирование с ИНДЕКС-ПОИСКПОЗ в Excel (4 простые формулы)
  • Как использовать условное форматирование на основе ВПР в Excel
  • Условное форматирование Excel на основе диапазона дат

3.

Формула Excel с функциями ЕСЛИ и СЧЁТЕС в условном форматировании

Здесь у нас есть набор данных, в котором диапазон B5:B9 содержит наименование продуктов и ассортимент C5:C9 содержит статус их доставки на День 1 . Мы увидим, что если количество « Доставлено » в диапазоне C5:C9 совпадает с количеством продуктов в диапазоне B5:B9 , то Ячейка C11 , содержащая текст DONE , будет отображаться в цвете. Мы будем использовать функцию СЧЕТЧИК Excel , обернутую в функцию ЕСЛИ с условным форматированием .

ШАГ 1:

  • Сначала выберите Cell C11 и перейдите на вкладку Home .
  • Щелкните раскрывающийся список Условное форматирование .
  • Далее выберите Новое правило .

ШАГ 2:

  • Выберите параметр « Использовать формулу для определения ячеек для форматирования » в окне « Новое правило форматирования ».
  • В поле формулы введите формулу:

=ЕСЛИ(СЧЕТЧ($C$5:$C$9)=СЧЕТЧ($B$5:$B$9),ИСТИНА,ЛОЖЬ)

  • В параметре Формат выберите конкретный цвет, как мы видели в приведенных выше процедурах.
  • Теперь нажмите OK .

🔎 Как работает формула?

  • COUNTA($C$5:$C$9): Excel Функция COUNTA будет подсчитывать количество ячеек в C5:C9 диапазон, содержащий значения.
  • COUNTA($B$5:$B$9):  Excel Функция COUNTA будет подсчитывать количество ячеек в диапазоне B5:B9 , содержащих значения.
  • ЕСЛИ(СЧЁТЕС($C$5:$C$9)=СЧЕТЧ($B$5:$B$9),ИСТИНА,ЛОЖЬ): Excel ЕСЛИ функция вернёт ИСТИНА , если два диапазона ( B5:B9 и C5: C9 ) равны, иначе FALSE .

ШАГ 3:

  • Наконец, когда в ячейке C9 мы набираем « Доставлено », ячейка C11 становится цветной.

Подробнее: Формула условного форматирования Excel

4. Формула условного форматирования Excel с комбинацией функций ЕСЛИ и И

Допустим, у нас есть набор данных ( B4:C9 ) продуктов и сумм их покупок. Мы собираемся использовать Excel , если и И функции с Условное форматирование , чтобы увидеть, какие продукты находятся в диапазоне сумм 1200-2800 .

ШАГ 1:

  • Сначала выберите диапазон ячеек C5:C9 .
  • Теперь перейдите на вкладку Home .
  • Выберите раскрывающийся список Условное форматирование .
  • Нажмите на опцию Новое правило .

ШАГ 2:

  • В окне Новое правило форматированияB выберите параметр « Использовать формулу для определения форматируемых ячеек ».
  • В поле формулы введите формулу:

=ЕСЛИ(И(C5>1200,C5<2800),ИСТИНА,ЛОЖЬ)

  • Выберите определенный цвет, как мы видели в приведенных выше процедурах, из параметра Формат .
  • Далее нажмите OK .

🔎 Как работает формула?

  • AND(C5>1200,C5<2800): Это вернет TRUE , если ячейка C5 больше 1200 или меньше 2800 .
  • ЕСЛИ(И(C5>1200,C5<2800),ИСТИНА,ЛОЖЬ): Это вернет ИСТИНА , если ячейка C5 находится в диапазоне 1200-2800 , иначе ЛОЖЬ .

ШАГ 3:

  • Наконец, мы видим, что ячейки выделены.

Подробнее: Как использовать условное форматирование в Excel [Полное руководство]

Заключение

Это быстрые методы формул условного форматирования с IF в Excel. Добавлена ​​рабочая тетрадь. Давай, попробуй. Не стесняйтесь спрашивать что-нибудь или предлагать любые новые методы.

Дополнительные материалы

  • Условное форматирование с формулой для нескольких условий в Excel
  • Как найти наибольшее значение в столбце Excel (4 метода)
  • 4 Быстрая формула Excel для изменения цвета ячейки на основе даты
  • Как сделать отрицательные числа красными в Excel (4 простых способа)
  • Как сравнить два столбца в Excel для поиска различий

Как использовать функцию ЕСЛИ с датами в Microsoft Excel


|

В Microsoft Excel присутствует множество функций.

Одной из этих функций является функция ЕСЛИ.

Он использует условия проверки и возвращает одно значение, независимо от того, является ли оно ИСТИНА или ЛОЖЬ.

Сравнение значений является одним из наиболее распространенных применений функций ЕСЛИ.

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

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

Синтаксис и использование функции ЕСЛИ

Формула синтаксиса функции ЕСЛИ:

ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

Вот расшифровка формулы:

  •  логический_текст — критерии, которые мы хотим проверить с помощью функции ЕСЛИ.
  • value_if_true — значение, которое вы хотите, чтобы тест возвращал, если logical_test имеет значение TRUE.
  • value_if_true — значение, которое вы хотите, чтобы тест возвращал, если logical_test имеет значение FALSE.

В нашем примере, если вы хотите, чтобы значение, равное точно 100, находилось в ячейке A3, тогда значение будет отображаться как «ДА».

Но если значение больше или меньше 100, то в ячейке B2 отображается значение «НЕТ». Вы можете увидеть наш пример на изображении ниже:

=ЕСЛИ(A3=100,"да","нет")

Сравнение дат в Microsoft Excel

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

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

или позже

Знак Обозначение
< предшествует
= Равно
>  После
<= То же, что и ранее
=> То же, что и

Как использовать функцию ЕСЛИ с датами

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

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

Если вы проверяете определенную дату, например «>01.12.2022», с помощью функции ЕСЛИ, она просто увидит ее как текст, а не дату.

Ниже приведены примеры использования функции ЕСЛИ для значения даты в качестве параметров логического_теста.

При использовании функции ЕСЛИ с функцией ЗНАЧ-ДАТА

Если вы используете значение даты в качестве логической проверки в вашей функции ЕСЛИ, вам нужно будет использовать функцию ЗНАЧ-ДАТА. Используя эту функцию, вы конвертируете текстовый формат даты в формат серийного номера. Теперь Excel распознает его как дату.

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

В нашем примере у вас есть заданная дата (02.02.1995), расположенная в ячейке A2, и вы хотите указать «выполнено» или «не выполнено» в ячейке B2.

Вам нужно будет использовать функцию ЕСЛИ, объединенную с функцией ЗНАЧ ДАННЫХ в B2. Вот формула, которую мы будем использовать:

9069.1

=ЕСЛИ(A2<ДАТАЗНАЧ("01.12.2022″),"выполнено","не выполнено")

Вот пример этого сценария:

С помощью функции ЕСЛИ с функцией СЕГОДНЯ

Если вам нужно использовать текущую дату и сравнить ее с определенной датой, функция СЕГОДНЯ очень полезна и объединяет ее с функцией ЕСЛИ.

В нашем примере у вас есть заданная дата (01.12.2022), расположенная в ячейке A2, и вы хотите указать «выполнено» или «не выполнено» в ячейке B2 и в то же время сравнить ее с текущей датой. Вам нужно будет использовать функцию ЕСЛИ, объединенную с функцией СЕГОДНЯ в B2. Вот формула, которую мы будем использовать:

=ЕСЛИ(A2<СЕГОДНЯ(), "выполнено", "не выполнено")

Вот пример этого сценария, где текущая дата — 01. 12.2022:

Использование функции ЕСЛИ с будущими или прошлыми датами

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

Каждый последующий день характеризуется одним целым числом.

Таким образом, 02 января 1900 года соответствует серийному номеру 2.

Добавление n цифр к дате аналогично n цифрам серийных номеров.

Если СЕГОДНЯ() равно 01.12.2022, то СЕГОДНЯ()+15 будет на пятнадцать дней вперед от текущей даты (16.12.2022).

Таким образом, TODAY()-15 отстает на пятнадцать дней от текущей даты (16.11.2022).

В нашем примере у вас есть заданная дата (01.12.2022), расположенная в ячейке A2, и вы хотите указать «в пределах диапазона», если она на 15 дней раньше текущей даты в ячейке B2. Если нет, то в этой ячейке должно быть указано «вне диапазона».

Вам потребуется использовать функцию ЕСЛИ, объединенную с функцией СЕГОДНЯ в B2. Вот формула, которую мы будем использовать:

=ЕСЛИ(A2<СЕГОДНЯ()+15, «в пределах диапазона», «вне диапазона»)

Вот пример этого сценария, где текущая дата — 01.12.2022:

Ключевые моменты:

Вот некоторые примечания, которые вам необходимо знать об использовании функции ЕСЛИ:

  1. В качестве альтернативы ручному вводу дат при использовании параметров логического теста функций ЕСЛИ вы можете записать даты в отдельную ячейку и указать их в своих формулах. В качестве замены для =ЕСЛИ(A2<"01.12.2022","сделано","не сделано"), вы можете просто записать дату 01.12.2022 в отдельную ячейку, выбрать ячейку B2 и ввести формулу: =IF(A2
  2. Как и в объяснении первой части руководства, вы также можете использовать функцию ЗНАЧ ДАННЫХ.
  3. Просто добавив ноль к дате, заключенной в круглые скобки, вы можете сделать свою формулу аккуратной. Просто введите: =IF(A2<"01.

Читайте также: