使用Excel的规划求解求得最优生产计划

2017-12-07

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

最近几年,大数据、机器学习等词语被炒得如火如荼,但是技术是为生产服务的。脱离了实际应用场景谈大数据没有太大的价值。殊不知,其实Excel也可以解决很多数据挖掘问题,了解了如何通过数学工具去解决实际问题,那么使用任何工具(如Python、R、SPSS)都是相同的道理。下面就是通过一个简单的模型来计算最优生产计划问题,弄懂了可以直接应用到实际生产中,无非需要加入更多的因素和约束条件而已。

一、待求解问题

假设需要生产A、B、C三种产品,每种产品使用3种原料(木材、铁、粮食),有如下前提条件:

  1. 每个产品消耗的原材料如下:

    木材 粮食
    产品A 8 2 3
    产品B 7 7 1
    产品C 3 5 5

     

  2. 每种产品的单位收益如下:

    单位收益
    产品A 12
    产品B 20
    产品C 16

     

  3. 原材料的可用量有如下限制:

    木材 粮食
    可用量 10000 10000 10000

     

在以上条件下,为了实现最大收益,每种产品需要生产多少?

二、使用Excel的规划求解获得最优解

带着这样的问题,我们在Excel中建立如下数据表,

  • 首先填入已知数据:
    1. 每种产品及每种原材料消耗量;
    2. 每种产品收益;
    3. 每种原材料的可用量

  • 填入计算公式:
    1. 在B10:D12区域定义公式,计算每种产品每种原材料的总消耗量,即单位产品消耗的原材料数量乘以该产品的生产量,例如:B10=B3*F3
    2. 在E10:E12区域定义公式,计算每种产品的总收益,即单位产品收益乘以该产品的生产量,例如:E10=E3*F3
    3. 在B13:B16区域定义公式,计算每种原材料的总消耗量,以及总收益。例如:总收益E13=SUM(E10:E12)
  • 运行规划求解
    1. 打开数据工具栏


    2. 打开规划求解


      注:如果找不到此选项,可以到Excel加载项中添加,具体操作不在此赘述。

    3. 规划求解参数对话框如下图所示(此处为Excel2016版本,其他版本可能会有差别):


    4. 设置目标为总收益$E$13,并选择最大值(” $”符号代表单元格的绝对坐标值),目标可以理解为函数y,且y=f(x1,x2,x3)


    5. 设置可变单元格为$F$3:$F$5,可变单元格可以理解为自变量x1,x2,x3


    6. 设置约束条件


      原材料木材的使用量不大于10000,例如:


      同理设置铁和粮食的使用量约束条件,完成后如下图所示:


      也可以使用一种简便的方式来批量设置一个区域数据的约束条件,两者效果相同,例如:


      确定后如下图所示:


    7. 设置结果为非负数


    8. 单击求解,结果如下图所示:


    9. 我们注意到,计算出来的生产量包含小数,这不符合实际生产中的状况。所以我们可以再增加一项约束条件,即要求所有的生产量为整数:


    10. 重新求解结果如下,红框中的生产量即为所求。

三、最后,将求解问题的流程做个总结

  • 将问题公式化
    1. 明确应该最优化的量(最大化或最小化),然后用几个变量将其用公式 表示。这个公式叫做目标函数
    2. 明确需要满足的约束条件,然后用变量分别将这些约束条件用公式表示。这些公式叫做约束条件
  • 解决问题的方法
    1. 使用规划求解时,在Excel中设定目标函数和约束条件
    2. 运行规划求解,求最优解

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

没有评论

发表评论

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