EXCEL办公实用函数使用技巧PPT.ppt

,Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,#,EXCEL,办公实用函数使用技巧,1,目录,EXCEL,函数的基本知识,逻辑函数的应用,文本函数的应用,日期与时间函数,数学与统计函数,查询与引用函数,数据库函数的应用,2,Unit 1 EXCEL,函数的基本知识,什么,是函数,函数的参数和返回值,函数的地址符,函数的套用,3,1.,什么,是函数,Excel,中所提的函数其实是一些预定义的公式，它们使用一,些称为参数的特定数值按特定的顺序或结构进行计算。,简单点说，函数是一组功能模块，使用函数能帮助你实现某个,功能。,举例：,Sum,函数可以实现数值的加法运算，,max,函数可以返回一组数的最大值。,4,2.,函数的参数及返回值,1.,参数可以是数字、文本、形如,TRUE,或,FALSE,的逻辑数值、数组、,形如,#N/A,的错误值或单元格引用。函数的参数使用逗号隔开如,sum(A1,2,B2),，其中，,A1,2,B2,都是,sum,函数的参数，用逗号隔开,另外要注意的是，函数使用的参数所用的符号，如逗号，引号要全部在,英文输入状态下输入,(,半角符号,),。,数字：,1,、,2,、,100,、,10000,文本：,astron,、,412398784737847,（以文本格式存储的数字）,逻辑值：,TRUE(,真,),或非零（一般用数字,1,表示）、,FALSE(,假,),或,0,比如,13,这个不等式的值为假。,单元格：比如,A47,、,B16,数组：同一种格式存储的单元格区域,5,2.,大部分函数都有一个返回值，返回你通过函数运算得出的结果,返回值可,以是一个数字，一个逻辑值，一个地址。另外要注意的是，要想要函数返,回正确的值，使用公式的单元格数值格式（快捷键,ctrl+1,）一定要正确。,举例：,sum(1,2,3),我们的目的是得出,1+2+3,相加的和，,sum,函数返回的值是,6,另外值得注意的是，对于函数的返回值如数值，它等同一个数值，而不要,把它认为是一个函数。它可以如其它数值一样使用加（,+,）、减（,-,）、,乘（*）、除（,/,）。,如上例中，可以,A1=10-sum(1,2,3),，那么,A1=4,。在这里，因为,sum,（）函数,的返回值是一个数值，可以直接把它当数值用。,2.,函数的参数及返回值,6,3.,函数的地址符,在我们使用函数时，会引用一些地址作为参数，比如,A47,、,B46,等,EXCEL,中有专门的地址引用符,$,。使用是,$,引用地址时，是绝对引用，,不使用,$,时，是相对引用。,举例：,A47,：相对引用，引用,A47,这个单元格。当使用这种类型的地址时，,我们使用拖拉功能将公式引用到其它单元格时，这个相对,地址会发生变化,7,3.,函数的地址符,$A$47:,绝对引用。无论将公式引用到哪一个单元格，这个地址不会,改变，永远定格在,A47,这个单元格。,$A47,和,A$47:,混合引用。只定格单元格的行或列，相对来说有点复杂，,不常用。,另外在引用地址时，冒号（：）在与单元格一起引用时，代表一片区域，,如,(A1:A47),代表的是从,A1,一直到,A47,的长条形区域，总共,47,个单元格，,如（,A1:B3),代表以,A1,和,B3,为对角的一个方形区域，共,9,个单元格。,注：在引用地址符时，我们可以使用,F4,鍵在这些地址间进行切换,8,4.,函数的套用,函数的套用，也就是函数的组合使用，单个函数已经很厉害了，,组合起来的函数威力会更强大。,举例：,IF(SUM(1,2,3)5,“100”,“1000”),在这个函数中，逻辑函数,IF,套用了,SUM,函数，它把,SUM,函数返回,的结果作为参数，如果,1,，,2,，,3,之和小于,5,，返回,100,，反之，返,回,1000.,9,Unit 2,逻辑函数的应用,IF,函数,AND,函数,OR,函数,NOT,函数,10,1.IF,函数,IF,在英文里是“假如，如果”的意思，顾名思义就是如果满足某个条件（为真）,则返回前面的一个值，不满足条件（为假），返回后面的一个值。,语法：,IF(,逻辑值，返回值,1,，返回值,2),，其中逻辑值类型为真或假，另外返,回值是文本格式时，要用双引号，如果为数字，可以不用双引号。,举例：如何判断考试成绩是否及格？,IF(,A1,60,“,不及格,”,“,及格,”),，,如果单元格,A1,中的成绩小于,60,，则,返回不及格，反之，返回及格。,IF(A184,“,优秀”,“,及格,”),，这是,IF,函数,套用,IF,函数，如果,A1,小于,60,，返回不及格，另外如果大于等于,85,，则返回,优秀，否则返回及格。,11,2.AND,函数,AND,在英文里是“和，与”的意思,只有当所有条件都满足时，才返回,ture(,真,),，否则返回,false(,假,),一般要与其它函数套用，比如,IF,函数。,语法：,AND(,条件,1,，条件,2,，,条件,30),返回值：,TURE,或,FALSE,举例：,1.and(A160,B280),只有,A160,B280,这三个条件,都满足时，才返回,TRUE,否则,FALSE.,2.IF,（,and(A160,B280),1,，,2,），三个条件都满足时，返回,1,，,否则返回,2.,12,3.OR,函数,OR,在英文里是“或，或者,”,的意思，只要满足任一条件就,返回,ture(,真,),，否则返回,false(,假,),一般要与其它函数套,用，比如,IF,函数。,语法：,OR(,条件,1,，条件,2,，,条件,30),举例：,or,(A160,B280),当,A160,B280,这三个条件有一个满足时，返回,TRUE,否则,FALSE.,13,4.not,函数,not,在英文里是“不，非,”,的意思，返回相反的值一般要,与其它函数套用，比如,IF,函数。,语法：,not(,条件,),举例：,not(A160),当,A160,时，返回,FALSE,否则,TRUE.,14,Unit 3,文本函数的应用,比较函数,excat,与等号,=,联结函数,concatenate,与联结号,&,Lower,、,upper,、,proper,、,trim,函数,left,、,right,、,mid,函数,15,1.,比较函数,excat,与等号,=,Excat,是一个比较函数，等号,=,与之有相同的功能。返回值为逻辑值，,对于返回值为逻辑值的函数，一般与,if,等函数套用。,语法：,excat(A1,B1),举例：,1.excat(A1,B1),当,A1,与,B1,相同时，返回,TRUE,否则,FALSE.,与,if,函数套用，,if(excat(A1,B1),”,相同”，“不相同”,),。,2.,使用等号比较时，如单元格,C1=,（,A1=B1,），如果,A1,与,B1,相同，,C1=TRUE,否则,C1=FALSE,与,if,函数套用，,if(A1=B1,“,相同”，“不相同”,),。,16,2.,联结函数,concatenate,与联结号,&,concatenate,是一个联结函数，联结号,&,与之有相同的功,能。,语法：,concatenate(,字符,1,字符,2,，,),，单独的文本要,使用双引号,举例：,1.concatenate(A1,B1,，“,tron”),如果,A1=A,，,B1=s,，,那么函数就会返回,Astron,2.,如果,C1=A1&B1&“tron,”,，那么,C1=Astron,17,3.Lower,、,upper,、,proper,、,trim,函数,Lower,函数是将文本中的字母转换成小写字母。,Upper,函数是将文本中的字母转换成大写字母,Proper,函数是将文本中的字符串首字母转换成大写字母，其余字符转换成小写字母。,Trim,函数是去除字符串中多余空白字符。,语法：,XXXX(,字符串），其中,XXXX,代表以上所有函数，它们的语法规则一样，字符串代表内容是字符的地址引用比如,A1,B2,也可以是单独的字符串（要使用双引号如“,astron”,）,18,3.Lower,、,upper,、,proper,、,trim,函数,举例：,假如,A1=ASTRON,B1=astron,C1=dongguan astron,D1=DongGuan Astron,lower(A1)=astron,Upper(B1)=ASTRON,Proper(C1)=Dongguan Astron,Trim(D1)=DongGuan Astron,19,4.left,、,right,、,mid,函数,Left,函数是自字符串左边开始提取字符串中的字符,Right,函数是自字符串左边开始提取字符串中的字符,Mid,函数是从字符串指定位置开始提取字符串中的字符,语法：,1.Left(,字符串，数值,),，其中数值为要提取的字符数,2.Right(,字符串，数值,),，其中数值为要提取的字符数,3.Mid(,字符串，数值,1,，数值,2),，其中数值,1,代表要提取,的第一个字符的位置，数值,2,代表要提取的字符数。,20,4.left,、,right,、,mid,函数,举例：,假如,A1=Astron,Left(A1,2)=As,Right(A1,2)=on,Mid(A1,2,2)=st,21,Unit 4,日期与时间函数,now,、,today,函数,year,、,month,、,day,、,hour,函数,dateif,函数,22,1.now,、,today,函数,now,函数是提取当前系统日期和时间,Today,函数是提取当前系统日期,语法：,Now(),参数为空,Today(),参数为空,举例：,假定系统当前时间为,2013/03/30 9:48,1.,如果,A1=now(),那么,A1=2013/03/30 9:48,2.,如果,B1=today(),那么,B1=2013/03/30,23,2.year,、,month,、,day,、,hour,函数,year,函数作用是提取日期中的年份,month,函数作用是提取日期中的月份,Day,函数作用是提取日期中的日数,Hour,函数作用是提取日期中的小时数,语法：,XXX(A1),，其中,XXX,代表上面所有函数，它们语法规则一样，,A1,代表包含日期与,时间的单元格。,举例：,假定系统当前时间为,A1=2011/11/17 9:58,1.,B1=year(A1),那么,B1=2011,2.,C1=month(A1),那么,C1=11,3.D1=day(A1),，那么,D1=17,4.E1=hour(A1),那么,E1=9,24,3.dateif,函数,dateif,函数作用是计算两个日期之间相差的年数、月数、日数，这个函数比较适用于统计出生年月,语法：,Dateif(,起始日期，结束日期，参数类型,),其中参数类型包括：,“,Y”,时间段中的整年数,，,“,M”,时间段中的整月数,，,“,D”,时间段,中,的天数,举例：,假定系统当前日期为,2011/11/17,单元格,A1,中日期为,2000/01/01,下面例子分别计算起始,日期与当前日期间相差的年数，月数，日数。,1.,如果,B1=DATEDIF(A1,TODAY(),y),那么,B1=11,2.,如果,B1=DATEDIF(A1,TODAY(),“m),那么,B1=142,3.,如果,B1=DATEDIF(A1,TODAY(),“d),那么,B1=4338,25,Unit,5,数学与统计函数,Sum,、,average,、,abs,、,mod,函数,sumif,、,countif,函数,max,、,min,、,small,、,large,、,rank,函数,value,、,isodd,、,iseven,函数,26,1.Sum,、,average,、,abs,、,mod,、,power,函数,Sum,函数作用是求和,Average,函数作用是求平均数,Abs,函数作用是求绝对值,Mod,函数作用是求余,Power,函数作用是求,n,次方,语法：,Sum(,数值,1,，数值,2),其中数值可以直接是数字，也可以是包含数字的单元格，也可以是一片单元格区域。,Average,语法规则同上,Abs,（数值）,Mod(,数值,1,，数值,2),，其中数值,1,是被除数，数值,2,是除数,Power(,数值,1,，数值,2),，其中数值,1,是数，数值,2,是多少次方,以上函数返回值皆为数字,27,1.Sum,、,average,、,abs,、,mod,函数,举例：,假设,A1=1,，,B1=2,C1=-2,1.sum(A1,B1)=3,2.average(A1,B1)=1.5,3.abs(A1)=1,abs(C1)=2,4.mod(B1,A1)=0,mod(A1,B1)=1,5.power(B1,2)=4,另外,（数字,6,上面的符号）也有这个功能，比如,B13=8,另外要注意的是，想要减或者求负数，直接在单元格前加,-,号就可以了,比如上面例子中,sum(A1,-B1)=-1,28,2.sumif,、,countif,函数,Sumif,函数作用是对区域内符合条件的单元格求和,Countif,函数作用是统计区域内符合条件的单元格的数量,语法：,Sumif(,条件区域，条件，要计算和的区域,),Countif(,区域，条件,),举例：,29,2.sumif,、,countif,函数,假设有如下表格,1.,假如我们要计算,1,班所有人的手工课的总分，可以使用如下函数,SUMIF(A2:A5,“1”,G2:G5),在函数里面，前一区域,A2:A5,代表查寻条件“,1”,的查找区,域，中间的“,1”,表示查找的条件,(,要使用双引号,),，后一区域,G2:G5,代表要加总的数字,区域,2.,假如我们要统计手工课大于,90,分的人数，可以使用,countif(G2:G5,”90”,),前面代表,要查找的区域，后面表示查找条件，记住要使用双引号,30,3.max,、,min,、,small,、,large,、,rank,函数,Ma,x,求一组数据的最大值,Min,求一组数据的最小值,Small,求一组数据的第,N,小的值,Large,求一组数据的第,N,大的值,Rank,作用是将一组数组排序,语法：,Max(,数字,1,，,数字,n),，参数可以是单独的数字，也可以是包含数字的单元格。,MIN,函数语法同上。,Small(,单元格区域，数字,N),，其中前者为包含数字的单元格区域，后者为要查找的第,N,小的数字。,31,3.max,、,min,、,small,、,large,、,rank,函数,large(,单元格区域，数字,N),，其中前者为包含数字的单元格区域，后者为要查找的第,N,大的数字。,Rank(,需排序的数字，需排序的数组，排序类型,),，其中排序类型为,0,时，按降序排列，不为,0,时，按升序排列。,举例：,假设,A1,A2,A3,A4,A5,分别为,12,，,9,，,7,，,7,，,21,Max(A1:A5)=21,Min(A1:A5)=7,Small(A1:A5,3)=9,Large(A1:A5,5)=7,32,3.max,、,min,、,small,、,large,、,rank,函数,举例,假设,A1,A2,A3,A4,A5,分别为,12,，,9,，,7,，,7,，,21,使用,Rank,（）函数,对这个数组进行排序，按降序,(,从大到小,),排列,其中,B,列是排序后的序号,B1=RANK(A1,$A$1:$A$5,0),其中,A1,表示要排序的数字，,A1:A5,表示排序,的数组，,0,表示按降序排列，另外要注意的是，引用排序的数组时，我,们用了绝对地址引用,$A$1:$A$5(,这个可以用,F4,鍵在地圵符间进行切换,),这样我们在引用公式到,B2,B3,单元格时，这个地址区域不会变。,33,4.value,函数,Value,函数作用是将文本类型的数字转换成数字类型，常用于出生日期，银行卡等数据类型的转换。,语法：,Value(,文本,),，其中文本必须为数字，只是存储类型为文本格式,举例：,假设,A1=20111121,，,A2=5,如果,B1=2011-VALUE(MID(A1,3,2),，那么,B1=2000.,我们知道，,MID,函数,的作用是取文本中的字符（第三章第,4,小节），在这里，我们从,A1,单元,格的第,3,个字符开始取，取,2,个字符，得到字符,11,，然后将用,value,函数,将其转换成数字类型,11,。最后与,2011,相减得,2000,34,Unit 6,查询与引用函数,Vlookup,hlookup,函数,Match,index,函数,35,1.,vlookup,hlookup,函数,Vlookup,函数用于在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。,H,lookup,函数用于在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。,语法：,Vlookup(,要查找的值，查找的区域，要返回列的序号，查找类型,),其中要注意的问题：,1.,要查找的值一定要位于查找区域的首列,2.,查找的区域中不要有隐藏的行或列，否则返回的值会出错,3.,查找类型为,0,或,false,（假）时，为精准匹配，当省略或为,true,时，为近似匹,配（没有找到要查找的值时，返回最接近于“查找值,”,的值），这里我们常,常使用精准匹配，保证查找的准确性。,Hlookup,函数语法与,Vlookup,函数语法相同。,36,1.,vlookup,hlookup,函数,举例：,表,1,（,sheet1,）表,2,（,sheet2,）,如果我们要在表,2,中根据姓名导出手工课的成绩，可以使用如下函数,B2=VLOOKUP(A2,表,1,的,$,A,$2:,表,1,的,$F$5,6,0,),填写参数时，可以在公式输入栏里选择,Vlookup,函数后直接用鼠标点击输入,1.,第一个参数代表我们要查找的值（根据姓名查找手工成绩）要注意的是，查找值有相同的（有同名,的），返回第一个值所对应的返回值,2.,第二个参数代表包含查找值的查找区域，这个查找区域可以在其他工作表上，也可以在同一张工作表上,在本例中我们的查找区域位于工作表一。查找区域使用绝对引用,$,固定的查找区域我们要使用绝对引,用，不然将公式引用到其它单元格时，查找区域也会变动，。另外在上面例子中我们的查找值位于查,找区域的第一列（必须的）,37,1.,vlookup,hlookup,函数,举例：,B2=VLOOKUP(A2,表,1,的,$,A,$2:,表,1,的,$F$5,6,0,),3.,第三个参数,6,代表我们要返回的值的列号（手工成绩位于第,6,列）,4.,第四个参数,0,代表包含查找类型为精准查找，一般我们要使用精准查,找，保证查找值的准确性,Hlookup,函数使用与,Vlookup,函数相似，只是将行列互换而已。具体区别大,家可以自己体验一下。,38,2,.Match,index,函数,Match,函数查找某值在数组中的位置。,Index,函数作用是返回数组中某行某列的值,语法：,1.Match(,要查找的值，数组，查找类型,),，其中要查找的值可以是单独输入的值,（要使用双引号），也可以是包含查找值的单元格。数组包含查找值的一组单元,格。查找类型包含,3,种，为,-1,时,参数中的数组须降序排列；为,1,时，数组须升序,排列；为,0,时，可以是无序排列。,2.index(,数组，行，列,),其中的行列是数组的相对的行列值，不是工作表的行列,39,2,.Match,index,函数,举例：,在上面的数组中,1.,A8,=MATCH(4,A2:A6,0),其中，第一个参数,4,代表我们要在数组（,A2:A4,）,中查找,4,这个值,最后一个参数,0,代表我们查找的数组是无序排列。,2.B8=INDEX(A2:B6,3,2),，其中，第,1,个参数表示数组的区域，第,2,，,3,个参,数表示这个查找区域的行和列。,40,Unit 7,数据库函数的应用,Dget,Dcount,函数,Dmax,Dmin,Dsum,Daverage,函数,41,1.Dget,Dcount,函数,D,函数是数据库类型函数，与其它类型函数不同的是前面多了一个字,母,D,（数据库,database,的首字母），数据库函数的使用功能更强大，可,以在查找引用中使用更加复杂的查找关系。,1.Dget,函数提取满足条件的单个记录，有多个值符合条件时返回错误,2.Dcount,函数返回满足条件的单元格的个数,语法：,D,函数的语法格式都一样,D,（数据库区域，函数要使用的数据列，查找条件），其中第一个参数表示构成数据库,的单元格区域（如下例中的,A3:F10,），第二个参数表示函数要使用的数据列，可以是用,双引号引用的文本，也可以是列号（如下例中的“语文”，,1,），第三个参数代表包含给,定条件的单元格区域，它至少包含一个列标志和列标志下方用于设定条件的单元格（如,下例中的,B1:B2,，,B1:C2,）。,42,1.Dget,Dcount,函数,举例：,1.Dcount(),，在上表中，如果我们想要统计语文,89,的人数数量，可以在,B1,中设置列标志,（语文），在,B2,中设置给定条件（,89,）,在这里我们在,A12,单元格求结果,A12=DCOUNT(A3:F10,“,语文,”,B1:B2),，其中第一个参数（,A3:F10,）表示数据库区域，,第二个参数“语文”为我们要使用的数据列（我们要在语文这一列查找），第三个参数,表示给定的查找条件（包含一个列标志,和一个位于列标志下方的条件,),2.,Dget(),求语文,89,历史,91,的人的姓名，在,B12,中求结果，,B12=DGET(A3:F10,1,B1:C2),这里，第二个参数我们使用列号来代表要使用的数据列。,43,2.Dmax,Dmin,Dsum,Daverage,函数,Dmax,函数返回数据库选项中的最大值。,Dmin,函数返回数据库选项中的最大值。,Dsum,函数作用是对符合条件的选项列求和。,Daverage,函数作用是对符合条件的选项列求平均值。,语法：,以上四个函数语法规则与其它,D,函数一样。,D,（数据库区域，函数要,使用的数据列，查找条件）,44,2.Dmax,Dmin,Dsum,Daverage,函数,举例：在下例中我们求小猪，小熊，小强三人中语文最高分。,1.,我们可以使用,Dmax(),，,A15=DMAX(A6:F13,2,A1:A4),，同样，第一个参数表示数据库区,域，第二个参数表示我们求值的数据列（可以是列号,2,，也可以是文本“语文”），第,三个参数是设定的条件。,45,2.Dmax,Dmin,Dsum,Daverage,函数,举例：在下例中我们求所有人中手工课的最低分。,2.,我们可以使用,Dmin(),，,B15=DMIN(A6:F13,“,手工,”,A6:A13),，同样，第一个参数表示据,库区域，第二个参数表示我们求值的数据列（可以是列号,6,，也可以是文本“手工,”),，,第三个参数是条件（至少包含一个列标志,和列标志下方的给定条件,）。,46,2,.Dmax,Dmin,Dsum,Daverage,函数,举例：在下例中我们求一班所有人的语文总分和平均分。,3.,求总分，我们可以使用,Dsum(),A13=DSUM(A4:G11,3,A1:A2),，得出结果为,252.,4.,求平均分，我们可以使用,Daverage(),B13=DAVERAGE(A4:G11,3,A1:A2),得出,结果为,84.,47,谢 谢！,48,2025/1/4 周六,