职业规划_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 1 1 2数据的有效性 如何取消对于 数据有效性 的设置 选中区域 数据 数据有效性 允许 任何值GO 1 3条件格式 利用突出的格式 经常能起到提醒的功能如 突出不满勤的人GO突出年龄大于30的人等 如何取消条件格式 认识Excel函数 2 1EXCEL函数的结构 Excel函数即是预先定义 执行计算 分析等处理数据任务的特殊公式 Excel函数结构 也有一些函数是没有参数的 如NOW 左右括号成对出现 单一结构 嵌套结构 参数与参数之间使用半角逗号进行分隔 应用实例从身份证号码提取出生日期在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位的字符 即出生日 在公式中加入 可以用来连接两个文本字符串 常用函数之查找类函数 函数VOOKUP VLOOKUP lookup value table array col index num range lookup 以从8月工资表导入考勤到8月考核表为例 GO 常用函数之查找类函数 函数VOOKUP选择区域里最好不要有合并的单元格 先 取消单元格合并 如果单元格里有空格 有可能导不出来 提示 函数TRIM A1 可以去除字符串首部和尾部的空格 注意重复的名字查找区域首列必须含有查找的内容 第一个参数 介绍vookup函数与IFERROR函数组合使用 以从8月工资表导入考勤到8月考核表为例 GO IFERROR VLOOKUP A4 8月工资 B F 5 0 0 如果VOOKUP函数运行后找不到对应的数据 就会显示为 N A IFERROR的作用就是把这些 N A都变为第2个参数 应用实例在C1输入 A1 B1如何根据A1得出B1和C1 在B1输入 LEFT A1 FIND A1 1 分析 思路是从左边开始 截取 号前的位数 在C1输入 RIGHT A1 LEN A1 FIND A1 分析 思路是截取 号后的位数 如何根据A1 B1得出C1 IF函数的应用实例 区域段自动算出对应等级 IF K4 90 A IF K4 80 B IF K4 70 C D 嵌套了两个IF函数 应用实例从身份证号码中提取性别 身份证倒数第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时 即为奇数 奇数就是男性 否则为女性 常用函数之日期类函数 DATEDIF函数常用于计算两日期的时间差 DATEDIF 较早的日期 较晚的日期 第三个参数 第三个参数 Y 两日期间共多少年 M 两日期间共多少个月 D 两日期间共多少天 MD 是两日期中天数的差 忽略日期中的月和年 YM 是两日期中月数的差 忽略日期中的年 YD 是两日期中天数的差 忽略日期中的年 常用函数之日期类函数 举例 算两个日期间差几年 几月 几日开始时间是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 个月 常用函数之日期类函数 应用实例3从出生日得出年龄在D2单元格中输入 DATEDIF C2 NOW y 因为从身份证提取出生日的C2 MID B2 7 4 MID B2 11 2 MID B2 13 2 所以若直接从身份证得出年龄D2 DATEDIF MID B2 7 4 MID B2 11 2 MID B2 13 2 NOW y 应用实例培训协议服务期限完成提醒思路 过期了可以显示 协议期限完成 没过期的 显示 没过期 IFERROR IF DATEDIF B2 TODAY d 0 协议期限完成 没过期 分析 DATEDIF B2 TODAY d 可以求出今天到B2的日期共多少天 如果 今天 在B2之前 就会产生错误值 如果 今天 在B2之后 这个函数就会算出B2距离 今天 有多少天 当这个天数 0时 就会执行IF函数 显示 协议期限完成 当DATEDIF运行是一个错误值时 这时候就执行IFERROR函数 显示 没过期 如果我想到期前一个月就提醒呢 思路 IFERROR IF DATEDIF TODAY B2 D 31 少于31天后就会过期 多于31天 已过期 DATEDIF函数 能运行 说明B2晚于今天 能执行IF函数 当B2晚于今天 31天 显示 少于31天后就会过期 当B2晚于今天 31天 显示 多于31天 不能运行 B2早于今天 培训期限过期了 DATEDIF函数会产生错误值 IF函数也会产生错误值 执行IFERROR函数 显示 已过期 常用函数之计数类函数 COUNTIF函数COUNTIF range criteria 区域条件标准是一个数数的函数 可以用于数出参数1区域内 符合参数2的条件的单元格个数 如 countif F3 F10 女 统计F3 F10中女的个数 COUNTIF函数的应用实例 应用实例在9月流动表中找出有相同名字的人步骤1 打开9月流动表 找出9月在职的工作表 在姓名右边新建一列 插入函数 找到COUNTIF函数 选择 确定 COUNTIF函数的应用实例 步骤2 点击Range的框格 选中E列 点击Criteria的框格 点击E2单元格 点击 确定 步骤3 点击F2单元格 把鼠标移到F2右下角 鼠标箭头变为黑色十字的时候双击 这个的意思就是在9月在职的人中只有一个叫严邦平的 COUNTIF函数的应用实例 步骤4 选中F列 排序与筛序 筛选 点击F1左上角小箭头 去掉1前的小勾 确定GO COUNTIF函数的应用实例 统计一定区间的个数如 统计9月流动表 9月在职 工作表中 50岁以上的人数 COUNTIF F F 50 20岁 年龄 40岁的人数 COUNTIF F F 20 COUNTIF F F 40 注意 如果要统计的数据是有一个绿色的左角的 要先选择要统计的数据 点击符号 选择 转换为数字 不然是统计不了个数的 GO 认识数据透视表 认识数据透视表 数据透视表是一种交互 交叉制作的报表 一组具有多个字段的数据进行多立体的分析汇总 用于对多种来源的数据进行汇总和分析 从而可以快速合并和比较大量数据 当数据规模比较大时 这种分析的意义就显得尤为突出 说人话 一种快速分析数据的工具 前提这些数据使用数段表进行管理 统计9月入职的招聘来源分布 统计离职原因分布个数 统计各部门平均工资 数据透视表可以快速解决这些事情 统计各部门学历分布情况 认识数据透视表 统计9月入职的招聘来源分布步骤1 打开工作表 选择数据来源区域 A1 i79 插入 数据透视表 确定统计9月入职的招聘来源分布 步骤2 把招聘来源拖到下面的 行标签 框 把序号拖到下面的 数值 框 就能快速生成下图 统计离职原因分布个数 同理 可根据前面介绍的步骤在辞工名单的表格中快速做出离职原因分布的表格GO拖 离职原因 到 行标签 框 拖 序号 到 数值 框注意 要留意 数值 框 是