ЗАДАНИЕ К ЛАБОРАТОРНОЙ РАБОТЕ

Лабораторная работа № 7

ЛОГИЧЕСКИЕ ФУНКЦИИ

Цели работы:

1) закрепить навыки использования логических и математических функций, создания сложных условий;

2) закрепить навыки использования абсолютной адресации ячеек;

3) закрепить навыки форматирования таблицы.

Задание:

1) изучите п. 1 «Учебный материал»;

2) выполните задания, приведенные в п. 2;

3) ответьте на контрольные вопросы (п. 3).

УЧЕБНЫЙ МАТЕРИАЛ

Логические функции помогают создавать сложные формулы, которые в зависимости от выполнения тех или иных условий будут совершать различные виды обработки данных. Формат записи логических функций описан в лабораторной работе № 5.

Использование логических функций при создании сложных условий рассмотрим на примерах.

Пример 1. Для группы сотрудников из пяти человек страховой компании известны оклад и сумма договоров страхования за один месяц. Страховой агент получает процент от суммы страхования. Если сумма договора до 30 000 руб., то вознаграждение составляет 5 % от суммы договора, если сумма от 30 000 до 50 000 руб., то 7 %, если выше 50 000, то 10 %. Определить сумму заработную плату каждого сотрудника.

Решение. Заполните таблицу по образцу, представленному
на рис. 68[1]. В ячейку Е3 внесите формулу, используя логическую функцию ЕСЛИ, и скопируйте в ячейки E4:E7. В ячейку F3 внесите формулу, используя функцию СУММ, и скопируйте в ячейки F4:F7. Результат произведенных вычислений показан на рис. 69.

Рис. 68. Пример заполнения таблицы и использования функций для решения задачи

Рис. 69. Результаты вычислений

Пример 2.Дана ведомость итогов сессии по трем предметам для группы из 5 студентов. Определить размер стипендии для каждого студента с учетом полученных оценок: если все экзамены сданы на «отлично», делается надбавка 50 % к минимальной стипендии, если на «хорошо»
и «отлично» – надбавка 25 %, если имеются оценки «удовлетворительно» – надбавки нет, если есть «неудовлетворительно», то стипендия не начисляется. Минимальный размер стипендии считать равным 1400 руб.

Решение. Заполните таблицу по образцу, представленному на рис. 70. В ячейку F3 внесите формулу, используя логические функции ЕСЛИ, ИЛИ, И и делая абсолютную ссылку на ячейку С9, в которую занесен минимальный размер стипендии. Скопируйте формулу в ячейки F4:F7. Измените значения оценок по предметам и оцените результат работы формулы.

F3 → =ЕСЛИ(И(C3=5;D3=5;E3=5);$C$9+$C$9*50%;

ЕСЛИ(ИЛИ(C3=2;D3=2;E3=2);0;

ЕСЛИ(ИЛИ(C3=3;D3=3;E3=3);$C$9;$C$9+$C$9*25%)))

Рис. 70. Пример заполнения и форматирования таблицы с результатом вычислений

ЗАДАНИЕ К ЛАБОРАТОРНОЙ РАБОТЕ

В первой строке рабочего листа поместите название таблицы, соответствующее тематике задачи. Количество столбцов таблицы и их названия определите самостоятельно. При заполнении столбца «№ п/п» используйте автозаполнение. Если какое-либо данное помещено в отдельную ячейку (как на рис. 70 минимальная стипендия), то в формуле на него делается абсолютная ссылка.

1. На складе фирмы хранятся скоропортящиеся продукты. Для пяти продуктов известны объем, цена единицы продукта и срок хранения к текущему моменту. По истечении срока хранения производится уценка продукта или его списание по следующей схеме: если продукт хранится к настоящему моменту не более 10 суток, то цена остается прежней; от 10 до 15 суток – уценка на 15 % от первоначальной цены; от 15 до 20 суток – на 30 %; от 20 до 30 суток – на 50 %; свыше 30 суток – продукт списывается (т. е. его цена становится равной нулю). Определить, на какую сумму
к началу месяца хранится каждого вида продукции.

2. При продаже квартир сотрудникам строительной фирмы в зависимости от стажа работы предусмотрены следующие скидки: при стаже менее 5 лет скидки нет, при стаже от 5 до 7 лет скидка 3 % от номинальной стоимости квартиры; при стаже от 7 до 10 лет скидка 5 %; при стаже от 10 до 15 лет скидка 10 %; при стаже больше 15 лет скидка 20 % от номинальной стоимости квартиры. Рассчитать стоимость квартиры с учетом скидки в зависимости от стажа работы сотрудника. Номинальная стоимость квартиры (это данное поместите в отдельную ячейку) и стаж работы сотрудников известны. Стоимость квартиры = Номинальная стоимость –Номинальная стоимость × Процент скидки. Данные известны для пяти сотрудников.

3. Для сотрудников отдела из пяти человек известны: фамилии, количество отработанных дней в месяце, тариф (сумма однодневного заработка, это данное поместите в отдельную ячейку) и количество отработанных дней, пришедшихся на выходные и праздники. Величина начисляемой премии сотрудникам учреждения зависит от количества отработанных дней в выходные и праздники: если 0 дней, то премии нет, если 1 или
2 дня, то надбавка к основному заработку составляет 5 %, если 3–5 дней – 7 %, больше 5 дней – 10 %. Удержания составляют: профсоюзный взнос
1 % и подоходный налог 13 % от заработка с учетом премии. Рассчитать заработную плату за отработанные дни, величину премии, сумму заработной платы каждого сотрудника и суммарную заработную плату всего отдела в зависимости от указанных параметров.

4. Для группы продавцов из пяти человек известны: фамилии, минимальная заработная плата сотрудников (это данное поместите в отдельную ячейку), стаж работы, сумма продажи товаров каждым сотрудником. Рассчитать: надбавку за стаж (если Стаж меньше 3 лет, то равна 0, иначе равна 20 % от минимальной заработной платы), размер комиссионного вознаграждения: если сумма продаж меньше 20 000 руб., то комиссионные составляют 10 % от этой суммы, если больше 20 000, но меньше 30 000, то
20 %, а если больше 30 000, то 30 %. Найти суммарную заработную плату каждого продавца и всего отдела.

5. Для жильцов каждой из пяти квартир известен расход электроэнергии (в кВт/ч) за один месяц (например, 200, 250, 300). Компания по снабжению электроэнергией взимает плату с клиентов по тарифу 1,2 руб. за
1 кВт/ч. (это данное поместите в отдельную ячейку). Средняя норма расхода электроэнергии 250 кВт/ч (это данное поместите в отдельную ячейку). Рассчитать плату за каждый кВт/ч сверх нормы по следующей схеме: если расход за месяц был меньше 250 кВт/ч (т. е. меньше средней нормы), доплаты нет, если расход от 250 до 300 кВт/ч, доплата составляет 1,5 руб. на каждый перерасходованный кВт/ч, если расход от 300 до 400 кВт/ч, доплата составляет 1,6 руб., если расход больше 400 кВт/ч, доплата составляет 1,7 руб. Подсчитать сумму оплаты за электроэнергию для каждого клиента и для всей группы жильцов.

6. Для группы сотрудников из пяти человек известны: фамилии, оклад, стаж работы, количество отработанных дней в месяце, а также минимальный размер заработной платы (это данное поместите в отдельную ячейку). Начисления составляют: Начислено по окладу (оклад умножить на Отработано дней и разделить на количество рабочих дней в месяце), Надбавка за стаж (если Стаж меньше 3 лет, то надбавка равна 0, если от 3 до 5 лет, то 10 % от Начислено по окладу, если от 5 до 7 лет, то 15 % от Начислено по окладу, иначе равна 20 % от Начислено по окладу), Районный коэффициент (30 % от Начислено по окладу). Итого начислено = Начислено по окладу + Надбавка за стаж + Районный коэффициент. Удержания составляют: Профсоюзные взносы (1 % от Итого начислено), Подоходный налог (если Итого начислено больше минимальной заработной платы, то вычисляется по формуле: Итого начислено × 13 %, в противном случае равен 0). Рассчитать сумму удержаний и сумму к выдаче.

7. Для жильцов каждой из пяти квартир известен расход водоснабжения (в м³) за холодную и горячую воду за один месяц (например, 3, 4, 5). Компания по водоснабжению взимает плату с клиентов по тарифу 8 руб. за 1 м³ за холодную воду и 62 руб. за 1 м³ за горячую воду. Средняя норма суммарного расхода воды 10 м³ (это данное поместите в отдельную ячейку). Рассчитать плату за каждый м³ сверх нормы по следующей схеме: если расход за месяц был меньше 10 м³ (т. е. средней нормы), доплаты нет, если расход от 11 до 12 м³, доплата составляет 1,5 руб. на каждый перерасходованный м³, если расход от 13 до 14 м³, доплата составляет 1,6 руб., если расход больше 14 м³, доплата составляет 1,7 руб. Подсчитать плату за водоснабжение для каждого клиента и для всей группы жильцов.

8. Для пяти арендаторов известны: название организации-арендатора, площадь арендуемого помещения (в м²), минимальная оплата за аренду (это данное поместите в отдельную ячейку). Составить таблицу расчета оплаты за аренду помещений в зависимости от площади с учетом поправочного коэффициента, используя формулу: Минимальная плата за аренду × Площадь + Минимальная плата за аренду × Коэффициент. Если арендуемая площадь меньше 100 м², то коэффициент равен 0,5, если арендуемая площадь больше, чем 100 м², но не превышает 200 м², то коэффициент равен 0,7, если площадь более 200 м², коэффициент – 0,8.

9. Продуктовый склад отпускает муку и сахар для предприятий по оптовым ценам в зависимости от объема закупок. Для муки используются следующие цены: более 10 000 кг – по 12 руб. за 1 кг, от 5 000 до 10 000 кг – по 13 руб. за 1 кг, от 1000 до 5000 кг – по 14 руб. за 1 кг, менее 1000 кг –
по 15 руб. за 1 кг. Для сахара: более 5000 кг – 13 руб. за 1 кг, от 3000 до 5000 кг – по 14 руб. за 1 кг, от 1000 до 3000 кг – по 16 руб. за 1 кг, менее 1000 кг – по 17 руб. за 1 кг. Пять предприятий города закупили муку и сахар в определенном объеме. Рассчитайте стоимость закупленных продуктов и общую стоимость для каждого предприятия.

10. При продаже квартир строительной компанией цена одного квадратного метра площади жилья рассчитывается следующим образом: если квартира однокомнатная, то стоимость одного квадратного метра составляет 41 500 руб., если двухкомнатная – 40 000 руб., если трехкомнатная – 39 500 руб. Стоимость квадратного метра квартиры, имеющей больше
3 комнат, составляет 38 500 руб. Каждая из квартир имеет балкон определенной площади, стоимость которой рассчитывается как Стоимость одного квадратного метра (данной квартиры) × Коэффициент. Известны данные о пяти квартирах. Рассчитать общую стоимость каждой квартиры. Коэффициент, равный 0,5, поместите в отдельную ячейку.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Что собой представляет сложное условие?

2. Как реализовать сложное условие средствами MS Excel?

3. Каково максимально возможное число уровней вложенности одной функции ЕСЛИ в другую?

[1] Формулы в ячейках таблицы отображены с использованием опции Файл → Параметры → Дополнительно → категория Показать параметры для следующего листа → Показывать формулы, а не их значения.