这是一个很有趣的案例。任务要求:部门每个人绩效考核评分后,表格根据分数,对所有员工进行自动排名并排序,不是显示名次,而是自动按名次重新排列员工顺序。说的多,不如看看动图化的要求吧,就像这样:
看明白了么?其实一开始觉得这个任务是个悖论,一方面我算出来结果,然后根据结果又原位排列顺序,是不合逻辑的。不过我们可以换个思路,我们可以改为2张表,表1用来计算结果,表2接收表1的结果并自动排列,这样就打破了悖论。这段话没看懂也不要紧,文章看完再回来看这一段。
第一步:完成表格版式,及表1数据录入
为演示方便,将表1设为静态表,实际工作中加公式就可以改造为动态的。表1的排名其实可以不设置,这里加上是为了看的清楚。将员工姓名复制粘贴到表2,并用VLOOKUP从表1取得分。
第二步:手动排序,并录制宏
这步很关键。自动排列,首先想到这是代码干的,所以要用到宏或VBA。我们先告诉EXCEL要干什么,即录个片段,让EXCEL自动执行就好。点击录制宏,名字随意。然后选择“得分”和“员工姓名”列,记住这里一定不要反哦,因为我们是要按分数排序。然后点击“数据”选项卡——“降序”,再点击停止录制宏,可以查看代码。
至此,静态的排序操作已经完成了,我们加个排名吧。
第三步:让表格动起来这步思想是,每次我操作后,让EXCEL自动做一次自动排序工作,即调用一次“宏2”。点击“开发工具”选项卡——“Visual Basic”,就打开了VBE环境。双击左侧的Sheet1,然后右侧界面第一个下拉框选择“Worksheet”,第二个下拉框选择“SelectionChange”。然后在第一个SUB过程中写上“宏2”,关掉就好。其实前面步骤可以给“宏2”起个好点的名字,比如“自动排名排序”,这里引用的时候就很清楚。
下面就是验证操作结果的时候了!再看一遍动图:
完成任务!注意保存文件!因为是有代码的,所以需要保存为启用宏的工作簿xlsm格式。
你以为这就结束了??
等等,发现排名有点问题。好几个并列分,排名怎么不一样?这里我们写个公式就好,E3=COUNTIF($G$3:$G$20,">"&G3)+1,在右侧得分已经排好序的情况下进行计算。公式具体什么意思呢?简单说,就是计算这个区域数字比G3大的次数,因为牛人7已经最高分了,所以COUNTIF的结果就是0,然后加个1,就是排名;同样啊,牛人9也是并列最高分,所以COUNTIF的结果也是0,然后加个1,就实现了并列第一的目的。再到牛人3,有2个分比他高,所以排名就是2+1=3。
保存文件,打完收工。
学有用的知识,做有用的表