EXCEL函数应用学习教材

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,EXCEL,函数应用,人力资源部,Duney,EXCEL,函数,1.,时间函数,2.,数学函数,3.,统计、求和函数,3.,字符处理函数,4.,逻辑函数,5.,地址函数,6.,自动化常用函数,EXCEL,函数的结构,Excel,函数即是预先定义，执行计算、分析等处理数据任务的,特殊公式,。,Excel,函数结构,:,也有一些函数是没有参数的,如,ROW(),左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用,半角逗号,进行分隔,函数参数常用符号或表示方法,函数公式中的文本必须用半角引号，如：,东南汽车,;,而非直接输入 东南汽车或“东南汽车”,连接符,: &,如：,东南,& ,汽车,的值为 东南汽车,空值,/,空格的表示法,:,空值：,空格：, ,相关数学符号,:,（不等于）,;=,（大于等于）,;1),6.,如何对日期进行上、中、下旬区分,=LOOKUP(DAY(A1),0,11,21,31,上旬,中旬,下旬,下旬,),7.,什么函数可以显示当前星期 如：星期二,10:41:56,=TEXT(NOW(),aaaahh:mm:ss),8.,将“二三年十二月二十五日”转为“,2003-12-25”,格式,:,=DATE(1899+MATCH(LEFT(A7,4),TEXT(ROW($1900:$2100),“DBNum10000”),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),“,元”,“,一”,),TEXT(ROW($1:$366),“DBNum1m,月,d,日”,),0),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),“,元”,“,一”,),TEXT(ROW($1:$366),“DBNum1m,月,d,日”,),0),9.,自动排序,=SUBTOTAL(3,$B$2:B2)*1,=IF(A2A1,1,N(C1)+1),10.,怎样按奇数顺序然后再按偶数顺序排序,=IF(MOD(A1,2),0,1),=IF(ROW()50,(ROW()*2)-100,(ROW()*2)-1),=ROW()*2-1-(ROW()50)*99,有一个日期比如,:2007/02/12,想知道它减去一个固定天数比如,6,后,最接近它的一个星期四,(,只能提前,),是多少号,2007/02/12,的答案应该是,2007/02/01,而不是,2007/02/08,日期在,A1,处，,B1,处输入：,=MAX(WEEKDAY(A1-6-1,2,3,4,5,6,7,2)=4)*(A1-6-1,2,3,4,5,6,7),A1=2007/02/12,B1,输入公式,:,=A1-6-MOD(WEEKDAY(A1-6,2)+3,7),求最接近某一天的星期数,1.ABS():,取绝对值,例：,ABS(-9)=9,2.int():,取整数,例：,INT(9.8)=9,3.round():,例：,round(3.1415,3)=3.142,求小数位数，四舍五入,4.mod():,返回两数相除的余数。结果的 正负号与除数相同。,例：,MOD(8,5)=3 MOD(1,8)=1 MOD(2,8)=2 MOD(7,8)=7,5. max():,例：,MAX(A:A),求,A,列中最大值,6. min() :,例：,min(A:A),求,A,列中最小数,7. large(),：求第,m,大的数,例：,LARGE(A:A,2),求,A,列中第二大的数,8. small(2,A:A):,例：,small(A:A,2):,求,A,列中第,2,小的数,9. rank(2,A:A):,例,rank(2,A:A):,求,2,在,A,列中的排位（第几大的）,10. ISEVEN,（）测试是否偶数,，,例：,ISEVEN,（,4,）,=TRUE,数学函数,例：取前五名，后五名的方法,=LARGE(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW(),=SMALL(IF(ISERROR($D$2:$D$57),0,$D$2:$D$57),ROW(),Count():,统计,Countif(),：条件统计,例：,Countif(A:A,B1) A,列中等于,B1,值的个数，判断是否重复：,例：,Countif(A:A,“,男”,),；判断,A,列（性别列）中为男性的人数,例：,Countblank,（,A:A,）统计,A,列空格数目,例：统计区域,A1:C3,中互不相同的数据个数,=COUNT(IF(FREQUENCY(A1:C3,A1:C3),1),Countifs(),：多条件统计,例如：,COUNTIFS(A:A,“,男”,B:B,“20”,C:C,“,党员”,),统计大于,20,岁的男性党员的个数,Countblank,（）,统计、求和、计算函数,Product():,求乘积,Sumproduct():,条件统计,Sum():,求和,Sumif():,条件求和,Sumifs():,多条件求和,例：,SUMFS(H:H,A:A,“,男”,B:B,“20”,C:C,“,党员”,),计算大于,20,岁的男性党员的工资和（,H,列为工资列）,Everage():,求平均值,QUARTILE(),求四分位,例：,QUARTILE(B:B,1),求,B,列中前,25%,的四分位，,1,：前,25%,2,50%,3,75%,高级统计类函数（部分）,PERCENTRANK( ),PERCENTRANK( ),求百分比排位，可用于查看数据在数据集中所处的位置。,例：,PERCENTRANK(A:A,，,B1,）,:,求,B1,在,A,列数据的百分位,(,要按,CTRL+SHIFT+ENTER),字符及处理函数,Find(),：查字符串中是否含有某字符（串）；区分大小写，不能使用通配符,例：,FIND(O,I LOVE YOU,4)=3,find(“O”,“I LOVE YOU”,，,5)=9,从第,5,个字符开始找,O,，,O,在,I LOVE YOU,整个字符串中的位置是,9,Search():,查字符串中是否含有某字符（串）；不区分大小写，能使用通配符（其他同,FIND),replace(,老字符串,开始位置,替代次数，新字符串,):,字符串替代函数,例：,replace(“,我是张三”,3,2, “,李四”）,=,我是张三,SUBSTITUTE(A1,“A”,B1),例,：,SUBSTITUTE(“,我是张三”,”“,张三”, “,李四”）,=,我是李四,统计某字符在字符串中出现的次数,LEN(A1)-LEN(SUBSTITUTE(A1,6,“”) :,统计字符,6,在字符串中出现的次数,Rept():,重复某一文本几次,例：,rept(“*”,5)=*,Lower():,大写转小写,例：,lower(“A”)=a,Upper():,小写转大写,例：,Upper(“a”)=A:,Len(),：求字符串长度,例：,Len(“I LOVE YOU”)=10,Left():,取字符串左边第几个字符,例：,left(“bs-400,防水摄像机”,2) =bs,Right():,取字符串右边第几个字符,例：,right(“bs-400,防水摄像机”,2) =bs,Mid():,取字符串中间第几个字符,例：,mid (“bs-400,防水摄像机”,4,3) =400,Trim():,去掉单词之间的其他空格,例：,Trim(“I love you”)=I love you,Exact():,判断两个字符串是否完全相同,例：,Exact (“a”,“A”)=false,&,：字符串连接函数,例：“总数量：”,&1+1&“,人”,=,总数量：,2,人,例：计算出一段话（在,A1,中）中，字符,abc,出现的个数：,公式,=(LEN(A1)-LEN(SUBSTITUTE(A1,abc,)/LEN(abc),is():,判断是否为,ISBLANK(value),、,ISERR(value),、,ISERROR(value),、,ISLOGICAL(value),、,ISNA(value),、,ISNONTEXT(value),、,ISNUMBER(value),、,ISREF(value),、,ISTEXT(value,N():,转换为数值,TYPE(value),And():,并且,Or(),或者,Not():,否定，相反,If():,如果,逻辑函数,例如：自动生成序号,比如在第二列中输入内容回车后第一列的下一行自动生成序列号。,=IF(B2,A2+1,),address():,Row():,求行号,Column(),：,求列号,OFFSET():,引用某个表格的某行某列的数值,Hyperlink():,超级连接,例：,Hyperlink(”httP:/“),地址函数,indirect():,例如：若,B10,单元格等于,15,，则,=indirect(“B”&row(A10)=B10,index() index(,引用区域，行，列,),Match():,查找匹配,CHOOSE(),例：,CHOOSE(3,”1A”,”AW”,”D”,”T”,)=D,例：求生肖,CHOOSE(MOD(YEAR(,出生日期,)-4,12)+1,鼠,牛,虎,兔,龙,蛇,马,羊,猴,鸡,狗,猪,),办公自动化的几个最重要函数,1.OFFSET($A$1,，,MATCH(,1,2,3),COLUMN(),$A$1:,坐标原点，即“,A”,列为,0,列，“,1”,行为,0,行,2.SUMIFS(D:D,A ,A1,B, B1,C ,C1,),D:D:,对,D,列求和；,条件,1,：,A,列中值,=A1;,条件,2: B,列中值,=B1;,条件,3: C,列中值,=C1;,3.Countifs(,区域,1,值,1,区域,2,值,2),条件,1,：区域,1,中值,=,值,1;,条件,2:,区域,2,中值,=,值,2;,自动化实例,1.C-N,列公式：,SUMIFS(,订单跟踪汇总表,!$Q:$Q,订单跟踪汇总表,!$E:$E,$A4,订单跟踪汇总表,!$C:$C,YEAR($B$2),订单跟踪汇总表,!$D:$D,LEFT(C$3,LEN(C$3)-1),2.R,列公式：,RANK(O4,O:O,0)-1,3.S,列公式：,COUNTIF(,订单跟踪汇总表,!G:G,客户资料表,!B2),4.T,列公式,;,LOOKUP(2,1/(,订单跟踪汇总表,!$G$3:$G$10000=,客户资料表,!B2),订单跟踪汇总表,!$Q$3:$Q$10000),5.U,列公式：,LOOKUP(2,1/(,订单跟踪汇总表,!$G$3:$G$10000=,客户资料表,!B2),订单跟踪汇总表,!$B$3:$B$10000),6.,如何自动标注底色提醒？,条件格式设置,1.F/I/J/K,列公式：,OFFSET(,产品代码表,!$A$1,MATCH(E3,产品代码表,!$A$2:$A$30000,0),N),2.N,列公式：,SUMIFS(M:M,E:E,E3),3.0,列公式：,SUMIFS(,库存,!C:C,库存,!A:A,订单跟踪汇总表,!E3),4.P,列公式：,IF(N3=MONTH(NOW()-1)*(,离职人员,!BK$1:BK$30000=YEAR(NOW(),ROW($1:$30000),ROW(1:1)&,“,按,CTRL+SHIFT+ENTER,挑出本月入职人员，形成单独表格：,INDEX(,在职人员,!B:B,SMALL(IF(,在职人员,!$AD$1:$AD$9997=DATE(YEAR(NOW(),MONTH(NOW(),1),ROW($B$1:$B$10000),65535),ROW(2:2)&,判断单元格,(A1,A12),单元格数据是否重复并统计出重复的行号,在,B1,中输入：,=IF(COUNTIF($A$1:$A$13,VLOOKUP(A1,A2:$A$13,1,0)1,CONCATENATE(,重复行号,:,MATCH(A1,A2:A$13,0)+ROW(A1),) B1,单元格中会显示与,A1,数据重复的行号。下面，选择区域,B1,B12,，点击菜单栏“编辑”“填充”“序列”，在弹出对话框中查看“类型”项目，在此选择“自动填充”，其余选项保持默认设置。确认操作后，,B2,B12,之间的重复行号均会自动填充（如图,1,）。,1.,求“您好”第,n,次出现的行号,:,SMALL(IF($A$1:$A$10=,您好,ROW($A$1:$A$10),48),ROW(1:1),2.,自动从大到小、从小到大排序：,H2,中输入“,=INDEX(Name,MATCH(LARGE(Salary+ROW(Salary),ROW()-1),Salary+ROW(Salary),0)”,，最后按,CTRL+SHIFT+ENTER,，,3.,阳历转阴历,IF(ISERROR(MONTH(TEXT(A2,$-130000e-m-d),SUBSTITUTE(TEXT(A2,$-130000e-m-d),13,12),TEXT(A2,$-130000e-m-d),4.,自动挑出出现频率最多的数值，并由大排列形成新的表,假设：表,1,中的,E,列中是电话号码（有很多号码是重复的），如把打的最多的电话号码挑选出来，并由大到校排列（第一行是标题）：,1.,在,O2,中输入：,countif(E:E,E1),，拉下去，,统计每个号码出现的次数,2.P2,列中输入：,INDEX(O:O,SMALL(IF(MATCH(IF(O:O=,1,O:O),IF(O:O=,1,O:O),)=ROW(O:O),ROW(O:O),65536),ROW(A2),按,CTRL+SHIFT+ENTER,数组公式，拉下去，,挑出不重复的出现次数,5,.,自动挑选排序填的列中输入（新的表，新的号码列）：,=OFFSET(,表,1$A$1,MATCH(LARGE(,表,1!P:P,ROW(B1),表,1!O:O,0)-1,5),回车,LARGE(,表,1!P:P,ROW(B1),：求出现次数中第一大的（即出现最多的次数）,MATCH(LARGE(,表,1!P:P,ROW(B1),表,1!O:O,0)-1:,求,(P,列,),最大次数在次数列,(O,列）中找，找到后返回行号，减,1,为减标题栏,OFFSET(,表,1$A$1,，,5,）以表,1,的,A1,单元格为坐标远点，引用返回出现拨打第,N,多的电话号码所在的行列号中的值。,6.,从含有重复值的列中挑选不重复的值形成另一个列,方法,1,：（数组公式，内存占用大，但是中间没空格）,INDEX(F:F,SMALL(IF(MATCH(IF(F:F=,1,F:F),IF(F:F=,1,F:F),)=ROW(F:F),ROW(F:F),65536),ROW(A2),数组公式，按,CTRL+SHIFT+ENTER,方法,2,：（函数，内存占用小，中间有空格）,IF(ISERROR(MATCH(F2,$F$1:$F1,0),INDEX(F:F,ROW(F2),),往下拖公式。,方法,3,：非公式法,选中数据列,-,数据,筛选,高级,将将筛选结果复制到其他位置,光标移到“复制到”框,勾选“选择不重复的记录”,确定,7.,自动显示重复行行号（根据行号可自动把重复数据形成报表）,若,A,列是数据，有重复数据，可在,B,列输入下列公式并按,CTRL+SHIFT+ENTER,IF(COUNTIF($A$1:$A$13,VLOOKUP(A2,A3:$A$13,1,0)1,MATCH(A2,A3:A$13,0)+ROW(A2),),如何自动标示,A,栏中的数字大小排序？,=RANK(A1,$A$1:$A$5),=RANK(A1,A:A),如何设置自动排序,A,列自动变成从小到大排列,B=SMALL(A$2:A$28,ROW(1:1),A,列自动变成从大到小排列,B=LARGE(A$2:A$28,ROW(1:1),重复数据得到唯一的排位序列,想得到数据的出现总数吗（,1,2,2,3,4,4,5,数据的出现总数为,5,）？,=RANK(B3,B$3:B$12)+COUNTIF(B$3:B3,B3)-1,怎样才能让数列自动加数,怎样做才能让数列自动加数，例：,A000X,公式为,=A1&“000”&COUNTIF(A$1:A1,A1),向下拖,对于普通排名分数相同时，按顺序进行不重复排名,=RANK(K32,$K$32:$K$55)+COUNTIF($K$32:$K32,K32)-1,如何实现快速定位（筛选出不重复值）,=IF(COUNTIF($A$2:A2,A2)=1,A2,),=IF(COUNTIF($A$2:A2,A2)=1)=TRUE,A2,),在工作表里有连续,10,行数据,现在要每行间格,2,行,=IF(MOD(ROW(),3)=1,INDEX(Sheet1!$A$1:$Z$500,INT(ROW()/3)+1,COLUMN(),),一个大表每一行下面需要加一行空行，怎么加最方便,=IF(MOD(ROW(),2),INDIRECT(a&ROUNDUP(ROW()/2,0),),将原有列中的内容倒置过来,B1 =OFFSET(A$1,COUNTA(A:A)-ROW(A1),),查找一列中最后一个数值,=LOOKUP(9E+307,Sheet2!A:A),最后一个数值,=LOOKUP(REPT(“,座”,255),Sheet2!A:A),最后一个文本，或,=INDEX(Sheet2!A:A,MATCH(9E+307,Sheet2!A:A),=INDEX(Sheet2!A:A,MATCH(*,Sheet2!A:A,-1),Match(rept(,座,255),sheet2!A:A),小写数字转换成人民币大写,=IF(A10,负,)&TEXT(TRUNC(ABS(A1),DBNum2)&,元,&IF(ISERR(FIND(.,TRUNC(A1,2),TEXT(RIGHT(TRUNC(A1*10),DBNum2)&IF(RIGHT(TRUNC(A1*10)=0,角,)&IF(LEFT(RIGHT(TRUNC(A1,2),3)=.,TEXT(RIGHT(TRUNC(A1,2),DBNum2)&,分,整,),隔行就用不一样的颜色填充背景；,选中行,格式,条件格式,(,公式）：,=MOD(ROW(A1),2)=0,然后用格式刷下去；,或：,格式,条件格式,(,公式）：,=IF($B1=,MOD(ROW(),2),隔,3,行显示：,MOD(ROW(A1),3)=1,获取外部数据,数据自网站输入,URL,（网站数据表格所在网页）转到网站表格左边点击“” 变成“” 导入数据确定,THANK YOU!,