Урок №29. Условные вычисления в электронных таблицах.
Как вы знаете, в программировании важную роль играют условные операторы (ветвления), позволяющие выбирать один из двух (или нескольких) вариантов обработки данных. В табличных процессорах тоже возможны условные вычисления, при которых в ячейку заносится то или иное значение в зависимости от выполнения какого-то условия.
Предположим, что в книжном интернет-магазине «Бука» доставка покупок бесплатна для тех, кто сделал заказ на сумму более 500 рублей, а для остальных доставка стоит 20% от суммы заказа) (рис. 5.1).
Таким образом, есть два варианта вычисления стоимости доставки, поэтому в формулах столбца С нужно использовать ветвление. Алгоритм вычисления значения в ячейке С2 может выглядеть так: «если В2 > 500, то записать в ячейку 0, иначе записать значение В2*0,2». В программе на языке Python мы бы записали:
if В2>500:
С2 =0
else:
С2=В2 *0.2
В табличных процессорах для условных вычислений используют функцию IF (ЕСЛИ):
=IF(B2>500;0;B2*0,2) =ЕСЛИ(В2>500;0;В2*0,2)У этой функции три аргумента, разделённые точками с запятой:
- условие (В2>500);
- значение ячейки в том случае, когда условие истинно (0);
- значение ячейки в том случае, когда условие ложно (В2*0,2).
Определите значения, которые появятся в ячейках диапазона В2:В6 после ввода формул (рис. 5.2).
В условии можно использовать не только числовые, но и символьные данные. Например, фирма «Салют» в этом месяце проводит рекламную акцию: предоставляет скидку 20% на все товары (рис. 5.3).
В этом случае в ячейку D2 запишем формулу:
=IF(В2="Салют";С2*20%;"")
=ECЛИ(B2="Салют";С2*20%;"")
и скопируем её во все ячейки столбца D. Запись «*20%» означает то же самое, что и «*0,2».
Работник получает премию, составляющую 10% от его зарплаты, только тогда, когда на него не поступает жалоб. Какую формулу нужно записать в ячейку D2 (рис. 5.4)?
Второй и третий аргументы функции IF могут содержать вложенные вызовы этой функции. Пусть, например, в книжном интернет-магазине «Бука» (см. задачу в начале параграфа) для заказов стоимостью более 200 рублей (но не более 500) стоимость доставки составляет 10% от суммы:
if В2>500:
С2=0
else:
if В2>200:
С2=В2*0.1
else:
С2=В2*0.2
В табличном процессоре этот алгоритм запишется в виде
=IF(В2>500;0;IF(В2>200;В2*0,1;В2*0,2))
=ЕСЛИ(В2>500;0;ЕСЛИ(В2>200;В2*0,1;В2*0,2))
Фирма «Форсаж» занимается доставкой мебели. Если в доме нет лифта, за подъём берут дополнительную плату 100 рублей, а если вес покупки больше 100 кг, то плата за подъём составляет 200 рублей. Какую формулу нужно записать в ячейку D2 (рис. 5.5)?
Первый аргумент функции IF (ЕСЛИ) может быть сложным условием, которое строится с помощью функций AND (И) — логическое умножение, OR (ИЛИ) — логическое сложение и NOT (НЕ) — отрицание.
Задача 1. Пусть в примере на рис. 5.1 бесплатная доставка распространяется только на заказы, у которых номер меньше 1500 и сумма больше 500 рублей.
В этом случае в ячейку С2 нужно записать такую формулу:
=IF(AND(A2<1500;B2>500);0;B2*0,2)
=ЕСЛИ(И(А2<1500;В2>500);0;В2*0,2)
Здесь использовано сложное условие AND(A2<1500; В2>500), которое истинно только при одновременном выполнении двух условий: А2<1500 и В2>500.
Задача 2. На трассе разрешается ехать со скоростью от 40 км/ч до 110 км/ч. Радар записывает скорость проезжающих машин, а видеокамера — их номера. Водителям, которые едут со скоростью, меньшей минимальной или большей максимальной, нужно выписать штраф 500 рублей. Требуется построить электронную таблицу такого вида (рис. 5.7).
Штраф выписывается, когда верно одно из двух условий (скорость меньше 40 км/ч ИЛИ скорость больше 110 км/ч). Формула в ячейке С2 может быть записана так:
=IF(OR(B2<40;B2>110);500;"")
=ЕСЛИ(ИЛИ(В2<40;В2>110);500;"")
Компания «Уют» проводит акцию: те, кто купил не меньше 5 стульев или не меньше 2 столов, получают приз. Какую формулу нужно записать в ячейку D2 (рис. 5.8)? Предложите несколько вариантов решения задачи.
Логические функции AND (И) и OR (ИЛИ) могут содержать более двух условий, которые перечисляются через точку с запятой.
Задача 3. На III тур соревнований нужно отобрать участников, которые набрали по сумме двух первых туров не менее 180 баллов или получили 100 баллов хотя бы в одном туре (рис. 5.9).
Формула в ячейке D2 содержит сложное условие, где операция OR (ИЛИ) объединяет три условия:
=IF(OR(B2+C2>=180;B2=100;C2=100);"+";"")
=ЕСЛИ(ИЛИ(В2+С2>=180;В2=100;С2=100);"+";" ")
Функция NOT (НЕ) выполняет логическое отрицание. Она используется на практике довольно редко.