Excel公式及函数的高级应用教程.docx

Excel公式及函数的高级应用教程.docx

《Excel公式及函数的高级应用教程.docx》由会员分享，可在线阅读，更多相关《Excel公式及函数的高级应用教程.docx（56页珍藏版）》请在冰豆网上搜索。

Excel公式及函数的高级应用教程

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1.2.13ROUND函数………………………………………………….……………..….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

三．添加新的记录………………………………………………………….…………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图表处理

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

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数组公式及其应用

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

在数组公式中使用两组或多组数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。

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

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

一．数组公式的输入

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

（1）选定单元格或单元格区域。

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

（2）输入数组公式。

（3）同时按“Crtl+Shift+Enter”组合键，则Excel自动在公式的两边加上大括号{}。

特别要注意的是，第（3）步相当重要，只有输入公式后同时按“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组公式。

否则，如果只按Enter键，则输入的只是一个简单的公式，也只在选中的单元格区域的第1个单元格显示出一个计算结果。

在数组公式中，通常都使用单元格区域引用，但也可以直接键入数值数组，这样键入的数值数组被称为数组常量。

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

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

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

（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，则可以采用下述的方法：

图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、C2、D2中分别输入了10、20、30、40和50、60、70、80，如图2-6所示。

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

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

D3

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

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

D3中得到数组公式“=A1:

D1+A2:

D2”，如图2-9所示。

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

二．编辑数组公式

数组公式的特征之一就是不能单独编辑、清除或移动数组公式所涉及的单元格区域中的某一个单元格。

若在数组公式输入完毕后发现错误需要修改，则需要按以下步骤进行：

（1）在数组区域中单击任一单元格。

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

（3）编辑数组公式内容。

（4）修改完毕后，按“Crtl+Shift+Enter”组合键。

要特别注意不要忘记这一步。

三．删除数组公式

删除数组公式的步骤是：

首先选定存放数组公式的所有单元格，然后按Delete键。

1.1.2数组公式的应用

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

【例2-1】如图2-10所示，已经知道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所示，则可以利用数组公式计算该企业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。

（2）输入公式“=B3:

D8*（1-20%）”。

（3）按Crtl+Shift+Enter组合键。

此外，当对结构相同的不同工作表数据进行合并汇总处理时，利用上述方法也将是非常方便的。

有关不同工作表单元格的引用可参阅第1章的有关内容，关于数据的合并计算可参阅本章2.3.5节的内容。

1.2常用函数及其应用

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

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

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

求和函数有三个：

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

一．无条件求和SUM函数

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

公式为=SUM（参数1，参数2，…，参数N）

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

。

例如，在例2-1中，求全年的销售量，则可以单击单元格N2，然后再单击求和按钮

，按回车键即可，如图1-13所示。

图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）”，即可得到分类销售额汇总表。

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

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

图1-15 SUMIF函数对话框

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

三．SUMPRODUCT函数

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

公式为=SUMPRODUCT（array1,array2,array3,…）

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

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

。

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

例如，在例1-2中，要计算2002年产品A的销售总额，可在任一单元格（比如O2）中输入公式“=SUMPRODUCT（C2:

N2,C3:

N3）”即可。

1.2.2AVERAGE函数

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

公式为=AVERAGE（参数1，参数2，…，参数N）

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

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

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

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

1.2.3MIN函数和MAX函数

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

公式为

=MIN（参数1，参数2，…，参数N）

=MAX（参数1，参数2，…，参数N）

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

例如，MIN（3，5，12，32）=3；MAX（3，5，12，32）=32。

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时返回的值；

value_if_false—logical_test为FALSE时返回的值。

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

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

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

（1）选定单元格区域C3:

C12。

（2）直接输入以下公式：

“=IF（B3:

B12<100000,"差",IF（B3:

B12<200000,"一般",IF（B3:

B12<300000,"好",IF（B3:

B12<400000,"较好","很好"））））”。

（3）按“Crtl+Shift+Enter”组合键。

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

C12中。

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

1.2.6AND函数、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单元格中。

（2）选取单元格区域E3:

E10，输入公式“=B3:

B10*D3:

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

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

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

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

一．LOOKUP函数

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

函数LOOKUP有两种语法形式：

向量和数组。

函数LOOKUP的向量形式是在单行区域或单列区域（向量）中查找数值，然后返回第二个单行区域或单列区域中相同位置的数值