Excel线性规划求解农场最大利润.doc

姓名： 学号：200900609 专业：资源环境与城乡规划管理 已知：某地有三个农场共用一条灌渠，每个农场的可灌溉耕地及可分配的最大用水量如表1： 表1：各农场可灌溉耕地及允许最大用水量 农场序号 可灌溉耕地（亩） 可分配水量（百方） 1 400 600 2 600 800 3 300 375 各农场均可种植甜菜、棉花及高粱三种作物，各作物的用水量、净收益以及国家规定的该地区各种作物种植总面积最高限额如表2： 表2：各种作物需水量、净收益及种植额限 作物种类 种植限额（亩） 耗水量（百方/亩） 净收益（元/亩） 甜菜 600 3 400 棉花 500 2 300 高粱 325 1 100 三个农场达成协议，他们的播种面积与其可灌溉面积之比应该相等，各农场种何种作物并无限制。如何制定各农场种植计划才能在上述条件下，是本地区的三个农场的总净收益最大。 解： 1、首先新建Excel表格，加载规划求解加载宏，“工具”—“加载宏”，选中“规划求解”复选框，单击确定即可。 2、在表中输入已知数据，如下图所示： 3、设各农场三种作物的种植面积分别为x1、x2、x3、x4、x5、x6、x7、x8、x9， 根据已知条件可知目标函数总利润为： Y= 400*(x1+x4+x7)+300*(x2+x5+x8)+100*(x3+x6+x7) 4、由已知条件可知约束条件为： 3*x1+2*x2+x3=600 ……约束农场1最大用水量 3*x4+2*x5+x6=800 ……约束农场2最大用水量 3*x7+2*x8+x9=375 ……约束农场3最大用水量 x1+x4+x7=600 ……约束甜菜种植限额 x2+x5+x8=500 ……约束棉花种植限额 x3+x6+x9=325 ……约束高粱种植限额 x1+x2+x3=400 ……约束农场1耕地面积 x4+x5+x6=600 ……约束农场2耕地面积 x7+x8+x9=300 ……约束农场3耕地面积 (x1+x2+x3)/400=(x4+x5+x6)/600=(x7+x8+x9)/300 ……约束各耕地面积比例 x1、x2、x3、x4、x5、x6、x7、x8、x9均=0 ……约束各耕地面积有意义 5、在对应单元格编辑相关函数： 如上图右下角表格所示，依次进行对应单元格相关函数的编辑。 总计面积=SUM(G10:G12)、SUM(H10:H12)、SUM(I10:I12) 总计=SUM(G10:I10)、SUM(G11:I11)、SUM(G12:I12) 农场所需水量=3*G10+2*G11+G12、3*H10+2*H11*H12、3*I10+2*I11+I12 耕地比例=G13/400、H13/600、I13/300 利润=400*J10+300*J11+100*J12 6、进行规划求解： ①“工具”——“规划求解”，弹出如下对话框： ②设置目标单元格为G16(利润对应单元格)； ③设置目标单元格为G10—I12,共九个； ④单击“添加”按钮，进行约束条件的添加 其中“单元格引用位置”为编辑的函数所对应的单元格： 例如编辑第一个约束条件：3*x1+2*x2+x3=600 ……约束农场1最大用水量 则“单元格引用位置”选取单元格G14即可（因为单元格G14对应的函数为：=3*G10+2*G11+G12）。其它约束条件的添加类似。 注： (x1+x2+x3)/400=(x4+x5+x6)/600=(x7+x8+x9)/300 ……约束各耕地面积比例 此约束条件的添加可如下设置： G15=H15；（农场1使用耕地比例=农场2使用耕地比例） G15=I15；（农场1使用耕地比例=农场3使用耕地比例） ⑤添加所有的约束条件后，单击“求解”按钮，Excel将自动进行线性求解计算，并得出结果，确定保存即可。如下图示： 至此，结果已经得出，有表格可知三个农场的最大总利润为25.3万元，当且仅当各农场各作物的种植面积满足下列条件是成立： 农场序号 1 2 3 甜菜 133.3 100.0 25.0 棉花 100.0 250.0 150.0 高粱 0 0 0 200900609 水资源规划与管理作业 资源与环境学院 2012/10/25