可能这是解决INDIRECT作为易失性函数存在的短板的一种方法

瓶砸

最近一直被一个烦恼所困扰着，自己使用VLOOKUP函数所引用的数据来自生产计划8月和生产计划9月两张表格。
而我希望可以从计划单的字符串中提取出月份，从而从相对应的表格中去寻找数据。
计划单号的格式为JG20180917-1，我用FIND函数做了一个条件判断，如图。

在被查找的表格中，B列为计划单号，G列为需要引用的数据列。

基于以上这些，我一开始写了这样一个函数。
=IFERROR(VLOOKUP(N2,INDIRECT("'G:\文件夹\[生产计划"&TEXT(MID(N5,7,2),0)&"月.xlsx]"&TEXT(MID(N5,7,2),0)&"月'!$B:$G"),6,),"")
然而这么一来有个弊端是需要打开源文件才能显示出数据。

后来想着将整个文件名作为条件判断的结果是否可行，于是又写了这么个函数。
=LOOKUP(,-FIND({201808,201809},N2),{"'G:\文件夹\[生产计划8月.xlsx]8月'!$B:$G","'G:\文件夹\[生产计划9月.xlsx]9月'!$B:$G"})

结果折腾到后面发现这样写返回的公式结果是文本形式的，并不能作为VLOOKUP函数的第二参数，如果要使其生效，依然要加上INDIRECT函数，如下。
=VLOOKUP(N2,INDIRECT(LOOKUP(,-FIND({201808,201809},N2),{"'G:\文件夹\[生产计划8月.xlsx]8月'!$B:$G","'G:\文件夹\[生产计划9月.xlsx]9月'!$B:$G"})),6,)
这样一来，依然是需要打开源文件才能显示出数据。

于是，就去百度找有什么可以替代INDIRECT函数的函数，结果除了VBA，貌似没有找到其他可行的方法，也有人说用INDEX函数，不过以我目前的水平还无法让引用的表格名称变得像加了INDIRECT函数时候的那么灵活。
那么，难道剩下的方法就只有采用链接外部数据的方法创建一个副本到函数所在的工作簿了么？方法是可行，操作起来还是觉得麻烦。
后来查了VLOOKUP函数的百度百科，看看其有无其他我不知道的用法，看到这么一段话：

VLOOKUP函数的工作原理：
1．含有VLOOKUP函数的工作表，每次在保存档案时，会同时保存一份其外部连结的档案。这样即使在单独打开这个工作表时，VLOOKUP函数一样可以抓取到数值。
2．在工作表打开时，微软会提示你，是否要更新远程参照。意思是说，你要不要连接最新的外部档案，好让你的VLOOKUP函数抓到最新的值。如果你有足够的耐心，不妨试试。
3．了解到这点，我们应该知道，每次单独打开含有VLOOKUP函数的工作表时，里面抓取外部档案的数值，只是上次我们存盘时保存的值。若要连结最新的值，必须要把外部档案同时打开。
所以，我们是否可以用VLOOKUP函数创建一份档案呢？经过尝试，发现是可行的。


我先输入在本文一开始我所提到的公式，本质是一样的，详细参数会略有不同，并向下填充。截图如下。

可以看出，当源文件表格没打开的时候，整列数据行都为空白。
接下来，我们在O4输入
=IFS(ISNUMBER(FIND(201808,N4)),VLOOKUP(N4,'G:\金工车间\[金工车间生产计划8月.xlsx]8月'!$B:$G,6,),ISNUMBER(FIND(201809,N4)),VLOOKUP(N4,'G:\金工车间\[金工车间生产计划9月.xlsx]9月'!$B:$G,6,),1,"")
然后回车，所有的数据就都显示出来了，如图所示。

而且这些数据当你按F9进行重新计算或者重新打开文件时候都是存在的。

这样一来，我的问题就暂时解决了，以后如果发现这个做法有什么BUG的话，我会另外补充的，也欢迎大家和我一起讨论。
另外，有兴趣的可以尝试下INDEX+INDIRECT是否可以用这个思路解决。
最后弱弱问一句，不知，我废话了这么多，你们看得懂我想表达的意思么？

TolerLine

没太看明白这个公式，我也有这方面的需求，不知道可否详细说说这个公式的嵌套规则，不知道你这个公式中原来indirect函数工作簿和sheet表可以设置为单元格并做变更的功能是否可以实现呢？

瓶砸

Toler如果有附件的话，可以上传附件看看，再做个模拟效果，这样理解起来更简单些。

瓶砸

今天做了个小测试。首先，新建一个工作簿，命名为测试-源文件，再新建两张工作表。

将其命名为8月。

将其命名为9月，标黄的为重复项。
再另外新建一个工作簿，将其命名为测试-链接文件。
随意输入几个编号，再在B2输入并左右填充
=INDEX(INDIRECT("'[测试-源文件.xls]"&TEXT(MID($A2,7,2),0)&"月'!B:B"),MATCH(A2,INDIRECT("'[测试-源文件.xls]"&TEXT(MID($A2,7,2),0)&"月'!$A:$A"),))
结果如图

然后把B2的公式修改为
=IFS(--TEXT(MID($A2,7,2),0)=8,VLOOKUP(A2,'C:\Users\Administrator\Desktop\[测试-源文件.xls]8月'!$A:$C,2,),--TEXT(MID($A2,7,2),0)=9,VLOOKUP(A2,'C:\Users\Administrator\Desktop\[测试-源文件.xls]9月'!$A:$C,2,),1,"该单号不存在")
整张表格的数据就都显示出来了。

源文件有无打开对该表格并无影响。

祝洪忠-

谢谢分享  收藏学习了

瓶砸

从上面可以看出，其有一种特性为：
只需要在任一单元格区域内使用VLOOKUP函数生成数据库，那么该工作表中凡是涉及到了这片数据区域的函数都可以共享这个数据库。
那么，如果不是在一个工作表内呢？
于是我又在Sheet3新建了一个同样的表格，依然用之前的INDEX函数填充，结果如图。

结果显示，VLOOKUP函数的数据库是一个工作簿共享的。
那么这批数据会不会受到VLOOKUP函数的特性影响呢？例如只能查找符合条件的第一个值。
于是我又尝试了下在这个工作簿做了次一对多查找。
如图。

结果显示，也是可行的。

当然，这么做也是有短板的。
在尝试过称中， 我发现，如果工作表名称是不规格的字符串，虽然INDIRECT函数可以直接引用单元格，但如果用IFS(数据列区域=工作表名称，VLOOKUP(,相对应工作表区域,,),,)这样的格式来引用的话，工作量就会十分巨大。所以也还是存在有一定局限性的。

uneye2

回4楼

任意单元格直接：

='C:\Users\Administrator\Desktop\[测试-源文件.xls]8月'!A1

不打开源文件的情况下，是不是其他对该源的任何方式引用都生效。

瓶砸

uneye2 发表于 2018-9-19 14:44
回4楼

任意单元格直接：

感谢回复，这个的话只能引用单一的单元格内容，正如你所说，不用打开源文件也可以进行引用。
虽然用相对引用把整张表格引用过来也是可以的，不过引用整张表格再进行数据的再整理就显得繁琐了。
且其有个明显的短板就是当源文件的单元格位置发生变动时，你所引用的数据就会发生错误，如果用这种引用方式用的地方比较多的话，后期修改起来估计是要耗费一番精力了。

上面说到，这样引用只能你所引用的单元格内容，而VLOOKUP函数则可以引用它的第二参数所选的区域，且可以为整个工作簿使用，所以任一单元格键入你所说的直接引用并不能达到不打开源文件就能显示数据的效果。

瓶砸

祝洪忠- 发表于 2018-9-19 11:14
谢谢分享  收藏学习了

感谢祝老师收藏。
平时一直求助也挺不好意思的，好不容易有些干货可以分享了，也是挺开心的。

uneye2

瓶砸 发表于 2018-9-20 09:19
感谢回复，这个的话只能引用单一的单元格内容，正如你所说，不用打开源文件也可以进行引用。
虽然用相对 ...

不是引用，是激活，上面是激活A1单元格，将A1换成A：Z，就是激活A：Z区域，
只需一个单元格、一道公式，就可以达到不用打开源工作簿而更新数据的目的，更新的数据即源工作簿最后保存的数据。至少WPS可以做到。