excel建模教程 _原创精品文档.pdfVIP

excel建模教程

使用Excel规划求解解线性规划问题

线性规划求解的基本知识

线性规划模型由3个基本部分组成:

•决策变量(variable)

•目标函数(objective)

•约束条件(constraint)

示例:营养配方问题

(问题)某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米与大豆粉配制而成,含有以下成份:

特殊饲料的营养要求就是至少30%的蛋白质与至多5%的纤维。该农场希望确定每天最小成本的饲

excel建模教程

料配制。

(解答过程)

因为饲料由玉米与大豆粉配制而成,所以模型的决策变量定义为:

x1=每天混合饲料中玉米的重量(磅)

x2=每天混合饲料中大豆粉的重量(磅)

目标函数就是使配制这种饲料的每天总成本最小,因此表示为:

minz=0、3×1+0、9×2

模型的约束条件就是饲料的日需求量与对营养成份的需求量,具体表示为:

x1+x2≥800

0、09×1+0、6×2≥0、3(x1+x2)

0、02×1+0、06×2≤0、05(x1+x2)

excel建模教程

将上述不等式化简后,完整的模型为:

minz=0、3×1+0、9×2

s、t、x1+x2≥800

0、21×1-0、3×2≤0

0、03×1-0、01×2≥0

x1,x2≥0

可以使用图解法确定最优解。下面,我们介绍使用Excel的规划求解加载项求解该模型。

使用Excel规划求解解线性规划问题

步骤1安装Excel规划求解加载项

单击“Office按钮——Excel选项——加载项——(Excel加载项)转到”,出现“加载宏”对话框,如下图

所示。选择“规划求解加载项”,单击“确定”。

excel建模教程

此时,在“数据”选项卡中出现带有“规划求解”按钮的“分析”组,如下图所示。

excel建模教程

步骤2设计电子表格

使用Excel求解线性规划问题时,电子表格就是输入与输出的载体,因此设计良好的电子表格,更加易

于阅读。本例的电子表格设计如下图所示:

excel建模教程

其中,输入数据的单元格使用了阴影格式,即B5:C8与F6:F8;变量与目标函数单元格为B12:D12,

加上了粗线边框;D5:D8中输入了约束公式,公式如上图中的右上角所示,其相应的代数表达式见上

文。

技巧:也可以在单元格D5中输入公式:

=SUMPRODUCT(B5:C5,$B$12:$C$12)

然后将其复制到下方相应的单元格中。

步骤3应用规划求解工具

单击“数据——分析——规划求解”,出现如下图所示的“规划求解参数”对话框,设计相应的参数。

excel建模教程

并且单击“添加”按钮,添加相应的约束,如下图所示。

excel建模教程

注意,上图所示的约束中,添加了非负限制,即$B$12:$C$12=0。还可以在“规划求解参数”对话框

中,单击“选项”按钮,在出现的“规划求解选项”对话框中(如下图所示)添加非负约束,即选择“采用线性

模型”与“假定非负”前的复选框,其余的默认值可以保持不变。当然,如果精度太高,可以调低精度,也获

得满意的结果。

excel建模教程

步骤4求解

设置好参数后,单击“规划求解参数”对话框中的“求解”按钮,结果如下图所示。

excel建模教程

为了增强可读性,还可以使用有描述性的Excel名称来代替单元格字母。

如果问题没有可行解,