Excel提供了非常实用的数据分析工具.docx

Excel提供了非常实用的数据分析工具.docx

《Excel提供了非常实用的数据分析工具.docx》由会员分享，可在线阅读，更多相关《Excel提供了非常实用的数据分析工具.docx（19页珍藏版）》请在冰豆网上搜索。

Excel提供了非常实用的数据分析工具

Excel提供了非常实用的数据分析工具，利用这些分析工具，可解决财务管理中的许多问题，例如财务分析工具、统计分析工具、工程分析工具、规划求解工具、方案管理器等等。

下面介绍财务管理与分析中常用的一些数据分析工具。

2.4.1模拟运算表

模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。

在Excel中，可以构造两种模拟运算表：

单变量模拟运算表和多变量模拟运算表。

2.4.1.1单变量模拟运算表

单变量模拟运算表就是基于一个输入变量，用它来测试对公式计算结果的影响。

【例2-13】企业向银行贷款10000元，期限5年，则可以使用【模拟运算表】工具来测试不同的利率对月还款额的影响，步骤如下：

（1）设计模拟运算表结构，如图2-62所示。

图2-62单变量模拟运算表

（2）在单元格B4中输入公式“=PMT（A4/12,5*12,B1）”。

（3）选取包括公式和需要进行模拟运算的单元格区域A4:

B13。

（4）单击【数据】菜单，选择【模拟运算表】项，弹出【模拟运算表】对话框，如图2-63。

图2-63【模拟运算表】对话框

（5）由于本例中引用的是列数据，故在【输入引用列的单元格】中输入“$A$4”。

单击【确定】按钮，即得到单变量的模拟运算表，如图2-62所示。

2.4.1.2双变量模拟运算表

双变量模拟运算表就是考虑两个变量的变化对公式计算结果的影响，在财务管理中应用最多的是长期借款双变量分析模型，有关详细内容可参阅第3章的有关章节。

2.4.2单变量求解

单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。

单变量求解工具可以解决许多财务管理中涉及到一个变量的求解问题。

【例2-14】某企业拟向银行以7%的年利率借入期限为5年的长期借款，企业每年的偿还能力为100万元，那么企业最多总共可贷款多少？

设计如图2-64所示的计算表格，在单元格B2中输入公式“=PMT（B1,B3,B4）”，单击【工具】菜单，选择【单变量求解】项，则弹出【单变量求解】对话框，如图2-65所示，在【目标单元格】中输入“B2”，在【目标值】中输入“100”，在【可变单元格】中输入“$B$4”，然后单击【确定】按钮，则系统立即计算出结果，如图2-64所示，即企业最多总共可贷款410.02万元。

                                        图2-64贷款总额计算               图2-65【单变量求解】对话框

2.4.3规划求解

规划求解是Excel的一个非常有用的工具，不仅可以解决运筹学、线性规划等问题，还可以用来求解线性方程组及非线性方程组。

【规划求解】加载宏是Excel的一个可选安装模块，在安装MicrosoftExcel时，如果采用【典型安装】，则【规划求解】工具没有被安装，只有在选择【完全/定制安装】时才可选择安装这个模块。

在安装完成进入Excel后，单击【工具】菜单，选择【加载宏】项，在【加载宏】对话框中选定【规划求解】复选框，然后单击【确定】按钮，则系统就安装和加载【规划求解】工具，可以使用它了。

2.4.3.1求解优化问题

财务管理中涉及到很多的优化问题，如最大利润、最小成本、最优投资组合、目标规划、线性回归及非线性回归等等。

下面仅举一个简单的例子来说明利用规划求解工具解决最大利润的问题，有关规划求解工具的更多实际应用可参阅后面的有关章节。

【例2-15】某企业在某月份生产甲、乙两种产品，其有关资料如图2-66所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润？

利用规划求解工具求解这个问题的步骤如下：

图2-66产品有关资料及优化结果

1）首先建立优化模型，（设x和y分别表示甲产品和乙产品的生产量）：

目标函数：

max{销售利润}=（140－60）×x+（180－100）×y

约束条件：

6x+9y≤360

         7x+4y≤240

         18x+15y≤850

         y≤30

         x≥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所示，输入完毕一个约束条件后，单击【添加】按钮，则又弹出空白的【添加约束】对话框，再输入第二个约束条件。

当所有约束条件都输入完毕后，单击【确定】按钮，则系统返回到【规划求解参数】对话框。

图2-68【添加约束】对话框

如果发现输入的约束条件有错误，还可以对其进行修改，方法是：

选中要修改的约束条件，单击【更改】按钮，则系统弹出【改变约束】对话框，如图2-69所示，再进行修改即可。

图2-69【改变约束】对话框

输入完毕约束条件后，若还需要添加约束条件，单击【添加】按钮，在弹出的【添加约束】对话框中输入约束条件即可。

（7）如果需要，还可以设置有关的项目，即单击【选项】按钮，弹出【规划求解选项】对话框，如图2-70所示，对其中的有关项目进行设置即可；

图2-70【规划求解选项】对话框

（8）在建立好所有的规划求解参数后，单击【求解】，则系统将显示如图2-71所示的【规划求解结果】对话框，选择【保存规划求解结果】项，单击【确定】，则求解结果显示在工作表上，如图2-66所示。

图2-71【规划求解结果】对话框

（9）如果需要，还可以单击【规划求解结果】对话框中的【保存方案】，以便于对运算结果做进一步的分析。

2.4.3.2求解方程组

利用规划求解工具还可以求解线性或非线性方程组，下面举例说明：

【例2-16】有如下的非线性方程组：

则利用规划求解工具求解方程组的解步骤如下：

（1）设计工作表格，如图2-72所示；

图2-72利用规划求解工具求解方程组

（2）单元格E2：

E4为变动单元格，存放方程组的解，其初值可设为零（空单元格）；

（3）在单元格B2中输入求和公式“=3*E2^2+2*E3^2-2*E4-8”；在单元格B3中输入求和公式“=E2^2+（E2+1）*E3-3*E2+E4^2-5”；在单元格B4中输入求和公式“=E2*E4^2+3*E2+4*E3*E4-10”；

（4）可以任意选取一个方程的求和作为目标函数，而其它两个方程的求和作为约束条件，这里选取方程1的求和作为目标函数，方程2和方程3的求和作为约束条件，故在单元格C2中输入目标函数公式“=B2”；

（5）在【规划求解参数】对话框中，【设置目标单元格】设置为单元格“$C$2”；【等于】设置为“值为0”；【可变单元格】设置为“$E$2:

$E$4”；【约束】中添加“$B$3=0”、“$B$4=0”。

如有必要，还可以对“选项”的有关参数进行设置，如“迭代次数”、“精度”等，这里精度设置为10-11。

（7）单击【求解】，即可得到方程组的解，如图2-72所示。

利用规划求解工具还可以求解一元方程的解，此时仅有一个可变单元格，方法同上。

2.4.4方案分析

在企业的生产经营活动中，由于市场的不断变化，企业的生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响。

Excel提供了称为方案的工具来解决上述问题，利用其提供的方案管理器，可以很方便地对多种方案（即多个假设条件）进行分析。

下面结合实例来说明如何使用方案管理器进行方案分析和管理。

【例2-17】某企业生产产品A、产品B、产品C，在2003年的销售额分别为200万元、400万元和300万元，销售成本分别为120万元、280万元和160万元。

根据市场情况推测，2004年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如图2-73所示。

图2-73产品销售资料及预计增长率

2.4.4.1建立方案

根据以上资料，建立分析方案：

（1）单击工作表的任一单元格，激活工作表，并设计方案计算分析格式，如图2-73所示，并在单元格G7中输入公式“=SUMPRODUCT（B3:

B5,1+G4:

G6）-

SUMPRODUCT（C3:

C5,1+H4:

H6）”。

（2）将可变单元格分别进行命名，即单元格G4的名字为“产品A销售额增长率”，单元格H4的名字为“产品A销售成本增长率”，单元格G5的名字为“产品B销售额增长率”，单元格H5的名字为“产品B销售成本增长率”，单元格G6的名字为“产品C销售额增长率”，单元格H6的名字为“产品C销售成本增长率”，单元格G7的名字为“总销售利润”。

（3）单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图2-74所示，单击【添加】按钮，系统弹出【添加方案】对话框，如图2-75所示。

                               图2-74【方案管理器】对话框           图2-75【添加方案】对话框

（4）在【添加方案】对话框中，【方案名】编辑框中输入“方案1销售好”，【可变单元格】编辑框中输入“$G$4:

$H$6”，单击【确定】按钮，系统弹出【方案变量值】对话框，如图2-76所示；

图2-76【方案变量值】对话框

（5）在【方案变量值】对话框中输入每个可变单元格的值（这里要按行输入），完毕后单击【添加】按钮，系统会弹出如图2-75所示的【添加方案】对话框，对第2个方案进行输入；待所有方案输入完毕后，单击【方案变量值】对话框中的【确定】按钮，系统返回到【方案管理器】对话框，如图2-77所示。

此时，可单击【关闭】按钮，回到工作表。

图2-77方案建立完毕后的【方案管理器】对话框

2.4.4.2显示方案

方案制定好后，任何时候都可以执行方案，查看不同的执行结果，方法如下：

（1）打开原工作表，并激活工作表。

（2）单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图2-77所示，选择要想查看的方案，单击【显示】按钮，则系统就自动显示出该方案的执行结果，如图2-73所示。

2.4.4.3修改、删除或增加方案

对做好的方案进行修改，只需在图2-77所示的【方案管理器】对话框中选中需要修改的方案，单击【编辑】按钮，系统弹出如图2-76所示的对话框，进行相应的修改即可。

若要删除某一方案，则在图2-77所示的【方案管理器】对话框中选中需要删除的方案，单击【删除】按钮。

若要增加方案，则在图2-77所示的【方案管理器】对话框中单击【添加】按钮，然后在图2-75所示的对话框填写相关的项目。

2.4.4.4建立方案报告

当需要将所有的方案执行结果都显示出来时，可建立方案报告，方法如下：

（1）在图2-77所示的【方案管理器】对话框中单击【总结】按钮，弹出【方案总结】对话框，如图2-78所示，在【结果类型】中选择“方案总结”项，在【结果单元格】中输入“G7”，然后单击【确定】按钮，则系统在当前工作簿中自动建立一个名为“方案总结”的工作表，如图2-79所示。

图2-78【方案总结】对话框

图2-79方案报告

2.4.5数据分析工具库

Excel提供了一组数据分析工具，称为“分析工具库”它包括方差分析、相关系数分析、协方差分析、描述统计分析、指数平滑分析、F-检验、傅里叶分析、直方图分析、移动平均分析、随机数发生器、排位与百分比排位、回归分析、抽样分析、t-检验、z-检验等，利用这些数据分析工具，可为实际的财务管理工作提供很大帮助，解决许多实际问题，例如财务预测问题。

有关数据分析工具库在财务管理中的应用，将在以后的有关章节中陆续予以介绍。

.1 模拟运算表

   所谓模拟运算表实际上是工作表中的一个单元格区域，它可以显示一个计算公式中某些参数的值的变化对计算结果的影响。

由于它可以将所有不同的计算结果以列表方式同时显示出来，因而便于查看、比较和分析。

根据分析计算公式中的参数的个数，模拟运算表又分为单变量模拟运算表和双变量模拟运算表。

4.1.1 单变量模拟运算表

  单变量模拟运算主要用来分析当其它因素不变时，一个参数的变化对目标值的影响。

例如，要计算一笔贷款的分期偿还额，可以使用Excel2000提供的财务函数之一PMT。

而如果要分析不同的利率对贷款的偿还额产生的影响，则可以使用单变量模拟运算表。

  假设某公司要贷款1000万元，年限为10年，目前的年利率为5％，分月偿还。

则利用PMT函数可以计算出每月的偿还额。

其具体操作步骤如下：

  在工作表中输入有关参数，如图4－1所示。

  在B5单元格输入计算月偿还额的公式：

“=PMT（B3/12,B4*12,B2）”

  在上述公式中，PMT函数有三个参数。

第一个参数是利率，因为要计算的偿还额是按月计算的，所以要将年利率除以12，将其转换成月利率。

第二个参数是还款期数，同样的原因需要乘以12。

第三个参数为贷款额。

该函数的计算结果为“-106065.52”，即在年利率为5%，年限为10年的条件下，需每月偿还106065.52元

请注意，这时单元格区域B8:

B16中的公式为“{=表（,B3）}”，表示其是一个以B3为列变量的模拟运算表。

与一般的计算公式相似，当改变模拟数据时，模拟运算表的数据会自动重新计算。

  除了用于贷款分析之外，函数PMT还可以计算出别的以年金方式付款的支付额。

例如，如果需要以按月定额存款方式在20年中存款100000，假设存款年利率为4%，则函数PMT可以用来计算月存款额：

“＝PMT（4%/12,20*12,0,100000）”，公式计算结果为“272.65”。

即向年利率4%的存款账户每月存入272.65元，20年后连本带利可获得100000元。

4.1.2 双变量模拟运算表

    当需要其它因素不变时，两个参数的变化对目标值的影响时，需要使用双变量模拟运算表。

例如上例，如果不仅要考虑利率的变化，还可以选择贷款年限，这时需要分析不同的利率和不同的贷款期限对贷款的偿还额的影响，这时需要使用双变量模拟运算表。

  双变量模拟运算表的操作步骤与单变量模拟运算表类似：

  选择某个单元格区域作为模拟运算表存放区域，在该区域的最左列输入假设的利率变化范围数据；在该区域的第一行输入可能的贷款年限数据。

  在模拟运算表区域的左上角单元格输入计算月偿还额的计算公式。

  选定整个模拟运算表区域。

如图4－5所示。

单击数据菜单中的模拟运算表命令。

  在模拟运算表对话框的输入引用行的单元格框中输入“$B$4”；在输入引用列的单元格框中输入“$B$3”。

单击确定。

  双变量模拟运算表的计算结果如图4－6所示。

其中B8:

F16单元格区域的计算公式为“{=表（B4,B3）}”，表示其是一个以B4为行变量，B3为列变量的模拟运算表

4.1.3 敏感分析

   利用模拟运算表还可以进一步进行其他方面的敏感分析。

下面通过购买某个险种的保险时如何选择缴款方式，来说明有关敏感分析的操作。

   设准备购买某保险10万元，可以有两种缴款方式供选择：

一种是趸交，即一次付清105,490元；另一种是分30年付款，每年付6,350元。

如果单从付款额来说，后一种付款方式累计缴款190,500元，大大多于趸交的款额。

但是对于这种长期投资问题，还必须要考虑利息的收益和利率变动的影响。

为此，可以利用Excel2000提供的现值函数PV或未来值函数FV，计算和比较在特定利率情况下两者的收益。

再进一步应用模拟运算表分析利率变动的影响。

   首先将有关数据输入到工作表中，再利用PV和FV函数计算分期付款方式在特定年利率情况下的现值和未来值。

这里设年利率为5％，则计算结果如图4－7所示

从计算结果可以看出，在年利率为5％的情况下，分期付款方式相当于现在一次付款102,495元。

也就是说，在年利率为5％的情况下，采用分期付款方式较好。

  近几年来，国家为了宏观调控经济的发展，曾多次调整银行利率。

为了比较不同利率对保险收益的影响，可建立以年利率3.50％～5.50％为行模拟数据的模拟运算表。

如图4－8所示。

从模拟运算表中可以看出，当年利率为4.75时，两种缴款方式效果近似，当年利率低于4.75时，宜采用趸交方式；而高于4.75时，宜采用分期付款方式。

或者说，如果有其他年利率大于4.75元的投资途径时，采用分期付款方式可以获得更好的收益。

  如果还要考查不同支付额的影响，可以使用双变量模拟运算表。

图4－9是以年利率3.50％～5.50％为行模拟数据，支付额6,150～6,550为列模拟数据的双变量模拟运算表。

在模拟运算表的基础上，还可以进一步进行敏感分析。

可以通过改变除行变量和列变量以外其他参数的值，分析其对模拟运算表计算结果的影响；而改变函数名称，则可以方便地得到其他相关指标的的模拟运算表。

  例如，在上例的现值分析中，年限都是30年，如果要考查年限为15年或是25年时，各模拟数据的变动情况，可以直接修改年限数据，这时整个模拟运算表会自动重新计算。

图4－10即年限为20年时的双变量模拟运算表。

果要分析这笔投资30年后的效益，可以使用FV函数计算其未来值。

显然未来值的计算也是同利率、付款额和年限等参数相关。

这里只需在原来模拟运算表的基础上，将原来的计算公式中的函数名由“PV”改成“FV”即可。

图4－11即为有关分期付款方式未来值的模拟运算表。

如果保险回报与之相比过低，而且风险不大时，可以考虑采取其他投资方式。

4.2 方案分析

  模拟运算表主要用来考查一个或两个决策变量的变动对于分析结果的影响，但对于一些更复杂的问题，常常需要考查更多的因素。

例如为了达到公司的预算目标，可以从多种途径入手。

可以通过增加广告促销，可以提高价格增收，可以降低包装费、材料费，可以减少非生产开支等等。

利用Excel2000提供的方案管理器，可以模拟为达到目标而选择的不同方式。

对于每个变量改变的结果都被称之为一个方案，根据多个方案的对比分析，可以考查不同方案的优劣，从中选择最合适公司目标的方案。

  例如图4－12所示的是思创公司1999年1月的损益表，其中包括了各项指标的计算公式。

管理人员希望分析，通过增加销售收入，减少生产费用，降低销售成本等措施对公司利润总额的影响。

这可以利用Excel2000的方案工具进行分析，主要包括下述操作。

4.2.1 创建方案

创建方案是方案分析的关键，应根据实际问题的需要和可行性来创建一组方案。

在创建方案之前，为了使创建的方案能够明确地显示有关变量，以及为了将来进行方案总结时便于阅读方案总结报告，需要先给有关变量所在的单元格命名。

其具体操作步骤是：

  在存放有关变量数据的单元格右侧单元格中输入相应指标的名称。

  选定要命名的单元格区域和单元格名称区域。

如图4－13所示。

单击插入菜单中的名称命令，然后单击指定子命令。

  这时将出现指定名称对话框，如图4-14所示在名称在框中选定最右列复选框，单击确定按钮。

  此时方案分析中需要用到的C3:

C15单元格全部被用D3:

D15单元格的内容命名。

这时可按下述步骤逐个创建所需的方案。

  单击工具菜单中的方案命令，将弹出方案管理器对话框。

由于现在还没有任何方案，所以方案管理器对话框中间显示“未定义方案”的信息。

根据提示，单击添加按钮。

  出现添加方案对话框，如图4－15所示。

在方案名框中键入方案的名称，这里键入“增加收入”。

然后指定销售收入和营业外收入所在的单元格为可变单元格，单击确定。

   出现方案变量值对话框，如图4－16所示。

框中显示原来的数据。

在相应的框中键入模拟数值。

单击确定。

  “增加收入”方案创建完毕，相应的方案自动添加到方案管理器的方案列表中。

  按照上述4.2.2 浏览、编辑方案

   方案创建好以后，可以根据需要查看每个方案对利润总额数据的影响。

其具体操作步骤是：

  在方案管理器对话框的方案列表中，选定要查看的方案。

  单击方案管理器对话框的显示按钮，再单击确定。

  这时工作表中将显示该模拟方案的计算结果。

  如果需要修改某个方案，其具体操作步骤是：

  在方案管理器对话框的方案列表中，选定要修改的方案。

  单击方案管理器对话框的编辑按钮。

  这时会弹出与添加方案一样的编辑方案对话框。

可以根据需要修改方案名称，改变可变单元格以及重新输入可变单元格的变量值。

4.2.3 方案总结

   上述浏览方式只能一个方案一个方案地查看，如果将所有方案汇总到一个工作表中，然后再对不同方案的影响比较分析，这对于帮助决策人员综合考查各种方案效果更好。

Excel2000的方案工具可以根据需要对多个方案创建方案总结，以便决策人员做出更明智的决策。

具体操作步骤如下：

   单击工具菜单中的方案命令，将弹出方案管理器对话框。

   单击方案管理器对话框中的总结按钮，将弹出方案总结对话框，如图4－18所示。

步骤再依次建立“减少费用”和“降低成本”两个方案

根据需要在方案总结对话框中选择适当的结果类型，一般情况下可选择方案总结，如果需要对报告进一步分析，可选方案数据透视表。

在结果单元格框中指定利润总额所在的单元格C15。

单击确定按钮。

 在方案总结中，“当前值”列显示的是在建立方案汇总时，可变单元格原来的数值。

每组方案的可变单元格均以灰色底纹突出显示。

根据各方案的模拟数据计算出的目标值也同时显示在总结中（单元格区域D13:

G13），便于管理人员比较分析。

比较三个方案的结果单元格“利润总额”的数值，可以看出“降低成本”方案效果最好，“减少费用”方案次之，“增加收入”方案对目标值的影响最小。

4.3 目标搜索

  “What-If”分析方法主要采用模拟计算的方法解决不同因素或不同方案对目标的影响。

这对于计划人员、决策人员都是常用的工具。

但是对于生产的组织和实施人员来说，经常遇到的是相反的问题。

例如，根据上机有关部门制定的某个目标，分析要实现该目标，需要实现的具体指标，再逐一落实。

当然也可以根据每个具体指标，进一步分析要达到的更详细的指标。

在进行这样的分析时，往往由于计算方法较为复杂或是许多因素交织在一起而很难进行。

这可以利用Excel2000