EXCEL常用函数教程讲课

1、ExcelExcel常用函数应用教程常用函数应用教程 EXCELEXCEL常用函数应用教程常用函数应用教程 EXCELEXCEL函数的基本知识函数的基本知识 逻辑函数的应用逻辑函数的应用 文本函数的应用文本函数的应用 日期与时间函数日期与时间函数 数学与统计函数数学与统计函数 查询与引用函数查询与引用函数 数据库函数的应用数据库函数的应用 Unit 1 EXCELUnit 1 EXCEL函数的基本知识函数的基本知识 什么什么是函数是函数 函数的参数和返回值函数的参数和返回值 函数的地址符函数的地址符 函数的套用函数的套用 Excel Excel中所提的函数其实是一些预定义的公式，它们使用一中所

2、提的函数其实是一些预定义的公式，它们使用一 些称为参数的特定数值按特定的顺序或结构进行计算。些称为参数的特定数值按特定的顺序或结构进行计算。 简单点说，函数是一组功能模块，使用函数能帮助你实现某个简单点说，函数是一组功能模块，使用函数能帮助你实现某个 功能。功能。 举例：举例：SumSum函数可以实现数值的加法运算，函数可以实现数值的加法运算，maxmax函数可以返回一组函数可以返回一组 数的最大值。数的最大值。 1.1.什么什么是函数是函数 2. 2. 函数的参数及返回值函数的参数及返回值 1.1.参数可以是数字、文本、形如参数可以是数字、文本、形如 TRUE TRUE 或或 FALSE F

3、ALSE 的逻辑数值、数组、的逻辑数值、数组、 形如形如 #N/A #N/A 的错误值或单元格引用。函数的参数使用逗号隔开如的错误值或单元格引用。函数的参数使用逗号隔开如 sum(A1,2,B2)sum(A1,2,B2)，其中，其中，A1,2,B2A1,2,B2都是都是sumsum函数的参数，用逗号隔开函数的参数，用逗号隔开 另外要注意的是，函数使用的参数所用的符号，如逗号，引号要全部在另外要注意的是，函数使用的参数所用的符号，如逗号，引号要全部在 英文输入状态下输入英文输入状态下输入( (半角符号半角符号) )。 数字：数字：1 1、2 2、100100、1000010000 文本：文本：a

4、stronastron、412398784737847412398784737847（以文本格式存储的数字）（以文本格式存储的数字） 逻辑值：逻辑值：TRUE(TRUE(真真) )或非零（一般用数字或非零（一般用数字1 1表示）、表示）、FALSE(FALSE(假假) )或或0 0 比如比如13,1313这个不等式的值为假。这个不等式的值为假。 单元格：比如单元格：比如A47A47、B16B16 数组：同一种格式存储的单元格区域数组：同一种格式存储的单元格区域 2.2.大部分函数都有一个返回值，返回你通过函数运算得出的结果大部分函数都有一个返回值，返回你通过函数运算得出的结果, ,返回值可返回

5、值可 以是一个数字，一个逻辑值，一个地址。另外要注意的是，要想要函数返以是一个数字，一个逻辑值，一个地址。另外要注意的是，要想要函数返 回正确的值，使用公式的单元格数值格式（快捷键回正确的值，使用公式的单元格数值格式（快捷键ctrl+1ctrl+1）一定要正确。）一定要正确。 举例：举例： sum(1,2,3),sum(1,2,3),我们的目的是得出我们的目的是得出1+2+31+2+3相加的和，相加的和，sumsum函数返回的值是函数返回的值是6 6 另外值得注意的是，对于函数的返回值如数值，它等同一个数值，而不要另外值得注意的是，对于函数的返回值如数值，它等同一个数值，而不要 把它认为是一个

6、函数。它可以如其它数值一样使用加（把它认为是一个函数。它可以如其它数值一样使用加（+ +）、减（）、减（- -）、）、 乘（乘（* *）、除（）、除（/ /）。）。 如上例中，可以如上例中，可以A1=10-sum(1,2,3)A1=10-sum(1,2,3)，那么，那么A1=4A1=4。在这里，因为。在这里，因为sumsum（）函数（）函数 的返回值是一个数值，可以直接把它当数值用。的返回值是一个数值，可以直接把它当数值用。 2. 2. 函数的参数及返回值函数的参数及返回值 3. 3. 函数的地址符函数的地址符 在我们使用函数时，会引用一些地址作为参数，比如在我们使用函数时，会引用一些地址作为

7、参数，比如A47A47、B46B46等等 EXCELEXCEL中有专门的地址引用符中有专门的地址引用符$ $。使用是。使用是$ $引用地址时，是绝对引用，引用地址时，是绝对引用， 不使用不使用$ $时，是相对引用。时，是相对引用。 举例：举例： A47A47：相对引用，引用：相对引用，引用A47A47这个单元格。当使用这种类型的地址时，这个单元格。当使用这种类型的地址时， 我们使用拖拉功能将公式引用到其它单元格时，这个相对我们使用拖拉功能将公式引用到其它单元格时，这个相对 地址会发生变化地址会发生变化 3. 3. 函数的地址符函数的地址符 $A$47:$A$47:绝对引用。无论将公式引用到哪一

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

9、（A1:B3)A1:B3)代表以代表以A1A1和和B3B3为对角的一个方形区域，共为对角的一个方形区域，共9 9个单元格。个单元格。 注：在引用地址符时，我们可以使用注：在引用地址符时，我们可以使用F4F4鍵在这些地址间进行切换鍵在这些地址间进行切换 4. 4. 函数的套用函数的套用 函数的套用，也就是函数的组合使用，单个函数已经很厉害了，函数的套用，也就是函数的组合使用，单个函数已经很厉害了， 组合起来的函数威力会更强大。组合起来的函数威力会更强大。 举例：举例： IF(SUM(1,2,3)5,“100”,“1000”)IF(SUM(1,2,3)5,“100”,“1000”) 在这个函数中，

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

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

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

13、函数套用，比如一般要与其它函数套用，比如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， 否则返回否

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

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

16、 & LowerLower、upperupper、properproper、trimtrim函数函数 leftleft、rightright、midmid函数函数 1.1.比较函数比较函数excatexcat与等号与等号= = ExcatExcat是一个比较函数，等号是一个比较函数，等号= =与之有相同的功能。返回值为逻辑值，与之有相同的功能。返回值为逻辑值， 对于返回值为逻辑值的函数，一般与对于返回值为逻辑值的函数，一般与ifif等函数套用。等函数套用。 语法：语法：excat(A1,B1)excat(A1,B1) 举例：举例： 1.excat(A1,B1),1.excat(A1,B1),当

17、当A1A1与与B1B1相同时，返回相同时，返回TRUE,TRUE,否则否则FALSE.FALSE. 与与ifif函数套用，函数套用，if(excat(A1,B1),”if(excat(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.联结函数联结函数concate

18、nateconcatenate与联结号与联结号& & concatenateconcatenate是一个联结函数，联结号是一个联结函数，联结号& &与之有相同的功与之有相同的功 能。能。 语法：语法：concatenate(concatenate(字符字符1,1,字符字符2 2，)，单独的文本要，单独的文本要 使用双引号使用双引号 举例：举例： 1.concatenate(A1,B11.concatenate(A1,B1，“tron”),tron”),如果如果A1=AA1=A，B1=sB1=s， 那么函数就会返回那么函数就会返回AstronAstron 2.2. 如果如果C1=A1&B1&“t

19、ronC1=A1&B1&“tron”，那么，那么C1=AstronC1=Astron 3. Lower3. Lower、upperupper、properproper、trimtrim函数函数 LowerLower函数是将文本中的字母转换成小写字母。函数是将文本中的字母转换成小写字母。 UpperUpper函数是将文本中的字母转换成大写字母函数是将文本中的字母转换成大写字母 ProperProper函数是将文本中的字符串首字母转换成大写字母，其余函数是将文本中的字符串首字母转换成大写字母，其余 字符转换成小写字母。字符转换成小写字母。 TrimTrim函数是去除字符串中多余空白字符。函数是去除

20、字符串中多余空白字符。 语法：语法： XXXX( XXXX(字符串），其中字符串），其中XXXXXXXX代表以上所有函数，它们的代表以上所有函数，它们的 语法规则一样，字符串代表内容是字符的地址引用比如语法规则一样，字符串代表内容是字符的地址引用比如 A1,B2,A1,B2,也可以是单独的字符串（要使用双引号如也可以是单独的字符串（要使用双引号如 “astron”astron”） 3. Lower3. Lower、upperupper、properproper、trimtrim函数函数 举例：举例： 假如假如A1=ASTRON,B1=astron,C1=dongguan astron,A1=A

21、STRON,B1=astron,C1=dongguan astron, D1=DongGuan AstronD1=DongGuan Astron lower(A1)=astronlower(A1)=astron Upper(B1)=ASTRONUpper(B1)=ASTRON Proper(C1)=Dongguan AstronProper(C1)=Dongguan Astron Trim(D1)=DongGuan AstronTrim(D1)=DongGuan Astron 4.left4.left、rightright、midmid函数函数 LeftLeft函数是自字符串左边开始提取字符串

22、中的字符函数是自字符串左边开始提取字符串中的字符 RightRight函数是自字符串左边开始提取字符串中的字符函数是自字符串左边开始提取字符串中的字符 MidMid函数是从字符串指定位置开始提取字符串中的字符函数是从字符串指定位置开始提取字符串中的字符 语法：语法： 1.Left(1.Left(字符串，数值字符串，数值) )，其中数值为要提取的字符数，其中数值为要提取的字符数 2.Right(2.Right(字符串，数值字符串，数值) )，其中数值为要提取的字符数，其中数值为要提取的字符数 3.Mid(3.Mid(字符串，数值字符串，数值1 1，数值，数值2)2)，其中数值，其中数值1 1代表

23、要提取代表要提取 的第一个字符的位置，数值的第一个字符的位置，数值2 2代表要提取的字符数。代表要提取的字符数。 4. left4. left、rightright、midmid函数函数 举例：举例： 假如假如A1=AstronA1=Astron Left(A1,2)=AsLeft(A1,2)=As Right(A1,2)=onRight(A1,2)=on Mid(A1,2,2)=stMid(A1,2,2)=st Unit 4Unit 4 日期与时间函数日期与时间函数 nownow、todaytoday函数函数 yearyear、monthmonth、dayday、hourhour函数函数 d

24、ateifdateif函数函数 1. now1. now、todaytoday函数函数 nownow函数是提取当前系统日期和时间函数是提取当前系统日期和时间 TodayToday函数是提取当前系统日期函数是提取当前系统日期 语法：语法： Now()Now()参数为空参数为空 Today()Today()参数为空参数为空 举例：举例： 假定系统当前时间为假定系统当前时间为 2013/03/30 9:482013/03/30 9:48 1.1.如果如果A1=now(),A1=now(),那么那么A1= 2013/03/30 9:48A1= 2013/03/30 9:48 2.2.如果如果B1=to

25、day(),B1=today(),那么那么B1= 2013/03/30B1= 2013/03/30 2. year2. year、monthmonth、dayday、hourhour函数函数 yearyear函数作用是提取日期中的年份函数作用是提取日期中的年份 monthmonth函数作用是提取日期中的月份函数作用是提取日期中的月份 DayDay函数作用是提取日期中的日数函数作用是提取日期中的日数 HourHour函数作用是提取日期中的小时数函数作用是提取日期中的小时数 语法：语法： XXX(A1)XXX(A1)，其中，其中XXXXXX代表上面所有函数，它们语法规则一样，代表上面所有函数，它们

26、语法规则一样，A1A1代表包含日期与代表包含日期与 时间的单元格。时间的单元格。 举例：举例： 假定系统当前时间为假定系统当前时间为 A1=2011/11/17 9:58A1=2011/11/17 9:58 1.1. B1=year(A1),B1=year(A1),那么那么B1= 2011B1= 2011 2.2. C1=month(A1),C1=month(A1),那么那么C1=11C1=11 3. D1=day(A1)3. D1=day(A1)，那么，那么D1=17D1=17 4. E1=hour(A1),4. E1=hour(A1),那么那么E1=9E1=9 3. dateif3. da

27、teif函数函数 dateifdateif函数作用是计算两个日期之间相差的年数、月数、日数，这个函数比较适用于统计出生函数作用是计算两个日期之间相差的年数、月数、日数，这个函数比较适用于统计出生 年月年月 语法：语法： Dateif(Dateif(起始日期，结束日期，参数类型起始日期，结束日期，参数类型) ) 其中参数类型包括：其中参数类型包括：“Y” Y” 时间段中的整年数时间段中的整年数 ，“M” M” 时间段中的整月数时间段中的整月数，“D” D” 时间段时间段中中 的天数的天数 举例：举例： 假定系统当前日期为假定系统当前日期为 2011/11/17 2011/11/17 单元格单元格

28、A1A1中日期为中日期为2000/01/01,2000/01/01,下面例子分别计算起始下面例子分别计算起始 日期与当前日期间相差的年数，月数，日数。日期与当前日期间相差的年数，月数，日数。 1.1.如果如果B1=DATEDIF(A1,TODAY(),y),B1=DATEDIF(A1,TODAY(),y),那么那么B1=11B1=11 2.2.如果如果B1=DATEDIF(A1,TODAY(),“m),B1=DATEDIF(A1,TODAY(),“m),那么那么B1=142B1=142 3.3.如果如果B1=DATEDIF(A1,TODAY(),“d),B1=DATEDIF(A1,TODAY(

29、),“d),那么那么B1=4338B1=4338 Unit Unit 5 5 数学与统计函数数学与统计函数 SumSum、averageaverage、absabs、modmod函数函数 sumifsumif、countifcountif函数函数 maxmax、minmin、smallsmall、largelarge、rankrank函数函数 valuevalue、isoddisodd、iseveniseven函数函数 1.Sum1.Sum、averageaverage、absabs、modmod、powerpower函数函数 SumSum函数作用是求和函数作用是求和 AverageAvera

30、ge函数作用是求平均数函数作用是求平均数 AbsAbs函数作用是求绝对值函数作用是求绝对值 ModMod函数作用是求余函数作用是求余 PowerPower函数作用是求函数作用是求n n次方次方 语法：语法： Sum(Sum(数值数值1 1，数值，数值2)2) 其中数值可以直接是数字，也可以是包含数字的其中数值可以直接是数字，也可以是包含数字的 单元格，也可以是一片单元格区域。单元格，也可以是一片单元格区域。 AverageAverage语法规则同上语法规则同上 AbsAbs（数值）（数值） Mod(Mod(数值数值1 1，数值，数值2)2)，其中数值，其中数值1 1是被除数，数值是被除数，数值

31、2 2是除数是除数 Power(Power(数值数值1 1，数值，数值2)2)，其中数值，其中数值1 1是数，数值是数，数值2 2是多少次方是多少次方 以上函数返回值皆为数字以上函数返回值皆为数字 1.Sum1.Sum、averageaverage、absabs、modmod函数函数 举例：举例： 假设假设 A1=1A1=1，B1=2,C1=-2B1=2,C1=-2 1.sum(A1,B1)=31.sum(A1,B1)=3 2.average(A1,B1)=1.52.average(A1,B1)=1.5 3.abs(A1)=1,abs(C1)=23.abs(A1)=1,abs(C1)=2 4.

32、mod(B1,A1)=0,mod(A1,B1)=14.mod(B1,A1)=0,mod(A1,B1)=1 5.power(B1,2)=4,5.power(B1,2)=4,另外另外 （数字（数字6 6上面的符号）也有这个功能，比如上面的符号）也有这个功能，比如B13=8B13=8 另外要注意的是，想要减或者求负数，直接在单元格前加另外要注意的是，想要减或者求负数，直接在单元格前加- -号就可以了号就可以了 比如上面例子中比如上面例子中sum(A1,-B1)=-1sum(A1,-B1)=-1 2.sumif2.sumif、countifcountif函数函数 SumifSumif函数作用是对区域内

33、符合条件的单元格求和函数作用是对区域内符合条件的单元格求和 CountifCountif函数作用是统计区域内符合条件的单元格的数量函数作用是统计区域内符合条件的单元格的数量 语法：语法： Sumif(Sumif(条件区域，条件，要计算和的区域条件区域，条件，要计算和的区域) ) Countif(Countif(区域，条件区域，条件) ) 举例：举例： 2.sumif2.sumif、countifcountif函数函数 假设有如下表格假设有如下表格 1.1.假如我们要计算假如我们要计算1 1班所有人的手工课的总分，可以使用如下函数班所有人的手工课的总分，可以使用如下函数 SUMIF(A2:A5,

34、“1”,G2:G5), SUMIF(A2:A5,“1”,G2:G5),在函数里面，前一区域在函数里面，前一区域A2:A5A2:A5代表查寻条件代表查寻条件“1”1”的查找区的查找区 域，中间的域，中间的“1”1”表示查找的条件表示查找的条件( (要使用双引号要使用双引号) )，后一区域，后一区域G2:G5G2:G5代表要加总的数字代表要加总的数字 区域区域 2.2.假如我们要统计手工课大于假如我们要统计手工课大于9090分的人数，可以使用分的人数，可以使用countif(G2:G5,countif(G2:G5,”90”90”) ), ,前面代表前面代表 要查找的区域，后面表示查找条件，记住要使

35、用双引号要查找的区域，后面表示查找条件，记住要使用双引号 3. max3. max、minmin、smallsmall、largelarge、rankrank函数函数 MaMax x求一组数据的最大值求一组数据的最大值 MinMin求一组数据的最小值求一组数据的最小值 SmallSmall求一组数据的第求一组数据的第N N小的值小的值 LargeLarge求一组数据的第求一组数据的第N N大的值大的值 RankRank作用是将一组数组排序作用是将一组数组排序 语法：语法： Max(Max(数字数字1 1，数字数字n)n)，参数可以是单独的数字，也可以是包含数字，参数可以是单独的数字，也可以是包

36、含数字 的单元格。的单元格。 MINMIN函数语法同上。函数语法同上。 Small(Small(单元格区域，数字单元格区域，数字N)N)，其中前者为包含数字的单元格区域，后，其中前者为包含数字的单元格区域，后 者为要查找的第者为要查找的第N N小的数字。小的数字。 3. max3. max、minmin、smallsmall、largelarge、rankrank函数函数 large(large(单元格区域，数字单元格区域，数字N)N)，其中前者为包含数字的单元格区域，后者为要查，其中前者为包含数字的单元格区域，后者为要查 找的第找的第N N大的数字。大的数字。 Rank(Rank(需排序的数

37、字，需排序的数组，排序类型需排序的数字，需排序的数组，排序类型) )，其中排序类型为，其中排序类型为0 0时，按降时，按降 序排列，不为序排列，不为0 0时，按升序排列。时，按升序排列。 举例：举例： 假设假设A1,A2,A3,A4,A5A1,A2,A3,A4,A5分别为分别为1212，9 9，7 7，7 7，2121 Max(A1:A5)=21Max(A1:A5)=21 Min(A1:A5)=7Min(A1:A5)=7 Small(A1:A5,3)=9Small(A1:A5,3)=9 Large(A1:A5,5)=7Large(A1:A5,5)=7 3. max3. max、minmin、s

38、mallsmall、largelarge、rankrank函数函数 举例举例 假设假设A1,A2,A3,A4,A5A1,A2,A3,A4,A5分别为分别为1212，9 9，7 7，7 7，2121, , 使用使用RankRank（）函数（）函数 对这个数组进行排序，按降序对这个数组进行排序，按降序( (从大到小从大到小) )排列排列, ,其中其中B B列是排序后的序号列是排序后的序号 B1=RANK(A1,$A$1:$A$5,0),B1=RANK(A1,$A$1:$A$5,0),其中其中A1A1表示要排序的数字，表示要排序的数字，A1:A5A1:A5表示排序表示排序 的数组，的数组，0 0表示

39、按降序排列，另外要注意的是，引用排序的数组时，我表示按降序排列，另外要注意的是，引用排序的数组时，我 们用了绝对地址引用们用了绝对地址引用$A$1:$A$5($A$1:$A$5(这个可以用这个可以用F4F4鍵在地圵符间进行切换鍵在地圵符间进行切换),), 这样我们在引用公式到这样我们在引用公式到B2,B3B2,B3单元格时，这个地址区域不会变。单元格时，这个地址区域不会变。 4.value4.value函数函数 ValueValue函数作用是将文本类型的数字转换成数字类型，常用于出生日期，函数作用是将文本类型的数字转换成数字类型，常用于出生日期， 银行卡等数据类型的转换。银行卡等数据类型的转换

40、。 语法：语法： Value(Value(文本文本) )，其中文本必须为数字，只是存储类型为文本格式，其中文本必须为数字，只是存储类型为文本格式 举例：举例： 假设假设A1=20111121A1=20111121，A2=5A2=5 如果如果B1=2011-VALUE(MID(A1,3,2)B1=2011-VALUE(MID(A1,3,2)，那么，那么B1=2000.B1=2000.我们知道，我们知道，MIDMID函数函数 的作用是取文本中的字符（第三章第的作用是取文本中的字符（第三章第4 4小节），在这里，我们从小节），在这里，我们从A1A1单元单元 格的第格的第3 3个字符开始取，取个字符开

41、始取，取2 2个字符，得到字符个字符，得到字符1111，然后将用，然后将用valuevalue函数函数 将其转换成数字类型将其转换成数字类型1111。最后与。最后与20112011相减得相减得20002000 Unit 6 Unit 6 查询与引用函数查询与引用函数 Vlookup,hlookupVlookup,hlookup函数函数 Match,indexMatch,index函数函数 1.1.vlookup,hlookupvlookup,hlookup函数函数 VlookupVlookup函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行函数用于在表格或数值数组的首列

42、查找指定的数值，并由此返回表格或数组当前行 中指定列处的数值。中指定列处的数值。 H Hlookuplookup函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列 中指定行处的数值。中指定行处的数值。 语法：语法： Vlookup(Vlookup(要查找的值，查找的区域，要返回列的序号，查找类型要查找的值，查找的区域，要返回列的序号，查找类型) ) 其中要注意的问题：其中要注意的问题： 1.1.要查找的值一定要位于查找区域的首列要查找的值一定要位于查找区域的首列 2.2.查找的区域中不要有隐藏的行或列

43、，否则返回的值会出错查找的区域中不要有隐藏的行或列，否则返回的值会出错 3.3.查找类型为查找类型为0 0或或falsefalse（假）时，为精准匹配，当省略或为（假）时，为精准匹配，当省略或为truetrue时，为近似匹时，为近似匹 配（没有找到要查找的值时，返回最接近于配（没有找到要查找的值时，返回最接近于“查找值查找值”的值），这里我们常的值），这里我们常 常使用精准匹配，保证查找的准确性。常使用精准匹配，保证查找的准确性。 HlookupHlookup函数语法与函数语法与VlookupVlookup函数语法相同。函数语法相同。 1.1.vlookup,hlookupvlookup,hl

44、ookup函数函数 举例：举例： 表表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) ), ,填写参数时，可以在公式输入栏里选择填写参数时，可以在公式输入栏里选择VlookupVlookup函数后直接用鼠函数后直接用鼠 标点击输入标点击输入 1.1.第一个参数代表我们要查找的值（根据姓名查找手工成绩）要

45、注意的是，查找值有相同的（有同名第一个参数代表我们要查找的值（根据姓名查找手工成绩）要注意的是，查找值有相同的（有同名 的），返回第一个值所对应的返回值的），返回第一个值所对应的返回值 2.2.第二个参数代表包含查找值的查找区域，这个查找区域可以在其他工作表上，也可以在同一张工作表上第二个参数代表包含查找值的查找区域，这个查找区域可以在其他工作表上，也可以在同一张工作表上 , ,在本例中我们的查找区域位于工作表一。查找区域使用绝对引用在本例中我们的查找区域位于工作表一。查找区域使用绝对引用$,$,固定的查找区域我们要使用绝对引固定的查找区域我们要使用绝对引 用，不然将公式引用到其它单元格时，查

46、找区域也会变动，。另外在上面例子中我们的查找值位于查用，不然将公式引用到其它单元格时，查找区域也会变动，。另外在上面例子中我们的查找值位于查 找区域的第一列（必须的）找区域的第一列（必须的） 1.1.vlookup,hlookupvlookup,hlookup函数函数 举例：举例： B2=VLOOKUP(A2,B2=VLOOKUP(A2,表表1 1的的$ $A A$2:$2:表表1 1的的$F$5,6,0$F$5,6,0) ), , 3.3.第三个参数第三个参数6 6代表我们要返回的值的列号（手工成绩位于第代表我们要返回的值的列号（手工成绩位于第6 6列）列） 4.4.第四个参数第四个参数0

47、0代表包含查找类型为精准查找，一般我们要使用精准查代表包含查找类型为精准查找，一般我们要使用精准查 找，保证查找值的准确性找，保证查找值的准确性 HlookupHlookup函数使用与函数使用与VlookupVlookup函数相似，只是将行列互换而已。具体区别大函数相似，只是将行列互换而已。具体区别大 家可以自己体验一下。家可以自己体验一下。 2 2.Match,index.Match,index函数函数 MatchMatch函数查找某值在数组中的位置。函数查找某值在数组中的位置。 IndexIndex函数作用是返回数组中某行某列的值函数作用是返回数组中某行某列的值 语法：语法： 1.Matc

48、h(1.Match(要查找的值，数组，查找类型要查找的值，数组，查找类型) )，其中要查找的值可以是单独输入的值，其中要查找的值可以是单独输入的值 （要使用双引号），也可以是包含查找值的单元格。数组包含查找值的一组单元（要使用双引号），也可以是包含查找值的单元格。数组包含查找值的一组单元 格。查找类型包含格。查找类型包含3 3种，为种，为-1-1时时, ,参数中的数组须降序排列；为参数中的数组须降序排列；为1 1时，数组须升序时，数组须升序 排列；为排列；为0 0时，可以是无序排列。时，可以是无序排列。 2.index(2.index(数组，行，列数组，行，列) ), ,其中的行列是数组的相对

49、的行列值，不是工作表的行列其中的行列是数组的相对的行列值，不是工作表的行列 2 2.Match,index.Match,index函数函数 举例：举例： 在上面的数组中在上面的数组中 1.1.A8A8=MATCH(4,A2:A6,0)=MATCH(4,A2:A6,0), ,其中，第一个参数其中，第一个参数4 4代表我们要在数组（代表我们要在数组（A2:A4A2:A4） 中查找中查找4 4这个值这个值, ,最后一个参数最后一个参数0 0代表我们查找的数组是无序排列。代表我们查找的数组是无序排列。 2.B8=INDEX(A2:B6,3,2)2.B8=INDEX(A2:B6,3,2)，其中，第，其中

50、，第1 1个参数表示数组的区域，第个参数表示数组的区域，第2 2，3 3个参个参 数表示这个查找区域的行和列。数表示这个查找区域的行和列。 Unit 7 Unit 7 数据库函数的应用数据库函数的应用 Dget,DcountDget,Dcount函数函数 Dmax,Dmin,Dsum,DaverageDmax,Dmin,Dsum,Daverage函数函数 1. Dget,Dcount1. Dget,Dcount函数函数 DD函数是数据库类型函数，与其它类型函数不同的是前面多了一个字函数是数据库类型函数，与其它类型函数不同的是前面多了一个字 母母D D（数据库（数据库databasedataba

51、se的首字母），数据库函数的使用功能更强大，可的首字母），数据库函数的使用功能更强大，可 以在查找引用中使用更加复杂的查找关系。以在查找引用中使用更加复杂的查找关系。 1.Dget1.Dget函数提取满足条件的单个记录，有多个值符合条件时返回错误函数提取满足条件的单个记录，有多个值符合条件时返回错误 2.Dcount2.Dcount函数返回满足条件的单元格的个数函数返回满足条件的单元格的个数 语法：语法： DD函数的语法格式都一样函数的语法格式都一样 DD（数据库区域，函数要使用的数据列，查找条件），其中第一个参数表示构成数据库（数据库区域，函数要使用的数据列，查找条件），其中第一个参数表示构

52、成数据库 的单元格区域（如下例中的的单元格区域（如下例中的A3:F10A3:F10），第二个参数表示函数要使用的数据列，可以是用），第二个参数表示函数要使用的数据列，可以是用 双引号引用的文本，也可以是列号（如下例中的双引号引用的文本，也可以是列号（如下例中的“语文语文”，1 1），第三个参数代表包含给），第三个参数代表包含给 定条件的单元格区域，它至少包含一个列标志和列标志下方用于设定条件的单元格（如定条件的单元格区域，它至少包含一个列标志和列标志下方用于设定条件的单元格（如 下例中的下例中的B1:B2B1:B2，B1:C2B1:C2）。）。 1. Dget,Dcount1. Dget,Dcount函数函数 举例：举例： 1.Dcount()1.Dcount()，在上表中，如果我们想要统计语文，在上表中，如果我们想要统计语文8989的人数数量，可以在的人数数量，可以在B1B1中设置列标志中设置列标志 （语文），在（语文），在B2B2中设置给定条件（中设置给定条件（8989）, ,在这里我们在在这里我们在A12A12单元格求结果单元格求结果 A1