.hd-box .hd-fr

Excel 中的高能函数 INDIRECT 介绍

2023-06-11 11:45秋叶Excel(小花)13评

原文标题:《用了十几年 Excel,这个高能函数我居然才知道,不要太好用!》

INDIRECT 函数是 Excel 中一个非常高能的函数,同时,它的语法还非常简单,仅有两个参数,且第二个参数还能缺省。

关于 INDIRECT 函数,绿水零老师在《偷偷学会这个小众高能函数,我再也没有加过班……》一文中已经进行了详细解读。

但即便如此,一旦实操,INDIRECT 函数依然是很多小伙伴的「梦魇」!

本文,小花就为大家剖析INDIRECT 函数的常见误区,相信定能为你一扫阴霾!

1、关于引用样式

问题来源某位粉丝的留言。

留言所指公式如下:

=INDIRECT("R"&MATCH(I3,A1:A11,0)&"C"&MATCH(I2,A1:F1,0),FALSE)

说实话,不止这位小伙伴,我们所有人几乎都习惯了 INDIRECT 函数省略第二个参数 a1 的样子,以至于它采用 R1C1 引用样式时,竟对面不识!

Excel 单元格地址的引用样式有两种:

A1 引用样式:用英文字母表示列号,数字表示行号,默认为相对引用,用 "$" 表示绝对引用;

R1C1 引用样式:用 R + 数字表示行号,C + 数字表示列号,默认为绝对引用,用 "[]" 表示相对引用。

二者对照关系如下:

留言所指公式中,参数 a1 是 FALSE,表示采用 R1C1 引用样式。两个 MATCH 通过匹配条件值出现的位置序数值,连接 R 和 C,构成完整的 R1C1 引用样式,INDIRECT 再根据该地址进行引用求值,最终完成交叉查询!

公式如下:

=INDIRECT("R"&MATCH(I3,A1:A11,0)&"C"&MATCH(I2,A1:F1,0),FALSE)

所以,即便绝大多数时候,INDIRECT 的第二个参数 a1 都是省略的,我们还是不能将它轻易遗忘哦!

2、关于引用地址

INDIRECT 可以正确处理的,只有代表引用地址的文本,绝大多数的 INDIRECT 函数应用错误都集中在引用地址文本的构建问题上。

下图列举了几种构建引用地址文本的方式。

简单来说,前四种,无论是直接将 INDIRECT 函数的第一个参数 Ref_text 设置为文本、单元格引用还是公式,只要最终 Ref_text 能够返回一个完整的、代表引用地址的文本,INDIRECT 函数就可以正确运算。

而第⑤种将需要引用的单元格 B1 直接作为参数 Ref_text,公式会先引用 B1 的值,得到 2,而数字「2」不是完整的引用地址,导致 INDIRECT 函数无法计算。

这与第②种情况直接将文本「B2」作为参数 Ref_text 不同,后者不会对文本「B2」进一步计算,文本「B2」即为引用地址

而第①种情况中引用 A1 单元格作为参数 Ref_text,公式先引用 A1 的值,得到「B2」,也可以正确计算。

第⑤种情况正是 INDIRECT 函数应用中的常见错误,你踩过雷吗?

3、关于单引号

使用 INDIRECT 函数进行跨表引用,是另一个错误的重灾区!

哪怕明明引用地址清楚明白准确,INDIRECT 函数还是无法计算!

这是为什么呢?

错误公式如下:

=INDIRECT("1 月 广州B2")

这是因为有些工作表名称中含有一些特殊字符,如空格、星号等,导致 INDIRECT 函数无法识别表名,这时候需要用单引号「 ' 」将工作表名圈定,INDIRECT 函数才能正确识别。

修正公式如下:

=INDIRECT("'1 月 广州'!B2")

那么怎么判断是否需要添加单引号呢?很简单,使用等号引用目标工作表的任意单元格,查看公式中是否包含单引号即可。

实际上,不需要单引号的情况使用单引号,也能够正确计算。

所以,当需要引用多个工作表时,一律添加单引号不失为稳妥之举!

4、关于跨多表引用

来看下面这个例子,小张需要计算特定三个城市中当日销售额的最大值,即要引用不连续的多个表格的同一单元格,再求最大值。

小张辛苦设置好了跨表引用公式,但结果却出错了,我们来看下出了什么问题。

跨多表引用错误公式如下:

{=MAX(INDIRECT($D$2:$D$4&"!B2"))}

通过数组运算使得 INDIRECT ($D$2:$D$4&"!B2") 根据表明分别引用惠州、佛山和东莞三张表的 B2 单元格,再使用 MAX 函数取最大值,这个公式似乎并无不妥之处。

BUG 出在 INDIRECT 跨多表引用的结果是一个多维引用,MAX 函数无法对这一多维引用进行运算,仅能返回第一个值,即惠州!B2,导致结果出错。

一般情况下,不使用特定函数,无法直接对多维引用进行运算。这些特定的函数包括 T 函数、N 函数、SUMIF 函数、SUBTOTAL 函数等等。

本例中,我们只需使用 N 函数将 INDIRECT 函数的多维引用结果转化为数值形式,MAX 函数就可以正确运算了。

跨多表引用修正公式如下:

{=MAX(N(INDIRECT($D$2:$D$4&"!B2")))}

INDIRECT 函数跨多表引用中的门道非常深,有兴趣了解的小伙伴可以期待小花后续的文章哦!

以上,就是小花拆解的 INDIRECT 函数常见误区,包括:

❶ 忽略参数 a1 导致无法理解 R1C1 引用样式下的 INDIRECT 函数;

❷ 错误构建导致参数 Ref_text 不是完整的、代表引用地址的文本;

❸ 没有添加单引号导致 INDIRECT 函数无法正确识别表名;

❹ 未使用特定函数处理多维引用结果,导致嵌套的其他函数无法运算。

看过本文的小伙伴,可不能再踩坑 INDIRECT 函数咯!如有其他小花未提及的 INDIRECT 函数常见错误类型,欢迎留言与我们交流哦!

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小花

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

下载IT之家APP,分享赚金币换豪礼
相关文章
大家都在买广告
热门评论
查看更多评论