ifempty

    Урок 2. Функция ЕСЛИ

    Оглавление

    Введение

    Функция ЕСЛИ - оператор сравнения Равно

    Функция ЕСЛИ - оператор сравнения Больше или равно

    Функция ЕСЛИ - оператор сравнения И или ИЛИ

    Файлы для скачивания

     

    Введение

    Функция ЕСЛИ является одной из логических функций MS Excel. Функция определяет выполняется ли заданное условие и производит нужные действия в случае его выполнения или невыполнения. Функцию можно вызвать через вкладку Формулы - -> Логические или через мастер функций ƒx, выбрав категорию логические (см. рис.1, 2)

     2 рис 1

    Рисунок 1. Вызов функции ЕСЛИ через вкладку Формулы

     2 рис 2

    Рисунок 2. Вызов функции ЕСЛИ через мастер функций

    У вас откроется окно - Аргументы функции, которое состоит из 3-х полей (см. рис.3):

    1. Логическое выражение - в данное поле нужно записать условие;
    2. Значение_если_истина - в данное поле записывается значение, которое должно быть получено при выполнении условия;
    3. Значение_если_ложь - в данное поле записывается значение, которое должно быть получено при невыполнении условия.

     2 рис 3

    Рисунок 3. Форма окна - аргументы функции ЕСЛИ

    Условие в логическое выражение записывается с помощью операторов сравнения. Можно использовать следующие операторы сравнения: 

    Таблица 1. Список операторов сравнения. 
     №   Знак оператора сравнения  Описание
    1 = равно
    2 <> неравно
    3 > больше
    4 < меньше
    5 >= больше или равно
    6 <= меньше или равно
    7 И логическая функция И
    8 ИЛИ логическая функция ИЛИ

    Дальше на примерах попробуем разобрать, как работает функция ЕСЛИ  с разными операторами сравнения.

     

    Функция ЕСЛИ  - оператор сравнения "равно"

    Cкачайте файл Приложение 1 - Оператор сравнения Равно.xlsx по ссылке ниже.  Для работы используйте лист Задача без решения. Для подсказки см. лист Задача с решением.

    Откройте скачанный файл Приложение 1 - Оператор сравнения Равно.xlsx , лист Задача без решения. На листе вы увидите таблицу, как на рисунке 4 - с формой табеля, в котором указаны количество часов и посчитаны общие суммы отработанных часов. 

    Если наверху окна вы видите надпись Защищенный просмотр, как показано на рисунке 4 ниже, то нужно нажать на кнопку Разрешить редактирование.

     2 рис 7

     Рисунок 4. Отключение защищенного просмотра

    Задача:

    Рабочие получают оплату по ставке 700 тенге/час за часы отработанные в течение нормальной продолжительности рабочего дня, и по ставке 1050 тенге/час за часы отработанные сверхурочно. Нужно посчитать сумму заработанных средств рабочими (см. рис.5).

     2 рис 4

    Рисунок 5. Нужно посчитать значения по столбцу "Сумма"

    Предварительный разбор задачи:

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

    Для начала, функцию ЕСЛИ нужно вызывать в ячейку N3  - это первая ячейка по графе Сумма. Затем один раз написав функцию, ее нужно будет протянуть вниз с помощью маркера автозаполнения для остальных ячеек столбца Сумма. В зависимости от того какой тип работы (нормальная продолжительность дня или сверхурочные часы), мы должны будем использовать либо ставку в ячейке В13, либо ставку в ячейке В14. Признаком типа работы являются буквы Н и С по столбцу Норма/сверхурочн. Если стоит буква Н - значит часы указаны для нормальной продолжительности дня, если стоит буква С  - значит, это часы сверухрочных работ. Ссылки на ячейки В13 и В14 нужно зафиксировать, так как они являются постоянными и не меняются, и когда мы будем тянуть формулу вниз, ссылки на эти ячейки не должны измениться. 

    Вызов функции ЕСЛИ:

    В ячейку N3 вызываем функцию ЕСЛИ. Для этого встаем на ячейку N3, переходим на вкладку Формулы и в разделе Логические выбираем ЕСЛИ (вы также можете вызвать функцию через мастер функций с помощью кнопки ƒx).

    Заполняем поля следующими данными (см. рис.6):

    1. В поле Логическое выражение указываем: B3 = "Н"
    2. В поле Значение_если_истина указываем: M3*$B$13
    3. В поле Значение_если_ложь указываем: M3*$B$14 

     2 рис 5

    Рисунок 6. Функция ЕСЛИ с оператором сравнения РАВНО (=)

    После того, как вы закончите ввод данных, нажмите ОК. У вас должно получиться, как показано на рисунке 7.

     2 рис 10

     Рисунок 7. Результат вызова функции ЕСЛИ

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

     2 рис 6

    Рисунок 8. Столбец Сумма после ввода функции ЕСЛИ

    Разбор проделанной работы:

    Теперь давайте попробуем разобраться с написанной функцией. Откройте окно с настройками функции первой ячейки - для этого встаньте на ячейку N3 и нажмите кнопку ƒx, как показано на рисунке 9:

     2 рис 8

     Рисунок 9. Повторное открытие настройки функции 

    У вас должно открыться окно Аргументы функции, как на рисунке 10:

     2 рис 9

     Рисунок 10. Форма окна Аргументы функции ЕСЛИ

    Посмотрим внимательно на указанные аргументы функции. Словами настройки функции можно было бы выразить так:

    Если значение ячейки В3 равно букве Н, то тогда значение в ячейке М3 умножаем на значение в ячейке В13. А если нет, то тогда значение в ячейке М3 умножаем на значение в ячейке В14.
     

    Обратите внимание, что буква Н взята в кавычки. Это правило для строковых типов данных - строковые типы данных (предложения, слова, фразы, буквы, знаки табуляции, знаки препинания) в формулах/функциях указываются в кавычках. 

     Функция ЕСЛИ правило1

    А дальше, написав функцию для первой ячейки, чтобы заново не писать формулу для остальных ячеек, с помощью маркера автозаполнения мы просто протянули формулу вниз до 10-й строки. Далее обратите внимание, что ссылки на ячейки  B13 и B14 зафиксированы с помощью знаков $ (то есть являются абсолютными). Это было сделано для того, чтобы ссылки на эти ячейки остались неизменными, когда будем тянуть формулу вниз по столбцу. 

     

    Функция ЕСЛИ  - оператор сравнения "больше или равно"

    Cкачайте файл Приложение 2 - Оператор сравнения Больше или равно.xlsx по ссылке ниже. Для работы используйте лист Задача без решения. Для подсказки см. лист Задача с решением.

    Откройте скачанный файл Приложение 2 - Оператор сравнения Больше или равно.xlsx, лист  - Задача без решения. Если у вас стоит защищенный просмотр, то нажмите на кнопку разрешить редактирование, как показано на рисунке 11.

     2 рис 11Рисунок 11. Разрешение редактирования файла

    Задача:

    На листе вы увидите таблицу со списком сотрудников, как показано на рисунке 12. Нужно посчитать суммы обязательных пенсионных взносов. Пенсионные взносы расчитываются как оклад умноженный на ставку (10%). Но при этом согласно законодательству есть ограничения в виде максимального возможного дохода для исчисления обязательных пенсионных взносов, сумма которого равна 75-ти минимальным размерам зарплаты (75 МЗП (2 121 300 тнг на 2018 год)). 

      2 рис 12

    Рисунок 12. Практикум 2. Расчет ОПВ

    Предварительный разбор задачи:

    Функцию нужно будет вызывать в ячейку D2, так как это первая ячейка столбца ОПВ. После того, как будет написана функция ее нужно будет протянуть вниз с помощью маркера автозаполнения. Показатели Ставка ОПВ, МЗП, Максимальный доход для ОПВ являются константами, то есть это постоянные значения и ссылки на них меняться не должны, поэтому их нужно будет фиксировать. Логика создания функции следующая:

    Если оклад сотрудника выше или равен максимальному доходу для расчета ОПВ -  2 121 300 ( 75 * МЗП ), то размер ОПВ будет  - 212 130 тенге (2 121 300 * ставку ОПВ). Если оклад сотрудника меньше максимального доходу для расчета ОПВ, то тогда размер ОПВ будет определяться как оклад * ставку ОПВ.

    Вызов функции ЕСЛИ:

    В ячейке D2 вызываем функцию ЕСЛИ. Для этого становимся на ячейку D2, переходим на вкладку Формулы и в разделе Логические выбираем ЕСЛИ (вы также можете вызвать функцию через мастер функций с помощью кнопки ƒx).

    Заполняем поля следующими данными (см. рис.13):

    1. В поле Логическое выражение указываем: C2>=$J$2
    2. В поле Значение_если_истина указываем: $J$2*$H$2
    3. В поле Значение_если_ложь указываем: C2*$H$2

      2 рис 13

    Рисунок 13. Вызов функции ЕСЛИ

     После того, как вы закончите ввод данных, нажмите ОК. У вас должно получиться, как показано на рисунке 14.

     2 рис 14Рисунок 14. Результат вызова функции ЕСЛИ

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

     2 рис 15

    Рисунок 15. Столбец ОПВ после ввода функции ЕСЛИ. 

    Разбор проделанной работы:

    Теперь давайте попробуем разобраться с написанной функцией. Откройте окно с настройками функции первой ячейки - для этого встаньте на ячейку D2 и нажмите кнопку ƒx, как показано на рисунке 16:

     2 рис 16Рисунок 16. Открытие настроек написанной функции

     У вас должно открыться окно Аргументы функции, как на рисунке 17:

     2 рис 17

     Рисунок 17. Аргументы функции ЕСЛИ 

    Посмотрим внимательно на указанные аргументы функции. Словами настройки функции можно было бы выразить так:

    Если значение ячейки С2 больше или равно значению ячейки J2, то тогда значение ячейки J2 * значение ячейки H2. А если нет (то есть, получается,  C2 меньше J2), то тогда значение ячейки C2 * значение ячейки H2.

    Обратите внимание, что ссылки на ячейки J2 и H2 зафиксированы с помощью знаков $, так как ячейка со ставкой ОПВ (J2)  и ячейка с максимальным доходом для расчета ОПВ (H2) являются постоянными (константами) и ссылки на них меняться не должны. Более подробно смотрите урок по ссылкам. 

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

     

    Операторы сравнения И и ИЛИ

    Скачайте файл Приложение 3 - операторы сравнения И и ИЛИ.xlsx по ссылке ниже.

    Откройте лист Задача без решения. На листе дана таблица1, где находистя список сотрудников.

    Задача:

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

    Вызов функции ЕСЛИ

    Становимся на ячейку F4, переходим на вкладку Формулы, в категории Логические выбираем функцию ЕСЛИ. Далее внутрь Логического выражения нужно вложить функцию И. Для этого нужно в левом верхнем углу окна нажать на поле ИМЯ, в котором в выпадающем списке функци выбрать функцию И (см. рисунок 18).

     функция если рис18

    Рисунок 18. Вложение функции И внутрь логического выражения функции ЕСЛИ

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

    Далее в окне Аргументы функции нужно указать условия:

     функция если рис19

    Рисунок 19. Заполнение аргументов функции ЕСЛИ

    Далее нужно перейти в функцию ЕСЛИ. Для этого нужно в строке формул нажать на ЕСЛИ,  и затем заполнить аргументы функции ЕСЛИ, как показано на рисунке 20:

     функция если рис 20

    Рисунок 20. Заполнение аргументов функции ЕСЛИ

    Теперь нужно заполнить столбец G. Становимся на ячейку G4 и вызываем функцию ЕСЛИ. Далее внутрь логического выражения нужно вложить функцию ИЛИ. Далее нужно заполнить аргументы функции, как показано на рисунке 21:

     функция если рис 21

    Рисунок 21. Заполнение аргументов функции ИЛИ

    Далее в строке формул нужно перейти в функцию ЕСЛИ и заполнить ее аргументы, как показано на рисунке 22:

     функция если рис 22

    Рисунок 22. Заполнение аргументов функции ЕСЛИ

     

    Файлы для скачивания:

     

     

    Зарегистрируйтесь и получите пробный однодневный доступ к полным материалам курса!  В него входит: учебник, видеоурок, практика, тестирование. После входа в систему вам будет доступен личный кабинет с курсами.

    Войти с помощью: 


    Пробная подписка 1 День

    Пробная подписка на 1 день

    Вы можете активировать только 1 раз.


    MS Excel Basic Навсегда


    MS Excel Intermediate Навсегда

    Курс Basic получаете в подарок


    MS Excel Advanced Навсегда

    Курсы Basic и Intermediate получаете в подарок.