本帖最后由 胡剑0227 于 2015-5-12 20:42 编辑
原发表于培训版块
INDIRECT浅析
好,我们现在就来认识一下INDIRECT这个函数。INDIRECT 英文含义为 “间接的、迂回的”,那么我们就想其对应的 “直接的,直奔目标”的又是怎么个表现呢?作为引用,最直接的方式就是 书写 引用地址(如,"a1,b$1,sheet1!c1"),那么间接的呢?简单的讲就是将这些 引用地址 套上双引号,然后再传递给INDIRECT函数,比如 a1-->"a1"--->indirect("a1"),如下图所示。
到这里也许大家会觉得这是多此一举。是,在上面这个例子中确实有点多余,但很好地诠释了INDIRECT间接、迂回的特性。不过更重要的是,我们发现INDIRECTt具有剥离 引号 的作用。牢记这一点对理清该函数实际引用的地址非常有帮助。我们看下图
我想对于第一个公式 =indirect("a1"),返回 "B1" 大家应该没有什么疑问,问题是=indirect(A1)返回"剥离引号",可能会有点混淆。虽然看着是这么回事,但有点不清晰。我们这么来做:=indirect(a1)=indirect("B1"),这样就明白了。(因为A1的值就是 "B1",这点要好好理解哟。如果会使用F9来计算值的话你会更加清楚的)
到这里,我想对函数的机制应该有点认识了。有同学可能会问,“有什么好处呀?”。好处就是智能、灵活。因为INDIRECT的参数是文本字符串,文本字符串可以灵活地进行组装。也就是说,一个目标地址可以通过多个单元格的内容来“生成”。我们还是来看一个图吧
从上图我们可以看到,INDIRECT函数引用的地址具有非常好的可控性。这一切都得益于参数可以由文本字符串组装着来生成。很多参数类型为字符串的函数都有这种灵活性的优点(比如SUMIF的第二参数)。
要组装这些目标地址必须先获知地址的表达格式,然后用上任何能想得到的方式来生成这个地址格式的文本形式。比如在上图中,地址格式为 "sheet1!b1" 这样的形式。如果我们对格式不确定时,最有效的方式就是用鼠标点选来提取正确的地址格式。这叫什么呢,投石问路。看图:
在上图中我们发现,当工作表表名称直接是数字的时,在工作表名称两边会添加上一对单引号。这一点平时不太容易注意,因此在实际使用时强烈建议使用这种投石问路的方式。
好了,到这里为止,我想INDIRECT函数应该算是基本掌握了。现在再谈谈他的引用方式问题。该函数的函数语法为 =indirect(ref_text,a1),当第二参数省略,即 =indirect(ref_text)形式的时候使用的是 A1 引用格式,当 指定引用方式 的参数a1=0时,那么就是用 RC的引用格式。RC格式,比如R2C2,就是ROW 2,COLUMN2,就是 B2 单元格。实际使用时可以多一个选择。看图
扩展:如果某个名称引用了某个单元格区域,我们使用这个名称的时候相当于在操作其所引用的区域于是猜想,这种返回引用区域的名称也是一种地址...于是可以使用这个公式=indirect("名称")来引用特定的单元格区域。
如果 名称使用 OFFSET等引用函数生成的,那么可以 定义一个名称 数据源=EVALUATE("名称"),这样这里的名称可以使用OFFSET等函数生成的区域引用了。
引用定义名称0227.rar
(8.12 KB, 下载次数: 5264)
=========================================================================================
恩,这句话确实说的很含糊,表达不清。早发现了,但没有去改...
里面出现多个名称,所以混了。
在数据有效性-序列中可以使用 区域引用(A1:A4),如果名称直接定义为 数据源=A1:A4,那么可以直接用名称。利用这点在多级菜单中非常有用。可以使用 =INDIRECT("数据源"),如果这里上一级菜单为 省,定义了名称 省=A1:A4(数据为该省的各个地市),且 目前 位于 B1单元格,需要在C1单元格中通过下拉选中该省的各个地市。那么可以通过在才C1的数据有效性序列中输入 =INDIRECT(B1) 来解决。
但如果 这个名称数据源是动态的 即 省=OFFSET(....),那么在C1有效性序列中输入 INDIRECT(B1)就不能达到预想的效果。但我们可以在有效性中输入 =EVALUATE(B1)来达到同样的功能。这是一个亮点
[ 本帖最后由 胡剑0227 于 2010-10-22 11:09 编辑 ]==========================================================================================================
INDIRECT函数,用迂回的方式引用数据源还带来一个极特别的优势:引用的数据源被删除时,公式可能返回#REF!,但公式本身内部由于没有直接引用数据源,因此不会在公式字符中产生#REF!错误,因此公式更加健壮。
举个例子吧,比如 sheet1是公式所在位置,sheet2是纯数据源,那么当SHEET2被删除时类似=SHEET2!A1的公式就会返回错误,不仅返回#REF!,而且公式也变成了 =#REF!A1,并且即使新增工作表并重新命名为sheet2时它也不会恢复。而=indirect("SHEET2!A1"),虽然在删除SHEET2时会返回#REF!,但重新造出一个SHEET2时它依然能正常发挥作用。这对于更新数据源来说就方便多了,可以整个工作表替换而无需复制粘贴了。
==========Excel函数实战技巧精粹================= 函数公开课群 246666938,进入群以后直接在聊天界面输入 #视频1001 可以下载公开课视频,不过建议直接输入 #报名 加入组织,那样毕业时你可以豪气万丈地对我说:“胡版,我当时连公开课都没听就直接报名了!!英明呀,回想起来真是痛快!“
|