Полезность функции IF расширяется путем вставки или вложения нескольких функций IF внутри друг друга. Вложенные функции IF увеличивают количество возможных условий, которые проверяются, и увеличивают количество действий, которые предпринимаются для решения этих задач. Для еще более проверенных возможных условий лучше всего использовать функции IF, AND и OR .
Инструкции в этой статье относятся к Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011 и Excel Online.
Руководство по функциям Nest IF
Как показано на рисунке, в этом руководстве используются две функции IF для создания формулы, которая рассчитывает сумму ежегодных отчислений для сотрудников на основе их годовой зарплаты. Формула, использованная в примере, показана ниже. Вложенная функция IF действует как аргумент value_if_false для первой функции IF.
= ЕСЛИ (Д7 <30000, $ D $ 3 * D7, ЕСЛИ (Д7> = 50000, $ D $ 5 * D7, $ D $ 4 * Д7))
Различные части формулы разделены запятыми и выполняют следующие задачи:
- Первая часть, D7 <30000, проверяет, составляет ли зарплата сотрудника менее 30 000 долларов США.
- Если зарплата составляет менее 30 000 долларов США, средняя часть, $ D $ 3 * D7, умножает зарплату на ставку вычета 6%.
- Если зарплата превышает 30000 долларов США, вторая функция ЕСЛИ IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) проверяет два дополнительных условия.
- D7> = 50000 проверяет, является ли зарплата сотрудника больше или равна 50 000 долларов США.
- Если заработная плата равна или превышает 50 000 долл. США, 5 долл. США * 5 долл. США умножают заработную плату на ставку вычета 10%.
- Если заработная плата составляет менее 50 000 долл. США, но превышает 30 000 долл. США, D $ 4 * D7 умножает заработную плату на ставку вычета 8%.
Введите учебные данные
Введите данные в ячейки C1 — E6 рабочего листа Excel, как показано на рисунке. Единственными данными, которые не были введены в этот момент, является сама функция IF, расположенная в ячейке E7.
Инструкции по копированию данных не включают в себя этапы форматирования для рабочего листа. Это не мешает завершению урока. Ваша рабочая таблица может отличаться от показанной в примере, но функция IF даст вам те же результаты.
Запустите вложенную функцию IF
Можно просто ввести полную формулу
= ЕСЛИ (Д7 <30000, $ D $ 3 * D7, ЕСЛИ (Д7> = 50000, $ D $ 5 * D7, $ D $ 4 * Д7))
в ячейку E7 рабочего листа и заставить его работать. В Excel Online это метод, который вы должны использовать. Однако, если вы используете настольную версию Excel, часто проще использовать диалоговое окно функции для ввода необходимых аргументов.
Использование диалогового окна немного сложнее при вводе вложенных функций, поскольку необходимо ввести вложенную функцию. Невозможно открыть второе диалоговое окно для ввода второго набора аргументов.
В этом примере вложенная функция IF вводится в третью строку диалогового окна в качестве аргумента Value_if_false. Поскольку на рабочем листе рассчитывается ежегодное удержание для нескольких сотрудников, формула сначала вводится в ячейку E7 с использованием абсолютных ссылок на ячейки для ставок удержания, а затем копируется в ячейки E8: E11.
Шаги учебника
- Выберите ячейку E7, чтобы сделать ее активной. Именно здесь будет расположена вложенная формула IF.
- Выберите Формулы .
- Выберите Logical, чтобы открыть раскрывающийся список функций.
- Выберите IF в списке, чтобы открыть диалоговое окно функции.
Данные, введенные в пустые строки в диалоговом окне, образуют аргументы функции IF. Эти аргументы сообщают функции проверяемое условие и какие действия предпринять, если условие истинно или ложно.
Вариант ярлыка учебника
Чтобы продолжить этот пример, вы можете:
- Введите аргументы в диалоговом окне, как показано на рисунке выше, а затем перейдите к последнему шагу, который охватывает копирование формулы в строки с 7 по 10.
- Или выполните следующие шаги, которые предлагают подробные инструкции и объяснения для ввода трех аргументов.
Введите аргумент Logical_test
Аргумент Logical_test сравнивает два элемента данных. Эти данные могут быть числами, ссылками на ячейки , результатами формул или даже текстовыми данными. Чтобы сравнить два значения, Logical_test использует оператор сравнения между значениями.
В этом примере есть три уровня заработной платы, которые определяют ежегодное удержание сотрудника:
- Менее 30000 долларов.
- От 30 000 до 49 999 долларов.
- 50 000 долларов или больше
Одна функция IF может сравнивать два уровня, но третий уровень зарплаты требует использования второй вложенной функции IF. Первое сравнение проводится между годовой зарплатой работника, расположенной в ячейке D, с пороговой зарплатой в 30 000 долл. США. Поскольку цель состоит в том, чтобы определить, составляет ли D7 менее 30000 долларов, между значениями используется оператор Less Than ( < ).
Шаги учебника
- Выберите строку Logical_test в диалоговом окне .
- Выберите ячейку D7, чтобы добавить эту ссылку на ячейку в строку Logical_test.
- Нажмите клавишу « меньше» ( < ) на клавиатуре.
- Введите 30000 после символа «меньше».
- Завершенный логический тест отображается как D7 <30000.
Не вводите знак доллара ( $ ) или запятую ( , ) с 30000. Недопустимое сообщение об ошибке появляется в конце строки Logical_test, если любой из этих символов вводится вместе с данными.
Введите аргумент Value_if_true
Аргумент Value_if_true сообщает функции IF, что делать, когда Logical_test имеет значение true. Аргумент Value_if_true может быть формулой, блоком текста, значением , ссылкой на ячейку или ячейку можно оставить пустым.
В этом примере, когда данные в ячейке D7 меньше 30000 долларов США, Excel умножает годовой оклад сотрудника в ячейке D7 на ставку вычета в 6 процентов, расположенную в ячейке D3.
Относительные и Абсолютные ссылки на ячейки
Обычно, когда формула копируется в другие ячейки, относительные ссылки на ячейки в формуле изменяются, чтобы отразить новое местоположение формулы. Это позволяет легко использовать одну и ту же формулу в нескольких местах. Иногда ссылки на ячейки изменяются при копировании функции, что приводит к ошибкам. Чтобы предотвратить эти ошибки, ссылки на ячейки могут быть сделаны абсолютными, что предотвращает их изменение при копировании.
Абсолютные ссылки на ячейки создаются путем добавления знаков доллара вокруг обычной ссылки на ячейки, такой как $ D $ 3. Добавление знаков доллара легко осуществляется нажатием клавиши F4 на клавиатуре после ввода ссылки на ячейку в диалоговом окне.
В этом примере ставка удержания, расположенная в ячейке D3, вводится как абсолютная ссылка на ячейку в строку Value_if_true диалогового окна.
Шаги учебника
- Выберите строку Value_if_true в диалоговом окне.
- Выберите ячейку D3 на листе, чтобы добавить эту ссылку на ячейку в строку Value_if_true.
- Нажмите клавишу F4, чтобы сделать D3 абсолютной ссылкой на ячейку ($ D $ 3).
- Нажмите звездочку ( * ). Звездочка — это символ умножения в Excel.
- Выберите ячейку D7, чтобы добавить эту ссылку на ячейку в строку Value_if_true.
- Завершенная строка Value_if_true отображается как $ D $ 3 * D7.
D7 не вводится как абсолютная ссылка на ячейку. Его необходимо изменить, когда формула копируется в ячейки E8: E11, чтобы получить правильную сумму вычетов для каждого сотрудника.
Введите вложенную функцию IF в качестве аргумента Value_if_false
Обычно аргумент Value_if_false сообщает функции IF, что делать, если Logical_test имеет значение false. В этом случае вложенная функция IF вводится как этот аргумент. При этом получаются следующие результаты:
- Аргумент Logical_test во вложенной функции IF (D7> = 50000) проверяет все зарплаты, которые составляют не менее 30 000 долларов.
- Для зарплат, превышающих или равных 50 000 долларов США, аргумент Value_if_true умножает их на ставку вычета 10%, расположенную в ячейке D5.
- Для остальных зарплат (те, которые больше, чем 30 000 долларов США, но меньше, чем 50 000 долларов США) аргумент Value_if_false умножает их на ставку вычета 8%, расположенную в ячейке D4.
Шаги учебника
Как упоминалось в начале урока, второе диалоговое окно не может быть открыто для входа во вложенную функцию, поэтому его необходимо ввести в строку Value_if_false.
Вложенные функции начинаются не со знака равенства, а с имени функции.
- Выберите строку Value_if_false в диалоговом окне.
- Введите следующую функцию IF:
ЕСЛИ (Д7> = 50000, $ D $ 5 * D7, $ D $ 4 * Д7)
- Выберите OK, чтобы завершить функцию IF и закрыть диалоговое окно.
- Значение 3678,96 долл. США отображается в ячейке E7. Поскольку Р. Холт зарабатывает более 30 000 долл. США, но менее 50 000 долл. США в год, для расчета его ежегодного вычета используется формула 45 987 * 8%.
- Выберите ячейку E7, чтобы отобразить полную функцию = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7)) в строке формул над рабочим листом.
После выполнения этих шагов ваш пример теперь соответствует первому изображению в этой статье.
Последний шаг заключается в копировании формулы , если клетки E8 для E11 с помощью маркера заполнения , чтобы завершить рабочий лист.
Скопируйте вложенные функции IF, используя маркер заполнения
Чтобы заполнить рабочий лист, скопируйте формулу, содержащую вложенную функцию IF, в ячейки E8-E11. Когда функция копируется, Excel обновляет относительные ссылки на ячейки, чтобы отразить новое местоположение функции, сохраняя при этом абсолютную ссылку на ячейку.
Один простой способ скопировать формулы в Excel — с помощью ручки заполнения.
Шаги учебника
- Выберите ячейку E7, чтобы сделать ее активной .
- Поместите указатель мыши на квадрат в нижнем правом углу активной ячейки. Указатель изменится на знак плюс (+).
- Выберите и перетащите маркер заполнения вниз в ячейку E11.
- Клетки E8-E11 заполнены результатами формулы, как показано на рисунке выше.