凯发k8网页登录
领先车焦点 > 行情 > excel中的高能函数indirect介绍

excel中的高能函数indirect介绍-凯发k8网页登录

编辑:樊华   时间:2023-06-11 11:55  来源:it之家  阅读量:18760     

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

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

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

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

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

1、关于引用样式

留言所指公式如下:

=indirectamp;"c"amp;match(i2,a1:f1,0),false)

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

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

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

? r1c1 引用样式:用 r 数字表示行号,c 数字表示列号,默认为绝对引用,用 "" 表示相对引用。

二者对照关系如下:

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

公式如下:

=indirectamp;"c"amp;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

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

修正公式如下:

=indirect

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

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

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

4、关于跨多表引用

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

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

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

=max)

通过数组运算使得 indirect 根据表明分别引用惠州、佛山和东莞三张表的 b2 单元格,再使用 max 函数取最大值,这个公式似乎并无不妥之处。

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

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

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

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

=max))

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

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

? 忽略参数 a1 导致无法理解 r1c1 引用样式下的 indirect 函数;

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

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

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

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

郑重声明:此文内容为本网站转载企业宣传资讯,目的在于传播更多信息,与本站立场无关。仅供读者参考,并请自行核实相关内容。

网站地图