筛选出每个人最高分的记录（一）

大家好，好久没更新文章了。最近这段时间没有动力，感觉爱也爱不了，恨也恨不了，做题也做不出，一直处于挣扎的状态，但又挣扎不出来，深深地无力感，受挫感。算了，那就不挣扎了，接受有些事情和有些题目自己目前是做不到的。

而且，随着陆续的复工，新关注公号的同学也在增加，说明大家还是有学习函数的需要，那我就继续分享我学到的一些函数技巧和知识（不定期的，因为我不知道自己什么时候状态就不好了，储备就不足了

），希望对大家有所帮助。

另外，我喜欢写一些比较有感觉的文章(人都是情感动物，我一个写思维逻辑的人也是更喜欢感觉的，所以大家不喜欢看这类文章，我也是理解的。毕竟情感和故事比逻辑更有趣)。比如，我用过很多次，有过很多体验，逻辑关系理解起来也很通透的一些经典方法。

还有一些是我刚见识和还未掌握的新方法和技巧，有新鲜感，会产生“居然还能这么玩”的想法。而且随着不断的练习和使用，当我对新方法有了更深的理解时，我还会多写几遍。

再有就是，对于同一个问题，我自己想出的和别人不同的方法，我也想写出来，就好像“这种方法是我想出来的，代表我活过”。尽管可能我的方法不是最好的，甚至是有点“滥”的。但是它有一种“活过”的感觉，所以感觉是很重要的。

反过来，对于一些我很少用的，但是需要按部就班写出来的函数，其实我是不怎么想写的，好像少了一种感觉，就只是我把别人的知识拿过来再传递给另一个人，这中间没有我自己的体验和理解，感觉“干巴巴的”。

以上是我对自己写文章的一个简单说明，比较偏向于个性化(如果不是个性化，估计我早就写不下去了)。所以有些函数我一直没写，比如数学函数和时间函数，可能会与大家的期望不符。如果你想学哪个函数，可以留言。再有一个个性化的地方就是我的大部分文章对于初学者来说可能有点难，对于高手来说又有点简单，所以excel中级用户可能会比较适合。

“废话”就说到这里，下面进入咱们今天的正题。筛选出每个人最高分的记录，数据源和完成后的效果如下图所示。其中，每个人的分数在1-100之间，每个人的最高分只有1个。比如“郭恺强”的记录有5条，分数分别是95，96，60，89，79。我们只筛选出最高分96和他的姓名。

我的思路是先求出每个人的最高分，然后用最高分和当前的分数比较是否相等，相等的就是最大值，返回对应的行号，再用small取出每个行号，最后用index返回对应的单元格内容，就是一对多的查询。

下面我们先用辅助列的方法做一下：在C列添加辅助列，C3单元格输入公式=MAX((A$3:A$17=A3)*B$3:B$17)，按ctrl+shift+enter三键结束，向下填充，得到C列的结果。这样的话就把每个人的最高分求出来了。比如“郭恺强”的最高分是96，“李红雯”的最高分是85。相信大家对这个公式没有疑问了，如果有疑问可以分步查看。

下一步就是用每个人的最高分(C列的分数)和ta当前的分数(B列的分数)比较，比如第6行“郭恺强”的当前分数是96，辅助列求出的最高分也是96，2者相等，说明这条记录就是我们要的。其他人相等的也是我们要的，所以让C列的分数和B列的分数作相等的比较，如果成立的返回对应的行号，不成立的返回4^8。如下图所示，在E3:E17输入公式=IF($C$3:$C$17=$B$3:$B$17,ROW($3:$17),4^8)，按三键结束。

再下一步就是用small提取出每个行号，如下图所示，公式为=SMALL(IF($C$3:$C$17=$B$3:$B$17,ROW($3:$17),4^8),ROW(A1))，三键结束，取得第1个最小值6，公式下拉取第2个最小值，再下拉取第3个，。。。一直取完。

最后用index返回对应列的单元格内容，如下图所示，公式为=INDEX(A:A,SMALL(IF($C$3:$C$17=$B$3:$B$17,ROW($3:$17),4^8),ROW(A1)))&""，按三键结束，向右向下填充，完成。最后&""是为了将空单元格返回的0变为空文本。因为一对多查询的方法说过好多次了，我就不再那么详细的说明每个步骤的意思了，主要是提供一个思路。大家如有不懂，可以查询之前的文章。

上面是用辅助列的方法，如果不用辅助列可不可以完成呢？这时有maxifs这个函数的小伙伴就得意了，不就是用maxifs数组的方式来代替上面的辅助列嘛？没错。maxifs是条件求最大值函数，和sumifs的用法类似，我这里就不具体说明它的用法了。直接给出不用辅助列的公式：=INDEX(A:A,SMALL(IF(MAXIFS($B$3:$B$17,$A$3:$A$17,$A$3:$A$17)=$B$3:$B$17,ROW($3:$17),4^8),ROW(A1)))&""，按三键结束，向右向下填充。

这个公式和上面辅助列的公式可以说是基本一样，只是用maxifs的结果代替了辅助列的结果。maxifs的第1参数是求最大值的区域，第2参数是条件区域，第3参数是条件，只不过这里的条件是一个数组，所以它返回的结果也是一个数组。

如果你说我的excel没有maxifs这个函数，我又不想用辅助列，能不能用其他函数来代替maxifs，得到每个人最高分的一个内存数组呢？这个问题你可以先想一下，下次咱们再说。

还有的同学，可能会想到用countifs来解决。countifs虽然不能求每个人的最高分，但可以对每个人的分数进行组内大小排名，不知你是否还记得。可以参考《有关排名问题的一些思考和总结》这篇文章。

用countifs来完成的完整公式为=INDEX(A:A,SMALL(IF(COUNTIFS($A$3:$A$17,$A$3:$A$17,$B$3:$B$17,">"&$B$3:$B$17)=0,ROW($3:$17),4^8),ROW(A1)))&""，按三键结束，向下向右填充。

=COUNTIFS($A$3:$A$17,$A$3:$A$17,$B$3:$B$17,">"&$B$3:$B$17)返回的结果如下图C列所示。其中0对应的就是每个人的最高分，所以让等于0的返回对应的行号，剩下的就是一对多的查询。

文件链接：

https://pan.baidu.com/s/1D07PONLwyaqaDdC4m6_TZQ

提取码：1hoo