用Excel进行求解最佳配送方案

2017-12-11

[尊重原创,转载请注明出处,本文链接  ]

上次分享了如何通过Excel制定最优生产计划,这次,我们继续介绍使用Excel来求解最佳配送方案。

  1. 待求解问题

    某公司从事饮品生产业务,分别由辽宁、山东、新疆三个不同地区的工厂生产,运往北京、上海、广州、成都4个城市销售。假设

    1. 各地区的产能情况:
产地 产能(箱)
辽宁 6000
山东 5000
新疆 12000
  1. 各城市的需求情况:
城市 需求量(箱)
北京 5000
上海 6000
广州 8000
成都 4000
  1. 从产地运往各城市的运输费用单价(即每箱的运输费用,单位:元):
产地 北京 上海 广州 成都
辽宁 10 22 35 40
山东 8 12 28 36
新疆 30 40 50 40

基于以上信息,为了使总的运输成本最小化,应该如何制定运输方案?

  1. 使用Excel进行求解
    1. 首先整理已知数据,在Excel中建立运算表,并定义公式

  • 表4中,B17:E19中分别代表从不同产地到不同目标城市的运输量,在区域B20:E20中定义公式计算各个城市的总采购量,例如B20=SUM(B17:B19);
  • 在区域F17:F19中定义公式计算各个产地的总运输量,例如F17=SUM(B17:E17)
  • 在单元格F20中定义公式,用表3中的运输费用单价分别与表4中的对应运输量计算乘积,然后汇总,得到总运输费用。例如:F20=B11*B17+B12*B18+… …+E13*E19,这个公式写起来非常繁琐,我们可以采用一个Excel内置的积和函数来简化,直接获得结果,例如F20=SUMPRODUCT(B11:E13, B17:E19)。
  1. 以上完成了运算表的准备工作,下面进行求解,首先打开规划求解对话框。(如果对于一些基础的Excel操作还不了解,建议参考一下上一篇《使用Excel的规划求解求得最优生产计划


  • 设置目标为总运费,并选择”最小值”;
  • 设置可变单元格区域为$B$17:$E$19,即每个产地到每个城市的运输量;
  • 设置约束条件:

    约束1:每个产地的运输量不大于其产能,即:


    约束2:每个城市的采购量不小于其需求量


    约束3:所有运输量为整数


  • 运行求解,结果如下表所示:


    至此,我们得出每个产地到每个城市的最优运输方案,及预计的总运费。

  1. 总结

    对于饮品行业,运费在成本中占据了相当大的比重,因此优化运输方案对于控制企业成本有着相当重要的意义。

本站今后会陆续推出Excel用来求解业务问题的系列文章。敬请关注。

没有评论

发表评论

电子邮件地址不会被公开。 必填项已用*标注