Excel规划求解在运输规划中的应用 专题

Excel规划求解在运输规划中的应用专题
规划求解是Excel的一个非常有用的工具，不仅可以解决运筹学、线性规划等问题，还可以用来求解线性方程。

【例】某公司拥有两个处于不同地理位置的生产工厂和五个位于不同地理位置的客户，现在需要将产品从两个工厂运往五个客户。

已知两个工厂的最大产量均为60000，五个客户的需求总量分别为30000、23000、15000、32000、16000，从各工厂到各客户的单位产品运输成本如表1所示，请用EXCEL的规划求解功能计算出使总成本最小的运输方案。

表1 单位产品运输成本
客户1 客户2 客户3 客户4 客户5
工厂1 1.75 2.25 1.50 2.00 1.50
工厂2 2.00 2.50 2.50 1.50 1.00 问题分析：一个公司需要将产品从两个不同的工厂运往五个不同的客户，每个工厂只有有限的供给，而且每个客户有确定需求量，那么如何运输才能使运输总成本最小？
解答过程：
（一）、根据已知条件建立EXCEL表格模型，如图1所示：
图1
说明：
1.B9:F10单元格区域是变量区域（可变单元格），变量区域的初始值全部设置为
2.B11单元格为客户1收到的来自两个工厂的产品数合计，公式为
“=SUM(B9:B10)”
3.G9单元格为工厂1出货的合计，公式为“=SUM(B9:F9)”
4.C13单元格为运输总成本，公式为“=SUMPRODUCT(B3:F4,B9:F10)”
（二）、加载宏
工具→加载宏→规划求解→确定
注意：如果Office软件没有安装完整，是不能加载和使用规划求解宏的，这种情况下建议卸载现有版本，重新完全安装Office。

（三）、设定规划求解参数和求解
1.工具→规划求解
2.设置求解参数，如图2所示：
图2
【目标单元格】：即放置运输总成本运算结果的单元格
【等于】：这里求最小值
【可变单元格】：可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格，即变量区域，本例中各工厂到各客户的运量为变量。

【约束条件】：
（1）非负约束：由于产品的运输数量不能是负数，故必须有此约束，这是一个隐含条件，否则求解结果不正确（即图2对话框中的第二个约束）
（2）其他约束：各客户接受的产品数量>=需求量（即图2对话框中的第一个约束）；各工厂的出货总量<=工厂产能（即图2对话框中的第三个约束）
3.点击“求解”，即可看到运算出的优化运输方案和运输总成本，如图3所示：
图3
【练习】
某商品混凝土公司在城市的不同位置修建了三个搅拌站生产混凝土，三个搅拌站的最大产量分别为2000t、2500t、2200t，这三个搅拌站要为城市的六个建筑工地供应混凝土，六个建筑工地的需求量分别为800t、750t、600t、780t、820t、670t，已知各搅拌站到各工地的单位运输成本如表3所示（单位：元/吨），请用EXCEL计算出能使运输总成本最低的优化运输方案。

表3
工地1 工地2 工地3 工地4 工地5 工地6 搅拌站1 8.9 8.4 7.9 8.8 8.6 9.1
搅拌站2 7.5 7.6 8.5 8.7 7.6 9.3。