评价函数_案例 | Excel九宫格矩阵评价统计，离不开这些函数

营长说

在绩效管理方案中，经常会遇到矩阵评价，常见的有2*2和3*3矩阵。总有学员问与矩阵查询统计的问题，有一定的代表性。今天营长就以经典的3*3九宫格矩阵为例，向你介绍涉及到的函数。

01. 矩阵分数查询

案例：

员工综合评价，分为业绩和能力两个维度。业绩分为优、中和差三类，能力分为A、B、C三类，业绩和能力形成3*3九宫格矩阵表，请按该表规则为每位员工匹配对应的分值。

这涉及到矩阵中行列交叉查询的问题，需要用到经典的INDEX+MATCH函数。 MATCH函数(雷达) MATCH函数返回对应的单元格坐标，即某个数据在指定数据区域里面排第几。MATCH函数的语法结构如下：    MATCH( 待查数据 ， 数据区域 ， 匹配参数 ) 数据区域必须是连续的单行或者连续的单列，匹配参数分为1(默认)、0、-1三种情况。

参数	查询目标	数据区域排序要求
1	查询<=待查询数据的最大值	升序排列
0	精确查询待查询数据	任意
-1	查询>=待查询数据的最小值	降序排列

INDEX函数(导弹)

INDEX函数按指定的行列坐标取值，可以获取矩形区域的第几行，第几列交叉的数据。 此函数分为数组形式和引用形式，本文介绍最常用的是数组形式，它的语法结构如下：

  INDEX(数据范围，第几行，第几列)

本例中就可以做出下面的效果。

在D1单元格输入公式

=INDEX($H$2:$J$4,MATCH(B2,$G$2:$G$4,0),MATCH(C2,$H$1:$J$1,0))

第一个MATCH公式获得业绩对应的行数。

第二个MATCH公式获得能力对应的列数。

INDEX函数根据计算的行数和列数从分数矩阵中做交叉取值。

这样分数就快速匹配出来了。

02. 名单矩阵呈现

还有这样的情况，根据每个人的业绩和能力评价结果，按九宫格进行人数统计或列出对应的名单，如下图效果。

关于九宫格人数统计，很简单，用数据透视表就可以快速汇总出来。

如何列出矩阵中对应的人员名单，是个难点。这里向你介绍Excel 365最新的动态数组函数FILTER。FILTER函数可以实现一对多和多对多的查询。

下图是查询业绩为”优“的员工名单。

在H1单元格中输入公式

=FILTER(A2:A16,B2:B16=G2,"")

用数据验证将业绩做成下拉列表，可以给你动态展示下FILTER的神奇效果。

要将人员名单放入一个单元格中，就要用到Excel 365中的TEXTJOIN和FILTER函数组合。TEXTJOIN函数可以设置名单之间的分隔符号。

TEXTJOIN函数的语法结构如下：

• TEXTJOIN(连接符号，是否忽略空单元格，文本1，文本2，…)

在H1单元格中输入公式

=TEXTJOIN("、",,FILTER($A$2:$A$16,$B$2:$B$16=$G2,""))

这是一维查询，仅仅根据业绩的优、中、差来匹配人员名单。

矩阵中的二维匹配只需在FILTER中添加一个条件即可，两个条件用*(星号)表示同时满足。

在H1单元格中输入公式

=TEXTJOIN("、",,FILTER($A$2:$A$16,($B$2:$B$16=$G2)*($C$2:$C$16=H$1),""))

很多人喜欢做成名单在单元格内换行的效果，需要对上面的公式进行改良。将分隔符号用CHAR(10)替换即可，当然需要设置单元格为自动换行。

在H2单元格中输入公式

=TEXTJOIN(CHAR(10),,FILTER($A$2:$A$16,($B$2:$B$16=$G2)*($C$2:$C$16=H$1),""))

所以，九宫格矩阵查询统计常用函数有：INDEX、MATCH、TEXTJOIN、FILTER、CHAR等，别看了，赶紧试试吧！

精彩推荐

▼▼▼

Excel中数据的“乾坤大挪移”，简单好记，值得收藏！ 2020-02-19 腾讯课堂 | 《Excel高效办公：财务数据管理》《商务PPT的说服之道》 2020-02-11 视频 | Excel窗体控件+INDEX函数，轻松制作动态图表 2020-02-03 用Excel制作动态四象限矩阵图，适合分区域管理 2020-01-04 Excel这个新函数，让你轻松实现多对多查询，太方便了 2019-11-27

图书教程

▼▼▼