Excel在财务管理与分析中的应用基础知识.pdf

《Excel在财务管理与分析中的应用基础知识.pdf》由会员分享，可在线阅读，更多相关《Excel在财务管理与分析中的应用基础知识.pdf（81页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1/81 Excel 在财务管理与分析中的应用基础知识 2.1 公式与函数的高级应用(1)公式和函数是 Excel 最基本、最重要的应用工具，是 Excel 的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。2.1.1 数组公式与其应用 数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。在数组公式中使用两组或多组数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。数组公式中的每个数组参数必须有相同数量的行和列。2.1.1.1 数组公式的输入、编辑与删除 1数组公式的输入 数组公式的输入步骤如下：（1）选定单元格或单元格区域。如果数组公式将返回一个结果，单击需

2、要输入数组公式的单元格；如果数组公式将返回多个结果，则要选定需要输入数组公式的单元格区域。（2）输入数组公式。（3）同时按“Crtl+Shift+Enter”组合键，则 Excel 自动在公式的两边加上大括号 。2/81 特别要注意的是，第（3）步相当重要，只有输入公式后同时按“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组公式。否则，如果只按 Enter 键，则输入的只是一个简单的公式，也只在选中的单元格区域的第 1 个单元格显示出一个计算结果。在数组公式中，通常都使用单元格区域引用，但也可以直接键入数值数组，这样键入的数值数组被称为数组常量。当不想在工作表中按单元格逐

3、个输入数值时，可以使用这种方法。如果要生成数组常量，必须按如下操作：（1）直接在公式中输入数值，并用大括号“”括起来。（2）不同列的数值用逗号“,”分开。（3）不同行的数值用分号“;”分开。输入数组常量的方法：例如，要在单元格 A1:D1 中分别输入 10，20，30 和 40 这 4 个数值，则可采用下述的步骤：（1）选取单元格区域 A1:D1，如图 2-1 所示。图 2-1 选取单元格区域 A1:D1 3/81（2）在公式编辑栏中输入数组公式“=10,20,30,40”，如图 2-2 所示。图 2-2 在编辑栏中输入数组公式（3）同时按 Ctrl+Shift+Enter 组合键，即可在单元

4、格 A1、B1、C1、D1 中分别输入了 10、20、30、40，如图 2-3 所示。假若要在单元格 A1、B1、C1、D1、A2、B2、C2、D2 中分别输入10、20、30、40、50、60、70、80，则可以采用下述的方法：图 2-3 同时按 Ctrl+Shift+Enter 组合键，得到数组常量（1）选取单元格区域 A1:D2，如图 2-4 所示。图 2-4 选取单元格区域 A1:D2（2）在编辑栏中输入公式“=10,20,30,40;50,60,70,80”，如图 2-5 所示。4/81 图 2-5 在编辑栏中输入数组公式（3）按 Ctrl+Shift+Enter 组合键，就在单元格

5、 A1、B1、C1、D1、A2、B2、C2、D2 中分别输入了 10、20、30、40 和 50、60、70、80，如图 2-6 所示。图 2-6 同时按 Ctrl+Shift+Enter 组合键，得到数组常量 输入公式数组的方法 例如，在单元格 A3:D3 中均有相同的计算公式，它们分别为单元格 A1:D1 与单元格 A2:D2 中数据的和，即单元格 A3 中的公式为“=A1+A2”，单元格 B3 中的公式为“=B1+B2”，则可以采用数组公式的方法输入公式，方法如下：（1）选取单元格区域 A3:D3，如图 2-7 所示。（2）在公式编辑栏中输入数组公式“=A1:D1+A2:D2”，如图 2

6、-8所示。5/81 图 2-7 选取单元格区域 A3:D3 图 2-8 在编辑栏中输入数组公式（3）同时按 Ctrl+Shift+Enter 组合键，即可在单元格 A3:D3 中得到数组公式“=A1:D1+A2:D2”，如图 2-9 所示。图 2-9 同时按 Ctrl+Shift+Enter 组合键，得到数组公式 2.1 公式与函数的高级应用(2)2编辑数组公式 数组公式的特征之一就是不能单独编辑、清除或移动数组公式所涉与的单元格区域中的某一个单元格。若在数组公式输入完毕后发现错误需要修改，则需要按以下步骤进行：（1）在数组区域中单击任一单元格。6/81（2）单击公式编辑栏，当编辑栏被激活时，

7、大括号“”在数组公式中消失。（3）编辑数组公式内容。（4）修改完毕后，按“Crtl+Shift+Enter”组合键。要特别注意不要忘记这一步。3删除数组公式 删除数组公式的步骤是：首先选定存放数组公式的所有单元格，然后按Delete 键。2.1.1.2 数组公式的应用 1用数组公式计算两个数据区域的乘积【例 2-1】如图 2-10 所示，已经知道 12 个月的销售量和产品单价，则可以利用数组公式计算每个月的销售额，步骤如下：图 2-10 用数组公式计算销售额（1）选取单元格区域 B4:M4。（2）输入公式“=B2:M2*B3:M3”。7/81（3）按“Crtl+Shift+Enter”组合键。

8、如果需要计算 12 个月的月平均销售额，可在单元格 B5 中输入公式“=AVERAGE(B2:M2*B3:M3)”，然后按“Crtl+Shift+Enter”组合键即可，如图 2-10 所示。在数组公式中，也可以将某一常量与数组公式进行加、减、乘、除，也可以对数组公式进行乘幂、开方等运算。例如在图 2-10 中，每月的单价相同，故我们也可以在单元格 B4:M4 中输入公式“=B2:M2*28”，然后按“Crtl+Shift+Enter”组合键；在单元格 B5 中输入公式“=AVERAGE(B2:M2*28)”，然后按“Crtl+Shift+Enter”组合键。在使用数组公式计算时，最好将不同的

9、单元格区域定义不同的名称，如在图 2-10 中，将单元格区域 B2:M2 定义名称为“销售量”，单元格区域 B3:M3 定义名称为“单价”，则各月的销售额计算公式为“=销售量*单价”，月平均销售额计算公式为“=AVERAGE(销售量*单价)”，这样不容易出错。2用数组公式计算多个数据区域的和 如果需要把多个对应的行或列数据进行相加或相减的运算，并得出与之对应的一行或一列数据时，也可以使用数组公式来完成。【例 2-2】某企业 2002 年销售的 3 种产品的有关资料如图 2-11 所示，则可以利用数组公式计算该企业 2002 年的总销售额，方法如下：8/81 图 2-11 某企业的月销售总额计算

10、（1）选取单元格区域 C8:N8。（2）输入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7”。（3）按“Crtl+Shift+Enter”组合键。3用数组公式同时对多个数据区域进行相同的计算【例 2-3】某公司对现有三种商品实施降价销售，产品原价如图 2-12所示，降价幅度为 20%，则可以利用数组公式进行计算，步骤如下：图 2-12 产品降价计算（1）选取单元格区域 G3:I8。（2）输入公式“=B3:D8*(1-20%)”。（3）按 Crtl+Shift+Enter 组合键。9/81 此外，当对结构相同的不同工作表数据进行合并汇总处理时，利用上述方法也将是非

11、常方便的。有关不同工作表单元格的引用可参阅第 1 章的有关内容，关于数据的合并计算可参阅本章 2.3.5 节的内容。2.1.2 常用函数与其应用 在第 1 章中介绍了一些有关函数的基本知识，本节对在财务管理中常用的一般函数应用进行说明，其他有关的专门财务函数将在以后的有关章节中分别予以介绍。2.1.2.1 SUM 函数、SUMIF 函数和 SUMPRODUCT 函数 在财务管理中，应用最多的是求和函数。求和函数有三个：无条件求和SUM 函数、条件求和 SUMIF 函数和多组数据相乘求和 SUMPRODUCT函数。1无条件求和 SUM 函数 该函数是求 30 个以内参数的和。公式为=SUM（参数

12、 1，参数 2，参数 N）当对某一行或某一列的连续数据进行求和时，还可以使用工具栏中的自动求和按钮。例如，在例 2-1 中，求全年的销售量，则可以单击单元格 N2，然后再单击求和按钮，按回车键即可，如图 2-13 所示。10/81 图 2-13 自动求和 2条件求和 SUMIF 函数 SUMIF 函数的功能是根据指定条件对若干单元格求和，公式为=SUMIF(range,criteria,sum_range)式中 range用于条件判断的单元格区域；criteria确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本；sum_range需要求和的实际单元格。只有当 range 中的相

13、应单元格满足条件时，才对 sum_range 中的单元格求和。如果省略 sum_range,则直接对 range 中的单元格求和。利用这个函数进行分类汇总是很有用的。【例 2-4】某商场 2 月份销售的家电流水记录如图 2-14 所示，则在单元格 I3 中输入公式“=SUMIF(C3:C10,211,F3:F10)”，单元格 I4 中输入公式“=SUMIF(C3:C10,215,F3:F10)”，在单元格 I5 中输入公式“=SUMIF(C3:C10,212,F3:F10)”，单元格 I6 中输入公式“=SUMIF(C3:C10,220,F3:F10)”，即可得到分类销售额汇总表。11/81

14、图 2-14 商品销售额分类汇总 SUMIF 函数的对话框如图 2-15 所示。图 2-15 SUMIF 函数对话框 当需要分类汇总的数据很大时，利用 SUMIF 函数是很方便的。3SUMPRODUCT 函数 SUMPRODUCT 函数的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。公式为=SUMPRODUCT(array1,array2,array3,)式中，array1,array2,array3,.为 1 至 30 个数组。需注意的是，数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值#VALUE!。对于非数值型的数组元素将作为 0 处理。12

15、/81 例如，在例 2-2 中，要计算 2002 年产品 A 的销售总额，可在任一单元格（比如 O2）中输入公式“=SUMPRODUCT(C2:N2,C3:N3)”即可 2.1 公式与函数的高级应用(3)2.1.2.2 AVERAGE 函数 AVERAGE 函数的功能是计算给定参数的算术平均值。公式为=AVERAGE（参数 1，参数 2，参数 N）函数中的参数可以是数字，或者是涉与数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。但是，如果单元格包含零值则计算在内。AVERAGE 函数的使用方法与 SUM 函数相同，此处不再介绍。2.1.2.3 MIN 函

16、数和 MAX 函数 MIN 函数的功能是给定参数表中的最小值，MAX 函数的功能是给定参数表中的最大值。公式为=MIN（参数 1，参数 2，参数 N）=MAX（参数 1，参数 2，参数 N）函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。例如，MIN（3，5，12，32）=3；MAX（3，5，12，32）=32。13/81 2.1.2.4 COUNT 函数和 COUNTIF 函数 COUNT 函数的功能是计算给定区域内数值型参数的数目。公式为=COUNT（参数 1，参数 2，参数 N）COUNTIF 函数的功能是计算给定区域内满足特定条件的单元格的数目。公式为=COUNTIF（r

17、ange，criteria）式中 range需要计算其中满足条件的单元格数目的单元格区域；criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。COUNT 函数和 COUNTIF 函数在数据汇总统计分析中是非常有用的函数。2.1.2.5 IF 函数 IF 函数也称条件函数，它根据参数条件的真假，返回不同的结果。在实践中，经常使用函数 IF 对数值和公式进行条件检测。公式为=IF(logical_test,value_if_true,value_if_false)式中 logical_test条件表达式，其结果要么为 TRUE，要么为 FALSE，它可使用任何比较运算

18、符；14/81 value_if_truelogical_test 为 TRUE 时返回的值；value_if_falselogical_test 为 FALSE 时返回的值。IF 函数在财务管理中具有非常广泛的应用。【例 2-5】例如，某企业对各个销售部门的销售业绩进行评价，评价标准与各个销售部门在 2002 年的销售业绩汇总如图 2-16 所示，评价计算步骤如下：图 2-16 销售部门业绩评价（1）选定单元格区域 C3:C12。（2）直接输入以下公式：“=IF(B3:B12100000,差,IF(B3:B12200000,一般,IF(B3:B12300000,好,IF(B3:B124000

19、00,较好,很好)”。（3）按“Crtl+Shift+Enter”组合键。则各个销售部门的销售业绩评价结果就显示在单元格域 C3:C12 中。15/81 也可以直接在单元格 C3 中输入公式“=IF(B3100000,差,IF(B3200000,一般,IF(B3300000,好,IF(B3300000,C3/B3$H$20”，这里要特别注意：必须以绝对引用的方式引用销售额平均值，以相对引用的方式引用数据清单中的数据。（3）按照前面介绍的步骤进行高级筛选，其中高级筛选的数据区域为$A$2:$G$16；高级筛选的条件区域为$B$19:$C$20，则筛选结果如图2-48 所示。2.3.3 数据的分类

20、与汇总 在对数据进行分析时，常常需要将相同类型的数据统计出来，这就是数据的分类与汇总。在对数据进行汇总之前，应特别注意的是：首先必须对要汇总的关键字进行排序。54/81 2.3.3.1 进行分类汇总 例如，在例 2-11 中，要按地区进行自动分类汇总，其步骤如下：（1）首先对“地区”进行排序，排序方法见前面所述。（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【分类汇总】项，系统弹出如图 2-49 所示的【分类汇总】对话框。图 2-49 【分类汇总】对话框（3）在【分类汇总】对话框中，【分类字段】选项下选择“地区”，【汇总方式】选项下选择“求和”，【选定汇总项】选项下选

21、定“数量”和“金额”，单击【确定】按钮，则分类汇总的结果如图 2-50 所示。55/81 图 2-50 按地区分类汇总结果 在图 2-50 中，左上角有 3 个按钮，按钮 1 表示 1 级汇总，显示全部的销售数量和销售金额汇总；按钮 2 表示 2 级汇总，显示各地区的全部销售数量和销售金额汇总；按钮 3 表示 3 级汇总，显示各地区的销售数量和销售金额的汇总明细与汇总额（即图 2-50 所示的汇总结果）。图 2-50 中，左边的滑动按钮 为隐藏明细按钮，单击此按钮，则将隐藏本级的明细数据，同时 变为显示明细按钮，再单击 按钮，则将显示本级的全部明细数据，同时 变为。在上述自动分类汇总的结果上，

22、还可以再进行分类汇总，例如再进行另一种分类汇总，两次分类汇总的关键字可以相同，也可以不同，其分类汇总方法与前面的是一样的，此处不再介绍。2.3 数据分析处理(4).3.3.2 分类汇总的撤消 56/81 如果不再需要分类汇总结果，可在图 2-49 所示的【分类汇总】对话框中单击【全部删除】，即可撤消分类汇总。2.3.4 数据透视表 数据透视表是用于快速汇总大量数据的交互式表格，用户可以旋转其行或列以查看对源数据的不同汇总，也可以通过显示不同的页来筛选数据，还可以显示所关心区域的数据明细。通过对源数据表的行、列进行重新排列，使得数据表达的信息更清楚明了。2.3.4.1 建立数据透视表 以例 2-

23、11 的数据为例，建立数据透视表的步骤如下：（1）首先，要保证数据源是一个数据清单或数据库，即数据表的每列必须有列标。（2）单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【数据透视表和图表报告】项，则系统弹出【数据透视表和数据透视图向导3 步骤之 1】对话框，如图 2-51 所示，根据待分析数据来源与需要创建何种报表类型，进行相应的选择，然后单击【下一步】按钮，系统弹出【数据透视表和数据透视图向导3 步骤之 2】对话框，如图 2-52 所示；57/81 图 2-51 【数据透视表和数据透视图向导3 步骤之 1】对话框 图 2-52 【数据透视表和数据透视图向导3 步骤之 2

24、】对话框（3）默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，则可直接输入“选定区域”，或单击【浏览】按钮，从其他的文件中提取数据源。确定数据源后，单击【下一步】按钮，系统弹出【数据透视表和数据透视图向导3 步骤之 3】对话框，如图 2-53所示。图 2-53 【数据透视表和数据透视图向导3 步骤之 3】对话框 58/81（4）在【数据透视表和数据透视图向导3 步骤之 3】对话框中，单击【版式】按钮，出现【数据透视表和数据透视图向导版式】对话框，如图 2-54 所示。（5）【数据透视表和数据透视图向导版式】对话框中，再根据需要，将右边的字段按钮拖到左边的图上，这里，将

25、“销售人员”拖到“行(R)”图上，将“商品”拖到“列(C)”图上，将“数量（台）”和“金额（元）”拖到“数据(D)”图上，如图 2-55 所示。图 2-54 【数据透视表和数据透视图向导版式】对话框 图 2-55 设置数据透视表的版式 59/81（6）设置好版式后，单击【确定】按钮，则系统就返回到图 244 所示的【数据透视表和数据透视图向导3 步骤之 3】对话框，然后单击【完成】按钮，数据透视表就完成了，如图 2-56 所示。这样，通过图 2-56 的数据透视表，即可看出每个销售人员所销售商品的种类、数量、销售额与其合计数，从而以此为基础可很方便地对每个销售人员的销售业绩进行评价。图 2-5

26、6 各个销售人员销售商品的数据透视表 2.3.4.2 数据的透视分析 在图 2-56 所建立的数据透视表上，可以很方便地进行多角度的统计与分析。比如要了解李四所销售商品的情况，可在“销售人员”下拉列标中只选中“李四”，然后单击“确定”按钮，则李四的销售情况如图 2-57所示。60/81 图 2-57 李四的销售情况汇总 还可以建立透视图，方法是：单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【数据透视图】项，则系统自动显示出数据透视图，从而得到每个销售人员的更为直观的销售情况。2.3 数据分析处理(5)2.3.4.3 数据更新 当数据清单中的数据发生变化时，需要对数据透视表进行更

27、新，方法是：单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【更新数据】项，也可在数据透视表的【数据】菜单中选择【更新数据】项。2.3.4.4 显示数据项的明细数据 要想查看数据透视表中某数据项的明细数据，只需双击该数据项即可。若要查看某一种商品的销售明细，比如要查看彩电的销售明细，可双击数据透视表中的数据项“彩电”，系统弹出【显示明细数据】对话框，选择需要显示的数据字段，单击【确定】按钮即可。2.3.5 数据的合并计算 在很多情况下，企业的销售数据分门分类记录，到年末时需要汇总计算到一张年度报表上，这时最简单的方法就是对它们进行合并计算，尤其是61/81 在需要合并的数据很多时，

28、利用合并计算工具能节省很大的工作量，并且也不容易出错。现举一个简单的例子来说明合并计算的方法和步骤。【例 2-12】某企业四个季度的销售量统计如图 2-58 所示，它们分别存放在不同的工作簿销售统计-1.xls，销售统计-2.xls，销售统计-3.xls 和销售统计-4.xls 中，现要将它们汇总合并到另外一张名为销售统计-年度.xls）的工作簿中，则合并计算步骤如下：图 2-58 某企业四个季度的销售量统计（1）在“销售统计-年度.xls”的工作簿中选取单元格区域 B3:B9。（2）单击【数据】菜单，选取【合并计算】项，则弹出【合并计算】对话框，如图 2-59 所示。62/81 图 2-59

29、 【合并计算】对话框（3）在【函数】项中选“求和”。（4）在【引用位置】中输入第一季度销售统计工作簿“销售统计-1.xls”的数据区域 B3:B9，最好用鼠标选取，即：激活工作簿“销售统计-1.xls”，然后选取该工作表的 B3:B9 单元格区域，输入完毕后，单击【添加】按钮，则该单元格区域自动加入【所有引用位置】中去，并在【引用位置】的文字呈反黑显示。（5）仿照上述方法输入其他三个季度的资料，最后的结果如图 2-59所示。（6）选中【创建连至数据源的连接】，这是为了能够查看最终汇总数据的来源，若没有这个要求，可不选此项。（7）单击【确定】按钮，则最后的合并计算结果如图 2-60 所示。图 2

30、-60 合并计算结果 63/81 在图 2-60 中，左上角的按钮 1 为总的合并数据的显示按钮，按钮 2为所有合并数据的明细显示按钮。在每个合并数据的左边有一个滑动按钮，单击此按钮，则将在此合并数据的上方显示隐藏的合并明细数据，同时 变为显示明细按钮，再单击此按钮，则合并明细数据再度隐藏起来，同时 变为。若不选中【创建连至数据源的连接】，则合并计算结果如图 2-61 所示。图 2-61 合并计算结果 需要说明的是，合并计算并不意味着只是简单的求和汇总（SUM 函数），还包括求平均值（AVERAGE 函数）、求最大值（MAX 函数）、求最小值（MIN 函数）、求对应单元格的乘积（PRODUCT

31、 函数）、计数（COUNT 函数）、求标准差（STDDEV 函数）、求总体标准差（STDDEVP函数）、求方差（VAR 函数）、求总体方差（VARP 函数）等运算。2.4 数据分析工具的应用(1)Excel 提供了非常实用的数据分析工具，利用这些分析工具，可解决财务管理中的许多问题，例如财务分析工具、统计分析工具、工程分析工具、规划求解工具、方案管理器等等。下面介绍财务管理与分析中常用的一些数据分析工具。2.4.1 模拟运算表 64/81 模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。在 Excel 中，可以构造两种模拟运算表：单变量模拟运

32、算表和多变量模拟运算表。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 【模拟运算表】对话

33、框 65/81（5）由于本例中引用的是列数据，故在【输入引用列的单元格】中输入“$A$4”。单击【确定】按钮，即得到单变量的模拟运算表，如图 2-62 所示。2.4.1.2 双变量模拟运算表 双变量模拟运算表就是考虑两个变量的变化对公式计算结果的影响，在财务管理中应用最多的是长期借款双变量分析模型，有关详细内容可参阅第 3 章的有关章节。2.4.2 单变量求解 单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。单变量求解工具可以解决许多财务管理中涉与到一个变量的求解问题。【例 2-14】某企业拟向银行以 7%的年利率借入期限为 5 年的长期借款，企业每年的偿还能

34、力为 100 万元，那么企业最多总共可贷款多少？设计如图 2-64 所示的计算表格，在单元格 B2 中输入公式“=PMT(B1,B3,B4)”，单击【工具】菜单，选择【单变量求解】项，则弹出【单变量求解】对话框，如图 2-65 所示，在【目标单元格】中输入“B2”，在【目标值】中输入“100”，在【可变单元格】中输入“$B$4”，然后单击【确定】按钮，则系统立即计算出结果，如图 2-64 所示，即企业最多总共可贷款 410.02 万元。66/81 图 2-64 贷款总额计算 图 2-65 【单变量求解】对话框 2.4.3 规划求解 规划求解是 Excel 的一个非常有用的工具，不仅可以解决运筹

35、学、线性规划等问题，还可以用来求解线性方程组与非线性方程组。【规划求解】加载宏是 Excel 的一个可选安装模块，在安装 Microsoft Excel 时，如果采用【典型安装】，则【规划求解】工具没有被安装，只有在选择【完全/定制安装】时才可选择安装这个模块。在安装完成进入 Excel 后，单击【工具】菜单，选择【加载宏】项，在【加载宏】对话框中选定【规划求解】复选框，然后单击【确定】按钮，则系统就安装和加载【规划求解】工具，可以使用它了。2.4.3.1 求解优化问题 财务管理中涉与到很多的优化问题，如最大利润、最小成本、最优投资组合、目标规划、线性回归与非线性回归等等。下面仅举一个简单的例

36、子来说明利用规划求解工具解决最大利润的问题，有关规划求解工具的更多实际应用可参阅后面的有关章节。67/81【例 2-15】某企业在某月份生产甲、乙两种产品，其有关资料如图 2-66 所示，则企业应如何安排两种产品的产销组合，使企业获得最大销售利润？利用规划求解工具求解这个问题的步骤如下：图 2-66 产品有关资料与优化结果.4 数据分析工具的应用(2)1）首先建立优化模型，（设 x 和 y 分别表示甲产品和乙产品的生产量）：目标函数：max销售利润=(14060)x+(180100)y 约束条件：6x+9y 360 7x+4y 240 18x+15y 850 y 30 68/81 x 0,y

37、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）单击【工具】菜单，选择【规划求解】项，则系统弹出【规划

38、求解参数】对话框，如图 2-67。图 2-67 【规划求解参数】对话框（6）在【规划求解参数】对话框中，【设置目标单元格】中输入“$B$12”；【等于】选“最大值”；【可变单元格】中输入“$B$11:$C$11”；在【约束】中添加以下的约束条件：“$B$11:$C$11=整数”、“$B69/81$11:$C$11=0”、“$B$14=$E$3”、“$B$15=$E$4”、“$B$16=$E$5”、“$B$11=$C$9”；这里，添加约束条件的方法是：单击【添加】按钮，系统会弹出【添加约束】对话框，如图 2-68 所示，输入完毕一个约束条件后，单击【添加】按钮，则又弹出空白的【添加约束】对话框，

39、再输入第二个约束条件。当所有约束条件都输入完毕后，单击【确定】按钮，则系统返回到【规划求解参数】对话框。图 2-68 【添加约束】对话框 如果发现输入的约束条件有错误，还可以对其进行修改，方法是：选中要修改的约束条件，单击【更改】按钮，则系统弹出【改变约束】对话框，如图 2-69 所示，再进行修改即可。图 2-69 【改变约束】对话框 输入完毕约束条件后，若还需要添加约束条件，单击【添加】按钮，在弹出的【添加约束】对话框中输入约束条件即可。70/81（7）如果需要，还可以设置有关的项目，即单击【选项】按钮，弹出【规划求解选项】对话框，如图 2-70 所示，对其中的有关项目进行设置即可；图 2-

40、70 【规划求解选项】对话框（8）在建立好所有的规划求解参数后，单击【求解】，则系统将显示如图 2-71 所示的【规划求解结果】对话框，选择【保存规划求解结果】项，单击【确定】，则求解结果显示在工作表上，如图 2-66 所示。图 2-71 【规划求解结果】对话框（9）如果需要，还可以单击【规划求解结果】对话框中的【保存方案】，以便于对运算结果做进一步的分析。2.4.3.2 求解方程组 利用规划求解工具还可以求解线性或非线性方程组，下面举例说明：71/81【例 2-16】有如下的非线性方程组：则利用规划求解工具求解方程组的解步骤如下：（1）设计工作表格，如图 2-72 所示；图 2-72 利用规

41、划求解工具求解方程组（2）单元格 E2：E4 为变动单元格，存放方程组的解，其初值可设为零（空单元格）；（3）在单元格 B2 中输入求和公式“=3*E22+2*E32-2*E4-8”；在单元格 B3 中输入求和公式“=E22+(E2+1)*E3-3*E2+E42-5”；在单元格 B4 中输入求和公式“=E2*E42+3*E2+4*E3*E4-10”；（4）可以任意选取一个方程的求和作为目标函数，而其它两个方程的求和作为约束条件，这里选取方程 1 的求和作为目标函数，方程 2 和方程3 的求和作为约束条件，故在单元格 C2 中输入目标函数公式“=B2”；（5）在【规划求解参数】对话框中，【设置目

42、标单元格】设置为单元格“$C$2”；【等于】设置为“值为 0”；【可变单元格】设置为“$E$2:$E$4”；【约束】中添加“$B$3=0”、“$B$4=0”。如有必要，72/81 还可以对“选项”的有关参数进行设置，如“迭代次数”、“精度”等，这里精度设置为 10-11。（7）单击【求解】，即可得到方程组的解，如图 2-72 所示。利用规划求解工具还可以求解一元方程的解，此时仅有一个可变单元格，方法同上。2.4 数据分析工具的应用(3)2.4.4 方案分析 在企业的生产经营活动中，由于市场的不断变化，企业的生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响。Excel

43、 提供了称为方案的工具来解决上述问题，利用其提供的方案管理器，可以很方便地对多种方案（即多个假设条件）进行分析。下面结合实例来说明如何使用方案管理器进行方案分析和管理。【例 2-17】某企业生产产品 A、产品 B、产品 C，在 2003 年的销售额分别为 200 万元、400 万元和 300 万元，销售成本分别为 120 万元、280 万元和 160 万元。根据市场情况推测，2004 年产品的销售情况有好、一般和差三种情况，每种情况下的销售额与销售成本的增长率如图 2-73所示。73/81 图 2-73 产品销售资料与预计增长率 2.4.4.1 建立方案 根据以上资料，建立分析方案：（1）单击

44、工作表的任一单元格，激活工作表，并设计方案计算分析格式，如图 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 的名字为“总销售利润”。74/81

45、（3）单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图 2-74 所示，单击【添加】按钮，系统弹出【添加方案】对话框，如图 2-75 所示。图 2-74 【方案管理器】对话框 图 2-75 【添加方案】对话框（4）在【添加方案】对话框中，【方案名】编辑框中输入“方案 1 销售好”，【可变单元格】编辑框中输入“$G$4:$H$6”，单击【确定】按钮，系统弹出【方案变量值】对话框，如图 2-76 所示；图 2-76 【方案变量值】对话框（5）在【方案变量值】对话框中输入每个可变单元格的值（这里要按行输入），完毕后单击【添加】按钮，系统会弹出如图 2-75 所示的【添加方案】对话

46、框，对第 2 个方案进行输入；待所有方案输入完毕后，单击75/81【方案变量值】对话框中的【确定】按钮，系统返回到【方案管理器】对话框，如图 2-77 所示。此时，可单击【关闭】按钮，回到工作表。图 2-77 方案建立完毕后的【方案管理器】对话框 2.4.4.2 显示方案 方案制定好后，任何时候都可以执行方案，查看不同的执行结果，方法如下：（1）打开原工作表，并激活工作表。（2）单击【工具】菜单，选择【方案】项，系统弹出【方案管理器】对话框，如图 2-77 所示，选择要想查看的方案，单击【显示】按钮，则系统就自动显示出该方案的执行结果，如图 2-73 所示。2.4.4.3 修改、删除或增加方案

47、 对做好的方案进行修改，只需在图 2-77 所示的【方案管理器】对话框中选中需要修改的方案，单击【编辑】按钮，系统弹出如图 2-76 所示的对话框，进行相应的修改即可。76/81 若要删除某一方案，则在图 2-77 所示的【方案管理器】对话框中选中需要删除的方案，单击【删除】按钮。若要增加方案，则在图 2-77 所示的【方案管理器】对话框中单击【添加】按钮，然后在图 2-75 所示的对话框填写相关的项目。2.4.4.4 建立方案报告 当需要将所有的方案执行结果都显示出来时，可建立方案报告，方法如下：（1）在图 2-77 所示的【方案管理器】对话框中单击【总结】按钮，弹出【方案总结】对话框，如图

48、 2-78 所示，在【结果类型】中选择“方案总结”项，在【结果单元格】中输入“G7”，然后单击【确定】按钮，则系统在当前工作簿中自动建立一个名为“方案总结”的工作表，如图 2-79 所示。图 2-78 【方案总结】对话框 77/81 图 2-79 方案报告 2.4.5 数据分析工具库 Excel 提供了一组数据分析工具，称为“分析工具库”它包括方差分析、相关系数分析、协方差分析、描述统计分析、指数平滑分析、F-检验、傅里叶分析、直方图分析、移动平均分析、随机数发生器、排位与百分比排位、回归分析、抽样分析、t-检验、z-检验等，利用这些数据分析工具，可为实际的财务管理工作提供很大帮助，解决许多实

49、际问题，例如财务预测问题。有关数据分析工具库在财务管理中的应用，将在以后的有关章节中陆续予以介绍。2.5 宏与 VBA 的初步应用 在有些情况下，我们需要建立自定义函数或子程序来解决某些问题。利用自定义函数不仅使用方便，而且也不容易出错。Excel 提供的 Visual Basic 编辑器可以帮助解决这个问题。下面举例说明建立自定义函数的方法和步骤。2.5.1 建立自定义函数【例 2-18】建立不允许缺货且陆续均衡供货和消耗情况下的经济订货批量自定义函数。经济订货批量的计算公式为 78/81 则经济订货批量自定义函数的建立方法和步骤如下：（1）单击【工具】菜单，选择【宏】项，在【宏】项的子菜单

50、中选择【Visual Basic 编辑器】，打开 Visual Basic 编辑器窗口，再单击 Visual Basic 编辑器窗口的【插入】菜单，选择【模块】项，则显示模块 1的窗口，如图 2-80 所示。图 2-80 准备创建模块（2）在模块 1 窗口中，单击【插入】菜单，选择【过程】项，如图 2-81 所示，则系统弹出【添加过程】对话框，如图 2-82 所示。图2-81 插入过程 图2-82 【添加过程】对话框 79/81（3）在【添加过程】对话框中，【名称】栏中输入“经济订货批量”，【类型】选“函数”，单击【确定】按钮，出现编辑过程页面。（4）将 Public Function 经济订