Функция ЕСЛИ позволяет выполнять логические сравнения значений и ожидаемых результатов. Она проверяет условие и в зависимости от его истинности возвращает результат.
- =ЕСЛИ(это истинно, то сделать это, в противном случае сделать что-то еще)
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Утверждения ЕСЛИ невероятно надежны и составляют основу многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться к минимальным условиям, таким как "мужской/женский", "да/нет/возможно" и т. д., но иногда может потребоваться вычислить более сложные сценарии, для которых требуется вложенность* более трех функций ЕСЛИ.
* "Вложенность" относится к практике объединения нескольких функций в одной формуле.
Технические подробности
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
Синтаксис
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Например:
- =ЕСЛИ(A2>B2;"Превышение бюджета";"OK")
- =ЕСЛИ(A2=B2;B4-A4;"")
| Имя аргумента | Описание |
|---|---|
|
лог_выражение (обязательный) |
Условие, которое нужно проверить. |
|
значение_если_истина (обязательный) |
Значение, которое должно возвращаться , если logical_test имеет значение ИСТИНА. |
|
значение_если_ложь (необязательный) |
Значение, которое должно возвращаться , если logical_test имеет значение ЛОЖЬ. |
Замечания
Хотя Excel позволяет вложить до 64 различных функций ЕСЛИ, делать это совершенно не рекомендуется. Почему?
- Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если не вложить формулу на 100 % точно, она может работать в 75 % случаев, но в 25 % случаев возвращаются непредвиденные результаты. К сожалению, шансов отыскать эти 25 % немного.
- Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.
Если вы обнаружите, что у вас есть утверждение ЕСЛИ, которое, кажется, продолжает расти, и конца этому не видно, пришло время отложить мышь и переосмыслить свою стратегию.
Давайте рассмотрим, как правильно создать сложную вложенную интеграцию ЕСЛИ с помощью нескольких ЕСЛИ, и понять, что пришло время использовать другой инструмент в вашем арсенале Excel.
Примеры
Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент.
- =ЕСЛИ(D2>89;"A";ЕСЛИ(D2>79;"B";ЕСЛИ(D2>69;"C";ЕСЛИ(D2>59;"D";"F"))))
Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:
- Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.
- Если тестовых баллов больше 79, учащийся получает оценку B.
- Если тестовых баллов больше 69, учащийся получает оценку C.
- Если тестовых баллов больше 59, учащийся получает оценку D.
- В противном случае учащийся получает оценку F.
Этот конкретный пример относительно безопасен, потому что маловероятно, что корреляция между результатами тестов и буквенными оценками изменится, поэтому он не потребует особого обслуживания. Но вот мысль — что, если вам нужно разделить оценки между A+, A и A- (и так далее)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот как ваша формула будет выглядеть сейчас:
- =ЕСЛИ(B2>97;"A+";ЕСЛИ(B2>93;"A";ЕСЛИ(B2>89;"A-";ЕСЛИ(B2 87;"B+>";ЕСЛИ(B2 83;"B">;ЕСЛИ(B2>79;"B-"ЕСЛИ(B2>77;"C+";ЕСЛИ(B2>73;"C";ЕСЛИ(B2>69;"C-";ЕСЛИ(B2>57;"D+";ЕСЛИ(B2>53;"D";ЕСЛИ(B2>49;"D-";"F"))))))))))))
Он по-прежнему точен с точки зрения функциональности и будет работать должным образом, но его написание занимает много времени и еще больше времени на проверку, чтобы убедиться в том, что он выполняет нужные вам действия. Еще одна вопиющая проблема заключается в том, что вам приходилось вводить баллы и эквивалентные буквенные оценки вручную. Какова вероятность того, что вы случайно сделаете опечатку? А теперь представьте, что вы пытаетесь сделать это 64 раза в более сложных условиях! Конечно, это возможно, но действительно ли вы хотите подвергать себя такого рода усилиям и вероятным ошибкам, которые будет действительно трудно обнаружить?
Совет
Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). Excel попытается помочь вам понять, что куда находится, раскрашивая различные части формулы при ее редактировании. Например, при редактировании формулы выше при перемещении курсора мимо каждой из закрывающих скобок ")" соответствующие открывающие скобки будут иметь тот же цвет. Это может быть особенно полезно в сложных вложенных формулах, когда нужно выяснить, достаточно ли совпадающих скобок.
Дополнительные примеры
Ниже приведен распространенный пример расчета комиссионных за продажу в зависимости от уровней дохода.
- =ЕСЛИ(C9>15000;20%;ЕСЛИ(C9>12500;17.5%;ЕСЛИ(C9>10000;15%;ЕСЛИ(C9>7500;12.5%;ЕСЛИ(C9>5000;10%;0)))))
Эта формула означает: ЕСЛИ(ячейка C9 больше 15 000, то вернуть 20 %, ЕСЛИ(ячейка C9 больше 12 500, то вернуть 17,5 % и т. д...
Хотя эта формула удивительно похожа на предыдущий пример с оценками, она является отличным примером того, насколько сложно может быть поддерживать большие отчеты ЕСЛИ: что вам нужно будет сделать, если ваша организация решит добавить новые уровни компенсации и, возможно, даже изменить существующие долларовые или процентные значения? У вас будет много работы!
Совет
Чтобы сложные формулы было проще читать, вы можете вставить разрывы строк в строке формул. Просто нажмите клавиши ALT+ВВОД перед текстом, который хотите перенести на другую строку.
Перед вами пример сценария для расчета комиссионных с неправильной логикой:
Видите, в чем дело? Сравните порядок сравнения доходов с предыдущим примером. В каком направлении движется эта? Правильно, он идет снизу вверх (от 5 000 до 15 000 долларов), а не наоборот. Но почему это должно быть таким большим делом? Это очень важно, потому что формула не может пройти первое вычисление для любого значения, превышающего 5000 $. Предположим, у вас есть доход в размере 12 500 долларов — отчет ЕСЛИ вернется на 10%, потому что он больше 5 000 долларов, и на этом он остановится. Это может быть невероятно проблематично, потому что во многих ситуациях такие ошибки остаются незамеченными, пока не окажут негативного влияния. Итак, зная, что существуют серьезные ошибки со сложными вложенными операторами ЕСЛИ, что вы можете сделать? В большинстве случаев вместо создания сложной формулы с помощью функции ЕСЛИ можно использовать функцию ВПР. Используя функцию ВПР, сначала необходимо создать справочную таблицу:
- =ВПР(C2;C5:D17;2;ИСТИНА)
В этой формуле предлагается найти значение ячейки C2 в диапазоне C5:C17. Если значение найдено, возвращается соответствующее значение из той же строки в столбце D.
- =ВПР(B9;B2:C6;2;ИСТИНА)
Эта формула ищет значение ячейки B9 в диапазоне B2:B22. Если значение найдено, возвращается соответствующее значение из той же строки в столбце C.
Примечание
В обеих функциях ВПР в конце формулы используется аргумент ИСТИНА, который означает, что мы хотим найти близкое совпадение. Иначе говоря, будут сопоставляться точные значения в таблице подстановки, а также все значения, попадающие между ними. В этом случае таблицы подстановки нужно сортировать по возрастанию, от меньшего к большему.
Функция ВПР рассматривается здесь гораздо подробнее, но она намного проще, чем 12-уровневая сложная вложенная инструкция ЕСЛИ! Есть и другие, менее очевидные, преимущества:
- Таблицы ссылок функции ВПР открыты и их легко увидеть.
- Значения в таблицах просто обновлять, и вам не потребуется трогать формулу, если условия изменятся.
- Если вы не хотите, чтобы другие пользователи видели или мешали справочной таблице, просто поместите ее на другой лист.
Вы знали?
Теперь есть функция УСЛОВИЯ, которая может заменить несколько вложенных операторов ЕСЛИ. Так, в нашем первом примере оценок с 4 вложенными функциями ЕСЛИ:
- =ЕСЛИ(D2>89;"A";ЕСЛИ(D2>79;"B";ЕСЛИ(D2>69;"C";ЕСЛИ(D2>59;"D";"F"))))
можно сделать все гораздо проще с помощью одной функции ЕСЛИМН:
- =ЕСЛИМН(D2>89;"A";D2>79;"B";D2>69;"C";D2>59;"D",ИСТИНА,"F")
Функция ЕСЛИМН удобна, потому что вам не нужно беспокоиться обо всех этих операторах ЕСЛИ и скобках.
Примечание
Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.