《excel电子表格规划求解工具在生产运作中的应用》由会员分享，可在线阅读，更多相关《excel电子表格规划求解工具在生产运作中的应用（60页珍藏版）》请在金锄头文库上搜索。
1、EXCEL规划求解工具在OM中的应用一、EXCEL规划求解使用步骤EXCEL中有一个工具叫规划求解，可以方 便地求解线性规划模型。第一步：“规划求解”模块的加载在EXCEL窗口菜单栏的“工具”中查看是 否有“规划求解”选项，若没有则在EXCEL窗 口菜单栏的“工具”下拉菜单的“加载宏”选项 打开“加载宏”对话框来添加“规划求解”。在应用规划求解工具前，要首先确认 EXCEL电子表格中包括决策变量、目标函数 、约束函数三种信息的单元格或单元格区域 。第二步：将要求解模型的所有信息和公式填入 电子表格中后，再选取“工具”、“规划求解” 命令后，弹出“规划求解参数”对话框。“规划求解参数”对话框的作
2、用就是让计算 机知道模型的每个组成部分放在电子表格的 什么地方。可以通过键入单元格（或单元格 区域）的地址或用鼠标在电子表格相应的单 元格（或单元格区域）单击或拖动的办法将 有关信息加入到对话框相应的位置。第三步：“规划求解参数”对话框使用 1、设置目标单元格在此文本框中应指定目标函数所在单元格 的引用位置，此目标单元格，经求解后获 得某一特定数值、最大值或最小值，此单 元格必须包含公式。美元符号是用来固定 地址的。 2、等于在此指定是否需要对目标单元格求取最大 值、最小值或某一指定数字。3、可变单元格可变单元格指定决策变量所在的各单元 格、不含公式，可以有多个区域或单元格 ，求解时其中的数字
3、不断调整，直到满足 约束条件，并且“设置目标单元格”编辑框 中指定的单元格达到目标值。可变单元格 必须直接或间接与目标单元格相联系。 4、约束在此列出当前的所有约束条件。5、添加、更改、删除点击“添加”显示“添加约束”对话框。在添 加约束对话框中有三个选项。 1）单元格引用位置指定需要约束其中数据的 单元格或单元区域，一般在此处添加约束 函数不等式左侧的函数表达式的单元格或 单元格区域。 2）约束值。选择相应的需要添加或修改的关 系运算符号（、），然后在 右侧的编辑框中输入数字、单元格或区域 引用及公式等约束条件。 3）添加单击“添加”按钮则不返回“规划求解参数” 对话框，可继续添加；单击“确
4、定”按钮则 返回“规划求解参数”对话框，添加结束。6、选项单击“选项”按钮，弹出“规划求解选项”， 选中“采用线性模型”和“假定非负”两个复 选框，单击“确定”按钮返回“规划求解参数 ”对话框。 7、求解在“规划求解参数”对话框中单击“求解”按钮 。二、规划求解在产品组合中应用某厂有三个车间，每个车间有600小时 的生产能力。现有6种产品要生产，每种产 品在三个车间的单台加工时间和可获得利 润的情况见下表。试制定可使利润达到最 大的生产计划？产产品P1P2P3P4P5P6在第一车间车间 加工时间时间210001在第二车间车间 加工时间时间023200在第三车间车间 加工时间时间000121单单
5、台产产品利润润（百元）465534建立模型解：设产品Pi的年产量为XiMAXZ=4X1+6X2+5X3+5X4+3X5+4X6s.t2X1+X2+X66002X2+3X3+2X4 600X4+2X5+X6 600Xi0 EXCEL电电子表格运用多元网点布局方法启发式方法启发式方法与最优规划方法的最大不 同是它不是精确式算法，不能保证给出的 解决方案是最优的，但只要处理得当，获 得的可行解与最优解是非常接近的，而且 启发式算法相对最优规划方法计算简单， 求解速度快。所以在实际应用中，启发式 方法是仅次于最优化规划技术的选址方法 。启发式方法CFLP法当配送中心的能力有限制，而且用户的 地址和需求
6、量以及设置多个配送中心的 数目均已确定的情况下，可采用CFLP法 (Capacitated Facility Location Problem) ，从配送中心的备选地点中选出总费用 最小的由多个配送中心(假设有m个)组成 的配送系统。启发式方法CFLP法步骤(1)初选配送中心地点。通过定性分析，根据 配送中心的配送能力和用户需求分布情况 适当的确定配送中心的数量及其设置地点 ，并以此作为初始方案。这一步骤非常重 要，因为它将直接影响整个计算的收敛速 度。CFLP法的基本思想是：首先假定网点 布局方案已经确定，即给出一组初始网点 设置地址。根据初始方案按运输规划模型 求出各初始网点的供货范围，然
7、后在各供 货范围内分别移动网点到其他备选地址上 ，以使各供货范围内的总成本下降，找到 各供货范围内总成本最小的新网点设置地 址，再将新网点设置地址代替初始方案， 重复上述过程直至各供货范围内总成本不 能再下降时为止。 为简单起见，以下图的物流网络结构 为对象来介绍CFLP方法的处理过程。 D1D2B1BjBn备选网点用户图 网络结构图上图中的物流网络没有反映出网点的 进货关系即不考虑网点的进货成本。容易 知道，当物资资源点距离布局网点的计划 区域足够远时，这样处理问题是可以理解 的。因为这时计划区内各网点从资源点进 货的进货成本之差异相对于进货成本本身 是微不足道的，因而可以忽略。这样，各 网
8、点的进货成本均相等，所以在讨论网点 布局时可不考虑。换句话说，进货成本与 网点布局方案无关。 当然，如果资源点并不是远离计划区域， 那就必须考虑进货成本。在此情况下，只需将 方法中的运输规划模型换成转运模型即可。下面先介绍CFLP法的基本步骤，然后举例 说明。假定某计划区域内网点备选地址已确定， 需从这些备选地址中选取q个设置网点。 步骤1，给出网点地址初始方案。通过定性分析，根据备选网点的中转能 力和物资需求的分布情况，恰当地选择q个 点作为设置网点的初始方案。初始方案选择 得是否恰当，将直接影响整个计算过程的收 敛速度。 步骤2，确定各网点的供货范围。用解运输问题的方法确定暂定物流网点 的
9、供货范围。设暂定物流网点为DK（K=1,2,q） ，其最大可能设置的规模为dK。如果有n个 需求用户，各用户的需求量为bj（ j=1,2,n）。以运输成本F最低为目标 ，即可构成运输规划模型： 其中：其中：K=1,2,q j=1,2,n K=1,2,q j=1,2,n （4-1）解此运输问题即可求得各暂定网点的供 货范围（子区域）。如果考虑网点的进货成本，式（41 ）则应为转运问题模型。解转运模型，除 了得到网点的供货范围外，条同时还确定 了网点与资源点之间的供货关系。 为叙述的方便，用IK（K=1,2,q）和JK 分别表示各供货区域内的网点备选地址和 用户集合。解决运输问题的结果可能出现一个
10、一 个用户同属于不同的子区域，这对整个问 题的解决并无影响，只需在不同子区域的 用户集合中重复考虑即可。 步骤3，寻求网点地址的新方案。在各供货子区域内移动网点到其他备 选地址上，并按以下费用函数计算子区域 内的区域总费用，式中 为网点设置成本 在此基础上找出各供货范围内使区域 总费用最小的网点设置点，即满足的网点地址DK，对所有q个子区域可得到新 的网点位置设置方案 。 步骤4，新旧方案对比为便于区别，引进迭代次数的上角标n， n0为初始方案。对于 和 新旧两个方案，分析 不等式 （42）如果 和 完全相同，式42 中必有等式成立说明已获得最终解， 即是满意的网点布局地址。否则将新方 案代替
11、旧方案，重复步骤24，直至 和 完全相同为止。 例: 在某计划区域内，物流网络结构如下 图所示，其中有12个需求点，“”中的数 字为各点需求量，弧线旁的数字为运价系数 。先需在12个需求点位置上选取3个点作为 网点位置地址。假定网点的最大规模为13， 设定每个网点的固定成本为10。 12345678910111222345435423254452469434 631536图 物流网络结构图解：由题意知，该计划区域内网点备选地址 为12个。 【步骤1】根据调查分析，选定备选区域中的 4，6，9组成初始方案，即 【步骤2】以4，6，9为发货点，各点发货量 均为13；以需求点为收货点，需求量为已 知
12、；收、发货点之间点的费用系数用最短 路线法求得。构成运输规划模型如下表所 示。 汇 费用 系数 源123456789101112资 源 量47630310111413161512136349107064910661396712131099100481313需求 量542324354322表 运 输 模 型解此运输问题得最优解如下表，即为初 始网点布局方案。由下表得结果知道，各供货子区域得用 户集合为：J11,2,3,4,5J26,7,8,12J31,7,9,10,11 【步骤3】寻找各子区域内使区域总费用最小 得网点位置。 对J1子区域有：表 初 始 方 案 汇 源12345678910111
13、2资 源 量424232 136 425 21393 1 432 13需求 量542324354322 0146273421055 401050 531063 501060 491059所以，在第一子区域内，在备选地址2处 设置网点时区域总费用最小。同理可以求得第二子区域内备选地址6 为区域费用最小点；第三子区域内备选地 址10为区域费用最小点。于是有 【步骤4】以2，6，10作为新方案，与原方 案4，6，9比较。显然，新方案2，6，10与原方案4 ，6，9不一样，必有 因此返回步骤2，重复步骤24。 第二次迭代所得新方案为：与第一次迭代结果比较，说明不能继 续改进，已获得最终解。所以，最佳网
14、点布局地址为2,6,10 ，网点规模均为13。这样设置网点的系统 总费用为152。 上面讨论的是网点数目有限的情况，如 果网点数目没有限制，则只需对网点数目为 1，2，3，.，12诸情况分别进行讨论，找 出使系统总费用最低的网点数目作为最佳方 案即可。 表上作业法表上作业法求解步骤： 1、建立初始调运表格； 2、用最小元素法或西北角法求初始解； 3、对求出的解用闭回路法进行最优检验； 4、用闭回路法对解进行调整、检验。例：假如某种商品有三个产地，每天的供应量分别为A1 ：7吨，A2：4吨，A3：9吨。要将这种产品分别运往 4个地区销售，各地区每天的需要量为：B1：3吨， B2：6吨，B3：5吨
15、，B4：6吨。已知从每个供应地到 各销地每吨商品的运价如下表所示：B1 B2 B3 B4A1 3 11 3 10A2 1 9 2 8A3 7 4 10 5汇 源运 价 系 数求：在满足各地销售量的情况下，应如何调运才能使 总的运输费用最小？解： 第一步：建立初始调运表格，如下表所示：B1 B2 B3 B4源量A1 7A2 4A3 9汇汇量3 6 5 6汇 源运 价 系 数3）11）3）10）1）9）2）8）7）4）10）5）第二步：用最小元素法求出初始解B1 B2 B3 B4源量A1 4 3 7A2 3 1 4A3 6 3 9汇汇量3 6 5 6汇 源运 价 系 数3）11）3）10）1）9）2）8）7）4）10）5）判断是否是初始解满足的条件： 1、表格中基格（数字格）总数应为m+n-1个； 2、所有约束均得到满足； 3、不存在以数字格（基格）为顶点构成的闭回路。.第三步：闭回路法检验B1 B2 B3 B4源量A1 1 2 7A2 1 -1 4A3
