严福文之常用函数教程

1、ExcelExcel常用函数应用教程常用函数应用教程讲师：严福文讲师：严福文EXCELEXCEL常用常用函数应用教程函数应用教程1.1.EXCELEXCEL函数的基本知识函数的基本知识2.2.逻辑函数的应用逻辑函数的应用3.3.文本函数的应用文本函数的应用4.4.日期与时间函数日期与时间函数5.5.查询查询与引用与引用函数函数Unit 1 EXCELUnit 1 EXCEL函数的基本知识函数的基本知识1.1.什么什么是函数是函数2.2.函数的参数和返回值函数的参数和返回值3.3.函数的地址符函数的地址符4.4.函数的套用函数的套用 Excel Excel中所提的中所提的函数函数其实是一些预定义

2、的公式，它们使用一其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。些称为参数的特定数值按特定的顺序或结构进行计算。简单点说，函数是一组功能模块，使用函数能帮助你实现某个简单点说，函数是一组功能模块，使用函数能帮助你实现某个功能。功能。举例：举例：SumSum函数可以实现数值的加法运算，函数可以实现数值的加法运算，maxmax函数可以返回一组函数可以返回一组数的最大值。数的最大值。 1.1.什么什么是函数是函数=（B4+25）/SUM(D5:F5)公式特定符号单元格地址运算操作符号EXCEL函数数值常量数值常量区域范围引用函数说明2. 2. 函数的参数及返回值函

3、数的参数及返回值1.1.参数可以是数字、文本、形如参数可以是数字、文本、形如 TRUE TRUE 或或 FALSE FALSE 的逻辑数值、的逻辑数值、数组。数组。数字数字：1 1、2 2、100100、1000010000文本：文本：astronastron、412398784737847412398784737847（以文本格式存储的数字）（以文本格式存储的数字）逻辑值：逻辑值：TRUE(TRUE(真真) )或非零（一般用数字或非零（一般用数字1 1表示）、表示）、FALSE(FALSE(假假) )或或0 0比如比如13,1313这个不等式的值为假。这个不等式的值为假。单元格：比如单元格：

4、比如A47A47、B16B16数组：同一种格式存储的单元格区域数组：同一种格式存储的单元格区域注意：函数使用的参数所用的符号，如逗号，引号要全部在注意：函数使用的参数所用的符号，如逗号，引号要全部在英文输入状态下输入英文输入状态下输入( (半角符号半角符号) )。 错误类型原因举例#N/A使用查找函数找不到数据#VALUE! 参数或操作数类型错误=sum(“a”,”b”)#Null！ 不正确的区域运算符=SUM(B2:C2 B3:C3)错误类型原因举例#DIV/0!当数字被零 (0) 除=100/0#REF!单元格引用 无效=VLOOKUP(C,E3:F11,4,0)#列不够宽，或使用了负的日

5、期或负的时间-2008-1-2#NAME? 函数名错误=sun(a1:a5)3 3. . 函数的地址符函数的地址符在我们使用函数时，会引用一些地址作为参数，比如在我们使用函数时，会引用一些地址作为参数，比如A47A47、B46B46等等EXCELEXCEL中有专门的地址引用符中有专门的地址引用符$ $。使用使用是是$ $引用地址时，是绝对引用引用地址时，是绝对引用，不，不使用使用$ $时，是相对引用。时，是相对引用。举例：举例：A47A47：相对引用相对引用，引用，引用A47A47这个单元格。当使用这种类型的地址时这个单元格。当使用这种类型的地址时，我们，我们使用拖拉功能将公式使用拖拉功能将公

6、式引用引用到到其它单元格时，这个其它单元格时，这个相对地址相对地址会发生会发生变化变化$A$47:$A$47:绝对引用绝对引用。无论将公式引用到哪一个单元格。无论将公式引用到哪一个单元格，地址永远，地址永远定格在定格在A47A47这个单元格。这个单元格。$A47$A47和和A$47:A$47:混合引用混合引用。只定格单元格的行或列，相对来说有点复杂。只定格单元格的行或列，相对来说有点复杂，不，不常用。常用。冒号（：）在与单元格一起引用时，代表一片区域，冒号（：）在与单元格一起引用时，代表一片区域，如如 (A1:A47) (A1:A47)代表的是从代表的是从A1A1一直到一直到A47A47的长条

7、形区域，总共的长条形区域，总共4747个单元格，个单元格，如（如（A1:B3)A1:B3)代表以代表以A1A1和和B3B3为对角的一个方形区域，共为对角的一个方形区域，共9 9个单元格。个单元格。 4. 4. 函数的套用函数的套用函数的套用，也就是函数的组合使用，单个函数已经很厉害了，函数的套用，也就是函数的组合使用，单个函数已经很厉害了，组合起来的函数威力会更组合起来的函数威力会更强大强大，最多支持最多支持7 7层套用。层套用。举例：举例：IF(SUM(A1,B2,C3IF(SUM(A1,B2,C3)5,“100”,“1000”)5,“100”,“1000”)在这个函数中，逻辑函数在这个函数

8、中，逻辑函数IFIF套用了套用了SUMSUM函数，它把函数，它把SUMSUM函数返回函数返回的结果作为参数，的结果作为参数，如果如果A1,B2,C3A1,B2,C3之之和小于和小于5 5，返回，返回100100，反之，返，反之，返回回1000.1000. Unit 2 Unit 2 逻辑函数的应用逻辑函数的应用1.1.IFIF函数函数2.2.ANDAND函数函数3.3.OROR函数函数4.4.NOTNOT函数函数1. IF1. IF函数函数IFIF在英文里是在英文里是“假如，如果假如，如果”的意思的意思，顾名思义就是如果满足某个条件（为真），顾名思义就是如果满足某个条件（为真）则返回前面的一个

9、值，不满足条件（为假），返回后面的一个值。则返回前面的一个值，不满足条件（为假），返回后面的一个值。语法：语法：IF(IF(逻辑值，返回值逻辑值，返回值1 1，返回值，返回值2)2)，其中逻辑值类型为真或假，另外，其中逻辑值类型为真或假，另外返返回值是文本格式时，要用双引号回值是文本格式时，要用双引号，如果为数字，可以不用双引号。，如果为数字，可以不用双引号。举例：如何判断考试成绩是否及格？举例：如何判断考试成绩是否及格？IF(IF(A1A1 6060,“,“不及格不及格”,“”,“及格及格”)”)，如果单元格如果单元格A1A1中的成绩小于中的成绩小于6060，则，则返回不及格，反之，返回及格

10、。返回不及格，反之，返回及格。IF(A160 IF(A184,“IF(A184,“优秀优秀”,“,“及格及格”)”)，这是，这是IFIF函数函数套用套用IFIF函数，如果函数，如果A1A1小于小于6060，返回不及格，另外如果大于等于，返回不及格，另外如果大于等于8585，则返回，则返回优秀，否则返回及格。优秀，否则返回及格。2. AND2. AND函数函数ANDAND在英文里是在英文里是“和，与和，与”的意思的意思, ,只有当所有条件都满足时，才返回只有当所有条件都满足时，才返回tureture( (真真) )，否则返回，否则返回false(false(假假),),一般要与其它函数套用，比如

11、一般要与其它函数套用，比如IFIF函数。函数。语法：语法：AND(AND(条件条件1 1，条件，条件2 2，条件条件30)30)返回值：返回值：TURETURE或或FALSEFALSE举例：举例：1.and(A160,B280),1.and(A160,B280),只有只有A160,B280A160,B280这三个条件这三个条件都满足时，才返回都满足时，才返回TRUE,TRUE,否则否则FALSE.FALSE.2.IF2.IF（and(A160,B280),1and(A160,B280),1，2 2），三个条件都满足时，返回），三个条件都满足时，返回1 1，否则返回否则返回2.2.3. OR3.

12、 OR函数函数OROR在英文里是在英文里是“或，或者或，或者”的意思的意思，只要满足任一条件就，只要满足任一条件就返回返回tureture( (真真) )，否则返回，否则返回false(false(假假),),一般要与其它函数套一般要与其它函数套用，比如用，比如IFIF函数。函数。语法：语法：OR(OR(条件条件1 1，条件，条件2 2，条件条件30)30)举例：举例：oror(A160,B280),(A160,B280),当当A160,B280A160,B280这三个条件有一个满足时，返回这三个条件有一个满足时，返回TRUE,TRUE,否则否则FALSE.FALSE.4. not4. not

13、函数函数notnot在英文里是在英文里是“ “ 不，非不，非”的意思的意思，返回相反的值一般要，返回相反的值一般要与其它函数套用，比如与其它函数套用，比如IFIF函数。函数。语法：语法：not(not(条件条件) )举例：举例：not(A160),not(A160),当当A160A160时，返回时，返回FALSE,FALSE,否则否则TRUE.TRUE.Unit 3 Unit 3 文本函数的应用文本函数的应用1.1.比较函数比较函数excatexcat与等号与等号= =2.2.联结函数联结函数concatenateconcatenate与联结号与联结号& &3.3.leftleft、right

14、right、midmid函数函数1.1.比较函数比较函数excatexcat与等号与等号= =ExcatExcat是一个比较函数，等号是一个比较函数，等号= =与之有相同的功能。返回值为逻辑值，与之有相同的功能。返回值为逻辑值，对于返回值为逻辑值的函数，一般与对于返回值为逻辑值的函数，一般与ifif等函数套用。等函数套用。语法：语法：excatexcat(A1,B1)(A1,B1)举例：举例：1.excat(A1,B1),1.excat(A1,B1),当当A1A1与与B1B1相同时，返回相同时，返回TRUE,TRUE,否则否则FALSE.FALSE. 与与ifif函数套用，函数套用，if(if

15、(excatexcat(A1,B1),”(A1,B1),”相同相同”，“不相同不相同”) )。2.2.使用等号比较时，如单元格使用等号比较时，如单元格C1=C1=（A1=B1A1=B1），如果），如果A1A1与与B1B1 相同，相同，C1=TRUE,C1=TRUE,否则否则C1=FALSEC1=FALSE 与与ifif函数套用，函数套用，if(A1=B1,“if(A1=B1,“相同相同”，“不相同不相同”) )。2.2.联结函数联结函数concatenateconcatenate与联结号与联结号& &concatenateconcatenate是一个联结函数，联结号是一个联结函数，联结号& &

16、与之有相同的功与之有相同的功能。能。语法：语法：concatenate(concatenate(字符字符1,1,字符字符2 2，)，单独的文本要，单独的文本要使用双引号使用双引号举例：举例：1.concatenate(A1,B11.concatenate(A1,B1，“trontron”),”),如果如果A1=AA1=A，B1=sB1=s，那么函数就会返回那么函数就会返回AstronAstron2.2. 如果如果C1=A1&B1&“C1=A1&B1&“trontron”，那么，那么C1=AstronC1=Astron3. left3. left、rightright、midmid函数函数举例：

17、举例：假如假如A1=AstronA1=Astron1.1.Left(A1,2)=AsLeft(A1,2)=As2.2.Right(A1,2)=onRight(A1,2)=on3.3.Mid(A1,2,2)=Mid(A1,2,2)=ststUnit 4Unit 4 日期与时间函数日期与时间函数1.1.nownow、todaytoday函数函数2.2.yearyear、monthmonth、dayday、hourhour函数函数3.3.dateifdateif函数函数1. now1. now、todaytoday函数函数nownow函数是提取当前系统日期和时间函数是提取当前系统日期和时间Today

18、Today函数是提取当前系统日期函数是提取当前系统日期语法：语法：Now()Now()参数为空参数为空Today()Today()参数为空参数为空举例：举例：假定系统当前时间为假定系统当前时间为 2016/06/25 2016/06/25 9:489:481.1.如果如果A1=now(),A1=now(),那么那么A1= A1= 2016/06/25 9:482016/06/25 9:482.2.如果如果B1=today(),B1=today(),那么那么B1= B1= 2016/06/252016/06/252. year2. year、monthmonth、dayday、hourhour函

19、数函数yearyear函数作用是提取日期中的年份函数作用是提取日期中的年份 monthmonth函数作用是提取日期中的月份函数作用是提取日期中的月份DayDay函数作用是提取日期中的日数函数作用是提取日期中的日数 HourHour函数作用是提取日期中的小时数函数作用是提取日期中的小时数语法：语法：XXX(A1)XXX(A1)，其中，其中XXXXXX代表上面所有函数，它们语法规则一样，代表上面所有函数，它们语法规则一样，A1A1代表包含日期与代表包含日期与时间的单元格。时间的单元格。举例：举例：假定系统当前时间为假定系统当前时间为 A1=2016/06/25 10:58A1=2016/06/25

20、 10:581.1. B1=year(A1B1=year(A1),),那么那么B1= B1= 201620162.2. C1=month(A1),C1=month(A1),那么那么C1=06C1=063 3. D1=day(A1). D1=day(A1)，那么，那么D1=25D1=254. E1=hour(A1),4. E1=hour(A1),那么那么E1=10E1=103. 3. dateifdateif函数函数dateifdateif函数作用是计算两个日期之间相差的年数、月数、日数，这个函数比较适用于统计出生函数作用是计算两个日期之间相差的年数、月数、日数，这个函数比较适用于统计出生年月年

21、月语法：语法：DateifDateif( (起始日期，结束日期，参数类型起始日期，结束日期，参数类型) )其中参数类型包括：其中参数类型包括：“Y” Y” 时间段中的整年数时间段中的整年数 ，“M” M” 时间段中的整月数时间段中的整月数，“D” D” 时间段时间段中中的天数的天数举例：举例：假定系统当前日期为假定系统当前日期为 2011/11/17 2011/11/17 单元格单元格A1A1中日期为中日期为2000/01/01,2000/01/01,下面例子分别计算起始下面例子分别计算起始日期与当前日期间相差的年数，月数，日数。日期与当前日期间相差的年数，月数，日数。1.1.如果如果B1=D

22、ATEDIF(A1,TODAY(),y),B1=DATEDIF(A1,TODAY(),y),那么那么B1=11B1=112.2.如果如果B1=DATEDIF(A1,TODAY(),“m),B1=DATEDIF(A1,TODAY(),“m),那么那么B1=142B1=1423.3.如果如果B1=DATEDIF(A1,TODAY(),“d),B1=DATEDIF(A1,TODAY(),“d),那么那么B1=4338B1=4338Unit 5 Unit 5 查询与引用函数查询与引用函数1.1.Vlookup,HlookupVlookup,Hlookup函数函数1.1.vlookup,hlookupv

23、lookup,hlookup函数函数VlookupVlookup函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。中指定列处的数值。H Hlookuplookup函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。中指定行处的数值。语法：语法：VlookupVlookup( (要查找的值，查找的区域，要返回列的序号，查找类型要查找的值，查找的区域，要返回列的序号，查找类型)

24、)其中要注意的问题：其中要注意的问题：1.1.要查找的值一定要位于查找区域的首列要查找的值一定要位于查找区域的首列2.2.查找的区域中不要有隐藏的行或列，否则返回的值会出错查找的区域中不要有隐藏的行或列，否则返回的值会出错3.3.查找类型为查找类型为0 0或或falsefalse（假）时，为精准匹配，当省略或为（假）时，为精准匹配，当省略或为truetrue时，为近似匹时，为近似匹 配（没有找到要查找的值时，返回最接近于配（没有找到要查找的值时，返回最接近于“查找值查找值”的值），这里我们常的值），这里我们常 常使用精准匹配，保证查找的准确性。常使用精准匹配，保证查找的准确性。HlookupH

25、lookup函数语法与函数语法与VlookupVlookup函数语法相同。函数语法相同。1.1.vlookup,hlookupvlookup,hlookup函数函数举例举例：表表1 1（sheet1sheet1） 表表2 2（sheet2sheet2）如果我们要在表如果我们要在表2 2中根据姓名导出手工课的成绩，可以使用如下函数中根据姓名导出手工课的成绩，可以使用如下函数 B2=VLOOKUP(A2,B2=VLOOKUP(A2,表表1 1的的$ $A A$2:$2:表表1 1的的$F$5,6,0$F$5,6,0) ), ,填写参数时，可以在公式输入栏里选择填写参数时，可以在公式输入栏里选择Vlook