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,就省略不写了,只写第一参数就可以了。

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

图1-1,A1单元格样式

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

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

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

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

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

图2-1

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

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

图2-2

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

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

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

图2-3

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

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

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

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

图3-1

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

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

图3-2

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

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

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

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

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

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

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

图3-4:函数不准确,引用失败

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

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

图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多表汇总引用正确动图展示:

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

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

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

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

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

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

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

图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数据。

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

图3-9:公式解析图

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

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

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

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

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

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


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

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

图4-3:区域定义名称

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

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

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

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

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注