系统规划理论与方法定量篇Word下载.docx

1、483第三季度286302356.75第四季度29.533.533利用最小二乘法来估计回归方程参数并计算模型的拟合优度的整个求解过程复杂，尤其是多元回归分析手工计算较为困难。利用Excel中的回归分析功能可以将整个求解过程交给计算机处理，从而降低分析的难度，提高预测效率。1.在进行计算前，首先要加载“分析工具库”模块回归分析属于Excel的扩展功能，在使用前需要加载“分析工具库”模块，具体的操作步骤如下：（1）鼠标左键点击菜单栏上的“工具”菜单，在打开的“工具”菜单上点击“加载宏”命令，如图所示。（2）在弹出的“加载宏”对话框中，选中“可用加载宏”区域中的“分析工具库”对应的复选框，然后点击“

2、确定”按钮，如图所示。（3）加载成功后，就可以在“工具”菜单中看到“数据分析”命令，如图所示。图3-1 “工具”菜单3-2 “加载宏”对话框3-3 “数据分析”对话框2.计算步骤（1）录入数据将案例1中2003-2008年的需求量录人到Excel电子表格中的A1：C8区域，如图所示。3-4 需求量工作表（2）算术平均法在D1单元格输入“算术平均法预测需求”，在D8单元格输入公式“=AVERAGE（C2：C7）”，按回车键即可得到算术平均法的预测结果。（3）移动平均法以最近两期的物流需求数据作为预测依据。在E1单元格输入“移动平均法预测需求”，在E4单元格输入公式“=AVERAGE（C2：C3）

3、”，按回车键即可得到2005年的需求预测值。左键按住E4单元格右下方的填充柄，向下拉动到E8单元格，即可得到2006 -2009年的需求预测值。（4）加权移动平均法1）按公式进行预测。2）在F1单元格输入“移动加权平均法预测需求”，在F4单元格输入公式“=0.4*C2+0.6*C3”，按回车键即可得到2005年的需求预测值。左键按住F4单元格右下方的填充柄，向下拉动到F8单元格，即可得到2006 -2009年的需求预测值。（5）指数平滑法1）取a等于0.3进行预测。2）在G1单元格输入“指数平滑法预测需求”，在G2单元格输入“139”，在G3单元格输入公式“=0.3*C2+（1-0.3）*G2

4、”，按回车键即可得到2004年的需求预测值。左键按住G3单元格右下方的填充柄，向下拉动到G8单元格，即可得到2005- 2009年的需求预测值。（6）长期趋势法1）左键单击菜单栏中的“工具”，在展开的下拉菜单中点击“数据分析”打开“数据分析”对话框，如图所示。选中“回归”后点击“确定”按钮，打开“回归”对话框，如图所示。3-5“数据分析对话框”图3-6 “回归”对话框一2）单击“Y值输入区域”对应的参数框右侧的折叠对话框按钮，选择区域C2：C7。单击“X值输人区域”对应的参数框右侧的折叠对话框按钮，选择区域B2：B7。单击“输出区域”对应的参数框右侧的折叠对话框按钮，选择$H$10单元格，如图

5、所示。图3-7 “回归”对话框二3）单击“确定”按钮，可以看到回归分析的结果如图所示。其中-10 276.933 33和5.2分别是和的值。在单元格H1中输入“长期预测法需求”，在单元格H2中输入“=$I $26+$I$27*B2”，按回车键即可得到2003年的需求预测值。左键按住H2单元格右下方的填充柄，向下拉动到H8单元格，即可得到2004-2009年的需求预测值。图3-8 “回归”分析结果（7）季节变动预测法1）新建一个工作表，将案例2中2006-2008年的需求量录入到Excel电子表格中的A1：D5区域，如图所示。3-9 数据录入工作表2）在A6单元格输入“年度汇总：”，在B6单元格

6、输入公式“=SUM（B2：B5）”，按回车键即可得到2006年的总需求量。左键按住B6单元格右下方的填充柄，向右拉动到D6单元格，即可得到2006-2008年的总需求量。3）在E1单元格输入“平均值：”，在E2单元格输入公式“=AVERAGE（B2：D2）”，按回车键即可得到第一季度的平均需求量。左键按住E2单元格右下方的填充柄，向下拉动到E5单元格，即可得到第二季度到第四季度的总需求量。4）在D7单元格输入“季度平均值：”，在E7单元格输入公式“=AVERAGE（E2：E5）”，按回车键即可得到所有季度的平均需求量。5）在F1单元格输入“S：”，在F2单元格输入公式“=E2E$7”，按回车键

7、即可得到第一季度的季度指数。左键按住F2单元格右下方的填充柄，向下拉动到F5单元格，即可得到第二季度到第四季度的季度指数。6）在G1单元格输入“2009”，在G2单元格输入公式“=AVERAGE（$C$6：$D$6）/4*F2”，按回车键即可得到2009年第一季度的需求预测值。左键按住G2单元格右下方的填充柄，向下拉动到G5单元格，即可得到2009年第二季度到第四季度的需求预测值。3. 实验结果与分析（1）记录每种预测方法的预测结果。 1）算术平均法、移动平均法、加权移动平均法、指数平滑法以及长期趋势法预测需求的预测结果如图所示。2）季节变动预测结果如图所示。（2）对比各种预测方法在预测精度上

8、的差异？3.2网络流模型案例：下图是连接某产品产地V1，和销地V7，的交通图。弧（Vi，Vj）表示从Vi到Vj的运输线，弧旁的数字表示这条运输线的最大通过能力Cij。现要求制定一个运输方案，使从V1，运到V7，的产品数量最多。1.关于“规划求解”“规划求解”是Excel 中的一个加载宏，借助“规划求解”，可求得工作表上某个单元格（被称为目标单元格）中公式（公式：单元格中的一系列值、单元格引用、名称或运算符的组合，可生成新的值。公式总是以等号 （=） 开始。）的最优值。“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。“规划求解

9、”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。在创建模型过程中，可以对“规划求解”模型中的可变单元格数值应用约束条件（约束条件：“规划求解”中设置的限制条件。可以将约束条件应用于可变单元格、目标单元格或其他与目标单元格直接或间接相关的单元格。而且约束条件可以引用其他影响目标单元格公式的单元格。使用“规划求解”可通过更改其他单元格来确定某个单元格的最大值或最小值。加载“规划求解”模块具体操作步骤如下：（1）点击“工具”菜单，在打开的“工具”菜单上点击“加载宏”命令。（2）在弹出的“加载宏”对话框中，选中“可用加载宏”区域中的“规划求解”对应的复选框，然后

10、点击“确定”按钮，加载成功后，就可以在“工具”菜单中看到“规划求解”命令。最大流问题的Excel求解可以按照以下步骤进行：（1） 建立问题模型，设过弧的流量为，则为，模型即为s.t . （2）输入基础数据首先如图所示，在B3：F15区域输入各个节点的距离，其中D4：D15为空白。然后分别定义B4：B15为“从”，C4：C15为“到”，D4：D15为“流量”以及F4：F15为“容量”。最大流基础数据接下来在单元格H3输人“节点”，在单元格I3输入“净流量”；在区域H4：H10输入各节点序列；在单元格I4根据节点相连情况，输入“=D4+D5”，纵向填充至I10，然后输入J5：J9以及K3：K10的

11、相应内容。最后在单元格I15输入“最大流”，J15输入“= D4+D5”，并定义区域$I $4：$I $10为“净流量”，$K $5：$K $9为“供应需求”以及$J $15为“最大流”。输人后的结果如图所示。最大流初始值输入（3）规划求解在“工具”菜单下选择“规划求解”子菜单，出现“规划求解参数”对话框后，在“设置目标单元格”处输入“$J $15”，并选择“最大值”，在“可变单元格”处输入“$D $4：$D $15”。在添加约束条件处点击“添加”按钮，出现“添加约束”对话框后，依次填入所有约束条件，具体结果如图所示。最大流规划求解参数最后在“规划求解参数”对话框中点击“选项”按钮，出现“规划

12、求解选项”对话框后，选定“采用线性模型”以及“假定非负”，然后点击“确定”，返回“规划求解参数”对话框，点击“求解”按钮即可求解，见图。最大流规划求解选项3. 结果与分析（1）记录实验结果；最终的实验结果如图所示，该网络的最大流是20。（2）分析指出上述项目中哪条路径是瓶颈路径。3.3运输模型某公司有3个分厂，分别设在武汉、东莞和长春。3个分厂生产的产品需要运往华东、华南、华北和华中4个配送中心。3个分厂的每月产能如3-2表所示。某月4个配送中心的需求量如表3-3所示。由于运输里程和交通条件的差异，各分厂到配送中心的运费并不相同，各分厂到各个配送中心的运输费用如表3-4所示。请利用线性规划方法

13、，安排一个合适的物流方案，使得运输费用最低。表3-2 产能表 单位：吨武汉厂东莞厂长春厂200240表3-3 需求表华北华中华东华南160170100表3-4 运费表元吨分厂1400260120015002450160017002800请利用线性规划方法，安排一个合适的物流方案，使运输费用最低。1. 求解步骤（1）建立数学模型。以1，2，3代替武汉、东莞和长春，a，b，c，d代替华北、华中、华东和华南，设x1a为华北到武汉的运量，其他依此类推，依案例分析可得：s.t.（2）录入基础数据。将案例中的产能表、需求表和运费表录入Excel工作表中，录入后的效果如图所示。运输问题基础数据在单元格G9输

14、入公式“=SUM（C9：F9）”，按回车键即可得到武汉厂的生产量，然后纵向填充至G11；在单元格C12输入公式“=SUM（C9：C11）”，按回车键即可得到华北的需求量，然后横向填充至F12；在单元格I14输入“=SUMPRODUCT（C3：F5，C9：F11）”。（3）规划求解。左键单击菜单栏中的“工具”，在展开的下拉菜单中点击“规划求解”命令打开“规划求解参数”对话框。单击“设置目标单元格”对应的参数框右侧的折叠对话框按钮，选择单元格“$I$14”。单击“等于”区域中的“最小值”，从而为线性规划设置目标函数，并指定最优操作为求最小值。单击“可变单元格”对应的参数框右侧的折叠对话框按钮，选择

15、区域“$C$9：$F$11”，即可为线性规划问题指定决策变量。在“约束”区域，单击“添加”按钮，弹出“添加约束”对话框，单击“单元格引用位置”对应的参数框右侧的折叠对话框按钮，选择“$G$9：$G$11”区域。在运算符下拉列表中选择“=”。单击“约束值”对应的参数框右侧的折叠对话框按钮，选择“$I$9：$I$11”。单击“确定”按钮，从而为线性规划添加产能约束。按照上一步的操作，为线性规划添加需求约束，具体结果如图所示。最后在“规划求解参数”对话框中点击“选项”按钮，出现“规划求解选项”对话框后，选定“采用线性模型”以及“假定非负”，然后点击“确定”，返回“规划求解参数”规划求解参数对话框点击

16、“求解”按钮即可求解，见图4-5。规划求解选项情况2. 结果与分析（1）最终的实验结果如图所示。（2）运输问题在增加约束条件后，其最优解将如何变化？有没有可能在运量增加的同时运费反而降低？3.4最短路模型有如下的网络图。需要计算从v1 点到v8 点的最短路经。用Excel求解最短路径的原理是：令变量为0 或者1。即如果最短路经通过v1v2，v2v4.，则设变量为1，不通过则为0，除起点和终点之外，每个点的进出权数和是0，起点的进出权数和是1，终点是-1，目标函数是各边权数和对应变量乘积的和。于是得到一组等式约束，通过求解可以得到最短路经和最短路程。在 Excel 上建立模型如下图，添加约束求解

17、结果图中变量是1的边就是最短路经通过的边， 即v1v2-v2v5-v5v7-v7v8,最短路程是15。注意：1E-06 表示接近0，可以看作0，可以通过调节“选项”中的精度一项达到。如把精度降到0.01 就可以得到变量全为0 或1。请读者自己调节。3.5选址模型学过物流设施规划的人应该都知道精确重心法，它是设施选址常用的方法，什么是精确重心法？简单地说，在一个直角坐标系上有若干个点，已知他们的坐标，要求一点，使这一点到这些点的直线距离之和最小。假设有5个点，其坐标分别是（3，1）（5，2）（4，3）（2，4）（1，5），另外赋予每个点不同的权重1，7，3，3，6。我们用欧几里德距离计算公式计算两点间的距离。即然后在Excel表格上建立模型如图添加约束得求解之后得即所求点是（3.93，2.98），最短距离是40.65