EXCEL求解线性规划问题.ppt

1、第五章利用EXCEL解决线性规划问题，建立线性规划问题模型。用EXCEL解决线性规划问题并分析运算结果(灵敏度分析)的目的：1 .基本的EXCEL知识，1。工作表命名、功能：存储信息、计算、数据排序、以图表形式显示数据、计划解决方案、会计分析、概率和统计分析等。(1)激活工作表1。单击“工作表1”选项卡，(2)命名工作表或(3)格式/工作表重命名以显示“重命名工作表”对话框，(4)键入工作表名称，2。工作表，4。计算，Excel-2003 256列，65535行，Excel-2007 16384列，1048576行，3。将数据输入单元格，常量：(1)输出数据时计算，A1=456 789，(2)

2、根据其他单元格的数据计算，a3456b3-789c3=a3b3，5。引用公式中的其他单元格，这些单元格可以引用此工作簿中的任何单元格或单元格组的数据，也可以引用其他工作簿中的任何单元格或单元格组的数据。引用单元格数据后，公式的计算值将随着引用单元格数据的变化而变化。(1)参考类型，三种类型：相对参考，绝对参考，混合参考，格式：A3，B6，(2)相对参考。使用相对引用后，系统将记住创建公式的单元格和被引用单元格的相对位置。复制此公式时，新的公式单元格和引用的单元格仍保持此相对位置。绝对引用是指被引用单元格和被引用公式单元格之间的位置关系是绝对的，无论公式是否被复制到任何单元格，公式都引用原始单元

3、格的数据。(4)混合引用，格式：$a$3 $d$5，格式：$A3列是绝对的，行是相对的，B$ 3列是相对的，行是绝对的，a，7，用Excel求解，1。关于“求解器”2。如何加载“求解器”3。2.如何加载规划求解，1)单击“工具”菜单上的“加载项”，a，9，2)在弹出对话框的“可用加载项”列表框中，选中要添加的加载项的规划求解选项旁边的复选框，然后单击“确定”。单击确定后，工具单击“规划求解”按钮，将出现以下规划求解参数设置对话框、a，11，单击“添加”显示添加约束对话框、a，12、a，13，选项：显示规划求解选项对话框，您可以在其中加载或保存规划求解模型，并执行规划2。确定决策变量的存储单元并

4、任意输入一组数据；3.确定约束条件中左端项系数的存储单元，并输入约束条件的左端项系数；4.在约束左端项系数存储单元的右单元格中输入约束左端项的计算公式，计算当前决策变量对应的约束左端项的函数值。5.在步骤4中，在数据右侧的约束条件中输入右端项(即常量项)。6.确定目标函数值的存储单元，并在该单元中输入目标函数值电容的计算公式。举一个简单的例子，一个工厂计划生产两种产品，利润分别为2和3，知道生产一个单位产品所需的设备小时数和两种原材料的消耗，如表所示，目标是确定两种产品的产量并在不超过资源限制的情况下获得最大利润。a、16、建立数学公式(步骤1)，在工作表顶部输入数据以确定每个决策变量对应的单

5、元格位置，选择单元格输入公式，找到目标函数值以确定约束单元格输入公式，计算每个约束条件的左值以确定约束单元格输入公式，并计算每个约束条件的右值。数据可以通过复制粘贴或直接输入的方式导入。a，17，建立数学公式(步骤2)，在工作表顶部输入数据以确定每个决策变量对应的单元格位置，选择单元格输入公式，计算每个约束条件的左值，选择单元格输入公式，并计算每个约束条件的右值。在图中，B12和C12被指定为可变单元格。可变单元格存储决策变量的值，可变单元格的数量等于决策变量的数量。a、18、建立数学公式(步骤3)，在工作表顶部输入数据以确定每个决策变量对应的单元格位置，选择一个单元格输入公式，找到目标函数值

6、以确定约束单元格输入公式，计算每个约束条件的左值以确定约束单元格输入公式，并计算每个约束条件的右值。a，19，建立数学公式(步骤4)，在工作表顶部输入数据以确定每个决策变量对应的单元格位置，选择单元格输入公式，找到目标函数的值以确定约束单元格输入公式，计算每个约束条件的左值以确定约束单元格输入公式，并计算每个约束条件的右值。在约束单元格中，需要填写计算约束函数值的公式。a，20，建立数学公式(步骤5)，在工作表顶部输入数据以确定每个决策变量对应的单元格位置，选择单元格输入公式，找到目标函数值以确定约束单元格输入公式，计算每个约束条件的左值以确定约束单元格输入公式，计算每个约束条件的右值，a，2

7、1，调用编程求解模块， 选择工具下拉菜单选择求解器选项(需要预先在办公安装盘上安装求解器功能)，a，22，填写目标单元格和变量单元格，在目标单元格中输入B14，在变量单元格中输入B12:C12选择添加。 在上图所示的界面中，您需要输入目标单元格和变量单元格，添加约束，并设置选项。a，23，添加约束。在“添加约束”对话框中，在单元格引用位置输入B17，选择=，并在约束值中输入D17。选择添加第三个条件。添加后，选择确定。当“规划求解参数”对话框重新出现时，选择选项，一个，24，“选项”设置。当选项对话框出现时，选择假设不是负数。选择确定、a、25，并用Excel求解。出现“规划和求解参数”对话框

8、，并选择“求解”。a，26，保存解决方案结果。出现解决方案结果对话框时，选择保存规划解决方案结果。选择确定。a，27，操作结果报告，其中列出了目标单元格和可变单元格及其初始值、最终结果、约束和关于约束的信息。初始值和最终值是指溶液前后的单元格值。，a，28，敏感性分析报告(1)，单元格中的单元格是指决策变量所在单元格的地址名称是指决策变量的名称，最终值是指决策变量的最终值，即最优值递减成本是指变量在最优解中等于0，相应目标函数中的系数增加或减少多少， 最优解不再是0“目标系数”，已知条件“允许增量”和“允许减量”是指当目标函数中的系数在增量和减量范围内变化时，最优解保持不变(注：最优值变化)，

9、a，29，灵敏度分析报告(2)， 受约束的单元格单元格指的是地址名称左边的单元格是约束名称左边的最终值是约束左边的值影子价格是指约束右边增加或减少一个单位，而目标函数值的增加或减少数是指约束右边的值。 对于已知条件“允许增量，减少”意味着当约束的右侧在允许范围内变化时，影子价格不会变化。因此，当右端项目在一定范围内变化时，影子价格保持不变，目标函数值的变化等于右端项目的变化值乘以影子价格a，30，这由极限值报告解释，并列出目标单元格和可变单元格及其数值、上限和下限以及目标值。具有整数约束的模型无法生成成本报告。其中，下限是在满足约束条件和保持其他可变单元格的值不变的情况下，可变单元格可以取的最

10、小值。上限是在这种情况下可以采用的最大值。a，31，extension，下面是目标系数同时改变并且约束右端值同时改变的情况的扩展。(1)目标系数同时变化的100%规则c:如果目标函数系数同时变化，则计算各系数在同一方向的系数允许变化范围内的变化百分比，然后将各系数的变化百分比相加。如果得到的和不超过100%，最优解不会改变；如果超过100%，则不确定最优解是否已经改变。如果对应于x1的目标系数c1从2变化到1.8，并且对应于x2的目标系数c2从3变化到3.5，根据灵敏度分析报告，从2到1的允许减少的百分比是(2-1.8)/0.5=40%，并且从3到3.5的允许增加的百分比是(3.5-3)/1=

11、50%。a，32，(2)约束条件B的右端值同时改变的100%规则：同时改变几个或所有函数的约束条件的右端值。如果这些变化的幅度不大，影子价格可以用来预测变化的影响。为了判断这些变化的幅度是否被允许，计算在相同方向上允许的变化范围内每个变化的百分比。如果所有百分比之和不超过100%，那么影子价格仍然有效；如果所有百分比之和超过100%，就不可能确定影子价格是否有效。如果右端值B分别从8、16、12变为8.5、15和11，使用100%规则， b1从8变为8.5，占(8.5-8)/2=25%，B2从16变为15，B3从12变为15，a，33，已知线性规划问题的灵敏度分析报告如下：改变单元、约束、20

12、12年12月管理创新实验班期末考试试题(1)写出问题的最优解，(2)分析当目标系数x1减少5且目标系数x2增加4时，最优解是否改变。 (3)当第一资源约束的右端值增加30，而第二资源约束的右端值增加4，第三资源约束的右端值减少15时，分析目标函数值的变化。，a，34，解：(1)最优解为x1=0，x2=12.4，x3=9.5(2)x1的目标系数减少5，占允许减少量的5/=0%,而x2的目标系数增加4，占允许增加量的4/7.8=51.2%。变化百分比总和为51.2%，不超过100%，因此最优解保持不变。(3)第一个资源约束的右端值增加30，占30 /=0%，第二个资源约束增加4，占4/15=26.

13、7%，第三个资源约束减少15，占15/50=30%。变化百分比总和为56.7%，不超过100%，因此影子价格仍然有效。因此，目标函数值的变化是30 0 4 2.8 15 1.2=11.2 18=6.8，a，35。练习：话务员调度问题，一家寻呼公司雇佣了很多话务员工作，他们每天工作3个小时，每个时段从午夜开始，早上3点，早上6点，早上9点，中午12点，下午3点。管理层安排每个操作员每天连续工作3次。根据调查，由于业务量不同，不同时期所需的操作人员数量也不同，公司支付的工资也不同。相关数据见下表。问：我们如何安排操作员来确保服务人员的数量最大限度地降低总成本？答案：这个问题实际上是一个成本效益平衡的问题。管理层应该在为客户提供满意服务的同时控制成本，因此有必要在成本和收益之间找到平衡。由于每个工段的工作时间为3小时，一天分为8班，每个人连续工作3个工段。每个班次的时间表如下：a，38，a，39。为了建立一个数学模型，对应于一般的成本效益平衡