Excel在人力资源管理中的应用(共-43张PPT)

LOGOExcel在人力资源管理中的应用ARROW+0LOGO数据录入§单元格的引用§数据有效性§条件格式认识Excel函数§Excel函数的结构§常见的几类函数§应用实例认识数据透视表§数据透视表是什么§数据透视表的几个简单应用实例课程大纲ARROW+0LOGO1-1单元格的引用1.相对引用相对引用指公式中的单元格位置将随着公式单元格的位置而改变2.绝对引用绝对引用是指公式和函数中的位置是固定不变的.绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$63.混合引用GO混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.例:行变列不变:$A4列变行不变:A$4ARROW+0LOGO1-2数据的有效性§控制单元格输入文本长度GO选中区域---数据---数据有效性---允许---文本长度可防止身份证/银行卡号/手机号码输入多一位或者少一位数v序列选中区域---数据---数据有效性---序列---在“来源”输入（A,B,C,D)注意逗号要半角格式§禁止输入重复数据=countif(区域,起始单元格)=1选中区域---数据---数据有效性---允许（自定义)---公式填写=COUNTIF(A:A,A1)=1ARROW+0LOGO1-2数据的有效性如何取消对于“数据有效性”的设置？选中区域---数据---数据有效性---允许---任何值GOARROW+0LOGO1-3条件格式ARROW+0利用突出的格式，经常能起到提醒的功能如：突出不满勤的人GO突出年龄大于30的人等如何取消条件格式？LOGO认识Excel函数ARROW+0LOGO2-1.EXCEL函数的结构Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。Excel函数结构:也有一些函数是没有参数的,如NOW()左右括号成对出现单一结构嵌套结构参数与参数之间使用半角逗号进行分隔ARROW+0LOGO常用函数之截取、合并类函数ARROW+0LOGO应用实例v从身份证号码提取出生日期在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位的字符，即出生日；在公式中加入&可以用来连接两个文本字符串。ARROW+0LOGO常用函数之查找类函数ARROW+0LOGO函数VOOKUP=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)(以从8月工资表导入考勤到8月考核表为例）GOARROW+0LOGO函数VOOKUPv选择区域里最好不要有合并的单元格（先“取消单元格合并”）v如果单元格里有空格，有可能导不出来（提示：函数TRIM（A1)可以去除字符串首部和尾部的空格）v注意重复的名字v查找区域首列必须含有查找的内容（第一个参数）v介绍vookup函数与IFERROR函数组合使用(以从8月工资表导入考勤到8月考核表为例）GO=IFERROR(VLOOKUP(A4,'8月工资'!B:F,5,0),0)如果VOOKUP函数运行后找不到对应的数据，就会显示为#N/A，IFERROR的作用就是把这些#N/A都变为第2个参数。ARROW+0LOGO常用函数之文本类函数ARROW+0LOGO常用函数之文本类函数ARROW+0LOGO应用实例在C1输入=A1&+&B1v如何根据A1得出B1和C1？在B1输入=LEFT(A1,FIND(+,A1)-1)分析：思路是从左边开始，截取“+”号前的位数。在C1输入=RIGHT(A1,LEN(A1)-FIND(+,A1))分析：思路是截取“+”号后的位数。ARROW+0如何根据A1、B1得出C1？LOGO常用函数之判断类函数ARROW+0LOGOIF函数的应用实例v区域段自动算出对应等级=IF(K4=90,A,IF(K4=80,B,IF(K4=70,C,D)))v嵌套了两个IF函数ARROW+0LOGO常用函数之求和类函数ARROW+0LOGO应用实例v从身份证号码中提取性别（身份证倒数第2位若为偶数为女性，奇数为男性）在C2单元格中输入=IF(MOD(MID(B2,17,1),2)=1,男,女)公式分析：MID(B2,17,1)是从身份证第17位起取1位字符；在本例即为数字5，MOD函数就是取余数的函数，在本例中就是MOD(5，2),5除以2的余数，最外面是一个IF函数，当这个余数为1时，即为奇数，奇数就是男性，否则为女性。ARROW+0LOGO常用函数之日期类函数ARROW+0LOGO常用函数之日期类函数ARROW+0LOGO常用函数之日期类函数DATEDIF函数v常用于计算两日期的时间差=DATEDIF(较早的日期，较晚的日期，第三个参数）第三个参数：v“Y”---两日期间共多少年。v“M”----两日期间共多少个月。v“D”----两日期间共多少天。v“MD”是两日期中天数的差。忽略日期中的月和年。v“YM”是两日期中月数的差。忽略日期中的年。v“YD”是两日期中天数的差。忽略日期中的年。ARROW+0LOGOv举例:算两个日期间差几年、几月、几日开始时间是2012-2-21结束时间是2014-2-19C2=DATEDIF(A2,B2,Y)D2=DATEDIF(A2,B2,YM)E2=DATEDIF(A2,B2,MD)F2=DATEDIF(A2,B2,D)G2=DATEDIF(A2,B2,M)H2=DATEDIF(A2,B2,“YD)I2=C2&年&D2&个月I2=DATEDIF(A2,B2,“y”)&“年”&DATEDIF(A2,B2,“ym”)&“个月ARROW+0LOGO应用实例3v从出生日得出年龄在D2单元格中输入=DATEDIF(C2,NOW(),“y”)因为从身份证提取出生日的C2=MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2)v所以若直接从身份证得出年龄D2=DATEDIF(MID(B2,7,4)&-&MID(B2,11,2)&-&MID(B2,13,2),NOW(),y)ARROW+0LOGO应用实例v培训协议服务期限完成提醒思路：过期了可以显示“协议期限完成”没过期的，显示“没过期”=IFERROR(IF(DATEDIF(B2,TODAY(),d)=0,协议期限完成,),没过期)分析：DATEDIF(B2,TODAY(),“d”)可以求出今天到B2的日期共多少天，如果“今天”在B2之前，就会产生错误值，如果“今天”在B2之后，这个函数就会算出B2距离“今天”有多少天。当这个天数≥0时，就会执行IF函数，显示“协议期限完成”，当DATEDIF运行是一个错误值时，这时候就执行IFERROR函数，显示“没过期”。ARROW+0如果我想到期前一个月就提醒呢？LOGO思路：=IFERROR(IF(DATEDIF(TODAY(),B2,D)=31,少于31天后就会过期,多于31天),已过期)ARROW+0DATEDIF函数能运行，说明B2晚于今天能执行IF函数当B2晚于今天≤31天显示“少于31天后就会过期”当B2晚于今天＞31天显示“多于31天”不能运行，B2早于今天（培训期限过期了）DATEDIF函数会产生错误值IF函数也会产生错误值执行IFERROR函数显示“已过期”LOGO常用函数之计数类函数ARROW+0LOGOCOUNTIF函数COUNTIF(range,criteria)区域条件标准是一个数数的函数，可以用于数出参数1区域内，符合参数2的条件的单元格个数。如：=countif(F3:F10,“女”)统计F3:F10中女的个数。ARROW+0LOGOCOUNTIF函数的应用实例应用实例v在9月流动表中找出有相同名字的人步骤1：打开9月流动表，找出9月在职的工作表，在姓名右边新建一列，插入函数，找到COUNTIF函数，选择“确定”。ARROW+0LOGOCOUNTIF函数的应用实例步骤2：点击Range的框格，选中E列，点击Criteria的框格，点击E2单元格，点击“确定”。步骤3：点击F2单元格，把鼠标移到F2右下角，鼠标箭头变为黑色十字的时候双击。ARROW+0这个的意思就是在9月在职的人中只有一个叫严邦平的LOGOCOUNTIF函数的应用实例步骤4：选中F列---排序与筛序---筛选---点击F1左上角小箭头---去掉1前的小勾---确定GOARROW+0LOGOCOUNTIF函数的应用实例v统计一定区间的个数如：统计9月流动表“9月在职”工作表中，≥50岁以上的人数：=COUNTIF(F:F,=50)20岁≤年龄＜40岁的人数：=COUNTIF(F:F,=20)-COUNTIF(F:F,=40)（注意：如果要统计的数据是有一个绿色的左角的，要先选择要统计的数据，点击符号，选择“转换为数字”，不然是统计不了个数的）GOARROW+0LOGO认识数据透视表ARROW+0LOGO认识数据透视表数据透视表是一种交互，交叉制作的报表，一组具有多个字段的数据进行多立体的分析汇总，用于对多种来源的数据进行汇总和分析，从而可以快速合并和比较大量数据。当数据规模比较大时，这种分析的意义就显得尤为突出。ARROW+0说人话一种快速分析数据的工具LOGO前提这些数据使用数段表进行管理ARROW+0LOGO统计9月入职的招聘来源分布统计离职原因分布个数统计各部门平均工资数据透视表可以快速解决这些事情ARROW+0统计各部门学历分布情况认识数据透视表LOGOv统计9月入职的招聘来源分布步骤1：打开工作表---选择数据来源区域（A1：i79）---插入---数据透视表---确定统计9月入职的招聘来源分布ARROW+0LOGOARROW+0步骤2：把招聘来源拖到下面的“行标签”框，把序号拖到下面的“数值”框，就能快速生成下图。LOGO统计离职原因分布个数同理，可根据前面介绍的步骤在辞工名单的表格中快速做出离职原因分布的表格GO拖“离职原因”到“行标签”框，拖“序号”到“数值”框注意：要留意“数值”框，是求和，还是计数，还是其他，左图的默认是“求和”，而我们是想“计数”。ARROW+0通过双击B3，出来“值字段对话框”，选择“计数”LOGOv统计出不同部门工资总和、平均工资行标签---部门数值---求和项：工资，平均值项(值字段设置可找到）：工资ARROW+0LOGO统计各部门学历分布情况行标签---部门，列标签---最高学历数值---计数项：最高学历ARROW+0想想：如果在列标签先拖入性别，再拖入最高学历，数据透视表会怎样LOGOThankYou!ARROW+0•激励学生学习的名言格言•220、每一个成功者都有一个开始。勇于开始，才能找到成功的路。•221、世界会向那些有目标和远见的人让路（冯两努——香港著名推销商）•222、绊脚石乃是进身之阶。•223、销售世界上第一号的产品——不是汽车，而是自己。在你成功地把自己推销给别人之前，你必须百分之百的把自己推销给自己。•224、即使爬到最高的山上，一次也只能脚踏实地地迈一步。•225、积极思考造成积极人生，消极思考造成消极人生。•226、人之所以有一张嘴，而有两只耳朵，原因是听的要比说的多一倍。•227、别想一下造出大海，必须先由小河川开始。•228、有事者，事竟成；破釜沉舟，百二秦关终归楚；苦心人，天不负；卧薪尝胆，三千越甲可吞吴。•229、以诚感人者，人亦诚而应。•230、积极的人在每一次忧患中都看到一个机会，而消极的人则在每个机会都看到某种忧患。•231、出门走好路，出口说好话，出手做好事。•232、旁观者的姓名永远爬不到比赛的计分板上。•233、怠惰是贫穷的制造厂。•234、莫找借口失败，只找理由成功。（