Excel高级教程

Excel高级教程－Excel分析工具Excel分析工具•假设分析是指观察改变单元格的数值对工作表中公式所产生结果的影响。例如：当偿还期限改变时对每月按揭额的影响。•利用Excel提供的假设分析功能（包括单变量求解、模拟运算表，规划求解及方案等），并结合Excel强大的函数库，可以方便轻松地完成各种财务统计等工作。Excel分析工具•单变量求解•模拟运算表–单变量模拟运算表–双变量模拟运算表•规划求解–线形规划–整数规划–0-1规划–非线形规划•方案管理单变量求解•如果已知公式的结果，而不知道公式的变量值，那么可以用单变量求解来寻求公式的特定解。进行单变量求解时，Excel通过一定的算法进行试算，直到找到合适的解。•说白了，就是解单变量的方程。单变量求解•单变量求解的具体操作如下：–公式编辑。–单击“工具”菜单中的“单变量求解”命令。这时将出现“单变量求解”对话框。–在“目标单元格”编辑框中，输入待求解的公式所在的单元格的引用。–在“目标值”编辑框中，键入所需的结果。–在“可变单元格”编辑框中，输入待调整数值（变量）所在单元格的引用。–单击确定可得到结果。单变量求解•示例解方程•在单元格A2中输入公式“＝A1*8+44”，A1作为存放变量x的单元格。100448x单变量求解•单击“工具”菜单中的“单变量求解”命令。这时将出现“单变量求解”对话框。•在“目标单元格”编辑框中，输入A2。•在“目标值”编辑框中，键入100。•在“可变单元格”编辑框中，输入A1。单变量求解•单击确定可得到结果。单变量求解•这时，有同学就要问，这么简单的问题，何必杀鸡用牛刀？•事实上，很多方程根本就无法人工解出来，必须依靠计算机来解决。这时Excel就是一个很好的工具。•比如就够你受的了。更别说更复杂的了。利用Excel很快就可以计算出x＝5.739552102模拟运算表•模拟运算表是工作表中的一个单元格区域，它可以显示公式中某些值的变化对计算结果的影响。模拟运算表为同时求解某一运算中所有可能的变化值提供了捷径，并且，它还可以将所有不同的计算结果同时显示在工作表中，便于查看和比较。模拟运算表•单变量模拟运算表•单变量模拟运算表的结构特点是，其输入数值被排列在一列中（列引用）或一行中（行引用）。单变量模拟运算表中使用的公式必须引用输入单元格。存放在输入单元格中的输入数据清单将被替换。工作表中的任何单元格都可以用作输入单元格。虽然输入单元格不必是模拟运算表的一部分，但模拟运算表中的公式必须引用输入单元格。模拟运算表•单变量模拟运算表•创建单变量模拟运算表，具体操作如下：–在一行或者一列中输入要替换工作表上的输入单元格的数值序列；–如果输入数值被排成一列（行），则在第一个数值的上一行（左边一列）且处于数值序列右侧（下方）的单元格中输入所需的公式。–选定包含公式和需要被替换的数值的单元格区域。–在“数据”菜单中，单击“模拟运算表”命令；–如果模拟运算表是列方向的，请在“输入引用列的单元格”编辑框中，为输入单元格键入引用。如果是行，相应做即可。模拟运算表•单变量模拟运算表下面我们通过绘制y＝sinx在[0,2Pi]的曲线来说明单变量模拟运算表的操作。绘制曲线，首先要算出函数在各个点的取值，然后画出各个点的散点图。模拟运算表•单变量模拟运算表1、如下图准备数据2、选中区域C3：D20模拟运算表•单变量模拟运算表3、单击“数据”－“模拟运算表”，弹出如下对话框，如图示设置列引用。4、单击“确定“，得到如下结果。模拟运算表•单变量模拟运算表5、用图表向导，作出散点图。模拟运算表•双变量模拟运算表•双变量模拟运算表与单变量模拟运算表的区别在于前者使用两个可变单元格。双变量模拟运算表中的两组输入数值使用同一个公式。这个公式必须使用两个不同的输入单元格。模拟运算表•双变量模拟运算表•创建双变量模拟运算表的具体操作如下：–在工作表的某个单元格中，输入所需的至少引用两个单元格的公式；–在公式的下面同一列中键入一组待替换的变量序列，在公式右边同一行键入待替换的变量序列；–选中包含公式以及数据行和列的单元格区域；–在“数据”菜单中，单击“模拟运算表…”命令；–在“输入引用行的单元格”编辑框中，输入要由行变量序列替换的输入单元格的引用；–在“输入引用列的单元格”编辑框中，输入要由列变量序列替换的输入单元格的引用；–单击“确定”得到所需的结果。模拟运算表•双变量模拟运算表•下面，我们通过制作一个乘法口诀表来说明双变量模拟运算表的操作过程。1、按照如下格式准备数据模拟运算表•双变量模拟运算表2、在单元格C3中输入“=B4&×&B5&=&B4*B5”模拟运算表•双变量模拟运算表3、选中区域C3：L12模拟运算表•双变量模拟运算表4、单击“数据”－“模拟运算表”，弹出如下对话框，如图示设置行引用和列引用。注意：这里行引用和列引用不能是上一步选择的区域中的任何一个单元格，否则将出错。单变量模拟运算表则无此限。模拟运算表•双变量模拟运算表5、单击“确定”，得到一种漂亮的乘法口诀表。模拟运算表•模拟运算表的应用实例分期付款（按揭）是当代比较流行的支付方式，下面我们将同过两个实例来说明单变量模拟运算表和双变量模拟运算表的应用。模拟运算表•示例一某人贷款10万元，欲分4年（48月）偿还，试求贷款按揭利率在8％～13％之间变化时，每月应等额偿还多少钱？模拟运算表•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）模拟运算表•从该表我们可以看出，当年利率8％～13％之间变化时，每月的偿还额从￥2411上升到￥2682。这就为想在4年内支付完一辆现在价格为10万元的汽车的消费者提供一个支付能力的参考。模拟运算表•示例二贷款10万元，当贷款年利率在8％～13％之间变化时，在各种利率下，还款期限为5、10、15、20年时所对应的每月偿还额是多少？模拟运算表•运算结果表明，每月的偿还额从2500到6825之间变化，年利率越高，月偿还额越大，期限越长，月偿还额月小。如果预期的月薪不到2500，就别奢望拥有现价为30万的房子了!规划求解•运筹学是经济管理过程中常用的工具之一。经济管理往往被一定的运筹模型来表达，但是人工求解比较困难，特别对于变量个数比较多的规划模型，人工求解简直就是痴心妄想!于是，人们就开始寻求机械的解法，特别是运用计算机来求解。现在已经有很多软件可以实现规划求解，如SAS、Malab、Excel。今天，我们介绍一下如何用Excel来求解运筹学中的规划问题。能够解决的规划问题包括：线性规划、目标规划、整数规划、非线性规划。规划求解Max(min)Z=C1X1+C2X2+…+CnXna11X1+a12X2+…+a1nXn(=,)b1a21X1+a22X2+…+a2nXn(=,)b2………am1X1+am2X2+…+amnXn(=,)bmXj0(j=1,…,n)规划求解•第一步，按照下面的格式，把目标函数、约束条件的两边分别用n个变量的表达式或以这些变量为参数的函数表示。规划求解•单击工具－加载宏，弹出下面的对话框，把规划求解选项钩上，单击确定。（这一步只是在第一次使用的时候需要，目的是把规划求解的模块加进来。）•单击工具－规划求解…，弹出对话框，把目标函数、最大或者最小、约束条件设置好，就可以求解了。“规划求解参数”对话框上各选项说明选项说明目标单元格在此指定目标单元格，经求解后获得最大值、最小值或者某一特定数值。这个单元格必须包含公式等于在此指定是否需要对目标单元格求取最大值、最小值或者某一特定数值。如果需要指定数值，请在右侧编辑框中键入可变单元格在此指定可变单元格。求解时其中的值不断调整，直到满足约束条件，并且“目标单元格”编辑框中指定的单元格达到目标值。可变单元格必须直接或间接与目标单元格相联系推测单击此钮，自动定位“目标单元格”编辑框中公式引用的所有非公式单元格，并在“可变单元格”编辑框中输入其引用约束在此列出当前的所有约束条件添加显示“添加约束”对话框更改显示“改变约束”对话框“规划求解参数”对话框上各选项说明（续）选项说明删除删除选定的约束条件求解对定义好的问题进行求解关闭关闭对话框，不进行规划求解。但保留通过“选项”、“添加”、“更改”或“删除”按钮所作的修改选项显示“规划求解选项”对话框。在其中装入或保留规划求解模型，并对求解运算的高级属性进行设定全部重设清除规划求解中的当前设置，将所有的设置恢复为初始值设置“规划求解”选项设置“规划求解”选项选项说明最长运算时间在此设定求解过程的时间，可输入的最大值为32767秒，默认值为100秒可以满足大多数小型规划求解要求迭代次数在此设定求解过程中迭代运算的次数，限制求解过程的时间。可输入的最大时间为32767，默认值为100次可满足大多数小型规划求解要求精度在此输入用于控制求解精度的数字，以确定约束条件单元格中的数值是否满足目标值或上下限。精度值必须为小数（0-1之间），输入数字的小数位越少，精度越低。精度越高，求解时间越长。允许误差在此输入满足整数约束条件的目标单元格求解结果与最佳结果间的允许百分偏差。这个选项只应用于具有整数约束条件的问题。设置的允许误差值越大，求解过程就越快。收敛度在此输入收敛度数值，当最近五次迭代时，目标单元格中数值的变化小于“收敛度”编辑框中设置的数值时，“规划求解”停止运行。收敛度只应用于非线性规划问题，并且必须由一个在0（零）和1之间的小数表示。设置的数值越小，收敛度就越高。例如，0.0001表示比0.01更小的相对差别。收敛度越小，“规划求解”得到结果所需的时间就越长。选项说明搜索指定每次的迭代算法，以确定搜索方向。牛顿法用准牛顿法迭代需要的内存比共轭法多，但所需的迭代次数少。共轭法比牛顿法需要的内存少，但要达到指定精度需要较多次的迭代运算。当问题较大或内存有限，或单步迭代进程序缓慢时，用此选项。装入模型显示“装入模型”对话框，输入对所要调入模型的引用。保存模型显示“保存模型”对话框，输入模型的保存位置。只有当需要在工作表上保存多个模型时，单击此命令。第一个模型会自动存储。设置“规划求解”选项（续1）设置“规划求解”选项（续2）选项说明正切函数使用正切向量线性外推。二次方程用二次函数外推法，提高非线性规划问题的计算精度。导数指定用于估计目标函数和约束函数偏导数的差分方案。向前差分用于大多数约束条件数值变化相对缓慢的问题。中心差分用于约束条件变化迅速，特别是接近限定值的问题。虽然此选项要求更多的计算，但在“规划求解”不能返回有效解时也许会有帮助。选项说明采用线性模型当模型中的所有关系都是线性的，并且希望解决线性优化问题时，选中此复选框可加速求解进程显示迭代结果如果选中此复选框，每进行一次迭代后都将中断“规划求解”，并显示当前的迭代结果。自动按比例缩放当输入和输出值数量差别很大时，可以使用此功能。例如，对一项百万美元投资的盈利百分比进行放大。假定非负对于在“添加约束”对话框的“约束值”编辑框中没有设置下限的可变单元格，假定其下限为0（零）。估计指定在每个一维搜索中用来得到基本变量初始估值的逼近方案。设置“规划求解”选项（续3）“规划求解”完成信息•当“规划求解”得到答案时，它将在“规划求解结果”对话框中显示下述两条信息之一：–“规化求解”找到一个解，可满足所有的约束及最优化要求。这表明按照“规划求解选项”对话框中设置的精度，所有约束条件都已满足，并且只要有可能，目标单元格中将得到极大值或极小值。–“规划求解”收敛于当前结果，并满足全部约束条件。