第5章
数据分析高级应用






第5章
案例导读


Excel 2016提供了强大的数据分析工具，相比于其他数据分析工具，具有更好的易用性，也更易于学习。Excel 2016可以作为数据分析的入门级基础工具，之后逐步向数据分析专业软件过渡。本章主要介绍的数据分析工具包括模拟运算表、方案管理器、单变量求解、规划求解，通过这些数据分析工具可以帮助用户模拟出可能的运算结果，解决复杂问题，求出解决问题的最佳方案。
实例51经济订货批量分析
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■


经济订货批量可以用来确定企业一次订货(外购或自制)的数量，当企业按照经济订货批量来订货时，可实现订货成本和储存成本之和最小化，本实例中利用公式“经济订货批量=2×年订货量×单次订货成本单位年储存成本”计算经济订货批量的值。若公式中年订货量和单次订货成本不变时，利用单变量模拟运算表分析单位年储存成本对经济订货批量的影响； 若公式中只有年订货量不变时，利用双变量模拟运算表分析单次订货成本和单位年储存成本对经济订货批量的影响。当公式中三个变量同时变化时，利用方案管理器可以比较不同情况下的经济订货批量。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

5.1模拟运算表

模拟运算表实际上是一个单元格区域，它可以显示一个公式中某些参数值的变化对计算结果的影响。由于它可以将所有不同的计算结果以列表方式同时显示出来，因而便于查看、比较和分析。根据公式中变化的参数的个数，模拟运算表分为单变量模拟运算表和双变量模拟运算表。

5.1.1单变量模拟运算表

单变量模拟运算表主要用来分析当其他参数不变时，一个参数的变化对结果的影响。实例51中当企业年订货量为10000个，单次订货成本为300元时，分析不同的单位年储存成本对经济订货批量的影响，操作步骤如下。

1. 创建数据模型

在工作表中创建如图51所示的数据模型。数据模型分为两部分，A1:B4区域为数据区域，A7:B22区域为模拟运算表区域，其中模拟运算表采用“列引用”结构，变量区域为A8:A22，计算的目标值区域为B8:B22。


图51实例51“单变量”数据模型




2. 确定公式位置并输入公式

利用模拟运算表进行分析计算时，模拟运算表区域要包含输入变量和目标值计算公式，所以在B7单元格中输入公式“=SQRT(2*B1*B2/B3)”。

3. 利用模拟运算表计算

选中模拟运算表区域A7:B22，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“模拟运算表”，弹出“模拟运算表”对话框。由于模拟运算表采用“列引用”结构，所以在“输入引用列的单元格”文本框中输入“$B$3”，如图52所示。设置完成后，单击“确定”按钮。计算过程中，A8:A22区域的变量值会替代公式中B3单元格的值。

模拟运算表的结果如图53所示，当选中计算的目标值区域时，单元格编辑栏中显示的公式为“{=TABLE(,B3)}”，表示引用列的单元格为B3。此区域为数组，不能单独编辑其中任何一个单元格的值，若要修改或删除某一单元格的值时，会弹出对话框提示“无法只更改模拟运算表的一部分”，单击“确定”后，按Esc键退出修改状态。当运算结果错误需要删除重新计算时，选中B8:B22区域后，按Delete键删除全部运算结果即可。



图52“模拟运算表”对话框




图53“列引用”运算结果



如果在创建数据模型时采用“行引用”结构，如图54所示，则C7:Q7区域为数据区域，B7:Q8区域为模拟运算表区域。在B8单元格中输入公式"=SQRTC2xB1*B2/B3"，单击“数据”→“模拟分析”→“模拟运算表”命令，在“模拟运算表”对话框的“输入引用行的单元格”文本框中输入“$B$3”，运算结果如图55所示。



图54“行引用”结构




图55“行引用”运算结果


在默认情况下，工作表为自动计算模式。在此模式下，工作表中的任何变化都会使模拟运算表重新计算。如果模拟运算表的数据量比较大，自动计算会减慢运算速度，这时需要停止模拟运算表自动计算模式。操作方法为： 单击“公式”→“计算选项”按钮，在下拉菜单中选择“除模拟运算表外，自动重算”。此时，只有按F9键，模拟运算表才会重新计算。

5.1.2双变量模拟运算表

双变量模拟运算表主要用来分析两个参数的变化对目标结果的影响，类似二元一次方程中变量X、取不同的值时，整个方程的值也会跟着变化。

实例51中当企业年订货量为10000个，分析不同的单位年储存成本和单次订货成本对经济订货批量的影响。其分析步骤与单变量模拟运算表相似，只是公式中有两个变量，具体步骤如下。

1. 创建数据模型

在工作表中创建如图56所示的数据模型，数据模型中A6:G21区域为模拟运算表区域，B6:G6和A7:A21分别为变量A和变量B区域，计算的目标值区域为B7:G21。



图56实例51“双变量”数据模型


2. 确定公式位置并输入公式

在A6单元格中输入公式“=SQRT(2*B1*B2/B3)”。

3. 利用模拟运算表计算

选择模拟运算表区域A6:G21，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“模拟运算表”，弹出“模拟运算表”对话框。在该对话框的“输入引用列的单元格”文本框中输入“$B$3”、“输入引用行的单元格”文本框中输入“$B$2”，如图57所示。设置完成后，单击“确定”按钮即可。

模拟运算表的结果如图58所示，B7:G21区域为运算结果区域。



图57“模拟运算表”对话框




图58运算结果



若要想只使用模拟运算结果的数据，可以用复制数值的方法，将数组结果转变为常量。操作方法为： 选中B7:G21单元格区域后，按Ctrl+C组合键复制数据，然后选中I7单元格后右击，在弹出的快捷菜单中选择“选择性粘贴”选项，在级联菜单的“粘贴数值”选项区中单击“值”按钮，粘贴结果如图59所示。



图59数值复制

5.2方案管理器

模拟运算表仅可以处理一个或两个变量，但可以处理这些变量众多不同的值。方案管理器可以处理多个变量，但最多只能容纳32个值。方案是Excel保存并可以在工作表单元格中自动替换的一组值。用户可以在工作表中创建和保存不同组的组值，然后切换到其中的任一新方案来查看不同的结果。

5.2.1方案的基本操作

实例51中，如果年订货量、单次订货成本和单位年储存成本同时发生变化，这时要计算在不同情况下的经济订货批量，则需要使用方案管理器。使用方案管理器前要先定义方案，现在有3种方案如表51所示。


表513种需求方案


方 案 名 称年 订 货 量单次订货成本单位年存储成本


需求下降800060035
需求持平1000050030
需求上升1200045027


1. 创建方案




图510经济订货批量模型


(1) 将B1:B3单元格区域作为可变单元格，在B4单元格中输入公式“=SQRT(2*B1*B2/B3)”，如图510所示。

(2) 选中B1:B3单元格区域，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“方案管理器”，弹出“方案管理器”对话框，如图511所示。

(3) 在“方案管理器”对话框中单击“添加”按钮，弹出“编辑方案”对话框，在“方案名”文本框中输入“需求下降”，在“可变单元格”文本框中输入“$B$1:$B$3”，如图512所示。




图511“方案管理器”对话框




图512“编辑方案”对话框



(4) 单击“确定”按钮，弹出“方案变量值”对话框，如图513所示。$B$1为年订货量，输入“8000”； $B$2为单次订货成本，输入“600”； $B$3为单位年储存成本，输入“35”。


(5) 单击“确定”按钮，返回“方案管理器”对话框。在“方案”下面的列表框中将显示出已经添加好的“需求下降”方案。

(6) 用同样的方法，根据表51中的数据，添加“需求持平”方案和“需求上升”方案。

2. 编辑方案

方案创建完成以后，可以修改方案中的方案名、可变单元格以及可变单元格中的值，也可以修改方案的保护选项。若需要将“需求持平”方案中“单次订货成本”的值修改为550，具体操作方法如下。

(1) 在“方案管理器”对话框中，选择方案“需求持平”，如图514所示。



图513“方案变量值”对话框




图514“方案管理器”对话框



(2) 单击“编辑”按钮，弹出“编辑方案”对话框,在该对话框中可以重新设置方案名和可变单元格。这里不需要修改则单击“确定”按钮，弹出“方案变量值”对话框，将“$B$2”的值修改为“550”。“编辑方案”对话框中也可以设置保护属性，如取消选中“防止更改”复选框，如果有人对方案进行修改，则在“备注”文本框中将会添加一条修改者的信息。

需要注意的是，在“方案管理器”对话框中，只显示当前工作表的方案名称。

3. 显示方案

工作表中可变单元格只能显示一种方案的值，显示方案的具体操作方法为： 在“方案管理器”对话框中，选择“需求下降”方

图515“方案”显示结果

案。单击“显示”按钮，B1单元格的值将变为“8000”，B2单元格的值变为“600”，B3单元格的值变为“35”。同时，工作表进行重新计算，B4单元格显示该方案的结果，如图515所示。

4. 删除方案


若想删除“需求持平”方案，在“方案管理器”对话框中，选中“需求持平”方案后单击“删除”按钮即可。

5. 保护方案

方案和单元格一样，可以进行保护。在添加方案或编辑方案时，可以在对话框中通过选中“防止更改”和“隐藏”复选框设置保护功能。当工作表受到保护时，设置为“防止更改”的方案不允许编辑或删除，设置为“隐藏”的方案名称不会显示在“方案管理器”对话框中。如果要重新显示已经隐藏的方案，则需要先解除工作表保护。

5.2.2合并方案

在多个工作簿中有多个方案，为了方便管理，可以将这些方案放到一个管理器中进行管理，可以实现多方案的比较，有利于决策。

例如，在“合并方案”工作表中创建如图510所示的经济订货批量模型，现要求将“需求上升方案”工作簿中Sheet1工作表、“需求持平方案”工作簿中Sheet1工作表和“需求下降方案”工作簿中Sheet1工作表的方案合并到“合并方案”工作表中（以上文件见配套资源）。

具体操作方法如下。

(1) 打开“需求上升”工作簿、“需求持平”工作簿和“需求下降”工作簿。

(2) 选择工作簿“实例51”中“合并方案”工作表，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“方案管理器”选项，弹出“方案管理器”对话框，在该对话框中单击“合并”按钮，弹出“合并方案”对话框，如图516所示。



图516“合并方案”对话框


(3) 在“工作簿”下拉列表中选择“需求持平方案”，在“工作表”选项框中选择工作表Sheet1，单击“确定”按钮，将会把“需求持平方案”工作簿的Sheet1工作表中的所有方案都合并到“实例51”工作簿的“合并方案”工作表中，并返回“方案管理器”对话框。

(4) 再次单击“合并”按钮，用同样的方式，将“需求上升方案”工作簿的Sheet1工作表中的方案以及“需求下降方案”工作簿的Sheet1工作表中的方案都添加到“实例51”工作簿的“合并方案”工作表中。

需要注意的是，当需要合并方案的所有工作表的基本结构完全相同时，合并方案的效果是最好的。如果合并结构不一致的工作表中的方案，可能会导致可变单元格出现在异常的位置。

5.2.3建立方案报告

创建方案后，用户可以在新建工作表中生成方案的摘要报告，用来查看多个方案产生的结果，便于进行对比分析。方案报告分为两种： 一种是方案摘要，采用大纲形式，适合于比较简单的方案管理； 另一种是方案数据透视表，适合方案中定义了多种结果

图517“方案摘要”对话框

单元格。


建立“实例51”工作簿“合并方案”工作表的方案报告，具体操作方法如下。

(1) 选择工作簿“实例51”中“合并方案”工作表，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“方案管理器”，弹出“方案管理器”对话框，在该对话框中单击“摘要”按钮，弹出“方案摘要”对话框，如图517所示。


(2) 在“报表类型”选项区中单击“方案摘要”单选按钮，在“结果单元格”文本框中输入放置方案结果的B4单元格。

(3) 单击“确定”按钮，生成如图518所示的“方案摘要”工作表。



图518分级结构的方案摘要


实例52解一元一次方程
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■


学生小李某一门专业课的综合成绩在60分以上(包括60分)才不需要补考，除了期末成绩以外，其他成绩已经公布，小李想知道期末卷面成绩至少考多少分，才能保证不需要参加补考。平时成绩、期中成绩及其所占比例如图519所示。



图519成绩明细数据


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

5.3单变量求解

单变量求解是在已知结果的情况下推测出形成这个结果的变量的值。从本质上来说，单变量求解就是求解一元一次方程中自变量X的值。因此，解决一元方程的问题就可使用单变量求解。

运用单变量求解时需要设置三个参数，分别是目标单元格、目标值和可变单元格。其中，目标单元格放置求解公式； 目标值放置公式的结果； 可变单元格放置自变量X。对于单变量求解可变单元格只能有一个。

要计算实例52中小李期末成绩至少考多少分才不用参加补考，首先需要创建数学模型，假设期末成绩为X，综合成绩为Y，则Y=80×20%+78×20%+X×60%。已知Y的值，求解X，这就是一个求解一元一次方程的问题。如果在B4单元格中存放X的值，B5单元格中存放Y的值，则Y=B2*C2+B3*C3+B4*C4。除了运用上述公式计算以外，Y的值也可以使用SUMPRODUCT函数进行计算。具体操作方法如下。

(1) 建立如图519所示的数学模型，在B5单元格中输入公式“=SUMPRODUCT(B2:B4,C2:C4)”。

(2) 使用单变量求解进行计算。选中目标B5单元格，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“单变量求解”，弹出“单变量求解”对话框，在“目标单元格”文本框中输入B5、“目标值”文本框中输入“60”，“可变单元格”文本框中输入“$B$4”，如图520所示。

(3) 单击“确定”按钮，弹出如图521所示的“单变量求解状态”对话框。如果要保存计算结果，则单击“确定”按钮，计算结果将保存在B4和B5单元格中，否则按“取消”按钮。



图520“单变量求解”对话框




图521“单变量求解状态”对话框



单变量求解还可以应用在贷款购房时用来选择贷款方案。例如，职工小王每月最多可以负担3500元的房贷，他准备贷款40万元，银行贷款年利率为： 10年以下4%~8%，10~20年是6%。小张应该选择哪种贷款方案更合适？

具体操作方法如下。

(1) 建立如图522所示的数学模型。


(2) 选中B7单元格，输入公式“=PMT(B3/12,B412,-B2)”。这里需要假设一种方案，才能正确运用单变量求解计算。假设贷款6年，年利率为4%~8%。利用单变量求解进行计算，“单变量求解”对话框参数设置如图523所示。

(3) 计算结果如图524所示，由于计算得到的贷款年限为12.75年，超过了10年，所以需要使用另一种方案。



图522计算贷款年限数据表




图523参数设置




图524计算结果



(4) 将B3单元格更改为6%，利用单变量求解重新计算，得到的贷款年限为14年，该贷款年限小于20年，故应该选择年利率为6%的贷款方案。

实例53商品采购数量决策
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■


在商品采购管理的实际过程中，采购商品的质量、价格、数量和时机决策被称为商品采购的四大决策技术，其中采购数量决策在降低采购总成本方面发挥着重要作用。科学的采购数量决策，可以有效地防止经营商品的积压和脱销，降低企业经营的风险。

某超市家电销售部在一个季度内销售电视、冰箱、空调、洗衣机的经营空间为85平方米，进货资金为12万元，四种家电的销售利润等情况如图525所示，如何利用Excel的“规划求解”功能帮助用户根据限定条件高效地确定四种家电的最佳采购数量，从而使这一季度的总利润最大化。



图525商品采购数量决策数据表


对商品采购数量决策表中的各个项目进行计算时，应用的计算公式如下。

(1) 销售时间=电视进货量/电视每天销售量+冰箱进货量/冰箱每天销售量+空调进货量/空调每天销售量+洗衣机进货量/洗衣机每天销售量； 

(2) 实需空间=电视进货量×电视每台占用空间+冰箱进货量×冰箱每台占用空间+空调进货量×空调每台占用空间+洗衣机进货量×洗衣机每台占用空间； 

(3) 实用资金=电视进货量×电视每台占用资金+冰箱进货量×冰箱每台占用资金+空调进货量×空调每台占用资金+洗衣机进货量×洗衣机每台占用资金； 

(4) 总利润=电视进货量×电视每台销售利润+冰箱进货量×冰箱每台销售利润+空调进货量×空调每台销售利润+洗衣机进货量×洗衣机每台销售利润。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

5.4规 划 求 解

Excel的规划求解模块是以可选加载项的方式与微软Office软件一起发行的求解运筹学问题的专业软件的免费版本，适用于求解线性规划和非线性规划等问题，具有操作简单、求解迅速等特点。

运筹学研究的问题一般是在若干资源有限的情况下如何找到最优的决策，如费用最小的方案、花费时间最短的方案、利润最大的方案等。运筹学在经济、管理、交通运输、物流等领域得到广泛运用，也是这些行业管理决策的核心技术。

用“规划求解”解决问题时，应明确以下3个要素。

(1) 目标单元格： 求解公式所在的单元格，目标单元格的值可以取某个特定的值，也可以取最大值或最小值。

(2) 可变单元格： 在求解过程中值可变的单元格，可以是多个。若单元格是连续的，可用“:”作为分隔符； 若单元格是不连续的，可用“,”作为分隔符。

(3) 约束条件： 需要转换成数学关系描述，约束关系中主要包括<=、=、>=、int、bin、dif，其中，bin代表可变单元格的值为二进制数，int代表可变单元格的值为整数，dif代表可变单元格的值各不相同。

规划求解的结果可以生成报告，也保存为方案。报告类型有敏感性报告、运算结果报告和极限值报告等，每份报告都会生成一个单独的工作表。

5.4.1加载“规划求解”工具

默认情况下，“规划求解”命令不在“数据”选项卡的“预测”组中，加载“规划求解”功能的具体操作方法如下。

(1) 在菜单栏上单击“文件”→“选项”命令，弹出“Excel选项”对话框，在该对话框中选择“加载项”选项，如图526所示。



图526“Excel选项”对话框


(2) 在“Excel选项”对话框右侧面板下方的“管理”下拉列表中选择“Excel加载项”，单击“转到”按钮，弹出“加载宏”对话框，在“可用加载宏”选项区中勾选“规划求解加载宏”，如图527所示，单击“确定”按钮。



图527“加载宏”对话框


加载成功后，在“数据”选项卡的“分析”组中会显示“规划求解”按钮。如果要卸载“规划求解”加载项，在“加载宏”对话框中取消勾选“规划求解加载项”复选框即可。

5.4.2规划求解应用

利用“规划求解”功能解决实例53，在约束条件下求解最大利润，生成运算结果报告并将求解结果保存名为“商品采购量决策”的方案，具体操作方法如下。

1. 建立问题求解模型

运用“规划求解”功能前，需要把实际问题转换为Excel模型，将目标值、变量和约束条件等数据反映到模型中，然后再计算。实例53的Excel模型同商品采购数量决策表，如图525所示。

2. 输入计算公式

(1) 选中C8单元格，输入公式“=G3/D3+G4/D4+G5/D5+G6/D6”。

(2) 选中E8单元格，输入公式“=SUMPRODUCT(G3:G6,E3:E6)”。

(3) 选中G8单元格，输入公式“=SUMPRODUCT(G3:G6,F3:F6)”。

(4) 选中C9单元格，输入公式“=SUMPRODUCT(G3:G6,C3:C6)”。

输入公式后，因为各种家电的采购数量还没有输入，所以四个公式的计算结果都为0。

3. 设置规划求解参数

(1) 单击“数据”→“规划求解”按钮，弹出“规划求解参数”对话框。

(2) 在“规划求解参数”对话框中，根据实例53的要求设置目标单元格、可变单元格。目标单元格为求解的总利润公式所在C9单元格，可变单元格为目标单元格公式中引用的G3、G4、G5、G6单元格。

单击“设置目标”文本框右侧的折叠按钮，选择C9单元格，再单击折叠按钮，“设置目标”文本框中会显示所选单元格的绝对引用$C$9。由于实例53中的目标就是利润最大化，所以计算总利润的C9单元格要目标最大化，在“到”选项组中选择“最大值”。单击“通过更改可变单元格”文本框右侧的折叠按钮，选择G3:G6单元格区域，再单击折叠按钮，文本框中会显示可变单元格的绝对引用$G$3:$G$6，如图528所示。



图528“规划求解”参数对话框


4. 设置约束条件

约束条件是指“规划求解”中设置的限制条件。实例53主要有以下约束条件。

(1) 时间限制>=销售时间，即C7>=C8； 

(2) 空间限制>=实需空间，即E7>=E8； 

(3) 资金限制>=实用资金，即G7>=G8； 

(4) 家电进货量为正整数，即G3>0且G3为整数，G4>0且G4为整数，G5>0且G5为整数，G6>0且G6为整数。

具体操作方法如下。

(1) 在“规划求解参数”对话框中，单击“添加”按钮，弹出“添加约束”对话框。在“单元格引用”文本框中选择C7单元格，在其右侧的运算符下拉列表中选择“>=”选项，在“约束”文本框中选择C8单元格，然后单击“添加”按钮，如图529所示。



图529“添加约束”对话框


用同样的方法，为“实需空间”添加约束条件，设置E7>=E8； 为“实用资金”添加约束条件，设置G7>=G8。

(2) “添加约束”对话框中，在“单元格引用”文本框中选择G3:G6单元格区域，在“运算符”下拉列表框中选择“int”，在“约束”文本框中将自动出现“整数”字样。设置完成后单击“确定”按钮，将返回“规划求解参数”对话框。

以上设置全部完成后，在“遵守约束”列表框中可以看到添加的所有约束条件，如图530所示。



图530“约束条件”参数


若要删除或修改设置好的约束条件，在“规划求解参数”对话框的“遵守约束”列表框中选择需要删除或修改的约束条件，然后单击“删除”或“更改”按钮。若需要删除所有的约束条件，则单击“全部重置”按钮即可。

5. 用规划求解工具求解

线性关系指关系式中各变量以一次方出现，非线性关系指变量以高次方出现或复杂地描述的关系。在选择求解方法的时候，可以试用非线性或者线性方法，实例53中使用非线性、线性方法都能求出结果，这里使用线性方法，具体操作方法如下。

(1) 在“规划求解参数”对话框中，“选择求解方法”下拉列表中选择“单纯线性规划”，如图528所示。

(2) 单击“选择求解方法”下拉列表框右侧的“选项”按钮，弹出“选项”对话框，在“忽略整数约束”复选中取消勾选，如图531所示，然后单击“确定”按钮返回“规划求解参数”对话框。



图531“选项”对话框


(3) 单击“求解”按钮，弹出“规划求解结果”对话框，在对话框第一行会显示出是否找到满足约束条件并使总利润最大的解，如图532所示。



图532“规划求解结果”对话框


根据实例53的条件可以求出解，选择“保留规划求解的解”单选按钮，然后单击“确定”按钮返回“规划求解参数”对话框，最后单击“关闭”按钮。在Excel模型中会显示出求解的结果，如图533所示。


从图533中可以看出，规划求解的最佳进货量分别是电视9台、冰箱28台、空调40台、洗衣机0台，这样在一个季度内可实现的最大利润额是87100元，达到最大利润时实用资金为119500元，由于受到其他约束条件限制，实用资金没有达到最大使用值。

6. 生成运算结果报告并保存方案

(1) 在“规划求解结果”对话框的“报告”列表框中选择“运算结果报告”，然后单击“确定”按钮，即可在新工作表中自动生成一份“运算结果报告”，如图534所示。报告中列出了各单元格的运算情况和取值情况。

在创建报告时，也可以同时选择多种报告类型，如同时选择敏感性报告和极限值报告等。如果勾选了“制作报告大纲”复选框，则将创建大纲形式的报告。


(2) 在单击“确定”按钮之前，单击“保存方案”按钮，将打开如图535所示的“保存方案”对话框，在“方集名称”文本框中输入“商品采购量决策”，可将结果保存为方案。



图533“规划求解”运算结果




图534运算结果报告




图535“保存方案”对话框




“规划求解”除了可以解决实例53的问题以外，还可以解决很多方面的问题，下面通过几个例子来介绍“规划求解”在其他方面的应用。

实例54求解三元一次方程组
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■


求下面方程组中x、y、z的值，并保存其规划求解参数。

3x-2y+7z=35
-5x+67+3z=28
2x-3y-5z=-21

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

具体操作方法如下。

1） 建立问题求解模型

在新工作簿的Sheet1工作表中按照实例54中的参数，建立如图536所示的“三元一次方程组”Excel模型。



图536实例54“三元一次

方程组”模型


2） 输入计算公式

(1) 选中B6单元格，输入公式“=B3*B2+C3*C2+D3*D2”。

(2) 选中B7单元格，输入公式“=B4*B2+C4*C2+D4*D2”。

(3) 选中B8单元格，输入公式“=B5*B2+C5*C2+D5*D2”。

3） 设置规划求解参数

(1) 单击“数据”→“规划求解”按钮，弹出“规划求解参数”对话框。

(2) 在“规划求解参数”对话框中，将“设置目标”旁边的文本框留空，在“通过更改可变单元格”文本框中选择“B2:D2”单元格区域，在“遵守约束”文本框中输入“$B$6:$B$8=$B$9:$B$11”，取消勾选“使无约束变量为非负数”复选框，在“选择求解方法”下拉列表中选择“单纯线性规划”，如图537所示。本例中“选择求解方法”选择“非线性GRG”也能求出解。



图537实例54“规划求解参数”设置


4） 用规划求解工具求解

在“规划求解参数”对话框中，单击“求解”按钮，弹出“规划求解结果”对话框，在该对话框中显示找到了一个解并满足所有约束条件，选择“保留规划求解的解”，如图538所示。然后单击“确定”按钮，在工作表Sheet1中将显示求解结果，如图539所示。



图538“规划求解结果”对话框



5） 保存“规划求解”参数

在“规划求解参数”对话框中，单击“装入/保存”按钮，弹出“装入/保存模型”对话框，在文本框中输入保存的起始位置$A$13，将参数保存在A13:A17单元格区域，然后单击“保存”按钮返回“规划求解参数”对话框，最后单击“关闭”。保存后效果如图540所示。



图539求解结果




图540保存结果



当求解其他三元一次方程组时，可以装入Sheet1中保存的参数，可以避免重新设置各参数。例如，求解下面方程组x、y、z的值。

x+y+z=152x+3y+z=282x-2y+4z=22

首先在Sheet2中建立如图541所示的问题求解模型，然后选择“数据”→“规划求解”，弹出“规划求解参数”对话框,单击“装入/保存”按钮，弹出“装入/保存模型”对话框，在文本框中输入Sheet1中规划求解参数存放的单元格区域“Sheet1!A13:A17”，单击“装入”按钮，计算结果如图542所示。




图541参数模型





图542求解结果



实例55假期员工值班安排
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■



某学校国庆节期间1号到7号放假7天，为保障校园安全、各学院能够处理简单事务，需要各学院安排人员值班。安排教职工值班既要协调好各方需求，又要合理安排，这是属于比较复杂的选择最佳方案的决策问题，如果人工安排，比较麻烦，利用Excel的“规划求解”可以迅速解决这个问题。

现学校要求各学院国庆7天假每天安排一人值班，信息学院安排的这7个人分别是张伟、刘明、赵涛、和大海、杨小琴、木亮、郑静。安排值班时，这7个人有以下条件。

① 杨小琴比张伟晚5天值班。

② 张伟比刘明早2天值班。

③ 杨小琴比赵涛早一天值班。

④ 木亮比和大海晚若干天值班。

⑤ 木亮比杨小琴早若干天值班。

⑥ 木亮在4号有空，需要安排他4号值班。

请按照以上条件，排出7人的值班表，并生成运算结果报告。

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

具体操作方法如下。

1） 建立“员工值班表”

创建一个新工作簿，按照图543所示，在Sheet1工作表中输入相应内容，建立“员工值班表”。

2） 设置变量、输入计算公式

(1) 在值班安排条件中，若干天无法用具体数值表示，故引入两个变量，分别为变量1和变量2，用这两个变量表示条件里的两个若干天。变量1的值放在D2单元格，变量2的值放在D3单元格。

(2) 在B7单元格中输入“4”，然后根据问题条件在B2:B6单元格区域中输入相应的关系式： 
在B2单元格中输入“=B6-5”，在B3单元格中输入“=B2+2”，在B4单元格中输入“=B6+1”，在B5单元格中输入“=B7-D3”，在B6单元格中输入“=B7+D4”。

输入公式后，各单元格会自动显示一些数值，如图544所示，显示的数值不是最终结果，只是由一些默认值计算出的结果。



图543员工值班表




图544输入公式的“员工值班表”



(3) 除了引入两个变量以外，还要引入一个“目标值”，用来计算限制B列值班日期的取值。目标值的数值放置在D4单元格，在C4单元格中输入“目标值”。



图545辅助运算数据


(4) 加入辅助计算数值，在D5:D11单元格区域中分别输入数值1~7，然后在D12单元格中输入公式“=PRODUCT(D5:D11)”，用来计算1~7的乘积，计算结果如图545所示。


(5) 设置一个“目标值”用于规划求解，“目标值”的值存放在D4单元格。在D4单元格中输入公式“=PRODUCT(B2:B8)”，后面将把D4单元格计算的值限制等于D12单元格的值，也就是5040，从而控制日期取值为1~7。因为无论怎么安排，每个人的值班日期都是1号到7号中的一天，7个人值班日期的乘积肯定是5040。

3） 设置规划求解参数


(1) 单击“数据”→“规划求解”按钮，弹出“规划求解参数”对话框。

(2) 在“规划求解参数”对话框中，在“设置目标”右侧的文本框中输入“$D$4”，然后在“到”选项区中选择“目标值”，在右侧的文本框中输入“5040”。在“通过更改可变单元格”文本框中输入“$D$2:$D$3,$B$8”单元格区域，如图546所示。



图546设置“规划求解参数”


(3) 单击“遵守约束”文本框右侧的“添加”按钮，在弹出的“添加约束”对话框中设置D2单元格为整数，如图547所示。用同样的方法将D3、B8单元格设置为整数。



图547“添加约束”对话框


继续单击“添加”按钮，分别将D2、D3和B8单元格设置为大于或等于1并且小于或等于7。最后，单击“确定”按钮返回到“规划求解参数”对话框，对话框中显示出添加的约束条件，如图546所示。

(4) 在“规划求解参数”对话框中的“选择求解方法”下拉列表中选择“非线性GRG”，单击“选项”按钮，在弹出的“选项”对话框中取消勾选“忽略整数约束”复选框，如图548所示。单击“确定”按钮，返回“规划求解参数”对话框，至此，全部参数和约束条件设置完成。



图548“选项”对话框


4） 用规划求解工具求解

在“规划求解参数”对话框中单击“求解”按钮，弹出“规划求解结果”对话框，在该对话框中显示找到一个满足条件的解，然

图549规划求解结果

后选择“保留规划求解的解”，最后单击“确定”按钮，工作表中会显示出求解结果，如图549所示。


5） 生成运算结果报告

单击“数据”→“规划求解”按钮，弹出“规划求解参数”对话框，单击“求解”按钮，弹出“规划求解结果”对话框，在“报告”选项列表中选择“运算结果报告”，单击“确定”按钮，在“运算结果报告1”工作表中会自动生成一份“运算结果报告”。

实例56商品运输成本决策
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■


为了促进乡村振兴战略发展，丽江市政府扶持某农业公司种植雪桃。该农业公司拥有3个雪桃种植区和5个销售雪桃的市场，雪桃种植区1、2、3每年最多可以产雪桃分别为1600、1500、1800吨，销售市场1、2、3、4、5每年销售量最多分别为1200、1000、800、900、1000吨。从雪桃种植区使用汽车运输到销售市场，其每吨运输成本如图550所示，利用规划求解分析雪桃种植区应如何向各个销售市场运输雪桃才能使运输成本最低？



图550运输成本


■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■

具体操作方法如下。

1） 建立求解模型

根据实例56中的数据在新工作表中建立如图551所示的问题求解模型，模型中B6:F6单元格区域为变量，需要分析出B6:F6如何取值能使总运费最小。



图551问题求解模型


2） 输入公式

(1) 计算销售量。销售量是指每个市场销售3个雪桃种植区提供的雪桃的总量。在B6单元格中输入公式“=SUM(B3:B5)”，利用单元格右下角的填充柄在单元格区域C6:F6中填充公式。

(2) 计算供给量。供给量是指每个雪桃种植区向各个市场运送雪桃的总量。在G3单元格中输入公式“=SUM(B3:F3)”，利用单元格右下角的填充柄在G4:G5单元格区域中填充公式。

(3) 计算总运费。总运费是运往各个市场的运输成本和运输量乘积的总和，在G9单元格中输入公式“=SUMPRODUCT(B3:F5,B10:F12)”。

3） 设置规划求解参数

(1) 单击“数据”→“规划求解”按钮，弹出“规划求解参数”对话框。

(2) 在“规划求解参数”对话框中，“设置目标”右侧的文本框中输入“$G$9”，在“到”选项组中单击“最小值”按钮。

(3) 在“通过更改可变单元格”文本框中输入“$B$3:$F$5”。

(4) 单击“添加”按钮，弹出“添加约束”对话框，在“单元格引用”文本框中输入“$B$6:$F$6”，在运算符下拉列表中选择“=”，在“约束”文本框中输入“$B$7:$F$7”。继续单击“添加”按钮，重新弹出“添加约束”对话框，在“单元格引用”文本框中输入“$G$3:$G$5”，在运算符下拉列表中选择“=”，在“约束”文本框中输入“$H$3:$H$5”，最后单击“确定”按钮。

(5) 在“规划求解参数”对话框中的“选择求解方法”下拉列表中选择“单纯线性规划”。在本例中选择“非线性GRG”也可以求解。

每项参数设置如图552所示。



图552规划求解参数设置


4） 用规划求解工具求解

在“规划求解参数”对话框中单击“求解”按钮，弹出“规划求解结果”对话框，在对话框中会显示找到一个满足条件的解，然后选择“保留规划求解的解”，最后单击“确定”按钮，工作表中会显示出求解结果，如图553所示。



图553规划求解结果


5.5数据分析综合案例
5.5.1预测分析投资收益

某企业员工小陶年底收入一笔奖金，准备在某证券公司投资一款理财产品。他准备投资20万元，这款理财产品预计年化收益率为6%，投资期限为5年，请按照如下要求完成计算和分析工作。

(1) 计算投资到期后的到期总额、总收益和总收益率。

(2) 小陶想知道如果投资3年、8年、10年、15年，在这些不同的投资年限下的到期总额、总收益和总收益率是多少，试利用单变量模拟运算表进行计算。

(3) 不同的投资年限下投资收益如表52所示，试利用双变量模拟运算表计算不同情况下的到期总额。


表52不同年限投资收益率情况表


投 资 年 限年化收益率投 资 年 限年化收益率


35%107.6%
87%159%


(4) 投资经理给小陶推荐了几款理财产品供他选择，如表53所示。试利用方案管理器比较几种理财产品的到期总额、总收益和总收益率。


表53四种理财产品情况表


方 案 名 称投资总额/元投资年限年化收益率


产品15万10年4.5%
产品210万6年5%
产品320万5年6%
产品450万2年8%


(5) 小陶的同事小王也想投资理财，他能投资的总额为10万元，投资期限不超过10年，投资总额10万元投资期限不超过10年的理财产品投资收益为年化5%。小王希望能够获得3万元的总投资收益，试利用单变量求解计算小王需要投资多少年。

具体分析操作方法如下。



图554计算模型


(1) 新建一个工作簿，命名为“预测分析投资收益”。在工作簿的Sheet1工作表中根据案例中的数据建立如图554所示的计算模型。


选中B4单元格，输入公式“=B1*(1+B2)^B3”，计算出到期总额为267645元。然后，选中B5单元格，输入公式“=B4-B1”，计算出总收益为67645元。最后，选中B6单元格，输入公式“=B5/B1”，计算出总收益率为34%。

(2) 在Sheet1工作表中建立如图555所示的单变量模拟运算表数据模型。在B9单元格中输入“=B4”，相当于把B4单元格中计算到期总额的公式复制给B9单元格。同理，在C9单元格中输入“=B5”，在D9单元格中输入“=B6”。

选择模拟运算表区域A9:D13，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“模拟运算表”，弹出“模拟运算表”对话框。由于模拟运算表采用“列引用”结构，所以在“输入引用列的单元格”文本框中输入“$B$3”，单击“确定”按钮。计算过程中，A10:A13区域的变量值会替代公式中B3单元格的值。计算结果如图556所示。



图555单变量模拟运算表数据模型




图556单变量模拟运算表计算结果



(3) 在Sheet1工作表中创建如图557所示的双变量模拟运算表数据模型。其中，A16:A18单元格区域为不同的投资年限，B15:D15单元格区域为不同的年化收益率。在A15单元格中输入计算公式“=B1*(1+B2)^B3”。


选择模拟运算表区域A15:D18，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“模拟运算表”，弹出“模拟运算表”对话框。在该对话框的“输入引用列的单元格”文本框中输入“$B$3”、“输入引用行的单元格”文本框中输入“$B$2”。设置完成后，单击“确定”按钮。双变量模拟运算表的结果如图558所示，B16:D18区域为运算结果区域。



图557双变量模拟运算表数据模型




图558双变量模拟运算表计算结果



(4) 将B1:B3单元格区域作为创建方案的可变单元格。选中B1:B3单元格区域，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“方案管理器”，弹出“方案管理器”对话框，单击“添加”按钮，弹出“添加方案”对话框，在“方案名”文本框中输入“产品1”、“可变单元格”文本框中输入“$B$1:$B$3”，单击“确定”按钮，弹出“方案变量值”对话框，在$B$1为“投资总额”，输入“50000”； $B$2为“年化收益率”，输入“4.5%”； $B$3为“投资年限”，输入“10”，单击“确定”按钮，返回“方案管理器”对话框。

在“方案”列表框中显示出已经添加好的“产品1”方案。用同样的方法，根据表54添加“产品2”方案、“产品3”方案和“产品4”方案。4种方案全部添加完成后，在“方案管理器”对话框中选择具体方案后单击“显示”按钮，即可在Sheet1工作表中查看选择的方案对应的到期总额、总收益和总收益率。

(5) 在Sheet1工作表中，建立如图559所示的单变量求解数学模型，在B23单元格中输入公式“=B20*(1+B21)^B22-B20”。

选中目标单元格B23，单击“数据”→“模拟分析”按钮，在下拉菜单中选择“单变量求解”，弹出“单变量求解”对话框，在“目标单元格”文本框中输入“B23”，“目标值”文本框中输入“30000”，“可变单元格”文本框中输入“$B$22”，单击“确定”按钮，弹出“单变量求解状态”对话框。如果要保存计算结果，则单击“确定”按钮，计算结果将保存在B22单元格中，否则按“取消”按钮。最终计算结果如图560所示。



图559单变量求解数学模型




图560单变量求解计算结果



5.5.2最短路径问题

疫情期间，需要将口罩、防护服和其他急需医疗物资从A地运送到F地，途经B、C、D、E这4个城市，构成的运输网，各个城市之间的距离如表54所示，X表示两个城市之间不存在运输网，利用规划求解分析计算从A地到F地的最短路径。


表54城市运输网


城市

相邻城市间的距离

BCDEF


A206040XX
B10X80X
C2060X
D2070
E80


具体分析操作方法如下。

1. 建立求解模型

新建一个工作簿，命名为“医疗物资运输”。根据表55的数据，在Sheet1工作表中建立求解模型，如图561所示。每个城市都可以看成是运输网中的节点，每个节点都有净流量，净流量等于流出量减去流入量。从A城市发出单位流量1，最后单位流量1到达F城市，所经过的城市净流量为零，由此可以确定最短的路径。求解模型中要计算C2:C11的值，使F2:F7在满足条件的基础上使总距离F10最小。



图561求解数学模型


2. 输入公式

(1) 计算静流量。在F2单元格中输入公式“=SUMIF($A$2:$A$11,E2,$C$2:$C$11)-SUMIF($B$2:$B$11,E2,$C$2:$C$11)”，利用单元格右下角的填充柄在F3:F7单元格区域中填充公式。

(2) 计算总距离。在F10单元格中输入公式“=SUMPRODUCT(C2:C11,D2:D11)”。

3. 设置规划求解参数

(1) 单击“数据”→“规划求解”按钮，弹出“规划求解参数”对话框。

(2) 在“规划求解参数”对话框中，“设置目标”右侧的文本框中输入$F$10，“到”选项组中选择“最小值”按钮。

(3) 在“通过更改可变单元格”文本框中输入“$C$2:$C$11”。

(4) 单击“添加”按钮，弹出“添加约束”对话框。在“单元格引用”文本框中输入“$C$2:$C$11”,运算符下拉列表中选择“>=”，“约束”文本框中输入“0”。继续单击“添加”按钮，重新弹出“添加约束”对话框。在“单元格引用”文本框中输入“$F$2:$F$7”，运算符下拉列表中选择“=”，“约束”文本框中输入“=$G$2:$G$7”，最后单击“确定”按钮

(5) 在“规划求解参数”对话框中的“选择求解方法”下拉列表中选择“单纯线性规划”。

每项参数设置如图562所示。



图562参数设置


4. 用规划求解工具求解

在“规划求解参数”对话框中单击“求解”按钮，弹出“规划求解结果”对话框，在对话框中显示找到一个满足条件的解，然后选择“保留规划求解的解”，最后单击“确定”按钮，工作表中会显示出求解结果，如图563所示。在求解结果中，可变单元格区域C2:C11中的值为1表示最短路径经过这两个城市组成的运输网，0表示不经过这两个城市组成的运输网，因此最短的路径是ADF，由此计算的最短距离是110。



图563求解结果


5.5.3旅行推销员问题

旅行推销员问题(Traveling Salesman Problem，TSP)是一个经典的组合优化问题。经典的TSP可以描述为： 一个商品推销员要去若干个城市推销商品，该推销员从一个城市出发，需要经过所有城市后，回到出发地。应如何选择行进路线，使总的行程最短。

本案例中推销员在5个城区之间推销，5个城区之间的距离如表55所示，X表示两个城区之间无路径。


表555个城区间的距离


到达城区

出 发 城 区

ABCDEF



AX4020XXX
B40X4030XX
C2040X6070X
DX3060X5060
EXX7050X30
FXXX6030X


具体操作方法如下。

1. 建立求解模型

新建一个工作簿，命名为“旅行推销员问题”。根据表57中的数据，在Sheet1工作表中建立求解模型，模型如图564所示。在模型中，用“9999”代替表57中的X，其意义是用一个很大的数来代表这两个城区间不存在路径，以此避免计算时选择此路径。在A10:J18单元格区域建立的解决问题模型中，“出发限制”用来限制出发城区，“到达限制”用来限制到达城区，两者都要保证只有一个。此外，C12:H18单元格区域取二进制数，“1”代表选择这条路径，“0”代表不选择这条路径。求解时，需要确定C12:H18单元格区域的值，使出发城区和到达城区满足唯一性，并且达到合计距离最短。



图564求解数学模型


2. 输入公式

输入公式前，先设置C12:H18单元格区域的格式。选中C12:H18单元格后右击，在弹出的快捷菜单中选择“设置单元格格式”选项，弹出“设置单元格格式”对话框切换到“数字”选项卡，在“分类”选项列表中选择“自定义”，“类型”选项列表中选择“0”，单击“确定”按钮即可。

(1) 计算出发限制在I12单元格中输入公式“=SUM(C12:H12)”，利用单元格右下角的填充柄在I13:I17单元格区域中自动填充公式。

(2) 计算到达限制。在C18单元格中输入公式“=SUM(C12:C17)”，利用单元格右下角的填充柄在D18:H18单元格区域中自动填充公式。

(3) 计算各路径距离。在J18单元格中输入公式“=SUMPRODUCT(C3:H3,C12:H12)”，利用单元格右下角的填充柄在I12:I15单元格区域中自动填充公式。

(4) 计算总距离，在J18单元格中输入公式“=SUM(J12:J17)”。

3. 设置规划求解参数

(1) 单击“数据”→“规划求解”按钮，弹出“规划求解参数”对话框。

(2) 在“规划求解参数”对话框中，“设置目标”右侧的文本框中输入“$J$18”，“到”选项组中选择“最小值”按钮。

(3) 在“通过更改可变单元格”文本框中输入“$C$12:$H$17”。

(4) 单击“添加”按钮，弹出“添加约束”对话框，在“单元格引用”文本框中输入“$C$12:$H$17”、运算符下拉列表中选择“bin”，“约束”文本框中会显示“二进制”字样。继续单击“添加”按钮，重新弹出“添加约束”对话框，在“单元格引用”文本框中输入“$I$12:$I$17”，运算符下拉列表中选择“=”，“约束”文本框中输入“1”。再继续单击“添加”按钮，重新弹出“添加约束”对话框，在“单元格引用”文本框中输入“$C$18:$H$18”，运算符下拉列表中选择“=”，“约束”文本框中输入“1”，最后单击“确定”按钮。

(5) 在“规划求解参数”对话框的“选择求解方法”下拉列表中选择“单纯线性规划”，并且勾选“使无约束变量为非负数”复选框。

(6) 单击“选择求解方法”文本框右侧的“选项”按钮，弹出“选项”对话框，取消勾选“忽略整数约束”复选框。

每项参数设置详情如图565所示。



图565参数设置


4. 用规划求解工具求解

在“规划求解参数”对话框中单击“求解”按钮，弹出“规划求解结果”对话框，在对话框中显示找到一个满足条件的解，然后选择“保留规划求解的解”，最后单击“确定”按钮，工作表中会显示出求解结果，如图566所示。



图566求解结果


在求解结果中，形成了三个没有封闭的路径，分别是C→A→C、D→B→D、E→F→E，这不符合案例中的要求。因此，需要增加约束条件形成闭环。把求解结果作为新的约束条件添加进来，使C14和E12单元格不能同时等于“1”，D15和F13单元格不能同时等于“1”，G17和H16单元格不能同时等于“1”。

在K11单元格中输入“辅助条件”，然后在K12单元格中输入公式“=C14+E12”，K13单元格中输入公式“=D15+F13”，K14单元格中输入公式“=G17+H16”。在“规划求解参数”对话框中，单击“添加”按钮，弹出“添加约束”对话框。在“单元格引用”文本框中输入“$K$12:$K$14”，运算符下拉列表中选择“<=”，“约束”文本框中输入“1”，最后单击“确定”按钮。

在“规划求解参数”对话框中单击“求解”按钮，弹出“规划求解结果”对话框，在对话框中显示找到一个满足条件的解，然后选择“保留规划求解的解”，最后单击“确定”按钮，工作表中会显示出求解结果，如图567所示。由求解结果可知，添加新的约束条件后，仍然形成了2个没有封闭的路径，分别是B→A→C→B和D→E→F→D。



图567求解结果


因此，需要继续添加新的约束条件，使D12和E13单元格不能同时等于“1”。在K15单元格中输入公式“=D12+E13”。在“规划求解参数”对话框中单击“添加”按钮，弹出“添加约束”对话框，在“单元格引用”文本框中输入“$K$15”，运算符下拉列表中选择“<=”，“约束”文本框中输入“1”，最后单击“确定”按钮。

在“规划求解参数”对话框中单击“求解”按钮，弹出“规划求解结果”对话框，在对话框中显示找到一个满足条件的解，然后选择“保留规划求解的解”，最后单击“确定”按钮，工作表中会显示出求解结果，如图568所示。由求解结果可知，添加新的约束条件后，仍然形成了2个没有封闭的路径，分别是C→A→B→C和D→E→F→D。



图568求解结果


因此，还需要继续添加新的约束条件，使D14和E12单元格不能同时等于“1”。在K16单元格中输入公式“=D14+E12”。在“规划求解参数”对话框中，单击“添加”按钮，弹出“添加约束”对话框，在“单元格引用”文本框中输入“$K$16”，运算符下拉列表中选择“<=”，“约束”文本框中输入“1”，最后单击“确定”按钮。

在“规划求解参数”对话框中单击“求解”按钮，弹出“规划求解结果”对话框，在对话框中显示找到一个满足条件的解，然后选择“保留规划求解的解”，最后单击“确定”按钮，工作表中会显示出求解结果，如图569所示。由求解结果可知，添加新的约束条件后，形成了一个封闭的路径B→A→C→E→F→D→B，符合案例中要求。



图569最终求解结果


5.6习题

1. 小王努力工作若干年后准备在北京买套自住商品房，他准备贷款40万元。目前银行年贷款利率为6.55%，不同的贷款年限对应不同的月还款额和到期总还款额，小王不知道选择何种方案进行贷款比较适合他目前的经济状况。试帮助小王利用单变量模拟运算表计算在不同的贷款年限下月还款额和到期总还款额都是多少？贷款计划表A如图570所示。



图570贷款计划表A


2. 题1中若不同的贷款年限对应的贷款年利率也不同，试帮助小王利用双变量模拟运算表计算不同的贷款年限和贷款年利率下月还款额是多少？贷款计划表B如图571所示。



图571贷款计划表B


3. 购房时公积金贷款利率比较低但是额度有限，商业贷款利率比较高但贷款额度相对也更高。现在有三种贷款模式，分别是公积金贷款、商业贷款和组合贷款，三种模式对应的贷款金额、贷款年限和贷款月利率如表56所示。试利用方案管理器比较不同模式下月还款额是多少？


表56三种贷款模式


贷 款 模 式公积金贷款商 业 贷 款组 合 贷 款


贷款金额400000元60000元50000元
贷款年限30年20年25年
月利率0.35%0.42%0.38%


4. 某屠宰场宰杀了若干只鸡和若干头猪，现已知宰杀鸡和猪的数量一共是36，得到鸡腿和猪脚的数量一共是96。试用单变量求解和规划求解两种方法分析宰杀了多少只鸡和多少头猪。