搞懂最难的excel中的INDIRECT函数；Excel跨表汇总,二级多级下拉菜单

Excel的INDIRECT函数很强大，但却是最抽象、最难懂的？不存在的，只要记住这一句公式口诀，就可以很清楚的了解这个函数的工作原理，我相信小伙伴们看了这篇函数的介绍，不会再迷惑。

◆函数的大概功能介绍（只包含了一部分）

1. INDIRECT函数可以跨工作簿、跨表将多张工作表的内容汇总在一张表格上；

2. INDIRECT函数可以做二级及二级以上的多级下拉菜单（案例在本文第四大段）；

3. INDIRECT函数可以和INDEX、MATCH、VLOOKUP、SUMIF等各种函数灵活组合。

▍ 一、INDIRECT函数参数介绍：

它是间接引用函数，原公式参数2个：INDIRECT（引用的单元格，单元格样式类型）。注意：第二参数可不写，以后直接写成 INDIRECT(引用的单元格)，一个参数就够了。原因如下：

单元格样式类型有两种，第二参数输入1就是选择A1单元格样式；输入0就是选择R1C1单元格样式。如图1-1：选中A1单元格，左上角会显示A1，这就是 A1单元格样式（几乎都用这种）；而如果是R1C1样式，则选中A1单元格，左上角会显示R1C1（几乎不用，因为很不直观）。在函数公式里，最后一个参数是1的话可以省略不写的，所以我们以后都默认第二参数是1，就省略不写了，只写第一参数就可以了。

图1-1，A1单元格样式

▍ 二、INDIRECT函数基础案例介绍：

INDIRECT函数之所以会把人绕晕，是因为这个函数功能是间接引用地址的内容（单元格如A1、C5就是地址；除此之外，工作表名称和工作簿名称也可以作为这个函数的地址）。

▼ 案例1，如图2-1：A1单元格内容为 一月，B1单元格内容为A1。在B3单元格里输入=INDIRECT(B1)，结果显示“一月”，而不是A1。这就是INDIRECT函数的间接引用功能。

=INDIRECT(B1)，默念公式口诀：引用B1单元格里A1地址的内容，A1地址的内容是“一月”，所以结果是“一月”。

图2-1

▼ 案例2，如图2-2：B5单元格内容为100，D2单元格内容为B5，E4单元格输入公式=INDIRECT(D2)，默念口诀：引用D2单元格里B5地址的内容，B5地址的内容是100，所以结果为100。

图2-2

▼ 案例3，如图2-3：E4单元格输入公式=INDIRECT(“D2”)，因为公式里给单元格加上了双引号，则口诀失效，表示直接引用D2单元格里的内容，结果显示B5。当函数参数是文本时，如=INDIRECT（“一月”），因为一月是文本，所以参数要加双引号，不然公式会错误。

注意：函数E4=INDIRECT("D2")表示永远只识别D2单元格里的内容，如果因为插入行D2变成D3，那它也不会识别D3，而是继续识别新D2单元格里的内容，这是和E4=D2不一样的地方，可以自己插入行插入列试试。

图2-3

如果这个还不理解的小伙伴只能再多看几遍，因为后面的跨工作簿引用，跨工作表引用，多张表格内容汇总为一张表格，都会用到这个函数的地址引用功能。

▍ 三、跨工作簿或跨工作表，将多张表格内容汇总为一张表格：

▼ 案例1，跨工作表多表汇总，如图3-1：将一月、二月、三月这三张表的数据全部汇总到一张汇总表上。这三张A列框选出来的名字顺序必须要一模一样，所以要求前期做表的时候要规范，要有规律，这样才方便函数引用（其中三月的A7单元格多一个名字，这个在后面的“备注”段落里有解释）。

图3-1

3-1、我们在“三月”表格后面新建一张表格，叫汇总表。在A列输入名字，在第1行输入一月，二月，三月。如图3-2：

图3-2

备注：汇总表的A列姓名顺序一定要和前面的几张表格一模一样。我们这里选择复制“三月”表格里的姓名，因为“三月”表格A7单元格多了一个姓名，A1到A6是一模一样的，所以选择“三月”表格的姓名列不会出现少统计一个人的情况。只要有一张表格A1到A6的人名顺序不一样，引用数据就会不准确，必须重新整理好姓名顺序。

3-2、现在开始在汇总表输入INDIRECT函数公式，将多张表的内容汇总到一张表上。初次使用这个函数很可能会书写错误，这里我们分步讲解，利用错误的公式，修改为正确公式，避免以后进入误区。

〓分步讲解-1：如动图3-3，在汇总表的B2单元格里输入=INDIRECT(一月!B2)，结果是错误的。因为(一月!B2)括号里的内容是属于文本型内容，在函数公式里出现文本都要加双引号，B2单元格里的公式应该改成=INDIRECT("一月!B2")。"一月!B2"的意思是直接引用一月表格B2地址的内容。除了单元格，工作簿名称和工作表名称也是可以成为地址的，这个写法要记住。

图3-3：INDIRECT函数错误写法

〓分步讲解-2：如图3-4，加了引号，公式正确了，=INDIRECT("一月!B2")也显示结果101了，但是为什么向下，向右填充引用失败，内容不会变？

图3-4：函数不准确，引用失败

〓分步讲解-3：上面的这条函数还要继续修改，因为函数=INDIRECT("一月!B2")的"一月!B2"是文本，文本是不会变的，而单元格会随着拖动变动数字，所以要把函数的文本替换成单元格，这就要用到INDIRECT函数的地址引用功能。如图3-5：

图3-5：函数替换步骤详解

=INDIRECT(B$1&"!B"&ROW())这个函数向右拖动就会变成=INDIRECT(C$1&"!B"&ROW())，因为C1单元格的内容是“二月”，二月也是工作表名称，工作表名称也是地址，所以默念口诀：引用C1单元格里二月B列第ROW行地址的内容。

ROW（）函数很简单，简单介绍下：在任意单元格输入=ROW(A5)，结果会显示5，表示第5行；=ROW(B5)，结果也是显示5，所以ROW函数只显示行数，跟A列还是B列没有关系。如果括号里没有参数，比如你在D13单元格里输入=ROW() ，则结果显示13，表示输入公式的当前单元格所在行数。ROW函数还可以进行加减乘除四则运算，如任意单元格输入=ROW(A6)-2，结果为4。ROW(A6)/2，结果为3。

INDIRECT多表汇总引用正确动图展示：

图3-6：INDIRECT多表汇总正确案例

▼ 案例2，跨工作簿多表汇总。在工作簿“表2”里新建汇总表，把表1工作簿的一月、二月、三月内容引用过来。如图3-7：

图3-7：跨工作簿汇总引用

因为跨表、跨工作簿的函数公式比较长，容易写错，建议大家先把错误的公式弄出来，再在错误的公式里修改。如动图3-8：

图3-8：INDIRECT函数跨工作簿多表汇总

公式解析图3-9：在B3单元格里输入=INDIRECT("[表1.xlsx]"&B$2&"!B"&ROW()-1)。这里用ROW（）-1是因为这张表格的数据是从B3单元格开始，而表1工作簿的一月表格它们数据都是从B2开始，所以在B3单元格输入ROW()-1就是3-1=2的意思，等于错行引用上一行数据，B3引用B2数据，B4引用B3数据。

图3-9：公式解析图

▍ 四、INDIRECT函数经典功能：制作二级下拉菜单，或者更多级下拉菜单。这里做一个四级下拉菜单的案例，二级下拉菜单都是一样的操作方法。根据A1：D7的信息，整理出一份如图3-10的格式表格。

图4-1，整理菜单的上下级

▼第一级菜单制作，选中A9：A13，点击“数据”菜单—选“数据验证或数据有效性”—选设置里的“序列”—区域选G1：H1，确定，一级菜单做好了。如图3-11：

图4-2：第一级菜单制作步骤

▼区域定义名称，在制作二级及二级以上下拉菜单的时候，就要先对某些区域定义名称。选中G1：H2，点“公式”菜单的“根据所选内容创建”，选首行，点确定，就会浙江省和江苏省建立一个包含地级市的文件夹。选中G4：H7，因为有空单元格，所以按F5，定位选“常量”，就会不选中空单元格，也接着一样的操作。选H9:：H12也是如此操作，建立名称。在“公式”菜单的“名称管理器”里能看到刚刚新建的五个文件夹。如图3-12：

图4-3：区域定义名称

▼制作二级或多级下拉菜单。选中B9：B13，点“数据”菜单—“数据验证或数据有效性”—设置的“序列”—在区域里输入=INDIRECT(A9），点确定，如果出现错误提示框，点“是”。选中C9：C13，一样操作，就是在区域框里输入=INDIRECT(B9)。选中D9：D13，一样操作，在区域框里输入=INDIRECT(C9)。这样四级菜单就设置完成了。如图3-13

图4-4：制作二级及多级下拉菜单

以上是INDIRECT函数的两种用法，一种是跨工作簿、跨工作表多表汇总，另一种就是制作二级及多级下拉菜单。因为篇幅原因，后续再发布一篇关于INDIRECT函数和INDEX、MATCH、VLOOKUP、SUMIF等各种函数组合使用的案例文章。

搞懂最难的excel中的INDIRECT函数；Excel跨表汇总,二级多级下拉菜单的下载地址：

• 本地下载