Использование вложенных функций ЕСЛИ()

Ранее рассматривались задачи, в которых путь решения выбирался в зависимости от двух ситуаций: выполняется условие или не выполняется условие. И, если условие выполнялось, то вычисления производились по Формуле 1, а иначе – по Формуле 2. При этом условия могли быть как простыми, так и сложными.

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

Рассмотрим задачу вычисления процента премии в зависимости от стажа работы сотрудника.

Если стаж меньше или равен 10 годам, то сотрудник получает 30% от базовой премии. Если стаж большее 10 лет, но меньше или равен 25 годам, то сотрудник получает 60% от базовой премии. В остальных случаях сотрудник получает всю базовую премию.

Стаж работы

10 лет

25 лет

30% премии

60% премии

100% премии

Рис. 26. Выбор из 3-х ситуаций

Из рис. 26 видно, что нужно рассмотреть 3 ситуации.

Вопрос в том, сколько функций ЕСЛИ() нужно использовать для рассмотрения всех ситуаций.

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

Рассмотрим ситуации, когда Стаж=7; Стаж=18; Стаж=27.

а) Стаж=7.

Вопросы: Стаж < 10? ДА. 1 вопрос.

б) Стаж=18.

Вопросы: Стаж < 10? НЕТ. Стаж < 25? ДА. 2 вопроса.

Если на вопрос «Стаж < 10?» получен ответ НЕТ, то понятно, что не нужно задавать вопрос «Стаж >= 10?», подразумевается, что на него получен ответ ДА.

в) Стаж=27

Вопросы: Стаж < 10? НЕТ. Стаж < 25? НЕТ. 2 вопроса.

Если на вопрос «Стаж < 25?» получен ответ НЕТ, то понятно, что не нужно задавать вопрос «Стаж >= 25?», подразумевается, что на него получен ответ ДА.

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

стаж< 10

стаж< 25

да

нет

Вычислить 30% от премии

Ячейка столбца F =1

Вычислить 100% от премии

Рис. 27. Блок-схема выбора из 3-х ситуаций

Более формальное правило определение количества функций ЕСЛИ():

· Одну функцию ЕСЛИ() можно использовать тогда, когда нужно рассмотреть 2 ситуации.

· В случае множественного выбора из 3-х ситуаций нужно использовать 2 функции ЕСЛИ().

· В случае множественного выбора из 4-х ситуаций нужно использовать 3 функции ЕСЛИ().

· В случае множественного выбора из N ситуаций нужно использовать N-1 функцию ЕСЛИ().

На Рис. 27 приведен фрагмент блок-схемы множественного выбора из 3-х ситуаций. Для проверки условия здесь используется логический блок. Количество логических блоков соответствует количеству используемых функций ЕСЛИ().

Рассмотрим решение задачи «Выплата премии» в среде табличного процессора.

Усл. 1

Усл. 2

да

нет

Формула 1

Формула 3

Формула 4

Формула 2

Рис. 28. Вложенные ЕСЛИ()

Обозначим базовую премию, как БП. Тогда расчет премии в терминах функции ЕСЛИ() будет выглядеть, как это показано на Рис. 28:

ЕСЛИ(Стаж<10;30%*БП;ЕСЛИ(СТАЖ<25;60%*БП;БП))

ЕСЛИ(Усл.1.;Формула 1; Формула 2)

Если Усл.1. выполняется, то вычисляется Формула 1, иначе (Усл.1 не выполняется), вычисления производятся по Формуле 2.

В данном случае Формула 2 содержит еще одно ЕСЛИ(), выполнение которого происходит по уже известному алгоритму (Рис. 28).

Формула 2: ЕСЛИ(СТАЖ<25; 60%*БП; БП)

ЕСЛИ(Усл. 2; Формула 3; Формула 4)

Рис. 29. Задача Расчет премии в среде Excel

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

Рассмотрим сначала 12-ю строчку электронной таблицы. Она соответствует сотруднику Борисову, стаж которого - 3 года. Для него логическое выражение С12<10 имеет значение ИСТИНА – (3 < 10), поэтому вычисления проводятся по Формуле 1. Для Борисова премия составляет 30% от базовой премии.

Рассмотрим 11-ю строчку электронной таблицы. Она соответствует сотруднику Ананьеву, стаж которого – 18 лет. Для него логическое выражение С11<10 имеет значение ЛОЖЬ – (18 < 10), поэтому вычисления проводятся по Формуле 2. Но Формула 2 снова содержит ЕСЛИ(), в котором анализируется выражение С11<25. В данном случае оно принимает значение ИСТИНА – (18<25), и вычисления производятся по Формуле 3. Ананьев получил 60% от базовой премии.

15-я строчка соответствует сотруднику Данилову, стаж работы которого – 27 лет. Для него логическое выражение С15<10 принимает значение ЛОЖЬ – (25 <10), поэтому вычисления проводятся по Формуле 2. Но Формула 2 снова содержит ЕСЛИ(), в котором анализируется выражение С15<25. В данном случае оно снова принимает значение ЛОЖЬ – (27<25), и вычисления производятся по Формуле 4. Данилов получил всю базовую премию.

При решении задачи с вложенными ЕСЛИ() работа разбивается на следующие этапы:

Шаг 1. Запустить Мастер функций и выбрать функцию ЕСЛИ().

Шаг 2. В окне Аргументы функции первой функции ЕСЛИ() и ввести значения в поля Лог_выражение и Значение_если_истина (Рис. 30).

Рис. 30. Окно Аргументы функции 1-ой функции ЕСЛИ()

Шаг 3. Щелкнуть в поле Значение_если_ложь, выбрать новую функцию ЕСЛИ и в окне Аргументы функции для вложенной функции ЕСЛИ() (Рис. 31) ввести значения в поля Лог_выражение и Значение_если_истина новой функции ЕСЛИ().

Рис. 31. Окно Аргументы функции вложенной функции ЕСЛИ()

Шаг 4. Если больше вложенных функций нет, то ввести значение в поле Значение_если_ложь и закончить работу с Мастером, нажав ОК. Если еще есть вложенные функции, то необходимо повторить действия с шага 3.

На рис. 31 показано окно Аргументы функции для второй функции ЕСЛИ() со значениями всех аргументов. Это окно можно вызвать из строки формул с помощью клавиш SHIFT-F3, находясь в поле первой функции.

Для корректировки значений аргументов любой функции достаточно перейти в строку формул в поле нужной функции и нажать SHIFT-F3.

На рис. 30 и рис. 31 показана поэтапная работа с Мастером функций для сотрудника Ананьева (11 строчка электронной таблицы), чей стаж равен 18 годам.

В окне Аргументы Функции первой функции ЕСЛИ() –(Рис. 30) рядом с логическим выражением С11<10 стоит его значение – ЛОЖЬ.

В окне Аргументы Функции вложенной функции ЕСЛИ() – рис. 31, рядом с логическим выражением С11<25 стоит его значение – ИСТИНА. Значит, вычисления будут производиться по формуле, находящейся в поле Значение_если_истина. Полученный размер премии равен 6000. В нижней части окна видно и значение вложенной функции – 6000.

На рис. 32 видны окончательные результаты решения задачи для сотрудника Ананьева. Показаны значения всех аргументов функции ЕСЛИ(), одним из которых (значение_если_ложь) является вложенная функция ЕСЛИ() и значение всей функции.