首页
学习
活动
专区
工具
TVP
发布
精选内容/技术社群/优惠产品,尽在小程序
立即前往

Excel多维表格或多区域查找:可以采用R1C1地址提取方法

编按:在上下并排的多组横排表格中查找数据,VLOOKUP和XLOOKUP都办不到。这个时候尝试用R1C1地址提取往往有奇效!实际上这就是多区域查找,在同一个工作表中的多个区域中同时进行查找,类似跨表查找。

这是上下连排的多组并列数据,现在需要按姓名如“孙倩”“林菲”查成绩。

很显然,VLOOKUPLOOKUPXLOOUP都无法完成,除非把表格整理称标准的横排或者竖排一维表,如下。

这个时候,一个小配角,INDIRECT跳了出来唱大戏——他真的可以简单搞定这类查找。

直接上公式:

=INDIRECT(TEXT(MAX((A1:F11=H2)*(ROW(A2:F12)/1%+COLUMN(A:F))),"R0C00"),0)

公式解析:

获得行数:

ROW(A2:F12),得到一组行数{2;3;4;5;6;7;8;9;10;11;12}数组。 再除以1%,将每个行数值放大100倍,变成{200;300;400…1200}

获得列数:

COLUMN(A:F),得到一组列数{1,2,3,4,5,6}

两者相加得到一组11行6列的包含了行数和列数的数{201,202,203,204,205,206;301…1206}

 A1:F11=H2判断各数据是否等于孙倩,得到一组11行6列的数;很明显只有第7行的第6列是1,其他都是0。

将和中相乘,除开{201,202,203,204,205,206;301…1206}中第7行6列的806外,其他都变成了0。

用MAX取0和806中的最大值806。

用TEXT函数将806变成字符串R8C06,该字符串正好是我们曾讲过的R1C1地址样式。

最后用INDIRECT引用“R8C06”地址数据即可。

实际上它们就是多区域查找,只是没行列间隔。

下方各区域间有行或列间隔,甚至有错位,同样可行:

扩展应用:

一对多查多个相同姓名的所有成绩:

如果名单中有相同的姓名,则公式修改一下,即可实现一对多查询,把相同姓名的成绩都查出来。

=IFERROR(INDIRECT(TEXT(LARGE(($A$1:$F$11=$H$2)*(ROW($A$2:$F$12)/1%+COLUMN(A:F)),ROW(A1)),"R0C00"),0),"")

一对多查同一姓名的多个返回值:

做Excel高手,快速提升工作效率,部落窝教育Excel精品好课任你选择!

学习交流请加微信hclhclsc进群领取资料

相关推荐:

用R1C1样式地址跨表求和

跨表查找

怎么创建公式中常用的数列

图表坐标文字太长被斜着排,怎么扶正方便观看?

版权申明:

本文作者小窝;部落窝教育享有稿件专有使用权。若需转载请联系部落窝教育。

  • 发表于:
  • 原文链接https://page.om.qq.com/page/OORlXrdq1zgGlf0PNQqsH6Yg0
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

扫码

添加站长 进交流群

领取专属 10元无门槛券

私享最新 技术干货

扫码加入开发者社群
领券