大话Excel之动态排名

已获授权转载

为了加强对捉妖师的管理，紫霞仙子在至尊宝的帮助下。设计了评价标准，并且通过Excel的公式能够方便地计算出捉妖师每个考核项的得分。给每个捉妖师都定了等级。为了进行差异化的考核，紫霞决定分等级进行绩效排名。

紫霞仙子: 宝宝，为了调动捉妖师们的工作积极性。我们决定按照捉妖师的等级进行绩效排名。有什么好的方法处理这个问题吗？

如下表所示，绩效得分根据岗位等级进行排名。

10%   A

30%   B+

70%   B

90%   C

100% D

     

至尊宝: 可以按照如下的步骤操作

1、按照岗位等级进行排序，将各个等级的员工放在一起

效果如下

2、创建名称用来存储动态的绩效得分区域

a.调出名称创建器        

b.每个等级的绩效得分数据都是存放在一起，因此只要取到数据的开始位置以及出现的次数  采用offset函数就可以找到对应的区域

c.创建开始位置的名称

开始位置=match(B2,B:B,0)

采用Match函数找到第一次出现的位置

d.创建出现次数的名称

出现次数=countif(B:B,B2)

采用countif函数找到出现的次数

e.采用offset函数找到对应的数据区域

数据区域＝offset($C$1,开始位置-1,出现次数,1,1)     

开始位置-1:表示从$C$1偏移的单元格

出现次数:表示该等级有多少个数据         

名称截图

 

3、使用Rank函数计算出排名

=Rank(c2,数据区域,0)

参数3设置为1表示按降序排序

效果

4、根据排名结合等级计算方法计算出最后的考核等级

建立比例与等级之间的对应关系表

借助lookup的模糊查询功能，当无法完全匹配时 返回待查找区域中的小于等于查找值的最大值

借助vlookup查找出对应的等级

=VLOOKUP(LOOKUP(D2/出现次数,$J$5:$J$9),$J$5:$K$9,2,FALSE)

D2/出现次数 用于计算排名对应的百分比

效果

紫霞仙子: 宝宝好棒，这么简单就解决了按等级相对排名计算出考核等级了。这里我可以了解到名称，rank match countif offset lookup vlookup函数。我得好好消化消化了。

至尊宝: 是啊，这里不光介绍这些函数，还回顾了上次介绍的把if函数转化为表格用查询进行处理的这种方便的设计模式。

---

●本文编号449，以后想阅读这篇文章直接输入449即可

●输入m可以获取到全部文章目录

●输入c可以获取到全部动画下载地址