用MicrosoftExcelSolver对线性规划求解.docVIP

用Microsoft Excel Solver对线性规划求解 为了通过Excel Solver来求解数学规划，我们首先安装Excel中的“规划求解”功能（方法是插入源安装光盘，打开Excel，选定“工具”“加载宏”“规划求解”来安装“规划求解”功能）。 例2.24 AB公司在一周内只生产两种产品：产品A，产品B。产品A的价格是每吨25美元，产品B的价格是每吨10美元。管理部门必须决定每种产品各生产多少吨，才能使收益最好。产品A 和产品B是由多种材料混合而成。可供这一周使用的三种原料数量如下。 原料1：12000吨；原料2：4000吨；原料3：6000吨。产品A由60%的原料1和40%的原料2组成。产品B由50%的原料1，10%的原料2和40%的原料3组成。假设产品A和产品B各生产X1、X2吨，则数学模型如下： max S =25x1+10x2 s.t. 0.6x1+0.5x2(12000 0.4x1+0.1x2(4000 0.4x2(6000 x1,x2(0 解：将问题输入电子数据表：C5、C6 分别对应于变量X1、X2，目标函数系数在B5、B6单元表示，目标函数值在C3单元被计算出来（运用公式，目标函数值=B5*C5+B6*C6），注意决策变量值被初始化为1，这样通过程序计算得到一个结果，但是可使用其他任何一个初始变量值（如图2-6）。 约束条件被表示在第9行到第11行，B列写明了可用资源量，C列注明了每种资源在当前解的情况下各自的使用量。我们可以用这个电子表，通过改变C5和C6单元的值来寻找C3单元的最大值，同时确保C9、C10、和C11的单元格的值相应的不超过B9、B10和B11的值。 1．选定“工具”弹出下拉菜单，双击“规划求解”，弹出参数表（如图2-7）； 2．选定目标函数类型，输入约束条件，并求解（如图2-8）； 3．保存方案运算结果（如图2-9）； 4．报告可以提供两种有用报告中任何一种：求解结果报告和灵敏度分析报告。（如图2-9、如图2-10）。 图2-6 图2-7 图2-8 图2-9 图2-10 图2-11 例2.25 某人有一笔50万元的资金可用于长期投资，可供选择的投资机会包括购买国库券、公司债券、投资房地产、购买股票或银行保值储蓄等。不同的投资方式的具体参数如下表。投资者希望投资组合的平均年限不超过5年，平均的期望收益率不低于13%，风险系数不超过4，收益的增长潜力不低于10%。问在满足上述要求的前提下投资者该如何选择投资组合使平均年收益率最高？ 表2-27 序号 投资方式 投资期限（年） 年收益率% 风险系数 增长潜力% 1 国库券 3 11 1 0 2 公司债券 10 15 3 15 3 房地产 6 25 8 30 4 股 票 2 20 6 20 5 短期存款 1 10 1 5 6 长期储蓄 5 12 2 10 7 现金存款 0 3 0 0 解：设xi为第i种投资方式在总投资额中的比例，则模型如下： Max S =11x1+15x2+25x3+20x4+10x5+12x6+3x7 s.t. 3x1+10x2+6x3+2x4+x5+5x6(5 11x1+15x2+25x3+20x4+10x5+12x6+3x7(13 x1+3x2+8x3+6x4+x5+2x6(4 15x2+30x3+20x4+5x5+10x6(10 x1+x2+x3+x4+x5+x6+x7=1 x1，x2，x3，x4，x5，x6，x7(0 模型的目标函数反映的是平均收益率最大，前四个约束分别是对投资年限、平均收益率、风险系数和增长潜力的限制。最后一个约束是全部投资比例的总和必须等于1。 最优解：X1=0.57143，X3=0.42857。平均年收益率=17%。即：投资国库券=0.57143*50=29万元，投资房地产=0.42857*50=21万元，投资年限=4.28571年，平均年收益率=17%，风险系数=4，增长潜力=12.8571%。 图2-12 图2-13 图2-14 例2.26 营养配餐问题 假定一个成年人每天需要从食物中获得3000千卡的热量、55克蛋白质和800毫克的钙。如果市场上只有四种食品可供选择，它们每千克所含的热量和营养成分和市场价格见下表。问如何选择才能在满足营养的前提下使购买食品的费用最小？ 各种食物的营养成分表 表2-28 序号 食品名称 热量(千卡) 蛋白质(克) 钙(毫克) 价格(元) 1 猪肉 1000 50 400 14 2 鸡蛋 800 60 200 6 3 大米 900 20 300 3 4 白菜 200 10 500 2 解：设xj为第j种食品每天的购入量，则配餐问题的线性规划模型为： min S=14x1+6x2 +3x3+2x4 s.t.1000x1+800x2