点击蓝字【秋叶 Excel】

发送【交流】

立即进【秋叶同学会】交流Excel!

本文作者:小花

本文编辑:竺兰

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

打开网易新闻 查看精彩图片

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

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

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

打开网易新闻 查看精彩图片

关于引用样式

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

打开网易新闻 查看精彩图片

留言所指公式如下:

=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 都是省略的,我们还是不能将它轻易遗忘哦!

打开网易新闻 查看精彩图片

关于引用地址

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 函数应用中的常见错误,你踩过雷吗?

打开网易新闻 查看精彩图片

关于单引号

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

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

这是为什么呢?

错误公式如下:

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

打开网易新闻 查看精彩图片

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

修正公式如下:

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

打开网易新闻 查看精彩图片

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

打开网易新闻 查看精彩图片

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

打开网易新闻 查看精彩图片

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

打开网易新闻 查看精彩图片

关于跨多表引用

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

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

多表引用错误公式如下:

{=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 函数常见错误类型,欢迎留言与我们交流哦!

除了今天介绍的 INDIRECT 函数,Excel 里还有很多的函数,比如 Vlookup、Xlookup、Sumif 等等。

如果你想学习更多关于函数的知识,更多 Excel 小技巧!

那我推荐你参加秋叶《3 天 Excel 集训营》,大神带你学习表格飞速排版、数据高效整理、图表美化设计……!!和志同道合的小伙伴一起交流进步~

秋叶《3 天 Excel 集训营》

但只要你是秋叶 Excel 的读者

就能限时1 元秒杀!!

仅需 3 天

你就可能成为 Excel 高手!

赶紧扫码抢课吧!!

优惠名额有限,先到先得!

现在扫码报名

还能免费领《35 个函数使用手册》!

打开网易新闻 查看精彩图片

遇到有价值的文章

点点在看支持一下 !

动动小手

分享给朋友~