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

Обновлено: 08.07.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel для Windows Phone 10 Excel Starter 2010 Еще. Меньше

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

=IF(Что-то верно, то сделайте что-нибудь, иначе сделайте что-нибудь другое)

Поэтому оператор IF может иметь два результата. Первый результат — если сравнение истинно, второй — если сравнение ложно.

Операции IF невероятно надежны и составляют основу многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться к минимальным условиям, таким как Мужчина/Женщина, Да/Нет/Возможно, и это лишь некоторые из них, но иногда вам может понадобиться оценить более сложные сценарии, требующие вложения* более 3 функций ЕСЛИ вместе.< /p>

* "Вложение" — это практика объединения нескольких функций в одну формулу.

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

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

Имя аргумента

Описание

логический_тест

Условие, которое вы хотите проверить.

значение_если_истина

Значение, которое вы хотите вернуть, если результат logical_test равен TRUE.

значение_если_ложь

Значение, которое вы хотите вернуть, если результат logical_test равен FALSE.

Примечания

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

Множественные операторы IF требуют тщательного обдумывания, чтобы правильно построить и убедиться, что их логика может правильно вычислять каждое условие до конца. Если вы не вложите свою формулу на 100 % точно, она может работать в 75 % случаев, но возвращать неожиданные результаты в 25 % случаев. К сожалению, шансы на то, что вы попадете в 25 %, невелики.

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

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

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

Примеры

Ниже приведен пример относительно стандартного вложенного оператора IF для преобразования результатов тестов учащихся в эквивалентные им буквенные оценки.

Этот сложный вложенный оператор IF следует простой логике:

Если результат теста (в ячейке D2) больше 89, учащийся получает пятерку

Если результат теста выше 79, учащийся получает B

Если результат теста выше 69, учащийся получает оценку C

Если результат теста выше 59, учащийся получает оценку D

В противном случае учащийся получает F

Этот конкретный пример относительно безопасен, потому что маловероятно, что корреляция между результатами тестов и буквенными оценками изменится, поэтому он не потребует особого обслуживания. Но вот мысль — что, если вам нужно сегментировать оценки между A+, A и A- (и так далее)? Теперь нужно переписать оператор IF с четырьмя условиями, чтобы он содержал 12 условий! Вот как теперь будет выглядеть ваша формула:

Он по-прежнему функционально точен и будет работать так, как ожидалось, но на написание и тестирование уходит много времени, чтобы убедиться, что он работает так, как вы хотите. Еще одна вопиющая проблема заключается в том, что вам приходилось вводить баллы и эквивалентные буквенные оценки вручную. Какова вероятность того, что вы случайно сделаете опечатку? А теперь представьте, что вы пытаетесь сделать это 64 раза с более сложными условиями! Конечно, это возможно, но вы действительно хотите подвергать себя таким усилиям и вероятным ошибкам, которые будет очень трудно обнаружить?

Совет. Каждая функция в Excel требует наличия открывающей и закрывающей скобки (). Excel попытается помочь вам понять, что и куда нужно, окрашивая различные части формулы, когда вы ее редактируете. Например, если вы отредактируете приведенную выше формулу, то при перемещении курсора за каждую из закрывающих скобок «)» соответствующая открывающая скобка окрасится в тот же цвет. Это может быть особенно полезно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли у вас совпадающих скобок.

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

Ниже приведен очень распространенный пример расчета комиссии с продаж на основе уровня дохода.

Эта формула гласит: ЕСЛИ(C9 больше 15 000, возвращается 20 %, ЕСЛИ(C9 больше 12 500, возвращается 17,5 % и т. д.

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

Совет. Чтобы упростить чтение длинных формул, в строку формул можно вставлять разрывы строк. Просто нажмите ALT+ENTER перед текстом, который вы хотите перенести на новую строку.

Вот пример сценария комиссии с нарушенной логикой:

Вы видите, что не так? Сравните порядок сравнения Revenue с предыдущим примером. В какую сторону идет этот? Правильно, она идет снизу вверх (от 5000 до 15000 долларов), а не наоборот. Но почему это должно быть таким большим делом? Это большое дело, потому что формула не может пройти первую оценку для любого значения, превышающего 5000 долларов. Допустим, у вас есть доход в размере 12 500 долларов – оператор IF вернет 10 %, потому что он больше 5 000 долларов, и на этом он остановится. Это может быть невероятно проблематично, потому что во многих ситуациях эти типы ошибок остаются незамеченными, пока они не окажут негативного влияния. Итак, зная, что сложные вложенные операторы IF сопряжены с некоторыми серьезными ловушками, что вы можете сделать? В большинстве случаев можно использовать функцию ВПР вместо построения сложной формулы с помощью функции ЕСЛИ. Используя функцию ВПР, сначала необходимо создать справочную таблицу:

Эта формула говорит, что нужно искать значение в ячейке C2 в диапазоне C5:C17. Если значение найдено, вернуть соответствующее значение из той же строки в столбце D.

Аналогично эта формула ищет значение в ячейке B9 в диапазоне B2:B22. Если значение найдено, вернуть соответствующее значение из той же строки в столбце C.

Примечание. Обе эти функции ВПР используют аргумент ИСТИНА в конце формул, что означает, что мы хотим, чтобы они искали приблизительное совпадение. Другими словами, он будет соответствовать точным значениям в таблице поиска, а также любым значениям, которые находятся между ними. В этом случае таблицы поиска необходимо отсортировать в порядке возрастания, от меньшего к большему.

ВПР рассматривается здесь более подробно, но это намного проще, чем 12-уровневый сложный вложенный оператор ЕСЛИ! Есть и другие менее очевидные преимущества:

Справочные таблицы ВПР находятся в открытом доступе, и их легко увидеть.

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

Если вы не хотите, чтобы люди видели вашу справочную таблицу или мешали ей, просто поместите ее на другой рабочий лист.

Знаете ли вы?

Появилась функция IFS, которая может заменить несколько вложенных операторов IF одной функцией. Поэтому вместо нашего примера с начальными оценками, в котором есть 4 вложенные функции ЕСЛИ:

Это можно сделать намного проще с помощью одной функции IFS:

Функция IFS великолепна, потому что вам не нужно беспокоиться обо всех этих операторах IF и скобках.

Примечание. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

Используйте функцию ИЛИ, одну из логических функций, чтобы определить, являются ли какие-либо условия в тесте ИСТИННЫМИ.

Пример

Отображает значение в ячейке A2, если оно меньше 0 ИЛИ больше 50, в противном случае отображается сообщение.

Расчет комиссии с продаж

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

=IF(OR(B14>=$B$4,C14>=$B$5),B14*$B$6,0) — ЕСЛИ общий объем продаж больше или равен (> =) цель продаж ИЛИ учетные записи больше или равны (>=) цели учетной записи, затем умножьте общий объем продаж на процент комиссии, в противном случае верните 0.

Нужна дополнительная помощь?

Функция Excel ИЛИ возвращает значение ИСТИНА, если какой-либо заданный аргумент оценивается как ИСТИНА, и возвращает ЛОЖЬ, если все предоставленные аргументы оцениваются как ЛОЖЬ. Например, чтобы проверить A1 на наличие «x» или «y», используйте =OR(A1="x",A1="y"). Функцию ИЛИ можно использовать в качестве логической проверки внутри функции ЕСЛИ, чтобы избежать вложенных ЕСЛИ, и ее можно комбинировать с функцией И.

logical1 — первое условие или логическое значение для оценки.
logical2 – [необязательно] Второе условие или логическое значение для оценки.

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

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

Цель функции ИЛИ – одновременно оценивать несколько логических тестов и возвращать ИСТИНА, если какой-либо результат ИСТИНА. Например, если A1 содержит число 50, то:

Функция ИЛИ оценит все предоставленные значения и вернет ИСТИНА, если какое-либо значение оценивается как ИСТИНА. Если все логические операции оцениваются как ЛОЖЬ, функция ИЛИ вернет ЛОЖЬ. Примечание. Excel оценит любое число, кроме нуля (0), как ИСТИНА.

Как функция И, так и функция ИЛИ будут объединять результаты в одно значение. Это означает, что их нельзя использовать в операциях с массивами, которые должны предоставить массив результатов. Чтобы обойти это ограничение, вы можете использовать булеву логику. Дополнительную информацию см. в разделе Формулы массива с логикой И и ИЛИ.

Примеры

Например, чтобы проверить, больше ли значение в A1 ИЛИ значение в B1 больше 75, используйте следующую формулу:

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

Эта формула вернет "Успешно", если значение в A1 больше 75 ИЛИ значение в B1 больше 75.

Форма массива

Если вы введете ИЛИ в качестве формулы массива, вы можете проверить все значения в диапазоне на соответствие условию. Например, эта формула массива вернет значение TRUE, если любая ячейка в A1:A100 больше 15:

Примечание. Это формула массива, которую необходимо вводить с помощью клавиш Ctrl + Shift + Enter, за исключением Excel 365.

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

Поэтому оператор IF может иметь два результата.Первый результат — если сравнение истинно, второй — если сравнение ложно.

Например, =IF(C2="Да",1,2) говорит ЕСЛИ(C2 = Да, то возвращает 1, иначе возвращает 2).

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

Имя аргумента

Описание

логический_тест (обязательно)

Условие, которое вы хотите проверить.

value_if_true (обязательно)

Значение, которое вы хотите вернуть, если результат logical_test равен TRUE.

value_if_false (необязательно)

Значение, которое вы хотите вернуть, если результат logical_test равен FALSE.

Простые примеры ЕСЛИ

В приведенном выше примере в ячейке D2 указано: IF(C2 = Да, затем вернуть 1, иначе вернуть 2)

В этом примере формула в ячейке D2 говорит: ЕСЛИ(C2 = 1, затем вернуть Да, иначе вернуть Нет)Как видите, функцию ЕСЛИ можно использовать для оценки как текста, так и значения. Его также можно использовать для оценки ошибок. Вы не ограничены только проверкой равенства одной вещи другой и возвратом одного результата, вы также можете использовать математические операторы и выполнять дополнительные вычисления в зависимости от ваших критериев. Вы также можете объединить несколько функций ЕСЛИ, чтобы выполнить несколько сравнений.

=IF(C2>B2, "Выше бюджета", "В рамках бюджета")

В приведенном выше примере функция ЕСЛИ в D2 говорит ЕСЛИ (C2 больше, чем B2, затем возвращает «Превышение бюджета», в противном случае возвращает «В рамках бюджета»)

На приведенном выше рисунке вместо текстового результата мы собираемся вернуть математический расчет. Таким образом, формула в E2 гласит: ЕСЛИ (фактическая сумма больше, чем заложенная в бюджете, затем вычтите заложенную в бюджете сумму из фактической суммы, в противном случае ничего не возвращайте).

В этом примере формула в F7 говорит ЕСЛИ (E7 = «Да», затем рассчитайте общую сумму в F5 * 8,25%, в противном случае налог с продаж не взимается, поэтому верните 0)< /p>

Примечание. Если вы собираетесь использовать текст в формулах, его необходимо заключить в кавычки (например, «Текст»). Единственным исключением является использование значений TRUE или FALSE, которые Excel автоматически понимает.

Распространенные проблемы

Что пошло не так

Не было аргументов ни для аргументов value_if_true, ни для аргументов value_if_False. Чтобы увидеть правильное возвращаемое значение, добавьте текст аргумента к двум аргументам или добавьте к аргументу ИСТИНА или ЛОЖЬ.

Обычно это означает, что в формуле написана ошибка.

Нужна дополнительная помощь?

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