Excel高级教程

目 录

第一节 EXCEL公式及函数的高级应用

数组公式及其应用 ………………………………………………………….………3

1

1.1

1.1.1 数组公式的输入、编辑及删除 ………………………………………..…………3

一． 数组公式的输入 ………………………………………….….…………………3

二． 编辑数组公式………………………………………………..…..…….…………4

三． 删除数组公式…………………………………………………………..…………5

1.1.2 数组公式的应用……………………………………………………….…….……..5

一． 用数组公式计算两个数据区域的乘积………………………………….……….5

二． 用数组公式计算多个数据区域的和………………………….……...…..……….5

三． 用数组公式同时对多个数据区域进行相同的计算………………...…..……….6

1.2 常用函数及其应用………………………………………………………...….….……6

1.2.1 SUM函数、SUMIF函数和SUMPRODUCT函数 …………………….….……6

一．无条件求和SUM函数…………………………………………………….………6

二．条件求和SUMIF函数……………………………………………………..………6

三．SUMPRODUCT函数………………………………………………….…….…….7

1.2.2 AVERAGE函数……………………………………………….…….……………….7

2

1.2.3 MIN函数和MAX函数……………………………………….……..………..……7

1.2.4 COUNT函数和COUNTIF函数…………………………….………..………..….7

1.2.5 IF函数………………………………………………………….………..……..……8

1.2.6 AND函数、OR函数和NOT函数…………………………………………..…….8

1.2.7 LOOKUP函数、VLOOKUP函数和HLOOKUP函数……………………...…..9

一．LOOKUP函数……………………………………………………..……………….9

二．VLOOKUP函数…………………………………………………………………….9

三．HLOOKUP函数 …………………………………………..………..…….…..….10

1.2.8 MATCH函数 ……………………………………………………..……….…..…10

1.2.9 INDEX函数……………………………………………………………………...….10

一． 返回数组中指定单元格或单元格数组的数值…………………………..….…..10

二． 返回引用中指定单元格 ………………………………………..…….……...…11

1.2.10 ADDRESS函数…………………………………………………..….…………..11

1.2.11 INDIRECT函数……………………………………………..…….…………….11

3

1.2.12 矩阵函数——TRANSPOSE函数、MINVERSE函数和MMULT函数.….…..12

一．TRANSPOSE函数……………………………………………….…………..……12

二．MINVERSE函数………………………………………………………….….…..12

三．MMULT函数 ………………………………………………………………….…12

1.2.13 ROUND函数 ………………………………………………….……………..….12

第二节 EXCEL数据分析处理

2.1 数据排序………………………………………………………………………..…..13

2.1.1 数据排序的规那么………………………………………………….……………..….13

2.1.2 数据排序步骤…………………………………………………….……………..….13

2.1.3 自定义排序………………………………………………………………….…..….14

2.2 数据的查找与筛选…………………………………………..…………….…..….14

2.2.1 记录单查找…………………………………………………………………..…….15

一． 查找数据记录 ……………………………………………..……………..…….15

二． 修改或删除记录………………………………………………………………….15

4

三． 添加新的记录………………………………………………………….…………15

2.2.2 自动筛选与自定义筛选………………………………………………………….15

一． 自动筛选…………………………………………………………….…….………15

二． 自定义筛选方式………………………………………………….….……………16

2.2.3 高级筛选…………………………………………………………….…..…………….16

一．一般情况下的高级筛选………………………………………….…..…………….17

二．计算条件情况下的高级筛选 ………………………………….….………………17

2.3 数据的分类与汇总…………………………………………….…..………………18

2.3.1 进行分类汇总………………………………………….....……………..…….……18

2.3.2 分类汇总的撤消……………………………………..……..…………..…….…….19

2.4 数据透视表………………………………………………………………..………..19

2.4.1 建立数据透视表……………………………………………………………….……19

2.4.2 数据的透视分析……………………………………..……………………..……….20

第三节 EXCEL图表处理

5

3.1 图表类型…………………………………………………………………….………….21

3.2 图表的建立………………………………………………….…………….……………21

3.3 图表的编辑、修改及格式化……………………………….………….………………23

一．设置坐标、标题、图例等的格式………………………………………………..23

二．改变图表大小………………………………………….…………….…………….23

三．移动或复制图表…………………………………………………………….…….23

四．添加数据标志……………………………………………………...……….….….23

五．改变图表颜色、图案、边框…………………………………………..………….24

3.4 地区销售分布图表的建立……………………………………………..……………..24

3.5 动态图表的建立……………………………………………………….…….………..25

第四节 EXCEL数据分析工具的应用

4.1 模拟运算表………………………………………………………………………….26

4.1.1 单变量模拟运算表…………………………………………………………………26

4.1.2 双变量模拟运算表……………………………………..…………………………..27

6

4.2 单变量求解…………………………………………….…………………………..27

4.3 规划求解…………………………………………….……………….……………..27

4.3.1 求解优化问题……………………………………….………..…………………….27

4.3.2 求解方程组 ………………………………………..………………………………29

4.4 方案分析………………………………………….…………………………………30

4.4.1 建立方案 ……………………………………………..…………………….………30

4.4.2 显示方案…………………………………………………………….……………….31

4.4.3 修改、删除或增加方案…………………………………………………………….31

4.4.4 建立方案报告……………………………………………………………………….31

4.5 数据分析工具库…………………………………...………………………………...32

第一节 EXCEL公式及函数的高级应用

公式和函数是Excel最根本、最重要的应用工具，是Excel的核心，因此，应对公式和函数熟练掌握，才能在实际应用中得心应手。

1.1 数组公式及其应用

7

数组公式就是可以同时进行多重计算并返回一种或多种结果的公式。在数组公式中使用两组或多组数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。数组公式中的每个数组参数必须有相同数量的行和列。

1.1.1 数组公式的输入、编辑及删除

一．数组公式的输入

数组公式的输入步骤如下：

(1〕选定单元格或单元格区域。如果数组公式将返回一个结果，单击需要输入数组公式的单元格；如果数组公式将返回多个结果，那么要选定需要输入数组公式的单元格区域。

(2〕输入数组公式。

(3〕同时按“Crtl+Shift+Enter〞组合键，那么Excel 自动在公式的两边加上大括号{ } 。

特别要注意的是，第〔3〕步相当重要，只有输入公式后同时按“Crtl+Shift+Enter〞组合键，系统才会把公式视为一个数组公式。否那么，如果只按Enter键，那么输入的只是一个简单的公式，也只在选中的单元格区域的第1个单元格显示出一个计算结果。

在数组公式中，通常都使用单元格区域引用，但也可以直接键入数值数组，这样键入的数值数组被称为数组常量。当不想在工作表中按单元格逐个输入数值时，可以使用这种方法。如果要生成数组常量，必须按如下操作：

〔1〕直接在公式中输入数值，并用大括号“{ }〞括起来。

8

〔2〕不同列的数值用逗号“,〞分开。

〔3〕不同行的数值用分号“;〞分开。

★ 输入数组常量的方法：

例如，要在单元格A1:D1中分别输入10，20，30和40这4个数值，那么可采用下述的步骤：

〔1〕选取单元格区域A1:D1，如图2-1所示。

图1-1 选取单元格区域A1:D1

〔2〕在公式编辑栏中输入数组公式“={10,20,30,40}〞，如图2-2所示。

图1-2 在编辑栏中输入数组公式

〔3〕同时按Ctrl+Shift+Enter组合键，即可在单元格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，那么可以采用下述的方法：

9

图1-3 同时按Ctrl+Shift+Enter组合键，得到数组常量

〔1〕选取单元格区域A1:D2，如图2-4所示。

图1-4 选取单元格区域A1:D2

〔2〕在编辑栏中输入公式“={10,20,30,40;50,60,70,80}〞，如图2-5所示。

图1-5 在编辑栏中输入数组公式

〔3〕按Ctrl+Shift+Enter组合键，就在单元格A1、B1、C1、D1、A2、B2、输入了10、20、30、40和50、60、70、80，如图2-6所示。

图1-6 同时按Ctrl+Shift+Enter组合键，得到数组常量

★ 输入公式数组的方法

10

C2、D2中分别

例如，在单元格A3:D3中均有相同的计算公式，它们分别为单元格A1:D1与单元格A2:D2中数据的和，即单元格A3中的公式为“=A1+A2”，单元格B3中的公式为“=B1+B2”，…，那么可以采用数组公式的方法输入公式，方法如下：

〔1〕选取单元格区域A3:D3，如图2-7所示。

〔2〕在公式编辑栏中输入数组公式“=A1:D1+A2:D2”，如图2-8所示。

图1-7 选取单元格区域A3:D3

图1-8 在编辑栏中输入数组公式

〔3〕同时按Ctrl+Shift+Enter组合键，即可在单元格A3:D3“=A1:D1+A2:D2”，如图2-9所示。

图1-9 同时按Ctrl+Shift+Enter组合键，得到数组公式

中得到数组公式11

二．编辑数组公式

数组公式的特征之一就是不能单独编辑、去除或移动数组公式所涉及的单元格区域中的某一个单元格。假设在数组公式输入完毕后发现错误需要修改，那么需要按以下步骤进行：

〔1〕在数组区域中单击任一单元格。

〔2〕单击公式编辑栏，当编辑栏被激活时，大括号“{ }〞在数组公式中消失。

〔3〕编辑数组公式内容。

〔4〕修改完毕后，按“Crtl+Shift+Enter〞组合键。要特别注意不要忘记这一步。

三．删除数组公式

删除数组公式的步骤是：首先选定存放数组公式的所有单元格，然后按Delete键。

1.1.2 数组公式的应用

一．用数组公式计算两个数据区域的乘积

【例2-1】如图2-10所示，已经知道12个月的销售量和产品单价，那么可以利用数组公式计算每个月的销售额，步骤如下：

12

图1-10 用数组公式计算销售额

〔1〕选取单元格区域B4:M4。

〔2〕输入公式“=B2:M2*B3:M3”。

〔3〕按“Crtl+Shift+Enter〞组合键。

如果需要计算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〞组合键。

在使用数组公式计算时，最好将不同的单元格区域定义不同的名称，如在图2-10中，将单元格区域B2:M2定义名称为“销售量〞，单元格区域B3:M3定义名称为“单价〞，那么各月的销售额计算公式为“=销售量*单价〞，月平均销售额计算公式为“=AVERAGE(销售量*单价)〞，这样不容易出错。

二．用数组公式计算多个数据区域的和

如果需要把多个对应的行或列数据进行相加或相减的运算，并得出与之对应的一行或一列数据时，也可以使用数组公式来完成。

【例2-2】某企业2002年销售的3种产品的有关资料如图2-11所示，那么可以利用数组公式计

13

算该企业2002年的总销售额，方法如下：

图1-11 某企业的月销售总额计算

〔1〕选取单元格区域C8:N8。

〔2〕输入公式“=C2:N2*C3:N3+C4:N4*C5:N5+C6:N6*C7:N7”。

〔3〕按“Crtl+Shift+Enter〞组合键。

三．用数组公式同时对多个数据区域进行相同的计算

【例2-3】某公司对现有三种商品实施降价销售，产品原价如图2-12所示，降价幅度为20%，那么可以利用数组公式进行计算，步骤如下：

图1-12 产品降价计算

〔1〕选取单元格区域G3:I8。

14

〔2〕输入公式“=B3:D8*(1-20%)〞。

〔3〕按Crtl+Shift+Enter组合键。

此外，当对结构相同的不同工作表数据进行合并汇总处理时，利用上述方法也将是非常方便的。有关不同工作表单元格的引用可参阅第1章的有关内容，关于数据的合并计算可参阅本章2.3.5节的内容。

1.2 常用函数及其应用

在第1节中介绍了一些有关函数的根本知识，本节对在财务管理中常用的一般函数应用进行说明，其他有关的专门财务函数将在以后的有关章节中分别予以介绍。

1.2.1 SUM函数、SUMIF函数和SUMPRODUCT函数

在财务管理中，应用最多的是求和函数。求和函数有三个：无条件求和SUM函数、条件求和SUMIF函数和多组数据相乘求和SUMPRODUCT函数。

一．无条件求和SUM函数

该函数是求30个以内参数的和。公式为 = SUM〔参数1，参数2，…，参数N〕

当对某一行或某一列的连续数据进行求和时，还可以使用工具栏中的自动求和按钮。

例如，在例2-1中，求全年的销售量，那么可以单击单元格N2，然后再单击求和按钮，按回车键即可，如图1-13所示。

15

图1-13 自动求和

二．条件求和SUMIF函数

SUMIF函数的功能是根据指定条件对假设干单元格求和，公式：

=SUMIF(range,criteria,sum_range)

式中 range—用于条件判断的单元格区域；

criteria—确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本；

sum_range—需要求和的实际单元格。

只有当range中的相应单元格满足条件时，才对 sum_range 中的单元格求和。假设省略 sum_range,那么直接对 range 中的单元格求和。

利用这个函数进行分类汇总是很有用的。

【例1-4】某商场2月份销售的家电流水记录如图1-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)〞，即可得到分类销售额汇总表。

16

图1-14 商品销售额分类汇总

SUMIF函数的对话框如图1-15所示。

图1-15 SUMIF函数对话框

当需要分类汇总的数据很大时，利用SUMIF函数是很方便的。

三．SUMPRODUCT函数

SUMPRODUCT函数的功能是在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。

公式为 = SUMPRODUCT(array1,array2,array3,…)

式中，array1,array2,array3,...为1至30个数组。

需注意的是，数组参数必须具有相同的维数，否那么，函数 SUMPRODUCT 将返回错误值

17

#VALUE!。对于非数值型的数组元素将作为0处理。

例如，在例1-2中，要计算2002年产品A的销售总额，可在任一单元格〔比方O2〕中输入公式“=SUMPRODUCT(C2:N2,C3:N3)〞即可。

1.2.2 AVERAGE函数

AVERAGE函数的功能是计算给定参数的算术平均值。

公式为 = AVERAGE〔参数1，参数2，…，参数N〕

函数中的参数可以是数字，或者是涉及数字的名称、数组或引用。如果数组或单元格引用参数中有文字、逻辑值或空单元格，那么忽略其值。但是，如果单元格包含零值那么计算在内。

AVERAGE函数的使用方法与SUM函数相同，此处不再介绍。

1.2.3 MIN函数和MAX函数

MIN函数的功能是给定参数表中的最小值，MAX函数的功能是给定参数表中的最大值。公式为

= MIN〔参数1，参数2，…，参数N〕

= MAX〔参数1，参数2，…，参数N〕

函数中的参数可以是数字、空白单元格、逻辑值或表示数值的文字串。

例如，MIN〔3，5，12，32〕=3；MAX〔3，5，12，32〕=32。

18

1.2.4 COUNT函数和COUNTIF函数

COUNT函数的功能是计算给定区域内数值型参数的数目。

公式为： = COUNT〔参数1，参数2，…，参数N〕

COUNTIF函数的功能是计算给定区域内满足特定条件的单元格的数目。

公式为： = COUNTIF〔range，criteria〕

式中 range—需要计算其中满足条件的单元格数目的单元格区域；

criteria—确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。

COUNT函数和COUNTIF函数在数据汇总统计分析中是非常有用的函数。

1.2.5 IF函数

IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。在实践中，经常使用函数IF对数值和公式进行条件检测。

公式为 = IF(logical_test,value_if_true,value_if_false)

式中 logical_test—条件表达式，其结果要么为 TRUE，要么为 FALSE，它可使用任何比拟运算符；

value_if_true—logical_test 为 TRUE 时返回的值；

19

value_if_false—logical_test 为 FALSE 时返回的值。

IF函数在财务管理中具有非常广泛的应用。

【例2-5】例如，某企业对各个销售部门的销售业绩进行评价，评价标准及各个销售部门在2002年的销售业绩汇总如图1-16所示，评价计算步骤如下：

图1-16 销售部门业绩评价

〔1〕选定单元格区域C3:C12。

〔2〕直接输入以下公式：“=IF(B3:B12<100000,\"差\一般\好\较好\很好\"))))〞。

〔3〕按“Crtl+Shift+Enter〞组合键。

那么各个销售部门的销售业绩评价结果就显示在单元格域C3:C12中。

也可以直接在单元格C3中输入公式“=IF(B3<100000,\"差\一般\好\较好\很好\"))))〞后，将其向下填充复制到C4～C12单元格中。

20

1.2.6 AND函数、OR函数和NOT函数

这3个函数的用法如下：

= AND〔条件1，条件2，…，条件N〕

= OR〔条件1，条件2，…，条件N〕

= NOT〔条件〕

AND函数表示逻辑与，当所有条件都满足时〔即所有参数的逻辑值都为真时〕，AND函数返回TRUE，否那么，只要有一个条件不满足即返回FALSE。

OR函数表示逻辑或，只要有一个条件满足时，OR函数返回TRUE，只有当所有条件都不满足时才返回FALSE。

NOT函数只有一个逻辑参数，它可以计算出TRUE或FALSE的逻辑值或逻辑表达式。如果逻辑值为 FALSE，函数 NOT 返回 TRUE；如果逻辑值为 TRUE，函数 NOT 返回FALSE。

这3个函数一般与IF函数结合使用。

【例2-6】某企业根据各销售部门的销售额及销售费用确定奖金提成比例及提取额，假设销售额大于300000元且销售费用占销售额的比例不超过1%，那么奖金提取比例为15%，否那么为10%，那么计算过程如下〔如图2-17所示〕：

〔1〕在单元格D3中输入公式“=IF(AND(B3>300000,C3/B3<1%),15%,10%)〞，将其向下填充复制到D4～C10单元格中。

21

〔2〕选取单元格区域E3:E10，输入公式“=B3:B10*D3:D10”，按“Crtl+Shift+Enter〞组合键。

那么各销售部门的销售奖金提成比例及奖金提取额如图1-17所示。

图1-17 奖金提成比例及提取额的计算

1.2.7 LOOKUP函数、VLOOKUP函数和HLOOKUP函数

一．LOOKUP函数

LOOKUP函数的功能是返回向量〔单行区域或单列区域〕或数组中的数值。函数 LOOKUP 有两种语法形式：向量和数组。函数 LOOKUP 的向量形式是在单行区域或单列区域〔向量〕中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值；函数 LOOKUP 的数组形式在数组的第一行或第一列查找指定的数值，然后返回数组的最后一行或最后一列中相同位置的数值。

〔1〕向量形式：公式为 = LOOKUP(lookup_value,lookup_vector,result_vector)

式中 lookup_value—函数LOOKUP在第一个向量中所要查找的数值，它可以为数字、文本、逻辑值或包含数值的名称或引用；

lookup_vector—只包含一行或一列的区域lookup_vector 的数值可以为文本、数字或逻辑值；

22

result_vector—为只包含一行或一列的区域其大小必须与 lookup_vector 相同。

〔2〕数组形式：公式为 = LOOKUP(lookup_value,array)

式中 array—包含文本、数字或逻辑值的单元格区域或数组它的值用于与 lookup_value 进行比拟。

例如：LOOKUP(5.2,{4.2,5,7,9,10})=5。

注意：lookup_vector的数值必须按升序排列，否那么函数LOOKUP不能返回正确的结果。文本不区分大小写。如果函数LOOKUP找不到lookup_value，那么查找lookup_vector中小于或等于lookup_value的最大数值。如果lookup_value小于lookup_vector中的最小值，函数LOOKUP返回错误值#N/A。

二．VLOOKUP函数

VLOOKUP函数的功能是在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。公式为：

= VLOOKUP〔lookup_value，table_array，col_index_num，range_lookup〕

式中 lookup_value—需要在数据表第一列中查找的数值，lookup_value 可以为数值、引用或文字串；

table_array—需要在其中查找数据的数据表，可以使用对区域或区域名称的引用，例如数据库或数据清单；

23

如果range_lookup为TRUE，那么table_array的第一列中的数值必须按升序排列，否那么函数VLOOKUP不能返回正确的数值，如果range_lookup为FALSE，table_array不必进行排序。table_array的第一列中的数值可以为文本、数字或逻辑值，且不区分文本的大小写；

col_index_num—table_array中待返回的匹配值的列序号；

col_index_num为1时，返回table_array第一列中的数值；col_index_num为2时，返回table_array第二列中的数值，以此类推。如果col_index_num小于1，函数VLOOKUP返回错误值#VALUE!；如果col_index_num大于table_array的列数，函数VLOOKUP返回错误值#REF!。

range_lookup—逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。

如果其为TRUE或省略，那么返回近似匹配值，也就是说，如果找不到精确匹配值，那么返回小于lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。如果找不到，那么返回错误值#N/A。

VLOOKUP函数在财务管理与分析中是一个经常用到的函数，因此熟悉它将会带来很大便利。在以后的有关章节中会经常用到它。

例如，假设单元格A1:A4中的数据分别为1、30、80和90，单元格B1:B4中的数据分别为400、500、600和700，那么有：VLOOKUP(5,A1:B4,2)=400，VLOOKUP(30,A1:B4,2)=500，VLOOKUP(79,A1:B4,2)=500，VLOOKUP(92,A1:B4,2)=700。

三．HLOOKUP函数

HLOOKUP函数的功能是从表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。公式为：

24

= (lookup_value，table_array，row_index_num，range_lookup)

式中 row_index_num—table_array中待返回的匹配值的行序号。

row_index_num为1时，返回table_array第一行的数值，row_index_num为2时，返回table_array第二行的数值，以此类推。如果row_index_num小于1，函数HLOOKUP返回错误值 #VALUE!；如果row_index_num大于table_array的行数，函数HLOOKUP返回错误值#REF!。

式中的其他参数含义参阅VLOOKUP函数。

HLOOKUP函数与VLOOKUP函数的区别是：当比拟值位于数据表的首行，并且要查找下面给定行中的数据时，使用函数HLOOKUP；当比拟值位于要进行数据查找的左边一列时，使用函数VLOOKUP。VLOOKUP函数在首列进行检索，先得到的是行号，然后根据col_index_num参数指定的列标返回指定的单元格数值；而HLOOKUP函数在首行进行检索，先得到的是列标，然后根据row_index_num参数指定的行号返回指定的单元格数值。

1.2.8 MATCH函数

MATCH函数的功能是返回在指定方式下与指定数值匹配的数组中元素的相应位置。公式为：

= MATCH(lookup_value,lookup_array,match_type)

式中 lookup_value—需要在数据表中查找的数值，可以是数值〔数字、文本或逻辑值〕或对数字、文本或逻辑值的单元格引用；

lookup_array—可能包含所要查找的数值的连续单元格区域，可以是数组或数组引用；

25

match_type—数字-1、0或1，它指明Excel如何在lookup_array中查找lookup_value。

查找方式如下：当match_type为-1时，lookup_array必须按降序排列，函数MATCH查找大于或等于lookup_value的最小数值；当match_type为0时，lookup_array可以按任何顺序排列，函数MATCH 查找等于lookup_value的第一个数值；当match_type为1时，lookup_array必须按升序排列，函数MATCH查找小于或等于lookup_value的最大数值。

例如，MATCH(12,{23,43,12,55},0)=3，MATCH(40,{23,43,12,55})=1。

EXCEL公式及函数的高级应用(5)

1.2.9 INDEX函数

INDEX函数的功能是返回表格或区域中的数值或对数值的引用。INDEX函数有以下两种形式：

一. 返回数组中指定单元格或单元格数组的数值。

公式为 = INDEX(array,row_num,column_num)

式中 array—单元格区域或数组常数；

row_num—数组中某行的行序号，函数从该行返回数值。如果省略row_num，那么必须有 column_num；

column_num—数组中某列的列序号，函数从该列返回数值。如果省略column_num，那么必须有 row_num。

26

需要注意的是：如果同时使用 row_num 和 column_num，函数 INDEX 返回 row_num 和 column_num 交叉处的单元格的数值。如果数组只包含一行或一列，那么相对应的参数row_num 或column_num为可选。如果数组有多行和多列，但只使用row_num 或column_num，函数INDEX返回数组中的整行或整列，且返回值也为数组。如果将row_num或column_num设置为0，函数INDEX那么分别返回整个列或行的数组数值。如果需要使用以数组形式返回的数值时，请在一个水平单元格区域中将函数INDEX作为数组公式输入。此外，row_num和column_num必须指向array中的某一单元格，否那么，函数INDEX返回错误值#REF!。

例如：INDEX({1,2;3,4},2,2) = 4。如果作为数组公式输入，那么：INDEX({1,2;3,4},0,2) = {2;4}

二. 返回引用中指定单元格。

公式为： INDEX(reference,row_num,column_num,area_num)

式中 reference—对一个或多个单元格区域的引用；

如果为引用输入一个不连续的选定区域，必须用括号括起来。如果引用中的每个区域只包含一行或一列，那么相应的参数row_num或column_num分别为可选项。例如，对于单行的引用，可以使用函数INDEX(reference,column_num)。

row_num—引用中某行的行序号，函数从该行返回一个引用；

column_num—引用中某列的列序号，函数从该列返回一个引用；

area_num—选择引用中的一个区域，并返回该区域中row_num和column_num的交叉区域。选中或输入的第一个区域序号为1，第二个为2，以此类推。如果省略area_num，函数INDEX使用区域1。

27

说明：row_num、column_num和area_num必须指向reference中的单元格,否那么，函数INDEX返回错误值#REF!。如果省略row_num和column_num，函数INDEX返回由area_num所指定的区域。

函数INDEX的结果为一个引用，且在其他公式中也被解释为引用。根据公式的需要，函数INDEX的返回值可以作为引用或是数值。例如，公式 CELL(\"width\等价于公式CELL(\"width\。CELL函数将函数INDEX的返回值作为单元格引用。而在另一方面，公式2*INDEX(A1:B2,1,2)将函数INDEX的返回值解释为B1单元格中的数字。

1.2.10 ADDRESS函数

ADDRESS函数的功能是按照给定的行号和列标，建立文本类型的单元格地址。公式为

= ADDRESS(row_num,column_num,abs_num,a1,sheet_text)

式中 row_num—在单元格引用中使用的行号；

column_num—在单元格引用中使用的列标；

abs_num—指明返回的引用类型，其中：当为1或省略时为绝对引用，当为2时为绝对行号，相对列标，当为3时为相对行号，绝对列标，当为4时为相对引用；

a1—用以指明A1或R1C1引用样式的逻辑值。如果A1为TRUE或省略，函数ADDRESS返回A1样式的引用，如果A1为FALSE，函数ADDRESS返回R1C1样式的引用；

sheet_text—一文本，指明作为外部引用的工作表的名称，如果省略sheet_text，那么不使用任何工作表名。

28

例如，ADDRESS(2,3)等于“$C$2”；ADDRESS(2,3,2)等于“C$2〞。

1.2.11 INDIRECT函数

INDIRECT函数的功能是返回由文字串指定的引用。此函数立即对引用进行计算，并显示其内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用此函数。

公式为： = INDIRECT(ref_text,a1)

式中 ref_text—对单元格的引用，此单元格可以包含A1样式的引用、R1C1样式的引用、定义为引用的名称或对文字串单元格的引用，如果ref_text不是合法的单元格的引用，函数INDIRECT返回错误值#REF!；

a1——逻辑值，指明包含在单元格ref_text中的引用的类型，如果a1为TRUE或省略，ref_text被解释为A1样式的引用，如果a1为FALSE，ref_text被解释为R1C1样式的引用。

需要注意的是，如果ref_text是对另一个工作簿的引用〔外部引用〕，那么那个工作簿必须被翻开。如果源工作簿没有翻开，函数INDIRECT返回错误值 #REF!。

例如：如果单元格A1包含文本\"B2\"，且单元格B2包含数值1.333，那么：INDIRECT($A$1)=1.333。

上述介绍的几个查找函数LOOKUP、VLOOKUP、HLOOKUP、MATCH、INDEX、ADDRESS、INDIRECT等在财务分析与决策、预测及建立动态图表等中是非常有用的。

1.2.12 矩阵函数——TRANSPOSE函数、MINVERSE函数和MMULT函数

29

一．TRANSPOSE函数

TRANSPOSE函数的功能是求矩阵的转置矩阵。

公式为 = TRANSPOSE(array)

式中，Array—需要进行转置的数组或工作表中的单元格区域。

函数TRANSPOSE必须在某个区域中以数组公式的形式输入，该区域的行数和列数分别与array的列数和行数相同。

【例1-7】假设矩阵A中的值如图1-18中单元格区域A2:C5，求其转置矩阵的步骤如下：

图1-18 求转置矩阵

〔1〕选取存放转置矩阵结果的单元格区域，如E2:H4。

〔2〕单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数TRANSPOSE，在该函数对话框中输入〔可用鼠标拾取〕单元格A2:C5，按“Crtl+Shift+Enter〞组合键，即得转置矩阵如图2-18所示。

利用TRANSPOSE函数可以把工作表中的某些行〔或列〕排列的数据转换成列〔或行〕排列的数据。例如，由于工作需要，要把工作表中的某些行数据改为列数据，假设一个一个地改动数据，将是

30

很麻烦也很费时的，而利用TRANSPOSE函数那么可以很轻松地进行这项工作。但需要注意的是，利用TRANSPOSE函数对行〔列〕数据进行转换，那么无法单独修改其中转换单元格区域中的某单元格的数据。

二．MINVERSE函数

MINVERSE函数的功能是返回矩阵的逆矩阵。公式为

= MINVERSE(array)

式中，array—具有相等行列数的数值数组或单元格区域。

MINVERSE函数的使用方法与TRANSPOSE函数是一样的。在求解线性方程组时，常常用到MINVERSE函数。

三．MMULT函数

MMULT函数的功能是返回两数组的矩阵乘积。结果矩阵的行数与 array1 的行数相同，列数与 array2 的列数相同。

公式为 = MMULT(array1,array2)

式中 array1, array2—要进行矩阵乘法运算的两个数组。

array1的列数必须与 array2 的行数相同，而且两个数组中都只能包含数值。array1和array2可以是单元格区域、数组常数或引用。如果单元格是空白单元格或含有文字串，或是array1的行数与 array2 的列数不相等时，那么函数MMULT返回错误值#VALUE!。

31

同样地，由于返回值为数组公式，故必须以数组公式的形式输入。

以例1-7的原矩阵和其转置矩阵为例，它们的乘积矩阵求解方法如下：

〔1〕选取存放乘积矩阵结果的单元格区域，如J2:L5。

〔2〕单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对话框中选取函数MMULT，在该函数对话框中的array1栏中输入〔可用鼠标拾取〕单元格区域A2:C5，在array2栏中输入单元格区域E2:H4，然后按“Crtl+Shift+Enter〞组合键，即得矩阵的乘积如图2-18所示。

1.2.13 ROUND函数

ROUND函数的功能是返回某个数字按指定位数舍入后的数字。

公式为 = ROUND(number,num_digits)

式中 number—需要进行舍入的数字；

num_digits—指定的位数，按此位数进行舍入。

如果num_digits大于0，那么舍入到指定的小数位；如果num_digits等于0，那么舍入到最接近的整数；如果num_digits小于0，那么在小数点左侧进行舍入。

利用ROUND函数可以防止利用格式工具栏上的【增加小数位数】或【减少小数位数】所带来的看起来“假数据〞问题的出现，使得工作表上显示的数据真实可靠。实际上，如果需要调整数据的小数位数，最好使用ROUND函数，而不要使用格式工具栏上的【增加小数位数】或【减少小数位数】按钮。

32

例如，假设单元格A1中的数据为14.3772，假设使用格式工具栏上的【减少小数位数】按钮将小数位数设为两位，那么单元格A1中的数据显示为14. 38，看起来似乎单元格A1的数据为14.38，但实际上仍为14.3772。假设在单元格B1中输入公式“=3*A1”，那么单元格B1中的数据显示为43.13，也许“不明〞的人认为单元格B1的数据算错了〔14.38乘以3应该等于43.14〕，但实际上单元格的数据为43.1316，这种看起来的“假〞数据可能会对实际工作带来不便。因此，正确的方法应是：单元格B1中应输入公式“=ROUND(3*ROUND(A1,2),2)〞，结果为43.14，即先将单元格A1的数据用函数ROUND四舍五入，然后再对计算后的数据四舍五入。

第二节 EXCEL数据分析处理

Excel提供了强大的数据分析处理功能，利用它们可以实现对数据的排序、分类汇总、筛选及数据透视等操作。

在进行数据分析处理之前，首先必须注意以下几个问题：

〔1〕防止在数据清单中存在有空行和空列。

〔2〕防止在单元格的开头和末尾键入空格。

〔3〕防止在一张工作表中建立多个数据清单，每张工作表应仅使用一个数据清单。

〔4〕工作表的数据清单应与其他数据之间至少留出一个空列和一个空行，以便于检测和选定数据清单。

〔5〕关键数据应置于数据清单的顶部或底部。

2.1 数据排序

33

2.1.1 数据排序的规那么

Excel允许对字符、数字等数据按大小顺序进行升序或降序排列，要进行排序的数据称之为关键字。不同类型的关键字的排序规那么如下：

数值：按数值的大小。

字母：按字母先后顺序。

日期：按日期的先后。

汉字：按汉语拼音的顺序或按笔画顺序。

逻辑值：升序时FALSE排在TRUE前面，降序时相反。

空格：总是排在最后。

2.1.2 数据排序步骤

〔1〕单击数据区中要进行排序的任意单元格。

〔2〕单击【数据】菜单，选择【排序】项，系统将弹出【排序】对话框，如图1-35所示。

34

图1-35 【排序】对话框

〔3〕在【排序】对话框中用下拉列表框选择要排序的关键字，关键字有“主要关键字〞、“次要关键字〞和“第三关键字〞，根据需要分别选择不同的关键字；

〔4〕单击【确定】按钮，数据就按要求进行了排序。

当只有一个关键字时，可以单击工具栏上的升序按钮或降序按钮，进行自动排序。

2.1.3 自定义排序

在有些情况下，对数据的排序顺序可能非常特殊，既不是按数值大小次序、也不是按汉字的拼音顺序或笔画顺序，而是按照指定的特殊次序，如对总公司的各个分公司按照要求的顺序进行排序，按产品的种类或规格排序等等，这时就需要自定义排序。

利用自定义排序方法进行排序，首先应建立自定义序列，其方法可参阅第1章的有关内容。建立好自定义序列后，即可对数据进行排序，方法是：单击数据区中要进行排序的任意单元格，单击【数据】菜单，选择【排序】项，在弹出的【排序】对话框中单击【选项】按钮，系统弹出【排序选项】对话框，如图1-36所示，在【自定义排序次序】的下拉列表中，选择前面建立的自定义序列，然后单击【确定】按钮，即可对数据进行自定义排序。

35

图1-36 【排序选项】对话框

2.2 数据的查找与筛选

企业的管理人员经常需要在数据库或数据清单众多的数据中找出需要的数据，Excel提供了功能强大的数据查找与筛选工具。数据查找是指从原始数据中提取满足条件的数据记录，源数据不会改变，也不会被隐藏；数据筛选是指把数据库或数据清单中所有不满足条件的数据记录隐藏起来，只显示满足条件的数据记录。常用的数据查找与筛选方法有：记录单查找、自动筛选和高级筛选。

下面结合实例说明各种查找方法的具体应用。

【例2—11】图2-37为某公司的局部商品销售记录清单。

图2-37 某公司的商品销售明细清单

根据图2-37中的有关资料，可以分别采用记录单查找、自动筛选或高级筛选的方式查找或选择所需要的信息，如下所述：

2.2.1 记录单查找

记录单是查找和编辑数据的最简单的方法，利用记录单，不仅可以查找数据记录，还可以修改和

36

删除记录、添加新的数据记录等。

一．查找数据记录

利用记录单查找数据记录的步骤如下：

〔1〕用鼠标单击数据清单或数据库中的任一非空单元格。

〔2〕单击【数据】菜单，选择【记录单】项，那么系统弹出如图1-38所示的记录单。

图1-38 记录单

〔3〕单击记录单中的【条件】按钮，那么弹出记录单条件对话框，如图1-39所示。

图1-39 记录单条件对话框

37

〔4〕输入条件，比方要查找“张三〞的销售记录，那么在【销售人员】栏中输入“张三〞，然后单击【上一条】按钮或【下一条】按钮，系统就逐次显示满足条件的记录行。

还可以使用多个条件联合查找记录，此处不再表达。

二．修改或删除记录

在图2-38所示的记录单中，即可对某一记录的各字段进行修改。假设要删除显示的记录，只需单击记录单上的【删除】按钮即可。

三．添加新的记录

在图2-38所示的记录单中，单击记录单上的【新建】按钮，那么出现各字段均为空白的新建记录单，在记录单中输入各字段的值，输入完毕后，单击【新建】按钮，即完成添加新记录。

2.2.2 自动筛选与自定义筛选

一．自动筛选

记录单检索数据每次只能显示一个数据行，当查询的数据较多，或要把查询的结果汇总成表时，就需要使用筛选工具了。自动筛选提供了快速检索数据清单或数据库的方法，通过简单的操作，就能筛选出需要的数据。利用自动筛选查找数据的步骤如下：

〔1〕用鼠标单击数据清单或数据库中的任一非空单元格。

〔2〕单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中选择【自动筛选】，那么系统自动在数据清单的每列数据的标题旁边添加一个下拉列标标志，如图1-40所示。

38

图1-40 自动筛选的下拉列表标志

〔3〕单击需要筛选的下拉列表，系统显示出可用的筛选条件，从中选择需要的条件，即可显示出满足条件的所有数据。例如，要查找所有彩电的销售记录，单击“商品〞右边的下拉列表，从中选择“彩电〞项，那么所有的彩电销售记录就显示出来，而其他的数据那么被隐藏，如图1-41所示。

图1-41 彩电销售清单的筛选结果

如果有关彩电的销售记录很多，超过了10个，当需要只显示10个记录时，可单击“单价〞、“数量〞、“金额〞等右边的下拉列表中的“前10个〞项，系统弹出【自动筛选前10个】对话框，如图2-42所示。这里，在【显示】下拉列表中“最大〞表示最大〔最好〕的前10个记录，“最小〞表示最小〔最差〕的前10个记录。中间的编辑框中的数值表示显示的记录行数，系统默认值为10，但可以修改，根据需要输入数值即可。

图1-42 【自动筛选前10个】对话框

假设要恢复所有的记录，那么单击“商品〞右边的下拉列表中的“全部〞项。假设要取消【自动筛选】状态，那么单击【数据】菜单，选择【筛选】项，在【筛选】子菜单中再次选择【自动筛选】。

39

二．自定义筛选方式

当在图1-40所示的下拉列表中选择“自定义〞项时那么会弹出【自定义自动筛选方式】对话框，如图1-43所示，用户可根据具体条件对各栏进行设置。如要查找销售金额大于或等于“150000〞且小于或等于“200000〞的所有记录，那么单击左上角的下拉箭头，选择“大于或等于〞，右上角的条件值输入“150000〞，单击左下角的下拉箭头，选择“小于或等于〞，右下角的条件值输入“200000〞，单击【确定】按钮，并选择“与〞条件，那么满足这些条件的所有记录就显示出来了，如图1-44所示。

图1-43 【自定义自动筛选方式】对话框

图1-44 【自定义自动筛选方式】筛选的结果

2.2.3 高级筛选

高级筛选可以使用较多的条件来对数据清单进行筛选，这些条件既可以是与条件，也可以是或条件，或与条件，与或条件的组合使用，还可以使用计算条件。

一．一般情况下的高级筛选

40

利用高级筛选对数据清单进行筛选的步骤如下：

〔1〕首先应建立一个条件区域。在条件区域中，同一行中的条件是与条件，也就是这些条件必须同时满足；不同行中的条件是或条件，也就是这些条件只要满足其一即可。如需要查找张三销售彩电的所有记录，那么建立条件区域如图1-45所示。

图1-45 建立条件区域

〔2〕单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【筛选】子菜单中的【高级筛选】项，那么系统弹出如图1-46所示的【高级筛选】对话框。

图1-46 【高级筛选】对话框

〔3〕一般情况下，系统将自动给出了数据区域，用户只需在【条件区域】栏中输入条件区域〔本例中为B19:C20，也可以用鼠标拾取单元格区域，此时在条件区域中将显示“销售明细清单!$B$19:$C$20”。

〔4〕高级筛选结果可以显示在数据清单的原有区域中，也可以显示在工作表的其他空白单元格区域，系统默认的方式是在数据清单的原有区域中显示结果。假设需要在工作表的其他空白单元格区域

41

显示结果，那么在【方式】项中选中“将筛选结果复制到其他位置〞，并在【复制到】栏中输入需要显示筛选结果的单元格〔开头的一个单元格即可〕。图1-47为在原有区域显示的高级筛选结果。

图1-47 在原有区域显示的高级筛选结果

当需要显示原始的全部数据时，可以单击【数据】菜单，选择【筛选】子菜单中的工程，在【筛选】子菜单中选择【全部显示】即可。

同样的方法可以进行建立或条件、与条件与或条件的组合使用情况下的高级筛选。

二．计算条件情况下的高级筛选

在有些情况下，筛选的条件不是一个常数，而是一个随数据清单中数据变化的计算结果，此时无法直接利用高级筛选进行数据筛选。不过，我们可以通过计算条件的方法解决。以例2-20为例〔见图1-37〕，这里要找出销售额大于平均销售额的所有记录。步骤如下：

〔1〕在数据清单以外的任一空单元格内输入平均值计算公式，比方在单元格H20中输入公式“=AVERAGE(E3:E16)〞，这里要特别注意的是存放平均值计算公式的单元格的列标不能与数据清单的任一列标相同，如图1-48所示。

42

图1-48 计算条件情况下的高级筛选

〔2〕设置条件区域，条件区域的列表可以是除数据清单中数据标题以外的任何文本，而筛选条件可在单元格B20中输入“=E3>$H$20”，这里要特别注意：必须以绝对引用的方式引用销售额平均值，以相对引用的方式引用数据清单中的数据。

〔3〕按照前面介绍的步骤进行高级筛选，其中高级筛选的数据区域为$A$2:$G$16；高级筛选的条件区域为$B$19:$C$20，那么筛选结果如图2-48所示。

2.3 数据的分类与汇总

在对数据进行分析时，常常需要将相同类型的数据统计出来，这就是数据的分类与汇总。在对数据进行汇总之前，应特别注意的是：首先必须对要汇总的关键字进行排序。

2.3.1 进行分类汇总

例如，在例2-11中，要按地区进行自动分类汇总，其步骤如下：

〔1〕首先对“地区〞进行排序，排序方法见前面所述。

〔2〕单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【分类汇总】项，系统弹出如图1-49所示的【分类汇总】对话框。

43

图1-49 【分类汇总】对话框

〔3〕在【分类汇总】对话框中，【分类字段】选项下选择“地区〞，【汇总方式】选项下选择“求和〞，【选定汇总项】选项下选定“数量〞和“金额〞，单击【确定】按钮，那么分类汇总的结果如图2-50所示。

图1-50 按地区分类汇总结果

在图1-50中，左上角有3个按钮，按钮1表示1级汇总，显示全部的销售数量和销售金额汇总；按钮2表示2级汇总，显示各地区的全部销售数量和销售金额汇总；按钮3表示3级汇总，显示各地区的销售数量和销售金额的汇总明细及汇总额〔即图1-50所示的汇总结果〕。

图1-50中，左边的滑动按钮为隐藏明细按钮，单击此按钮，那么将隐藏本级的明细数据，同时变为显示明细按钮，再单击按钮，那么将显示本级的全部明细数据，同时变为。

44

在上述自动分类汇总的结果上，还可以再进行分类汇总，例如再进行另一种分类汇总，两次分类汇总的关键字可以相同，也可以不同，其分类汇总方法与前面的是一样的，此处不再介绍。

2.3.2 分类汇总的撤消

如果不再需要分类汇总结果，可在图2-49所示的【分类汇总】对话框中单击【全部删除】，即可撤消分类汇总。

2.4 数据透视表

数据透视表是用于快速汇总大量数据的交互式表格，用户可以旋转其行或列以查看对源数据的不同汇总，也可以通过显示不同的页来筛选数据，还可以显示所关心区域的数据明细。通过对源数据表的行、列进行重新排列，使得数据表达的信息更清楚明了。

2.4.1 建立数据透视表

以例2-11的数据为例，建立数据透视表的步骤如下：

〔1〕首先，要保证数据源是一个数据清单或数据库，即数据表的每列必须有列标。

〔2〕单击数据清单或数据库中的任一非空单元格，然后单击【数据】菜单，选择【数据透视表和图表报告】项，那么系统弹出【数据透视表和数据透视图向导—3步骤之1】对话框，如图1-51所示，根据待分析数据来源及需要创立何种报表类型，进行相应的选择，然后单击【下一步】按钮，系统弹出【数据透视表和数据透视图向导—3步骤之2】对话框，如图1-52所示；

45

图1-51 【数据透视表和数据透视图向导—3步骤之1】对话框

图1-52 【数据透视表和数据透视图向导—3步骤之2】对话框

〔3〕默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，那么可直接输入“选定区域〞，或单击【浏览】按钮，从其他的文件中提取数据源。确定数据源后，单击【下一步】按钮，系统弹出【数据透视表和数据透视图向导—3步骤之3】对话框，如图1-53所示。

图1-53 【数据透视表和数据透视图向导—3步骤之3】对话框

〔4〕在【数据透视表和数据透视图向导—3步骤之3】对话框中，单击【版式】按钮，出现【数据透视表和数据透视图向导—版式】对话框，如图1-所示。

46

〔5〕【数据透视表和数据透视图向导—版式】对话框中，再根据需要，将右边的字段按钮拖到左边的图上，这里，将“销售人员〞拖到“行(R)〞图上，将“商品〞拖到“列(C)〞图上，将“数量〔台〕〞和“金额〔元〕〞拖到“数据(D)〞图上，如图1-55所示。

图1- 【数据透视表和数据透视图向导—版式】对话框

图1-55 设置数据透视表的版式

〔6〕设置好版式后，单击【确定】按钮，那么系统就返回到图2—44所示的【数据透视表和数据透视图向导—3步骤之3】对话框，然后单击【完成】按钮，数据透视表就完成了，如图1-56所示。

这样，通过图2-56的数据透视表，即可看出每个销售人员所销售商品的种类、数量、销售额及其合计数，从而以此为根底可很方便地对每个销售人员的销售业绩进行评价。

47

图1-56 各个销售人员销售商品的数据透视表

2.4.2 数据的透视分析

在图1-56所建立的数据透视表上，可以很方便地进行多角度的统计与分析。比方要了解李四所销售商品的情况，可在“销售人员〞下拉列标中只选中“李四〞，然后单击“确定〞按钮，那么李四的销售情况如图1-57所示。

图1-57 李四的销售情况汇总

还可以建立透视图，方法是：单击数据透视表中的任一单元格，单击鼠标右键，在快捷菜单中选择【数据透视图】项，那么系统自动显示出数据透视图，从而得到每个销售人员的更为直观的销售情况。

第三节 EXCEL图表处理

Excel具有完整的图表功能，它不仅可以生成诸如条形图、折线图、饼图等标准图表，还可以生

48

成较为复杂的三维立体图表。对各种财务数据进行图表处理，可以更直观地进行财务分析，找出工作表格不容易发现的问题，使得财务管理工作更为有效。

3.1 图表类型

Excel提供了约14种标准图表类型，如面积图、柱形图、条形图、折线图、饼图、圆环图、气泡图、雷达图、股价图、曲面图、散点图、锥形图、圆柱图、棱锥图等，每种图表类型又都有几种不同的子类型。此外，Excel还提供了约20种自定义图表类型，用户可根据不同的需要选用适当的图表类型。

关于各种图表类型的具体情况，可单击工具栏上的【图表向导】按钮选择【图表】项，即可查看各种图表。

，或单击【插入】菜单，

3.2 图表的建立

建立图表的过程非常简单，只要按照【图表向导】的有关说明，一步一步地进行操作，即可完成图表的制作。下面结合实例进行说明。

【例2-8】某企业2002年12个月的销售量与销售费用的有关数据如图1-19所示，绘制各月销售额与销售费用之间关系的图表的步骤如下：

图1-19 销售额与销售费用有关数据

〔1〕选取单元格区域A2：M3，单击工具栏上的【图表向导】按钮，或单击【插入】菜单，选择【图表】项，出现【图表向导－4步骤之1－图表类型】对话框，如图1-20所示，在【图表类型】

49

中选【折线图】，在【子图表类型】中选【数据点折线图】，单击【下一步】按钮，出现【图表向导－4步骤之2－图表数据源】对话框，如图1-21所示。

图1-20 【图表向导－4步骤之1－图表类型】对话框

图1-21 【图表向导－4步骤之2－图表源数据】对话框

〔2〕在【图表向导－4步骤之2－图表源数据】对话框中，单击【系列】，在【分类〔X〕轴标志】栏中填入“=Sheet1!$B$1:$M$1”，单击【下一步】按钮，出现【图表向导－4步骤之3－图表选项】对话框，如图1-22所示。

50

图 1-22 【图表向导-4步骤之3-图表选项】对话框

〔3〕在【图表向导－4步骤之3－图表选项】对话框中，对【标题】的各项进行输入，即在【图表标题】栏中填入“销售额和销售费用的变化图〞，在【数值X轴】栏中填入“月份〞，在【数值Y轴】栏中填入“金额〔千元〕〞，单击【下一步】按钮，出现【图表向导－4步骤之4－图表位置】如图2-23所示。

图 1-23 【图表向导-4步骤之4-图表位置】对话框

〔4〕在【图表向导－4步骤之4－图表位置】对话框中，假设要建立嵌入式图表，即图表嵌入在本工作表中，那么选择“作为其中的对象插入〞；假设要建立工作表图表，那么选择“作为新工作表插入〞。这里选择“作为其中的对象插入〞；然后单击【完成】按钮，即得到需要的图表，如图1-24所示。

51

图1-24 销售额与销售费用变化图

3.3 图表的编辑、修改及格式化

通过图表向导建立的图表可能不尽人意，如标题太大或太小、坐标系列太多、图表尺寸太小、漏掉了数据系列、需要添加数据标志等等，这时就需要对图表进行修改和格式化。

一．设置坐标、标题、图例等的格式

设置坐标、标题、图例等的格式的方法非常简单，可将鼠标移到坐标、标题、图例等上，单击右键，在快捷菜单上选择相应的工程即可。例如要改变X坐标大小，单击右键，出现快捷菜单，选择【坐标轴格式】项，就会弹出【坐标轴格式】对话框，如图1-25所示，选择需要修改的工程，进行设置即可。

52

图1-25 【坐标轴格式】对话框

二．改变图表大小

单击图表区域，将它激活，图表边框出现8个操作柄，用鼠标指向某个操作柄，当鼠标指针呈现双箭头时，按住左键不放，拖动操作柄到需要的位置上，然后放开鼠标左键，即可完成。

三．移动或复制图表

移动：单击图表区域，将它激活，图表边框出现8个操作柄，在图表区域按住鼠标左键不放，拖动鼠标将图表移到需要的地方。

复制：单击图表区域，将它激活，图表边框出现8个操作柄，在图表区域按住鼠标左键不放，拖动鼠标将图表移到需要的地方，按Ctrl键，然后放开鼠标。假设需要将图表复制到其他工作表或其他文件中，可选中图表，按“Ctrl+C〞键，再在需要安置图表的工作表或其他文件的适当位置，按“Ctrl+V〞键

四．添加数据标志

在很多情况下，在图表上添加数据标志，可以更直观地表示因素的变化情况。添加数据标志的步骤如下：单击需要显示数据标志的数据点，然后按鼠标右键，出现快捷菜单，如图2-26所示，选择【数据系列格式】对话框，单击【数据标志】，选择“显示值〞。

53

图2-26 快捷菜单

通过【数据系列格式】对话框，还可以进行其他方面的修改或格式化，有关内容可单击【数据系列格式】对话框的有关项。

图1-27 【数据系列格式】对话框

五．改变图表颜色、图案、边框

改变图表颜色、图案、边框，可通过【图表区格式】对话框来完成，方法是：单击图表区域，单击鼠标右键，在快捷菜单中选【图表区格式】项，弹出【图表区格式】对话框，即可进行相应的修改。

3.4 地区销售分布图表的建立

企业的产品销往全国各地及世界各地，各地的销售量是不同的，我们可以利用Excel的地图分析工具建立销售数据地图，从而可以将企业产品在各地的销售情况更加直观地表示出来。

【例2-9】某企业在某些省份的销售数据如图1-28所示，那么建立数据地图的步骤如下：

图1-28 销售数据

〔1〕选中数据区域A2:B15。

〔2〕单击【插入】菜单，执行【对象】命令，弹出【对象】对话框，如图2-29所示，选中“Microsoft地图〞，那么Excel就会根据所选的工作表数据建立如图2-30所示的数据地图；在数据地图中，数据越多的区域〔省份〕，颜色就越深。

〔3〕在图2-30中所示的地图中没有标明省份名称，但可以通过下述方法参加省份名称：双击地图，出现地图的菜单，如图2-31所示，单击地图菜单上的【工具】，选择【标志】项，出现【地图标志】对话框，如图2-32所示；

〔4〕在【地图标志】对话框中，【需要设置标志的地图项】中选择“中国〞，【创立标志】中选择“地图项名称〞，然后单击【确定】按钮。

55

图1-29 【对象】对话框

图1-30 某企业的销售地区分布

图1-31 地图菜单项

图1-32 【地图标志】对话框

〔5〕在地图上移动鼠标，在鼠标移动过程中，Excel会显示该区域所对应的省份名称，单击左键，该省份名称就会标注在对应的省份区域上〔图表太小，此处省略〕。

〔6〕如果需要，还可以对各省份添加数量标志，方法是：双击地图，出现【Microsoft 地图控件】对话框，如图1-33所示，根据需要选择数据类型格式，用鼠标把需要的格式拖放在对话框右边区域中的格式上，然后把要设置这种格式的数据列〔对话框中的【第B列】〕拖放在图中的“列〞字框上即可。【Microsoft 地图控件】对话框提供了6种不同的数据类型格式。图1-20就是第1种数据

56

类型格式。

图1-33 【Microsoft 地图控件】对话框

需要注意的是，图1-28的工作表数据中的各省份名称必须与Excel所规定的相同，否那么可能会出现意想不到的错误。Excel所规定的各省份名称如下：黑龙江省、吉林省、辽宁省、内蒙古自治区、维吾尔自治区、北京市、天津市、河北省、山西省、陕西省、青海省、宁夏回族自治区、自治区、山东省、河南省、江苏省、浙江省、上海市、安徽省、湖北省、湖南省、福建省、广东省、广西壮族自治区、江西省、四川省、云南省、贵州省、海南省、、省、。

3.5 动态图表的建立

在企业的经营活动中，往往需要为每个部门建立大量相似的图表，如果在一张工作表上建立太多的图表，既费时也使得图表显得凌乱不堪。我们可以建立动态图表来解决这个问题，当需要了解某个部门的销售情况时，只需将鼠标移到工作表中该部门的单元格上，即可立即显示出该部门的销售图表。

【例2-10】某企业的8个销售部门一年内各月的销售量数据如图1-34所示，建立各部门的动态图表的步骤如下：

57

图1-34 动态销售图表

〔1〕设计动态图表数据区域，如图2-34所示。

〔2〕在单元格A13中输入公式“=INDIRECT(ADDRESS(CELL(\"row\"),COLUMN (A3)))〞，并把该公式向右填充复制到M13中，这里COLUMN的意思是返回参数所在的列标，CELL(\"row\")的意思是返回当前光标所在的行号，ADDRESS〔行号，列标〕的意思是返回由行号和列标确定的单元格，INDIRECT的意思是返回参数所确定的单元格内容；

〔3〕选中区域A12:M13，插入“折线图〞，并进行相应的格式设置，那么动态图表就建立起来了。

假设鼠标单击A3单元格，再按F9键〔即对工作表数据重新计算〕，就会显示部门A的销售图；假设鼠标单击A5单元格，再按F9键〔即对工作表数据重新计算〕，就会显示部门C的销售图。这样，就可以很方便地对各个销售部门的销售量进行直观的观察和分析。

第四节 EXCEL数据分析工具的应用

Excel提供了非常实用的数据分析工具，利用这些分析工具，可解决财务管理中的许多问题，例

58

如财务分析工具、统计分析工具、工程分析工具、规划求解工具、方案管理器等等。下面介绍财务管理与分析中常用的一些数据分析工具。

4.1 模拟运算表

模拟运算表就是将工作表中的一个单元格区域的数据进行模拟计算，测试使用一个或两个变量对运算结果的影响。在Excel中，可以构造两种模拟运算表：单变量模拟运算表和多变量模拟运算表。

4.1.1 单变量模拟运算表

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

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

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

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

〔2〕在单元格B4中输入公式“=PMT(A4/12,5*12,B1)〞。

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

59

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

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

〔5〕由于本例中引用的是列数据，故在【输入引用列的单元格】中输入“$A$4”。单击【确定】按钮，即得到单变量的模拟运算表，如图2-62所示。

4.1.2 双变量模拟运算表

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

4.2 单变量求解

单变量求解就是求解只有一个变量的方程的根，方程可以是线性方程，也可以是非线性方程。单变量求解工具可以解决许多财务管理中涉及到一个变量的求解问题。

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

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

60

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

4.3 规划求解

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

【规划求解】加载宏是Excel的一个可选安装模块，在安装Microsoft Excel时，如果采用【典型安装】，那么【规划求解】工具没有被安装，只有在选择【完全/定制安装】时才可选择安装这个模块。在安装完成进入Excel后，单击【工具】菜单，选择【加载宏】项，在【加载宏】对话框中选定【规划求解】复选框，然后单击【确定】按钮，那么系统就安装和加载【规划求解】工具，可以使用它了。

4.3.1 求解优化问题

财务管理中涉及到很多的优化问题，如最大利润、最小本钱、最优投资组合、目标规划、线性回归及非线性回归等等。下面仅举一个简单的例子来说明利用规划求解工具解决最大利润的问题，有关规划求解工具的更多实际应用可参阅后面的有关章节。

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

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

61

图1-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)〞；

62

计算公式为

，

〔4〕在单元格B14中输入产品消耗工时合计计算公式“=SUMPRODUCT(B6:C6,B11:C11)〞。在单元格B15中输入产品消耗材料合计计算公式“=SUMPRODUCT(B7:C7,B11:C11)〞，在单元格B16中输入产品消耗能源合计计算公式“=SUMPRODUCT(B8:C8,B11:C11)〞。

〔5〕单击【工具】菜单，选择【规划求解】项，那么系统弹出【规划求解参数】对话框，如图1-67。

图1-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”；

这里，添加约束条件的方法是：单击【添加】按钮，系统会弹出【添加约束】对话框，如图1-68所示，输入完毕一个约束条件后，单击【添加】按钮，那么又弹出空白的【添加约束】对话框，再输入第二个约束条件。当所有约束条件都输入完毕后，单击【确定】按钮，那么系统返回到【规划求解参数】对话框。

63

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

如果发现输入的约束条件有错误，还可以对其进行修改，方法是：选中要修改的约束条件，单击【更改】按钮，那么系统弹出【改变约束】对话框，如图1-69所示，再进行修改即可。

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

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

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

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

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

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

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

4.3.2 求解方程组

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

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

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

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

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

〔2〕单元格E2：E4为变动单元格，存放方程组的解，其初值可设为零〔空单元格〕；

65

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

B4

中输入求和公式

〔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所示。

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

4.4 方案分析

在企业的生产经营活动中，由于市场的不断变化，企业的生产销售受到各种因素的影响，企业需要估计这些因素并分析其对企业生产销售的影响。Excel提供了称为方案的工具来解决上述问题，利用其提供的方案管理器，可以很方便地对多种方案〔即多个假设条件〕进行分析。

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

【例2-17】某企业生产产品A、产品B、产品C，在2003年的销售额分别为200万元、400万元和300万元，销售本钱分别为120万元、280万元和160万元。根据市场情况推测，

66

2 ://down.cooai :80/年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售本钱的增长率如图1-73所示。

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

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所示，

67

单击【添加】按钮，系统弹出【添加方案】对话框，如图1-75所示。

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

〔4〕在【添加方案】对话框中，【方案名】编辑框中输入“方案1 销售好〞，【可变单元格】编辑框中输入“$G$4:$H$6”，单击【确定】按钮，系统弹出【方案变量值】对话框，如图1-76所示；

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

68

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

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

4.4.2 显示方案

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

〔1〕翻开原工作表，并激活工作表。

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

4.4.3 修改、删除或增加方案

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

69

假设要删除某一方案，那么在图1-77所示的【方案管理器】对话框中选中需要删除的方案，单击【删除】按钮。

假设要增加方案，那么在图1-77所示的【方案管理器】对话框中单击【添加】按钮，然后在图1-75所示的对话框填写相关的工程。

4.4.4 建立方案报告

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

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

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

70

图1-79 方案报告

4.5 数据分析工具库

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

71