办公自动化培训

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/125.用公式算出除去当月星期六、星期日以外的天数=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(),"aaaa

hh: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(A2<>A1,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/12B1,输入公式:=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)=*****Lower():大写转小写例：lower(“A”)=aUpper():小写转大写例：Upper(“a”)=A:Len()：求字符串长度例：Len(“ILOVEYOU”)=10Left():取字符串左边第几个字符例：left(“bs-400防水摄像机”,2)=bsRight():取字符串右边第几个字符例：right(“bs-400防水摄像机”,2)=bsMid():取字符串中间第几个字符例：mid(“bs-400防水摄像机”,4,3)=400Trim():去掉单词之间的其他空格例：Trim(“Iloveyou”)=IloveyouExact():判断两个字符串是否完全相同例：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(valueN():转换为数值TYPE(value)And():并且Or()或者Not():否定，相反If():如果逻辑函数例如：自动生成序号,比如在第二列中输入内容回车后第一列的下一行自动生成序列号。=IF(B2<>"",A2+1,"")address():Row():求行号Column()：求列号OFFSET():引用某个表格的某行某列的数值Hyperlink():超级连接例：Hyperlink(”httP://“)地址函数indirect():

例如：若B10单元格等于15，则=indirect(“B”&row(A10))=B10index()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)-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<=O3,"足够","差"&N3-O3&"个")5.U列公式：IF(ISERROR(DATEDIF(TODAY(),T3,"D")),-DATEDIF(T3,TODAY(),"D"),DATEDIF(TODAY(),T3,"D"))6.AA列公式：IF(Z3="OK","OK",IF(P3="足够","OK","须跟进"))1.B列公式：OFFSET(进销存汇总表!$A$3,MATCH($A5,进销存汇总表!$A$4:$A$1101,0),1)2.I-R列公式：SUMIFS(办公用品领用登记表!$J:$J,办公用品领用登记表!$E:$E,$A5,办公用品领用登记表!$K:$K,产品使用明细1!I$4)3S列公式：SUM(C5:R5)4.T列公式：S5/$S$1745.U列公式：RANK(S5,S$5:S$173)其他应用技巧1.自动变色提醒：右击选中列→开始→条件格式→突出显示/新建规则…2.下拉框选择按钮：选中单元格→数据→数据有效性→允许中选择“序列”→勾选忽略空值→来源填写下拉框内容所在的表和区域3.插入名称函数：例：选中B1单元格→公式→定义名称→名称栏输入“SIDE”→引用位置输入EVALUEATE($A$1)→确定→在B1单元格输入“=SIDE”,回车如何做多级联动下拉菜单1.选中单元格K2→数据→数据有效性→允许中选择“序列”→勾选忽略空值→来源填写：=档案!$A$2:$E$2或=INDIRECT(“档案!A2:AD2”)→确定;注:第2行是部门标题栏2.选中单元格L2→数据→数据有效性→允许中选择“序列”→勾选忽略空值→来源填写=OFFSET(INDIRECT("档案!A2"),1,MATCH($A2,INDIRECT("档案!2:2"),)-1,30)→确定档案表下拉表中人员数据有效性填充（二级下拉框）下拉表中部门数据有效列填充（一级下拉框）其他重要函数挑出本月离职人员名单，形成单独表格：

INDEX(离职人员!B:B,SMALL(IF((离职人员!BJ$1:BJ$30000>=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),4^8),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,"[$-130000]e-m-d"))),SUBSTITUTE(TEXT(A2,"[$-130000]e-m-d"),13,12),TEXT(A2,"[$-130000]e-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