生产计划问题最优解的EXCEL实现
摘 要:求解生产计划问题的最优解一般需要通过专门的运筹学软件进行,本文结合具体案例探讨了EXCEL下生产计划问题最优解的求解的实现过程,较为简捷、准确地得出了生产计划问题最优解的求解结果。
关键词:线性规划;生产计划问题;EXCEL;规划求解
文献标识码: A 中图分类号:F224.31
在工业领域用线性规划求解的典型问题有运输问题、生产计划问题、配套生产问题、下料和配料问题等。生产计划问题是指用m种资源生产n种产品,已知各种产品每生产一单位可得的利润和所需的各种资源的数量,以及各种资源的限额,问如何计划各种产品的生产量,使总的利润为最大?求解生产计划问题的最优解的过程较为复杂,一般需要通过专门的运筹学软件进行。本文结合具体案例探讨EXCEL下生产计划问题最优解求解的实现过程。
1应用案例[1]
永久机械厂生产三种产品,每种产品要经过A、B两道工序加工。设该厂有两种规格的设备能完成A工序,它们以A1、A2表示;有三种规格的设备能完成B工序,它们以B1、B2、B3表示,产品1可以在A、B任何一种规格设备上加工;产品2可在任何一种规格的A设备上加工,但完成B工序时只能在B1设备上加工;产品3只能在A2与B2 设备上加工。已知各种机床设备的单件工时,原材料单价,产品销售价格,各种设备有效台时以及满负荷操作时机床的设备费用,如表1所示,要求安排最优的生产计划,使厂利润最大。
第1步:对表1进行补充计算,得到单位台时设备费用与单件利润。
单位台时设备费用G3=F3/E3,向下复制到G7(见图1G1:G7);
单件利润B10=B9-B8,向右复制到D10(见图1A10:D10)。
第2步:设置三种产品的产量变量矩阵,赋初始值1(见图1B12:D16)。
第3步:确定A、B设备产量(见图1A17:D18)。
从案例条件知,各产品在A设备加工的产量应等于在B设备上加工的产量,因此有:
故设:产品1在A设备上加工的产品产量为B17=B12+B13,在B设备上加工的产品产量为B18=B14+B15+B16;同理,产品2为 C17=C12+C13,C18=C15;产品3为D17=D13,D18=D15。
第4步:设置设备有效台时约束公式。
H3 =SUMPRODUCT(B3:D3,B12:D12),向下复制到H7,得到各设备加工三种产品的总台时(见图1H1:H7)。
第5步:设置目标函数公式。
本案例目标函数为利润最大值,根据案例资料利润值宜分三步计算(见图1H9:H11):
(1)计算收入减原料费用,H9= SUMPRODUCT(B17:D17,B10:D10)
(2)计算设备费用,H10= SUMPRODUCT(G3:G7,H3:H7)
(3)计算利润值=(收入-原料费用)-设备费用,H11=H9-H10
第6步:利用EXCEL规划求解工具进行最优产量求解。
单击工具-规划求解,作如下规划求解参数设置(见图2):
(1)目标单元格为:H11,等于最大值;
(2)可变单元格为三种产品的产量变量矩阵:B12:D16;
(3)添加的约束条件包括:
H3:H7<=E3:E7(各设备加工台时小于等于设备有效台时)
B17:D17=B18:D18(各产品A设备产量等于B设备产量)
B12:D16=int(三种产品的产量变量矩阵为整数)
B12:B16>=0 (三种产品的产量变量矩阵>=0)
C15:C16=0,D12=0,D14=0,D16=0(不经过该工序加工,产量为0)
(4)单击求解得到求解结果,见图3。
三种产品在A、B设备加工的产量分别为1432件、500件、323件,在五道工序上的产量具体见图3中A11:D16,此时达到利润最大值为1146.39元(H11)。
比较EXCEL与运筹学专门软件的求解过程和求解结果,在EXCEL下通过补充计算单位台时设备费用与单件利润、设置出三种产品的产量变量矩阵、分步确定目标利润公式等前期准备,就能较为简捷地列示出目标函数与约束条件,本例中目标函数在EXCEL下为SUMPRODUCT(B17:D17,B10:D10)-SUMPRODUCT(G3:G7,H3:H7),其列示过程较原公式简单,原约束条件包括11个公式,在EXCEL下只需要5个公式;从求解结果看,二者的结果是一致的,且EXCEL的实现过程更为简捷。
参考文献:
[1]韩伯棠编著,管理运筹学[M]北京:高等教育出版社 2000 P46-48
[2] 郁玉环辅助生产费用代数分配法实际应用研究会计之友2006.5
[3] 郁玉环Excel“规划求解”在多方案组合排队投资决策中的应用中国管理信息化2010.3
推荐访问： 最优 计划 生产 EXCEL
