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

例: 在某计划区域内，物流网络结构如下 图所示，其中有12个需求点，“△”中的数 字为各点需求量，弧线旁的数字为运价系数。 先需在12个需求点位置上选取3个点作为网 点位置地址。假定网点的最大规模为13，设 定每个网点的固定成本为10。
2 2 12 9 5 4 4 6 3 5 3 10 4 4 3 4
步骤2，确定各网点的供货范围。 用解运输问题的方法确定暂定物流网点 的供货范围。 设暂定物流网点为DK（K=1,2,…,q）， 其最大可能设置的规模为dK。如果有n个需 求用户，各用户的需求量为bj （j=1,2,…,n）。以运输成本F′最低为目 标，即可构成运输规划模型：
q n ' min F C Kj X Kj K 1 j 1 n X Kj d K j 1 q X Kj b j K 1 X 0 Kj
第二步：将要求解模型的所有信息和公式填入 电子表格中后，再选取“工具”、“规划求 解”命令后，弹出“规划求解参数”对话框。 “规划求解参数”对话框的作用就是让计 算机知道模型的每个组成部分放在电子表格 的什么地方。可以通过键入单元格（或单元 格区域）的地址或用鼠标在电子表格相应的 单元格（或单元格区域）单击或拖动的办法 将有关信息加入到对话框相应的位置。
所以，在第一子区域内，在备选地址2处 设置网点时区域总费用最小。 同理可以求得第二子区域内备选地址6 为区域费用最小点；第三子区域内备选地 址10为区域费用最小点。于是有
D 2,6,10
1 K
【步骤4】以{2，6，10}作为新方案，与原方 案{4，6，9}比较。 显然，新方案{2，6，10}与原方案{4， 6，9}不一样，必有
【步骤3】寻找各子区域内使区域总费用最小 得网点位置。 对J1子区域有：
表 初始方案 汇 源 4 6 9 需求 量 3 5 4 2 3 2 4 1 2 2 4 3 2 4 3 5 2 4 2 1 3 5 5 4 4 3 3 2 2 2 2 6 7 8 9
10 11 12
资 源 量
13 13 13
F1,1
表 运 输 模 型
汇 费用 系数 源
1
2
3
4
5
6
7
8
9
10
11
12
资 源 量
13
4 6 9 需求 量
7 3 6 5
6 4 7 4
3 9
12
0
10
3 7
10
10
11
14
131615120 9 46 9 3
4
10
9 0 4
10
6 8 2
6
13
13
13
4 3
13
2
3
2
5
2
解此运输问题得最优解如下表，即为初 始网点布局方案。 由下表得结果知道，各供货子区域得用 户集合为： J1＝{1,2,3,4,5} J2＝{6,7,8,12} J3＝{1,7,9,10,11}
min FK iI F K Ki

K 1,2,...., q,
的网点地址DK，对所有q个子区域可得到新 DK q1 。 的网点位置设置方案 k
步骤4，新旧方案对比 为便于区别，引进迭代次数的上角标n， n＝0为初始方案。 1 0 对于 DK 和 DK 新旧两个方案，分析 不等式
第三步：“规划求解参数”对话框使用 1、设置目标单元格 在此文本框中应指定目标函数所在单元格 的引用位置，此目标单元格，经求解后获 得某一特定数值、最大值或最小值，此单 元格必须包含公式。美元符号是用来固定 地址的。 2、等于 在此指定是否需要对目标单元格求取最大 值、最小值或某一指定数字。
3、可变单元格 可变单元格指定决策变量所在的各单 元格、不含公式，可以有多个区域或单元 格，求解时其中的数字不断调整，直到满 足约束条件，并且“设置目标单元格”编 辑框中指定的单元格达到目标值。可变单 元格必须直接或间接与目标单元格相联系。 4、约束 在此列出当前的所有约束条件。
5、添加、更改、删除 点击“添加”显示“添加约束”对话框。 在添加约束对话框中有三个选项。 1）单元格引用位置指定需要约束其中数据的 单元格或单元区域，一般在此处添加约束 函数不等式左侧的函数表达式的单元格或 单元格区域。 2）约束值。选择相应的需要添加或修改的关 系运算符号（＜＝、＝、＞＝），然后在 右侧的编辑框中输入数字、单元格或区域 引用及公式等约束条件。
P2 1 2 0 6
P3 0 3 0 5
P4 0 2 1 5
P5 0 0 2 3
P6 1 0 1 4
建立模型
解：设产品Pi的年产量为Xi
MAXZ=4X1+6X2+5X3+5X4+3X5+4X6
s.t
2X1+X2+X6≤600 2X2+3X3+2X4 ≤600 X4+2X5+X6 ≤600 Xi≥0 EXCEL电子表格运用
2 10
10
8 5
求：在满足各地销售量的情况下，应如何调运才能使 总的运输费用最小？
解： 第一步：建立初始调运表格，如下表所示：
当配送中心的能力有限制，而且用户的 地址和需求量以及设置多个配送中心的 数目均已确定的情况下，可采用CFLP法 (Capacitated Facility Location Problem)， 从配送中心的备选地点中选出总费用最 小的由多个配送中心(假设有m个)组成的 配送系统。
启发式方法——CFLP法步骤 (1)初选配送中心地点。通过定性分析，根 据配送中心的配送能力和用户需求分布情 况适当的确定配送中心的数量及其设置地 点，并以此作为初始方案。这一步骤非常 重要，因为它将直接影响整个计算的收敛 速度。
为简单起见，以下图的物流网络结构 为对象来介绍CFLP方法的处理过程。
D1 D2
备选网点
B1
Bj
Bn
用户
图
网络结构图
上图中的物流网络没有反映出网点的 进货关系即不考虑网点的进货成本。容易 知道，当物资资源点距离布局网点的计划 区域足够远时，这样处理问题是可以理解 的。因为这时计划区内各网点从资源点进 货的进货成本之差异相对于进货成本本身 是微不足道的，因而可以忽略。这样，各 网点的进货成本均相等，所以在讨论网点 布局时可不考虑。换句话说，进货成本与 网点布局方案无关。
1 0 FK FK K 1 K 1 q q
（4－2）
1 0 如果 DK 和 DK 完全相同，式4－2 1 DK 中必有等式成立说明已获得最终解， 即是满意的网点布局地址。否则将新方 n 案代替旧方案，重复步骤2～4，直至 DK n 和 DK1 完全相同为止。
当然，如果资源点并不是远离计划区域， 那就必须考虑进货成本。在此情况下，只需将 方法中的运输规划模型换成转运模型即可。 下面先介绍CFLP法的基本步骤，然后举例 说明。 假定某计划区域内网点备选地址已确定， 需从这些备选地址中选取q个设置网点。
步骤1，给出网点地址初始方案。 通过定性分析，根据备选网点的中转能 力和物资需求的分布情况，恰当地选择q个 点作为设置网点的初始方案。初始方案选择 得是否恰当，将直接影响整个计算过程的收 敛速度。
例：假如某种商品有三个产地，每天的供应量分别为 A1：7吨，A2：4吨，A3：9吨。要将这种产品分别运 往4个地区销售，各地区每天的需要量为：B1：3吨， B2：6吨，B3：5吨，B4：6吨。已知从每个供应地到 各销地每吨商品的运价如下表所示：
汇
源
B1
B2
B3
B4
A1
A2 A3
3
1 7
11
9 4
3
（4-1）
其中：K=1,2,…,q
j=1,2,…,n
解此运输问题即可求得各暂定网点的供 货范围（子区域）。 如果考虑网点的进货成本，式（4－1） 则应为转运问题模型。解转运模型，除了 得到网点的供货范围外，条同时还确定了 网点与资源点之间的供货关系。
为叙述的方便，用IK（K=1,2,…,q）和JK 分别表示各供货区域内的网点备选地址和 用户集合。 解决运输问题的结果可能出现一个一 个用户同属于不同的子区域，这对整个问 题的解决并无影响，只需在不同子区域的 用户集合中重复考虑即可。
EXCEL规划求解工具在OM中的应用
一、EXCEL规划求解使用步骤
EXCEL中有一个工具叫规划求解，可以方 便地求解线性规划模型。 第一步：“规划求解”模块的加载 在EXCEL窗口菜单栏的“工具”中查看 是否有“规划求解”选项，若没有则在 EXCEL窗口菜单栏的“工具”下拉菜单的 “加载宏”选项打开“加载宏”对话框来添 加“规划求解”。 在应用规划求解工具前，要首先确认 EXCEL电子表格中包括决策变量、目标函数、 约束函数三种信息的单元格或单元格区域。
CFLP法的基本思想是：首先假定网点 布局方案已经确定，即给出一组初始网点 设置地址。根据初始方案按运输规划模型 求出各初始网点的供货范围，然后在各供 货范围内分别移动网点到其他备选地址上， 以使各供货范围内的总成本下降，找到各 供货范围内总成本最小的新网点设置地址， 再将新网点设置地址代替初始方案，重复 上述过程直至各供货范围内总成本不能再 下降时为止。
多元网点布局方法——启发式方法 启发式方法与最优规划方法的最大不 同是它不是精确式算法，不能保证给出的 解决方案是最优的，但只要处理得当，获 得的可行解与最优解是非常接近的，而且 启发式算法相对最优规划方法计算简单， 求解速度快。所以在实际应用中，启发式 方法是仅次于最优化规划技术的选址方法。
启发式方法——CFLP法
C
J i1
1j
X 1 j f 1,1
＝0＋1×4＋6×2＋7×3＋4×2＋10＝55 40＋10＝50 F1, 2 53＋10＝63 F1, 3 50＋10＝60 F1, 4 49＋10＝59 F1,5