一版掌握20~30个,能满足日常工作需要。
要把HR工作做好,要掌握大约50个。
5个必须掌握的函数:
IF/COUNTIF/COUNTIF/DATEDIF/VLOOKUP
一、IF函数
最多嵌套64个IF。
例:C35 单元格存储数字10。
IF(C35<>10,"学习”,) 返回0
IF(C35<>10,"学习“) 返回FALSE
IF(C35<>10,"学习“,”“)返回空(空格)
多条件判断的实际应用
AND函数 OR函数
AND(条件1,条件2……) OR(条件1,条件2……)
IF(AND(D1="男”,D2<=35), "候选“,“”)
多层IF嵌套
IF(D1="总监”,2000,IF(D1="经理”,1500,IF(D1="主管”,800,0)))
模板:
IF(<节点1,结果1,IF(<节点2,结果2,IF(节点3,结果3,结果4)))
错误示例: 60
AND(60
二、SUMIF函数
sumif(range,criteria,[sum_range])
第三个参数如省略,则是对range参数中指定单元格求和。
例:
sumif(D1:D10,"生产部",F1:F10)
SUMIF(D1:D10,L1,F1:F10) (L1="生产部")(条件可以直接输入,可以引用)
SUMIF(D1:D10,“李*”,F1:F10)
*任意字符
?单个字符
~指定字符
SUMIF(D1:D10,L2&"*",F1:F10) (L2="李", &:文本连接符)
SUMIF(F1:F10,">2000",F1:F10)
SUMIF(F1:F10,">2000") 条件区域与求和区域一致,可省略
SUMIF(F1:F10,">"&L10) L10="2000" 引用单元格拼凑查询条件,运算符要用双引号引起来
SUMIF(G1:G10,500) 数字等于500的求和,不用引号
SUMIF(D1:D10,"<>生产部",F1:F10)
SUMIF(D1:D10,"<>"&L3,F1:F10)
SUMIF($D$1:$D$10,D1,$F$1:$F$10) 切换相对引用和绝对引用,按F4
忽略错误求和(#DIV/0, #N/A ,#REF!等)
=SUMIF(D1:D10,“<9e307") 9e307代表相当大的数
9月5日之前数据求和:
=SUMIF(I1:I10,”<2016-9-5“,J1:J10)
=SUMIF(I1:I10,"<"&N8,J1:J10) ( N8=”2016-9-5“)
三、COUNTIF函数
countif(range,criteria)
COUNTIF(D1:D10,"生产部")
COUNTIF(D1:D10,N1) (N1="生产部)
COUNTIF(D2:D10,"李*")
COUNTIF(D2:D10,N2&"*") (N2="李”)
COUNTIF(F1:F10,">2000")
COUNTIF(F1:F10, ">"&L2) (L2="2000")
COUNTIF(F1:F10,500)
COUNTIF(F1:F10,L3) "L3=500"
COUNTIF(D1:D10,"<>生产部")
COUNTIF(D1:D10,"<>"&L3) (L3="生产部")
COUNTIF(D1:D10,D12) (D12="生产部",可下拉复制公式)
四、DATEDIF 函数
隐藏函数,不会提示
DATEDIF(start_date,end_date,unit)
start_date必须
unit 6种格式:
"Y"--整年数
“M"--整月数
”D"--天数
“MD"--天数之差,忽略月份和年份
”YM“--月份之差,忽略天数和年份
”YD“--日期部分之差,忽略年份。
注意:结束日期大于等于开始日期
2015-8-1 2016-8-7
=DATEDIF(C35,D35,"Y") 1
=DATEDIF(C35,D35,"M") 12
=DATEDIF(C35,D35,"D") 372
=DATEDIF(C35,D35,"MD") 6
=DATEDIF(C35,D35,"YD") 6
=DATEDIF(C35,D35,"YM") 0
2015-8-1 2016-8-1
=DATEDIF(C35,D35,"Y") 0
=DATEDIF(C35,D35,"M") 11
=DATEDIF(C35,D35,"D") 360
=DATEDIF(C35,D35,"MD") 25
=DATEDIF(C35,D35,"YD") 360
=DATEDIF(C35,D35,"YM") 11
参数为日起类型数据的,用英文双引号引用,参数为数字的,不需要双引号
=DATEDIF("2013-8-7","2016-8-1","Y")
=DATEDIF(41493,42583,"Y")
五、VLOOKUP函数
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
range_lookup 可选,1(true)近似匹配 或 0(false)精确匹配。
实现近似匹配时,必须对数据区域的第一列进行升序排列,返回精确匹配值,如无精确匹配值则返回小于查找值的最大值。
VL +TAB可快速调出函数
=VLOOKUP("张飞",D1:G10,4,0)
=VLOOKUP(N1,$D$1:$D$10,4,0) (N1="张飞")
=VLOOKUP("张*”,$D$1:$D$10,4,0)
=VLOOKUP(N2&"*",$D$1:$D$10,4,0) (N2="张")
反向查找:
如果查找的列比备查的列靠前,不能使用VL( , ,-1,0)
SHIFT+鼠标左键 可拖动到后面
如果位置固定不能调整,有一个固定的用法:
=VLOOKUP(I1,IF({1,0},$E$1:$E$10,$D$1:$D$10),2,0) 按CTRL+ SHIFT+ENTER
建议采用互换方法,简便。
多条件查找 在数据区最前方增加辅助列
=D1&D2
=VLOOKUP(D1&D2, 数据,3,0)
如果不增加辅助列,用反向查找
=VLOOKUP(D1&D2,IF({1,0},$D$1&$D$4,$F$1:$F$4),2,0) 按CTRL+SHIFT+ENTER
建议增加辅助列,方便。
一对多查找
辅助列 = F53&COUNTIF($F$53:F53,F53)
查找所有刘备
=VLOOKUP(F53&ROW(A1),$D$53:$G$58,4,0)
ROW(1,1)
近似匹配
0 很差
60一般
80良好
90优秀
必须对数据区域第一列进行升序排列
=VLOOKUP(G63,$D$63:$G$68,2,1)
赞同
收藏
评论
分享