Как писать формулы в эксель с несколькими условиями примеры
Функция ЕСЛИ в Excel с примерами нескольких условий
Логический оператор ЕСЛИ в Excel применяется для записи определенных условий. Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.
Логические функции – это очень простой и эффективный инструмент, который часто применяется в практике. Рассмотрим подробно на примерах.
Синтаксис функции ЕСЛИ с одним условием
Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.
=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)
Разберем синтаксис функции:
Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).
Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).
Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).
Пример:
Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».
Внимание! Слова в формуле необходимо брать в кавычки. Чтобы Excel понял, что нужно выводить текстовые значения.
Еще один пример. Чтобы получить допуск к экзамену, студенты группы должны успешно сдать зачет. Результаты занесем в таблицу с графами: список студентов, зачет, экзамен.
Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.
Функция ЕСЛИ в Excel с несколькими условиями
Часто на практике одного условия для логической функции мало. Когда нужно учесть несколько вариантов принятия решений, выкладываем операторы ЕСЛИ друг в друга. Таким образом, у нас получиться несколько функций ЕСЛИ в Excel.
Синтаксис будет выглядеть следующим образом:
=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))
Здесь оператор проверяет два параметра.
Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.
Примеры несколько условий функции ЕСЛИ в Excel:
Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.
В этом примере мы добавили третье условие, подразумевающее наличие в табеле успеваемости еще и «двоек». Принцип «срабатывания» оператора ЕСЛИ тот же.
Расширение функционала с помощью операторов «И» и «ИЛИ»
Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.
Функции И и ИЛИ могут проверить до 30 условий.
Пример использования оператора И:
Пример использования функции ИЛИ:
Как сравнить данные в двух таблицах
Пользователям часто приходится сравнить две таблицы в Excel на совпадения.
Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.
Чтобы сравнить 2 таблицы в Excel, можно воспользоваться оператором СЧЕТЕСЛИ. Рассмотрим порядок применения функции.
Для примера возьмем две таблицы с техническими характеристиками разных кухонных комбайнов. Мы задумали выделение отличий цветом. Эту задачу в Excel решает условное форматирование.
Исходные данные (таблицы, с которыми будем работать):
Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:
В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.
Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.
Выбираем формат и устанавливаем, как изменятся ячейки при соблюдении формулы. Лучше сделать заливку цветом.
Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).
Скачать все примеры функции ЕСЛИ в Excel
Здесь вместо первой и последней ячейки диапазона мы вставили имя столбца, которое присвоили ему заранее. Можно заполнять формулу любым из способов. Но с именем проще.
ЕСЛИ (функция ЕСЛИ) - Служба поддержки Майкрософт
Функция ЕСЛИ — одна из самых популярных функций в Excel. Она позволяет выполнять логические сравнения значений и ожидаемых результатов.
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Например, функция =ЕСЛИ(C2="Да";1;2) означает следующее: ЕСЛИ(С2="Да", то вернуть 1, в противном случае вернуть 2).
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Например:
|
Имя аргумента |
Описание |
|---|---|
|
лог_выражение (обязательно) |
Условие, которое нужно проверить. |
|
значение_если_истина (обязательно) |
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА. |
|
значение_если_ложь (необязательно) |
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ. |
Простые примеры функции ЕСЛИ
-
=ЕСЛИ(C2="Да";1;2)
В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)
В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст "Да", в противном случае вернуть текст "Нет"). Как видите, функцию ЕСЛИ можно использовать для сравнения и текста, и значений. А еще с ее помощью можно оценивать ошибки. Вы можете не только проверять, равно ли одно значение другому, возвращая один результат, но и использовать математические операторы и выполнять дополнительные вычисления в зависимости от условий. Для выполнения нескольких сравнений можно использовать несколько вложенных функций ЕСЛИ.
В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст "Превышение бюджета", в противном случае вернуть текст "В пределах бюджета")
На рисунке выше мы возвращаем не текст, а результат математического вычисления.
Формула в ячейке E2 означает: ЕСЛИ(значение "Фактические" больше значения "Плановые", то вычесть сумму "Плановые" из суммы "Фактические", в противном случае ничего не возвращать).
В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = "Да", то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)
Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: "Текст"). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.
Распространенные неполадки
|
Проблема |
Возможная причина |
|---|---|
|
0 (ноль) в ячейке |
Не указан аргумент значение_если_истина или значение_если_ложь. |
|
#ИМЯ? в ячейке |
Как правило, это указывает на ошибку в формуле. |
Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
См. также
Функция ЕСЛИ — вложенные формулы и типовые ошибки
Функция УСЛОВИЯ
Использование ЕСЛИ с функциями И, ИЛИ и НЕ
СЧЁТЕСЛИ
Рекомендации, позволяющие избежать появления неработающих формул
Общие сведения о формулах в Excel
Функция ЕСЛИ в Excel с несколькими условиями
В этом учебном пособии показано, как создать несколько операторов ЕСЛИ в Excel с логикой И и ИЛИ.
Кроме того, вы узнаете, как использовать ЕСЛИ вместе с другими функциями Excel.
В первой части нашего учебника по Excel IF мы рассмотрели, как создать простой оператор IF с одним условием для текста, чисел, дат, пробелов и непустых значений. Однако для мощного анализа данных вам часто может потребоваться оценка нескольких условий одновременно. Приведенные ниже примеры формул покажут вам наиболее эффективные способы сделать это.
Как использовать функцию ЕСЛИ с несколькими условиями
В сущности, существует два типа формулы ЕСЛИ с несколькими критериями, основанными на логике И/ИЛИ . Следовательно, при логической проверке вашей формулы ЕСЛИ вы должны использовать одну из следующих функций:
- Функция И - возвращает ИСТИНА, если все условия соблюдены; ЛОЖЬ в противном случае.
- Функция ИЛИ - возвращает ИСТИНА, если выполняется любое одно условие ; ЛОЖЬ в противном случае.
Чтобы лучше проиллюстрировать это, давайте рассмотрим некоторые примеры формул из реальной жизни.
Оператор ЕСЛИ в Excel с несколькими условиями (логика И)
Общая формула ЕСЛИ в Excel с двумя или более условиями:
В переводе на человеческий язык формула гласит: Если условие 1 истинно И условие 2 истинно, вернуть значение_если_истина ; иначе вернуть значение_если_ложь .
Предположим, у вас есть таблица, в которой перечислены результаты двух тестов в столбцах B и C. Чтобы сдать выпускной экзамен, учащийся должен иметь оба балла выше 50.
Для логического теста вы используете следующий оператор AND: AND (B2>50, C2>50)
Если оба условия верны, формула вернет «Пройдено»; если какое-либо условие ложно - "Fail".
=ЕСЛИ(И(B2>50, B2>50), "Пройдено", "Не пройдено")
Легко, не правда ли? Скриншот ниже доказывает, что наша формула Excel IF/AND работает правильно:
Аналогичным образом можно использовать функцию ЕСЛИ в Excel с несколькими текстовыми условиями .
Например, чтобы вывести «Хорошо», если и B2, и C2 больше 50, и «Плохо» в противном случае, формула выглядит следующим образом: «Хорошо!», «Плохо»)
Важное примечание! Функция И проверяет все условия , даже если уже проверенные условия оценены как ЛОЖЬ.
Такое поведение немного необычно, поскольку в большинстве языков программирования последующие условия не проверяются, если какой-либо из предыдущих тестов вернул FALSE.
На практике кажущийся правильным оператор IF может привести к ошибке из-за этой специфики. Например, приведенная ниже формула вернет #DIV/0! (ошибка "делить на ноль"), если ячейка A2 равна 0:
=ЕСЛИ(И(A2<>0, (1/A2)>0,5),"Хорошо","Плохо")
во избежание этого следует использовать вложенную функцию ЕСЛИ:
=ЕСЛИ(A2<>0, ЕСЛИ((1/A2)>0,5, "Хорошо", "Плохо"), "Плохо")
Подробнее информацию см. в формуле ЕСЛИ И в Excel.
Excel Функция ЕСЛИ с несколькими условиями (логика ИЛИ)
Чтобы сделать что-то одно, если выполняется любое условие , в противном случае сделать что-то еще, используйте эту комбинацию функций ЕСЛИ и ИЛИ:
ЕСЛИ(ИЛИ( условие1 , условие2 , …), значение_если_истина, значение_если_ложь)
Отличие от рассмотренной выше формулы ЕСЛИ/И состоит в том, что Excel возвращает ИСТИНА, если любое из указанных условий истинно.
Итак, если в предыдущей формуле использовать ИЛИ вместо И:
=ЕСЛИ(ИЛИ(B2>50, B2>50), "зачетно", "незачетно")
Тогда любой, кто наберет более 50 баллов на любом из экзаменов, получит "зачет" в столбце D. При таком условиях наши ученики имеют больше шансов сдать выпускной экзамен (особенно не повезло Иветте, которая провалилась всего на 1 балл :)
Совет. Если вы создаете множественный оператор ЕСЛИ с текстом и проверяете значение в одной ячейке с помощью логики ИЛИ (т. е. ячейка может быть «этой» или «той»), вы можете построить более компактную формулу, используя массив постоянный.
Например, чтобы пометить продажу как «закрытую», если ячейка B2 либо «доставлена», либо «оплачена», формула выглядит следующим образом:
=ЕСЛИ(ИЛИ(B2={"доставлено", "оплачено"}) , "Закрыто", "")
Дополнительные примеры формул можно найти в функции Excel ЕСЛИ ИЛИ.
ЕСЛИ с несколькими операторами И и ИЛИ
Если ваша задача требует оценки нескольких наборов множественных условий, вам придется использовать обе функции И и ИЛИ одновременно.
Предположим, что в нашей примерной таблице у вас есть следующие критерии для проверки результатов экзамена:
- Условие 1: экзамен1>50 и экзамен2>50
- Условие 2: экзамен1>40 и экзамен2>60
При выполнении любого из условий итоговый экзамен считается сданным.
На первый взгляд формула кажется немного хитрой, но на самом деле это не так! Вы просто выражаете каждое из вышеперечисленных условий как оператор И и вкладываете их в функцию ИЛИ (поскольку нет необходимости выполнять оба условия, достаточно любого из них):
ИЛИ(И(B2>50, C2>50), И(В2>40, С2>60)
Затем используйте функцию ИЛИ для логической проверки ЕСЛИ и укажите нужные значения value_if_true и value_if_false . В результате вы получите следующую формулу ЕСЛИ с несколькими условиями И/ИЛИ:
=ЕСЛИ(ИЛИ(И(B2>50, C2>50), AND(B2>40, C2>60), "Pass" , "Fail")
Скриншот ниже показывает, что мы сделали формулу правильно:
Естественно, вы не ограничены использованием только двух функций И/ИЛИ в формулах ЕСЛИ.
Вы можете использовать их столько, сколько требует ваша бизнес-логика, при условии, что:
- В Excel 2007 и выше у вас не более 255 аргументов, а общая длина формулы ЕСЛИ не превышает 8 192 символов.
- В Excel 2003 и более ранних версиях не более 30 аргументов, а общая длина формулы ЕСЛИ не превышает 1024 символов.
Вложенный оператор IF для проверки нескольких логических тестов
Если вы хотите оценить несколько логических тестов в рамках одной формулы, вы можете вложить несколько функций одну в другую. Такие функции называются вложенные функции ЕСЛИ . Они оказываются особенно полезными, когда вы хотите вернуть разные значения в зависимости от результатов логических тестов.
Вот типичный пример: предположим, вы хотите квалифицировать достижения учащихся как « Хорошо », « Удовлетворительно » и « Плохо » на основе следующих баллов:
- Хорошо: 60 или более (> =60)
- Удовлетворительно: от 40 до 60 (>40 и <60)
- Плохо: 40 или меньше (<=40)
Прежде чем писать формулу, подумайте о порядке вложенных функций.
Excel будет оценивать логические тесты в том порядке, в котором они появляются в формуле. Как только условие оценивается как ИСТИНА, последующие условия не проверяются, то есть формула останавливается после первого результата ИСТИНА.
В нашем случае функции расположены от большего к меньшему:
=ЕСЛИ(В2>=60, "Хорошо", ЕСЛИ(В2>40, "Удовлетворительно", "Плохо"))
Естественно, при необходимости вы можете вложить больше функций (до 64 в современных версиях).
Дополнительные сведения см. в разделе Использование нескольких вложенных операторов IF в Excel.
Формула массива ЕСЛИ Excel с несколькими условиями
Другой способ получить ЕСЛИ Excel для проверки нескольких условий — использовать формулу массива.
Чтобы оценить условия с помощью логики И, используйте звездочку:
ЕСЛИ( условие1 ) * ( условие2 ) * …, значение_если_истина, значение_если_ложь)
Чтобы проверить условия с помощью логики ИЛИ, используйте знак плюс:
ЕСЛИ( условие1 ) + ( условие2 ) + …, значение_если_истина, значение_если_ложь)
Чтобы правильно заполнить формулу массива, нажмите одновременно клавиши Ctrl + Shift + Enter. В Excel 365 и Excel 2021 это также работает как обычная формула благодаря поддержке динамических массивов.
Например, чтобы получить "Пройдено", если и B2, и C2 больше 50, используйте следующую формулу:
=ЕСЛИ((B2>50) * (C2>50), "Пройдено", "Не пройдено")
В моем Excel 365 обычная формула работает нормально (как вы можете видеть на скриншотах выше). В Эксель 2019и ниже, не забудьте сделать его формулой массива, используя сочетание клавиш Ctrl + Shift + Enter.
Для оценки нескольких условий с помощью логики ИЛИ используется следующая формула:
=ЕСЛИ((B2>50) + (C2>50), «Удачно», «Не пройдено»)
Использование ЕСЛИ вместе с другими функциями
В этом разделе объясняется, как использовать ЕСЛИ в сочетании с другими функциями Excel и какие преимущества это дает.
Пример 1. Если ошибка #Н/Д в ВПР
Когда функция ВПР или другая функция поиска не могут что-либо найти, она возвращает ошибку #Н/Д. Чтобы ваши таблицы выглядели лучше, вы можете вернуть нулевой, пустой или конкретный текст, если #N/A. Для этого используйте эту общую формулу:
IF(ISNA(VLOOKUP(…)), value_if_na , VLOOKUP(…))
Например:
Если #N/A возвращает 0:
Если искомое значение в E1 не найдено, формула возвращает ноль.
=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ)), 0, ВПР(E1, A2:B10, 2, ЛОЖЬ))
Если #Н/Д, вернуть пустое значение:
Если искомое значение не найдено, формула ничего не возвращает (пустая строка).
=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ )), "", ВПР(E1, A2:B10, 2, ЛОЖЬ))
Если #Н/Д вернуть определенный текст:
Если искомое значение не найдено, формула возвращает определенный текст.
=ЕСЛИ(ИСНА(ВПР(E1, A2:B10, 2,ЛОЖЬ )), "Не найдено", ВПР(E1, A2:B10, 2, ЛОЖЬ))
Дополнительные примеры формул см. в разделе ВПР с оператором ЕСЛИ в Excel.
Пример 2. ЕСЛИ с функциями СУММ, СРЗНАЧ, МИН и МАКС
Для суммирования значений ячеек на основе определенных критериев в Excel предусмотрены функции СУММЕСЛИ и СУММЕСЛИМН.
В некоторых ситуациях ваша бизнес-логика может потребовать включения функции СУММ в логическую проверку ЕСЛИ. Например, чтобы вернуть разные текстовые метки в зависимости от суммы значений в ячейках B2 и C2, используйте следующую формулу:
=ЕСЛИ(СУММ(B2:C2)>130, "Хорошо", ЕСЛИ(СУММ(B2:C2) )>110, "Удовлетворительно", "Плохо"))
Если сумма больше 130, то результат "хорошо"; если больше 110 – «удовлетворительно», если 110 и ниже – «плохо».
Аналогичным образом вы можете встроить функцию СРЗНАЧ в логическую проверку ЕСЛИ и возвращать разные метки на основе среднего балла:
=ЕСЛИ(СРЕДНИЙ(B2:C2)>65, "Хорошо", ЕСЛИ(СРЕДНИЙ(B2:C2)>55, "Удовлетворительно", "Плохо"))
Предполагая, что общий балл находится в столбце D , вы можете определить самые высокие и самые низкие значения с помощью функций MAX и MIN:
=IF(D2=MAX($D$2:$D$10), "Лучший результат", "")
=IF(D2=MAX($D$2:$D$10), "Лучший результат", "")
Чтобы обе метки были в одном столбце, вложите вышеуказанные функции одну в другую:
=ЕСЛИ(D2=МАКС($D$2:$D$10), "Лучший результат", ЕСЛИ(D2=МИН($D$2:$D$10), "Худший результат", ""))
Точно так же вы можете использовать IF вместе со своими пользовательскими функциями. Например, вы можете комбинировать его с GetCellColor или GetCellFontColor, чтобы возвращать разные результаты в зависимости от цвета ячейки.
Кроме того, Excel предоставляет ряд функций для расчета данных на основе условий. Подробные примеры формул см. в следующих руководствах:
- СЧЁТЕСЛИ — подсчет ячеек, соответствующих условию
- COUNTIFS - количество ячеек с несколькими критериями
- СУММЕСЛИ - условно суммировать ячейки
- СУММЕСЛИМН - сумма ячеек с несколькими критериями
Пример 3. ЕСЛИ с ISNUMBER, ISTEXT и ISBLANK
Для идентификации текста, чисел и пустых ячеек в Microsoft Excel предусмотрены специальные функции, такие как ISTEXT, ISNUMBER и ISBLANK. Поместив их в логические тесты трех вложенных операторов IF, вы можете идентифицировать все различные типы данных за один раз:
=ЕСЛИ(ИСТЕКСТ(A2), "Текст", ЕСЛИ(ЧИСЛО(A2), "Число", ЕСЛИ(ПУСТО(A2), "Пусто", "")))
Пример 4. ЕСЛИ и СЦЕПИТЬ
Чтобы вывести результат ЕСЛИ и некоторый текст в одну ячейку, используйте функции СЦЕПИТЬ или СЦЕПИТЬ (в Excel 2016 - 365) и ЕСЛИ вместе. Например:
=СЦЕПИТЬ("Вы выступили ", ЕСЛИ(B1>100,"фантастика!", ЕСЛИ(B1>50,"ну", "плохо")))
=CONCAT("Вы выполнено", ЕСЛИ(B1>100,"фантастика!", ЕСЛИ(B1>50, "хорошо", "плохо")))
Глядя на скриншот ниже, вам вряд ли понадобятся объяснения того, что делает формула:
ЕСЛИ ОШИБКА / формула ISNA в Excel
В современных версиях Excel есть специальные функции для перехвата ошибок и замены их другим вычислением или предопределенным значением — ЕСЛИОШИБКА (в Excel 2007 и более поздних версиях) и IFNA (в Excel 2013 и более поздних версиях). В более ранних версиях Excel вместо этого можно использовать комбинации ЕСЛИ ОШИБКА и ЕСЛИ ЕСНА.
Разница в том, что ЕСЛИОШИБКА и ЕОШИБКА обрабатывают все возможные ошибки Excel, включая #ЗНАЧ!, #Н/Д, #ИМЯ?, #ССЫЛКА!, #ЧИСЛО!, #ДЕЛ/0! и #NULL!. В то время как IFNA и ISNA специализируются исключительно на ошибках #N/A.
Например, чтобы заменить ошибку «делить на ноль» (#DIV/0!) собственным текстом, можно использовать следующую формулу:
=ЕСЛИ(ЕОШИБКА(A2/B2), «Н/Д ", А2/В2)
И это все, что я могу сказать об использовании функции ЕСЛИ в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Практическая рабочая тетрадь для загрузки
Несколько критериев Excel IF — примеры (файл .xlsx)
Вложенные формулы, несколько операторов и т. д.
В этом руководстве показано, как использовать ЕСЛИ вместе с функцией И в Excel для проверки нескольких условий в одной формуле.
Некоторые вещи в мире конечны. Другие бесконечны, и функция ЕСЛИ кажется одной из таких вещей. В нашем блоге уже есть несколько учебных пособий по Excel IF, и мы по-прежнему каждый день находим новые способы их использования. Сегодня мы рассмотрим, как можно использовать ЕСЛИ вместе с функцией И для одновременной оценки двух или более условий.
- Формула Excel ЕСЛИ И
- ЕСЛИ больше И меньше
- ЕСЛИ И, то вычислить
- Excel IF с несколькими условиями И
- Вложенные операторы ЕСЛИ И в Excel
- Функция IF AND с учетом регистра
- Операторы ЕСЛИ И ИЛИ
Оператор ЕСЛИ И в Excel
Чтобы построить оператор ЕСЛИ И, вам, очевидно, необходимо объединить функции ЕСЛИ и И в одной формуле. Вот как:
ЕСЛИ(И( условие1 , условие2 ,…), значение_если_истина, значение_если_ложь)
В переводе на простой язык формула выглядит следующим образом: ЕСЛИ условие 1 истинно И условие 2 истинно, сделайте одно, иначе сделайте что-то другое.
В качестве примера составим формулу, которая проверяет, "доставлен" ли B2 и не является ли C2 пустым, и в зависимости от результатов выполняет одно из следующих действий:
- Если оба условия ИСТИННЫ, помечаем заказ как " Закрыто".
- Если какое-либо из условий имеет значение FALSE или оба условия имеют значение FALSE, то возвращается пустая строка ("").
=ЕСЛИ(И(B2="доставлено", C2<>""), "Закрыто", "")
На снимке экрана ниже показана функция ЕСЛИ И в Excel:
Если хотите чтобы вернуть некоторое значение в случае, если логическая проверка дает FALSE, укажите это значение в аргументе value_if_false . Например:
=ЕСЛИ(И(B2="доставлено", C2<>""), "Закрыто", "Открыто")
Модифицированная формула выводит "Закрыто", если столбец B "доставлен" и C содержит любую дату (не пустую). Во всех остальных случаях возвращает «Открыть»:
Примечание. При использовании формулы ЕСЛИ И в Excel для оценки текстовых условий помните, что строчные и прописные буквы рассматриваются как один и тот же символ. Если вы ищете формулу ЕСЛИ И с учетом регистра, оберните один или несколько аргументов И в функцию ТОЧНОЕ, как это делается в связанном примере.
Теперь, когда вы знаете синтаксис оператора ЕСЛИ И в 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/9/2018")), "x", "")
Или просто введите даты From и To в две ячейки ($F $1 и $F$2 в этом примере) и "вытащить" их из этих ячеек с помощью уже знакомой формулы ЕСЛИ И:
=ЕСЛИ(И(B2>=$F$1, B2<=$F$2), " x", "")
Для получения дополнительной информации см. Оператор 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
При работе с большими листами есть вероятность, что вам может потребоваться проверить несколько наборов различных критериев AND одновременно. Для этого вы берете классическую вложенную формулу ЕСЛИ Excel и расширяете ее логические тесты операторами И, например:0005
ЕСЛИ(И(…), выход1 , ЕСЛИ(И(…), выход2 , ЕСЛИ(И(…), выход3 , выход4 )))
Чтобы получить общее представление, пожалуйста посмотрите на следующий пример.
Предположим, вы хотите оценить свои услуги на основе стоимости доставки и предполагаемого времени доставки (ETD):
- Отлично : стоимость доставки менее 20 долларов США и ETD менее 3 дней
- Плохо : стоимость доставки более 30 долларов США и ETD более 5 дней
- В среднем : что-то среднее между
Для этого вы пишете два отдельных оператора IF AND:
IF(AND(B2<20, C2<3), «Отлично», …)
IF(AND(B2>30, C2) >5), «Плохо», …)
…и вкладывать одно в другое:
=ЕСЛИ(И(В2>30, С2>5), «Плохо», ЕСЛИ(И(В2<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 вы не ограничены использованием только одной логической функции. Для проверки различных комбинаций нескольких условий вы можете комбинировать функции ЕСЛИ, И, ИЛИ и другие функции для запуска необходимых логических тестов. Вот пример формулы ЕСЛИ И ИЛИ, которая проверяет пару условий ИЛИ внутри И. А теперь я покажу вам, как вы можете выполнить два или более теста И в рамках функции ИЛИ.
Предположим, вы хотите пометить заказы двух клиентов на сумму, превышающую определенное число, скажем, 100 долларов.
На языке Excel наши условия выражаются следующим образом:
ИЛИ(И( Клиент1 , Сумма >100), И( Клиент2 , Сумма >100)
=ЕСЛИ(ИЛИ(И(B2=$G$1, C2>$G$3), И(B2=$G$2, C2>$G$3)), "x", "")
тех же результатов можно добиться с помощью более компактного синтаксиса:
=ЕСЛИ(И(ИЛИ(B2=$G$1,B2=$G$2), C2>$G$3), "x", "")
Не уверены, что полностью понимаете логику формулы? Дополнительную информацию можно найти в Excel ЕСЛИ с несколькими условиями И/ИЛИ.
Видео-курс
