Excel高级应用：Excel的规划求解功能

Excel的规划求解功能张健人事行政部目录•引例•EXCEL中的规划求解工具•线性规划求解方法•对偶问题与影子价格•线性规划的敏感度分析•整数规划求解•非线性规划求解•目标规划问题求解•综合运用引例•生产两种风机（风机A和风机B）。•生产风机A，需要工时3小时，用电4千瓦，钢材9吨；•生产风机B，需要工时7小时，用电5千瓦，钢材5吨。•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。•假设，两种产品的单位利润分别为200万元和210万元。怎样安排两种产品的生产量，所获得的利润最大？规划求解就是用来解决这类问题的，其实就像是在做应用题，设未知数，然后写函数。规划求解的第一步也是将所描述的问题数学化，模型化。接下来按照解题格式来做一下上面的应用题。引例•生产两种风机（风机A和风机B）。生产风机A，需要工时3小时，用电4千瓦，钢材9吨；生产风机B，需要工时7小时，用电5千瓦，钢材5吨。•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。•假设，两种产品的单位利润分别为200万元和210万元。怎样安排两种产品的生产量，所获得的利润最大？规划求解的第一步也是将所描述的问题数学化，模型化。解：设风机A产量为x，风机B产量为y，最大利润为Pmax•x,y=0•3x+7y=300•4x+5y=250•9x+5y=420•Pmax=200x+200y引例•生产两种风机（风机A和风机B）。生产风机A，需要工时3小时，用电4千瓦，钢材9吨；生产风机B，需要工时7小时，用电5千瓦，钢材5吨。•公司可提供的工时为300小时，可提供的用电量为250千瓦，可提供的钢材为420吨。•假设，两种产品的单位利润分别为200万元和210万元。怎样安排两种产品的生产量，所获得的利润最大？规划求解的第二步也是将数学模型，输入Excel表格，构建关系引例规划求解的第二步也是将数学模型，输入Excel表格，构建关系，并将约束条件输入规划求解参数表引例通过规划求解功能，找到答案引例•1939年，前苏联科学家康托洛维奇总结了他对生产组织的研究，写出了《生产组织与计划中的数学方法》一书，是线性规划应用于工业生产问题的经典著作。•康托罗维奇指出，提高企业的劳动效率有两条途径。一条是技术上的各种改进，另一条是生产组织和计划方面的改革。过去，由于没有必要的计算工具，后一条途径很少被利用。解乘数法的提出，为求解线性规划问题，为科学地组织和计划生产开辟了现实的前景。他把这一方法推广运用于一系列实践。诸如合理地分配机床机械的作业，最大限度地减少废料，最佳地利用原材料和燃料，有效地组织货物运输，最适当地安排农作物的布局等等。•解决这类问题的一般程序是，首先建立数学模型，即根据问题的条件，将生产的目标、资源的约束、所求的变量这三者之间的数量关系用线性方程式表达出来，然后求解计算。列奥尼德·康托罗维奇（1912-1986）1975年，与美国经济学家库普曼斯共同获得当年的诺贝尔经济学奖，成为第一个获此殊荣的前苏联经济学家。第一节EXCEL中的规划求解工具“规划求解”是Excel中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（目标单元格）中公式的最优值。“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。MicrosoftExcel的“规划求解”工具取自德克萨斯大学奥斯汀分校的LeonLasdon和克里夫兰州立大学的AllanWarren共同开发的GeneralizedReducedGradient(GRG2)非线性最优化代码。线性和整数规划问题取自FrontlineSystems公司的JohnWatson和DanFylstra提供的有界变量单纯形法和分支边界法。第一节EXCEL中的规划求解工具•规划求解功能需要加载：开始-选项；进入选项对话框，选择加载项，在管理excel加载项右边，点击转到第一节EXCEL中的规划求解工具一、关于“规划求解参数”对话框【设置目标】在此框内（左图中，蓝色框表示），指定要设置为特定数值或者最大值或最小值的目标单元格。该单元格必须包含公式。【到】在此（左图中，红色框表示）指定是否希望目标单元格为最大值、最小值或某一特定数值。如果需要指定数值，请在右侧编辑框中键入该值。第一节EXCEL中的规划求解工具一、关于“规划求解参数”对话框【通过更改可变单元格】在此（左图，红色框表示）指定可变单元格。求解时其中的数值不断调整，直到满足约束条件并且“设置目标”框中指定的单元格达到目标值。在“可变单元格”框中，输入每个可变单元格的名称或引用，用逗号分隔不相邻的引用。可变单元格必须直接或间接与目标单元格相联系。最多可以指定200个可变单元格。第一节EXCEL中的规划求解工具一、关于“规划求解参数”对话框【遵守约束】在此（左图，红色框表示）列出了规划求解的所有约束条件。【添加】显示“添加约束”对话框。详见下文。【更改】显示“更改约束”对话框。【删除】删除选定的约束条件。【全部重置】重置规划求解参数对话框中的所有参数。【装入/保存】保存当前的参数设置或者将之前保存过的参数设置进行载入。【选项】显示“规划求解选项”对话框。在其中可加载或保存规划求解模型，并对求解过程的高级属性进行控制。第一节EXCEL中的规划求解工具二、关于“规划求解选项”对话框在本对话框中，可以设定规划求解过程的一些高级功能、加载或保存规划求解定义，以及为线性和非线性规划求解定义参数。每一选项都有默认设置，可以满足大多数情况下的要求。第一节EXCEL中的规划求解工具二、关于“规划求解选项”对话框【最大时间（秒）】在此设定求解过程的时间。可输入的最大值为32767（秒），默认值100（秒）可以满足大多数小型规划求解要求。【迭代次数】在此设定求解过程中迭代运算的次数，限制求解过程的时间。可输入的最大值为32767，默认值100次可满足大多数小型规划求解要求。第一节EXCEL中的规划求解工具二、关于“规划求解选项”对话框【约束精确度】在此输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值或上下限。精度值必须表示为小数（0到1之间），输入数字的小数位越多，精度越高。例如，0.0001比0.01的精度高。第一节EXCEL中的规划求解工具三、约束条件（一）添加约束条件1.在“规划求解参数”对话框中，单击“添加”。2.在【单元格引用】框中，输入需要对其中数值进行约束的单元格引用或单元格区域的名称。3.单击希望在引用单元格和约束条件之间使用的关系（“=”、“=”、“=”、“Int”或“Bin”）。如果单击“Int”，则“约束值”框中会显示“整数”；如果单击“Bin”，则“约束值”框中会显示“二进制”。4.在【约束】框中，键入数字、单元格引用或名称，或键入公式。5.执行约束条件并要添加其他的约束条件，请单击【添加】；若要接受约束条件并返回“规划求解参数”对话框，请单击【确定】。6.当“规划求解选项”对话框中的“采用线性模型”复选框被选中时，对约束条件的数量没有限制。对于非线性问题，每个可变单元格除了变量的范围和整数限制外，还可以有多达100个约束。第一节EXCEL中的规划求解工具三、约束条件(二)更改或删除约束条件1.在“规划求解参数”对话框，单击要更改或删除的约束条件。2.单击“更改”，并进行所需的更改，或单击“删除”。3.单击“求解”，再执行下列操作之一：若要在工作表中保存求解后的数值，请在“规划求解结果”对话框中，单击“保存规划求解结果”。4.若要恢复原始数据，请单击“还原初值”。第二节线性规划求解方法线性规划是在一定的限制条件下，利用数学方法进行运算，使对前景的规划达到最优的方法。在经济管理中，线性规划研究的主要问题包括运输问题、生产的组织与计划问题、合理下料问题、配料问题、布局问题、分派问题等。有的著作把它分为资源分配问题、成本收益平衡问题、网络配送问题等。线性规划的组成：•目标函数：Maxf（x）或Minf(x)•约束条件：s.t.(subjectto)满足于•决策变量：用符号来表示可控制的因素线性规划问题的一般形式为：目标函数：Max（Min）z=c1x1+c2x2+…+cnxn约束条件：s.t.a11x1+a12x2+…+a1nxn≤(≥)b1a21x1+a22x2+…+a2nxn≤(≥)b2………………………………ak1x1+ak2x2+…+aknxn≤(≥)bkx1，x2，…，xn≥0第二节线性规划求解方法线性规划问题的标准形式可以通过对一般形式进行改造而得。①如果第k个式子为：ak1x1+ak2x2+…+aknxn≤(≥)bk，则加入xm+k≥0，改为：ak1x1+ak2x2+…+aknxn＋xm+k＝bk。其他式子也如法炮制。xm+k为松弛变量。②如果问题是求目标函数maxz=c1x1+c2x2+…+cnxn，则化为求目标函数minzَ=－c1x1－c2x2－…－cnxn③如果对某变量xj没有非负限制，则引进两个非负变量xjَ≥0，xََ≥0，令xj＝xjَ－xََ，代入约束条件和目标函数中，化为对全部变量都有非负限制。由此，线性规划的标准形式为：左式中，A为矩阵，b,c,x均为向量：111212122211nnmmmnaaaaaaAaaa我们把满足所有约束条件的一组值称为线性规划的可行解。在所有的可行解中，使目标函数取得最大值或最小值的可行解称为线性规划问题的最优解。对应的目标函数的取值称为最优值。第二节线性规划求解方法线性规划的运算方法很多，如图解法、表上作业法、图上作业法、匈牙利等等。其中最常用的是单纯形法。EXCEL的线性规划求解功能就是按单纯形法设计的。单纯形是美国数学家G.B.丹齐克于1947年首先提出来的。单纯形法的基本思想是：先找出一个基本可行解，对它进行鉴别，看是否是最优解；若不是，则按照一定法则转换到另一改进的基本可行解，再鉴别；若仍不是，则再转换，按此重复进行。因基本可行解的个数有限，故经有限次转换必能得出问题的最优解。如果问题无最优解也可用此法判别。第二节线性规划求解方法一、运输问题一般地，设某种物资有m个产地：A1，A2，…，Am，联合供应n个目标市场：B1，B2，…，Bn。产地产量、各目标市场销量、各产地到目标市场单位运价如下表：目标市场产地B1，B2，…，Bn产量（千克）A1A2┆AmC11C12…C1nC21C22…C2n…┆┆┆Cm1Cm2…Cmna1a2┆am销售量（千克）b1b2…bn表中：ai表示产地Ai的产量（i＝1，2，…m）；bj表示目标市场Bj的销量（j＝1，1，…n）；Cij表示AiBj的单位运价（元／千克）（i＝1，2，…m；j＝1，2，…n）。第二节线性规划求解方法一、运输问题假定产销平衡（∑ai＝∑bj）。设xij表示由产地Ai运往销地Bj的物资数量，那么，上述运输问题的数学模型为：（１）已知数据：cij、ai、bj均为常数；（２）决策变量：xij；（３）目标函数：minS＝c11x11+c12x12+…cmnxmn（４）约束条件：11121121222212111211212222120nnmmmnmnnmmmnmijxxxaxxxaxxxaxxxbxxxbxxxbx第二节线性规划求解方法一、运输问题如果在运输问题中，没有产销平衡这一限制，当产大于销（∑ai＞∑bj）时，这一问题的数学模型为：（１）已知数据：cij、ai、bj均为常数；（２）决策变量：xij；（３）目标函数：（４）约束条件：第二节线性规划求解方法一、运输问题【例1】假定有某种产品要从Ａ、Ｂ、Ｃ三个工厂运到甲、乙、丙、丁四个经销商处。三个工厂的供应量分别为：1000t、800t、500t；四个经销商的需求量分别为：500t、700t、800t、300t，各工厂和经销商之间每吨产品