excel建模教程.docx
- 文档编号:27337945
- 上传时间:2023-06-29
- 格式:DOCX
- 页数:5
- 大小:152.53KB
excel建模教程.docx
《excel建模教程.docx》由会员分享,可在线阅读,更多相关《excel建模教程.docx(5页珍藏版)》请在冰豆网上搜索。
excel建模教程
使用Excel规划求解解线性规划问题
线性规划求解的基本知识
线性规划模型由3个基本部分组成:
∙决策变量(variable)
∙目标函数(objective)
∙约束条件(constraint)
示例:
营养配方问题
(问题)某农场每天至少使用800磅特殊饲料。
这种特殊饲料由玉米和大豆粉配制而成,含有以下成份:
特殊饲料的营养要至少30%的蛋白质和至多5%的纤维。
该农场希望确定每天最小成本的饲料配制。
(解答过程)
因为饲料由玉米和大豆粉配制而成,所以模型的决策变量定义为:
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)
将上述不等式化简后,完整的模型为:
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加载项)转到”,出现“加载宏”对话框,如下图所示。
选择“规划求解加载项”,单击“确定”。
此时,在“数据”选项卡中出现带有“规划求解”按钮的“分析”组,如下图所示。
步骤2 设计电子表格
使用Excel求解线性规划问题时,电子表格是输入和输出的载体,因此设计良好的电子表格,更加易于阅读。
本例的电子表格设计如下图所示:
其中,输入数据的单元格使用了阴影格式,即B5:
C8和F6:
F8;变量和目标函数单元格为B12:
D12,加上了粗线边框;D5:
D8中输入了约束公式,公式如上图中的右上角所示,其相应的代数表达式见上文。
技巧:
也可以在单元格D5中输入公式:
=SUMPRODUCT(B5:
C5,$B$12:
$C$12)
然后将其复制到下方相应的单元格中。
步骤3 应用规划求解工具
单击“数据——分析——规划求解”,出现如下图所示的“规划求解参数”对话框,设计相应的参数。
并且单击“添加”按钮,添加相应的约束,如下图所示。
注意,上图所示的约束中,添加了非负限制,即$B$12:
$C$12>=0。
还可以在“规划求解参数”对话框中,单击“选项”按钮,在出现的“规划求解选项”对话框中(如下图所示)添加非负约束,即选择“采用线性模型”和“假定非负”前的复选框,其余的默认值可以保持不变。
当然,如果精度太高,可以调低精度,也获得满意的结果。
步骤4 求解
设置好参数后,单击“规划求解参数”对话框中的“求解”按钮,结果如下图所示。
为了增强可读性,还可以使用有描述性的Excel名称来代替单元格字母。
如果问题没有可行解,规划求解将会显示明确的信息“规划求解找不到有用的解”。
如果最优目标值是无界的,规划求解将会显示不太明确的信息“设置目标单元格的值未收敛”。
这些情况都表明模型构造的公式有错误。
当然,规划求解工具还可以得出更详细的报告,这些功能我们在以后讨论。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- excel 建模 教程