Логическая функция ЕСЛИ

На рис. 4.32 показана электронная таблица, с которой мы будем производить расчеты. Рассмотрим применение функции ЕСЛИ на примерах.

Рисунок 4.32 - Таблица с исходными данными

В таблице есть данные об общем количестве выставленного на продажу товара и количестве проданного. Допустим, требуется изменить цену на товар, который не пользуется спросом. Например, если продано менее 50% товара, следует уменьшить цену на него на 30%. Используем функцию ЕСЛИ (категория «Условные»). Ее синтаксис имеет вид:

=ЕСЛИ (условие; выражение1; выражение2)

При выполнении этой функции происходит проверка условия, если оно выполняется, то в ячейку записывается выражение1, в противном случае – выражение2.

При вводе условия разрешается использовать шесть знаков операций сравнения: < (меньше), > (больше), < =(меньше или равно), > =(больше или равно), =(равно), < > (неравно). Условия могут выглядеть следующим образом:

A2< 3; B2< =sin(F2); C2=сумм(F2: F20);

A2> B2+5; A2> =F2*8+6; A2< =мин(F2: F20).

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

Вернемся к нашей задаче, для ее решения следует сначала ввести вспомогательный столбец процента продажи. На Рис. 4.33 показана таблица с расчетными формулами. В ячейку А7 мы ввели название нового столбца, в А8 – формулу для расчета процента продаж =D2/C2*100, которая копировалась вниз для определения процентов продаж каждого из товаров. Нами использована относительная адресация, т.к. в каждой строке содержится информация о новом товаре.

Функция ЕСЛИ введена в виде:

=ЕСЛИ(A8< 50; B2*0, 7; B2)

Рисунок 4.33 - Формулы для расчета

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

=ЕСЛИ(A8< 50; B2*0, 7).

В случае невыполнения условия появился бы текст «ЛОЖЬ». Согласитесь, надпись на ценнике товара не очень располагает к покупке.

Таблица 4.6 - Результат работы логической функции ЕСЛИ

Поэтому, хотя и разрешается использовать неполную функцию ЕСЛИ, этого лучше не делать.

1. Усложним задачу. Допустим, если продано более 75%, мы хотим увеличить цену на товар на 10%, если продано более 50%, но менее 75%, то цена должна остаться прежней, если продано от 10 до 50%, цену уменьшить в два раза, и, наконец, если продано менее 10%, считаем нецелесообразным дальнейший выпуск товара, о чем следует дать сообщение.

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

=ЕСЛИ(A8> 75; B2*1, 1; ЕСЛИ(A8> 50; B2; ЕСЛИ(A8> =10; B2/2; " не выпускать! ")))

=ЕСЛИ(A8> 75; B2*1, 1 – определяем, как изменится цена при A8> 75;

ЕСЛИ(A8> 50; B2; -если первое условие не выполняется, начинаем новую проверку, причем мы уже не проверяем, выполняется ли условие A8< = 75, т.к. раз дошло дело до второго выражения, то первое условие уже проверено и не выполняется. Запомните, все условия проверяются слева направо последовательно до первого условия, которое удовлетворяет поставленной задаче, как только такое условие найдено, дальнейшие проверки не производятся. Иногда говорят, компьютер – очень умный, но очень ленивый. Это из этой серии.

ЕСЛИ(A8> =10; B2/2; - мы доберемся до этой проверки только, если все предыдущие были неудачны, значит, если A8> =10 и при этом A8< =50, следует уменьшить цену в два раза;

" не выпускать! " – если все условия не выполняются, дать текстовое сообщение. Текст взят в двойные кавычки (это требование синтаксиса).

2. В столбце G (рис. 4.32) указана страна-производитель продукции. Введем налог, используя для расчета следующий алгоритм: если это украинский товар, то налог составит 10% от цены (это неверно чисто экономически, но мы рассматриваем только компьютерную реализацию), для других стран он должен быть равен 20%. Формула выглядит следующим образом:

=ЕСЛИ(G2=" Украина"; B2*0, 1; B2*0, 2)

Как и в предыдущем варианте, текст помещается в двойные кавычки. А теперь изменим условие, включив Россию в список стран с 10% налогом. Мы должны проверить два условия, является ли страна Россией или Украиной. Для сложных проверок в функции ЕСЛИ используются логические операции И - ИЛИ. Операция И требует совместного выполнения всех условий сразу, а операция ИЛИ – выполнения одного из перечисленных. Так как страна не может быть одновременно и Россией, и Украиной, то мы будем использовать ИЛИ.

=ЕСЛИ(ИЛИ (G2=" Украина"; G2=" Россия"); B2*0, 1; B2*0, 2)

При использовании ИЛИ условия помещаются в круглые скобки, максимально количество вводимых для проверки условий равно 30.

3. Попробуем найти названия товаров, которые удовлетворяют следующему набору условий: страна - производитель – Россия; гарантийный срок меньше года, а цена ниже 500 (зачем нам такие товары неизвестно, но требуется одновременное выполнение всех трех условий). В этом случае используем логическую операцию И.

4.

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

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

Рисунок 4.34 - Использование мастера функций при работе с функцией ЕСЛИ

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

СЧЁТЕСЛИ (диапазон; критерий),

где диапазон — интервал, в котором нужно подсчитать ячейки, критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Критерий может быть выражен следующим образом: 18, " 18″, “> 18″, “студенты” (рис. 4.35).

Рисунок 4.35 - Использование функции СЧЕТЕСЛИ

Вычисление суммы ячеек, заданных критерием, производится с помощью функции СУММЕСЛИ.

СУММЕСЛИ (диапазон; критерий; диапазон_суммирования),

где диапазон — диапазон вычисляемых ячеек, критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки.

Диапазон суммирования — фактические ячейки для суммирования. Ячейки в «диапазоне суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию. Если «диапазон суммирования» оставить незаполненным, то суммируются ячейки в аргументе «диапазон». Например, формула

=СУММЕСЛИ(B2: B5; " > 100"; C2: C5) определяет общее количество товаров, цена на которые превышает 100 (рис. 4.33).