kl800.com省心范文网

Excel_2010在人力资源管理中的应用_图文





Excel 2010在人力资源管理 中的应用

高手指引
在人力资源管理中,经常会用到表格的设计与建立,系统的建立和信息的筛选等,利用Excel 2010可以让这些工作事半功倍。

重点导读
了解人力资源流程表的创建方法 了解员工基本资料表的创建方法 了解人力资源管理中其他表格的创建方法

Excel 2010 精 选 案 例 应 用 电 子 书

2.1 设计人力资源招聘流程表
招聘作为一个公司的重要管理任务,在人力资源管理中占据着极其重要的位置,与其他人 力资源管理职能也有着密切的关系。本节将主要介绍人力资源招聘流程表的制作。

2.1.1

插入艺术字

为了美化流程表,表头信息可以使用艺术字,在工作表中插入艺术字的操作步骤如下。

? 在Excel 2010中,新建一个空白工作簿。

,在弹出的【艺术字 ? 在【插入】选项卡中,单击【文本】选项组中的【艺术字】按钮 样式】列表中选择第四行第二列的样式,工作表中出现艺术字体的“请在此放置您的文字”。

? 将光标定位在艺术字框中,输入“人力资源招聘流程表”。

? 选中文本“人力资源招聘流程表”,在切换到【开始】选项卡,在【字体】选项组中的【字 号】文本框内输入“36”。

58

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 将鼠标指针放置在艺术字的边框上,当指针变为 格区域A1:G4中。

形状时,按住鼠标左键,将其拖曳至单元

2.1.2

制作流程图

制作流程图的具体操作步骤如下。 ,在弹 ? 单击单元格A5,在【插入】选项卡中,单击【插图】选项组中的【形状】按钮 出的形状列表中选择【流程图】组中的【过程】选项。当光标变为十字形状时,在适当位置拖曳 鼠标,即可创建一个【过程】图形。

? 在图形上单击鼠标右键,在弹出的快捷菜单中选择【编辑文字】选项。

“提出用人申请” 并设置文本的字体为 , “宋体” 字号为 、 “11” , ? 在图形中光标闪烁处输入文字 并设置为居中和水平居中。

59

Excel 2010 精 选 案 例 应 用 电 子 书

提示
可以调整图形的大小,具体方法是将鼠标指 针移至图形周围的小圆圈控制点上,当指针 变为双向箭头时,拖动鼠标调整即可。

, ? 选中图形,在【格式】选项卡中,单击【形状样式】选项组中的【形状效果】按钮 在弹出的下拉列表中选择【发光】选项,然后在弹出的子菜单中选择第二行第一列的样式,可以 看到图形效果相应地发生改变。

,在弹出的形状列表中 ? 在【插入】选项卡中,单击【插图】选项组中的【形状】按钮 选择【线条】组中的【箭头】,当指针变为十字形状时,在【过程】图形下方拖曳鼠标,即可绘 制一个箭头。

提示
如果对箭头的样式不满意,可以在【格式】 选项卡,单击【形状样式】选项组中的【形 状轮廓】和【形状效果】按扭为箭头设置粗 细、线型、阴影或三维效果等。

? 参照步骤?~?设置其他【过程】图形。
60

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

提示
设置其他图形时,可以选中创建好的图形, 按住【Ctrl】键拖曳鼠标,将其复制到合适的 位置。

? 选中【下达正式录取通知】图形,在【格式】选项卡,单击【插入形状】选项组中的【编辑
形状】按钮 ,在弹出的下拉列表中选择【更改形状】选项,在弹出的子菜单中选择【流程图】 组中的【终止】选项,“下达正式录取通知”的图形发生改变,然后适当调整该图形的大小。

提示
“下达正式录取通知”是招聘流程的最后一 步,因此用【终止】图形来代替【过程】图 形。

? 选择最左侧的的形状和箭头,在【格式】选项卡【排列】选项组中单击【对齐】按钮,在下 拉列表中选择【水平居中】,依次调整其他的形状后,结果如图所示。

61

Excel 2010 精 选 案 例 应 用 电 子 书

2.1.3

保存流程表

保存表格的操作步骤如下。

? 一般在招聘流程表中还可以链接一些招聘过程中需要的表格。选中相应流程图形,单击鼠标 右键,在弹出的快捷菜单中选择【超链接】选项,在弹出的【插入超链接】对话框中,选择要链 接的文件的路径,单击【确定】按钮。

? 选择【文件】选项卡,在弹出的下拉列表中选择【保存】选项,在弹出的【另存为】对话框 中的【文件名】文本框内输入“人力资源招聘流程表.xlsx”,单击【保存】按钮。

? 至此人力资源招聘流程表全部制作完成。

提示
上面介绍的操作步骤读者可以当作范例,在 工作中可以根据实际需要来设计和完善此表 格。

62

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

2.2 设计员工基本资料表
每个企业都会建立一个员工基本资料表,以便于人事管理。本节主要介绍如何设计一个建 立员工基本资料表。

2.2.1

建立员工基本资料表

下面主要讲解如何建立员工基本资料表以及正确输入员工信息。

第1步:建立表格
? 打开Excel 2010新建一个工作簿,选择Sheet1工作表,依次在单元格区域A1:H1中,分别输入 如下信息。

? 选中A~H列,在【开始】选项卡中,单击【对齐方式】选项组中的【居中】按钮
文件中复制以上数据)。

,然后

依次输入员工信息(在输入员工信息时,可以从随书光盘中的“素材\ch18\员工基本资料.xlsx”

第2步:设置编号和工资
? 在单元格A2中输入“1”,按【Enter】键确认。
63

Excel 2010 精 选 案 例 应 用 电 子 书

? 选择单元格A2,单击鼠标右键,在弹出的快捷菜单中选择【设置单元格格式】选项。在弹出 的【设置单元格格式】对话框中,选择【数字】标签。在【分类】列表框中选择【自定义】选项, 在【类型】文本框中输入“000”。

? 单击【确定】按钮,返回到工作表,即可看到刚才输的“1”已变为“001”。把鼠标指针放 在单元格右下角的填充柄上,当指针变为+形状时向下拖曳快速填充数据。

? 当拖曳至最后一个单元格时,下方出现自动填充选项按钮 ,将鼠标指针放在该按钮上,会 出现一个下拉箭头,单击该箭头,在弹出的菜单中选择【填充序列】单选按钮,员工编号数据将 按顺序自动填充。
64

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 员工编号填充效果见下图。用自动填充的方法要比键盘输入快捷方便得多。

? 依次输入员工的基本工资信息。

? 选单元格区域择H2:H23并右击,在弹出的快捷菜单中选择【设置单元格格式】选项。在弹出 的【设置单元格格式】对话框中,选择【数字】标签,在【分类】列表框中选择【货币】选项, 【小数位数】文本框中输入“2”,在【货币类型】下拉列表框中选择“¥”。

65

Excel 2010 精 选 案 例 应 用 电 子 书

? 单击【确定】按钮,得到需要的格式。

? 选择【文件】选项卡,在弹出的下拉菜单中选择【保存】选项,在弹出的【另存为】对话框 中的【文件名】文本框内输入“员工基本资料表.xlsx”, 单击【保存】按钮。

2.2.2

排序和筛选员工数据

员工基本资料表制作已完成,但由于输入信息时没有分门别类,所以表格中的数据非常杂 乱,查找比较困难,下面就来讲解利用排序和筛选功能解决这个问题的操作步骤。

? 打开上一小节中制作的“员工基本资料表.xlsx”文件,单击数据区域内的任一单元格,在【数

66

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

据】选项卡中,单击【排序和筛选】选项组中的【排序】按钮



? 弹出【排序】对话框,选择【主要关键字】下拉列表框中的【人员级别】选项,再选择【排 序依据】后下拉列表框中的【数值】选项,最后选择【次序】下拉列表框中的【升序】选项,然 后单击【复制条件】按钮。

? 则【排序】对话框中显示出【次要关键字】的属性,在【次要关键字】下拉列表框中选择【性 别】选项,在【排序依据】下拉列表框中选择【数值】选项,在【次序】下拉列表框选择【降序】 选项。

? 单击【确定】按钮,则员工资料表中相同级别的信息显示在一起,而同一级别内又按照女员 工在前男员工在后的顺序排列显示。

67

Excel 2010 精 选 案 例 应 用 电 子 书

? 下面进行数据的筛选。在【数据】选项卡中,单击【排序和筛选】选项组中的【筛选】按钮
则在标题行每列的右侧出现一个下拉箭头。



? 单击【所在部门】列右侧的下拉箭头,在弹出的下拉列表中的【文本筛选】列表框中仅选择 【市场部】复选框,撤选其他复选框。

? 单击【确定】按钮,则筛选出“市场部”所有员工的基本资料。

提示
单击【所在部门】列右侧的下拉箭头,在弹 出下拉列表中选择 【全选】 复选框, 单击 【确 定】按钮,即可显示出所有员工的信息。

? 做好的筛选表要保存起来。选择【文件】选项卡,在弹出的下拉菜单中选择【另存为】选项, 在弹出的【另存为】对话框中的【文件名】文本框内输入“员工基本资料筛选表.xlsx”,单击【保 存】按钮。
68

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

2.3 设计员工年假表
有效的管理才能培养出优秀的员工,而优秀的员工才能支撑起一个成功的企业,因此对于 员工的管理非常重要。本节主要讲解建立员工年假表的方法。

2.3.1

建立表格

新建工作表格的操作步骤如下。

? 在Excel 2010中,新建一个空白工作簿,选择Sheet1工作表,复制随书光盘中的“素材\ch18\ 员工年假表.xlsx”文件中的员工资料数据。在Sheet1工作表标签上单击右键,在弹出的快捷菜单 中选择【重命名】选项,将工作表命名为“年假表”。

? 在单元格I1和J1中,分别输入“工龄”和“年假天数”。

69

Excel 2010 精 选 案 例 应 用 电 子 书

? 选择Sheet2工作表,复制随书光盘中的“素材\ch18\年假规则.xlsx”文件中的数据,在Sheet2 工作表标签上右击,在弹出的快捷菜单中选择【重命名】选项,将工作表命名为“年假规则”。

提示
年假规则表是根据企业员工年假标准制作 的,年假规范为:任职不满一年的员工,不 享受年假; 任职满一年的员工, 年假为 8 天; 任职一年以上的员工,工龄每增加一年,年 假增加 1 天;任职十年以上的员工,年假均 为 21 天。

2.3.2

计算工龄

工作表建立完成后下面来计算员工工龄。

? 单 击 “ 年 假 表 ” 工 作 表 中 的 单 元 格 I2 , 在 编 辑 栏 中 直 接 输 入 公 式 “ =YEAR(NOW()) - YEAR(G2)”。

? 按【Enter】键确认,发现计算结果不正确。这是因为单元格格式不正确。

70

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 选择单元格H2,单击鼠标右键,在弹出的快捷菜单中选择【设置单元格格式】选项,在弹出 的【设置单元格格式】对话框中,选择【数字】标签,在【分类】列表框中选择【数值】,设置 【小数位数】为“0”。

? 单击【确定】按钮,即可看到单元格H2显示的计算结果为“6”。

? 将鼠标指针放在单元格I2右下角的填充柄上,当指针变为 形状时拖动,将公式复制到单元格 I6中,显示计算结果为“6”,由于现在时间为“2010年9月7日”,所以“蔡峰”的实际工龄不 满6年,所以需要增加IF函数来更正。

71

Excel 2010 精 选 案 例 应 用 电 子 书

? 在 单 元 格 I2 中 重 输 入 公 式 “ =YEAR(NOW()) - YEAR(G2) - IF(DATE(YEAR(G2), MONTH(NOW()),DAY(NOW())) <=G2,1,0)”。

? 按【Enter】键确认.

? 将鼠标指针放在单元格I2右下角的填充柄上,当指针变为 形状时拖动,将公式复制到该列的 其他单元格中,这时显示“蔡峰”的工龄为5年。

提示
公 式 “ =YEAR(NOW()) - YEAR(G2) - IF(DATE(YEAR(G2),MONTH(NOW()),DAY(NOW()))<=G2,1,0)”中, DATE(YEAR (G2),MONTH(NOW()), DAY(NOW())) 表示返回单元格 G2 中的年份,与现在的月份和日期组成时间(2004 年 6 月 7 日) ; DATE(YEAR(F2),MONTH(NOW()),DAY(NOW()))<=G2 表示返回的这 个 时 间 小 于 或 等 于 G2 表 示 的 时 间 ( 2004 年 2 月 2 日 ) ; IF(DATE(YEAR(G2),MONTH(NOW()),DAY(NOW()))<=G2,1,0) 表 示 如果返回的这个时间小于或等于 G2 表示的时间,则输出数值 1,否 则输出 0。 “

72

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

2.3.3

计算年假天数

计算完工龄后下面来计算年假天数,具体的操作步骤如下。

? 选择“年假表”工作表,选择单元格J2,切换到【公式】选项卡,单击【插入函数】按钮



? 弹出【插入函数】对话框,在【或选择类别】下拉列表框中选择【查找与引用】选项,在【选 择函数】列表框中选择【VLOOKUP】函数,单击【确定】按钮。

? 弹出【函数参数】对话框,在【Lookup_value f】文本框中输入“I2”,在【Col_index_num】 文本框中输入“2”,在【Range_lookup】文本框中输入“1”。

? 单击【Table_array】文本框右侧的【折叠】按钮


73

Excel 2010 精 选 案 例 应 用 电 子 书

? 此时【函数参数】对话框折叠变小,切换到“年假规则”工作表中,选择单元格区域A3:B15。

? 这时单元格区域为相对引用,可以按下键盘上的【F4】键,将单元格区域相对引用变为绝对 引用, 这样无论引用该函数的单元格区域如何变化, 变为绝对引用的单元格区域地址都不会变化。

? 单击折叠对话框右侧的【展开】按钮

,返回到【函数参数】对话框。

提示
参数“年假规则!$A$3:$B$15”表示年假规则 工作表中的单元格区域 A3:B15。

? 单击【确定】按钮,则计算出员工的年假天数。
74

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 将鼠标指针放在单元格I2右下角的填充柄上,当指针变为 形状时拖动,将公式复制到该列的 其他单元格中,则所有员工的年假天数都可以计算出来。

? 选择【文件】选项卡,在弹出的下拉菜单中选择【保存】选项,将工作簿命名为“员工年假 表.xlsx”,单击【保存】按钮即可。

2.4 设计出勤管理表
为了便于进行人员出勤情况的统计和管理,公司需要建立“加班记录”和“缺勤记录”两 个表格。下面首先讲解如何建立加班记录表。

2.4.1

设计加班记录表

加班记录表的具体设计步骤如下。

? 打开随书光盘中的“素材\ch18\出勤管理.xlsx”工作簿,包含3个工作表,分别是“年假表”、 “加班记录”和“缺勤记录”。

75

Excel 2010 精 选 案 例 应 用 电 子 书

? 选择“加班记录”工作表。

? 设置自动显示员工姓名。选择单元格C2,在编辑栏中直接输入公式“=VLOOKUP(B2,年假 表!$A$2:$B$23,2,1)”,按【Enter】键确认,员工的姓名会自动显示出来。

? 将鼠标指针放在单元格C2右下角的填充柄上,当指针变为 形状时拖动,将公式复制到该列 的其他单元格中。

? 设置自动显示员工所属部门。单击单元格D2,在编辑栏中直接输入公式=VLOOKUP(B2,年假 表!$A$2:$D$23,4,1)”,按【Enter】键确认,员工的所属部门自动显示出来。

76

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

提示
公式“=VLOOKUP(B2,年假表!$A$2: $D$23,4,1)”中第 3 个参数设置为“4”表示取满足条件的记录在“年假 表!$A$2: $D$23”区域中第 4 列的值。

? 将鼠标指针放在单元格D2右下角的填充柄上,当指针变为 形状时拖动,将公式复制到该列 的其他单元格中。

? 下面计算加班费。在单元格G2中重输入公式“=IF(HOUR(F2-E2)<=2,70,140)”,按【Enter】 键确认。

提示
企业员工的加班费标准为:加班时间在两小 时以内,加班费为 70 元;超过两个小时,加 班费为 140 元。

? 使用填充方法将公式复制到该列的其他单元格中,则可计算出所有加班员工的加班费。

77

Excel 2010 精 选 案 例 应 用 电 子 书

2.4.2

设计缺勤记录表

制作缺勤记录表之前,先了解一下公司的缺勤管理制度,有以下几种情况:请病假一天只 计半天的工资;请事假要按天扣除工资;迟到20分钟以内扣50元,超过20分钟扣100元;每月 按实际天数计算,如8月份31天,则每天的工资就是基本工资除以31天。

? 选择 “缺勤记录”工作表。

? 计算病假扣款。单击单元格H2,在编辑栏中直接输入公式“

提示
公式“=ROUND(G2/31*D2/2,0)”表示用每 天的工资(G2/31)乘以病假天数的一半 (D2/2) ,然后四舍五入取整数,其中第 2 个参数为“0”就表示结果不保留小数。由于 公司规定病假一天按半天扣款,所以要用每 天的工资乘以病假天数的一半。

? 将鼠标指针放在单元格H2右下角的填充柄上,当指针变为 形状时拖动,将公式复制到该列 的其他单元格中,则所有员工的病假扣款就可以计算出来。

? 按 照 同 样 的 方 法 计 算 事 假 扣 款 。 单 击 单 元 格 I2 , 在 编 辑 栏 中 直 接 输 入 公 式 “=ROUND(G2/31*E2,0)”,然后进行公式填充,计算出所有员工的事假扣款。
78

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

提示
公式“=ROUND(G2/31*E2,0)”表示用每天的工资(G2/31)乘以事 假天数(E2) ,结果取整数。由于公司规定事假不计薪金,所以要用 每天的工资乘以事假天数。

? 按 照 同 样 的 方 法 计 算 迟 到 扣 款 。 单 击 单 元 格 J2 , 在 编 辑 栏 中 直 接 输 入 公 式 “=ROUND(IF(F2>20,100,50-IF(F2=0, 50,0)),0)”,然后进行公式填充,计算出所有员工的迟 到扣款.

提示
公式“=ROUND(IF(F2>20,100,50-IF (F2=0,50,0)),0)”表示如果 该员工迟到的时间超过 20 分钟(F2>20) ,则显示结果为 100;如果 迟 到 , 但 不 超 过 20 分 钟 , IF(F2=0,50,0) 的 结 果 为 0 , 50 - IF(F2=0,50,0))的结果为 50;但如果没有迟到,即迟到时间为 0,则 IF(F2=0,50,0)的结果为 50,50-IF(F2=0,50,0))的结果为 0。该公 式的依据是公司规定迟到时间以 20 分钟为分界点,超过扣 100 元, 不超过扣 50 元,不迟到不扣款。

? 下面计算合计扣款。在单元格G2中输入公式“=H2+I2+J2”,然后进行公式填充,计算出员工 的总计扣款额。

79

Excel 2010 精 选 案 例 应 用 电 子 书

? 选择【文件】选项卡,在弹出的列表中选择【另存为】选项,将工作簿另存为“员工出勤管 理表.xlsx”,单击【保存】按钮。

2.5 设计业绩管理及业绩评估系统
员工取得的业绩好,公司的业务发展就好,所以公司业务的发展主要体现在员工的业绩表 现上,因此人事部门在月底对员工的业绩管理是非常必要和重要的。 下面首先讲解如何建立业绩管理表。

2.5.1

设计业绩管理表

业绩管理表的具体设计步骤如下。

? 打开随书光盘中的“素材\ch18\业绩管理及业绩奖金评估.xlsx”工作簿,包含3个工作表,分 别是“业绩管理表”、“业绩奖金标准表”和“业绩奖金评估表”。

选择 中的单元格C3, 在编辑栏中直接输入公式 “=SUM(D3:O3)” , ? 计算累计业绩。 “业绩管理表” 按【Enter】键确认。

80

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

提示
假如当前为五月末,只有 1~5 月份的业绩, 但公式“=SUM(D3:O3)”中参数单元格区域 D3:O3 已包含 1~12 月份,所以当输入 6 月 份(含)以后的业绩时,单元格 C3 将自动重 新计算累计业绩。

? 将鼠标指针放在单元格C3右下角的填充柄上,当指针变为 形状时拖动,将公式复制到该列 的其他单元格中,计算其他员工的累计业绩。

? 在累计业绩中,可以通过添加图表来形象表示业绩。选择单元格区域D8:H8,在【插入】选项
卡中,单击【图表】选项组中的【柱形图】按钮 图】组中的【三维簇状柱形图】选项。 ,在弹出的图表下拉列表中选择【二维柱形

81

Excel 2010 精 选 案 例 应 用 电 子 书

? 这时可以看到工作表中生成一个“王宝超”的业绩图表,同时多出【设计】、【布局】及【格 式】3个图标工具选项卡。

? 在【布局】选项卡中,单击【标签】选项组中的【图表标题】按钮

,在弹出的下拉菜单中

选择【图表上方】命令。在图表顶部显示出“图表标题”,将其改为“王宝超的业绩”。

? 在【布局】选项卡中,单击【标签】选项组中的【坐标轴标题】按钮

,在弹出的下拉菜单

中选择【主要横坐标标题】,在子菜单中选择【坐标轴下方标题】选项,将横坐标标题改为“月 份”。

82

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 使用同样的方法将纵坐标标题改为“业绩量”。

? 分析图已设置好,可以直观地看到员工“王宝超”每月取得的业绩。

2.5.2

设计业绩奖金评估表

只有对员工的业绩给予一定的业绩奖金,企业才能最大程度地调动员工的积极性。下面就 来介绍如何根据奖金评估标准来评估员工的业绩奖金。

提示
“业绩奖金标准表” 工作表中的数据是根据某企业的业绩奖金标准得来的, 主要有以下几条:单月销售额在 34 999 以下的,没有基本业绩奖;单月销 售额在 35 000~49 999 的,按销售额的 3%发放基本业绩奖金;单月销售 额在 50 000~79 999 的,按销售额的 6%发放基本业绩奖金;单月销售额 在 80 000~119 999 的,按销售额的 9%发放基本业绩奖金;单月销售额在 120 000 以上的,按销售额的 12%发放基本业绩奖金,但基本业绩奖金不得 超过 48 000;累计销售额超过 220 000,公司给予一次性 18 000 的奖励。

? 选择“业绩奖金标准表”工作表。

83

Excel 2010 精 选 案 例 应 用 电 子 书

? 选择“业绩奖金评估表”工作表。

选择 工作表中的单元格C2, ? 设置自动显示销售业绩额即5月份的销售业绩。 “业绩奖金评估表” 在编辑栏中直接输入公式“=VLOOKUP(A2,业绩管理表!$A$3:$H$11,8,1)”,按【Enter】键确 认,可以看到单元格C2中自动显示员工“陈青花”5月份的业绩量。

提示
公 式 “ =VLOOKUP(A2, 业 绩 管 理!$A$3:$H$11,8,1)”中第 3 个参数设置为 “8”表示取满足条件的记录在“业绩管理 表!$A$3:$H$11”区域中第 8 列的值。

? 设置自动显示累计业绩额。单击单元格E2,在编辑栏中直接输入公式“=VLOOKUP(A2,业绩 管理表!$A$3:$C$11,3,1)”,按【Enter】键确认,可以看到单元格E2中自动显示员工“陈青花” 累计业绩额。

? 按照同样的方法计算奖金比例。单击单元格D2,在编辑栏中直接输入公式“=HLOOKUP(C2, 业绩奖金标准表!$B$2:$F$3, 2)”,按【Enter】键确认,可以看到单元格D2中自动显示员工的奖 金比例。

84

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

提示
公式“=HLOOKUP(C2,业绩奖金标准!$B$2:$F$3,2)” 中第 3 个参 数设置为 “2” 表示取满足条件的记录在 “业绩奖金标准!$B$2:$F$3” 区域中第 2 行的值。

? 使 用 同 样 的 方 法 计 算 基 本 业 绩 奖 金 。 单 击 单 元 格 F2 , 在 编 辑 栏 中 直 接 输 入 公 式 “=IF(C2<=400000,C2*D2,"48,000")”,按【Enter】键确认。

提示
公式“=IF(C2<=400000,C2*D2,"48, 000")”的含义为:当单元格 C2 数据小于等于 400 000 时, 返回结果为单元格 C2 乘以单元格 D2, 否则返回 48 000。因为公司规定,单月销售额在 120 000 以上的, 按销售额的 12%发放基本业绩奖金,但基本业绩奖金不得超过 48 000,基本业绩奖金 48,000 对应的销售额为 400 000。

? 下面计算累计业绩奖金。在单元格G2中输入公式“=IF(E2>220000,18000,0)”,按 【Enter】键确认,即可计算出累计业绩奖金。

85

Excel 2010 精 选 案 例 应 用 电 子 书

? 在单元格H2中输入公式“=F2+G2”,按【Enter】键确认,计算出业绩总奖金额。

? 使用填充柄复制公式,将公式填充到其他单元格中,计算出所有员工相应的业绩额。

? 选择【文件】选项卡,在弹出的列表中选择【另存为】选项,将工作簿另存为 “业绩管理及 业绩评估系统.xlsx”,然后单击【保存】按钮保存文件即可。

2.6 设计薪资管理系统
人事部门管理薪资的一个重要手段就是对企业员工的薪资管理, 薪资管理需要对大量的数 据进行统计汇总,工作量非常繁杂,使用Excel 2010进行自动管理将大大提高工作效率。

2.6.1

自动更新基本工资

使用Excel 2010可以建立一个随时更新的数据库,方便快速地调阅数据,让每个员工的基 本工资都自动更新的具体方法如下。

? 打开随书光盘中的“素材\ch18\薪资管理.xlsx”工作簿,包含2个工作表,分别为“薪资调整” 和“薪资汇总”。

86

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 选择“薪资调整”工作表,选择单元格区域A2:E10,在【公式】选项卡,单击【定义的名称】 。 选项组中【定义名称】按钮

? 弹出【新建名称】对话框,在【名称】文本框中输入“薪资调整”,在【范围】下拉列表中 选择“工作簿”,在【引用位置】文本框中输入“=薪资调整!$A$2:$E$10”。

? 单击【确定】按钮,则名称框中显示定义的范围名称“薪资调整”。

? 切换到“薪资汇总”工作表,选择单元格D2,在编辑栏中直接输入公式“=VLOOKUP(A2,薪 资调整,5)”,按【Enter】键确认,则可自动显示出员工的基本工资。
87

Excel 2010 精 选 案 例 应 用 电 子 书

? 将鼠标指针放在单元格D2右下角的填充柄上,当指针变为 形状时拖动,将公式复制到该列 的其他单元格中,。

2.6.2

奖金及扣款数据的链接

Excel 2010中的一种非常好用的功能——数据链接,这项功能最大的优点就是结果会随着 数据源的变化自动更新。下面主要讲解如何实现数据的链接。

? 打开随书光盘中的“素材\ch18\员工出勤管理表.xlsx”和“业绩表.xlsx”文件。

? 设置加班费的链接。选择“薪资调整”工作表中的单元格E2,在编辑栏中直接输入公式 “=VLOOKUP(A2,[员工出勤管理表.xlsx]加班记录!$B$2:$G$10,6)”,按【Enter】键确认,可以 看到单元格E2中自动显示员工“陈青花”的加班费,然后填充其他员工的加班费。
88

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

提示
公 式 “ =VLOOKUP(A2,[ 员 工 出 勤 管 理 表 .xlsx] 加 班 记 录!$B$2:$G$10,6)”中第 3 个参数设置为“6”表示取满足条件的记 录在“[员工出勤管理表.xlsx]加班记录!$B$2:$G$10”区域中第 6 列 的值, “[员工出勤管理表.xlsx]加班记录!$B$2:$G$ 10”区域就是“员 工 出 勤 管 理 表 .xlsx ” 中 的 “ 加 班 记 录 ” 工 作 表 中 的 单 元 格 区 域 $B$2:$G$10。

? 设置缺勤扣款的链接。单击单元格F2,在编辑栏中直接输入公式“=VLOOKUP(A2,[员工出勤 管理表.xlsx]缺勤记录!$A$2:$K$10,11)”,按【Enter】键确认,然后自动填充其他员工的缺勤扣 款。

? 按 照 同 样 的 方 法 设 置 业 绩 奖 金 的 链 接 。 单 击 单 元 格 G2 , 在 编 辑 栏 中 直 接 输 入 公 式 “=VLOOKUP(A2,[业绩表.xlsx]业绩奖金评估!$A$2:$H$10,8)”,按【Enter】键确认,在单元格
G2中显示出员工的业绩奖金,然后自动填充其他员工的业绩奖金。

? 在单元格H2中输入公式“=D2+E2-F2+G2”,按【Enter】键确认,计算出税前工资,同样 填充公式到本列的其他单元格中。

89

Excel 2010 精 选 案 例 应 用 电 子 书

2.6.3

计算个人所得税

依照我国税法规定,企业员工应缴纳个人所得税,而一般计算应纳税额用的是超额累进税 率,计算起来比较麻烦和繁琐,而使用Excel 2010的速算扣除书计算法功能,计算会变得简便。

? 打开随书光盘中的“素材\ch18\所得税计算表.xlsx”文件,选择“所得税率”工作表,参考此 所得税率表会发现公司001、004和006号员工适用的所得税率为25%,扣除数为1375, 据此编辑公式,在单元格I2中输入“=H2*25%-1375”,按【Enter】键确认,计算出应纳税款。 用同样的方法求另外两个员工的税款。

扣除数为375, 据此编辑 ? 同样参考所得税率表会发现公司 002号员工适用的所得税率为20%, 公式,在单元格I3中输入“=H3*20%-375”,按【Enter】键确认,计算出应纳税款。

? 参考所得税率表会发现公司其他员工适用的所得税率为15%,扣除数为125,据此编辑公式, 在单元格I4中输入“=H4*15%-125”,按【Enter】键确认,计算出应纳税款。用同样的方法求 出其他员工的税款。

90

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 员工的应发工资应为税前工资减去应交所得税,所以,在单元格J2中输入“=H2-I2”,求出 员工的应发工资,填充得到所有员工的应发工资。

? 选择【文件】选项卡,在弹出的列表中选择【另存为】选项,将工作簿另存为“薪资管理系 统.xlsx”单击【保存】按钮即可。

2.7 设计员工年度考核系统
一般人事部门都会在年终或季度末时对员工的表现做一次考核, 这不但可以对员工的工作 进行督促和检查,还可以根据考核情况发放年终和季度奖金。

2.7.1

设置数据有效性

设置数据有效性的具体操作步骤如下。

? 打开随书光盘中的“素材\ch18\员工年度考核.xlsx”工作薄,包含2个工作表,分别为“年度 考核表”和“年度考核奖金标准”。

91

Excel 2010 精 选 案 例 应 用 电 子 书

提示
企业的年度考核将对排在前几名的员工给予 奖金奖励,假定标准为:第 1 名奖金 10 000 元;第 2、3 名奖金 70 00 元;第 4、5 名奖 金 40 00 元;第 6~10 名奖金 20 00 元.

? 选中“出勤考核”所在的D列,在【数据】选项卡中,单击【数据工具】选项组中【数据有效
性】按钮 ,在弹出的下拉菜单中选择【数据有效性】选项。

? 在弹出的【数据有效性】对话框中,选择【设置】标签,单击【允许】列表框右侧的下拉箭 头,在弹出的列表中选择【序列】选项,在【来源】文本框中输入“6,5,4,3,2,1”。

提示
假设企业对员工的考核成绩分为 6、5、4、3、 2 和 1 六个等级,从 6 到 1 依次降低。另外, 在输入“6,5,4,3,2,1”时中间的逗号要在半 角模式下输入。



92

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 切换到【输入信息】标签,选择【选定单元格时显示输入信息】复选项,在【标题】文本框 中输入“请输入考核成绩”,在【输入信息】文本框中输入“可以在下拉列表中选择”。

? 切换到【出错警告】标签,选择【输入无效数据时显示出错警告】复选项,单击【样式】文 本框右侧的下拉箭头,在弹出的列表中选择【停止】选项,在【标题】文本框中输入“考核成绩 错误”,在【错误信息】文本框中输入“请到下拉列表中选择”。

? 切换到【输入法模式】标签,单击【模式】文本框右侧的下拉箭头,在弹出的列表中选择【关 闭(英文模式)】选项,以保证在该列输入内容时始终不是英文输入法。

? 单击【确定】按钮,则数据有效性就设置完毕。单击单元格D2,则其下方出现一个黄色的信
93

Excel 2010 精 选 案 例 应 用 电 子 书

息框。

? 在单元格D2中输入“8”,按【Enter】键后会弹出【考核成绩错误】提示框,如果要接续单 击【重试】按钮,可以重新输入。

? 参照步骤?~?设置E、F、G列的数据有效性,并依次输入员工的成绩。

? 计算综合考核成绩。在单元格H2中输入“=SUM(D2:G2)”,按【Enter】键确认,然后将鼠 标指针放在单元格H2右下角的填充柄上,当指针变为 形状时拖动,将公式复制到该列的其他单 元格中,则可以看到这些单元格中自动显示员工的综合考核成绩。

2.7.2

设置条件格式

设置条件格式的操作步骤如下。
94

第2章

Excel 在 人 力 资 源 管 理 中 的 应 用

? 选择单元格区域H2:H10,切换到【开始】选项卡,单击【样式】选项组中的【条件格式】按 钮 ,在弹出的下拉菜单中选择【新建规则】选项。

? 弹出【新建格式规则】对话框,在【选择规则类型框】列表框中选择【只为包含以下内容的 单元格设置格式】选项,在【编辑规则说明】区域的第一个下拉列表框中选择【单元格值】选项, 第二个下拉列表框中选择【大于或等于】选项,在右侧文本框中输入“18”。

? 在【新建格式规则】对话框中单击【格式】按钮,打开【设置单元格格式】对话框,选择【填 充】标签,在【背景色】列表框中选择【红色】选项,在【示例】区可以看到预览效果。

? 单击【确定】按钮,返回【新建格式规则】对话框,单击【确定】按钮。可以看到18分及18 分以上的员工的综合考核呈红色背景色显示,非常醒目。

95

Excel 2010 精 选 案 例 应 用 电 子 书

2.7.3

计算员工年终奖金

操作步骤如下。 在单元格I2中输入 “=RANK(H2,$H$2:$H$10,0)” 按 , 【Enter】 ? 对员工综合考核成绩进行排序。 键确认,可以看到在单元格I2显示出排名顺序,使用自动填充功能得到其他员工的排名顺序。

? 有了员工的排名顺序,就可以计算出他们的年终奖金。在单元格J2中输入“=LOOKUP(I2,年 度考核奖金标准!$A$2:$B$5)”,按【Enter】键确认,可以看到在单元格J2中显示出年终奖金, 使用自动填充功能得到其他员工的奖金。

提示
企业对年度考核排在前几名的员工给予奖金奖励, 假定标准为: 第 1 名奖金 10 000 元;第 2、3 名奖金 70 00 元;第 4、5 名奖 金 40 00 元;第 6~10 名奖金 20 00 元。

? 选择【文件】选项卡,在弹出的列表中选择【另存为】选项,将工作簿另存为“员工年度考 核系统.xlsx”,然后单击【保存】按钮保存文件即可。
96


Excel_2010在人力资源管理中的应用_图文.pdf

Excel_2010在人力资源管理中的应用 - 第 章 Excel 2010在人

EXCEL在人力资源管理中应用_图文.ppt

EXCEL在人力资源管理中应用 - Excel在人力资源管理中的应用 人力资源部

Excel在人力资源管理的应用_图文.ppt

Excel在人力资源管理的应用 - 人力资源部门 Excel在人力资源管理中的应用 用好工具,高效工作 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 认识...

EXCEL在人力资源管理中的应用_图文.ppt

EXCEL在人力资源管理中的应用_幼儿读物_幼儿教育_教育专区 人阅读|次下载 EXCEL在人力资源管理中的应用_幼儿读物_幼儿教育_教育专区。 ...

Excel在人力资源管理中的应用(共 43张PPT)_图文.ppt

Excel在人力资源管理中的应用(共 43张PPT) - LOGO Excel在人力资源管理中的应用 ARROW +0 课程大纲 数据录入 § 单元格的引用 § 数据有效性 § 条件格式...

《EXCEL在人力资源管理中的高效应用》_图文.doc

EXCEL在人力资源管理中的高效应用》 - 蓝草咨询,快乐培训!www.bgwahaha.cn 上海蓝草企业管理咨询有限公司 EXCEL 在人力资源管理中的高效应用 课程背景: 作为人力...

Excel在人力资源管理的应用(极其精美实用)_图文.ppt

Excel在人力资源管理的应用(极其精美实用) - LOGO Excel在人力资源管理中的应用 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 LOGO 认识Exc...

第10章(excel在人力资源管理中的应用)_图文.ppt

第10章(excel在人力资源管理中的应用) - 第10章 经典实例:管理员工工

EXCEL在人力资源中的应用_图文.ppt

EXCEL在人力资源中的应用_幼儿读物_幼儿教育_教育专区。 文档贡献者 w55774123 贡献于2019-10-18 1 /2 相关文档推荐 Excel在人力资源管理中的... 42页 5...

第5章(excel在人力资源管理中的应用)_图文.ppt

第5章(excel在人力资源管理中的应用) - 第5章 经典实例:管理人员招聘与

Excel在人力资源管理的应用1教程_图文.ppt

Excel在人力资源管理的应用1教程 - LOGO Excel在人力资源管理中的应用 ARROW +0 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 LOGO ...

第1章(excel在人力资源管理中的应用)_图文.ppt

第1章(excel在人力资源管理中的应用) - 第1章 认识Excel管理人力资

office使用窍门-Excel在人力资源管理的应用_图文.pdf

office使用窍门-Excel在人力资源管理的应用 - 人力资源部门 Excel在人力资源管理中的应用 用好工具,高效工作 课程大纲 ?数据录入 ? 单元格的引用 ? 数据有效性 ?...

HR高效工作的秘诀:Excel在人力资源管理的应用_图文.ppt

HR高效工作的秘诀:Excel在人力资源管理的应用 - 人力资源部门 Excel在人力资源管理中的应用 用好工具,高效工作 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ...

第6章(excel在人力资源管理中的应用)_图文.ppt

第6章(excel在人力资源管理中的应用) - 第6章 经典实例:管理员工合同

第2章(excel在人力资源管理中的应用)_图文.ppt

第2章(excel在人力资源管理中的应用) - 第2章 经典实例:制作人事资料管

EXCEL在人力资源管理中的应用精品文档_图文.ppt

EXCEL在人力资源管理中的应用精品文档_人力资源管理_经管营销_专业资料 人阅读|次下载 EXCEL在人力资源管理中的应用精品文档_人力资源管理_经管营销_专业资料。 ...

office使用窍门-Excel在人力资源管理中的应用_图文.pdf

office使用窍门-Excel在人力资源管理中的应用 - LOGO Excel在人力资源管理中的应用 课程大纲 v 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 LOGO ...

EXCEL在企业人力资源管理上的应用PPT课件_图文.ppt

EXCEL在企业人力资源管理上的应用PPT课件 - 从数据中拧出信息 EXCEL在人力资源管理上的应用 讲师: 2008 年版 2019/7/8 管理智慧系列 1 讲师介绍 管理...

Excel在人力资源管理的应用_图文.ppt

Excel在人力资源管理的应用 - Excel在人力资源管理中的应用 用好工具,高效工作 课程大纲 数据录入 ? 单元格的引用 ? 数据有效性 ? 条件格式 认识Excel函数 ? ...