excel图表技巧：做一张员工年度考核表的步骤

编按：哈喽，大家好！又到了一年中最忙碌的日子，想必小伙伴们这段时间肯定在被各种数据折腾。今天小编要给大家分享一招制作员工年度考核表的方法，学会了此招，相信会对正在忙于制表的你，大有益处，赶紧来看看吧！

2019，岁末收官，人事部门会在年终对员工全年的表现进行一次考核，这不仅可以对员工的工作进行督促和检查，还可以根据考核的情况发放年终奖。以下是员工年度考核表。看似简单，这里面却用到了数据验证、条件格式、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中出现的年终奖金，向下填充，即可得到其他员工的年终奖金。最后选中表格，数据居中，添加边框，员工年度考核表就完成啦！

分享结束，自己动手试试吧！

＊＊＊＊部落窝教育－excel年度考核表＊＊＊＊

原创：赋春风/部落窝教育（未经同意，请勿转载）

微信公众号：exceljiaocheng