规划求解

《经济与管理应用软件》Excel-规划求解Excel-规划求解例1：（产品组合问题）一个工厂接了一批鼠标、键盘的订单，用现在的设备来生产，鼠标每个1分钟，键盘第个1.5分钟，1个鼠标的毛利是50元，1个键盘的毛利是75元，成本价鼠标为15元，键盘为20元，鼠标每日要生产最少200个，一天成本控制在10000元以下，每天10小时，这个工厂每天生产多少个鼠标、多少个键盘才能赚到最大的利润？Excel-规划求解•步骤1、首先在Excel表中输入如下内容：其中“计划产量”中的值是自己随便输入的初始值。最后3行是公式。总时间：各自产量*各自单位时间；总成本：各自产量*各自成本，然后相加求和；总利润：各自产量*各自单位毛利，然后相加求和；Excel-规划求解•步骤2、设定规划求解参数。工具-规划求解。（如果没有，则工具—加载宏，选择规划求解），设置参数如下图：其中：“设置目标单元格”是所求的最大利润；可变单元格是鼠标键盘的各自计划产量，即通过改变产量搭配，以实现在满足约束条件情况下得到最大利润；几个约束条件的解释：1)、鼠标、键盘的各自生产总时间不超过10小时（600分钟）；2)、总成本不超过最大成本10000；3)、鼠标产量不小于200；点击“选项”，在弹出窗口中勾选“采用线性模型”和“假定非负”，然后单击“确定”。Excel-规划求解•步骤3、设置完成后，点击“求解”，规划求解将计算出一个最佳解决方案（如果有）。•本题中，求得的结果是：计划产量：鼠标200，键盘350；最大利润：36250。Excel-规划求解例2：（营养配方问题）某农场每天至少使用800磅特殊饲料。这种特殊饲料由玉米和大豆粉配制而成，含有以下成份：特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤维。该农场希望确定每天最小成本的饲料配制。Excel-规划求解•因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为：x1=每天混合饲料中玉米的重量（磅）x2=每天混合饲料中大豆粉的重量（磅）•目标函数是使配制这种饲料的每天总成本最小，因此表示为：minz=0.3×x1+0.9×x2•模型的约束条件是饲料的日需求量和对营养成份的需求量，具体表示为：x1+x2≥8000.09×x1+0.6×x2≥0.3(x1+x2)0.02×x1+0.06×x2≤0.05(x1+x2)Excel-规划求解•将上述不等式化简后，完整的模型为：minz=0.3×x1+0.9×x2s.t.x1+x2≥8000.21×x1-0.3×x2≤00.03×x1-0.01×x2≥0x1,x2≥0•可以使用图解法确定最优解。下面，我们介绍使用Excel的规划求解加载项求解该模型。Excel-规划求解•步骤1：设计电子表格•其中，输入数据的单元格使用了阴影格式，即B5:C8和F6:F8；•变量和目标函数单元格为B12:D12，加上了粗线边框；D5:D8中输入了约束公式，公式如上图中的右上角所示，其相应的代数表达式见上文。技巧：也可以在单元格D5中输入公式：=SUMPRODUCT(B5:C5,$B$12:$C$12)然后将其复制到下方相应的单元格中。Excel-规划求解•步骤2:应用规划求解工具单击“数据——分析——规划求解”，出现如下图所示的“规划求解参数”对话框，设计相应的参数。Excel-规划求解•并且单击“添加”按钮，添加相应的约束，如下图所示。•注意，上图所示的约束中，添加了非负限制，即$B$12:$C$12=0。•还可以在“规划求解参数”对话框中，单击“选项”按钮，在出现的“规划求解选项”对话框中添加非负约束，即选择“采用线性模型”和“假定非负”前的复选框，其余的默认值可以保持不变。当然，如果精度太高，可以调低精度，也获得满意的结果。Excel-规划求解Excel-规划求解•步骤3求解设置好参数后，单击“规划求解参数”对话框中的“求解”按钮，结果如下图所示。Excel-规划求解•为了增强可读性，还可以使用有描述性的Excel名称来代替单元格字母。•如果问题没有可行解，规划求解将会显示明确的信息“规划求解找不到有用的解”。•如果最优目标值是无界的，规划求解将会显示不太明确的信息“设置目标单元格的值未收敛”。•这些情况都表明模型构造的公式有错误。Excel-规划求解•例3：（运输问题）表中数字为单位运费。问：如何组织运输可使得运费最省？销地产地甲乙丙丁产量137645224322343853销量3322Excel-规划求解•例4：（指派问题）有4个工人，要指派他们分别完成4项工作，每人做各项工作所消耗的时间如下表。问指派哪个人去完成哪项工作，可使得总的消耗时间为最小？工作工人ABCD甲15182124乙19232218丙26171619丁19212317