经济数学（第三版） 教案 实训5 利用Excel求解线性规划问题

实训五利用EXCEL求解线性规划问题注意：如果在【数据】菜单中没有见到【规划求解】命令，则要单击【文件】【选项】【加载项】【转到】命令，在弹出的对话框中的列表框中，选定【规划求解加载项】选项前的复选框．【实训目的】掌握使用EXCEL自定义函数进行数据处理掌握线性规划问题模型建立掌握EXCEL求解线性规划问题【实训内容】一、线性规划问题的三要素．1.有一组决策变量；2.有一个线性目标函数;3.有一组线性约束条件。线性规划问题的数学模型可表示如下线性规划问题的求解有很多方法，也有很多工具，比如常用的Matlab、Lingo、Excel等.Excel具有强大的规划求解功能，可以解决最多有200个变量，100个外在约束和400个简单约束（决策变量整数约束的上下边界）的线性规划与非线性规划问题，本节主要讲解如何使用Excel求解线性规划问题.使用Excel求解线性规划规划求解加载宏是一组命令构成的一个子程序，其功能是可以求出线性和非线性数学规划问题的最优解和最优值.使用规划求解加载宏求解数学规划的步骤：第一步：在Excel工作表中输入目标函数的系数、决策变量、约束条件的系数、约束条件左端的值和约束条件右端的值（每一个单元格输入一个数据）；第二步：选定一个单元格存储目标函数，用定义公式的方式在这个目标单元格内定义目标函数；第三步：选定与决策变量个数相同的单元格（称为可变单元格），用以存储决策变量；再选择与约束条件个数相同的单元格，用定义公式的方式在每一个单元格内计算出相应的约束函数（称为约束函数单元格）；第四步：点击规划求解按钮，打开规划求解参数设定对话框，添加约束条件，完成规划模型的设定.【实训案例】案例1求解线性规划问题：Excel演算步骤第一步：启动Excel，在工作表中的A1，A2，A3，A10，E3，F3单元格中分别输入文字“目标函数系数”，“决策变量”，“约束条件”，“目标函数值”，“约束条件左端的值”，“约束条件右端的值”；在B1，C1，D1单元格中输入目标函数的系数1，-2，1，在B4，C4，D4单元格中输入第一个约束条件的系数1，1，1；同理，在相应单元格中输入其他约束条件的系数与约束条件右端的值，如下图4-1所示：图4-1数据输入第二步：计算约束条件左端的值和目标函数值．因为约束条件左端的值等于约束条件的系数乘以相应的决策变量，所以在E4单元格中输入公式“=B4*B2+C4*C2+D4*D2”，在E5单元格中输入公式“=B5*B2+C5*C2+D5*D2”，依次类推在E9单元格中输入公式“=B9*B2+C9*C2+D9*D2目标函数的值等于目标函数系数乘以决策变量，从而在D10单元格中输入公式“=B1*B2+C1*C2+D1*D2”，如图4-2图4-2计算约束条件左端的值和目标函数值事实上，在计算约束条件左端值时，只需要在E4单元格中输入公式“=B4*$B$2+C4*$C$2+D4*$D$2”，然后单击E4单元格，将鼠标至于E4单元格右下角，当光标变为小黑十字时拖曳至E9单元格第三步：单击【工具】菜单中的【规划求解】命令，在弹出的规划求解对话框中输入各项参数．（1）设置目标单元格和可变单元格在“规划求解参数”对话框中选中“最大值”前的单选按钮，设置目标单元格为“$D$10”，可变单元格为“$B$2:$D$2”，如图4图4-3“规划求解参数”对话框（2）添加约束条件单击【规划求解参数】对话框中的【添加】按钮，打开【添加约束】对话框，单击单元格引用位置文本框，然后选定工作表中的E4单元格，则在文本框中显示“$E$4”，选择“<=”约束条件；单击约束值文本框，然后选定工作表中的F4单元格，如图4-4所示．图4-4“添加约束”对话框图中所示约束条件表示，依此类推，把所有约束条件都添加到【规划求解参数】对话框的【约束】列表框中．第四步：在“选择求解方法中”点击“单纯线性规划”，单击【求解】按钮，弹出图4-5所示的【规划求解结果】对话框，选中【保存规划求解结果】单选按钮．图4-5“规划求解结果”对话框第五步：在【规划求解结果】对话框中，单击【确定】按钮，工作表中就显示出规划求解的结果，如图4-6所示．图4-6结果显示如果要生成运算结果报告，可在【规划求解】对话框中选择【报告】列表框中的【运算结果报告】．单击【确定】按钮，则产生如图4-7所示的运算结果报告表，在该表中对约束条件和结果作出了详细的说明．图4-7运算结果报告表从图4-6或图4-7可以很容易看出，当变量时，目标函数的最大值为．案例2某奶制品加工厂用牛奶生产两种奶制品，1桶牛奶可以在设备甲上用12小时加工成3公斤，或者在设备乙上用8小时加工成4公斤．根据市场需求，生产的全部能售出，且每公斤获利24元，每公斤获利16元．现在加工厂每天能得到50桶牛奶的供应，每天正式工人总的劳动时间为480小时，并且设备甲每天至多能加工100公斤，设备乙的加工能力没有限制．请为该厂制定一个生产计划，使得工厂每天获利最大．解决方案:1.模型建立第一步：根据实际问题，设置决策变量．设每天用桶牛奶生产，用桶牛奶生产.第二步：确定目标函数．设每天获利元．桶牛奶可生产3公斤，获利，桶牛奶可生产4公斤，获利，故.第三步：分析各种资源限制，列出约束条件．原料供应：生产的原料(牛奶)总量不超过每天的供应，即桶；劳动时间：生产的总加工时间不超过总的劳动时间，即；设备能力：的产量不得超过设备甲每天的加工能力，即；非负约束：均不能为负值，即.第四步：综合目标函数和各个约束条件，写出整个线性规划模型如下：2.Excel求解第一步：在工作表中的A14，A15，A16，E15，E16，A22单元格中分别输入“目标函数系数”，“决策变量”，“约束条件系数”，“约束条件左端的值”，“约束条件右端的值”,“目标函数值”；在B14，C14单元格中输入目标函数的系数72，64，在B16，C16单元格中输入第一个约束条件的系数1，1；同理，在相应单元格中输入其他约束条件的系数与约束条件右端的值，如下图4-8所示：图4-8数据输入第二步：计算约束条件左端的值和目标函数值．在E16单元格中输入公式“=B16*$B$15+C16*$C$15”，并使用句柄填充拖曳至E20单元格.在B22单元格中输入公式“=B14*B15+C14*C15”，如图4-9所示．图4-9计算约束条件左端的值和目标函数值第三步：单击【数据】菜单中的【规划求解】命令，在弹出的规划求解对话框中输入各项参数．设置目标单元格和可变单元格在“规划求解参数”对话框中选中“最大值”前的单选按钮，设置目标单元格为“$B$22”，可变单元格为“$B$15:$C$15”，如图4-10所示．图4-10“规划求解参数”对话框添加约束条件单击【添加】按钮，打开【添加约束】对话框，单击单元格引用位置文本框，然后选定工作表中的E16至E18单元格，则在文本框中显示“$E$16:$E$18”，选择“<=”约束条件；单击约束值文本框，然后选定工作表中的F16至F18单元格，如图4-11所示．图4-11“添加约束”对话框依此类推，把非负约束条件添加到约束列表框中．第四步：在【规划求解结果】对话框中，单击【确定】按钮，工作表中就显示出规划求解的结果，如图4-12所示．图4-12“规划求解结果”对话框从图4-12可以很容易看出，当变量时，目标函数的最大值为．【实训练习】实训1某单位一次性购买了10本图书，书名、数量、单价数据见表7-11：表7-11图书单价及数量表书名单价数量书名单价数量大学语文18.320政治经济学2813高等数学3420文学5.512化工与化学23.58自然地理197大学英语2015计算机3840写作1830英语写作13.524任务1：请利用EXCEL自定义函数计算出每本书的购买总价.任务2：请利用EXCEL自定义函数计算出购买平均价格、平均数量.实训2某工厂用A、B两种配件生产甲、乙两种产品，每生产1件甲产品使用4个A配件耗时1h，每生产1件乙产品使用4个B配件耗时2h.该厂每天最多可从配件厂获得16个A配件和12个B配件，按每天工作8h计算，若生产1件甲产品获利2万元，生产1件乙产品获利3万元，问工厂应如何安排生产，以使得总利润最大？任务1：建立使总利润最大的线性规划模型.任务2：使用EXCEL求解该线性规划模型.实训3某农场每天需要使用一种特殊饲料800千克，这种特殊饲料由玉米和大豆粉配制而成，营养要求是至少含有30%的蛋白质和至多5%的纤维，具体成份见表7-12.表7-12饲料每千克饲料中含有成份总量（千克）费用（元/千克）蛋白质纤维玉米0.090.020.3大豆粉0.