HR高效工作的秘诀：Excel在人力资源管理的应用

Excel在人力资源管理中的应用用好工具，高效工作数据录入单元格的引用数据有效性条件格式认识Excel函数Excel函数的结构常见的几类函数应用实例认识数据透视表数据透视表是什么数据透视表的几个简单应用实例课程大纲1-1单元格的引用

1.相对引用

相对引用指公式中的单元格位置将随着公式单元格的位置而改变2.绝对引用

绝对引用是指公式和函数中的位置是固定不变的.

绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$63.混合引用GO混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.例:行变列不变:$A4列变行不变:A$4

1-2数据的有效性控制单元格输入文本长度GO选中区域---数据---数据有效性---允许---文本长度可防止身份证/银行卡号/手机号码输入多一位或者少一位数序列选中区域---数据---数据有效性---序列---在“来源”输入（A,B,C,D)注意逗号要半角格式禁止输入重复数据

=countif(区域,起始单元格)=1选中区域---数据---数据有效性---允许（自定义)---公式填写=COUNTIF(A:A,A1)=11-2数据的有效性如何取消对于“数据有效性”的设置？选中区域---数据---数据有效性---允许---任何值GO1-3条件格式利用突出的格式，经常能起到提醒的功能如：突出不满勤的人GO

突出年龄大于30的人等如何取消条件格式？认识Excel函数2-1EXCEL函数的结构Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。Excel函数结构:也有一些函数是没有参数的,如NOW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔函数名语法函数结构函数作用例子运行结果1、Left1、LEFT(text,num_chars)其中：Text

是包含要提取字符的文本字符串。Num_chars

指定要由LEFT所提取的字符数。=LEFT(A1,2)从A1最左边向右取两位2、Right2、RIGHT(text,num_chars)其中：Text

是包含要提取字符的文本字符串。Num_chars

指定希望RIGHT提取的字符数。=RIGHT(A1,2)从A1最右边向左取两位3、Mid3、MID(text,start_num,num_chars)其中：Text

是包含要提取字符的文本字符串。Start_num

是文本中要提取的第一个字符的位置。Num_chars

指定希望MID从文本中返回字符的个数。=MID(A1,2,3)从A1左边第2位开始取3位4、&（连接符号）XX&XX=A2&B2将A2和B2内容合并在一起

应用实例从身份证号码提取出生日期

在C2单元格中输入=MID(B2,7,4)&"-"&MID(B2,11,2)&"-"&MID(B2,13,2)

公式解析：

MID(B2,7,4)表示提取B2单元格中从第7位开始连续4位的字符，即出生年；

MID(B2,11,2)表示提取B2单元格中从第11位开始连续2位的字符，即出生月；MID(B2,11,2)表示提取B2单元格中从第13位开始连续2位的字符，即出生日；在公式中加入&可以用来连接两个文本字符串。

函数名语法函数结构函数作用FIND=FIND("龙",C6)在c6中查找龙，结果显示龙在c6中的位数，查不到显示#VALUE!

常用函数之查找类函数函数VOOKUP=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)(以从8月工资表导入考勤到8月考核表为例）GO参数简单说明输入数据类型lookup_value要查找的值数值、引用或文本字符串table_array要查找的区域数据表区域col_index_num返回数据在查找区域的第几列数正整数range_lookup精确匹配/模糊匹配0或1常用函数之查找类函数函数VOOKUP选择区域里最好不要有合并的单元格（先“取消单元格合并”）如果单元格里有空格，有可能导不出来

（提示：函数TRIM（A1)可以去除字符串首部和尾部的空格）注意重复的名字查找区域首列必须含有查找的内容（第一个参数）介绍vookup函数与IFERROR函数组合使用(以从8月工资表导入考勤到8月考核表为例）GO=IFERROR(VLOOKUP(A4,'8月工资'!B:F,5,0),0)

如果VOOKUP函数运行后找不到对应的数据，就会显示为#N/A，IFERROR的作用就是把这些#N/A都变为第2个参数。函数名语法/例子函数结构函数作用Value=value(b1)将b1数值化Numberstring=NumberString(7890,1）运行结果：七千八百九十=NumberString(7890,2)运行结果：柒仟捌佰玖拾=NumberString(7890,3)运行结果：七八九○=numberstring(b1,2)将b1数字转为大写汉字Row=ROW(reference)其中：Reference

为需要得到其行号的单元格或单元格区域。=row(b1)求b1是第几行（值为1)Column=COLUMN(reference)=column(b1)求b1是第几列(值为2)

函数名语法函数结构函数作用len=LEN(text)其中：Text

是要查找其长度的文本。空格将作为字符进行计数。=len(C2)计算C2的字符长度rankRANK(number,ref,order)Number

为需要找到排位的数字。Ref

为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。Order

为一数字，指明排位的方式。=rank(b1,b1:b10,0)=rank(b1,b1:b10,1)计算b1在b1:b10区域中从大到小排第几名；计算b1在b1:b10区域中从小到大排第几名。

应用舱实例在C1输入=A鸟1&街"+雁"&冬B1如何矛根据A1得出B1和C1？在B1输入=L牲EF杀T(分A1失,F隆IN塘D(祖"+倍",专A1稻)-忠1)分析池：思孙路是饱从左枝边开匪始，柏截取路“+”号嫁前的王位数辞。在C1输入=R粮IG英HT答(A滨1,限LE说N(捆A1宫)-薄FI挺ND准("院+"声,A投1)因)分析猜：思悬路是仇截取妹“+”号次后的腹位数父。如何踩根据A1、B1得出C1？函数名语法函数结构函数作用IFIF(logical_test,value_if_true,value_if_false)=if(a2>0,1,0)当a2>0时，显示1，否则显示0IFREEOR第一个参数一般为一个函数，当这个函数运行是个错误值时（#N/A

#DIV/0!

#N/A

#VALUE!#NAME

#NULL!

#NUM!

#REF!），运行结果等于第二个参数=iferror(vookup(A1,A:B,2,0),0)如果VLOOKUP函数运行值是个错误值的话（找不到数据），就等于0，否则，就等于VLOOKUP函数运行的值。ANDAND(logical1,logical2,...)Logical1,logical2,...

表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。=and(a3>0,b3<0)当A3>0,并且b3<0时，显示true，否则显示falseOROR(logical1,logical2,...)Logical1,logical2,...

表示待检测的1到30个条件值，各条件值可为TRUE或FALSE。=or(a4>0,b4<0)当a4>0,或者b4<0时，都显示true，否则显示falseIF函数灯的应绒用实台例区域镇段自壮动算揪出对歪应等摆级=I酿F(贝K4条>=凤90平,"姨A"号,I承F(拴K4外>=舌80特,"汤B"攀,I蓬F(志K4野>=帝70扑,"智C"见,"衫D"氏))爱)嵌套羞了两宴个IF函数分数段等级分数≥90分A80分≤分数＜90分B70分≤分数＜80分C分数＜70分D函数名语法函数结构函数作用SUMIFSUMIF(range,criteria,sum_range)其中：Range

为用于条件判断的单元格区域。Criteria

为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、"32"、">32"或"apples"。Sum_range

是需要求和的实际单元格。=sumif(B:B,H1,E:E)当B列满足了条件（HI），对应的E列的单元格的数值之和GOROW求余数的函数，当第二个参数为2时，常用于判断奇偶数，第二个参数为2时，运行结果为0是偶数，1是奇数=ROW(5，2)求5除以2的余数是多少，本例的运行结果是1应用物实例从身塔份证薪号码称中提档取性搞别（身份兔证倒鸭数第2位若涂为偶爹数为桥女性迈，奇护数为抛男性芹）在C2单元源格中肥输入=I谣F(怨MO估D(进MI胞D(宇B2挺,1凯7,吹1)截,2辞)=酿1,锄"男厦",胞"女较")公式丑分析观：MI幸D(徒B2霞,1趟7,仿1)是从暴身份柜证第17位起概取1位字华符；敌在本误例即恋为数繁字5，MO剖D函数寸就是想取余搂数的轿函数斤，在僵本例榴中就苹是MO尤D(鹊5，2)观,5除以2的余艰数，垫最外疏面是器一个IF函数庭，当丘这个娃余数旅为1时，兆即为松奇数阶，奇看数就池是男内性，拨否则院为女驱性。函数名语法函数结构函数作用1、NOW=now()显示当前的日期+时间2、TODAY=today()显示今天的日期3、WEEKDAY=weekday(b1,2)判断b1为星期几4、DAY=day(b1)显示b1的号数5、MONTH=month(b1)显示b1的月数6、YEAR=year(b1)显示b1的年数函数名以单元格b1为2013-4-5为例的运行结果函数结构函数作用WEEKDAY6=weekday(b1,2)判断b1为星期几DAY5=day(b1)显示b1的号数MONTH4=month(b1)显示b1的月数YEAR2013=year(b1)显示b1的年数常用吵函数示之日倾期类纺函数DA黑TE懒DI谋F函数常用钞于计背算两紧日期锈的时既间差=D饿AT栋ED抓IF撇(较早典的日抵期，那较晚述的日贫期，撒第三去个参齿数）第三息个参怎数：“Y”第-盯--两日地期间末共多搏少年抬。“M樱”符--镰--两日粘期间裙共多筹少个电月。“D宋”蹦--船--两日冈期间榨共多谊少天解。“M妥D”是两岭日期妖中天您数的藏差。俊忽略壳日期矛中的沸月和早年。“Y救M”是两洪日期辱中月勒数的贝差。永忽略柱日期兔中的侦年。“Y陕D”是两庄日期扒中天铺数的德差。手忽略帮日期绍中的锅年。常用攻函数厦之日推期类交函数举例:算两四个日捡期间久差几济年、息几月夹、几列日开始扬时间沫是20恰12美-2凡-2采1结束厉时间配是20浸14攻-2垦-1筹9C2乒=D回AT洲ED徐IF祝(A劫2,准B2添,"茄Y"芝)D2垫=D默AT贿ED迷IF岂(A群2,望B2远,"捉YM民")E2励=D届AT趁ED姨IF济(A舅2,肉B2辅,"果MD绩")F2摆=D喘AT猛ED简IF旗(A析2,据B2志,"典D"钳)G2苍=D惑AT脸ED愿IF妻(A侮2,踪蝶B2敢,"算M"栽)H2眉=D允AT师ED乒IF纠(A压2,储B2并,“略YD艇")I2继=C珍2&饰"年"&滨D2锁&"个月"I2病=D遣AT雁ED小IF底(A惨2,干B2恋,“肠y”旷)&真“年”&绸DA状TE减DI炼F(揪A2拔,B浙2,伞“y错m”采)&曾“个月"常用局函数敌之日候期类政函数应用液实例3从出低生日其得出中年龄在D2单元割格中婚输入=D馅AT库ED低IF食(C打2,庸NO万W(文),窜“y铜”)因为质从身诸份证针提取挽出生铃日的C2比=倡MI宽D(专B2还,7坑,4碍)&作"-完"&孝MI链D(维B2逼,1荡1,逆2)之&"芳-"机&M晋ID烦(B烂2,牺13歪,2番)所以滋若直接厕从身背份证促得出奶年龄D2取=绢DA梦TE璃DI代F(广MI饥D(瞎B2膝,7片,4坐)&呈"-贷"&秘MI升D(躲B2否,1己1,杯2)剩&"脑-"末&M周ID邪(B斗2,罪13托,2不),顿NO裁W(心),筋"y予")应用咏实例培训耕协议拌服务掌期限缝完成附提醒思路华：过庸期了呢可以吃显示干“协就议期之限完者成”没过迅期的迫，显畜示“抱没过拾期”=I京FE矛RR罗OR阀(I特F(怎DA之TE凝DI吧F(备B2蛋,T畜OD夸AY歼()北,"销d"茫)>委=0嚼,论"协议朱期限冲完成",铃)县,"没过疗期")分析将：DA盈TE倦DI湿F(钞B2低,T左OD霸AY善()相,“拦d”仔)可以求出摊今天元到B2的日菊期共腔多少舅天，纽奉如果土“今屡天”在B2之前绝，就努会产严生错洋误值哄，如妙果“射今天诊”在B2之后路，这爹个函冷数就势会算福出B2距离阁“今添天”翅有多少症天。稿当这哑个天剃数≥0时，竿就会拍执行IF函数卧，显示陵“协谜议期套限完疯成”软，当DA选TE歉DI皇F运行溉是一个错臣误值负时，运这时庙候就烛执行IF图ER傍RO乐R函数督，显示陶“没示过期纱”。如果劫我想避到期确前一差个月墓就提慌醒呢剃？思路陆：=I听FE浙RR秃OR弟(I点F(势DA吐TE碌DI顺F(晕TO橡DA此Y(优),积B2锁,"眼D"虎)<哭=3子1,弃"少于31天后净就会壳过期",锅"多于31天")逃,"已过立期")多于今天超过31天的显示“多于31天”多于今天不超过31天的显示“少于31天后就会过期”比今天早的显示“已过期”DA那TE承DI渠F函数能运辟行，逐说明B2晚于农今天能执谢行IF函数当B2晚于草今天叠≤31天显示凭“少神于31天后腿就会那过期蔽”当B2晚于呆今天盘＞31天显示五“多史于31天”不能认运行呀，B2早于印今天犹（培离训期挡限过彩期了包）DA在TE好DI约F函数柱会产抢生错树误值IF函数衔也会勒产生拉错误篇值执行IF督ER死RO扇R函数显示童“已膛过期隙”函数名语法函数结构函数作用1、COUNT=Count(a1:a10)计a1:a10中数字的个数2、COUNTA=Counta(a1:a10)计a1:a10中非空单元格的个数，（0也算）3、COUNTBLANK=Countblank(a1:a10)计a1:a10中空白单元格的个数常用晕函数欠之计杰数类记函数CO沉UN眼TI帽F函数CO堪UN错TI舌F(获ra习ng养e,朱cr瞧it舅er截ia漠)区域累条件施标准是一筐个数慰数的秒函数棚，可霜以用学于数孔出参胆数1区域坐内，岔符合土参数2的条漏件的乓单元滴格个扯数。如：=c乳ou宽nt稼if座(F塔3:所F1昂0,晕“女”)统计F3鸟:F们10中女贪的个裂数。CO辆UN旗TI倍F函数祝的应畅用实仙例应用纳实例在9月流保动表车中找出喘有相框同名饮字的粱人步骤1：打南开9月流绞动表孕，找剩出9月在岸职的妇工作岁表，烟在姓怨名右大边新备建一头列，味插入的函数崇，找米到CO高UN裤TI点F函数得，选距择“幕确定冶”。CO啄UN丝式TI捕F函数酷的应方用实邻例步骤2：点牛击Ra爬ng员e的框融格，塔选中E列，客点击Cr巧it芽er营ia的框即格，约点击E2单元即格，裤点击愤“确犬定”完。步骤3：点效击F2单元萍格，饮把鼠红标移去到F2右下取角，魔鼠标顿箭头修变为完黑色突十字欢的时献候双大击。这个纱的意怀思就驻是在9月在条职的藏人中由只有央一个亏叫严逆邦平参的CO提UN钱TI便F函数责的应呢用实堪例步骤4：选叙中F列--扭-排序渗与筛真序--葛-筛选--请-点击F1左上膛角小业箭头--扬-去掉1前的侨小勾--茅-确定GOCO尊UN祥TI享F函数寺的应套用实领例统计捷一定侦区间向的个屈数如：篇统计9月流锁动表隆“9月在宰职”屡工作例表中模，≥50岁以杂上的称人数菜：=C等OU奋NT乐IF份(F浪:F处,"油>=后50泼")20岁≤深年龄助＜40岁的版人数钉：=C上OU扇NT数IF珍(F魄:F响,"灿>=滚20怀")弊-C笨OU悟NT杏IF煌(F倾:F梦,"灾>=颠40萍")（注锋意：趋如果刑要统话计的速数据提是有侧一个凭绿色脚的左钟角的问，要先美选择答要统边计的规数据宾，点苍击符符淋号，威选择“转放换为肢数字循”，皆不然先是统部计不虚了个躬数的善）GO认识惩数据毕透视坝表认识盼数据物透视私表数据淹透视静表是都一种哄交互昏，交下叉制茫作的德报表像，一辩组具子有多州个字裳段的谈数据跨进行顷多立转体的举分析征汇总待，用糕于对射多种滚来源合的数祝据进犁行汇帮总和愧分析堆，从幕而可肚以快室速合已并和估比较姐大量否数据饶。当却数据保规模吓比较乡丰大时炊，这垂种分牛析的袜意义咽就显米得尤中为突侮出。说人子话一种党快速赛分析米数据担的工落具前提这些戏数据垮使用理