excel中单变量求解、、模拟运算规划求解问题.doc

模拟运算1、 例如，用户准备贷款100000万元买房，年数是10年，想看看在不同利率下每个月应偿还的贷款金额。 步骤如下：（1）设计模拟运算表结构，如图2-62所示。图2-62 单变量模拟运算表（2）在单元格B4中输入公式“=PMT(A4/12,5*12,B1)”。（3）选取包括公式和需要进行模拟运算的单元格区域A4:B13。（4）单击【数据】菜单，选择【模拟运算表】项，弹出【模拟运算表】对话框，图2-63 【模拟运算表】对话框（5）由于本例中引用的是列数据，故在【输入引用列的单元格】中输入“$A$4”。单击【确定】按钮，即得到单变量的模拟运算表，如图2-62所示。2、单变量求解某企业拟向银行以7%的年利率借入期限为5年的长期借款，企业每年的偿还能力为100万元，那么企业最多总共可贷款多少？设计如图2-64所示的计算表格，在单元格B2中输入公式“=PMT(B1,B3,B4)”，单击【工具】菜单，选择【单变量求解】项，则弹出【单变量求解】对话框，如图2-65所示，在【目标单元格】中输入“B2”，在【目标值】中输入“100”，在【可变单元格】中输入“$B$4”，然后单击【确定】按钮，则系统立即计算出结果，如图2-64所示，即企业最多总共可贷款410.02万元。图2-64 贷款总额计算 图2-65 【单变量求解】对话框3、规划求解【例2-15】某企业在某月份生产甲、乙两种产品，其有关资料如图2-66所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润？利用规划求解工具求解这个问题的步骤如下：图2-66 产品有关资料及优化结果（1）首先建立优化模型，（设x和y分别表示甲产品和乙产品的生产量）：目标函数：max销售利润= (14060)x + (180100)y约束条件：6x + 9y 3607x + 4y 24018x + 15y 850y 30x 0, y 0，且为整数（2）单元格B11和C11为可变单元格，分别存放甲、乙产品的生产量。（3）单元格B12为目标单元格（销售利润），计算公式为“=SUMPRODUCT(B4:C4-B5:C5,B11:C11)”；（4）在单元格B14中输入产品消耗工时合计计算公式“=SUMPRODUCT(B6:C6,B11:C11)”。在单元格B15中输入产品消耗材料合计计算公式“=SUMPRODUCT(B7:C7,B11:C11)”，在单元格B16中输入产品消耗能源合计计算公式“=SUMPRODUCT(B8:C8,B11:C11)”。（5）单击【工具】菜单，选择【规划求解】项，则系统弹出【规划求解参数】对话框，如图2-67。图2-67 【规划求解参数】对话框（6）在【规划求解参数】对话框中，【设置目标单元格】中输入“$B$12”；【等于】选“最大值”；【可变单元格】中输入“$B$11:$C$11”；在【约束】中添加以下的约束条件：“$B$11:$C$11=整数”、“$B$11:$C$11=0”、“$B$14=$E$3”、“$B$15=$E$4”、“$B$16=$E$5”、“$B$11=$C$9”；这里，添加约束条件的方法是：单击【添加】按钮，系统会弹出【添加约束】对话框，如图2-68所示，输入完毕一个约束条件后，单击【添加】按钮，则又弹出空白的【添加约束】对话框，再输入第二个约束条件。当所有约束条件都输入完毕后，单击【确定】按钮，则系统返回到【规划求解参数】对话框。（8）在建立好所有的规