高手帮我解决一下多工作表按条件汇总

wub80

这是一个仓库库位管理表格，很实用的，哪位高手帮我解决最后一步，在公式区求不到很好的解决方法，看看这里怎么样

平帆

第一、二行的所在的字段都是合并单元格，表格格式不改的话，不能用透视表

pc520

吼吼，你又来这里发帖，我的03版只能接受12个货架的：
SELECT 编号, 名称, MAX(区域) AS 区域1, IIF(MAX(区域)=MIN(区域),'',MIN(区域)) AS 区域2
FROM (select * from [01$B3:D27] UNION select * from [01$E3:G27] UNION select * from [01$H3:J27] UNION select * from [01$K3:M27] UNION        select * from [02$B3:D27] UNION select * from [02$E3:G27] UNION select * from [02$H3:J27] UNION select * from [02$K3:M27] UNION        select * from [03$B3:D27] UNION select * from [03$E3:G27] UNION select * from [03$H3:J27] UNION select * from [03$K3:M27] UNION        select * from [04$B3:D27] UNION select * from [04$E3:G27] UNION select * from [04$H3:J27] UNION select * from [04$K3:M27] UNION        select * from [05$B3:D27] UNION select * from [05$E3:G27] UNION select * from [05$H3:J27] UNION select * from [05$K3:M27] UNION        select * from [06$B3:D27] UNION select * from [06$E3:G27] UNION select * from [06$H3:J27] UNION select * from [06$K3:M27] UNION        select * from [07$B3:D27] UNION select * from [07$E3:G27] UNION select * from [07$H3:J27] UNION select * from [07$K3:M27] UNION        select * from [08$B3:D27] UNION select * from [08$E3:G27] UNION select * from [08$H3:J27] UNION select * from [08$K3:M27] UNION        select * from [09$B3:D27] UNION select * from [09$E3:G27] UNION select * from [09$H3:J27] UNION select * from [09$K3:M27] UNION        select * from [10$B3:D27] UNION select * from [10$E3:G27] UNION select * from [10$H3:J27] UNION select * from [10$K3:M27] UNION        select * from [11$B3:D27] UNION select * from [11$E3:G27] UNION select * from [11$H3:J27] UNION select * from [11$K3:M27] UNION        select * from [12$B3:D27] UNION select * from [12$E3:G27] UNION select * from [12$H3:J27] UNION select * from [12$K3:M27])
WHERE (编号 Is Not Null)
GROUP BY 编号, 名称
ORDER BY 编号


那位用07版或10版的，测试以下代码：

SELECT 编号, 名称, MAX(区域) AS 区域1, IIF(MAX(区域)=MIN(区域),'',MIN(区域)) AS 区域2
FROM (select * from [01$B3:D27] UNION select * from [01$E3:G27] UNION select * from [01$H3:J27] UNION select * from [01$K3:M27] UNION        select * from [02$B3:D27] UNION select * from [02$E3:G27] UNION select * from [02$H3:J27] UNION select * from [02$K3:M27] UNION        select * from [03$B3:D27] UNION select * from [03$E3:G27] UNION select * from [03$H3:J27] UNION select * from [03$K3:M27] UNION        select * from [04$B3:D27] UNION select * from [04$E3:G27] UNION select * from [04$H3:J27] UNION select * from [04$K3:M27] UNION        select * from [05$B3:D27] UNION select * from [05$E3:G27] UNION select * from [05$H3:J27] UNION select * from [05$K3:M27] UNION        select * from [06$B3:D27] UNION select * from [06$E3:G27] UNION select * from [06$H3:J27] UNION select * from [06$K3:M27] UNION        select * from [07$B3:D27] UNION select * from [07$E3:G27] UNION select * from [07$H3:J27] UNION select * from [07$K3:M27] UNION        select * from [08$B3:D27] UNION select * from [08$E3:G27] UNION select * from [08$H3:J27] UNION select * from [08$K3:M27] UNION        select * from [09$B3:D27] UNION select * from [09$E3:G27] UNION select * from [09$H3:J27] UNION select * from [09$K3:M27] UNION        select * from [10$B3:D27] UNION select * from [10$E3:G27] UNION select * from [10$H3:J27] UNION select * from [10$K3:M27] UNION        select * from [11$B3:D27] UNION select * from [11$E3:G27] UNION select * from [11$H3:J27] UNION select * from [11$K3:M27] UNION        select * from [12$B3:D27] UNION select * from [12$E3:G27] UNION select * from [12$H3:J27] UNION select * from [12$K3:M27] UNION        select * from [13$B3:D27] UNION select * from [13$E3:G27] UNION select * from [13$H3:J27] UNION select * from [13$K3:M27] UNION        select * from [16$B3:D27] UNION select * from [16$E3:G27] UNION select * from [16$H3:J27] UNION select * from [16$K3:M27] UNION        select * from [17$B3:D27] UNION select * from [17$E3:G27] UNION select * from [17$H3:J27] UNION select * from [17$K3:M27] UNION        select * from [18$B3:D27] UNION select * from [18$E3:G27] UNION select * from [18$H3:J27] UNION select * from [18$K3:M27] UNION        select * from [19$B3:D27] UNION select * from [19$E3:G27] UNION select * from [19$H3:J27] UNION select * from [19$K3:M27] UNION        select * from [24$B3:D27] UNION select * from [24$E3:G27] UNION select * from [24$H3:J27] UNION select * from [24$K3:M27])
WHERE (编号 Is Not Null)
GROUP BY 编号, 名称
ORDER BY 编号

pc520

晕，我写文章也写不了这么多字

wub80

原帖由 pc520 于 2010-2-27 13:47 发表
晕，我写文章也写不了这么多字

谢谢兄弟，只是你的这个太复杂了点，公式什么的不能解决吗？？

pc520

公式，要写出来估计比我的复杂，而且也会很慢吧，建议找VBA 的高手弄........，他们厉害
或者你改变格式，我这要把你所有的表按不同区域连接起来，代码就得这么多。

yysy

好像07也只能12个货架，多了就无法添加表

lj1226189

就是有10个区域也可以显示出来,昨天晚上做的放在家里了

nkgsm

这么多工作表用SQL是有限制的，用VBA将其集中再简单透视即可实现汇总目的。