Задание 2. Штрафы за отходы

Задание выполняется на основе листа Штрафы за отходы из файла-заготовки.

Предприятие каждый месяц сбрасывает в реку промышленные отходы.

В таблице указаны месяцы и количество отходов, а также наличие арендованных помещений на предприятии.

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

Если предприятие в данный месяц сдает помещение в аренду, то штраф увеличивается в два раза (т.к. предприятие зарабатывает дополнительные средства, но не следит за своими арендаторами).

Требуется найти:

1. В каких месяцах был начислен штраф.

2. Какой штраф был уплачен предприятие в каждом месяце.

3. Месяцы, когда был начислен максимальный штраф.

4. Сколько месяцев выплачивался штраф и суммарный штраф за год.

Результаты расчетов представлены на образце (рис. 38).

Рис. 38. Образец выполнения задания 3.

Технология выполнения задания

1. Откройте файл-заготовку.

2. Откройте лист Штрафы за отходы. Цветным фоном выделены ячейки, которые следует заполнить результатами расчетов.

3. В столбце D рассчитайте разность между отходами (столбец B) и предельным значением (ячейка С14).

4. В столбце Е определите, начислен штраф в этом месяце или нет по следующему правилу: если превышение предельного значения больше 0, то штраф начислен (обозначим это 1), в противном случае – не начислен (обозначим это 0). Указанный результат можно получить с использованием логической функции ЕСЛИ.

В ячейке Е20 формула выглядит так: =ЕСЛИ(D20>0;1;0)

5. В столбце F определите месяц, когда выплачивался штраф по следующему правилу: если в значение в ячейке столбца Е равно 1 (т.е. штраф начислен), то дать ссылку на месяц (столбец А), в противном случае – ввести пустое значение (две кавычки) или пробел.

В ячейке F20 формула выглядит так: =ЕСЛИ(E20=1;A20;"")

6. В столбце G вычислите, какой был бы начислен штраф без учета аренды помещений по следующему правилу: если штраф начислен, то сумма штрафа равна произведению превышения отходов (столбец D) на штраф на 1 кг (ячейка С15).

В ячейке G20 формула выглядит так: =ЕСЛИ(E20=1;D20*$C$15;0)

Примечание. Значения столбца G являются вспомогательными для вычисления окончательной суммы штрафа с учетом аренды помещений.

7. В столбце H вычислите штраф с учетом аренды помещений по следующему правилу: если есть аренда (ячейка столбца С равна «да»), то сумма штрафа (столбец G) умножается на 2, в противном случае берется сумма штрафа из столбца G.

В ячейке Н20 формула выглядит так: =ЕСЛИ(C20="да";G20*2;G20).

Примечание. Для вычисления окончательной суммы штрафа мы проверили два условия: наличие штрафа и наличие аренды. Для этого вычислили вспомогательный столбец G. Можно было бы получить итоговую сумму штрафа и без использования вспомогательного столбца. Тогда в одной формуле надо проверить сразу два условия. Это можно сделать при помощи двух вложенных функций ЕСЛИ.

Тогда в ячейке Н20 формула выглядела бы так:
=ЕСЛИ(C20="нет";G20;ЕСЛИ(E20=1;D20*$C$15*2;0))

8. В ячейке Н32 вычислите максимальный штраф с использованием встроенной функции МАКС: =МАКС(H20:H31)

9. В столбце I вычислите месяц, в котором была уплачена максимальная сумма штрафа по следующему правилу: если значение в ячейке столбца Н равно максимальному (Н32), то надо указать месяц (из столбца А), в противном случае ввести пустое значение (две кавычки) или пробел.

В ячейкеI20 формула выглядит так: =ЕСЛИ(H20=$H$32;A20;"")

10. В ячейке D35 вычислите, сколько месяцев выплачивался штраф (сумма по столбцу Е).

11. В ячейке D36 вычислите общую сумму штрафа за год (сумма по столбцу Н).

12. Сохраните выполненное задание.