岁末收官,人事部门会在年终对员工全年的表现进行一次考核,这不仅可以对员工的工作进行督促和检查,还可以根据考核的情况发放年终奖。以下是员工年度考核表。看似简单,这里面却用到了数据验证、条件格式、SUMPRODUCT函数、LOOKUP函数等,了解该表的制作过程,将对人事部的制表者大有益处。
员工年度考核表的具体制作步骤如下:
步骤1:设置数据有效性
选中“出勤考核”所在的D列,在“数据”选项卡中,单击“数据工具”选项组的“数据验证”按钮,在弹出的下拉菜单中选择“数据验证”命令。
在弹出的“数据验证”对话框中选择“设置”选项卡,在“允许”下拉列表框中选择“序列”选项。假设企业对员工的考核成绩分为4、3、2、1共4个等级,等级越高,表现越优秀。在“来源”输入框中输入“4,3,2,1”,注意数字间间隔的逗号,需要在英文状态下输入。
接着,切换到“输入信息”选项卡,选中“选定单元格时显示输入信息”复选框,在“标题”文本框中输入“请输入考核成绩”,在“输入信息”列表框中输入“可以在下拉列表中选择”。
然后切换到“出错警告”选项卡,选中“输入无效数据时显示出错警告”复选框,在“样式”下拉列表框中选择“停止”选项,在“标题”文本框中输入“考核成绩错误”,在“错误信息”列表框中输入“请到下拉列表中选择”。
切换到“输入法模式”选项卡,在“模式”下拉列表框中选择“关闭(英文模式)”选项,以保证在该列输入内容时始终不是英文输入法。
单击 “确定”按钮,数据验证设置完毕。接着单击单元格D2,其下方会出现一个黄色的信息框。
在单元格中输入“5”,按“Enter”键,会弹出“考核成绩错误”提示框,如果单击“重试”按钮,则可重新输入。
参照上述步骤,设置E、F、G列的数据有效性,并依次输入员工的成绩。
接下来,我们就需要计算员工的综合考核成绩了。在单元格H2中输入“=SUM(D2:G2)”,对D2:G2区域求和,按“Enter”键确认,并向下填充。
步骤2:设置条件格式
为了更加突出考核优异的员工,我们对综合考核超过12的员工设置条件格式。选择H2:H10单元格区域,在“开始”选项卡中,单击“样式”选项组的“条件格式”,在弹出的下拉菜单中选择“新建规则”命令,在列表框中选择“只为包含以下内容的单元格设置格式”选项,在“编辑规则说明”区域的第一个下拉列表框中选择“单元格值”选项,在第二个下拉列表框中选择“大于”选项,在右侧文本框中输入“12”。
单击“格式”按钮,打开“设置单元格格式”对话框,点击“填充”选项卡,在“背景色”列表框中选择“黄色”选项,在“示例”区可以预览效果。
单击“确定”按钮,返回“新建格式规则”对话框,单击“确定”按钮。可以看到综合考核为12分以上的员工呈黄色显示,非常醒目。这样即使在考核人数众多的情况下,也能马上识别出综合考核优异的员工。
步骤3:计算年终奖金
企业会对年度考核在前几名的员工发放奖金奖励,标准为:第一名奖金10000元,第二、三名奖金5000元,第四、五名奖金2000元,其他1000元,如下图所示。
我们需要先对员工综合考核成绩进行排名,才能计算出对应的年终奖金,这里我们采用中国式排名。
在I2单元格中输入公式:
=SUMPRODUCT((H$2:H$10>=H2)/COUNTIF(H$2:H$10,H$2:H$10))
公式用到了两个函数,其中SUMPRODUCT函数是在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和;COUNTIF函数是计算某个区域中满足给定条件的单元格数目。公式的第一部分内容:H$2:H$10>=H2,判断H2:H10区域中的成绩是否大于等于当前的H2成绩,如果成立返回TRUE,否则返回FALSE。公式的第二部分内容:COUNTIF(H$2:H$10,H$2:H$10)是统计在H2:H10区域中每个成绩出现的次数,如H2为10分,在H2:H10区域中一共有3个,H5为8分,在H2:H10区域中一共有2个......依此类推。最终得到了两组数据分别一一对应进行计算,最后由SUMPRODUCT函数进行求和,得到当前成绩的排名。向下填充,即可得到其他员工的排名。
对中国式排名还不是很熟悉的小伙伴可以点击查看相关教程《统计中国式排名的三种方法,一定有一个适合你》
有了员工的排名顺序,就可以轻松计算出年终奖金啦。
在J2单元格中输入公式:
=LOOKUP(I2,年度考核奖金标准!$A$2:$B$5)
LOOKUP函数是Excel中的查找函数,实质是返回在“年度考核奖金标准!$A$2:$B$5”中该排名对应的奖金。按“Enter”键确认,可以看到单元格J2中出现的年终奖金,向下填充,即可得到其他员工的年终奖金。最后选中表格,数据居中,添加边框,员工年度考核表就完成啦!