用EXCEL庇腠格解运输问题 .pptVIP

《物流工程 》课程研究性学习交流会 题目:用EXCEL表格解运输问题 研究小组成员:(工业工程041班) 洪 斌 (049094004) 金晓雨 (049094007) 刘正道 (049094017) 吕 浩 (049094019) 管 理 科 学 与 工 程 学 院 2007年12月19日 用EXCEL表格解运输问题 　　　在经济建设中，经常碰到大宗物资调运问题．例如煤、钢材、木材、粮食等物资，在全国有若干生产基地，根据已有的交通网，应如何制定调运方案，将这些物资运到各消费地点，而使运费最低。 　　　在现实当中，这是我们所要解决的问题。解运输问题，有许多种方法，如表上作业法、图与网络分析、 EXCEL等。 　　　下面着重介绍一下怎样用EXCEL表格来求解运输问题。 　　　在人们的实践中，经常出现各种运输活动。运输活动一般都有若干个发货地点，简称产地；有若干个收货地点，简称销地；各产地各有一定的可供货量，简称产量；各销地各有一定的需求量，简称销量。那么，运输问题就是要在买足各销地的需求与产地产量平衡的前提下，如何组织调运才能使总的运输费用达到最低。 　　　运用EXCEL的规划求解进行优化分析的步骤： 　　　一、根据题意，设置本问题的决策变　　　　　　　　　　　　　量和目标函数 。 　　　二、根据题意及决策变量与目标函数得出本问题的线性规划模型 。 　　　 三、根据上述约束条件构建EXCEL模型 。 　　　四、根据上述规划模型进行规划求解参数设置 。 　　　五、规划求解结果。 　　　下面通过产销平衡一个实例来运用EXCEL的规划求解功能进行运输问题的分析。 　　　例：某地区有A1，A2，A3三个铁矿，每天要把生产的铁矿石运往B1，B2，B3，B4四个炼铁厂。各矿的产量、各厂的销量（百元/天）以及各厂矿间的运价如表1所示。问应如何组织调运才能达到产销平衡并使总运费最少？ 　　 解： 　１、根据题意，设置本问题的决策变量和目标函数。 　　　设：Xij为每天从Ai矿运往Bj厂的矿石数量（百吨），Y为总运费，由表1及变量可以得出总运费Y=6＊X11+3＊X12+2＊X13+5＊X14+7＊X21+5＊X22+8＊X23+4＊X24+3＊X31+2＊X32 +9＊X33+7＊X34 ， 则本问题的目标函数为求minY 。 ２、根据题意及决策变量与目标函数得出本问题的线性规划模型。 目标函数： Y=6＊X11+3＊X12+2＊X13+5＊X14+7＊X21+5＊X22+8＊X23+4＊X24+3＊X31+2＊X32 +9＊X33+7＊X34 约束条件: X11+ X12+ X13+ X14=5（满足A1矿的产量）??? ??????????????X21+ X22+ X23+ X24=2（满足A2矿的产量）??????? ??????????X31+ X32+ X33+ X34=3（满足A3矿的产量）??????????? ??????X11+ X21+ X31 =2（满足B1厂的需求量）??????????????? ??X12+ X22 +X32 =3（满足B2矿的需求量）?????????? ???????X13+ X23 +X33 =1（满足B3矿的需求量）?????????????? ???X14+ X24 +X34 =4（满足B4矿的需求量）?????????????? Xij =0(i=1,2,3,j=1,2,3,4)(决策变量非负约束) 3、根据上述约束条件构建EXCEL模型，如下图 　　其中单元格B4：E4分别为决策变量X11，X12，X13，X14 所在单元格；B6：E6分别为决策变量X21，X22，X23，X24 所在单元格；B8：E8分别为决策变量X31，X32，X33，X34 所在单元格；B11单元格为实际运价所在单元格，其公式:　　　“=SUMPRODUCT(B3:E3,B4:E4)+SUMPRODUCT(B5:E5,B6:E6)+SUMPRODUCT(B7:E7,B8:E8)” ; F4=SUM(B4:E4); F6=SUM(B6:E6); F8=SUM(B8:E8); B9=SUM(B4+B6+B8); C9=SUM(C4+C6+C8); D9=SUM(D4+D6+D8); E9=SUM(E4+E6+E8)