excel求解精确重心法说课讲解

e x c e l求解精确重心

法

精确重心法 问题描述

假设有一系列点代表生产地和需求地，各自有一定量货物需要以一定的运输费率运向待定的仓库，或从仓库运出，现在要确定仓库应该位于何处才能使总运输成本最小？这是一类单设施选址问题，精确重心法是求解这类问题最有效的算法之一。

我们以该点的运量乘以到该点的运输费率再乘以到该点的距离，求出上述乘积之和最小的点，即：

n

i i i i 1min TC V R d ==∑

其中：TC ——总运输成本

i V ——i 点的运输量 i R ——到i 点的运输费率

i d ——从位置待定的仓库到i 点的距离

i d =其中k 代表一个度量因子，将坐标轴上的一单位指标转换为通用的距离单位，如英里等。

传统的精确重心法是对上述目标函数求偏微分，然后再使用迭代的方法，计算过程繁琐，在这里我们使用excel 软件求解。

算例：假设有两个工厂向一个仓库供货，由仓库供应三个需求中心，工厂一生产A 产品，工厂二生产B 产品。工厂和需求地的坐标、货运量和运输费率见表1，k 值取10。

表一 工厂和需求地的坐标、货运量和运输费率

地点产品总运输量

（担）

运输费率

（美元/担/英

里）

坐标值

X Y

工厂一 A 2000 0.05 3 8

工厂二 B 3000 0.05 8 2

需求地一A、B 2500 0.075 2 5

需求地二A、B 1000 0.075 6 4

需求地三A、B 1500 0.075 8 8

第一步：建立excel模型，输入已知数据，如图1所示

第二步：在第一步基础上，利用excel提供的函数，分别求出各个地点到仓库的运输成本和总成本。如图2（1）和2（2）所示

第三步：用excel的“规划求解”工具求解。点击“工具”菜单，选择“规划求解”（如果没有此菜单，选择“工具——加载宏”，选择加载“规划求解”即可。），此时出现一个“规划求解参数”对话框，如图3所示。在此对话框中输入“规划求解”

的参数，其中目标单元格为$J$10,目标函数求的是最小值，可变单元格为

$D$5、$E$9，即仓库坐标值x和y所在的单元格。最后点击“求解”按钮求解。

第四步：保存计算结果。计算机计算完成后将会提示是否将结果保存，点击“确定”保存结果。本算例的求解结果如图4所示。求得的仓库最优坐标值为

（4.910，5.058），总运输成本为21425.136美元。

分析：（1）在上述excel模型中，仓库坐标值的初始值不能为0，即D5和E5单元格均不能为0。

（2）excel的“规划求解”实际上包含一个非线性优化模块，对于求解小型问题很有效。