=И(B2>=50;C2>=50) (по-английски AND) возвращает ИСТИНА, только когда оба условия истинны. =ИЛИ(B2>=50;C2>=50) (OR) возвращает ИСТИНА, когда истинно хотя бы одно из них. Внутри ЕСЛИ они превращают два условия в одну проверку: =ЕСЛИ(И(B2>=50;C2>=50);"Pass";"Fail"). Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Exam | Project | Both 50+ | Either 50+ |
| 2 | Ana | 72 | 64 | Pass | Pass |
| 3 | Ben | 45 | 80 | Fail | Pass |
| 4 | Chloe | 58 | 51 | Pass | Pass |
| 5 | Dan | 38 | 42 | Fail | Fail |
| 6 | Eve | 91 | 47 | Fail | Pass |
=ЕСЛИ(И(B2>=50;C2>=50);"Pass";"Fail")Ben и Eve сдают одну часть и не сдают другую, поэтому в столбце D (И) у них Fail, а в столбце E (ИЛИ) Pass. Dan не сдаёт обе части и получает Fail в обоих столбцах. Замените экзамен Ben в B3 на 55, и он сдаст и в D.
Функция И
=AND(logical1, [logical2], ...)
И принимает до 255 условий и возвращает ИСТИНА, только если каждое из них даёт ИСТИНА. Одного значения ЛОЖЬ достаточно, чтобы результат стал ЛОЖЬ. Сама по себе И показывает в ячейке ИСТИНА или ЛОЖЬ; внутри ЕСЛИ она решает, какой результат вернёт ЕСЛИ.
Проверить, что число между двумя значениями
Функции МЕЖДУ в Excel нет. Используйте И с нижней и верхней границей:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Sample | Temp | Between 10 and 20 | Status |
| 2 | S1 | 14 | TRUE | OK |
| 3 | S2 | 22 | FALSE | Check |
| 4 | S3 | 10 | TRUE | OK |
| 5 | S4 | 7 | FALSE | Check |
| 6 | S5 | 20 | TRUE | OK |
=И(B2>=10;B2<=20)S3 и S5 стоят ровно на границах и считаются в пределах, потому что проверки используют >= и <=. Чтобы исключить границы, используйте > и <. Если держать границы в ячейках, =И(B2>=$F$1;B2<=$F$2), диапазон можно менять, не правя формулу. Проверка вроде 10<=B2<=20 в Excel не работает: она сравнивает 10<=B2 (ИСТИНА или ЛОЖЬ) с 20, а Excel ставит ИСТИНА и ЛОЖЬ выше любого числа, поэтому результат всегда ЛОЖЬ.
Функция ИЛИ
=OR(logical1, [logical2], ...)
ИЛИ возвращает ИСТИНА, если хотя бы одно условие даёт ИСТИНА, и ЛОЖЬ, только когда все они дают ЛОЖЬ. Это обычный способ принять несколько текстовых значений:
| A | B | C | |
|---|---|---|---|
| 1 | Order | Region | Zone |
| 2 | 1001 | North | Zone 1 |
| 3 | 1002 | East | Zone 2 |
| 4 | 1003 | South | Zone 1 |
| 5 | 1004 | West | Zone 2 |
| 6 | 1005 | south | Zone 1 |
=ЕСЛИ(ИЛИ(B2="North";B2="South");"Zone 1";"Zone 2")Сравнение текста не учитывает регистр, поэтому south строчными буквами тоже попадает в Zone 1. Для длинного списка допустимых значений =OR(B2={"North","South","Central"}) (английская запись) сравнивает B2 с каждым элементом массива констант за один раз.
НЕ и ИСКЛИЛИ
НЕ (NOT) обращает логическое значение: =НЕ(ИСТИНА) даёт ЛОЖЬ. Она хорошо читается, когда имеющееся условие противоположно нужному: =ЕСЛИ(НЕ(C2="Done");"Follow up";"") то же самое, что =ЕСЛИ(C2<>"Done";"Follow up";"").
ИСКЛИЛИ (XOR), доступная с Excel 2013, это исключающее ИЛИ: с двумя условиями она даёт ИСТИНА, когда истинно ровно одно из них, и ЛОЖЬ, когда оба истинны или оба ложны. С большим числом условий она даёт ИСТИНА, когда истинно нечётное их количество.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Task | Owner | Status | Open | One check only |
| 2 | Report | Ana | Done | FALSE | FALSE |
| 3 | Budget | Ben | Open | TRUE | FALSE |
| 4 | Slides | Ana | Open | TRUE | TRUE |
| 5 | Survey | Ben | Done | FALSE | TRUE |
| 6 | Plan | Ana | Done | FALSE | FALSE |
=НЕ(C2="Done")Столбец E даёт ИСТИНА для Slides (Ana, но не Done) и Survey (Done, но не Ana) и ЛОЖЬ там, где выполнены оба условия или ни одного.
И и ИЛИ вместе
Условия можно вкладывать: И внутрь ИЛИ или ИЛИ внутрь И. Менеджер получает бонус, когда он из региона North и либо продал больше чем на 1000, либо привёл нового клиента:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | New client | Bonus |
| 2 | Ana | North | 1200 | No | Bonus |
| 3 | Ben | North | 700 | Yes | Bonus |
| 4 | Chloe | South | 1500 | Yes | |
| 5 | Dan | North | 900 | No | |
| 6 | Eve | North | 1100 | Yes | Bonus |
=ЕСЛИ(И(B2="North";ИЛИ(C2>1000;D2="Yes"));"Bonus";"")Бонус получают Ana (продажи), Ben (новый клиент) и Eve (и то, и другое). Chloe выполняет часть ИЛИ, но она из South, а Dan из North, но не выполняет ни одного условия ИЛИ.
Практика: бесплатная доставка
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Order | Total | Member | Shipping |
| 2 | 1001 | 35 | Yes |
Ваша очередь: В D2 покажите "Free", когда сумма заказа в B2 не меньше 50 или клиент участник клуба (в C2 стоит "Yes"), иначе покажите "Paid".
И и ИЛИ в формулах массива: используйте * и +
И и ИЛИ сводят всё, что им передано, к одному значению ИСТИНА или ЛОЖЬ. В одной строке это то, что нужно, но не тогда, когда формула работает сразу со всем столбцом: =И(B2:B6="North";C2:C6>100) возвращает одно значение на все пять строк вместе. В формулах массива (ФИЛЬТР, СУММПРОИЗВ, СУММ по условию) пишите условия в скобках и перемножайте их для И, складывайте для ИЛИ. ИСТИНА считается как 1, а ЛОЖЬ как 0, поэтому произведение равно 1, только когда оба условия истинны, а сумма не меньше 1, когда истинно любое.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Count | ||
| 2 | Ana | North | 1200 | North and over 1,000 | 2 | |
| 3 | Ben | South | 700 | North or over 1,000 | 4 | |
| 4 | Chloe | North | 900 | |||
| 5 | Dan | South | 1500 | |||
| 6 | Eve | North | 1100 |
=СУММПРОИЗВ((B2:B6="North")*(C2:C6>1000))F2 насчитывает 2 строки (Ana и Eve), F3 насчитывает 4 (все, кроме Ben). В F3 важна часть >0: строка, которая выполняет оба условия, даёт в сумме 2, а сравнение превращает любую сумму больше 0 в одно значение ИСТИНА. По тому же образцу фильтруются строки, =ФИЛЬТР(A2:C6;(B2:B6="North")*(C2:C6>1000)); для простого подсчёта с логикой И формулы массива не нужны, это делает СЧЁТЕСЛИМН: =СЧЁТЕСЛИМН(B2:B6;"North";C2:C6;">1000"), смотрите страницу СЧЁТЕСЛИМН.
Часто задаваемые вопросы
Как использовать ЕСЛИ с И в Excel?
Поместите И в проверку ЕСЛИ: =ЕСЛИ(И(B2>=50;C2>=50);"Pass";"Fail"). Результат будет Pass, только когда и B2, и C2 равны 50 или больше.
Как проверить, что число находится между двумя значениями в Excel?
Используйте И с двумя сравнениями: =И(B2>=10;B2<=20) возвращает ИСТИНА для любого значения от 10 до 20 включительно. Внутри ЕСЛИ: =ЕСЛИ(И(B2>=10;B2<=20);"In range";"Out of range").
Чем И отличается от ИЛИ в Excel?
И возвращает ИСТИНА, только когда все условия дают ИСТИНА. ИЛИ возвращает ИСТИНА, когда хотя бы одно условие даёт ИСТИНА. =И(ИСТИНА;ЛОЖЬ) даёт ЛОЖЬ, а =ИЛИ(ИСТИНА;ЛОЖЬ) даёт ИСТИНА.
Почему И не работает в формуле с ФИЛЬТР или СУММПРОИЗВ?
И и ИЛИ сводят весь диапазон к одному значению ИСТИНА или ЛОЖЬ вместо одного на строку. В формулах массива перемножайте условия для И, (B2:B6="North")*(C2:C6>100), и складывайте их для ИЛИ.
Можно ли использовать И и ИЛИ в одной формуле?
Да, вложите одну в другую: =ЕСЛИ(И(B2="North";ИЛИ(C2>1000;D2="Yes"));"Bonus";"") требует North и вдобавок либо продажи больше 1000, либо Yes в D2.
