生产计划_excel规划求解工具在om中的应用

EXCEL规划求解工具在OM中的应用 一 EXCEL规划求解使用步骤 EXCEL中有一个工具叫规划求解 可以方便地求解线性规划模型 第一步 规划求解 模块的加载在EXCEL窗口菜单栏的 工具 中查看是否有 规划求解 选项 若没有则在EXCEL窗口菜单栏的 工具 下拉菜单的 加载宏 选项打开 加载宏 对话框来添加 规划求解 在应用规划求解工具前 要首先确认EXCEL电子表格中包括决策变量 目标函数 约束函数三种信息的单元格或单元格区域 第二步 将要求解模型的所有信息和公式填入电子表格中后 再选取 工具 规划求解 命令后 弹出 规划求解参数 对话框 规划求解参数 对话框的作用就是让计算机知道模型的每个组成部分放在电子表格的什么地方 可以通过键入单元格 或单元格区域 的地址或用鼠标在电子表格相应的单元格 或单元格区域 单击或拖动的办法将有关信息加入到对话框相应的位置 第三步 规划求解参数 对话框使用1 设置目标单元格在此文本框中应指定目标函数所在单元格的引用位置 此目标单元格 经求解后获得某一特定数值 最大值或最小值 此单元格必须包含公式 美元符号是用来固定地址的 2 等于在此指定是否需要对目标单元格求取最大值 最小值或某一指定数字 3 可变单元格可变单元格指定决策变量所在的各单元格 不含公式 可以有多个区域或单元格 求解时其中的数字不断调整 直到满足约束条件 并且 设置目标单元格 编辑框中指定的单元格达到目标值 可变单元格必须直接或间接与目标单元格相联系 4 约束在此列出当前的所有约束条件 5 添加 更改 删除点击 添加 显示 添加约束 对话框 在添加约束对话框中有三个选项 1 单元格引用位置指定需要约束其中数据的单元格或单元区域 一般在此处添加约束函数不等式左侧的函数表达式的单元格或单元格区域 2 约束值 选择相应的需要添加或修改的关系运算符号 然后在右侧的编辑框中输入数字 单元格或区域引用及公式等约束条件 3 添加单击 添加 按钮则不返回 规划求解参数 对话框 可继续添加 单击 确定 按钮则返回 规划求解参数 对话框 添加结束 6 选项单击 选项 按钮 弹出 规划求解选项 选中 采用线性模型 和 假定非负 两个复选框 单击 确定 按钮返回 规划求解参数 对话框 7 求解在 规划求解参数 对话框中单击 求解 按钮 二 规划求解在产品组合中应用 某厂有三个车间 每个车间有600小时的生产能力 现有6种产品要生产 每种产品在三个车间的单台加工时间和可获得利润的情况见下表 试制定可使利润达到最大的生产计划 建立模型 解 设产品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方法的处理过程 上图中的物流网络没有反映出网点的进货关系即不考虑网点的进货成本 容易知道 当物资资源点距离布局网点的计划区域足够远时 这样处理问题是可以理解的 因为这时计划区内各网点从资源点进货的进货成本之差异相对于进货成本本身是微不足道的 因而可以忽略 这样 各网点的进货成本均相等 所以在讨论网点布局时可不考虑 换句话说 进货成本与网点布局方案无关 当然 如果资源点并不是远离计划区域 那就必须考虑进货成本 在此情况下 只需将方法中的运输规划模型换成转运模型即可 下面先介绍CFLP法的基本步骤 然后举例说明 假定某计划区域内网点备选地址已确定 需从这些备选地址中选取q个设置网点 步骤1 给出网点地址初始方案 通过定性分析 根据备选网点的中转能力和物资需求的分布情况 恰当地选择q个点作为设置网点的初始方案 初始方案选择得是否恰当 将直接影响整个计算过程的收敛速度 步骤2 确定各网点的供货范围 用解运输问题的方法确定暂定物流网点的供货范围 设暂定物流网点为DK K 1 2 q 其最大可能设置的规模为dK 如果有n个需求用户 各用户的需求量为bj j 1 2 n 以运输成本F 最低为目标 即可构成运输规划模型 其中 K 1 2 qj 1 2 n 4 1 解此运输问题即可求得各暂定网点的供货范围 子区域 如果考虑网点的进货成本 式 4 1 则应为转运问题模型 解转运模型 除了得到网点的供货范围外 条同时还确定了网点与资源点之间的供货关系 为叙述的方便 用IK K 1 2 q 和JK分别表示各供货区域内的网点备选地址和用户集合 解决运输问题的结果可能出现一个一个用户同属于不同的子区域 这对整个问题的解决并无影响 只需在不同子区域的用户集合中重复考虑即可 步骤3 寻求网点地址的新方案 在各供货子区域内移动网点到其他备选地址上 并按以下费用函数计算子区域内的区域总费用 式中为网点设置成本 在此基础上找出各供货范围内使区域总费用最小的网点设置点 即满足的网点地址DK 对所有q个子区域可得到新的网点位置设置方案 步骤4 新旧方案对比为便于区别 引进迭代次数的上角标n n 0为初始方案 对于和新旧两个方案 分析不等式 4 2 如果和完全相同 式4 2中必有等式成立说明已获得最终解 即是满意的网点布局地址 否则将新方案代替旧方案 重复步骤2 4 直至和完全相同为止 例 在某计划区域内 物流网络结构如下图所示 其中有12个需求点 中的数字为各点需求量 弧线旁的数字为运价系数 先需在12个需求点位置上选取3个点作为网点位置地址 假定网点的最大规模为13 设定每个网点的固定成本为10 解 由题意知 该计划区域内网点备选地址为12个 步骤1 根据调查分析 选定备选区域中的4 6 9组成初始方案 即 步骤2 以4 6 9为发货点 各点发货量均为13 以需求点为收货点 需求量为已知 收 发货点之间点的费用系数用最短路线法求得 构成运输规划模型如下表所示 解此运输问题得最优解如下表 即为初始网点布局方案 由下表得结果知道 各供货子区域得用户集合为 J1 1 2 3 4 5 J2 6 7 8 12 J3 1 7 9 10 11 步骤3 寻找各子区域内使区域总费用最小得网点位置 对J1子区域有 表初始方案 0 1 4 6 2 7 3 4 2 10 5540 10 5053 10 6350 10 6049 10 59 所以 在第一子区域内 在备选地址2处设置网点时区域总费用最小 同理可以求得第二子区域内备选地址6为区域费用最小点 第三子区域内备选地址10为区域费用最小点 于是有 步骤4 以 2 6 10 作为新方案 与原方案 4 6 9 比较 显然 新方案 2 6 10 与原方案 4 6 9 不一样 必有因此返回步骤2 重复步骤2 4 第二次迭代所得新方案为 与第一次迭代结果比较 说明不能继续改进 已获得最终解 所以 最佳网点布局地址为 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吨 B4 6吨 已知从每个供应地到各销地每吨商品的运价如下表所示 求 在满足各地销售量的情况下 应如何调运才能使总的运输费用最小 解 第一步 建立初始调运表格 如下表所示 第二步 用最小元素法求出初始解 判断是否是初始解满足的条件 1 表格中基格 数字格 总数应为m n 1个 2 所有约束均得到满足 3 不存在以数字格 基格 为顶点构成的闭回路 第三步 闭回路法检验 汇 源 检验数 1 从非基格 空格 出发 沿水平或垂直方向前进 当遇到有基格 数字格 时 便转角90度 继续前进 最后回到出发点的回路 2 求出所有非基格检验数 3 如果表格中的检验数都大于或等于零 说明该方案最优 若检验数有负数 则该方案不是最优 需要调整 第四步 用闭回路法对解进行调整 1 从绝对值最大的负检验数的格 非基格 作为入基变量 出发 在初始方案上作一个除该空格之外其余顶点均有运量 数字格或基格 的闭回路 在这条闭回路上进行最大可能的调整 2 在经过的数字格中选择 1 的最小者 对应的基变量为出基变量 对数据进行调整 本例最终调整如下表所示 汇 源 3 对调整后的运输方案进行检验 本例调整后的运输方案中各空格的检验数如下表所示 汇 源 检验数 由上图可知调整后的运输方案各空格的检验数均大于或等于零 所以该方案为最优方案 课堂练习 例 某商品的供应地和需求地各有三个 各供应地的供应量如下 A1 10吨 A2 7吨 A3 5吨 各需要地的需求量分别为 B1 6吨 B2 8吨 B3 8吨 供应地和需求地的单位运价如下表所示 问 如何规划运输方案才能使运输费用最低 解 1 建立初始调运表格如下表 2 用最小元素法 优先满足运价系数最小的格 在格中填上对应的min 供应量 需求量 函数中的供应量和需求量是修正值 或西北角法求出初始解 如下表所示 3 闭回路调整 检验得最优解 如下表 故最小成本为 C 6 1 4 5 7 2 7 1 4 3 59 元 EXCEL在运输规划模型中的运用 例 某汽车发动机制造厂拟计划生产一批发动机来满足未来四个月汽车安装的需要 为了给出最优的进度安排 使总成本最小 有关人员已收集数据如下表所示 每个月生产一定数量的发动机 没有安装完的入库保存 加班的单位生产成本高于正常时间生产成本 这样 成本由生产成本和库存成本两部分构成 单位成本 单位生产成本 单位库存成本 库存的月份数 试为该问题寻找最优进度方案 使总成本最小 运输模型转化 生产进度安排 转换 产地为每月正常或加班时间生产发动机 供应量为最大产量 销地为每月安装发动机 需求量为计划安装量 EXCEL建模 三 规划求解在库存管理中的应用 利用函数SQRT直接求出 附 多阶段生产安排问题 南方机电制造公司为全国各地生产一种大型机电设备 按照公司的订单合同 不久要交付使用一定数量的机电设备 所以有必要制定为期6个月的设备生产计划 根据合同 公司必须在未来6个月中每个月底交付一定数量的机电设备 由于原料价格 生产条件 保修和维护工作等安排不同 每月的生产能力和生产成本也不同 当然 可以在成本较低的月份多生产