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

川长思鸟来 2023-01-06 15:53 136阅读 0赞

营长说

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

01. 矩阵分数查询

**案例：**

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

![068b01b688fff8e88f02209d2a31d115.png][]

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

<table> 
 <tbody> 
 <tr> 
 <td>参数</td> 
 <td>查询目标</td> 
 <td>数据区域排序要求</td> 
 </tr> 
 <tr> 
 <td>1</td> 
 <td>查询&lt;=待查询数据的最大值</td> 
 <td>升序排列</td> 
 </tr> 
 <tr> 
 <td>0</td> 
 <td>精确查询待查询数据</td> 
 <td>任意</td> 
 </tr> 
 <tr> 
 <td>-1</td> 
 <td>查询&gt;=待查询数据的最小值</td> 
 <td>降序排列</td> 
 </tr> 
 </tbody> 
</table>

**INDEX函数(导弹)**

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

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

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

![ce1f118c6ba82ec3598b9bc2253bccfc.png][]

在D1单元格输入公式

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

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

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

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

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

![718e957c4634eeda8da0e4f8f1b6bf21.png][]

02. 名单矩阵呈现

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

![b837ab06efe35ba3c2530ed5110ab4d4.png][]

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

![62714818f2da29b5bacbf70fb670fb69.png][]

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

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

![085ddabcd0efbfa2ae44ade9bd611733.png][]

在H1单元格中输入公式

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

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

![23a8a8215c209ae3149ef1284d61b75b.gif][]

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

TEXTJOIN函数的语法结构如下：

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

![ce138c306fef298b22512bd121c6863f.png][]

在H1单元格中输入公式

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

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

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

![353b860808ba7741a186603e4d8326ad.png][]

在H1单元格中输入公式

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

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

![2cffe8e67f8980a2937ff3f1b8639caf.png][]

在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

图书教程

▼▼▼

![62841a2617c9af6b9d59f2888b39eb43.png][]

[068b01b688fff8e88f02209d2a31d115.png]: /images/20221119/1f927bfdf5794b1b87b1fa360e873887.png
[ce1f118c6ba82ec3598b9bc2253bccfc.png]: /images/20221119/ac7dfb08e05f4ca0a91cf6c1935ac7b8.png
[718e957c4634eeda8da0e4f8f1b6bf21.png]: https://img-blog.csdnimg.cn/img_convert/718e957c4634eeda8da0e4f8f1b6bf21.png
[b837ab06efe35ba3c2530ed5110ab4d4.png]: https://img-blog.csdnimg.cn/img_convert/b837ab06efe35ba3c2530ed5110ab4d4.png
[62714818f2da29b5bacbf70fb670fb69.png]: https://img-blog.csdnimg.cn/img_convert/62714818f2da29b5bacbf70fb670fb69.png
[085ddabcd0efbfa2ae44ade9bd611733.png]: https://img-blog.csdnimg.cn/img_convert/085ddabcd0efbfa2ae44ade9bd611733.png
[23a8a8215c209ae3149ef1284d61b75b.gif]: https://img-blog.csdnimg.cn/img_convert/23a8a8215c209ae3149ef1284d61b75b.gif
[ce138c306fef298b22512bd121c6863f.png]: https://img-blog.csdnimg.cn/img_convert/ce138c306fef298b22512bd121c6863f.png
[353b860808ba7741a186603e4d8326ad.png]: https://img-blog.csdnimg.cn/img_convert/353b860808ba7741a186603e4d8326ad.png
[2cffe8e67f8980a2937ff3f1b8639caf.png]: https://img-blog.csdnimg.cn/img_convert/2cffe8e67f8980a2937ff3f1b8639caf.png
[62841a2617c9af6b9d59f2888b39eb43.png]: https://img-blog.csdnimg.cn/img_convert/62841a2617c9af6b9d59f2888b39eb43.png