《EXCEL函数应用学习课程》由会员分享，可在线阅读，更多相关《EXCEL函数应用学习课程（43页珍藏版）》请在金锄头文库上搜索。
1、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、 求今天现在的日期 2.now(): 求现在 3.year(): 求年 例：YEAR(“2012-12-31”)=2012 4.month(): 求“月” 例：MONTH(“2012-12-31”)=12 5.day(): 返回天 例：DAY(“2012-12-31”)=31 6. hour() :求小时数 7.minute()：求分钟 8.second(): 求秒 9.date()：求日期 10.datedif() 例： datedif(“2012-1-1”,”2013-1-1”,”Y”)=1 11. TIMEVALUE() 例：12:00和8:00相差 TIMEVALUE(“12：00”)
3、-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
4、(区域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.
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,足够,差 注:第2行是部门标题栏 2.选中单元格L2数据数据有效性允许中选择“序列” 勾选忽略空值来源填写= OFFSE
6、T(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
7、(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，点击菜单栏“编辑”“填充”“序列”，在弹出对话框中查看“类型”项目，在此选择“自
8、动填充”，其余选项保持默认设置。确认操作后，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(A
9、2,$-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,数组公式，拉下去， 挑
10、出不重复的出现次数,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：
11、（数组公式，内存占用大，但是中间没空格） 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.自动显示重复行行号（根据行号可自动把重复数据形
12、成报表） 若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,
13、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行数据, 现在
14、要每行间格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(Sheet
15、2!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!,
