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

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

Некоторые вещи в мире конечны. Другие бесконечны, и функция ЕСЛИ кажется одной из таких вещей. В нашем блоге уже есть несколько учебных пособий по Excel IF, и мы по-прежнему каждый день находим новые способы их использования. Сегодня мы рассмотрим, как можно использовать ЕСЛИ вместе с функцией И для одновременной оценки двух или более условий.

Оператор ЕСЛИ И в Excel

Чтобы построить оператор ЕСЛИ И, вам, очевидно, нужно объединить функции ЕСЛИ и И в одной формуле. Вот как:

ЕСЛИ(И(условие1, условие2,…), значение_если_истина, значение_если_ложь)

В переводе на простой язык формула выглядит следующим образом: ЕСЛИ верно условие 1 И верно условие 2, сделайте одно, в противном случае сделайте что-нибудь другое.

В качестве примера создадим формулу, которая проверяет, «доставлен» ли B2 и не является ли C2 пустым, и в зависимости от результатов выполняет одно из следующих действий:

• Если оба условия ИСТИННЫ, помечаем ордер как «Закрытый».
• Если одно из условий равно FALSE или оба условия FALSE, то возвращается пустая строка (“”).

=ЕСЛИ(И(B2=”доставлено”, C2<>“”), “Закрыто”, “”)

На снимке экрана ниже показана функция ЕСЛИ И в Excel:

Если вы хотите вернуть какое-то значение в случае, если логический тест дает FALSE, укажите это значение в значение_если_ложь аргумент. Например:

=ЕСЛИ(И(B2=”доставлено”, C2<>“”), “Закрыто”, “Открыто”)

Измененная формула выводит «Закрыто», если столбец B «доставлен» и C содержит любую дату (непустую). Во всех остальных случаях возвращает «Открыть»:

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

Теперь, когда вы знаете синтаксис оператора Excel IF AND, позвольте мне показать вам, какие задачи он может решить.

Excel ЕСЛИ: больше И меньше

В предыдущем примере мы тестировали два условия в двух разных ячейках. Но иногда вам может понадобиться запустить два или более тестов в одной и той же ячейке. Типичным примером является проверка того, находится ли значение ячейки между двумя числами. Функция Excel ЕСЛИ И тоже может легко сделать это!

Предположим, у вас есть данные о продажах в столбце B, и вас просят отметить суммы, превышающие 50 долларов США, но менее 100 долларов США. Чтобы это сделать, вставьте эту формулу в C2, а затем скопируйте ее вниз по столбцу:

=ЕСЛИ(И(В2>50, В2<100), "х", "")

Если вам нужно включить граничные значения (50 и 100), используйте меньше или равно оператор (<=) и больше или равно (>=) оператор:

=ЕСЛИ(И(B2>=50, B2<=100), "х", "")

Чтобы обработать некоторые другие граничные значения без изменения формулы, введите минимальное и максимальное значения в две отдельные ячейки и обратитесь к этим ячейкам в формуле. Чтобы формула корректно работала во всех строках, обязательно используйте абсолютные ссылки для граничных ячеек (в нашем случае $F$1 и $F$2):

=ЕСЛИ(И(B2>=$F$1, B2<=$F$2), "x", "")

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

Например, давайте отметим даты с 10 сентября 2018 года по 30 сентября 2018 года включительно. Небольшое препятствие заключается в том, что даты не могут быть предоставлены логическим тестам напрямую. Чтобы Excel понимал даты, они должны быть заключены в функцию ДАТАЗНАЧ, например:

=ЕСЛИ(И(B2>=ДАТАЗНАЧ(“10.09.2018”), B2<=ДАТАЗНАЧ("30.09.2018")), "x", "")

Или просто введите Из а также К даты в двух ячейках ($F$1 и $F$2 в этом примере) и «вытягивать» их из этих ячеек с помощью уже знакомой формулы ЕСЛИ И:

=ЕСЛИ(И(B2>=$F$1, B2<=$F$2), "x", "")

Для получения дополнительной информации см. оператор Excel IF между двумя числами или датами.

ЕСЛИ это И то, то вычислить что-то

Помимо возврата предопределенных значений, функция Excel ЕСЛИ И также может выполнять различные вычисления в зависимости от того, являются ли заданные условия ИСТИНА или ЛОЖЬ.

Чтобы продемонстрировать подход, мы будем рассчитывать бонус в размере 5% для «Закрытых» продаж на сумму, превышающую или равную 100 долларам США.

Предполагая, что сумма находится в столбце B, а статус заказа в столбце C, формула выглядит следующим образом:

=ЕСЛИ(И(B2>=100, C2=”закрыто”), B2*10%, 0)

Приведенная выше формула присваивает нуль остальным заказам (значение_если_ложь = 0). Если вы готовы давать небольшой стимулирующий бонус, скажем, 3%, к заказам, не соответствующим условиям, включите соответствующее уравнение в значение_если_ложь аргумент:

=ЕСЛИ(И(B2>=100, C2=”закрыто”), B2*10%, B2*3%)

Несколько операторов ЕСЛИ И в Excel

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

• В Excel 2007 и более поздних версиях в формуле можно использовать до 255 аргументов, а общая длина формулы не превышает 8 192 символов.
• В Excel 2003 и более ранних версиях допускается не более 30 аргументов общей длиной не более 1024 символов.

В качестве примера нескольких условий И рассмотрим следующие:

• Сумма (B2) должна быть больше или равна 100 долларов США.
• Статус заказа (C2) “Закрыт”
• Дата доставки (D2) в текущем месяце

Теперь нам нужен оператор IF AND, чтобы определить заказы, для которых все 3 условия ИСТИННЫ. И вот оно:

=ЕСЛИ(И(B2>=100, C2=”Закрыто”, МЕСЯЦ(D2)=МЕСЯЦ(СЕГОДНЯ())), “x”, “”)

Учитывая, что «текущий месяц» на момент написания был октябрь, формула дает следующие результаты:

Вложенные операторы IF AND

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

ЕСЛИ(И(…), выход1ЕСЛИ(И(…), выход2ЕСЛИ(И(…), выход3, выход4)))

Чтобы получить общее представление, посмотрите на следующий пример.

Предположим, вы хотите оценить свою услугу на основе стоимости доставки и расчетного времени доставки (ETD):

• Превосходно: стоимость доставки менее 20 долларов США и ETD менее 3 дней
• Бедный: стоимость доставки более 30 долларов США и ETD более 5 дней
• Средний: что-то между

Чтобы это сделать, вы пишете два отдельных оператора IF AND:

ЕСЛИ(И(B2<20, C2<3), "Отлично", …)

ЕСЛИ(И(B2>30, C2>5), “Плохо”, …)

…и вложить одно в другое:

=ЕСЛИ(И(B2>30, C2>5), “Плохо”, ЕСЛИ(И(B2<20, C2<3), "Отлично", "Средне"))

Результат будет выглядеть примерно так:

Дополнительные примеры формул можно найти во вложенных операторах IF AND Excel.

Функция ЕСЛИ И с учетом регистра в Excel

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

Если вы работаете с данными, чувствительными к регистру, и хотите оценить условия И с учетом регистра текста, выполните каждый отдельный логический тест внутри функции EXACT и вложите эти функции в свой оператор AND:

ЕСЛИ(И(ТОЧНО(клетка“условие1“), ТОЧНЫЙ(клетка“условие2“)), значение_если_истина, значение_если_ложь)

В этом примере мы будем отмечать заказы конкретного клиента (например, компании с именем Киберпространство) на сумму, превышающую определенное число, скажем, 100 долларов.

Как вы можете видеть на снимке экрана ниже, некоторые названия компаний в столбце B выглядят одинаково, если не считать регистр символов, и, тем не менее, это разные компании, поэтому мы должны точно проверить названия. Суммы в столбце C являются числами, и мы запускаем для них обычный тест «больше чем»:

=ЕСЛИ(И(ТОЧНО(B2, “Киберпространство”), C2>100), “x”, “”)

Чтобы сделать формулу более гибкой, вы можете ввести имя и сумму целевого клиента в две отдельные ячейки и ссылаться на эти ячейки. Только не забудьте заблокировать ссылки на ячейки знаком $ (в нашем случае $G$1 и $G$2), чтобы они не изменились при копировании формулы в другие строки:

=ЕСЛИ(И(ТОЧНО(B2, $G$1), C2>$G$2), “x”, “”)

Теперь вы можете ввести любое имя и сумму в указанных ячейках, и формула отметит соответствующие заказы в вашей таблице:

ЕСЛИ ИЛИ И формула в Excel

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

Предположим, вы хотите пометить заказы двух клиентов на сумму, превышающую определенное число, скажем, 100 долларов.

На языке Excel наши условия выражаются следующим образом:

ИЛИ(И(Клиент1, Количество>100), И(Клиент2, Количество>100)

Предполагая, что имена клиентов находятся в столбце B, суммы в столбце C, 2 целевых имени находятся в G1 и G2, а целевая сумма находится в G3, вы используете эту формулу, чтобы пометить соответствующие заказы знаком «x»:

=ЕСЛИ(ИЛИ(И(B2=$G$1, C2>$G$3), И(B2=$G$2, C2>$G$3)), “x”, “”)

Те же результаты могут быть достигнуты с более компактным синтаксисом:

=ЕСЛИ(И(ИЛИ(B2=$G$1,B2=$G$2), C2>$G$3), “x”, “”)

Не уверены, что полностью понимаете логику формулы? Дополнительную информацию можно найти в Excel ЕСЛИ с несколькими условиями И/ИЛИ.

Вот как вы используете функции ЕСЛИ и И вместе в Excel. Спасибо за чтение и увидимся на следующей неделе!

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

ЕСЛИ И Excel – примеры формул (файл .xlsx)

Вас также могут заинтересовать