вложенная формула, несколько операторов и многое другое
В учебнике показано, как использовать ЕСЛИ вместе с функцией И в 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)