Вложенная ЕСЛИ (по-английски nested IF) это ЕСЛИ внутри другой ЕСЛИ, она нужна, когда возможных результатов больше двух. =ЕСЛИ(B2>=90;"A";ЕСЛИ(B2>=80;"B";ЕСЛИ(B2>=70;"C";"F"))) ставит A за 90 и больше, B за 80…89, C за 70…79 и F ниже 70. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | |
|---|---|---|---|
| 1 | Student | Score | Grade |
| 2 | Ana | 94 | A |
| 3 | Ben | 81 | B |
| 4 | Chloe | 70 | C |
| 5 | Dan | 65 | F |
| 6 | Eve | 88 | B |
| 7 | Finn | 90 | A |
=ЕСЛИ(B2>=90;"A";ЕСЛИ(B2>=80;"B";ЕСЛИ(B2>=70;"C";"F")))Щёлкните C2 и посмотрите на строку формул: три функции ЕСЛИ и три закрывающие скобки в конце. Замените балл Dan в B5 на 75, и его оценка сменится с F на C.
Как читается вложенная ЕСЛИ
Excel читает формулу с начала и останавливается на первой проверке, которая даёт ИСТИНА:
=IF(B2>=90, "A",
IF(B2>=80, "B",
IF(B2>=70, "C",
"F")))
- Балл 90 или больше? Тогда A, и больше ничего не проверяется.
- Иначе, 80 или больше? Тогда B. Этой проверке не нужно добавлять «и меньше 90», потому что балл 90 и выше до неё никогда не доходит.
- Иначе, 70 или больше? Тогда C.
- Иначе F, value_if_false последней ЕСЛИ.
Каждая внутренняя ЕСЛИ стоит на месте value_if_false предыдущей. Excel допускает до 64 уровней, но формулу больше чем с четырьмя или пятью трудно проверить на глаз. Excel принимает переносы строк внутри формулы, так что длинную формулу можно разложить в строке формул вот так: нажимайте Alt+Enter (Windows) или Control+Option+Return (Mac) перед каждой ЕСЛИ.
Почему важен порядок условий
Поскольку Excel останавливается на первой проверке с результатом ИСТИНА, при >= пороги должны идти от самого высокого к самому низкому. В столбце D те же три проверки стоят в обратном порядке:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Student | Score | Right order | Wrong order |
| 2 | Ana | 94 | A | C |
| 3 | Ben | 81 | B | C |
| 4 | Chloe | 70 | C | C |
| 5 | Dan | 65 | F | F |
| 6 | Eve | 88 | B | C |
=ЕСЛИ(B2>=70;"C";ЕСЛИ(B2>=80;"B";ЕСЛИ(B2>=90;"A";"F")))В столбце D все, у кого 70 и больше, получают C: балл 94 проходит первую проверку, B2>=70, и до проверок на B и A дело не доходит. Если удобнее начинать с самого низкого интервала, переверните операторы: =ЕСЛИ(B2<70;"F";ЕСЛИ(B2<80;"C";ЕСЛИ(B2<90;"B";"A"))) даёт те же оценки, что и столбец C.
Вложенная ЕСЛИ с текстом
Проверки могут сравнивать и текст. Здесь стоимость доставки зависит от региона, а все регионы, которые не названы, получают последнее значение:
| A | B | C | |
|---|---|---|---|
| 1 | Order | Region | Fee |
| 2 | 1001 | North | $5.00 |
| 3 | 1002 | South | $7.00 |
| 4 | 1003 | West | $9.00 |
| 5 | 1004 | East | $6.00 |
| 6 | 1005 | Islands | $9.00 |
=ЕСЛИ(B2="North";5;ЕСЛИ(B2="South";7;ЕСЛИ(B2="East";6;9)))West и Islands не подходят ни под одну из трёх проверок и получают последнее значение, $9.00. Когда каждая проверка сравнивает одну и ту же ячейку с фиксированным значением, как здесь, ПЕРЕКЛЮЧ (SWITCH) записывает то же правило, называя каждый регион один раз: =ПЕРЕКЛЮЧ(B2;"North";5;"South";7;"East";6;9). Смотрите страницу ПЕРЕКЛЮЧ.
Вложенная ЕСЛИ с И
Вложенная ЕСЛИ может сочетать уровни с И или ИЛИ, когда интервал зависит от двух ячеек. Менеджер с продажами от 2000 и стажем не меньше 3 лет получает 10%, любой другой с продажами от 2000 получает 5%, остальные ничего:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Rep | Sales | Years | Rate |
| 2 | Ana | 2400 | 4 | 10% |
| 3 | Ben | 2100 | 1 | 5% |
| 4 | Chloe | 1500 | 6 | 0% |
| 5 | Dan | 3000 | 3 | 10% |
| 6 | Eve | 900 | 2 | 0% |
=ЕСЛИ(И(B2>=2000;C2>=3);10%;ЕСЛИ(B2>=2000;5%;0))Ana и Dan получают 10%, у Ben есть продажи, но нет стажа, и он получает 5%, а Chloe и Eve получают 0%. Порядок важен и здесь: более строгая проверка идёт первой.
ЕСЛИМН: то же самое без вложения
В Excel 2019, Excel 2021 и Microsoft 365 функция ЕСЛИМН (IFS) принимает проверки и результаты парами, без внутренних ЕСЛИ и с одной закрывающей скобкой. TRUE (в русском Excel ИСТИНА) в качестве последней проверки означает «всё остальное»:
=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F")
В русском Excel: =ЕСЛИМН(B2>=90;"A";B2>=80;"B";B2>=70;"C";ИСТИНА;"F"). Она читает условия в том же порядке и останавливается на первом истинном, так что правило порядка выше по-прежнему действует. Об этом рассказывает страница ЕСЛИМН, в том числе об ошибке #Н/Д (по-английски #N/A), которую она возвращает, когда ни одна проверка не подошла. В Excel 2016 и более ранних ЕСЛИМН нет, и файл с ней показывает там #ИМЯ? (#NAME?). Таблицы на этой странице показывают ошибки под английскими именами.
Практика: комиссия с тремя интервалами
| A | B | C | |
|---|---|---|---|
| 1 | Rep | Sales | Commission |
| 2 | Ana | $6,200 | |
| 3 | Ben | $2,400 | |
| 4 | Chloe | $600 |
Ваша очередь: В C2 начислите 10% от продаж в B2, если они 5000 или больше, 5%, если они 1000 или больше, и 0 в остальных случаях. Формула протягивается вниз до C4.
Таблица поиска вместо множества ЕСЛИ
Когда интервалы числовые и их больше трёх или четырёх, держите пороги в маленькой таблице и ищите по ней. Таблица отсортирована от самого низкого порога вверх, а приблизительное совпадение (TRUE последним аргументом, в русском Excel ИСТИНА) возвращает строку наибольшего порога, который не выше балла:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Grade | Min score | Grade | |
| 2 | Ana | 94 | A | 0 | F | |
| 3 | Ben | 81 | B | 70 | C | |
| 4 | Chloe | 70 | C | 80 | B | |
| 5 | Dan | 65 | F | 90 | A | |
| 6 | Eve | 88 | B |
=ВПР(B2;$E$2:$F$5;2;ИСТИНА)Результаты совпадают с вложенной ЕСЛИ в начале страницы; в русском Excel формула в C2 пишется =ВПР(B2;$E$2:$F$5;2;ИСТИНА). Чтобы сдвинуть интервал B на 85, замените E4 на 85: ни одна формула не меняется, а все оценки обновляются. С ПРОСМОТРX (XLOOKUP) тот же поиск выглядит как =ПРОСМОТРX(B2;$E$2:$E$5;$F$2:$F$5;;-1), где -1 означает «точное совпадение или следующее меньшее значение»; тогда таблицу не нужно сортировать.
Попробуйте: таблица интервалов готова, напишите поиск.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Grade | Min score | Grade | |
| 2 | Ana | 86 | 0 | F | ||
| 3 | 70 | C | ||||
| 4 | 80 | B | ||||
| 5 | 90 | A |
Ваша очередь: В C2 верните оценку для балла из B2 по таблице интервалов в E2:F5.
Часто задаваемые вопросы
Как написать несколько условий ЕСЛИ в Excel?
Поместите следующую ЕСЛИ в аргумент value_if_false предыдущей: =ЕСЛИ(B2>=90;"A";ЕСЛИ(B2>=80;"B";ЕСЛИ(B2>=70;"C";"F"))). Excel проверяет условия от первого к последнему и останавливается на первом, которое даёт ИСТИНА.
Сколько функций ЕСЛИ можно вложить в Excel?
До 64 уровней в Excel 2007 и новее. Задолго до этого предела формулу становится трудно читать и проверять; если интервалов больше трёх или четырёх, таблицу с приблизительным поиском ВПР или ПРОСМОТРX поддерживать проще.
Почему вложенная ЕСЛИ возвращает неверный результат?
Обычно потому, что условия стоят в неверном порядке. С проверками >= начинайте с самого высокого порога: если первой стоит B2>=70, балл 95 остановится на ней и получит результат интервала от 70.
Чем можно заменить вложенные ЕСЛИ в Excel?
ЕСЛИМН в Excel 2019 и новее (=ЕСЛИМН(B2>=90;"A";B2>=80;"B";ИСТИНА;"F")), ПЕРЕКЛЮЧ, когда одно значение сравнивается с фиксированными, и таблицей поиска с =ВПР(B2;$E$2:$F$5;2;ИСТИНА) для числовых интервалов.
