|
hi，大家好我们来聊一聊如何用Excel中的规划求解功能，来完成工作中遇到的，多变量求最优解的复杂问题。 一、模拟场景：制造型企业原材料、工时安排在不同产品上的产量多少，能使得企业的利润最大化某制造企业A、B、C三种原料生产P1、P2、P3三种产品。每生产1件P1产品需要A、B、C的数量为3，4，2公斤，生产1件P2的数量为4，2，1公斤，生产1件P3的数量为1，1，0.5公斤。生产1件P1、P2、P3产品，所用工时分别为10、8、4小时，工厂现有A、B、C三种原料的数量分别为2800，2700，1100公斤，工时最大投入6600小时。请问：在三种产品最低不能小于200件的情况下，如何安排生产P1、P2、P3的产量能获利最大？3、要建立利润与产量之间的建模关系，还需要补充各产品对应的利润和公司整体的固定成本，即总利润=各产品边际利润-固定成本（最终结果在模型中展示）；4、各产品边际利润、固定成本可以从以往数据来获取，即基期数据（需要在模型中构建）；5、模拟场景中有设置各产品原材料的最大消耗量，和工时也就是人员的最大上限，可以看做是约束条件（约束条件需要在模型中构建）；内容解读完毕，很容易列示出建模公式，但是由于变量与约束条件较多，导致计算较为复杂（见图1），规划求解功能可以轻松解决多元函数求解问题。 ▲图1:模型17-建模公式
1、展示：模型分①基期数据部分，②规划求解部分，③基期和规划后量对比三部分，其中模型中灰色部分数据需要自行填列，规划求解模型已经建立，仅需要单击重新计算按钮可完成操作（见图2）▲图2:模型17-1—多产品多变动因素下求利润最大线性规划求解(宏)2、因模型17-1为简化规划求解的重复，加入了宏（重新计算按钮），但是从多版本测试中来看执行不顺畅，所以增加一个手工调整规划求解功能版本，在修改灰色部分后，可以手工操作一遍规划求解功能，其他操作都一致（见图3） ▲图3:模型17-2—多产品多变动因素下求利润最大线性“规划求解”3、怎么将约束条件，也就是利润最大、原材料、工时不能超过最大、产量不低于200，产量必须是整数这些条件，录入到规划求解功能中，详见图4 四、建模后的思考 1、这个模型，更适用于市场比较稳定的情景，销售价格相对稳定，市场需求稳定，产量可以对销量有主导性，如果非这样的情景需要辩证使用，用此模型算出最优产品结构，与现在的销售结构对比，指导销售产品结构朝着更有利的方向调整
2、规划求解功能还可以应用于多种场景中，比如用工的最优安排、选址的最优解等等，大家可以结合现状自行设计。
|
