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

В первой строке рабочего листа поместите название таблицы, соответствующее тематике задачи. Количество столбцов таблицы и их названия определите самостоятельно. При заполнении столбца «№ п/п» используйте автозаполнение. Если какое-либо данное помещено в отдельную ячейку (как на рис. 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. Каково максимально возможное число уровней вложенности одной функции ЕСЛИ в другую?

4. Как на рабочем листе отобразить формулы, а не их значения?

 

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