Настройка данных ячейки с помощью функции IF в Excel

Как работает функция IF

Обзор функций IF

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

Основная форма или синтаксис функции IF в Excel:

 = IF (logic_test, value_if true, value_if_false) 

Что делает функция:

  • Проверяет, является ли указанное условие – логический тест – истинным или ложным.
  • Если условие истинно, функция будет выполнять действие, указанное в аргументе value if true
  • Если условие ложно, оно будет выполнять действие, указанное в значении if false .

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

ЕСЛИ Функция пошаговое руководство

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

 = IF (D6 <30000, $ D $ 3 * D6, $ D $ 4 * D6) 

Внутри круглых скобок три аргумента выполняют следующие задачи:

  1. логический тест проверяет, составляет ли зарплата сотрудника менее 30 000 долларов США.
  2. Если значение меньше $ 30000, аргумент value if true умножает зарплату на ставку вычета 6%
  3. Если не менее $ 30000, аргумент value if false умножает зарплату на ставку вычета 8%

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

Шаги по обучению

  1. Ввод данных учебника
  2. Запуск функции IF
  3. Ввод логического аргумента теста
  4. Ввод значения, если истина Аргумент
  5. Ввод значения, если false аргумент и завершение функции IF
  6. Копирование функции IF с использованием маркера заполнения

Ввод данных учебника

Введите данные в ячейки С1 по Е5 рабочего листа Excel, как показано на рисунке выше.

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

Для тех, кто не хочет печатать, используйте эти инструкции для копирования данных в таблицу Excel.

Примечание. Инструкции по копированию данных не включают в себя этапы форматирования рабочего листа.

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

Запуск функции IF

Диалоговое окно «Функция IF»

Хотя можно просто набрать функцию IF

 = IF (D6 <30000, $ D $ 3 * D6, $ D $ 4 * D6) 

В ячейку E6 на рабочем листе многим людям проще использовать диалоговое окно функции для ввода функции и ее аргументов.

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

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

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

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

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

Данные, которые будут введены в три пустые строки в диалоговом окне, сформируют аргументы функции IF.

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

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

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

Ввод логического аргумента теста

Ввод логического аргумента теста

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

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

В этом уроке проводится сравнение между значением в ячейке E6 и пороговой зарплатой в 30 000 долларов.

Поскольку цель состоит в том, чтобы выяснить, меньше ли E6, чем 30 000 долларов, используется оператор Less Than " <".

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

  1. Нажмите на строку Logical_test в диалоговом окне.
  2. Нажмите на ячейку D6, чтобы добавить эту ссылку на ячейку в строку Logical_test .
  3. Введите на клавиатуре менее чем « <».
  4. Введите 30000 после символа «меньше».
  5. Примечание . Не вводите знак доллара ($) или разделитель запятых (,) с указанной выше суммой. Сообщение об ошибке Invalid появится в конце строки Logical_test , если любой из этих символов будет введен вместе с данными.
  6. Завершенный логический тест должен выглядеть следующим образом: D6 <3000

Ввод значения, если истинный аргумент

Ввод аргумента Value_if_true

Аргумент Value_if_true сообщает функции IF, что делать, если логический тест равен true.

Аргумент Value_if_true может быть формулой, блоком текста, числом, ссылкой на ячейку или ячейку можно оставить пустым.

В этом учебном пособии, если годовая зарплата сотрудника, расположенная в ячейке D6, составляет менее 30 000 долл. США, функция IF должна использовать формулу для умножения зарплаты на ставку вычета 6%, расположенную в ячейке D3.

Относительная или абсолютная ячейка

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

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

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

Однако иногда, когда ссылки на ячейки меняются при копировании функции, это приводит к ошибкам.

Чтобы предотвратить такие ошибки, ссылки на ячейки могут быть сделаны Absolute , что предотвращает их изменение при копировании.

Абсолютные ссылки на ячейки создаются путем добавления знаков доллара вокруг обычной ссылки на ячейку, например $ D $ 3.

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

Абсолютные ссылки на ячейки

В этом руководстве две ссылки на ячейки, которые должны оставаться одинаковыми для всех экземпляров функции IF, - это D3 и D4 - ячейки, содержащие ставки вычетов.

Следовательно, для этого шага, когда ссылка на ячейку D3 вводится в строку Value_if_true диалогового окна, она будет использоваться в качестве абсолютной ссылки на ячейку $ D $ 3.

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

  1. Нажмите на строку Value_if_true в диалоговом окне.
  2. Нажмите на ячейку D3 на листе, чтобы добавить эту ссылку на ячейку в строку Value_if_true .
  3. Нажмите клавишу F4 на клавиатуре, чтобы сделать E3 абсолютной ссылкой на ячейку ( $ D $ 3 ).
  4. Нажмите звездочку ( * ) на клавиатуре. Звездочка - это символ умножения в Excel.
  5. Нажмите на ячейку D6, чтобы добавить эту ссылку на ячейку в строку Value_if_true .
  6. Примечание. D6 не вводится как абсолютная ссылка на ячейку, поскольку его необходимо изменить при копировании функции.
  7. Заполненная строка Value_if_true должна выглядеть следующим образом: $ D $ 3 * D6 .

Ввод значения, если ложный аргумент

Ввод аргумента Value_if_false

Аргумент Value_if_false сообщает функции IF, что делать, если Logical Test имеет значение false.

Аргумент Value_if_false может быть формулой, блоком текста, значением, ссылкой на ячейку или ячейку можно оставить пустым.

В этом учебном пособии, если годовая зарплата сотрудника, расположенная в ячейке D6, составляет не менее 30 000 долл. США, функция IF должна использовать формулу для умножения зарплаты на ставку вычета 8%, расположенную в ячейке D4.

Как и на предыдущем шаге, для предотвращения ошибок при копировании завершенной функции ПЧ коэффициент вычета в D4 вводится как абсолютная ссылка на ячейку ( $ D $ 4 ).

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

  1. Нажмите на строку Value_if_false в диалоговом окне.
  2. Нажмите на ячейку D4, чтобы добавить ссылку на эту ячейку в строку Value_if_false .
  3. Нажмите клавишу F4 на клавиатуре, чтобы сделать D4 абсолютной ссылкой на ячейку ( $ D $ 4 ).
  4. Нажмите звездочку ( * ) на клавиатуре. Звездочка - это символ умножения в Excel.
  5. Нажмите на ячейку D6, чтобы добавить эту ссылку на ячейку в строку Value_if_false .
  6. Примечание. D6 не вводится как абсолютная ссылка на ячейку, поскольку его необходимо изменить при копировании функции.
  7. Заполненная строка Value_if_false должна иметь вид: $ D $ 4 * D6 .
  8. Нажмите OK, чтобы закрыть диалоговое окно и ввести завершенную функцию IF в ячейку E6.
  9. Значение 3678,96 долл. США должно появиться в ячейке E6.
  10. Поскольку Б. Смит зарабатывает более 30 000 долларов в год, функция IF использует формулу 45 987 * 8% для расчета своего ежегодного вычета.
  11. Если щелкнуть ячейку E6, полная функция
    = IF (D6 <3000, $ D $ 3 * D6, $ D $ 4 * D6) появится в строке формул над рабочим листом.

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

Копирование функции IF с помощью ручки заполнения

Копирование функции IF с использованием маркера заполнения

Чтобы заполнить рабочий лист, нам нужно добавить функцию IF в ячейки E7-E10.

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

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

Чтобы скопировать нашу функцию, мы будем использовать Fill Handle.

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

  1. Нажмите на ячейку E6, чтобы сделать ее активной.
  2. Поместите указатель мыши на черный квадрат в правом нижнем углу. Указатель изменится на знак плюс "+".
  3. Нажмите левую кнопку мыши и перетащите маркер заливки вниз в ячейку F10.
  4. Отпустите кнопку мыши. Ячейки с E7 по E10 будут заполнены результатами функции IF.
Оцените статью
Solutics.ru
Добавить комментарий