excel建模教程

1、使用Excel规划求解解线性规划问题线性规划求解的基本知识线性规划模型由3个基本部分组成： 决策变量(variable) 目标函数(objective) 约束条件(constraint)示例：营养配方问题(问题)某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成，含有以下成份：超蚓海幡有嬲的豌费用(美元/褫)事白质聘玉米0.090.020.30大豆粉(1.360.90特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤维。该农场希望确定每天最小成本的饲料配制。（解答过程）因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为:x1=每天混合饲料中玉米的重量（磅）x2=每天

2、混合饲料中大豆粉的重量（磅）目标函数是使配制这种饲料的每天总成本最小，因此表示为：minz=0.3X1+0.9X2模型的约束条件是饲料的日需求量和对营养成份的需求量，具体表示为:x1+x28000.09X1+0.6X20.3(x1+x2)0.02X1+0.06X28000.21X1-0.3x20 x1,x20可以使用图解法确定最优解。下面，我们介绍使用Excel的规划求解加载项求解该模型使用Excel规划求解解线性规划问题步骤1安装Excel规划求解加载项单击“Office按钮一一Excel选项一一加载项一一（Excel加载项）转到，由现加载宏”对话框，如下图所示。选择规划求解加载项工单击确定

3、”。此时，在数据”选项卡中由现带有规划求解”按钮的分析”组，如下图所示步骤2设计电子表格使用Excel求解线性规划问题时，电子表格是输入和输由的载体，因此设计良好的电子表格，更加易于阅读。本例的电子表格设计如下图所示：KBCDEFGHI1营养配方模型2单元格公式1P3T112D5 -G5*1BS2=B00D8 =GMB412+U3jCtl27浅白膜含量21。,30仁0D12 =D5e纤雉含量0.03|,06009:二110.第出:,1E2E二2解决方亮1.其中，输入数据的单元格使用了阴影格式，即B5:C8和F6:F8;变量和目标函数单元格为B12:D12加上了粗线边框；D5:D8中输入了约束公

4、式，公式如上图中的右上角所示，其相应的代数表达式见上文。技巧：也可以在单元格D5中输入公式：=SUMPRODUCT(B5:C5,$B$12:$C$12)然后将其复制到下方相应的单元格中。步骤3应用规划求解工具单击数据一一分析一一规划求解:由现如下图所示的规划求解参数”对话框，设计相应的参数并且单击添加”按钮，添加相应的约束，如下图所示。确定取消法；林加帮助如=mt=bin注意，上图所示的约束中，添加了非负限制，即$B$12:$C$12=0o还可以在规划求解参数”对话框中，单击选项”按钮，在由现的规划求解选项”对话框中（如下图所示）添加非负约束，即选择采用线性模型”和假定非负”前的复选框，其余的默认值可以保持不变。当然，如果精度太高，可以调低精度，也获得满意的结果。ABCDEFJ12营养配方模型数据二3zlx2玉米的重量大豆粉的重量总计限制5目标0.30.9437.656日需求量11aoo.oo=EDO7蛋白质含量0.21-0.30.00=091011=0-o输出;xlZ12解决方案1470.59)329.41437.65为了增强可读性，还可以使用有描述性的Excel名称来代替单元格字母如果问题没有可行解，规划求解将会显示明确的信息规划求解找不到有用的解如果最优目标值是无界的，规划