Как вложить несколько функций IF в Excel

Полезность функции IF расширяется путем вставки или вложения нескольких функций IF друг в друга. Вложенные функции IF увеличивают количество возможных условий, которые проверяются, и увеличивают количество действий, которые предпринимаются для решения этих задач.

Примечание . Инструкции в этой статье относятся к 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.

 = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7)) 

Различные части формулы разделены запятыми и выполняют следующие задачи:

  1. Первая часть, D7 <30000, проверяет, составляет ли зарплата сотрудника менее 30 000 долларов США.
  2. Если зарплата составляет менее 30 000 долларов США, средняя часть, $ D $ 3 * D7, умножает зарплату на ставку вычета в 6%.
  3. Если зарплата превышает 30000 долларов США, вторая функция ЕСЛИ IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) проверяет два дополнительных условия.
  4. D7> = 50000 проверяет, является ли зарплата сотрудника больше или равна 50 000 долларов США.
  5. Если заработная плата равна или превышает 50 000 долл. США, 5 долл. США * 5 долл. США умножают заработную плату на ставку вычета 10%.
  6. Если заработная плата составляет менее 50 000 долл. США, но превышает 30 000 долл. США, D $ 4 * D7 умножает зарплату на ставку вычета 8%.

Введите учебные данные

Введите данные в ячейки с C1 по E6 рабочего листа Excel, как показано на рисунке. Единственными данными, которые не были введены в этот момент, является сама функция IF, расположенная в ячейке E7.

Примечание . Инструкции по копированию данных не содержат этапов форматирования рабочего листа. Это не мешает завершению урока. Ваш рабочий лист может отличаться от показанного в примере, но функция IF даст вам те же результаты.

Запустите вложенную функцию IF

Можно просто ввести полную формулу

 = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7)) 

в ячейку E7 рабочего листа и заставить его работать. В Excel Online это метод, который вы должны использовать. Однако, если вы используете настольную версию Excel, часто проще использовать диалоговое окно функции для ввода необходимых аргументов.

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

В этом примере вложенная функция IF вводится в третью строку диалогового окна в качестве аргумента Value_if_false. Поскольку на рабочем листе рассчитывается ежегодное удержание для нескольких сотрудников, формула сначала вводится в ячейку E7 с использованием абсолютных ссылок на ячейки для ставок удержания, а затем копируется в ячейки E8: E11.

Учебное пособие

  1. Выберите ячейку E7 , чтобы сделать ее активной. Именно здесь будет расположена вложенная формула IF.
  2. Выберите Формулы .
  3. Выберите Логический , чтобы открыть раскрывающийся список функций.
  4. Выберите IF в списке, чтобы открыть диалоговое окно функции.

Данные, введенные в пустые строки в диалоговом окне, образуют аргументы функции IF. Эти аргументы сообщают функции проверяемое условие и какие действия предпринять, если условие истинно или ложно.

Вариант ярлыка учебника

Чтобы продолжить этот пример, вы можете:

  • Введите аргументы в диалоговом окне, как показано на рисунке выше, а затем перейдите к последнему шагу, который охватывает копирование формулы в строки с 7 по 10.
  • Или выполните следующие шаги, которые предлагают подробные инструкции и объяснения для ввода трех аргументов.

Введите аргумент Logical_test

Аргумент Logical_test сравнивает два элемента данных. Эти данные могут быть числами, ссылками на ячейки, результатами формул или даже текстовыми данными. Чтобы сравнить два значения, Logical_test использует оператор сравнения между значениями.

В этом примере есть три уровня заработной платы, которые определяют ежегодное удержание сотрудника:

  • Менее 30000 долларов.
  • От 30 000 до 49 999 долларов.
  • 50 000 долларов США или больше

Одна функция IF может сравнивать два уровня, но третий уровень зарплаты требует использования второй вложенной функции IF.Первое сравнение проводится между годовой зарплатой сотрудника, расположенной в ячейке D, с пороговой зарплатой в 30 000 долл. США. Поскольку цель состоит в том, чтобы определить, составляет ли D7 менее 30 000 долларов, между значениями используется оператор Less Than ( <).

Учебное пособие

  1. Выберите строку Logical_test в диалоговом окне.
  2. Выберите ячейку D7 , чтобы добавить эту ссылку на ячейку в строку Logical_test.
  3. Нажмите клавишу «меньше» ( <) на клавиатуре.
  4. Введите 30000 после символа «меньше».
  5. Завершенный логический тест отображается как 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 диалогового окна.

Учебное пособие

  1. Выберите строку Value_if_true в диалоговом окне.
  2. Выберите ячейку D3 на листе, чтобы добавить эту ссылку на ячейку в строку Value_if_true.
  3. Нажмите клавишу F4 , чтобы сделать D3 абсолютной ссылкой на ячейку ($ D $ 3).
  4. Нажмите клавишу со звездочкой ( * ). Звездочка – это символ умножения в Excel.
  5. Выберите ячейку D7 , чтобы добавить эту ссылку на ячейку в строку Value_if_true.
  6. Завершенная строка 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.

Примечание . Вложенные функции начинаются не со знака равенства, а с названия функции.

  1. Выберите строку Value_if_false в диалоговом окне.
  2. Введите следующую функцию IF:
     IF (D7> = 50000, $ D $ 5 * D7, $ D $ 4 * D7) 
  3. Выберите ОК , чтобы завершить функцию ПЧ и закрыть диалоговое окно.
  4. Значение 3678,96 долл. США отображается в ячейке E7. Поскольку Р. Холт зарабатывает более 30 000 долларов США, но менее 50 000 долларов США в год, для расчета его ежегодного вычета используется формула 45 987 * 8%.
  5. Выберите ячейку E7 , чтобы отобразить полную функцию = IF (D7 = 50000, $ D $ 5 * D7, $ D $ 4 * D7)) на панели формул над рабочим листом.

После выполнения этих шагов ваш пример теперь соответствует первому изображению в этой статье.

Последний шаг включает в себя копирование формулы IF в ячейки E8-E11 с использованием маркера заполнения для заполнения таблицы.

Скопируйте вложенные функции IF, используя маркер заполнения

Чтобы заполнить рабочий лист, скопируйте формулу, содержащую вложенную функцию IF, в ячейки E8-E11. Когда функция копируется, Excel обновляет относительные ссылки на ячейки, чтобы отразить новое местоположение функции, сохраняя при этом абсолютную ссылку на ячейку.

Один простой способ скопировать формулы в Excel – с помощью ручки заполнения.

Учебное пособие

  1. Выберите ячейку E7 , чтобы сделать ее активной.
  2. Поместите указатель мыши на квадрат в правом нижнем углу активной ячейки. Указатель изменится на знак плюс (+).
  3. Выберите и перетащите маркер заполнения вниз в ячейку E11.
  4. Клетки E8-E11 заполнены результатами формулы, как показано на рисунке выше.
Оцените статью
Solutics.ru
Добавить комментарий