利用indirect函数的R1C1形式进行多表查询汇总.docx

1、利用indirect函数的R1C1形式进行多表查询汇总利用indirect函数的R1C1形式进行多表查询汇总 多表查询汇总可以使用数据透视表进行，也可以使用导入外部数据结合sql语句将各个表连接在一起进行汇总，如果只是做查询汇总，最高效和直观的方法是通过indirect函数实现的，这里用到两种嵌套函数的方式，其中第二种R1C1的形式是最容易理解的，也是最便捷的，在下面的实例中在设置完函数之后通过屏蔽零值，再利用条件格式设置非空单元格具有特定条件进一步完善查询汇总表。方法/步骤如下图显示的工作薄中有办公室、技术部、人力资源部、销售部四张工作表，每个表中存放的是各个部门的日常费用数据，包括日期、费

2、用项目、金额、经办人这4个字段，现在需要根据不同的部门将各个字段对应的数据进行汇总为一张查询汇总表。查询汇总表最终需要在b1单元格中选择部门，然后下方对应字段下会根据选择的部门自动将数据显示在汇总表中，此时选择部门对应的是办公室，可以直接选中b4单元格，然后输入一个等号，然后点选办公室表中的a2单元格，然后将公式向下复制，但是如果这样操作，当部门名称发生变化时，下面的明细数据需要根据部门的变化发生变化。如果使用index函数，首先需要将第一个参数通过点选设置为的a列，要取的行数在办公室表的第二行，而公式所在单元格为汇总表的第四行，所以可以将第二个参数设置为row（）-2，作用是通过row函数算

3、出当前单元格行号，然后减去2是因为在汇总表中比引用表中多了两行。1. 4在b1单元格中需要通过设置数据有效性来实现部门的选择，选中b1单元格，然后单击菜单栏数据命令，在弹出的菜单中点按有效性命令，在弹出的数据有效性对话框中选择设置选项卡，然后在允许下拉列表中选择序列，然后在来源中输入四个部门的名称用半角都好分隔开，然后点按确定，此时就可以通过技术部对应的下拉按钮来选择不同的部门了，作为查询依据的部门就可以按需要变动了。现在选择a4单元格，然后在编辑栏中将原来的函数修改为=INDEX(INDIRECT($B$1&!A:A),ROW()-2)，这里我添加了一个indirect函数作为index函数

4、的第一个参数，用绝对引用下的b1单元格替换了原来的工作表名称，然后用一个连接符号&连接起了了index对应的第一个相应工作表中的区域的字符串，点按回车完成公式的复制，并向下复制公式，然后将公式向右复制，由于index函数的首个参数的引用区域被限制为字符串形式，因此当公式由左向右复制时候，对应的引用参数的列区域不能发生对应的变化，如果汇总字段比较少，可以通过手动更改，但是如果字段较多，这就比较麻烦了，而且公式也不具有灵活性。2. 6在indirect函数的语法结构是这样的语法INDIRECT(ref_text,a1)Ref_text 为对单元格的引用，此单元格可以包含 A1-样式的引用、R1C1

5、-样式的引用、定义为引用的名称或对文本字符串单元格的引用。如果 ref_text 不是合法的单元格的引用，函数 INDIRECT 返回错误值 #REF!。如果 ref_text 是对另一个工作簿的引用（外部引用），则那个工作簿必须被打开。如果源工作簿没有打开，函数 INDIRECT 返回错误值 #REF!。A1 为一逻辑值，指明包含在单元格 ref_text 中的引用的类型。如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。之前嵌套函数使用的是A1这种方式的引用，下面我将使用R1C1这种

6、引用方式直接通过indirect函数来取得一一对应的字段的数据，这样就省去了index函数，下面第一个图对应的是用indrirect函数的A1形式并将公式向右侧复制，依然不能实现预期效果，第二个图对应的是indirect的R1C1效果，达到了预期的效果，六个箭头从左到右侧指向的分别是区域、R、1、C、1、和第二个参数0，此时公式向右侧复制，可以到达预期的效果。3. 7此时将完整的公式=INDIRECT($B$1&!R&ROW()-2&C&COLUMN(),0)输入到a4单元格中，然后将公式向右侧复制，然后再将公式向下复制，此时对应部门对应字段中的数据军都通过公式显示在对应的位置了，但是下面第二

7、个截图中的红框内的数据为0值（日期格式下的0值），我们需要将其屏蔽掉。4. 8首先将原来设置在a4单元格中的公式选中，然后点击右键，在弹出的菜单中选择剪切，然后在公式中输入if函数=if(原来的公式=0,原来的公式)，然后将刚才剪切的内容INDIRECT($B$1&!R&ROW()-2&C&COLUMN(),0)，粘贴到if函数中对应的位置，完成的函数为=IF(INDIRECT($B$1&!R&ROW()-2&C&COLUMN(),0)=0,INDIRECT($B$1&!R&ROW()-2&C&COLUMN(),0)，原来函数应粘贴到的对应的位置如第二个截图下箭头指向所示，然后再讲完整的函数复

8、制到对应区域，此时的函数已经屏蔽掉了0值。5. 9选中红框所示的区域，尽量向下多选一些行，然后点按菜单栏格式命令下的条件格式命令，在弹出的条件格式对话框中条件下设置为公式，然后在公式框中输入=A4，然后点按格式按钮，然后在弹出的对话框中设置边框，然后点按确定，返回条件格式后再次点按确定。6. 10此时通过改变选择部门后的部门，可以看到随着部门的变化，不但下面的数据发生了变化，而且边框的范围也在发生变化，这是由于设置了条件格式，如果单元格区域内为非空得条件成立，该单元格才会添加边框，最后将费用项目和经办人设置为居中格式，金额设置为对应的数字格式后的效果如下。END注意事项 在设置数据有效性的时候，序列中的项目比较多，可以定义一个名称，然后将名称输入到来源中；如果序列的项目比较少，可以直接将各个项目输入到来源中，并通过半角逗号作为间隔 在使用indirect函数时候如果省略第二个参数1，此时函数将应用A1形式，如果第二个参数为0，此时不能省略该参数，函数将应用R1C1的形式。