办公自动化培训XXXX

EXCEL函数应用人力资源部DuneyEXCEL函数1.时间函数2.数学函数3.统计、求和函数3.字符处理函数4.逻辑函数5.地址函数6.自动化常用函数EXCEL函数的结构Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。Excel函数结构:也有一些函数是没有参数的,如ROW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔函数参数常用符号或表示方法函数公式中的文本必须用半角引号，如：〝东南汽车〞;而非直接输入东南汽车或“东南汽车”连接符:&如：〝东南〞&〝汽车〞的值为东南汽车空值/空格的表示法:空值：〝〞空格：〝〞相关数学符号:（不等于）;=（大于等于）;=（小于等于）单元格引用表示法:A2;A$2;$A2;A$2;A2:B7；1:1；1:5；F:F；A：N1.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)&/&MONTH(A1)&/&DAY(A1)显示：1968/6/12=TEXT(A1,yyyy/mm/dd)显示：1968/06/125.用公式算出除去当月星期六、星期日以外的天数=SUMPRODUCT(--(MOD(ROW(INDIRECT(DATE(YEAR(NOW()),MONTH(NOW()),1)&:&DATE(YEAR(NOW()),MONTH(NOW())+1,0))),7)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),“[DBNum1]0000”),0),MONTH(MATCH(SUBSTITUTE(MID(A7,6,7),“元”,“一”),TEXT(ROW($1:$366),“[DBNum1]m月d日”),0)),DAY(MATCH(SUBSTITUTE(MID(A7,6,7),“元”,“一”),TEXT(ROW($1:$366),“[DBNum1]m月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)=92.int():取整数例：INT(9.8)=93.round():例：round(3.1415,3)=3.142求小数位数，四舍五入4.mod():返回两数相除的余数。结果的正负号与除数相同。例：MOD(8,5)=3MOD(1,8)=1MOD(2,8)=2MOD(7,8)=75.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,ILOVEYOU,4)=3find(“O”,“ILOVEYOU”，5)=9从第5个字符开始找O，O在ILOVEYOU整个字符串中的位置是9•Search():查字符串中是否含有某字符（串）；不区分大小写，能使用通配符（其他同FIND)•replace(老字符串,开始位置,替代次数，新字符串):字符串替代函数例：replace(“我是张三”,3,2,“李四”）=我是张三•SUBSTITUTE(A1,“A”,B1)例：SUBSTITUTE(“我是张三”,”“张三”,“李四”）=我是李四统计某字符在字符串中出现的次数LEN(A1)-LEN(SUBSTITUTE(A1,6,“”)):统计字符6在字符串中出现的次数•Rept():重复某一文本几次例：rept(“*”,5)=*****1.Lower():大写转小写例：lower(“A”)=a2.Upper():小写转大写例：Upper(“a”)=A:3.Len()：求字符串长度例：Len(“ILOVEYOU”)=104.Left():取字符串左边第几个字符例：left(“bs-400防水摄像机”,2)=bs5.Right():取字符串右边第几个字符例：right(“bs-400防水摄像机”,2)=bs6.Mid():取字符串中间第几个字符例：mid(“bs-400防水摄像机”,4,3)=4007.Trim():去掉单词之间的其他空格例：Trim(“Iloveyou”)=Iloveyou8.Exact():判断两个字符串是否完全相同例：Exact(“a”,“A”)=false9.&：字符串连接函数例：“总数量：”&1+1&“人”=总数量：2人★例：计算出一段话（在A1中）中，字符abc出现的个数：公式=(LEN(A1)-LEN(SUBSTITUTE(A1,abc,)))/LEN(abc)1.1.is():判断是否为——ISBLANK(value)、ISERR(value)、ISERROR(value)、ISLOGICAL(value)、ISNA(value)、ISNONTEXT(value)、ISNUMBER(value)、ISREF(value)、ISTEXT(value2.N():转换为数值3.TYPE(value)4.And():并且5.Or()或者6.Not():否定，相反7.If():如果逻辑函数例如：自动生成序号,比如在第二列中输入内容回车后第一列的下一行自动生成序列号。=IF(B2,A2+1,)address():Row():求行号Column()：求列号OFFSET():引用某个表格的某行某列的数值Hyperlink():超级连接例：Hyperlink(”httP://www.sina.com“)地址函数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,