Понятие ветвления
При решении некоторых задач в Excel значение ячейки необходимо вычислить одним из нескольких способов, в зависимости от выполнения или невыполнения одного или нескольких условий. Схематично это можно представить следующим образом:
Рисунок 5 - Блок-схема разветвляющегося алгоритма
Конструкция, в которой в зависимости от условия осуществляется то или иное действие, называется ветвлением.
Условие представляет собой величины и/или выражения одного типа, связанные между собой одним из знаков отношений: > (больше); < (меньше); >= (больше или равно); <= (меньше или равно); = (равно); <> (не равно). Условие принимает одно из двух возможных значений истина или ложь.
Условия бывают:
простые;
сложные (составные).
Простое условие соединяет между собой величины или выражения только одним из знаков отношений.
Например, а>5, (a+b)<0, 5>3 и т.п.
Сложным (составным) условием называется несколько простых условий, связанных между собой с помощью логических операций И (логические умножение), ИЛИ (логическое сложение).
Формирование ветвлений с простыми условиями
Для реализации ветвлений в Excel используется логическая функция ЕСЛИ.
Формат записи:
= ЕСЛИ(условие;действие1;действие2)
Эта запись означает:
если условие выполняется, то выполняется действие1;
в противном случае, т.е. если не выполняется условие, то выполняется действие 2
Функция ЕСЛИ используется для выполнения действий (принятия решения) в зависимости от условия.
В качестве действия1 или действия2 могут быть:
числовое выражение;
функция или формула;
ссылка на ячейку таблицы;
заключенный в кавычки (“ “) текст.
Задача1: В ячейку В1 следует записать число 1, если положительно произведение ячеек А1 и А2, в противном случае записать в ячейку В1 число 0.
Решение: Для выполнения этого задания в ячейку В1 следует записать формулу: =ЕСЛИ(А1*А2>0; 1;0)
Задача2. Пусть ряду работников начислена заработная плата, которая отображена в столбце В (рисунок 2). В столбце С необходимо рассчитать подоходный налог с каждого работника, по следующей схеме:
если размер заработной платы менее 2000 руб, то с работника налог взымается в размере 10% от заработной платы, в противном случае 12%. В столбце D получить суммы, которые получит каждый работник в итоге.
Рисунок 6 – Примерный вид таблицы
Решение.
в ячейку С2 следует ввести формулу:
=ЕСЛИ(В2<2000;B2*10%;B2*12%)
скопировать эту формулу в ячейки диапазона С3:С6. В результате в столбце С будет получена сумма налога, удержанная с каждого работника;
в ячейку D2 ввести формулу для подсчета итоговой суммы: =B2-C2
скопировать эту формулу в ячейки D3:D6. В результате таблица примет следующий вид, изображенный на рисунке 7.
Рисунок 7 – Таблица для расчета заработной платы работникам
Формирование ветвлений со сложными условиями
Для формирования сложных условий используются следующие логические функции:
И (AND);
ИЛИ (OR);
Формат записи функции И:
И(условие1; условие2,…)
Данная функция используется в случаях, когда простые условия соединены между собой союзом «И».
Например, условие «число в ячейке А1 – положительное и больше 100» можно записать следующим образом:
И(А1>0;A1>100)
Функция И возвращает значение ИСТИНА, если все ее аргументы имеют значение ИСТИНА; значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Функция И может содержать от 1 до 30 проверяемых условий.
Например,
функция =И(2+2=4; 2+3=5) примет значение ИСТИНА, т.к. оба простых условия истины;
если ячейка B4 =160, то функция =И(B4>1; B4<100) возвратит значение ЛОЖЬ, т.к. одно из условий принимает значение ЛОЖЬ.
Задача: Предположим, что ячейка С2 принимает значение ячейки B4, если В4 содержит число строго между 1 и 100, в противном случае в ячейке С2 вывести сообщение "Значение вне интервала".
Решение: Для реализации поставленной задачи в ячейку С2 следует записать следующую формулу:
=ЕСЛИ(И(1<B4; B4<100); B4; "Значение вне интервала")
Формат записи функции ИЛИ:
ИЛИ(условие1; условие2,…)
Функция ИЛИ используется в случаях, когда простые условия соединены между собой союзом «ИЛИ».
Например, условие «число в ячейке А1 меньше -5 или больше 10» можно записать следующим образом:
ИЛИ(А1<-5;A1>10)
Данная функция возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ. Функция ИЛИ также может содержать от 1 до 30 проверяемых условий.
Например, ИЛИ(1+1=1;2+2=5) равняется ЛОЖЬ.