Excel 高级教程

1. Excel高级教程 －Excel分析工具

2. Excel分析工具 • 假设分析是指观察改变单元格的数值对工作表中公式所产生结果的影响。例如：当偿还期限改变时对每月按揭额的影响。 • 利用Excel提供的假设分析功能（包括单变量求解、模拟运算表，规划求解及方案等），并结合Excel强大的函数库，可以方便轻松地完成各种财务统计等工作。

3. Excel分析工具 • 单变量求解 • 模拟运算表 • 单变量模拟运算表 • 双变量模拟运算表 • 规划求解 • 线形规划 • 整数规划 • 0-1规划 • 非线形规划 • 方案管理

4. 单变量求解 • 如果已知公式的结果，而不知道公式的变量值，那么可以用单变量求解来寻求公式的特定解。进行单变量求解时，Excel通过一定的算法进行试算，直到找到合适的解。 • 说白了，就是解单变量的方程。

5. 单变量求解 • 单变量求解的具体操作如下： • 公式编辑。 • 单击“工具”菜单中的“单变量求解”命令。这时将出现“单变量求解”对话框。 • 在“目标单元格”编辑框中，输入待求解的公式所在的单元格的引用。 • 在“目标值”编辑框中，键入所需的结果。 • 在“可变单元格”编辑框中，输入待调整数值（变量）所在单元格的引用。 • 单击确定可得到结果。

6. 单变量求解 • 示例 解方程 • 在单元格A2中输入公式“＝A1*8+44”，A1作为存放变量x的单元格。

7. 单变量求解 • 单击“工具”菜单中的“单变量求解”命令。这时将出现“单变量求解”对话框。 • 在“目标单元格”编辑框中，输入A2。 • 在“目标值”编辑框中，键入100。 • 在“可变单元格”编辑框中，输入A1。

8. 单变量求解 • 单击确定可得到结果。

9. 单变量求解 • 这时，有同学就要问，这么简单的问题，何必杀鸡用牛刀？ • 事实上，很多方程根本就无法人工解出来，必须依靠计算机来解决。这时Excel就是一个很好的工具。 • 比如 就够你受的了。更别说更复杂的了。利用Excel很快就可以计算出x＝5.739552102

10. 模拟运算表 • 模拟运算表是工作表中的一个单元格区域，它可以显示公式中某些值的变化对计算结果的影响。模拟运算表为同时求解某一运算中所有可能的变化值提供了捷径，并且，它还可以将所有不同的计算结果同时显示在工作表中，便于查看和比较。

11. 模拟运算表 • 单变量模拟运算表 • 单变量模拟运算表的结构特点是，其输入数值被排列在一列中（列引用）或一行中（行引用）。单变量模拟运算表中使用的公式必须引用输入单元格。存放在输入单元格中的输入数据清单将被替换。工作表中的任何单元格都可以用作输入单元格。虽然输入单元格不必是模拟运算表的一部分，但模拟运算表中的公式必须引用输入单元格。

12. 模拟运算表 • 单变量模拟运算表 • 创建单变量模拟运算表，具体操作如下： • 在一行或者一列中输入要替换工作表上的输入单元格的数值序列； • 如果输入数值被排成一列（行），则在第一个数值的上一行（左边一列）且处于数值序列右侧（下方）的单元格中输入所需的公式。 • 选定包含公式和需要被替换的数值的单元格区域。 • 在“数据”菜单中，单击“模拟运算表”命令； • 如果模拟运算表是列方向的，请在“输入引用列的单元格”编辑框中，为输入单元格键入引用。如果是行，相应做即可。

13. 模拟运算表 • 单变量模拟运算表 下面我们通过绘制y＝sin x在[0,2Pi]的曲线来说明单变量模拟运算表的操作。 绘制曲线，首先要算出函数在各个点的取值，然后画出各个点的散点图。

14. 模拟运算表 • 单变量模拟运算表 1、如下图准备数据 2、选中区域C3：D20

15. 模拟运算表 • 单变量模拟运算表 3、单击“数据”－“模拟运算表”，弹出如下对话框，如图示设置列引用。 4、单击“确定“，得到如下结果。

16. 模拟运算表 • 单变量模拟运算表 5、用图表向导，作出散点图。

17. 模拟运算表 • 双变量模拟运算表 • 双变量模拟运算表与单变量模拟运算表的区别在于前者使用两个可变单元格。双变量模拟运算表中的两组输入数值使用同一个公式。这个公式必须使用两个不同的输入单元格。

18. 模拟运算表 • 双变量模拟运算表 • 创建双变量模拟运算表的具体操作如下： • 在工作表的某个单元格中，输入所需的至少引用两个单元格的公式； • 在公式的下面同一列中键入一组待替换的变量序列，在公式右边同一行键入待替换的变量序列； • 选中包含公式以及数据行和列的单元格区域； • 在“数据”菜单中，单击“模拟运算表…”命令； • 在“输入引用行的单元格”编辑框中，输入要由行变量序列替换的输入单元格的引用； • 在“输入引用列的单元格”编辑框中，输入要由列变量序列替换的输入单元格的引用； • 单击“确定”得到所需的结果。

19. 模拟运算表 • 双变量模拟运算表 • 下面，我们通过制作一个乘法口诀表来说明双变量模拟运算表的操作过程。 1、按照如下格式准备数据

20. 模拟运算表 • 双变量模拟运算表 2、在单元格C3中输入“=B4 & "×" & B5 & "=" & B4*B5”

21. 模拟运算表 • 双变量模拟运算表 3、选中区域C3：L12

22. 模拟运算表 • 双变量模拟运算表 4、单击“数据”－“模拟运算表”，弹出如下对话框，如图示设置行引用和列引用。 注意：这里行引用和列引用不能是上一步选择的区域中的任何一个单元格，否则将出错。单变量模拟运算表则无此限。

23. 模拟运算表 • 双变量模拟运算表 5、单击“确定”，得到一种漂亮的乘法口诀表。

24. 模拟运算表 • 模拟运算表的应用实例 分期付款（按揭）是当代比较流行的支付方式，下面我们将同过两个实例来说明单变量模拟运算表和双变量模拟运算表的应用。

25. 模拟运算表 • 示例一 某人贷款10万元，欲分4年（48月）偿还，试求贷款按揭利率在8％～13％之间变化时，每月应等额偿还多少钱？

26. 模拟运算表 • PMT(rate,nper,pv,fv,type) • 有关函数 PMT 中参数的详细说明，请参阅函数 PV。 • Rate   贷款利率。 • Nper   该项贷款的付款总数。 • Pv   现值，或一系列未来付款的当前值的累积和，也称为本金。 • Fv   为未来值，或在最后一次付款后希望得到的现金余额，如果省略 fv，则假设其值为零，也就是一笔贷款的未来值为零。 • Type   数字 0 或 1，用以指定各期的付款时间是在期初还是期末。 • 如，贷款10万，分48月还，年利率为8％，每月月初应还的数额为pmt（0.08/12，48，100000，0，0）

27. 模拟运算表 • 从该表我们可以看出，当年利率8％～13％之间变化时，每月的偿还额从￥2411上升到￥2682。这就为想在4年内支付完一辆现在价格为10万元的汽车的消费者提供一个支付能力的参考。

28. 模拟运算表 • 示例二 贷款10万元，当贷款年利率在8％～13％之间变化时，在各种利率下，还款期限为5、10、15、20年时所对应的每月偿还额是多少？

29. 模拟运算表 • 运算结果表明，每月的偿还额从2500到6825之间变化，年利率越高，月偿还额越大，期限越长，月偿还额月小。如果预期的月薪不到2500，就别奢望拥有现价为30万的房子了!

30. 规划求解 • 运筹学是经济管理过程中常用的工具之一。经济管理往往被一定的运筹模型来表达，但是人工求解比较困难，特别对于变量个数比较多的规划模型，人工求解简直就是痴心妄想!于是，人们就开始寻求机械的解法，特别是运用计算机来求解。现在已经有很多软件可以实现规划求解，如SAS、Malab、Excel。今天，我们介绍一下如何用Excel来求解运筹学中的规划问题。能够解决的规划问题包括：线性规划、目标规划、整数规划、非线性规划。

31. a11X1+ a12X2+…+ a1nXn (=, )b1 a21X1+ a22X2+…+ a2nXn(=, )b2 … … … am1X1+ am2X2+…+ amnXn(=, )bm Xj 0(j=1,…,n) 规划求解 Max(min)Z=C1X1+ C2X2+…+CnXn

32. 规划求解 • 第一步，按照下面的格式，把目标函数、约束条件的两边分别用n个变量的表达式或以这些变量为参数的函数表示。

33. 规划求解 • 单击工具－加载宏，弹出下面的对话框，把规划求解选项钩上，单击确定。（这一步只是在第一次使用的时候需要，目的是把规划求解的模块加进来。） • 单击工具－规划求解…，弹出对话框，把目标函数、最大或者最小、约束条件设置好，就可以求解了。

34. “规划求解参数”对话框上各选项说明

35. “规划求解参数”对话框上各选项说明（续）

36. 设置“规划求解”选项

37. 设置“规划求解”选项

38. 设置“规划求解”选项（续1）

39. 设置“规划求解”选项（续2）

40. 设置“规划求解”选项（续3）

41. “规划求解”完成信息 • 当“规划求解”得到答案时，它将在“规划求解结果”对话框中显示下述两条信息之一： • “规化求解”找到一个解，可满足所有的约束及最优化要求。 这表明按照“规划求解选项”对话框中设置的精度，所有约束条件都已满足，并且只要有可能，目标单元格中将得到极大值或极小值。 • “规划求解”收敛于当前结果，并满足全部约束条件。 这表明目标单元格中的数值在最近五次求解过程中的变化量小于“规划求解选项”对话框中“收敛度”设置的值。“收敛度”中设置的值越小，“规划求解”在计算时就会越精细，但求解过程将花费更多的时间。

42. “规划求解”完成信息 • 当规划求解不能得到最佳结果时，在“规划求解结果”对话框中显示下述信息之一： • 满足所有约束条件。“规划求解”不能进一步优化结果。 这表明仅得到近似值，迭代过程无法得到比显示结果更精确的数值：或是无法进一步提高精度，或是精度值设置得太小，请在“规划求解选项”对话框中试着设置较大的精度值，再运行一次。

43. “规划求解”完成信息 • 求解达到最长运算时间后停止。 这表明在达到最长运算时间限制时，没有得到满意的结果。如果要保存当前结果并节省下次计算的时间，请单击“保存规划求解”或“保存方案”选项。

44. “规划求解”完成信息 • 求解达到最大迭代次数后停止。 这表明在达到最大迭代次数时，没有得到满意的结果。增加迭代次数也许有用，但是应该先检查结果数值来确定问题的原因。如果要保存当前值并节省下次计算的时间，请单击“保存规划求解”或“保存方案”选项。 • 目标单元格中数值不收敛。 这表明即使满足全部约束条件，目标单元格数值也只是有增有减但不收敛。这可能是在设置问题时忽略了一项或多项约束条件。请检查工作表中的当前值，确定数值发散的原因，并且检查约束条件，然后再次求解。

45. “规划求解”完成信息 • “规划求解”未找到合适结果。 这表明在满足约束条件和精度要求的条件下，“规划求解”无法得到合理的结果，这可能是约束条件不一致所致。请检查约束条件公式或类型选择是否有误。

46. “规划求解”完成信息 • “规划求解”应用户要求而中止。 这表明在暂停求解过程之后，或在单步执行规划求解时，单击了“显示中间结果”对话框中的“停止”按钮。 • 无法满足设定的“采用线性模型”条件。 这表明求解时选中了“采用线性模型”复选框，但是最后计算结果并不满足线性模型。计算结果对工作表中的公式无效。如果要验证问题是否为非线性的，请选定“自动按比例缩放”复选框，然后再运行一次。如果又一次出现同样信息，请清除“采用线性模型”复选框，再运行一次。

47. “规划求解”完成信息 • “规划求解”在目标或约束条件单元格中发现错误值。 这表明在最近的一次运算中，一个或多个公式的运算结果有误。请找到包含错误值的目标单元格或约束条件单元格，修改其中的公式或内容，以得到合理的运算结果。 还有可能是在“添加约束”或“改变约束”对话框中键入了无效的名称或公式，或者在“约束”编辑框中直接键入了“integer”或“binary”。如果要将数值约束为整数，请在比较操作符列表中单击“Int”。如果要将数值约束为二进制数，请单击“Bin”。

48. “规划求解”完成信息 • 内存不够。 Microsoft Excel 无法获得规划求解所需的内存。请关闭一些文件或应用程序再试一次。 • 其它的 Excel 例程正在使用 SOLVER.DLL。 请表明有多个 Microsoft Excel 任务在运行，其中一个任务正在使用 SOLVER.DLL。SOLVER.DLL 同时只能供一个任务使用。

49. 关于报告 • 创建指定类型的报告，并将每份报告存放到工作簿中单独的一张工作表上。 • 运算结果报告 列出目标单元格和可变单元格以及它们的初始值、最终结果、约束条件和有关约束条件的信息。 • 敏感性报告 在“规划求解参数”对话框的“目标单元格”编辑框中所指定的公式的微小变化，以及约束条件的微小变化对求解结果都会有一定的影响。此报告提供关于求解结果对这些微小变化的敏感性的信息。含有整数约束条件的模型不能生成本报告。对于非线性模型，此报告提供缩减梯度和拉格朗日乘数；对于线性模型，此报告中将包含缩减成本、影子价格（机会成本）、目标系数(允许有小量增减额)以及右侧约束区域。 • 限制区域报告 列出目标单元格和可变单元格以及它们的数值、上下限和目标值。含有整数约束条件的模型不能生成本报告。下限是在满足约束条件和保持其它可变单元格数值不变的情况下，某个可变单元格可以取到的最小值。上限是在这种情况下可以取到的最大值。

50. 规划求解实例 • 下面，我们通过实例来演示规划求解的强大求解功能，我们将通过4个例子来说明如何利用规划求解功能求解线性规划、整数规划、0-1规划、非线性规划问题。