EXCEL公式及函数应用技巧.docx
《EXCEL公式及函数应用技巧.docx》由会员分享，可在线阅读，更多相关《EXCEL公式及函数应用技巧.docx（40页珍藏版）》请在冰豆网上搜索。
EXCEL公式及函数应用技巧
EXCEL公式及函数的高级应用
公式和函数是Excel最基本、最重要的应用工具，是Excel的核
心，所以，应付公式和函数娴熟掌握，才能在实质应用中驾轻就熟。
数组公式及其应用
数组公式就是能够同时进行多重计算并返回一种或多种结果的公
式。
在数组公式中使用两组或多组数据称为数组参数，数组参数能够
是一个数据地区，也能够是数组常量。
数组公式中的每个数组参数必
须有相同数目的行和列。
数组公式的输入、编写及删除
一．数组公式的输入
数组公式的输入步骤以下：
（1）选定单元格或单元格地区。
假如数组公式将返回一个结果，
单击需要输入数组公式的单元格；假如数组公式将返回多个结果，则
要选定需要输入数组公式的单元格地区。
（2）输入数组公式。
（3）同时按“Crtl+Shift+Enter”组合键，则Excel自动在公式
的两边加上大括号{}。
特别要注意的是，第（3）步相当重要，只有输入公式后同时按
“Crtl+Shift+Enter”组合键，系统才会把公式视为一个数组公式。
不然，假如只按Enter键，则输入的不过一个简单的公式，也只在选
中的单元格地区的第1个单元格显示出一个计算结果。
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
所示。
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所示。
3
图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所示。
4
图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所
示。
5
图1-7选用单元格地区A3:
D3
图1-8在编写栏中输入数组公式
（3）同时按Ctrl+Shift+Enter组合键，即可在单元格A3:
D3中得
到数组公式“=A1:
D1+A2:
D2”，如图2-9所示。
图1-9同时按Ctrl+Shift+Enter组合键，获得数组公式
二．编写数组公式
数组公式的特点之一就是不可以独自编写、消除或挪动数组公式所波及的单元格地区中的某一个单元格。
若在数组公式输入完成后发现错误
6
需要改正，则需要按以下步骤进行：
（1）在数组地区中单击任一单元格。
（2）单击公式编写栏，当编写栏被激活时，大括号“{}”在数组公式中消逝。
（3）编写数组公式内容。
（4）改正完成后，按“Crtl+Shift+Enter”组合键。
要特别注意不要忘掉这一步。
三．删除数组公式
删除数组公式的步骤是：
第一选定寄存数组公式的全部单元格，而后
按Delete键。
数组公式的应用
一．用数组公式计算两个数据地区的乘积
【例2-1】如图2-10所示，已经知道12个月的销售量和产品单价，则能够利用数组公式计算每个月的销售额，步骤以下：
图1-10用数组公式计算销售额
7
（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年的总销售额，方法以下：
8
图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%，则能够利用数组公式进行计算，步骤以下：
9
图1-12产品降价计算
（1）选用单元格地区G3:
I8。
（2）输入公式“=B3:
D8*（1-20%）”。
（3）按Crtl+Shift+Enter组合键。
别的，当对构造相同的不一样工作表数据进行归并汇总办理时，利
用上述方法也将是特别方便的。
相关不一样工作表单元格的引用可参阅
第1章的相关内容，对于数据的归并计算可参阅本章节的内容。
常用函数及其应用
在第1节中介绍了一些相关函数的基本知识，本节对在财务管理中常用的一般函数应用进行说明，其余相关的特意财务函数将在此后的相关章节中分别予以介绍。
1.2.1SUM函数、SUMIF函数和SUMPRODUCT函数
在财务管理中，应用最多的是乞降函数。
乞降函数有三个：
无条
件乞降SUM函数、条件乞降SUMIF函数和多组数据相乘乞降
SUMPRODUCT函数。
一．无条件乞降SUM函数
该函数是求30个之内参数的和。
公式为=SUM（参数1，参数
2，，参数N）
当对某一行或某一列的连续数据进行乞降时，还能够使用工具栏
10
中的自动乞降按钮。
比如，在例2-1中，求整年的销售量，则能够单击单元格N2，然
后再单击乞降按钮，按回车键即可，如
图1-13所示。
11
图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）”，即可获得分类销售额汇总表。
12
图1-14商品销售额分类汇总
SUMIF函数的对话框如图1-15所示。
图1-15SUMIF函数对话框
当需要分类汇总的数据很大时，利用SUMIF函数是很方便的。
三．SUMPRODUCT函数
SUMPRODUCT函数的功能是在给定的几组数组中，将数组间对应的
元素相乘，并返回乘积之和。
公式为=SUMPRODUCT（array1,array2,array3,）
式中，array1,array2,array3,...为1至30个数组。
13
需注意的是，数组参数一定拥有相同的维数，不然，函数SUMPRODUCT将返回错误值#VALUE!
。
对于非数值型的数组元素将作为
1办理。
比如，在例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.4COUNT函数和COUNTIF函数
COUNT函数的功能是计算给定地区内数值型参数的数目。
公式为：
=COUNT（参数1，参数2，，参数N）
14
COUNTIF函数的功能是计算给定地区内知足特定条件的单元格的数
目。
公式为：
=COUNTIF（range，criteria）
式中range—需要计算此中知足条件的单元格数目的单元格区
域；
criteria—确立哪些单元格将被计算在内的条件，其形式
能够为数字、表达式或文本。
COUNT函数和COUNTIF函数在数据汇总统计剖析中是特别实用的
函数。
1.2.5IF函数
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所示，
评论计算步骤以下：
15
图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函数表示逻辑与，当全部条件都知足时（即全部参数的逻辑
16
值都为真时），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所示。
17
图1-17奖金提成比率及提取额的计算
1.2.7LOOKUP函数、VLOOKUP函数和HLOOKUP函数
一．LOOKUP函数
LOOKUP函数的功能是返回向量（单行地区或单列地区）或数组中
的数值。
函数LOOKUP有两种语法形式：
向量和数组。
函数LOOKUP的
向量形式是在单行地区或单列地区（向量）中查找数值，而后返回第
二个单行地区或单列地区中相同地点的数值；函数LOOKUP的数组形
式在数组的第一行或第一列查找指定的数值，而后返回数组的最后一
行或最后一列中相同地点的数值。
（1）向量形式：
公式为=
LOOKUP（lookup_value,lookup_vector,result_vector）
式中lookup_value—函数LOOKUP在第一个向量中所要查找的
数值，它能够为数字、文本、逻辑值或包括数值的名称或引用；
lookup_vector—只包括一行或一列的地区lookup_vector的数
值能够为文本、数字或逻辑值；
result_vector—为只包含一行或一列的区域其大小必须与
18
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—需要在此中查找数据的数据表，能够使用对地区或地区
名称的引用，比如数据库或数据清单；
假如range_lookup为TRUE，则table_array的第一列中的数值
一定按升序摆列，不然函数VLOOKUP不可以返回正确的数值，假如
range_lookup为FALSE，table_array不用进行排序。
table_array
的第一列中的数值能够为文本、数字或逻辑值，且不划分文本的大小
19
写；
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函数的功能是从表格或数值数组的首行查找指定的数
值，并由此返回表格或数组目前列中指定行处的数值。
公式为：
=（lookup_value，table_array，row_index_num，range_lookup）
20
式中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
升级会员 