Excel电子表格规划求解工具在生产运作中的应用

EXCEL规划求解工具在OM中的应用一、EXCEL规划求解使用步骤EXCEL中有一个工具叫规划求解，可以方便地求解线性规划模型。第一步：“规划求解”模块的加载在EXCEL窗口菜单栏的“工具”中查看是否有“规划求解”选项，若没有则在EXCEL窗口菜单栏的“工具”下拉菜单的“加载宏”选项打开“加载宏”对话框来添加“规划求解”。在应用规划求解工具前，要首先确认EXCEL电子表格中包括决策变量、目标函数、约束函数三种信息的单元格或单元格区域。第二步：将要求解模型的所有信息和公式填入电子表格中后，再选取“工具”、“规划求解”命令后，弹出“规划求解参数”对话框。“规划求解参数”对话框的作用就是让计算机知道模型的每个组成部分放在电子表格的什么地方。可以通过键入单元格（或单元格区域）的地址或用鼠标在电子表格相应的单元格（或单元格区域）单击或拖动的办法将有关信息加入到对话框相应的位置。第三步：“规划求解参数”对话框使用1、设置目标单元格在此文本框中应指定目标函数所在单元格的引用位置，此目标单元格，经求解后获得某一特定数值、最大值或最小值，此单元格必须包含公式。美元符号是用来固定地址的。2、等于在此指定是否需要对目标单元格求取最大值、最小值或某一指定数字。3、可变单元格可变单元格指定决策变量所在的各单元格、不含公式，可以有多个区域或单元格，求解时其中的数字不断调整，直到满足约束条件，并且“设置目标单元格”编辑框中指定的单元格达到目标值。可变单元格必须直接或间接与目标单元格相联系。4、约束在此列出当前的所有约束条件。5、添加、更改、删除点击“添加”显示“添加约束”对话框。在添加约束对话框中有三个选项。1）单元格引用位置指定需要约束其中数据的单元格或单元区域，一般在此处添加约束函数不等式左侧的函数表达式的单元格或单元格区域。2）约束值。选择相应的需要添加或修改的关系运算符号（＜＝、＝、＞＝），然后在右侧的编辑框中输入数字、单元格或区域引用及公式等约束条件。3）添加单击“添加”按钮则不返回“规划求解参数”对话框，可继续添加；单击“确定”按钮则返回“规划求解参数”对话框，添加结束。6、选项单击“选项”按钮，弹出“规划求解选项”，选中“采用线性模型”和“假定非负”两个复选框，单击“确定”按钮返回“规划求解参数”对话框。7、求解在“规划求解参数”对话框中单击“求解”按钮。二、规划求解在产品组合中应用某厂有三个车间，每个车间有600小时的生产能力。现有6种产品要生产，每种产品在三个车间的单台加工时间和可获得利润的情况见下表。试制定可使利润达到最大的生产计划？产品P1P2P3P4P5P6在第一车间加工时间210001在第二车间加工时间023200在第三车间加工时间000121单台产品利润（百元）465534建立模型解：设产品Pi的年产量为XiMAXZ=4X1+6X2+5X3+5X4+3X5+4X6s.t2X1+X2+X6≤6002X2+3X3+2X4≤600X4+2X5+X6≤600Xi≥0EXCEL电子表格运用多元网点布局方法——启发式方法启发式方法与最优规划方法的最大不同是它不是精确式算法，不能保证给出的解决方案是最优的，但只要处理得当，获得的可行解与最优解是非常接近的，而且启发式算法相对最优规划方法计算简单，求解速度快。所以在实际应用中，启发式方法是仅次于最优化规划技术的选址方法。启发式方法——CFLP法当配送中心的能力有限制，而且用户的地址和需求量以及设置多个配送中心的数目均已确定的情况下，可采用CFLP法(CapacitatedFacilityLocationProblem)，从配送中心的备选地点中选出总费用最小的由多个配送中心(假设有m个)组成的配送系统。启发式方法——CFLP法步骤(1)初选配送中心地点。通过定性分析，根据配送中心的配送能力和用户需求分布情况适当的确定配送中心的数量及其设置地点，并以此作为初始方案。这一步骤非常重要，因为它将直接影响整个计算的收敛速度。CFLP法的基本思想是：首先假定网点布局方案已经确定，即给出一组初始网点设置地址。根据初始方案按运输规划模型求出各初始网点的供货范围，然后在各供货范围内分别移动网点到其他备选地址上，以使各供货范围内的总成本下降，找到各供货范围内总成本最小的新网点设置地址，再将新网点设置地址代替初始方案，重复上述过程直至各供货范围内总成本不能再下降时为止。为简单起见，以下图的物流网络结构为对象来介绍CFLP方法的处理过程。D1D2B1BjBn备选网点用户图网络结构图上图中的物流网络没有反映出网点的进货关系即不考虑网点的进货成本。容易知道，当物资资源点距离布局网点的计划区域足够远时，这样处理问题是可以理解的。因为这时计划区内各网点从资源点进货的进货成本之差异相对于进货成本本身是微不足道的，因而可以忽略。这样，各网点的进货成本均相等，所以在讨论网点布局时可不考虑。换句话说，进货成本与网点布局方案无关。当然，如果资源点并不是远离计划区域，那就必须考虑进货成本。在此情况下，只需将方法中的运输规划模型换成转运模型即可。下面先介绍CFLP法的基本步骤，然后举例说明。假定某计划区域内网点备选地址已确定，需从这些备选地址中选取q个设置网点。步骤1，给出网点地址初始方案。通过定性分析，根据备选网点的中转能力和物资需求的分布情况，恰当地选择q个点作为设置网点的初始方案。初始方案选择得是否恰当，将直接影响整个计算过程的收敛速度。步骤2，确定各网点的供货范围。用解运输问题的方法确定暂定物流网点的供货范围。设暂定物流网点为DK（K=1,2,…,q），其最大可能设置的规模为dK。如果有n个需求用户，各用户的需求量为bj（j=1,2,…,n）。以运输成本F′最低为目标，即可构成运输规划模型：0min1111'KjqKjKjnjKKjqKnjKjKjXbXdXXCF其中：K=1,2,…,qj=1,2,…,n（4-1）解此运输问题即可求得各暂定网点的供货范围（子区域）。如果考虑网点的进货成本，式（4－1）则应为转运问题模型。解转运模型，除了得到网点的供货范围外，条同时还确定了网点与资源点之间的供货关系。为叙述的方便，用IK（K=1,2,…,q）和JK分别表示各供货区域内的网点备选地址和用户集合。解决运输问题的结果可能出现一个一个用户同属于不同的子区域，这对整个问题的解决并无影响，只需在不同子区域的用户集合中重复考虑即可。步骤3，寻求网点地址的新方案。在各供货子区域内移动网点到其他备选地址上，并按以下费用函数计算子区域内的区域总费用，式中为网点设置成本KJjKiijijKifXCF,,....,2,1qKKIiKif在此基础上找出各供货范围内使区域总费用最小的网点设置点，即满足的网点地址DK，对所有q个子区域可得到新的网点位置设置方案。KiFKIiFKmin,,....,2,1qKqkKD1步骤4，新旧方案对比为便于区别，引进迭代次数的上角标n，n＝0为初始方案。对于和新旧两个方案，分析不等式1KD0KDqKqKKKFF1101（4－2）如果和完全相同，式4－2中必有等式成立说明已获得最终解，即是满意的网点布局地址。否则将新方案代替旧方案，重复步骤2～4，直至和完全相同为止。1KD0KD1KDnKD1nKD例:在某计划区域内，物流网络结构如下图所示，其中有12个需求点，“△”中的数字为各点需求量，弧线旁的数字为运价系数。先需在12个需求点位置上选取3个点作为网点位置地址。假定网点的最大规模为13，设定每个网点的固定成本为10。12345678910111222345435423254452469434631536图物流网络结构图解：由题意知，该计划区域内网点备选地址为12个。【步骤1】根据调查分析，选定备选区域中的4，6，9组成初始方案，即9,6,4310kKD【步骤2】以4，6，9为发货点，各点发货量均为13；以需求点为收货点，需求量为已知；收、发货点之间点的费用系数用最短路线法求得。构成运输规划模型如下表所示。汇费用系数源123456789101112资源量47630310111413161512136349107064910661396712131099100481313需求量542324354322表运输模型解此运输问题得最优解如下表，即为初始网点布局方案。由下表得结果知道，各供货子区域得用户集合为：J1＝{1,2,3,4,5}J2＝{6,7,8,12}J3＝{1,7,9,10,11}【步骤3】寻找各子区域内使区域总费用最小得网点位置。对J1子区域有：表初始方案汇源123456789101112资源量42423213642521393143213需求量542324354322＝0＋1×4＋6×2＋7×3＋4×2＋10＝5540＋10＝5053＋10＝6350＋10＝6049＋10＝591,11111,1fXCFiJjj2,1F3,1F4,1F5,1F50},,,,min{5,14,13,12,11,11FFFFFF所以，在第一子区域内，在备选地址2处设置网点时区域总费用最小。同理可以求得第二子区域内备选地址6为区域费用最小点；第三子区域内备选地址10为区域费用最小点。于是有10,6,21KD【步骤4】以{2，6，10}作为新方案，与原方案{4，6，9}比较。显然，新方案{2，6，10}与原方案{4，6，9}不一样，必有因此返回步骤2，重复步骤2～4。310311KKKKFF第二次迭代所得新方案为：与第一次迭代结果比较，说明不能继续改进，已获得最终解。所以，最佳网点布局地址为{2,6,10}，网点规模均为13。这样设置网点的系统总费用为152。10,6,22KD上面讨论的是网点数目有限的情况，如果网点数目没有限制，则只需对网点数目为1，2，3，….，12诸情况分别进行讨论，找出使系统总费用最低的网点数目作为最佳方案即可。表上作业法表上作业法求解步骤：1、建立初始调运表格；2、用最小元素法或西北角法求初始解；3、对求出的解用闭回路法进行最优检验；4、用闭回路法对解进行调整、检验。例：假如某种商品有三个产地，每天的供应量分别为A1：7吨，A2：4吨，A3：9吨。要将这种产品分别运往4个地区销售，各地区每天的需要量为：B1：3吨，B2：6吨，B3：5吨，B4：6吨。已知从每个供应地到各销地每吨商品的运价如下表所示：B1B2B3B4A1311310A21928A374105汇源求：在满足各地销售量的情况下，应如何调运才能使总的运输费用最小？解：第一步：建立初始调运表格，如下表所示：B1B2B3B4源量A17A24A39汇量3656汇源3）11）3）10）1）9）2）8）7）4）10）5）第二步：用最小元素法求出初始解B1B2B3B4源量A1437A2314A3639汇量3656汇源3）11）3）10）1）9）2）8）7）4）10）5）判断是否是初始解满足的条件：1、表格中基格（数字格）总数应为m+n-1个；2、所有约束均得到满足；3、不存在以数字格（基格）为顶点构成的闭回路。......第三步：闭回路法检验B1B2B3B4源量A1127A21-14A310129汇量36563）11）3）10）1）9）2）8）7）4）10）5）1、从非基格（空格）出发，沿水平或垂直方向前进，当遇到有基格（数字格）时，便转角90度，继续前进，最后回到出发点的回路。2、求出所有非基格检验数。3、如果表格中的检验数都大于或等于零，说明该方案最优。若检验数有负数，则该方案不是最优，需要调整。第四步：用闭回路法对解进行调整。1、从绝对值最大的负检验数的格（非基格，作为入基变量）出发，在初始方案上作一个除该空格之外其余顶点均有运量（数字格或基格）的闭回路，在这条闭回路上进行最大可能的调整。2、在经过的数字格中选择（-1）的最小者，对应的基变量为出基变量，对数据进行调整。本例最终调