Indirect函数的应用(实例详解)

INDIRECT函数的使用Excel帮助中的语法：INDIRECT(ref_text,引用方式)其中ref_text为对单元格的引用，可为文字串或返回字符串的表达式，可以包含A1-样式的引用、R1C1-样式的引用、定义为引用的名称或对文字串单元格的引用。如果ref_text不是合法的单元格的引用，函数INDIRECT返回错误值#REF!。引用方式为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果引用方式为TRUE或省略，ref_text被解释为A1-样式的引用。如果引用方式为FALSE，也可以用0表示，ref_text被解释为R1C1-样式的引用。特别注意：如果ref_text是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。如果源工作簿没有打开，函数INDIRECT返回错误值#REF!。此函数返回的是ref_text文本串所指定的单元格区域引用。关键是ref_text表达式应返回一个字符串A1样式引用：就是最普通的excel窗口中上方的“A、B、C、…”列标，右边的“1、2、3、…”行号所对应的单元格引用如C4即指对第C列（第3列）第4行的单元格引用。R1C1样式引用：就是在“工具》选项》常规”中设置了“R1C1引用样式”后，窗口上方的列标变为“1、2、3、…”后所指的引用。如上面的A1样式的C4在R1C1样式中就变为R4C3，其中R4指第4行、C3指第3列。提示：用INDIRECT函数时，用什么样式引用与“工具》选项”中对引用样式的设置无关(有限制的)。也就是设置为A1样式引用，INDIRECT函数中照样可以用R1C1样式的引用，反之也行。下面作一些详细的分析，供大家参考A.对本表的一个单元格的引用INDIRECT(E23)省略了引用样式，所以是A1样式引用，文字E23就表示对E23单元格的引用，结果如下R23C7G23=INDIRECT(E23)G2312345INDIRECT(E23)同样是A1样式引用，但此处的参数E23并不是文本串而是指E23单元格中的文本G23，所以返回对G23单元格的引用。12345=INDIRECT(E23)以上两式用R1C1样式可这样写G23=INDIRECT(R23C5,0)12345=INDIRECT(E22,0)上面右边的公式为什么用=INDIRECT(E22,0)？因为目前的设置是A1样式引用，E22必须用A1样式，而E22格中的文本用R1C1样式。提示：看INDIRECT函数到底是对哪个单元格的引用？可选中INDIRECT函数的第一个参数，按F9键，看那个文本是指那个区域。ref_text参数到底以什么样式写？如是直接的字符串，两种方式均可以，只要和第二个引用方式参数相一致；如ref_text参数是一个字符串计算表达式，那么这个表达式中的单元格引用应与当前“选项”中的引用设置相一致，然后再根据这个表达式的结果字符串来决定用相一致的第二个引用方式参数。B.跨表的一个单元格的引用只要在ref_text中加上工作表名即可，公式为INDIRECT(工作表名!单元格引用,引用方式)300=INDIRECT(Sheet2!B4)Sheet2!B4或300=INDIRECT(G35)Sheet2!R4C2R1C1样式引用300=INDIRECT(Sheet2!R4C2,0)300=INDIRECT(G36,0)那么下面的公式怎么错了？表B!B4#REF!=INDIRECT(表B!B4)#REF!=INDIRECT(J39)因为“表B”中有空格，excel规定当表名有空格时必须用'号将表名括起来。此问题与半榻茶烟版主讨论过，开始以为在INDIRECT函数中ref_text已是字符串，可不用'号，最后的结论还是要加'号。400=INDIRECT('表B'!B4)400=INDIRECT(J43)'表B'!B4提示:不要忘了用'号将表名括起来，否则可能出了问题也找不到原因在哪儿，加了'号对所有的表名(不管是否有空格)均适用。C.对一个单元格区域的引用对“表B”A1:B4区域的引用，用多单元格数组公式返回在一个单元格区域中。怎么输入多单元格数组公式姓名工资{=INDIRECT('表B'!A1:B4)}王五500赵六200李四400当然直接在函数中用一个固定的文本来对区域引用的意义不大，实际使用中是要对引用参数用表达式计算的。选择表表B姓名工资{=INDIRECT('&B52&'!A1:B4)}选择姓名李四王五500工资400赵六200B54格的公式=IF(ISNA(VLOOKUP(B53,INDIRECT('&B52&'!A:B),2,0)),,VLOOKUP(B53,INDIRECT('&B52&'!A:B),2,0))李四400或者，继续计算，如求指定表的工资总额1100=SUM(INDIRECT('&B52&'!A:B))提示:INDIRECT函数可以返回对整列引用，当然也可以返回对整行引用。以下部分涉及INDIRECT函数的三维引用，对数组不是特别感兴趣的朋友可不看D.一个数组元素参数的单元格区域引用为什么下面这样就不行了？公式为{=INDIRECT('表B'!A1:B&ROW(4:4))}，不用数组方式输入也出错！怎么输入多单元格数组公式#VALUE!#VALUE!按理ROW(4:4)返回4，'表B'!A1:B&ROW(4:4)表达式的结果为'表B'!A1:B4#VALUE!#VALUE!最后的公式与A47:B50区域的相同，均为{=INDIRECT('表B'!A1:B4)},在单元格区域中的结果应该相同。#VALUE!#VALUE!其实ROW(4:4)返回的不是4，而是一个元素的数组{4}，这样ref_text表达式计算的结果就#VALUE!#VALUE!不是'表B'!A1:B4，而是一个字符串元素的数组{'表B'!A1:B4}，选中某个黄色区域公式单元格，在公式编辑栏中选择INDIRECT函数的参数表达式，按F9键就可看到。提示：INDIRECT函数的参数如果为一个单元素数组时，而这个数组的元素又表示一个单元格区域时(不是一个单元格)，返回的是一个三维的数组，就不能在工作表的单元格中全部显示出来，但不影响继续计算。如：按姓名查找工资姓名工资对照A47:B50区域，正确！赵六200=VLOOKUP(E69,INDIRECT('表B'!A1:B&ROW(4:4)),2,0)E.INDIRECT(71:72)与INDIRECT(71:72)的区别表三!B2表三!B3表三!B4表三!B5表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2biao4!B2biao4!B3biao4!B4biao4!B5biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2INDIRECT(71:72)返回对71、72两行的引用，结果如下：怎么输入多单元格数组公式表三!B2表三!B3表三!B4表三!B5表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2表三!B2biao4!B2biao4!B3biao4!B4biao4!B5biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2biao4!B2INDIRECT(71:72)这样的用法往往被认为是错误的，其实可以这样用，返回对71、72行单元格中值所指定的单元格的引用，但返回的是一个三维数组，可用N函数(对数值)转化为二维数组，请看：400200100800400400400400400400400400400400300200300500300300300300300300300300300300其实没人要这么用，但可用SUM函数对71、72行单元格中值指定引用求和179200结果正确！F.多个数组元素参数，每个参数为单个单元格的引用ABC1怎么求出左边单列区域偶数行的值？ABC1怎么输入多单元格数组公式ABC2可能很快就会想到用INDEX函数，见右面。11但这个公式只能在单元格区域中显示数组的各个元素，而不是ABC52一个我称之为内存数组的公式，可检验如下4ABC5再外套一个MAX函数求最大值0错误，应该为4ABC93所以我说右面公式返回的不是一个内存数组，没法再继续数组运算。4用INDIRECT函数就可以做到，为简化说明问题，已将判别偶数行的部分去掉了ABC8ABC1{=IF(T(INDIRECT(A&80+ROW(1:5)*2))=,N(INDIRECT(A&80+ROW(1:5)*2)),T(INDIRECT(A&80+ROW(1:5)*2)))}ABC91检验求出的最大值ABC54正确4ABC9提示：因为INDIRECT函数的参数是数组，所以返回的是三维的数组，不能直接在单元格区域中显示，可用T和N函数转化为普通的数组，转化原则是T函数对文本，如是数值就返回空字符；N函数对数值，如是文本就返回0。所以再用IF函数判别情况分别返回。进一步的说明，如INDIRECT函数的每个数组元素如指一个单元格区域，那么T和N函数只对区域的第一个元素进行转换并返回，本例中刚好INDIRECT函数的数组元素均指对一个单元格的引用，所以把满足条件的引用全返回了。G.最复杂可能也是最需要的多个数组元素参数并且为多个单元格区域引用问题：怎样在指定的工作表间按指定的姓名求出工资总和？起始表结束表姓名工资总额关于工作表名的几个宏表函数名称定义Sheet2biao4李四#N/A定义了相关名称后，公式就简单了，{=SUM(IF(usedsheet,SUMIF(INDIRECT(usedsheet&!A:A),C101,INDIRECT(usedsheet&!B:B))))}IF函数省略了第3个参数，当usedsheet的元素是空时返回FALSE，SUM函数计算时忽略FALSE，提示：INDIRECT函数中的数组元素所指的引用是各表的A列或B列区域，返回的是三维的多单元格区域数组，不要想对这样的INDIRECT函数引用在工作表的单元格中返回所有的值，好在excel中有些函数可在内存中对这个三维的区域数组进行计算，SUMIF、COUNTIF正是可用的函数。还有没有，我没找到！以上对INDIRECT函数的分析可能太抽象了，希望对大家有所启发，除非你不想深入了解函数，否则就要清楚INDIRECT函数的运算规则。祝新年快乐！chenjunExcelhome.net2004年元旦附注A.怎么输入多单元格数组公式选中一个单元格区域，不加{}号输入公式，按ctrl+shift+enter三键结束姓名工资看单元格的公式是否为多单元格数组公式，可选中一个公式单元格王五500按ctrl+/就可以选中整个多单元格数组公式区域，如提示赵六200未找到单元格，那就不是多单元格数组公式。李四400参见链接的例子数组的特殊用途返回附注B.关于工作表名的几个宏表函数名称定义在“插入》名称》定义”中定义下列名称使用了excel4.0的宏表函数，用31是因为表名最长为31字符所有表名的行数组sheetlist=MID(GET.WORKBOOK(1),FIND(],GET.WORKBOOK(1))+1,31)INDIRECTSheet2表B表三biao4本工作表名thissheet=MID(GET.DOCUMENT(1),FIND(],GET.DOCUMENT(1))+1,31)起始表firstsheet=INDIRECT!$A$101结束表lastsheet=INDIRECT!$B$101需使用表名行数组usedsheet=IF((sheetlist=thissheet)+(MATCH(firstsheet,sheetlist,0)MATCH(sheetlist,sheetlist,0))+(MATCH