办公自动化培训XXXX.ppt

EXCEL函数应用,人力资源部Duney,EXCEL函数,1.时间函数2.数学函数3.统计、求和函数3.字符处理函数4.逻辑函数5.地址函数6.自动化常用函数,EXCEL函数的结构,Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。,Excel函数结构:,也有一些函数是没有参数的,如ROW(),左右括号成对出现,单一结构,嵌套结构,参数与参数之间使用半角逗号进行分隔,函数参数常用符号或表示方法,函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入东南汽车或“东南汽车”连接符:A2:B7；1:1；1:5；F:F；A：N,1.today():求今天现在的日期2.now():求现在3.year():求年例：YEAR(“2012-12-31”)=20124.month():求“月”例：MONTH(“2012-12-31”)=125.day():返回天例：DAY(“2012-12-31”)=316.hour():求小时数7.minute()：求分钟8.second():求秒9.date()：求日期10.datedif()例：datedif(“2012-1-1”,”2013-1-1”,”Y”)=111.TIMEVALUE()例：12:00和8:00相差TIMEVALUE(“12：00”)-TIMEVALUE(“8:00”),时间函数,1.将“20060501”转换为“2006-05-01”格式=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2)2.将文本“2004.01.02”转换为日期格式：2004-1-2=DATE(MID(A1,1,4),MID(A1,6,2),MID(A1,9,2)3.将2005-8-6转换为2005年8月6日格式=TEXT(A1,yyyy年m月d日;)4.将“1968年6月12日”转换为“1968/6/12”格式=YEAR(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)-13.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)&判断单元格(A1A12)单元格数据是否重复并统计出重复的行号,在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数据重复的行号。下面，选择区域B1B12，点击菜单栏“编辑”“填充”“序列”，在弹出对话框中查看“类型”项目，在此选择“自动填充”，其余选项保持默认设置。确认操作后，B2B12之间的重复行号均会自动填充（如图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+ENTERIF(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)