Excel условия в формулах: Использование ЕСЛИ с функциями И, ИЛИ и НЕ

Использование операторов строк и подстановочных знаков

В формулах можно использовать оператор строки (&), чтобы сцепить или объединить две строки или несколько строк либо содержимое ячеек, на которые указывают ссылки. Подстановочные знаки (*, ?, ~) можно использовать в условиях для замены одного или нескольких символов.

Сцепление строк или содержимого ячеек

В формулах можно использовать символ &, чтобы сцепить или объединить две строки или несколько строк либо содержимое ячеек, на которые указывают ссылки.

Ниже приведено несколько примеров использования оператора сцепления.

  • «Абв»&»Где» возвращает «АбвГде».

  • «Абв»&A1 возвращает «Абв2», если ячейка A1 содержит 2.

  • A1&A2 возвращает «12», если ячейка A1 содержит 1, а ячейка A2 содержит 2.

  • B2&»; «&E2 возвращает «Последний; Первый», если ячейка B2 содержит «Последний», а ячейка E2 содержит «Первый».

Использование подстановочных знаков для подстанвоки одиночных символов

Символ «?» используется для подстановки одиночного символа в выражении, допускающем применение условий.

Ниже приведено несколько примеров использования символа «?» в различных вариантах подстановки.

  • «Ка?» представляет любую строку, которая начинается с «Ка» и содержит ровно один дополнительный символ, например «Ка2» или «Каж».

  • «Эт??» представляет любую строку, которая начинается с «Эт» и содержит ровно два дополнительных символа, например «Этих» или «Этим».

  • СЧЕТЕСЛИ(B2:E7;»?ол») возвращает количество ячеек в диапазоне B2:E7, содержащих значение, которое начинается с одиночного символа, после которого следуют символы «ол», например «кол» и «вол». Такие слова, как «скол» или «ствол», не соответствуют этому условию.

Использование подстановочных знаков для подстановки любого количества символов

Символ «*» используется для подстановки любого, в том числе пустого, набора символов в выражении, допускающем применение условий.

Ниже приведено несколько примеров использования подстановочного знака «*» в различных вариантах подстановки.

  • «*ег» представляет строку любой длины, оканчивающуюся на «ег», например «бег» или «снег».

  • СЧЕТЕСЛИ(B2:E7;»*ек») возвращает количество ячеек в диапазоне B2:E7, содержащих значение, которое оканчивается на «ек», например «человек» или «век». Слово «веко» не соответствует этому условию.

Полное соответствие подстановочному символу

Символ «~» указывает на то, что следующий за ним символ в выражении, допускающем применение условий, не является подстановочным знаком, а соответствует только собственному значению.

Ниже приведено несколько примеров использования символа «~» в различных вариантах подстановки.

  • «~?» представляет вопросительный знак, а не подстановочный символ, используемый для подстановки любого одиночного символа.

  • СЧЕТЕСЛИ(E;»~*») возвращает количество ячеек в столбце E, содержащих символ звездочки.

  • ПОИСК(«~?»;B2) возвращает 11, если ячейка B2 содержит «Это вопрос? Да!», поскольку вопросительный знак является 11‑м символом в строке.

Использование нескольких подстановочных символов в условии

Подстановочные символы (?, *, ~) могут использоваться вместе в выражениях, допускающих применение условий. Ниже приведено несколько примеров.

  • «*а?» представляет любое выражение, содержащее символ «а», за которым следует любой другой одиночный символ, например «смак», «шар» и «кочан».

  • СЧЕТЕСЛИ(B2:E7;»*од?») возвращает количество ячеек в диапазоне B2:E7, содержащих значение, которое начинается с любого набора символов (в том числе пустого), после которого следуют символы «од», а затем одиночный символ. Этому сочетанию символов соответствуют слова «вода», «колода», «одр» и «вроде». При этом слова «лодка» или «провод» не соответствуют данному условию, так как в первом случае после сочетания «од» следуют два символа, а во втором случае после сочетания «од» нет ни одного символа.

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

См. такжеОбщие сведения о функцияхТипы аргументов и значенийСписок функций по категориямФункции, поддерживающие условия и подстановочные символы в качестве аргументов

Как задать простое логическое условие в Excel




Перейти к содержанию





Search for:

Главная » Уроки MS Excel







Автор Антон Андронов На чтение 2 мин Опубликовано



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

  • 5 больше 8?
  • Содержимое ячейки A5 меньше 8?
  • А может равно 8?

Операторы сравнения в Excel

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

Как задать условие в Excel

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

=A1=B1 — Данное условие вернет ИСТИНА, если значения в ячейках A1 и B1 равны, или ЛОЖЬ в противном случае. Задавая такое условие, можно сравнивать текстовые строки без учета регистра. К примеру, сравнивая «ЯНВАРЬ» и «январь» формула возвратит ИСТИНА.

=A1>B1 — Следующая формула возвратит ИСТИНА, если значение ячейки А1 больше, чем в B1. В противном случае формула вернет ЛОЖЬ. Такие сравнения можно задавать и при работе с текстом.

Например, если в ячейке A1 хранится значение «Апельсин», а в B1 – «Арбуз», то формула вернет ЛОЖЬ, поскольку в алфавитном порядке «Арбуз» находится ниже, чем «Апельсин». Чем ниже, тем больше.

=A1<=B1 — Формула вернет ИСТИНА, если значение ячейки A1 меньше или равно значению в ячейке B1. Иначе результатом будет ЛОЖЬ.

=A1<>B1 — Формула вернет ИСТИНА, если значения ячеек A1 и B1 не равны. В противном случае – ЛОЖЬ.

В Excel существуют логические функции ИСТИНА() и ЛОЖЬ(), которые не имеют аргументов. Данные функции существуют в основном для обеспечения совместимости с другими электронными таблицами. Вы можете вводить значения ИСТИНА и ЛОЖЬ прямо в ячейки или формулы, не используя форму записи функции, Excel все прекрасно поймет.

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

Оцените качество статьи. Нам важно ваше мнение:






Adblock
detector

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Теперь, когда вы знаете синтаксис оператора ЕСЛИ И, позвольте мне показать вам, какие задачи он может решать.

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

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

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

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

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

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

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

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

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

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

.

=ЕСЛИ(И(B2>=ДАТАЗНАЧ("9/10/2018"), B2<=DATEVALUE("30.09.2018")), "x", "")

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

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

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

ЕСЛИ это И то, то посчитайте что-нибудь

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

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

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

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

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

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

Множественные операторы IF AND в 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 и расширяете ее логические тесты операторами И, например:0005

ЕСЛИ(И(…), выход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 ")), значение_если_истина, значение_если_ложь)

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

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

.

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

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

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

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

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

Предположим, вы хотите пометить заказы двух клиентов на сумму, превышающую определенное число, скажем, 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)

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

Как объединить функции ЕСЛИ и ИЛИ в Excel [Совет по формулам]

Главная / Формулы Excel / Как объединить функции ЕСЛИ и ИЛИ в Excel

Краткое введение

Как работают функции ЕСЛИ и ИЛИ?

Примеры

Загрузить образец файла

Последние слова

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

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

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

Краткое введение

Я уверен, что вы использовали обе эти функции, но позвольте мне дать вам краткое введение.

  • IF — используйте эту функцию для проверки состояния. Он вернет определенное значение, если это условие истинно, или другое конкретное значение, если это условие ложно.
  • ИЛИ — проверить несколько условий. Он вернет true, если какое-либо из этих условий истинно, и false, если все эти условия ложны.

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

Как работают функции ЕСЛИ и ИЛИ?

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

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

Затем он возвращает значение на основе результата этого условия. Теперь, если мы используем функцию ИЛИ для этого аргумента и указываем для него несколько условий.

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

Примеры

Здесь у меня есть таблица со сведениями о запасах двух складов. Теперь дело в том, что я хочу обновить статус в таблице.

При отсутствии товара на обоих складах статус должен быть «Нет в наличии».

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