excel常用函数绝对是精华.docx

excel常用函数绝对是精华.docx

《excel常用函数绝对是精华.docx》由会员分享，可在线阅读，更多相关《excel常用函数绝对是精华.docx（21页珍藏版）》请在冰豆网上搜索。

excel常用函数绝对是精华

第一章成绩查询常用函数2

1．求和函数SUM2

2．条件求和函数SUMIF3

3.逻辑函数IF3

4.平均值函数AVERAGE4

5．快捷方式或链接创建函数HYPERLINK4

6．计数函数COUNT5

7．条件计数函数COUNTIF5

8．最大值函数MAX、最小值函数MIN5

9排序函数RANK6

10.测函数ISNUMBER6

11、ISERROR函数6

12、FREQUENCY函数7

13、INDEX函数7

14、MATCH函数7

15、VLOOKUP函数8

16.RANDBETWEEN8

第二章：

财务函数8

1.ACCRINT8

2.ACCRINTM9

3.AMORDEGRC9

4.AMORLINC9

5.COUPDAYBS10

6.COUPDAYS10

7.COUPDAYSNC10

8.COUPNUM10

9.COUPPCD11

10.CUMIPMT11

11.CUMPRINC11

12.DB12

13.DDB12

14.DISC12

15.DOLLARDE12

16.DOLLARFR13

17.DURATION13

18.EFFECT13

19.FV13

20.FVSCHEDULE13

21.INTRATE14

22.IPMT14

23.IRR14

24.ISPMT14

25.MDURATION15

26.MIRR15

27.NOMINAL15

28.NPER15

29.NPV16

30.ODDFPRICE16

31.ODDFYIELD16

32.ODDLPRICE17

33.ODDLYIELD17

34.PMT17

35.PPMT18

36.PRICE18

37.PRICEDISC18

38.PRICEMAT18

39.PV19

40.RATE19

41.RECEIVED19

42.SLN19

43.SYD20

44.TBILLEQ20

45.TBILLPRICE20

46.TBILLYIELD20

47.VDB20

48.XIRR21

49.XNPV21

50.YIELD21

51.YIELDDISC22

52.YIELDMAT22

第一章成绩查询常用函数

1．求和函数SUM

语法：

SUM（number1，number2，．．．）。

参数：

number1、number2．．．为1到30个数值（包括逻辑值和文本表达式）、区域或引用，各参数之间必须用逗号加以分隔。

注：

number1可以是b1：

h3，这样的形式也可以是整个表格，并且number2也可以和number1一样，他们这间需要用逗号隔开，number2还可以是另一个表格的数据。

注意：

参数中的数字、逻辑值及数字的文本表达式可以参与计算，其中逻辑值被转换为1，文本则被转换为数字。

如果参数为数组或引用，只有其中的数字参与计算，数组或引用中的空白单元格、逻辑值、文本或错误值则被忽略。

应用实例一：

跨表求和（高）

使用SUM函数在同一工作表中求和比较简单，如果需要对不同工作表的多个区域进行求和，可以采用以下方法：

选中ExcelXP“插入函数”对话框中的函数，“确定”后打开“函数参数”对话框。

切换至第一个工作表，鼠标单击“number1”框后选中需要求和的区域。

如果同一工作表中的其他区域需要参与计算，可以单击“number2”框，再次选中工作表中要计算的其他区域。

上述操作完成后切换至第二个工作表，重复上述操作即可完成输入。

“确定”后公式所在单元格将显示计算结果。

应用实例二：

SUM函数中的加减混合运算

财务统计需要进行加减混合运算，例如扣除现金流量表中的若干支出项目。

按照规定，工作表中的这些项目没有输入负号。

这时可以构造“=SUM（B2:

B6，C2:

C9，-D2，-E2）”这样的公式。

其中B2:

B6，C2:

C9引用是收入，而D2、E2为支出。

由于Excel不允许在单元格引用前面加负号，所以应在表示支出的单元格前加负号，这样即可计算出正确结果。

即使支出数据所在的单元格连续，也必须用逗号将它们逐个隔开，写成“=SUM（B2:

B6，C2:

C9，-D2，-D3，D4）”这样的形式。

应用实例三：

及格人数统计

假如B1:

B50区域存放学生性别，C1:

C50单元格存放某班学生的考试成绩，要想统计考试成绩及格的女生人数。

可以使用公式“=SUM（IF（B1:

B50=〃女〃，IF（C1:

C50>=60，1，0）））”，由于它是一个数组公式，输入结束后必须按住Ctrl+Shift键回车。

公式两边会自动添加上大括号，在编辑栏显示为“{=SUM（IF（B1:

B50=〃女〃，IF（C1:

C50>=60，1，0）））}”，这是使用数组公式必不可少的步骤。

2．条件求和函数SUMIF

语法：

SUMIF（range，criteria，sum_range）。

参数：

range是用于条件判断的单元格区域，criteria是由数字、逻辑表达式等组成的判定条件，sum_range为需要求和的单元格、区域或引用。

应用实例：

及格平均分统计

假如A1:

A36单元格存放某班学生的考试成绩，若要计算及格学生的平均分，可以使用公式“=SUMIF（A1:

A36，〃>=60〃，A1:

A36）/COUNTIF（A1:

A36，〃>=60〃）。

公式中的“=SUMIF（A1:

A36，〃>=60〃，A1:

A36）”计算及格学生的总分，式中的“A1:

A36”为提供逻辑判断依据的单元格引用，“>=60”为判断条件，不符合条件的数据不参与求和，A1:

A36则是逻辑判断和求和的对象。

公式中的COUNTIF（A1:

A36，〃>=60〃）用来统计及格学生的人数。

3.逻辑函数IF

语法：

IF（①logical_test，②value_if_true，③value_if_false）。

参数：

logical_test是结果为true（真）或false（假）的数值或表达式；value_if_true是logical_test为true时函数的返回值，如果logical_test为ture并且省略了value_if_true，则返回true。

而且value_if_true可以是一个表达式；value_if_false是logical_test为false时函数的返回值。

如果logical_test为false并且省略value_if_false，则返回false。

Value_if_false也可以是一个表达式。

注：

红字的内容为，当①为真时则将2的值返回，如果为假则将3的值返回。

2，3可以是表达式或值，还可以是if语句，这就是if嵌套。

应用实例一：

个人收入调节税计算

假设个人收入调节税的稽征办法是：

工资在1000元以下的免征调节税，工资1000元以上至1500元的超过部分按5%的税率征收，1500元以上至2000元的超过部分按8%的税率征收，高于2000元的超过部分按30%的税率征收。

假如工作表的B列存放职工姓名，C列存放工资，选中D列的空白单元格输入公式“=IF（C2<=1000，〃〃，IF（（C2-1000）<=1500，（C2-1000）*0.05，IF（C2-1500）<=2000，（C2-1500）*0.08，IF（C2>2000，（C2-2000）*0.3））））”，回车后即可计算出C2工资应缴纳的收入调节税。

公式中的IF语句是逐次计算的，如果第一个逻辑判断C2<=1000成立，则公式所在单元格被填入空格；如果第一个逻辑判断式不成立，则计算第二个IF语句；直至计算结束。

如果税率征收标准发生了变化，只须改变逻辑和计算式中的值，如1000、1500和2000等即可。

应用实例二：

消除无意义的零

用SUM函数计算工资总额等问题时，若引用的全部参数均为空白单元格，公式仍然会计算出一个“0”。

这样打印出来的报表不仅很不美观。

为此可将计算公式设计为“=IF（SUM（A1:

B1，D2:

E8）<>0，SUM（A1:

B1，D2:

E8），〃〃）”，即是当SUM（A1:

B1，D2:

E8）计算结果不等于零时，公式所在单元格显示SUM（A1:

B1，D2:

E8）的计算结果，否则显示一个空格。

上面这个问题在财务计算中依然存在，如公式“=A1-A6-A9”有可能为零或显示负数。

为了避免打印零或负数，可以设计公式“=IF（A2-A6-A9=0，〃〃，IF（A2-A6-A9<0，RMB（A2-A6-A9），A2-A6-A9））”。

当A2-A6-A9=0时在单元格中填写零，否则进行下一个逻辑判断。

如果A2-A6-A9<0则在结果前添加一个“￥”符号，否则进行正常运算直至结束。

应用实例三：

多条件求和

假设C1:

C460区域内存放着职工的职称，D1:

D460区域存放着职工的性别。

如果要统计具有高级职称的男性职工总数，可以使用公式“=SUM（IF（C1:

C460=〃高级〃，IF（D1:

D460=〃男〃，1，0）））”。

这是一个数组公式，输入结束后按住Ctrl+Shift回车即可计算出结果。

4.平均值函数AVERAGE

语法：

AVERAGE（number1，number2，．．．）。

参数：

number1、number2．．．是需要计算平均值的1～30个参数。

注意：

参数可以是数字、包含数字的名称、数组或引用。

数组或单元格引用中的文字、逻辑值或空白单元格将被忽略，但单元格中的零则参与计算。

如果需要将参数中的零排除在外，则要使用特殊设计的公式，下面的介绍。

应用实例一：

跨表计算平均值

标签名为“一班”、“二班”和“三班”的工作表存放各班学生的成绩，则它们的总平均分计算公式为“=AVERAGE（一班!

C1:

C36，三班!

C1:

C32，三班!

C1:

C45）”。

式中的引用输入方法与SUM跨表求和时相同。

跨表求值：

最前面写标签名后面接!

再后面接所要求值的区域。

应用实例二：

忽略零的平均值计算公式

假设A1:

A200随机存放包括零在内的48个数值，在AVERAGE参数中去掉零引用很麻烦，这种情况可以使用公式“=AVERAGE（IF（A1:

A200<>0，A1:

A200，〃〃）”。

公式输入结束后按住Ctrl+Shift回车，即可对A1:

A200中的数值（不包括零）计算平均值。

5．快捷方式或链接创建函数HYPERLINK

语法：

HYPERLINK（link_location，friendly_name）。

参数：

link_location是文件的路径和文件名，它可以指向文档中的某个具体位置。

如Excel工作表中的特定单元格或区域，或某个Word文档中的书签，也可以指向硬盘中的文件或是Internet或Intranet的URL。

Friendly_name为单元格中显示的链接文字或数字，它用蓝色显示并带有下划线。

如果省略了friendly_name，单元格就将link_location显示为链接。

应用实例：

管理文档或网站地址

如果你拥有大量文档或收集了许多网站地址，管理起来一定有困难。

如果能够将其中的文档名、URL等与文档等对象链接起来，不仅管理方便，还可以直接打开文档或访问站点。

具体操作方法是：

首先根据文档类型建立管理工作表，其中的文件名或网站名必须使用以下方法输入：

选中一个空白单元格，单击ExcelXP工具栏中的“粘贴函数”按钮，打开“粘贴函数”对话框，在“函数分类”框下选中“查找与引用”，然后在“函数名”框内找到HYPERLINK函数。

单击对话框中的“确定”按钮，弹出“HYPERLINK”函数向导。

在“link_location”框中输入文件的完整路径和名称（包括扩展名），如“C:

＼mydocuments＼IT网站集锦.doc”，然后在“fr