跨工作表根据月份智能求和（增加了透视表方法）

cleverzhzhf

我猜我以下要写的内容值一个技术分？哈哈，先不扯，直接步入正题：

在做汇总统计工作中，习惯上可能经常以12个月份命名工作表，然后在《汇总》表中做统计：
对于单个单元格的统计：

1. 
   
2. =SUM('1月份:12月份'!B2)

复制代码

对于单个项目的统计：

1. =SUM('1月份:11月份'!B2:B16)

复制代码

但是如何能够更快捷的只统计其中某几个月份呢？
比如只选取4-6月份？设定M2与M3单元格就是月份选择，分别为4月份和6月份。
常规的想法就是使用INDIRECT。常规的写法如下：

1. 
   
2. =SUM(N(INDIRECT($M$3&":"&$M$4&"!B2")))
   
3. =SUM(N(INDIRECT($M$3&":"&$M$4&"!B2:B16")))
   

复制代码

但是怎么处理仍然不能成功。不管是piny版主的IF+N+INDEX，还是胡剑版主的“大颗粒”，我都没有试验成功。
后来换了一个办法，竟然发现成功了，可详细参考附件：

1. 
   
2. =SUM(N(INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&CHAR(64+COLUMN())&ROW())))

复制代码

1. =SUM(N(INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&CHAR(64+COLUMN(B:B))&TRANSPOSE(ROW($2:$16)))))

复制代码

后来仔细解读了一下式子，将其中的：
ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))
这一部分抹黑，然后点F9，得到以下结果：
{4;5;6}
结合前后的公式计算行成以下式子：

1. =SUM(N(INDIRECT({4;5;6}&"月份!"&{"B2"})))

复制代码

进一步变成：

1. =SUM(N(INDIRECT({"4月份!B2";"5月份!B2";"6月份!B2"})))

复制代码

通过上式可以看出来：
在跨工作表的三维引用上，INDIRECT并不识别"4月份:6月份"的形式；
要把这些一个个罗列出来，形成{"4月份";"5月份";"6月份"}，从这个角度说明INDIRECT是可以接数组函数的。



补充内容 (2013-7-24 10:10):
16楼增加“数据透视表”操作方式，请参考。

补充内容 (2013-8-2 13:45):
http://club.excelhome.net/forum. ... 1019239&pid=7083284

补充内容 (2013-9-12 11:27):
在本帖23楼增加通过Subtotal解决四维引用求和计算问题。Subtotal是个神奇的东西。
http://club.excelhome.net/forum. ... 1019239&pid=7190381

当在三维上求和时，Subtotal是个神奇的东东： 对于整个区域的汇总： =SUM(SUBTOTAL(9,INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&"B2:F16"))) 复制代码 同时扩展，通过Subtotal可以求得平均值、数字个数、非空单元格数、最大值、最小值： =AVERAGE(SUBTOTAL(1,INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&"B2:F16"))) =SUM(SUBTOTAL(2,INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&"B2:F16"))) =SUM(SUBTOTAL(3,INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&"B2:F16"))) =MAX(SUBTOTAL(4,INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&"B2:F16"))) =MIN(SUBTOTAL(5,INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&"B2:F16"))) 复制代码 2013-9-12 11:23 上传 下载附件 (53.51 KB) 跨工作表根据月份智能求和（增加Subtotal汇总及透视表法）.zip (42.98 KB, 下载次数: 0)

小雨点沙沙响

楼主，正对这个函数一筹莫展，及时雨呀！谢谢！

wileychen

还没能理解过来，学习了

kiss被注册

学习了，感谢楼主的分亨

人事难料

留名学习中，正抓耳挠腮着呢。

cleverzhzhf

小雨点沙沙响 发表于 2013-5-20 22:16
楼主，正对这个函数一筹莫展，及时雨呀！谢谢！

源引自胡剑版主的一句话：INDIRECTt具有剥离 引号 的作用

胡剑版主关于INDIRECT的精彩基础讲解：
http://club.excelhome.net/forum.php?mod=viewthread&tid=618433

cleverzhzhf

人事难料 发表于 2013-5-20 23:12
留名学习中，正抓耳挠腮着呢。

对indirect的基础学习，可以参考胡剑版主关于INDIRECT的精彩基础讲解：
http://club.excelhome.net/forum.php?mod=viewthread&tid=618433

zsxq

谢谢分享！！！！！！

七夕、

这个估计给不了技术分。。。

yzm888518

支持一下。