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

第2章Excel在财务管理与分析中的应用基础知识

2.1公式及函数的高级应用(1)

公式和函数是Excel最基本、最重要的应用工具，是Excel

的核心，因此，应对公式和函数熟练掌握，才能在实际应用

中得心应手。

2.1.1数组公式及其应用

数组公式就是可以同时进行多重计算并返回一种或多种结

果的公式。在数组公式中使用两组或多组数据称为数组参

数，数组参数可以是一个数据区域，也可以是数组常量。数

组公式中的每个数组参数必须有相同数量的行和列。

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

1.数组公式的输入

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

(1)选定单元格或单元格区域。如果数组公式将返回一个

结果，单击需要输入数组公式的单元格；如果数组公式将返

回多个结果，则要选定需要输入数组公式的单元格区域。

(2)输入数组公式。

(3)同时按“Crtl+Shift+Enter”组合键，则Excel自动在公

式的两边加上大括号{}0

特别要注意的是，第(3)步相当重要，只有输入公式后同时

按“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组

公式。否则，如果只按Enter键，则输入的只是一个简单的

公式，也只在选中的单元格区域的第1个单元格显示出一个

计算结果。

在数组公式中，通常都使用单元格区域引用，但也可以直接

键入数值数组，这样键入的数值数组被称为数组常量。当不

想在工作表中按单元格逐个输入数值时，可以使用这种方

法。如果要生成数组常量，必须按如下操作：

(1)直接在公式中输入数值，并用大括号“{}”括起来。

(2)不同列的数值用逗号”了分开。

(3)不同行的数值用分号分开。

输入数组常量的方法:

例如，要在单元格A1:D1中分别输入10,20,30和40这4

个数值，则可采用下述的步骤：

(1)选取单元格区域A1:D1,如图2-1所示。

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

(2)在公式编辑栏中输入数组公式“刍{10,20,30,40}”，如图

2-2所示。

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

(3)同时按Ctrl+Shift+Enter组合键，即可在单元格Al、B1、

Cl、D1中分别输入了10、20、30、40,如图2-3所示。

假若要在单元格Al、Bl、Cl、DI、A2、B2、C2、D2中分

别输入10、20、30、40、50、60、70、80,则可以采用下述

的方法：

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

(1)选取单元格区域A1:D2,如图2-4所示。

■

3

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

(2)在编辑栏中输入公式“冬{10,20,30,40;50,60,70,80}”,如

图2-5所示。

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

(3)按Ctrl+Shift+Enter组合键，就在单元格Al、Bl、CK

DI、A2、B2、C2、D2中分别输入了10、20、30、40和50、

60、70、80,如图2-6所示。

工AlJ立丽药30,而丁

▲厂B1CIDIi

图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-8所示。

(3)同时按Ctrl+Shift+Enter组合键，即可在单元格A3:D3

中得到数组公式“刍A1:D1+A2:D2"，如图2-9所示。

DWA2

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

2.编辑数组公式

数组公式的特征之一就是不能单独编辑、清除或移动数组公

式所涉及的单元格区域中的某一个单元格。若在数组公式输

入完毕后发现错误需要修改，则需要按以下步骤进行：

(1)在数组区域中单击任一单元格。

(2)单击公式编辑栏，当编辑栏被激活时，大括号“{}”

在数组公式中消失。

(3)编辑数组公式内容。

(4)修改完毕后，按“Crtl+Shift+Enter”组合键。要特别注

意不要忘记这一步。

3.删除数组公式

删除数组公式的步骤是：首先选定存放数组公式的所有单元

格，然后按Delete键。

数组公式的应用

1.用数组公式计算两个数据区域的乘积

【例2-1］如图2-10所示，已经知道12个月的销售量和产品

单价，则可以利用数组公式计算每个月的销售额，步骤如下:

ABICDEF_GHIJX.L1

11234s67•.910H12

2MMS3xn3S0CO360400356<»4SO390500510

3202829282829292828282928

4班!(霹.；<>“_0400980011TC010640H2Q09968120401260010920IWOOt<280

工j月平均椅iHwtnw口

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

(1)选取单元格区域B4:M4。

(2)输入公式“二B2:M2*B3:M3”。

(3)按“t>tl+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-2］某企业2002年销售的3种产品的有关资料如图

2-11所示，则可以利用数组公式计算该企业2002年的总销售

额，方法如下:

】二BqD：EJ,晨H,JJ：N

w8■m9012

产3eo510

价

・

”m

M28

泗

产

0«■卷

$T»OS204W

舞

・

忖

或

皿

产g32n»

w盾■

710790B2O830T80

左

饵

・/

02ls25252S

DI£.谢

U358<728048470527TO<?880

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

(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%,则可以利用数组公式进行

计算，步骤如下：

G色—kB3D8♦(卜2W)

ABDITG1H1

产区原价产神价后的价格

产品猊格产品A产品B产£c产品拱格产品A产品B产取

XI120260XI1196|168208

X2150S70X2120'184536

X3130290X3104192232

X4160300X4128160240

X5180310X51441S2248

X6170320X6136144256

图2-12产品降价计算

(1)选取单元格区域G3:I8。

(2)输入公式“二B3:D8*(1-2O%)”°

(3)按Crtl+Shift+Enter组合键。

此外，当对结构相同的不同工作表数据进行合并汇总处理

时，利用上述方法也将是非常方便的。有关不同工作表单元

格的引用可参阅第1章的有关内容，关于数据的合并计算可

参阅本章235节的内容。

2.1.2常用函数及其应用

在第1章中介绍了一些有关函数的基本知识，本节对在财务

管理中常用的一般函数应用进行说明，其他有关的专门财务

函数将在以后的有关章节中分别予以介绍。

SUM函数、SUMIF函数和SUMPRODUCT函

数

在财务管理中，应用最多的是求和函数。求和函数有三个：

无条件求和SUM函数、条件求和SUMIF函数和多组数据相

乘求和SUMPRODUCT函数。

1.无条件求和SUM函数

该函数是求30个以内参数的和。公式为

二SUM(参数1,参数2,…，参数N)

当对某一行或某一列的连续数据进行求和时，还可以使用工

具栏中的自动求和按钮£。

例如，在例2-1中，求全年的销售量，则可以单击单元格N2,

然后再单击求和按钮£，按回车键即可，如图2-13所示。

A~~B~C_D~I~F_G_H~~I_J_K_L~■MI~；

123451T•9:tOII12_

惜"：:湎二疣:蔚二南:源二访二笳漓:祠:滴二就:期

图2-13自动求和

2.条件求和SUMIF函数

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

为

=SUMIF(range,criteria,sum_range)

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

criteria一确定哪些单元格将被相加求和的条件，其形式

可以为数字、表达式或文本；

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

只有当range中的相应单元格满足条件时，才对sum_range

中的单元格求和。如果省略sum_range,则直接对range中的

单元格求和。

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

【例2-4】某商场2月份销售的家电流水记录如图2-14所示,

则在单元格13中输入公式“二SUMIF(C3:C10,211,F3:F10)”，单

元格14中输入公式“二SUMIF(C3:C10,215,F3:F10)”，在单元格15

中输入公式“二SUMIF(C3:C10,212,F3:F10)”,单元格16中输入公

式"二SUMIF(C3:C10,220,F3:F10)”,即可得到分类销售额汇总

表。

13C10.2H.P3PIO)

ABC1>IGK_I___|

1前才电情也正集sMiSZS

日“网as踪和码沿，金■主氏1

Jj8月2日，寸长虹白电21121600

48月4日内寸长虹方电21!336001500

58月10日洸海紫瓦衣机2151150016100

68«14015西・尔至•2122460016000

78月8日1》既•尔空•21236900

88月25日我!S电0220216000

98月25日先寸皿岑电21111200

to8月31日15四9宗交。21224600

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

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

图2-15SUMIF函数对话框

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

的。

3.SUMPRODUCT函数

SUMPRODUCT函数的功能是在给定的几组数组中，将数组

间对应的元素相乘，并返回乘积之和。公式为

=SUMPRODUCT(array1,array2,array3j)

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

需注意的是，数组参数必须具有相同的维数，否则，函数

SUMPRODUCT将返回错误值#VALUE!o对于非数值型的

数组元素将作为。处理。

例如，在例2-2中，要计算2002年产品A的销售总额，可在

任一单而格(比她口02)中褊入公式

U^SUMPRODUCT(C2:N2,C3:N3)”即可。

AVERAGE函数

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

为

=AVERAGE(参数1,参数2,…，参数N)

函数中的参数可以是数字，或者是涉及数字的名称、数组或

引用。如果数组或单元格引用参数中有文字、逻辑值或空单

元格，则忽略其值。但是，如果单元格包含零值则计算在内。

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

绍。

MIN函数和MAX函数

MIN函数的功能是给定参数表中的最小值，MAX函数的功

能是给定参数表中的最大值。公式为

-MIN(参数1,参数2,…，参数N)

=MAX(参数1,参数2,…，参数N)

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

的文字串。

例如，MIN(3,5,12,32)=3；MAX(3,5,12,32)=32。

COUNT函数和COUNTIF函数

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

公式为

=COUNT(参数1,参数2,…，参数N)

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

元格的数目。公式为

=COUNTIF(range,criteria)

式中range一需要计算其中满足条件的单元格数目的单元格

格区域；

criteria一确定哪些单元格将被计算在内的条件，其形式

可以为数字、表达式或文本。

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

常有用的函数。

IF函数

IF函数也称条件函数，它根据参数条件的真假，返回不同的

结果。在实践中，经常使用函数IF对数值和公式进行条件检

测。公式为

二IF(logical_test,value_if_true,value_iCfalse)

式中logical_test一条件表达式，其结果要么为TRUE,要么

为FALSE,它可使用任何比较运算符；

value_iCtrue一logical_test为TRUE时返回的值；

value」Jfalse—logical_test为FALSE时返回的值。

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

【例2-5】例如，某企业对各个销售部门的销售业绩进行

评价,评价标准及各个销售部门在2002年的销售业绩汇总如

图2-16所示，评价计算步骤如下：

ABDE

1晌皂的门协及*价

2vmn蜘评价结聚

3BfTA2S0743100000以下9

4BDB32S4S9]QQ(XM^200000一帙

S邦DC248015200000~300000好

6部CJD354129300000-400000较好

TBflF401254OIXWLLt飒

8筋B2M2S0

94S&S21

104102S6

111M226

12BflK290152好

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

(1)选定单元格区域C3:C12。

(2)直朦输入以不公式：'JlF(B3:B12vl00000,”差

",IF(B3:B12<200000,"一般”,IF(B3:B12V300000,“好

”,IF(B3:B12v400000,“较好“，“很好”))))"°

(3)按“Gtl+Shift+Enter”组合键。

则各个销售部门的销售业绩评价结果就显示在单元格域

C3:C12中。

也可以直接在单元格C3中输入公式“二IF(B3<100000,”差

”,IF(B3v200000,“一般”,IF(B3〈300000,“好”,IF(B3v400000,“较

好“，“很好”))))”后，将其向下填充复制到C4〜C12单元格中。

AND函数、OR函数和NOT函数

这3个函数的用法如下：

=AND(条件1,条件2,…，条件N)

=OR（条件1,条件2,…，条件N）

=NOT（条件）

AND函数表示逻辑与，当所有条件都满足时（即所有参数的

逻辑值都为真时），AND函数返回TRUE,否则，只要有一

个条件不满足即返回FALSEo

OR函数表示逻辑或，只要有一个条件满足时，OR函数返回

TRUE,只有当所有条件都不满足时才返回FALSEo

NOT函数只有一个逻辑参数,它可以计算出TRUE或FALSE

的逻辑值或逻辑表达式。如果逻辑值为FALSE,函数NOT

返回TRUE；如果逻辑值为TRUE,函数NOT返回FALSE。

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

【例2-61某企业根据各销售部门的销售额及销售费用确定

奖金提成比例及提取额，若销售额大于300000元且销售费

用占销售额的比例不超过1%,则奖金提取比例为15%,否则

为10%,则计算过程如下（如图2-17所示）:

（1）在单单元格D3中输入公公式

=IF（AND（B3>3OOOOO,C3/B3<1%）,15%,10%）”，将其向下填

充复制到D4〜C10单元格中。

（2）选取单元格区域E3:E10,输入公式“刍B3:B10*D3:D10”,

按“Crtl+Shift+Enter”组合键。

则各销售部门的销售奖金提成比例及奖金提取额如图2-17

所示。

g二]・田调前版000石质4司.15£石£；

1F

1一K—辆也?蓬3叫品向1*J（元I—一

2mran晌离融情旨费用奖金提成比倒奖金找窜龄

3IBHAn432SM2510%]23453

«412465S43010%43247

5546742590810%54674

6SODS68T364SO315%9S310

7部HE785833SM?IS%114875

8BOFXSTT3X4615%51881

9Bno29905012S610%28905

10BfTH423576542810%42358

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

LOOKUP函数、VLOOKUP函数和HLOOKUP

函数

1.LOOKUP函数

LOOKUP函数的功能是返回向量（单行区域或单列区域）或

数组中的数值。函数LOOKUP有两种语法形式：向量和数

组。函数LOOKUP的向量形式是在单行区域或单列区域（向

向量）中查找数值，然后返回第二个单行区域或单列区域中

相同位置的数值；函数LOOKUP的数组形式在数组的第一

行或第一列查找指定的数值，然后返回数组的最后一行或最

后一列中相同位置的数值。

(1)向量形式：公式为

=LOOKUP(lookup_value,lookup_vector,result_vector)

式中lookup_value一函数LOOKUP在第一个向量中所要查

找的数值，它可以为数字、文本、逻辑值或

包含数值的名称或引用；

lookup_vector一只包含一行或一列的区域lookup_vector

的数值可以为文本、数字或逻辑值；

result_vector一为只包含一行或一列的区域其大小必须

与lookup_vector相同。

(2)数组形式：公式为

=LOOKUP(lookup_value,array)

式中array一包含文本、数字或逻辑值的单元格区域或数组

它的值用于与lookup_value进行比较。

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

注意：lookup_vector的数值必须按升序排列，否则函数

LOOKUP不能返回正确的结果。文本不区分大小写。如果函

数LOOKUP找不至(Jlookup_value,则查找lookup_vector中

小于或等于lookup_value的最大数值。如果lookup_value小

于lookup_vector中的最小值，函数LOOKUP返回错误值

#N/Ao

2.VLOOKUP函数

VLOOKUP函数的功能是在表格或数值数组的首列查找指定

的数值，并由此返回表格或数组当前行中指定列处的数值。

公式为

=VLOOKUP(lookup_value,table_array,col_index_num,

range_lookup)

式中lookup_value—需要在数据表第一列中查找的数值，

lookup_value可以为数值、引用或文字串；

table_array一需要在其中查找数据的数据表，可以使用

对区域或区域名称的引用，例如数据库或数据清单；

如果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!。

rangejookup一逻辑值，指明函数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,Al:B4,2)=700。

3.HLOOKUP函数

HLOOKUP函数的功能是从表格或数值数组的首行查找指定

的数值，并由此返回表格或数组当前列中指定行处的数值。

公式为

=(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参数指定的行号

返回指定的单元格数值。

MATCH函数

MATCH函数的功能是返回在指定方式下与指定数值匹配的

数组中元素的相应位置。公式为：

二MATCH(lookup_value,lookup_array,match_type)

式中lookup_value—需要在数据表中查找的数值，可以是数

值(数字、文本或逻辑值)或对数字、文本

或逻辑值的单元格引用；

1ookup_array一可能包含所要查找的数值的连续单元格

区域，可以是数组或数组引用；

match_type一数字-1、0或1,它指明Excel如何在

lookup_array中查找lookup_valueo

查找方式如下：当match_type为-1时，lookup_array必须按

降序排列，函数MATCH查找大于或等于lookup_value的最

小数值；当match_type为。时，lookup_array可以按任何顺序

排列，函数MATCH查找等于lookup_vahie的第一个数值；

当match_type为1时，lookup_array必须按升序排列，函数

MATCH查找小于或等于lookup_value的最大数值。

例如，MATCH(12,(23,43,12,55},0)=3，

MATCH(40,{23,43,12,55})=1o

.1.2.9INDEX函数

INDEX函数的功能是返回表格或区域中的数值或对数值的

引用。INDEX函数有以下两种形式：

1.返回数组中指定单元格或单元格数组的数值。公式为

=INDEX(array,row_num,column_num)

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

row_num一数组中某行的行序号，函数从该行返回数值。

如果省略row_num,则必须有column_num；

column_num一数组中某列的列序号，函数从该列返回数

值8如果省嘴column_num,则J必须有

row_numo

需要注意的是：如果同时使用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}

2.返回引用中指定单元格。公式为

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使用区

域lo

说明：row_num、column_num和area_num必须指向reference

中的单元格,否则，函数INDEX返回错误值#REF!。如果省

略row_num和column_num,函数INDEX返回由area_num

所指定的区域。

函数INDEX的结果为一个引用，且在其他公式中也被解释

为引用。根据公式的需要，函数INDEX的返回值可以作为

引用或是数值。例如，公式CELL("width",INDEX(A1:B2,1,2))

等价于公式CELL("width",Bl)oCELL函数将函数INDEX的

速回值作珈单元格却用。而征另一方面，公式

2*INDEX(A1:B2,1,2)将函数INDEX的返回值解释为Bl单元

格中的数字。

0ADDRESS函数

ADDRESS函数的功能是按照给定的行号和列标，建立文本类

类型的单元格地址。公式为

-ADDRESS(row_num,column_num,abs_num,al,sheet_text)

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

column_num一在单元格引用中使用的列标;

abs_num一指明返回的引用类型，其中：当为1或省略时

为绝对引用，当为2时为绝对行号，相对列标,

当为3时为相对行号，绝对列标，当为4时为

相对引用；

al一用以指明A1或R1C1引用样式的逻辑值。如果A1

为TRUE或省略，函数ADDRESS返回A1样式的

引用,如果A1为FALSE,函数ADDRESS返回R1C1

样式的引用；

sheet_text——文本，指明作为外部引用的工作表的名称,

如果省略sheet_text,则不使用任何工作表名。

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

“C$2”。

1INDIRECT函数

INDIRECT函数的功能是返回由文字串指定的引用。此函数

立即对引用进行计算，并显示其内容。当需要更改公式中单

元格的引用，而不更改公式本身时，可使用此函数。公式为

二INDIRECT(ref_text,al)

式中ref_text一对单元格的引用，此单元格可以包含A1样

式的引用、R1C1样式的引用、定义为引用的名

称或对文字串单元格的引用，如果refjext不是

合法的单元格的引用，函数INDIRECT返回错

误值#REF!；

al一—逻辑值，指明包含在单元格ref_text中的引用的

类型，如果al为TRUE或省略，ref_text被解释为

A1样式的引用，如果al为FALSE,refjext被解

释为R1C1样式的引用。

需要注意的是，如果ref_text是对另一个工作簿的引用(外

部引用)，则那个工作簿必须被打开。如果源工作簿没有打

开，函数INDIRECT返回错误值#REF!。

例如：如果单元格A1包含文本“B2”，且单元格B2包含数值

1.333,则:INDIRECT($A$1)=1.333o

上述介绍锄几个查摭函数LOOKUP、VLOOKUP、、

HLOOKUP、MATCH>INDEX、ADDRESS>INDIRECT等

在财务分析与决策、预测及建立动态图表等中是非常有用

的。

2矩阵函数——TRANSPOSE函数、MINVERSE

函数和MMULT函数

1.TRANSPOSE函数

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

=TRANSPOSE(array)

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

域。

函数TRANSPOSE必须在某个区域中以数组公式的形式输入

入，该区域的行数和列数分别与array的列数和行数相同。

【例2-7】假设矩阵A中的值如图2-18中单元格区域A2:C5,

求其转置矩阵的步骤如下：

0勺■:川野心丁灯衣iT

AD」」PJU11KL.

1♦店A学力®'同临自

2i乂2563I2：2154ST50SOW

亳二2?4JW；*•CT»wWW9ZJT3®

421nSI38251s

5.wMr«>«?mi«i$

图2-18求转置矩阵

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

(2)单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对

话框中选取函数TRANSPOSE,在该函数对话框中输入(可

用鼠标拾取)单元格A2:C5,按“Crtl+Shift+Enter”组合键,

即得转置矩阵如图2-18所示。

利用TRANSPOSE函数可以把工作表中的某些行(或列)排

列的数据转换成列(或行)排列的数据。例如，由于工作需

要，要把工作表中的某些行数据改为列数据，若一个一个地

改动数据，将是很麻烦也很费时的，而利用TRANSPOSE函

数则可以很轻松地进行这项工作。但需要注意的是，利用

TRANSPOSE函数对行(歹(J)数据进行转换，则无法单独修

改其中转换单元格区域中的某单元格的数据。

2.MINVERSE函数

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

=MINVERSE(array)

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

MINVERSE函数的使用方法与TRANSPOSE函数是一样的。

在求解线性方程组时，常常用到MINVERSE函数。

3.MMULT函数

MMULT函数的功能是返回两数组的矩阵乘积。结果矩阵的

行数与array1的行数相同，列数与array2的列数相同。公

式为

=MMULT(array1,array2)

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

arrayl的列数必须与array2的行数相同，而且两个数组中都

只能包含数值。arrayl和array2可以是单元格区域、数组常

数或引用。如果单元格是空白单元格或含有文字串，或是

arrayl的行数与array2的列数不相等时，则函数MMULT

返回错误值#VALUE!。

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

输入。

以例2-7的原矩阵和其转置矩阵为例，它们的乘积矩阵求解

方法如下：

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

(2)单击工具栏上的【粘贴函数】按钮，在【粘贴函数】对

话框中选取函数MMULT,在该函数对话框中的arrayl栏中

输入(可用鼠标拾取)单元格区域A2:C5,在array2栏中输

入单元格区域E2:H4,然后按“Crtl+Shift+Enter”组合键，即

得矩阵的乘积如图2-18所示。

3ROUND函数

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

字。公式为

=ROUND(number,num_digits)

式中number一需要进行舍入的数字;

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

如果num_digits大于0,则舍入到指定的小数位；如果

num_digits等于0,则舍入到最接近的整数；如果num_digits

小于0,则在小数点左侧进行舍入。

利用ROUND函数可以防止利用格式工具栏上的【增加小数

位数】或【减少小数位数】所带来的看起来“假数据”问题

的出现，使得工作表上显示的数据真实可靠。实际上，如果

需要调整数据的小数位数，最好使用ROUND函数，而不要

使用格式工具栏上的【增加小数位数】或【减少小数位数】

按钮。

例如，若单元格A1中的数据为14.3772,若使用格式工具栏

上的【减少小数位数】按钮将小数位数设为两位，则单元格

A1中的数据显示为14.38,看起来似乎单元格A1的数据为

14.38,但实际上仍为14.3772。若在单元格B1中输入公式

“=3*A1"，则单元格Bl中的数据显示为43.13,也许“不明

真相”的人认为单元格B1的数据算错了（14.38乘以3应该

等于43.14）,但实际上单元格的数据为43.1316,这种看起

来的“假”数据可能会对实际工作带来不便。因此，正确的

方法应是：单元格B1中应输入公式

“=ROUND(3*ROUND(A1,2),2)"，结果为43.14,即先将单

元格Al的数据用函数ROUND四舍五入，然后再对计算后

的数据四舍五入。

第2章Excel在财务管理与分析中的应用基础知识

2.2图表处理(1)

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

线图、饼图等标准图表，还可以生成较为复杂的三维立体图

表。对各种财务数据进行图表处理，可以更直观地进行财务

分析，找出工作表格不容易发现的问题，使得财务管理工作

更为有效。

2.2.1图表类型

Excel提供了约14种标准图表类型，如面积图、柱形图、条

形图、折线图、饼图、圆环图、气泡图、雷达图、股价图、

曲面图、散点图、锥形图、圆柱图、棱锥图等，每种图表类

型又都有几种不同的子类型。此外，Excel还提供了约20种

自定义图表类型，用户可根据不同的需要选用适当的图表类

型

关于各种图表类型的具体情况，可单击工具栏上的【图表向

导】按钮勤，或单击【插入】菜单，选择【图表】项，即可

查看各种图表。

2.2.2图表的建立

建立图表的过程非常简单，只要按照【图表向导】的有关说

明，一步一步地进行操作，即可完成图表的制作。下面结合

实例进行说明。

【例2-8］某企业2002年12个月的销售量与销售费用的

有关数据如图2-19所示，绘制各月销售额与销售费用之间关

系的图表的步骤如下：

12

90<0G40O9600HT6010640”200996012000125601<210IC6O14820

25022502««05320<X3SIX30505630«K&3105so61Q

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

(1)选取单元格区域A2：M3,单击工具栏上的【图表向导】

按钮，或单击【插入】菜单，选择【图表】项，出现【图表

向导一4步骤之1—图表类型】对话框，如图2-20所示，在

【图表类型】中选【折线图工在【子图表类型】中选【数

据点折线图】，单击【下一步】按钮，出现【图表向导-4步

骤之2—图表数据源】对话框，如图2-21所示。

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

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

(2)在【图表向导-4步骤之2—图表源数据】对话框中，

单击【系列】，在【分类(X)轴标恶】栏中埋入

“=Sheetl!$B$l:$M$l"，单击【下一步】按钮，出现【图表向

导一4步骤之3—图表选项】对话框，如图2-22所示。

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

(3)在【图表向导-4步骤之3—图表选项】对话框中，对

【标题】的各项进行输入，即在【图表标题】栏中填入“'销

售额和销售费用的变化图”，在【数值X轴】栏中填入“月

份”，在【数值Y轴】栏中填入“金额(千元)"，单击【下

一步】按钮，出现【图表向导-4步骤之4—图表位置】如图

2-23所示。

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

(4)在【图表向导-4步骤之4—图表位置】对话框中，若

要建立嵌入式图表，即图表嵌入在本工作表中，则选择“作

为其中的对象插入”；若要建立工作表图表，则选择“作为

新工作表插入”。这里选择“作为其中的对象插入”；然后单

击【完成】按钮，即得到需要的图表，如图2-24所示。

专费用由度化图T-销售额

T-销售费用

20000

15000

1/H

卷10000

侬

5000

0

123456789101112

月份

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

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

通过图表向导建立的图表可能不尽人意，如标题太大或太

小、坐标系列太多、图表尺寸太小、漏掉了数据系列、需要

添加数据标志等等，这时就需要对图表进行修改和格式化。

1.设置坐标、标题、图例等的格式

设置坐标、标题、图例等的格式的方法非常简单，可将鼠标

移到坐标、标题、图例等上，单击右键，在快捷菜单上选择

相应的项目即可。例如要改变X坐标大小，单击右键，出现

快捷菜单，选择【坐标轴格式】项，就会弹出【坐标轴格式】

对话框，如图2-25所示，选择需要修改的项目，进行设置即

可。

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

2.改变图表大小

单击图表区域，将它激活，图表边框出现8个操作柄，用鼠

标指向某个操作柄，当鼠标指针呈现双箭头时，按住左键不

放，拖动操作柄到需要的位置上，然后放开鼠标左键，即可

完成。

3.移动或复制图表

移动：单击图表区域，将它激活，图表边框出现8个操作柄，

在图表区域按住鼠标左键不放，拖动鼠标将图表移到需要的

地方。

复制：单击图表区域，将它激活，图表边框出现8个操作柄，

在图表区域按住鼠标左键不放，拖动鼠标将图表移到需要的

地方，按Ctrl键，然后放开鼠标。若需要将图表复制到其他

工作表或其他文件中，可选中图表，按“Ctrl+C”键，再在

需要安置图表的工作表或其他文件的适当位置，按“Ctrl+V”

键。

4.添加数据标志

在很多情况下，在图表上添加数据标志，可以更直观地表示

因素的变化情况。添加数据标志的步骤如下：单击需要显示

数据标志的数据点，然后按鼠标右键，出现快捷菜单，如图

2-26所示，选择【数据系列格式】对话框，单击【数据标志】,

选择“显示值”。

旨数据系列格式

雄音中…

敷露》0

添加修势

相Q)

图2-26快捷菜单

通过【数据系列格式】对话框，还可以进行其他方面的修改

或格式化，有关内容可单击【数据系列格式】对话框的有关

项。

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

5.改变图表颜色、图案、边框

改变图表颜色、图案、边框，可通过【图表区格式】对话框

来完成，方法是：单击图表区域，单击鼠标右键，在快捷菜

单中选【图表区格式】项，弹出【图表区格式】对话框，即

可进行相应的修改。

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

企业的产品销往全国各地及世界各地，各地的销售量是不同

的，我们可以利用Excel的地图分析工具建立销售数据地图,

从而可以将企业产品在各地的销售情况更加直观地表示出

来。

【例2-9】某企业在某些省份的销售数据如图2-28所示，则

建立数据地图的步骤如下：

AB厂

jd产工怕g记

可比京而2

37,。小342

＜上却由276

5.河牝令'<S9

61山西猫I4咖6

7:辽亍♦3

日林令

885

to河启河

11江苏■

图2-28销售数据

(1)选中数据区域A2:B15。

(2)单击【插入】菜单，执行【对象】命令，弹出【对象】

对话框,如图2-29所示，选中“Microsoft地图”，则Excel

就会根据所选的工作表数据建立如图2-30所示的数据地图;

在数据地图中，数据越多的区域(省份)，颜色就越深。

(3)在图2-30中所示的地图中没有标明省份名称，但可以

通过下述方法加入省份名称：双击地图，出现地图的菜单，

如图2-31所示，单击地图菜单上的【工具】，选择【标志】

项，出现【地图标志】对话框，如图2-32所示；

(4)在【地图标志】对话框中，【需要设置标志的地图项】

中选择“中国”，【创建标志】中选择“地图项名称”，然后单

击【确定】按钮。

图2-29【对象】对话框

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

tael-堆/在熔SI

■文件9M«）四用现）■入3xaa）3B，＞・0g片物如

ITCI-1HA|*I•4|B!jg5M^gE—|W

-------z---------;--------c-----_2z£!___FT----2-

图2-31地图菜单项

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

(5)在地图上移动鼠标，在鼠标移动过程中，Excel会显示

该区域所对应的省份名称，单击左键，该省份名称就会标注

在对应的省份区域上(图表太小，此处省略)。

(6)如果需要，还可以对各省份添加数量标志，方法是：

双击地图，出现【Microsoft地图控件】对话框，如图2-33

所示，根据需要选择数据类型格式，用鼠标把需要的格式拖

放在对话框右边区域中的格式上，然后把要设置这种格式的

数据列(对话框中的【第B列】)拖放在图中的“列”字框

上即可。［Microsoft地图控件】对话框提供了6种不同的数

据类型格式。图2-20就是第1种数据类型格式。

图2-33[Microsoft地图控件】对话框

需要注意的是，图2-28的工作表数据中的各省份名称必须与

Excel所规定的相同，否则可能会出现意想不到的错误。Excel

所规定的各省份名称如下：黑龙江省、吉林省、辽宁省、内

蒙古自治区、新疆维吾尔自治区、北京市、天津市、河北省、

山西省、陕西省、青海省、宁夏回族自治区、西藏自治区、

山东省、河南省、江苏省、浙江省、上海市、安徽省、湖北

省、湖南省、福建省、广东省、广西壮族自治区、江西省、

四川省、云南省、贵州省、海南省、香港、台湾省、澳门。

2.2.5动态图表的建立

在企业的经营活动中，往往需要为每个部门建立大量相似的

图表，如果在一张工作表上建立太多的图表，既费时也使得

图表显得凌乱不堪。我们可以建立动态图表来解决这个问

题，当需要了解某个部门的销售情况时，只需将鼠标移到工

作表中该部门的单元格上，即可立即显示出该部门的销售图

表。

【例2-10】某企业的8个销售部门一年内各月的销售量数据

如图2-34所示，建立各部门的动态图表的步骤如下：

图2-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键(即对工作表数据重新计算)，就会显