ExcelExcel常用函数应用教程常用函数应用教程EXCELEXCEL常用函数应用教程常用函数应用教程1.1.EXCELEXCEL函数的基本知识函数的基本知识2.2.逻辑函数的应用逻辑函数的应用3.3.文本函数的应用文本函数的应用4.4.日期与时间函数日期与时间函数5.5.数学与统计函数数学与统计函数6.6.查询与引用函数查询与引用函数7.7.数据库函数的应用数据库函数的应用Unit 1 EXCELUnit 1 EXCEL函数的基本知识函数的基本知识1.什么什么是函数是函数2.2.函数的参数和返回值函数的参数和返回值3.3.函数的地址符函数的地址符4.4.函数的套用函数的套用 Excel Excel中所提的函数其实是一些预定义的公式，它们使用一中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。些称为参数的特定数值按特定的顺序或结构进行计算。简单点说，函数是一组功能模块，使用函数能帮助你实现某个简单点说，函数是一组功能模块，使用函数能帮助你实现某个功能。功能。举例：举例：SumSum函数可以实现数值的加法运算，函数可以实现数值的加法运算，maxmax函数可以返回一组数函数可以返回一组数的最大值。的最大值。 1.1.什么什么是函数是函数2. 2. 函数的参数及返回值函数的参数及返回值1.1.1.1.参数可以是数字、文本、形如参数可以是数字、文本、形如 TRUE TRUE 或或 FALSE FALSE 的逻辑数值、数组、的逻辑数值、数组、2.2.形如形如 #N/A #N/A 的错误值或单元格引用。函数的参数使用逗号隔开如的错误值或单元格引用。函数的参数使用逗号隔开如3.3.sum(A1,2,B2)sum(A1,2,B2)，其中，其中，A1,2,B2A1,2,B2都是都是sumsum函数的参数，用逗号隔开函数的参数，用逗号隔开4.4.另外要注意的是，函数使用的参数所用的符号，如逗号，引号要全部在另外要注意的是，函数使用的参数所用的符号，如逗号，引号要全部在5.5.英文输入状态下输入英文输入状态下输入( (半角符号半角符号) )。6.6.数字：数字：1 1、2 2、100100、10000100007.7.文本：文本：astronastron、412398784737847412398784737847（以文本格式存储的数字）（以文本格式存储的数字）8.8.逻辑值：逻辑值：TRUE(TRUE(真真) )或非零（一般用数字或非零（一般用数字1 1表示）、表示）、FALSE(FALSE(假假) )或或0 09.9.比如比如13,1313这个不等式的值为假。这个不等式的值为假。10.10.单元格：比如单元格：比如A47A47、B16B1611.11.数组：同一种格式存储的单元格区域数组：同一种格式存储的单元格区域 2.2.大部分函数都有一个返回值，返回你通过函数运算得出的结果大部分函数都有一个返回值，返回你通过函数运算得出的结果, ,返回值可返回值可以是一个数字，一个逻辑值，一个地址。另外要注意的是，要想要函数返以是一个数字，一个逻辑值，一个地址。另外要注意的是，要想要函数返回正确的值，使用公式的单元格数值格式（快捷键回正确的值，使用公式的单元格数值格式（快捷键ctrl+1ctrl+1）一定要正确。）一定要正确。举例：举例：sum(1,2,3),sum(1,2,3),我们的目的是得出我们的目的是得出1+2+31+2+3相加的和，相加的和，sumsum函数返回的值是函数返回的值是6 6另外值得注意的是，对于函数的返回值如数值，它等同一个数值，而不要另外值得注意的是，对于函数的返回值如数值，它等同一个数值，而不要把它认为是一个函数。它可以如其它数值一样使用加（把它认为是一个函数。它可以如其它数值一样使用加（+ +）、减（）、减（- -）、）、乘（乘（* *）、除（）、除（/ /）。）。如上例中，可以如上例中，可以A1=10-sum(1,2,3)A1=10-sum(1,2,3)，那么，那么A1=4A1=4。在这里，因为。在这里，因为sumsum（）函数（）函数的返回值是一个数值，可以直接把它当数值用。的返回值是一个数值，可以直接把它当数值用。 2. 2. 函数的参数及返回值函数的参数及返回值3. 3. 函数的地址符函数的地址符在我们使用函数时，会引用一些地址作为参数，比如在我们使用函数时，会引用一些地址作为参数，比如A47A47、B46B46等等EXCELEXCEL中有专门的地址引用符中有专门的地址引用符$ $。使用是。使用是$ $引用地址时，是绝对引用，引用地址时，是绝对引用，不使用不使用$ $时，是相对引用。时，是相对引用。举例：举例：A47A47：相对引用，引用：相对引用，引用A47A47这个单元格。当使用这种类型的地址时，这个单元格。当使用这种类型的地址时，我们使用拖拉功能将公式引用到其它单元格时，这个相对我们使用拖拉功能将公式引用到其它单元格时，这个相对地址会发生变化地址会发生变化 3. 3. 函数的地址符函数的地址符$A$47:$A$47:绝对引用。无论将公式引用到哪一个单元格，这个地址不会绝对引用。无论将公式引用到哪一个单元格，这个地址不会改变，永远定格在改变，永远定格在A47A47这个单元格。这个单元格。$A47$A47和和A$47:A$47:混合引用。只定格单元格的行或列，相对来说有点复杂，混合引用。只定格单元格的行或列，相对来说有点复杂，不常用。不常用。另外在引用地址时，冒号（：）在与单元格一起引用时，代表一片区域，另外在引用地址时，冒号（：）在与单元格一起引用时，代表一片区域，如如 (A1:A47) (A1:A47)代表的是从代表的是从A1A1一直到一直到A47A47的长条形区域，总共的长条形区域，总共4747个单元格，个单元格，如（如（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”)在这个函数中，逻辑函数在这个函数中，逻辑函数IFIF套用了套用了SUMSUM函数，它把函数，它把SUMSUM函数返回函数返回的结果作为参数，如果的结果作为参数，如果1 1，2 2，3 3之和小于之和小于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在英文里是在英文里是“假如，如果假如，如果”的意思，顾名思义就是如果满足某个条件（为真）的意思，顾名思义就是如果满足某个条件（为真）则返回前面的一个值，不满足条件（为假），返回后面的一个值。则返回前面的一个值，不满足条件（为假），返回后面的一个值。语法：语法：IF(IF(逻辑值，返回值逻辑值，返回值1 1，返回值，返回值2)2)，其中逻辑值类型为真或假，另外返，其中逻辑值类型为真或假，另外返回值是文本格式时，要用双引号，如果为数字，可以不用双引号。回值是文本格式时，要用双引号，如果为数字，可以不用双引号。举例：如何判断考试成绩是否及格？举例：如何判断考试成绩是否及格？IF(IF(A1A1 6060,“,“不及格不及格”,“”,“及格及格”)”)，如果单元格如果单元格A1A1中的成绩小于中的成绩小于6060，则，则返回不及格，反之，返回及格。返回不及格，反之，返回及格。IF(A160 IF(A184,“IF(A184,“优秀优秀”,“,“及格及格”)”)，这是，这是IFIF函数函数套用套用IFIF函数，如果函数，如果A1A1小于小于6060，返回不及格，另外如果大于等于，返回不及格，另外如果大于等于8585，则返回，则返回优秀，否则返回及格。优秀，否则返回及格。2. AND2. AND函数函数ANDAND在英文里是在英文里是“和，与和，与”的意思的意思, ,只有当所有条件都满足时，才返回只有当所有条件都满足时，才返回ture(ture(真真) )，否则返回，否则返回false(false(假假),),一般要与其它函数套用，比如一般要与其它函数套用，比如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. 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,否则否则FALSE.FALSE.4. not4. not函数函数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.LowerLower、upperupper、properproper、trimtrim函数函数4.4.leftleft、rightright、midmid函数函数1.1.比较函数比较函数excatexcat与等号与等号= =ExcatExcat是一个比较函数，等号是一个比较函数，等号= =与之有相同的功能。返回值为逻辑值，与之有相同的功能。返回值为逻辑值，对于返回值为逻辑值的函数，一般与对于返回值为逻辑值的函数，一般与ifif等函数套用。等函数套用。语法：语法：excat(A1,B1)excat(A1,B1)举例：举例：1.excat(A1,B1),1.excat(A1,B1),当当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.联结函数联结函数concatenateconcatenate与联结号与联结号& &concatenateconcatenate是一个联结函数，联结号是一个联结函数，联结号& &与之有相同的功与之有相同的功能。能。语法：语法：concatenate(concatenate(字符字符1,1,字符字符2 2，)，单独的文本要，单独的文本要使用双引号使用双引号举例：举例：1.concatenate(A1,B11.concatenate(A1,B1，“tron”),tron”),如果如果A1=AA1=A，B1=sB1=s，那么函数就会返回那么函数就会返回AstronAstron2.2. 如果如果C1=A1&B1&“tronC1=A1&B1&“tron”，那么，那么C1=AstronC1=Astron3. Lower3. Lower、upperupper、properproper、trimtrim函数函数LowerLower函数是将文本中的字母转换成小写字母。函数是将文本中的字母转换成小写字母。UpperUpper函数是将文本中的字母转换成大写字母函数是将文本中的字母转换成大写字母ProperProper函数是将文本中的字符串首字母转换成大写字母，其余函数是将文本中的字符串首字母转换成大写字母，其余字符转换成小写字母。字符转换成小写字母。TrimTrim函数是去除字符串中多余空白字符。函数是去除字符串中多余空白字符。语法：语法： XXXX( XXXX(字符串），其中字符串），其中XXXXXXXX代表以上所有函数，它们的代表以上所有函数，它们的语法规则一样，字符串代表内容是字符的地址引用比如语法规则一样，字符串代表内容是字符的地址引用比如A1,B2,A1,B2,也可以是单独的字符串（要使用双引号如也可以是单独的字符串（要使用双引号如“astron”astron”）3. Lower3. Lower、upperupper、properproper、trimtrim函数函数举例：举例：假如假如A1=ASTRON,B1=astron,C1=dongguan astron,A1=ASTRON,B1=astron,C1=dongguan astron,D1=DongGuan AstronD1=DongGuan Astron1.1.lower(A1)=astronlower(A1)=astron2.2.Upper(B1)=ASTRONUpper(B1)=ASTRON3.3.Proper(C1)=Dongguan AstronProper(C1)=Dongguan Astron4.4.Trim(D1)=DongGuan AstronTrim(D1)=DongGuan Astron4.left4.left、rightright、midmid函数函数LeftLeft函数是自字符串左边开始提取字符串中的字符函数是自字符串左边开始提取字符串中的字符RightRight函数是自字符串左边开始提取字符串中的字符函数是自字符串左边开始提取字符串中的字符MidMid函数是从字符串指定位置开始提取字符串中的字符函数是从字符串指定位置开始提取字符串中的字符语法：语法：1.Left(1.Left(字符串，数值字符串，数值) )，其中数值为要提取的字符数，其中数值为要提取的字符数2.Right(2.Right(字符串，数值字符串，数值) )，其中数值为要提取的字符数，其中数值为要提取的字符数3.Mid(3.Mid(字符串，数值字符串，数值1 1，数值，数值2)2)，其中数值，其中数值1 1代表要提取代表要提取 的第一个字符的位置，数值的第一个字符的位置，数值2 2代表要提取的字符数。代表要提取的字符数。4. left4. left、rightright、midmid函数函数举例：举例：假如假如A1=AstronA1=Astron1.1.Left(A1,2)=AsLeft(A1,2)=As2.2.Right(A1,2)=onRight(A1,2)=on3.3.Mid(A1,2,2)=stMid(A1,2,2)=stUnit 4Unit 4 日期与时间函数日期与时间函数1.1.nownow、todaytoday函数函数2.2.yearyear、monthmonth、dayday、hourhour函数函数3.3.dateifdateif函数函数1. now1. now、todaytoday函数函数nownow函数是提取当前系统日期和时间函数是提取当前系统日期和时间TodayToday函数是提取当前系统日期函数是提取当前系统日期语法：语法：Now()Now()参数为空参数为空Today()Today()参数为空参数为空举例：举例：假定系统当前时间为假定系统当前时间为 2013/03/30 9:482013/03/30 9:481.1.如果如果A1=now(),A1=now(),那么那么A1= 2013/03/30 9:48A1= 2013/03/30 9:482.2.如果如果B1=today(),B1=today(),那么那么B1= 2013/03/30B1= 2013/03/302. year2. year、monthmonth、dayday、hourhour函数函数yearyear函数作用是提取日期中的年份函数作用是提取日期中的年份 monthmonth函数作用是提取日期中的月份函数作用是提取日期中的月份DayDay函数作用是提取日期中的日数函数作用是提取日期中的日数 HourHour函数作用是提取日期中的小时数函数作用是提取日期中的小时数语法：语法：XXX(A1)XXX(A1)，其中，其中XXXXXX代表上面所有函数，它们语法规则一样，代表上面所有函数，它们语法规则一样，A1A1代表包含日期与代表包含日期与时间的单元格。时间的单元格。举例：举例：假定系统当前时间为假定系统当前时间为 A1=2011/11/17 9:58A1=2011/11/17 9:581.1. B1=year(A1),B1=year(A1),那么那么B1= 2011B1= 20112.2. C1=month(A1),C1=month(A1),那么那么C1=11C1=113. D1=day(A1)3. D1=day(A1)，那么，那么D1=17D1=174. E1=hour(A1),4. E1=hour(A1),那么那么E1=9E1=93. dateif3. dateif函数函数dateifdateif函数作用是计算两个日期之间相差的年数、月数、日数，这个函数比较适用于统计出生函数作用是计算两个日期之间相差的年数、月数、日数，这个函数比较适用于统计出生年月年月语法：语法：Dateif(Dateif(起始日期，结束日期，参数类型起始日期，结束日期，参数类型) )其中参数类型包括：其中参数类型包括：“Y” Y” 时间段中的整年数时间段中的整年数 ，“M” M” 时间段中的整月数时间段中的整月数，“D” D” 时间段时间段中中的天数的天数举例：举例：假定系统当前日期为假定系统当前日期为 2011/11/17 2011/11/17 单元格单元格A1A1中日期为中日期为2000/01/01,2000/01/01,下面例子分别计算起始下面例子分别计算起始日期与当前日期间相差的年数，月数，日数。日期与当前日期间相差的年数，月数，日数。1.1.如果如果B1=DATEDIF(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 Unit 5 5 数学与统计函数数学与统计函数1.1.SumSum、averageaverage、absabs、modmod函数函数2.2.sumifsumif、countifcountif函数函数3.3.maxmax、minmin、smallsmall、largelarge、rankrank函数函数4.4.valuevalue、isoddisodd、iseveniseven函数函数1.Sum1.Sum、averageaverage、absabs、modmod、powerpower函数函数SumSum函数作用是求和函数作用是求和 AverageAverage函数作用是求平均数函数作用是求平均数AbsAbs函数作用是求绝对值函数作用是求绝对值 ModMod函数作用是求余函数作用是求余 PowerPower函数作用是求函数作用是求n n次方次方语法：语法：Sum(Sum(数值数值1 1，数值，数值2)2) 其中数值可以直接是数字，也可以是包含数字的单其中数值可以直接是数字，也可以是包含数字的单元格，也可以是一片单元格区域。元格，也可以是一片单元格区域。AverageAverage语法规则同上语法规则同上AbsAbs（数值）（数值）Mod(Mod(数值数值1 1，数值，数值2)2)，其中数值，其中数值1 1是被除数，数值是被除数，数值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=-21.sum(A1,B1)=31.sum(A1,B1)=32.average(A1,B1)=1.52.average(A1,B1)=1.53.abs(A1)=1,abs(C1)=23.abs(A1)=1,abs(C1)=24.mod(B1,A1)=0,mod(A1,B1)=14.mod(B1,A1)=0,mod(A1,B1)=15.power(B1,2)=4,5.power(B1,2)=4,另外另外 （数字（数字6 6上面的符号）也有这个功能，比如上面的符号）也有这个功能，比如B13=8B13=8另外要注意的是，想要减或者求负数，直接在单元格前加另外要注意的是，想要减或者求负数，直接在单元格前加- -号就可以了号就可以了比如上面例子中比如上面例子中sum(A1,-B1)=-1sum(A1,-B1)=-12.sumif2.sumif、countifcountif函数函数SumifSumif函数作用是对区域内符合条件的单元格求和函数作用是对区域内符合条件的单元格求和CountifCountif函数作用是统计区域内符合条件的单元格的数量函数作用是统计区域内符合条件的单元格的数量语法：语法：Sumif(Sumif(条件区域，条件，要计算和的区域条件区域，条件，要计算和的区域) )Countif(Countif(区域，条件区域，条件) )举例：举例：2.sumif2.sumif、countifcountif函数函数假设有如下表格假设有如下表格1.1.假如我们要计算假如我们要计算1 1班所有人的手工课的总分，可以使用如下函数班所有人的手工课的总分，可以使用如下函数 SUMIF(A2:A5,“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”) ), ,前面代表前面代表 要查找的区域，后面表示查找条件，记住要使用双引号要查找的区域，后面表示查找条件，记住要使用双引号3. max3. max、minmin、smallsmall、largelarge、rankrank函数函数MaMax x求一组数据的最大值求一组数据的最大值 MinMin求一组数据的最小值求一组数据的最小值SmallSmall求一组数据的第求一组数据的第N N小的值小的值 LargeLarge求一组数据的第求一组数据的第N N大的值大的值RankRank作用是将一组数组排序作用是将一组数组排序语法：语法：Max(Max(数字数字1 1，数字数字n)n)，参数可以是单独的数字，也可以是包含数字，参数可以是单独的数字，也可以是包含数字的单元格。的单元格。MINMIN函数语法同上。函数语法同上。Small(Small(单元格区域，数字单元格区域，数字N)N)，其中前者为包含数字的单元格区域，后者，其中前者为包含数字的单元格区域，后者为要查找的第为要查找的第N N小的数字。小的数字。3. max3. max、minmin、smallsmall、largelarge、rankrank函数函数large(large(单元格区域，数字单元格区域，数字N)N)，其中前者为包含数字的单元格区域，后者为要查，其中前者为包含数字的单元格区域，后者为要查找的第找的第N N大的数字。大的数字。Rank(Rank(需排序的数字，需排序的数组，排序类型需排序的数字，需排序的数组，排序类型) )，其中排序类型为，其中排序类型为0 0时，按降序时，按降序排列，不为排列，不为0 0时，按升序排列。时，按升序排列。举例：举例：假设假设A1,A2,A3,A4,A5A1,A2,A3,A4,A5分别为分别为1212，9 9，7 7，7 7，2121Max(A1:A5)=21Max(A1:A5)=21 Min(A1:A5)=7Min(A1:A5)=7Small(A1:A5,3)=9Small(A1:A5,3)=9 Large(A1:A5,5)=7Large(A1:A5,5)=73. max3. max、minmin、smallsmall、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表示按降序排列，另外要注意的是，引用排序的数组时，我表示按降序排列，另外要注意的是，引用排序的数组时，我们用了绝对地址引用们用了绝对地址引用$A$1:$A$5($A$1:$A$5(这个可以用这个可以用F4F4鍵在地圵符间进行切换鍵在地圵符间进行切换),),这样我们在引用公式到这样我们在引用公式到B2,B3B2,B3单元格时，这个地址区域不会变。单元格时，这个地址区域不会变。4.value4.value函数函数ValueValue函数作用是将文本类型的数字转换成数字类型，常用于出生日期，函数作用是将文本类型的数字转换成数字类型，常用于出生日期，银行卡等数据类型的转换。银行卡等数据类型的转换。语法：语法：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个字符开始取，取个字符开始取，取2 2个字符，得到字符个字符，得到字符1111，然后将用，然后将用valuevalue函数函数将其转换成数字类型将其转换成数字类型1111。最后与。最后与20112011相减得相减得20002000Unit 6 Unit 6 查询与引用函数查询与引用函数1.1.Vlookup,hlookupVlookup,hlookup函数函数2.2.Match,indexMatch,index函数函数1.1.vlookup,hlookupvlookup,hlookup函数函数VlookupVlookup函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。中指定列处的数值。H Hlookuplookup函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。中指定行处的数值。语法：语法：Vlookup(Vlookup(要查找的值，查找的区域，要返回列的序号，查找类型要查找的值，查找的区域，要返回列的序号，查找类型) )其中要注意的问题：其中要注意的问题：1.1.要查找的值一定要位于查找区域的首列要查找的值一定要位于查找区域的首列2.2.查找的区域中不要有隐藏的行或列，否则返回的值会出错查找的区域中不要有隐藏的行或列，否则返回的值会出错3.3.查找类型为查找类型为0 0或或falsefalse（假）时，为精准匹配，当省略或为（假）时，为精准匹配，当省略或为truetrue时，为近似匹时，为近似匹 配（没有找到要查找的值时，返回最接近于配（没有找到要查找的值时，返回最接近于“查找值查找值”的值），这里我们常的值），这里我们常 常使用精准匹配，保证查找的准确性。常使用精准匹配，保证查找的准确性。HlookupHlookup函数语法与函数语法与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) ), ,填写参数时，可以在公式输入栏里选择填写参数时，可以在公式输入栏里选择VlookupVlookup函数后直接用鼠函数后直接用鼠标点击输入标点击输入1.1.第一个参数代表我们要查找的值（根据姓名查找手工成绩）要注意的是，查找值有相同的（有同名第一个参数代表我们要查找的值（根据姓名查找手工成绩）要注意的是，查找值有相同的（有同名 的），返回第一个值所对应的返回值的），返回第一个值所对应的返回值2.2.第二个参数代表包含查找值的查找区域，这个查找区域可以在其他工作表上，也可以在同一张工作表上第二个参数代表包含查找值的查找区域，这个查找区域可以在其他工作表上，也可以在同一张工作表上 , ,在本例中我们的查找区域位于工作表一。查找区域使用绝对引用在本例中我们的查找区域位于工作表一。查找区域使用绝对引用$,$,固定的查找区域我们要使用绝对引固定的查找区域我们要使用绝对引 用，不然将公式引用到其它单元格时，查找区域也会变动，。另外在上面例子中我们的查找值位于查用，不然将公式引用到其它单元格时，查找区域也会变动，。另外在上面例子中我们的查找值位于查 找区域的第一列（必须的）找区域的第一列（必须的）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 0代表包含查找类型为精准查找，一般我们要使用精准查代表包含查找类型为精准查找，一般我们要使用精准查 找，保证查找值的准确性找，保证查找值的准确性HlookupHlookup函数使用与函数使用与VlookupVlookup函数相似，只是将行列互换而已。具体区别大函数相似，只是将行列互换而已。具体区别大家可以自己体验一下。家可以自己体验一下。2 2.Match,index.Match,index函数函数MatchMatch函数查找某值在数组中的位置。函数查找某值在数组中的位置。IndexIndex函数作用是返回数组中某行某列的值函数作用是返回数组中某行某列的值语法：语法：1.Match(1.Match(要查找的值，数组，查找类型要查找的值，数组，查找类型) )，其中要查找的值可以是单独输入的值，其中要查找的值可以是单独输入的值 （要使用双引号），也可以是包含查找值的单元格。数组包含查找值的一组单元（要使用双引号），也可以是包含查找值的单元格。数组包含查找值的一组单元 格。查找类型包含格。查找类型包含3 3种，为种，为-1-1时时, ,参数中的数组须降序排列；为参数中的数组须降序排列；为1 1时，数组须升序时，数组须升序 排列；为排列；为0 0时，可以是无序排列。时，可以是无序排列。2.index(2.index(数组，行，列数组，行，列) ), ,其中的行列是数组的相对的行列值，不是工作表的行列其中的行列是数组的相对的行列值，不是工作表的行列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)，其中，第，其中，第1 1个参数表示数组的区域，第个参数表示数组的区域，第2 2，3 3个参个参 数表示这个查找区域的行和列。数表示这个查找区域的行和列。Unit 7 Unit 7 数据库函数的应用数据库函数的应用1.1.Dget,DcountDget,Dcount函数函数2.2.Dmax,Dmin,Dsum,DaverageDmax,Dmin,Dsum,Daverage函数函数1. Dget,Dcount1. Dget,Dcount函数函数DD函数是数据库类型函数，与其它类型函数不同的是前面多了一个字函数是数据库类型函数，与其它类型函数不同的是前面多了一个字母母D D（数据库（数据库databasedatabase的首字母），数据库函数的使用功能更强大，可的首字母），数据库函数的使用功能更强大，可以在查找引用中使用更加复杂的查找关系。以在查找引用中使用更加复杂的查找关系。1.Dget1.Dget函数提取满足条件的单个记录，有多个值符合条件时返回错误函数提取满足条件的单个记录，有多个值符合条件时返回错误2.Dcount2.Dcount函数返回满足条件的单元格的个数函数返回满足条件的单元格的个数语法：语法：DD函数的语法格式都一样函数的语法格式都一样DD（数据库区域，函数要使用的数据列，查找条件），其中第一个参数表示构成数据库（数据库区域，函数要使用的数据列，查找条件），其中第一个参数表示构成数据库的单元格区域（如下例中的的单元格区域（如下例中的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单元格求结果单元格求结果 A12=DCOUNT(A3:F10,“ A12=DCOUNT(A3:F10,“语文语文”,B1:B2)”,B1:B2)，其中第一个参数（，其中第一个参数（A3:F10A3:F10）表示数据库区域，）表示数据库区域， 第二个参数第二个参数“语文语文”为我们要使用的数据列（我们要在语文这一列查找），第三个参数为我们要使用的数据列（我们要在语文这一列查找），第三个参数 表示给定的查找条件（包含一个列标志表示给定的查找条件（包含一个列标志和一个位于列标志下方的条件和一个位于列标志下方的条件)2.2.Dget(),Dget(),求语文求语文89,89,历史历史9191的人的姓名，在的人的姓名，在B12B12中求结果，中求结果，B12=DGET(A3:F10,1,B1:C2)B12=DGET(A3:F10,1,B1:C2), , 这里，第二个参数我们使用列号来代表要使用的数据列。这里，第二个参数我们使用列号来代表要使用的数据列。2. Dmax,Dmin,Dsum,Daverage2. Dmax,Dmin,Dsum,Daverage函数函数DmaxDmax函数返回数据库选项中的最大值。函数返回数据库选项中的最大值。DminDmin函数返回数据库选项中的最大值。函数返回数据库选项中的最大值。DsumDsum函数作用是对符合条件的选项列求和。函数作用是对符合条件的选项列求和。DaverageDaverage函数作用是对符合条件的选项列求平均值。函数作用是对符合条件的选项列求平均值。语法：语法：以上四个函数语法规则与其它以上四个函数语法规则与其它DD函数一样。函数一样。DD（数据库区域，函数要（数据库区域，函数要使用的数据列，查找条件）使用的数据列，查找条件）2. Dmax,Dmin,Dsum,Daverage2. Dmax,Dmin,Dsum,Daverage函数函数举例：在下例中我们求小猪，小熊，小强三人中语文最高分。举例：在下例中我们求小猪，小熊，小强三人中语文最高分。1.1.我们可以使用我们可以使用Dmax()Dmax()，A15=DMAX(A6:F13,2,A1:A4)A15=DMAX(A6:F13,2,A1:A4)，同样，第一个参数表示数据库区，同样，第一个参数表示数据库区 域，第二个参数表示我们求值的数据列（可以是列号域，第二个参数表示我们求值的数据列（可以是列号2 2，也可以是文本，也可以是文本“语文语文”），第），第 三个参数是设定的条件。三个参数是设定的条件。2. Dmax,Dmin,Dsum,Daverage2. Dmax,Dmin,Dsum,Daverage函数函数举例：在下例中我们求所有人中手工课的最低分。举例：在下例中我们求所有人中手工课的最低分。2.2.我们可以使用我们可以使用Dmin()Dmin()，B15=DMIN(A6:F13,“B15=DMIN(A6:F13,“手工手工”,A6:A13)”,A6:A13)，同样，第一个参数表示据，同样，第一个参数表示据 库区域，第二个参数表示我们求值的数据列（可以是列号库区域，第二个参数表示我们求值的数据列（可以是列号6 6，也可以是文本，也可以是文本“手工手工”)”)， 第三个参数是条件（至少包含一个列标志第三个参数是条件（至少包含一个列标志和列标志下方的给定条件和列标志下方的给定条件）。）。2 2. Dmax,Dmin,Dsum,Daverage. Dmax,Dmin,Dsum,Daverage函数函数举例：在下例中我们求一班所有人的语文总分和平均分。举例：在下例中我们求一班所有人的语文总分和平均分。3.3.求总分，我们可以使用求总分，我们可以使用Dsum(),A13=DSUM(A4:G11,3,A1:A2)Dsum(),A13=DSUM(A4:G11,3,A1:A2)，得出结果为，得出结果为252.252.4.4.求平均分，我们可以使用求平均分，我们可以使用Daverage(),B13=DAVERAGE(A4:G11,3,A1:A2),Daverage(),B13=DAVERAGE(A4:G11,3,A1:A2),得出得出 结果为结果为84.84.谢谢！谢谢！