Лабораторная работа «Оптимизации количества удобрений, вносимых в поле»
Постановка задачи. Агроному необходимо определить количество органических и минеральных удобрений для подкормки зерновых культур таким образом, чтобы полная стоимость вносимых удобрений была минимальной. Стоимость и химический состав последних показаны ниже в таблице 1.
Таблица 1. Стоимость и химический состав удобрений
Удобрение |
Стоимость, руб/т |
Азот, % |
Фосфор, % |
Калий, % |
Органическое удобрение |
|
0,6 |
0,15 |
0,4 |
Аммофос |
13500 |
12 |
52 |
0 |
Азофоска |
11810 |
16 |
16 |
16 |
NPK |
13500 |
13 |
19 |
19 |
Карбамид |
8000 |
46 |
0 |
0 |
Минимальная потребность в элементах питания и площади посева культур приведены в следующей таблице.
Таблица 2. Потребность в питательных веществах и площади возделывания культур
|
Озимая пшеница |
Озимая рожь |
Яровая пшеница |
Яровой ячмень |
Овес |
Азот, ц/га |
0,6 |
0,5 |
0,8 |
0,5 |
0,6 |
Фосфор, ц/га |
0,7 |
0,7 |
0,6 |
0,5 |
0,6 |
Калий, ц/га |
0,4 |
0,3 |
0,2 |
0,3 |
0,4 |
Площадь, га |
220 + 10а |
350 – 10а |
190 + 10а |
210 + 10а |
420 – 20а |
В наличии имеется 300 т органических удобрений, которые нужно израсходовать полностью. Количество минеральных удобрений нужно подобрать таким образом, чтобы их суммарная стоимость была минимальной.
Рис.8
Для решения задачи создаем рабочий лист EXCEL, внеся на него данные из таблиц, как показано на рис.8. Здесь обрабатывается информация по двум из пяти культур.
Управляемые переменные – это количество удобрений каждого вида, вносимых под каждую культуру. Их значения будут содержаться в массиве, заданном строками 9 – 10 и столбцами C – G. Названия строк и столбцов описывают назначение каждой ячейки.
Рассмотрим три столбца H, I, J, соответствующие озимой пшенице. Столбец «Выход» показывает, сколько питательного вещества каждого вида будет запланировано для внесения на поле, когда определятся значения в строке 9, соответствующей озимой пшенице. В ячейку Н5 вводим формулу =СУММПРОИЗВ($C5:$G5;$C$9:$G$9)/100 и растягиваем ее вниз на три ячейки. В столбце «Потребность» показана потребность поля в каждом питательном веществе. В ячейку J5 вводим формулу =I5*I$8 и растягиваем ее вниз на три ячейки. Аналогично заполняются столбцы, Соответствующие ржи, но управляемые переменные берутся из соответствующей строки 10.
В строке 11 подсчитывается общее количество удобрений каждого вида. В ячейку С11 вводим формулу =СУММ(C9:C10) и растягиваем ее до ячейки G11.
В ячейке Н12 подсчитывается общая стоимость удобрений, это значение целевой функции. Вводим в нее формулу
=СУММПРОИЗВ(C11:G11;C12:G12).
Теперь вызываем Сервис Поиск решения. В открывшееся диалоговое окно вносим всю необходимую информацию. Ограничения по количеству питательных веществ можно вводить не по одному, а списком, выделяя по запросу нужные массивы.
Рис.9
Выполняем необходимые процедуры с кнопкой Параметры, нажимаем Выполнить и получаем решение задачи
Рис.10
Расчет по всем заданным культурам производим, дополнив таблицу соответствующими строками и столбцами.
Замечание. В полученном решении все органическое удобрение направлено под рожь. При желании можно его распределить принудительно, исключив переменные столбца С из списка управляемых. Можно также этот столбец разбить на два столбца: в одном распределить принудительно часть органического удобрения, а в другом оставшуюся часть распределить оптимально. В задаче можно также учесть не только стоимость удобрения, но и стоимость его внесения, что сделает решение более оптимальным.