筛选出每个人最高星级的记录(升级版)
今天就来一个升级版的问题，筛选出每个人最高星级的记录。如下图所示，左表是数据源，右表是我们想要得到的效果。姓名我就用ABCD代替了，以姓名A为例说明一下题意：A对应的星级有6颗星，3颗星，4颗星，2颗星，我们只取最大的6颗星，并且把姓名也提取出来。BCD也是一样的，最后的结果如DE列所示。星星的颗数不超过10颗，每个人最高星级的只有1个。这个题目用数据透视表应该也做不出来吧？(我的数据透视表水平是小白级别
）。
想要做出这个题，就不能像之前那样直接使用maxifs了，因为B列的星级是文本。所以，首先需要把B列的星级变为数字(星星的颗数)，然后再用maxifs求出每个人的最高星级，最后用最高星级和当前的星级比较是否相等，相等的就是我们想要的。用一对多查询的方法返回对应的值就可以了。这样的话就要增加辅助列了，如下图所示。
如果不用辅助列能不能做到呢？其实用countifs就可以，它可以对文本进行大小的比较(我之前默认如果是文本的话countifs也不行，写着写着才发现它是可以的
）。如下图所示，countifs得到了组内的大小排名，等于1的就是每个人的最高星级，所以只要筛选出等于1的就得到了我们想要的结果。公式我就不写了，大家自己写一下，可以参考之前的文章。
还说是升级版题目，没想到被countifs就给破了，啪啪打脸。
不过countifs这种方法本来不是我今天想分享的，只是半路想出来的。主要想分享的是如何用一个内存数组得到每个人的最高星级数。下图C列就是每个人的最高星级数，比如A的最高星级是6，所以每个A都是6。D列是本来的星级数，用C列的最高星级数和D列的本来星级数比较，相等的就是我们想要的，如箭头所示。
所以，如何得到每个人的最高星级数就是当前的主要问题。不能用辅助列，并且结果是一个内存数组。这样的话，对我来说还是挺难实现的。下面来看看高手是怎么做到的？主要用到的方法是矩阵取最小值法。
=N(A2:A16=TRANSPOSE(A2:A16))这部分返回的结果如下图灰色区域所示，用A列的姓名和转置后的姓名进行相等的比较，形成一个二维数组，也可以说是一个矩阵。这个矩阵可以理解为用A列的姓名和每个姓名分别比较，比如G列是A列的姓名中等于A的，H列是A列的姓名中等于B的。。。以此类推。
=(A2:A16=TRANSPOSE(A2:A16))*LEN(B2:B16)这部分返回的结果如下图红色框所示，就是用上一步的结果乘以对应的星级数。这样的话，等于A的就返回A对应的星级数，比如G列等于A的分别是6，3，4，4，2。同样的，H列等于B的分别是3，4，5。得到这些数有什么用呢？其实我们想要从G列中取得A的最高星级6，从H列中取得B的最高星级5，从I列中取得C的最高星级8，。。。也就是我用红色标记的那几个数字。
如何从这个二维矩阵中取出每个人的最高星级数呢？=(A2:A16=TRANSPOSE(A2:A16))*LEN(B2:B16)+COLUMN(A:O)/1%这部分返回的结果如下图所示，其实就是给第1列加100，第2列加200，第3列加300。。。这样的话第1列的数据肯定比第2列的小，第2列的又比第3列的小。。。同时106是第1列的最大值，205是第2列的最大值，308是第3列的最大值。
每一列中有15个数字，所以106在这个二维数组中是第15个最小值，205是第30个最小值，308是第45个最小值。。。以此类推。=SMALL((TRANSPOSE(A2:A16)=A2:A16)*LEN(B2:B16)+COLUMN(A:O)/1%,ROW(1:15)*15)这部分返回的结果如下图C列所示，从这个二维数组中提取出第15个，30个，45个。。。最小值。因为一共有15列，所以是ROW(1:15)*15。
上图C列数字的个位数就是每个人的最高星级数。所以用mod除以100取余数就可以了，公式为=MOD(SMALL((TRANSPOSE(A2:A16)=A2:A16)*LEN(B2:B16)+COLUMN(A:O)/1%,ROW(1:15)*15),100)。至此，每个人的最高星级数就求出来了。然后用最高星级数和当前星级数比较，相等的就是我们要的。用一对多的套路查询出来就可以了。
最后完整的筛选公式为=INDEX(A:A,SMALL(IF(MOD(SMALL(($A$2:$A$16=TRANSPOSE($A$2:$A$16))*LEN($B$2:$B$16)+COLUMN($A:$O)/1%,ROW($1:$15)*15),100)=LEN($B$2:$B$16),ROW($2:$16),4^8),ROW(A1)))&""，按ctrl+shift+enter三键结束，向右向下填充。
由于二维矩阵占的位置比较大，截图可能显示不完整，强烈建议大家下载文件查看，而且文件中不只一种方法。
文件链接：
https://pan.baidu.com/s/1IO8VJWumn7W-Zn6rNBMSBQ
提取码：2sym