如何实现同工作簿中跨多个工作表查询？

九月蛳 · 发表于 2016-9-21 13:57

请问如何实现同工作簿中跨多个工作表查询？之前用LOOKUP在同一张工作表内查询没问题，多个表后就无法使用了，请问还有更好的办法吗？谢谢！

zengyidin · 发表于 2016-9-21 14:32

本帖最后由 zengyidin 于 2016-9-21 14:41 编辑

请自行设置为日期显示格式
B6:

=MAX(MMULT(INDIRECT(TEXT(MATCH(2,COUNTIF(INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),B5),),"00")&"!a9:b39"),{1;100000}))-B5*10^5

复制代码

B8:

=MIN(MMULT(INDIRECT(TEXT(MATCH(2,COUNTIF(INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),B7),),"00")&"!a9:b39"),{1;100000}))-B7*10^5

复制代码

九月蛳 · 发表于 2016-9-22 08:34

zengyidin 发表于 2016-9-21 14:32
请自行设置为日期显示格式
B6:
B8:

没能理解到公式，然后拖动公式，C、D两列显示#N/A。麻烦详解，万分感谢。

zengyidin · 发表于 2016-9-22 11:38

九月蛳发表于 2016-9-22 08:34
没能理解到公式，然后拖动公式，C、D两列显示#N/A。麻烦详解，万分感谢。

附件已更新
以B6公式为例，=MAX(MMULT(INDIRECT(TEXT(MATCH(2,COUNTIF(INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),B5),),"00")&"!a9:b39"),{1;100000}))-B5*10^5
1.INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),此条通过INDIRECT函数和TEXT函数会获得从1－12月共12个工作表的B列，即{"01!B:B";"02!B:B";"03!B:B";"04!B:B";"05!B:B";"06!B:B";"07!B:B";"08!B:B";"09!B:B";"10!B:B";"11!B:B";"12!B:B"}
2.COUNTIF(INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),B5)  通过条件计数函数COUNTIF得到B5这个最大值在哪一个工作表出现过
3.MATCH(2,COUNTIF(INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),B5),)  通过MATCH函数得到最大值所在工作表是第几个表
4.INDIRECT(TEXT(MATCH(2,COUNTIF(INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),B5),),"00")&"!a9:b39") 再次通过INDIRECT函数得到需要的工作表的A9:B39数据区域
5.MMULT(INDIRECT(TEXT(MATCH(2,COUNTIF(INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),B5),),"00")&"!a9:b39"),{1;100000})  通过MMULT函数得到数据区域中的数值乘上100000，最后得到数值加上日期，其中包括了最大值以及它的日期
6.MAX(MMULT(INDIRECT(TEXT(MATCH(2,COUNTIF(INDIRECT(TEXT({1;2;3;4;5;6;7;8;9;10;11;12},"00")&"!B:B"),B5),),"00")&"!a9:b39"),{1;100000}))  此条通过MAX提取出最大值和它的日期
7.-B5*10^5  这个得到最大值乘上100000，MAX()-B5*10^5  就得到了最大值对应的日期了
其它的原理相同，包括最小值日期，C列、D列都是这个原理，你自己琢磨一下

001.rar (12.32 KB, 下载次数: 37)

zengyidin · 发表于 2016-9-22 11:49

001.rar (12.32 KB, 下载次数: 52) 附件已更新
刚才输入那么多居然没有保存成功，郁闷，懒得再输入了，整条公式的意思大概说一下：以B6公式为例
就是先通过INDIRECT函数得到1－12个月的工作表的B列，
然后通过COUNTIF函数得到最大值在哪个工作表，再通过MATCH函数得到这个工作表的具体位置
又通过INDIRECT函数得到最大值工作表的A9:B39数据区域
然后通过MMULT函数将数据区域内的数值乘上100000，再加上A列的日期，最大值和日期包括其中
MAX函数得到最大值和日期
再减去B5乘上100000，即得到最大值的日期
其它公式的原理类似，自己理解一下

九月蛳 · 发表于 2016-9-22 15:10

zengyidin 发表于 2016-9-22 11:49
附件已更新
刚才输入那么多居然没有保存成功，郁闷，懒得再输入了，整条公式的意思大概说一下：以B6公式为 ...

基本算是理解了。但是为什么我自己做就是错误的呢？我在分表里面新增了一列（C列），然后在总表里面计算仍然出错，找不到出错原因。麻烦大神帮忙看看，谢谢。

九月蛳 · 发表于 2016-9-22 15:11

zengyidin 发表于 2016-9-22 11:49
附件已更新
刚才输入那么多居然没有保存成功，郁闷，懒得再输入了，整条公式的意思大概说一下：以B6公式为 ...

我估计是数组那儿出了错，但是不知道怎么修改。

zengyidin · 发表于 2016-9-22 16:20

不是数组的问题，我的公式是普通公式，主要是你的子工作表里有些没有填充数据，在公式里并上一个0就可以了，具体看附件

234.rar (13.24 KB, 下载次数: 173)

九月蛳 · 发表于 2016-9-23 11:33

zengyidin 发表于 2016-9-22 16:20
不是数组的问题，我的公式是普通公式，主要是你的子工作表里有些没有填充数据，在公式里并上一个0就可以了 ...

太完美了，好厉害！谢谢！！！

lrh788 · 发表于 2018-7-3 15:42

学习留印待用！

		自动登录	找回密码
密码			免费注册

[求助] 如何实现同工作簿中跨多个工作表查询？

评分