|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 wshcw 于 2013-9-12 12:25 编辑
我猜我以下要写的内容值一个技术分?哈哈,先不扯,直接步入正题:
在做汇总统计工作中,习惯上可能经常以12个月份命名工作表,然后在《汇总》表中做统计:
对于单个单元格的统计:
对于单个项目的统计:
但是如何能够更快捷的只统计其中某几个月份呢?
比如只选取4-6月份?设定M2与M3单元格就是月份选择,分别为4月份和6月份。
常规的想法就是使用INDIRECT。常规的写法如下:
- =SUM(N(INDIRECT($M$3&":"&$M$4&"!B2")))
- =SUM(N(INDIRECT($M$3&":"&$M$4&"!B2:B16")))
复制代码 但是怎么处理仍然不能成功。不管是piny版主的IF+N+INDEX,还是胡剑版主的“大颗粒”,我都没有试验成功。
后来换了一个办法,竟然发现成功了,可详细参考附件:
- =SUM(N(INDIRECT(ROW(INDIRECT(TRIM(LEFTB($M$3,2))&":"&TRIM(LEFTB($M$4,2))))&"月份!"&CHAR(64+COLUMN())&ROW())))
复制代码- =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}
结合前后的公式计算行成以下式子:
- =SUM(N(INDIRECT({4;5;6}&"月份!"&{"B2"})))
复制代码 进一步变成:
- =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)
|
|
评分
-
8
查看全部评分
-
|