在excel中你可能会认为vlookup的查找最牛X、Sumifs求和最牛X,但它们都可以用其他函数所替换。而有一个函数却无法用其他函数替代,高手写的公式中经常会有它的身影。它就是:Indirect函数
描述:可以把一个字符表达式或名称转换为地址引用。在excel也只有它可以实现这样的功能。
语法结构:=INDIRECT(ref_text,[a1])
解释:引用,并显示其内容,分为两种一种加引号一种不加。
用法举例:
从上面的图中大家有没有发现,INDIRECT函数说白了是一个间接引用函数,哈哈,INDIRECT本身的英文意思就是 “间接的、迂回的”,那聪明的你一定会想了,我直接引用就得了,干嘛要间接引用呢。这不多此一举吗?
不急大家先听我慢慢讲来。先来解释下上面用到的公式:
我想对于第一个公式 =indirect('a1'),返回 'B1' 大家应该没有什么疑问,问题是=indirect(A1)返回'谢先森',可能会有点混淆。虽然看着是这么回事,但有点不清晰。我们这么来做:=indirect(a1)=indirect('B1'),这样就明白了。(因为A1的值就是 'B1',这点要好好理解哟。如果会使用F9来查看公式的话,你会更加清楚的)
现在我能来回答有什么好处了,好处就是智能、灵活。因为INDIRECT的参数是文本字符串,文本字符串可以灵活地进行组装。也就是说,一个目标地址可以通过多个单元格的内容来“生成”。我们来看一下下面这个例子吧!
如果你想知道更多做多级列表的知识请点击动态列表。下面是接着上面的例子。
应用举例
一、制作动态下拉菜单
此例上面已经讲过,公众号里面也有相应知识。感兴趣的亲请自行查找!
二、多表合并
所用到公式为:=INDIRECT(COLUMN()-1&'月'&'!B'&ROW())
简单做下解释:COLUMN()-1&'月'&'!B'&ROW()运行得到的结果是{1月!B2}。COLUMN()根据列号生成数字{2,3,4,5,6,7},ROW()根据行号生成行号,最后一起生成单元格地址。
三、多表查找(VLOOKUP INDIRECT)
此例用到2种方法,推荐使用第1种方法,所用到公式为:
=IFERROR(VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({'销售';'服务';'人事';'综合';'财务'}&'!a:a'),A2),{'销售';'服务';'人事';'综合';'财务'})&'!a:g'),7,0),'无此人信息')
1、确定员工是在哪个表中。这里利用countif函数可以多表统计来分虽计算各个表中该员工存在的个数。
2、利用lookup(1,0/(数组),数组) 结构取得工作表的名称
3、利用indirec函数把字符串转换成单元格引用。
4、利用vlookup查找。
如果你要使用此公式,你只需要做以下更改:
A2:查找的内容
{''}:大括号内是要查找的多个工作表名称,用逗号分隔
a:a :本例是姓名在各个表中的A列,如果在B列则为b:b
a:g :vlookup查找的区域
7:是vlookup第3个参数,相对应的列数。你懂的。
四、多表求和(SUMPRODUCT INDIRECT)
例子中所用到的公式为:
=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$6)&'月!A2:A9'),A2,INDIRECT(ROW($1:$6)&'月!B2:B9')))
表亲们已经看到了,如果只有一个表,那只需要用SUMIF就可以了。但是如果需要多个表同时求和,就需要用到INDIRECT()函数引用多个表了。最后生成的数据是一个数组,于是需要SUMPRODUCT()函数来进行求和了。
如果你想拿这个函数来用,你知道怎么改吗?有问题请留言!这些例子只一些简单的知识点我就没有详细讲解了,因为在公众号里都可以找到的。最后还请大家帮我宣传宣传公众号。
联系客服