不规范表格下正向和逆向查找，这里的方法比较好！

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

某学校组织了一次知识竞赛，下面是这次竞赛的最终参赛队员名单。

表格完成后，我们需要通过姓名来查找学号，以及，通过学号来查找姓名。由于表格录入并不是会规范，这给我们后续的查找统计公祖带来了一定的困难。

该如何操作呢？思考十秒钟...

01

处理这种结构表格，我们可以考虑使用INDEX函数。由于学号和姓名都是唯一的，因此只要找到等于给定条件数值所在的行和列，就可以找到对应的姓名

在单元格E7中输入公式“=INDEX(A1:Y20,MIN(IF(G5:W14=D7,ROW(5:14))),MIN(IF(G5:X14=D7,COLUMN(H:Y))))”即可

在单元格E11中输入公式“=SUMIF(H5:X14,D11,G5)”即可。

由于上面连个公式都比较简单，这里就不再详细介绍了。其中单元格E11中的公式使用了SUMIF函数错位求和和方法。关于这个的详细介绍请但看帖子总结篇--SUMIF函数实用终极帖。

02

其实，这个例子中根据学号查找姓名还可以通过多维引用的思路来解决。

在单元格E7中输入公式“=INDIRECT(TEXT(MIN(IF(G5:W14=D7,ROW(5:14)*100+COLUMN(H:X))),"R0C00"),0)”，三键回车即可。

思路：

  • IF(G5:W14=D7,ROW(5:14)*100+COLUMN(H:X))部分，将源数据区域中所有等于给定条件的数据的行号扩大100倍，同时加上对应的列号

  • MIN(IF(G5:W14=D7,ROW(5:14)*100+COLUMN(H:X)))部分，由于我们已知学号和姓名是不重复的，因此这里可以使用MIN函数。如果有多个等于给定条件的数据，则要使用SMALL函数

  • TEXT(MIN(IF(G5:W14=D7,ROW(5:14)*100+COLUMN(H:X))),"R0C00")部分，将结果转换为“R1C1”格式

  • 最后利用INDIRECT函数取得正确的结果

-END-

长按下方二维码关注EXCEL应用之家

面对EXCEL操作问题时不再迷茫无助

我就知道你“在看”

注意！前方有红包挡道！速点阅读原文消灭之！

推荐阅读
赞 (0)

相关推荐