Office中国论坛/Access中国论坛

标题: EXCEL大问题——综合-跨工作簿、跨工作表统计 [打印本页]

作者: pureshadow 时间: 2010-11-4 17:02
标题: EXCEL大问题——综合-跨工作簿、跨工作表统计
这是经常遇到的一个问题，案例如下图：
社保一个工作簿，分12个工作表；公积金一个工作簿，分12个工作表，现在要统计每个人全年社保和公积金的总和。
[attach]43997[/attach]
提供6种方法：

方法一：VBA代码（代码写在统计工作簿的模块里）：
优点：
　　一键完成
　　运算速度快
缺点：
　　代码不亲民导致修改困难（PS，这段代码其实还是把各分工作簿打开后提取的，如果完全不打开，需要创建ADO引用，不然代码无法运行，这一做法更不亲民。）
适用：
　　有一定VBA代码基础的使用者
Sub subtotal()
Dim MyFind As Range
Dim MyBook As Workbook
Dim MySheet As Long, i As Long, x As Long
Dim MyBName As String
Dim MyArr, MyRes
Application.DisplayAlerts = False
x = 2
Do
MyBName = Sheets("sheet1").Cells(2, x)
MyArr = Sheets("sheet1").Range("a3:a10")
Set MyBook = GetObject(ThisWorkbook.Path & "\" & MyBName & ".xls")
      With Windows(MyBook.Name)
         .Visible = True
         .Activate
      End With
      ReDim MyRes(1 To 8, 0)
      For i = 1 To 8
         For MySheet = 1 To Sheets.Count
            Set MyFind = Sheets(MySheet).Range("a:a").Find(what:=MyArr(i, 1), lookat:=xlWhole)
            If Not MyFind Is Nothing Then
                  MyRes(i, 0) = MyRes(i, 0) + Sheets(MySheet).Cells(MyFind.Row, 7)
            End If
         Next MySheet
      Next i
      MyBook.Close
Sheets("sheet1").Range(Cells(3, x), Cells(10, x)) = MyRes
x = x + 1
Loop Until Sheets("sheet1").Cells(2, x) = "总计"
Set MyFind = Nothing
Set MyBook = Nothing
Application.DisplayAlerts = True
End Sub

方法二：SQL法（我是SQL白，等不白的时候再来写）

方法三：函数法
优点：
　　就现实工作来讲没有优点
　　业余时间用来锻炼函数脑袋
缺点：
　　由于INDIRECT函数不支持提取未打开工作簿数据，所以使用以下函数时相关工作簿必须在打开状态
　　运算速度慢
适用：
　　闲来无事喜欢钻函数玩的同学
　　已有数据均在同一工作簿内
=SUM(SUMIF(INDIRECT("'["&B$2&".xls]"&ROW($1:$12)&"月'!a:a"),$A3,INDIRECT("'["&B$2&".xls]"&ROW($1:$12)&"月'!g:g")))
CTRL+SHIFT+ENTER结束右拉下拉

方法四：合并计算法
优点：
　　操作方法简单
　　可以跨工作表、跨工作簿操作（下图为节约时间只以《公积金》为例）
缺点：
　　当分表特别多时，每个分表的区域都要选择一次
　　数据源更新后汇总表不能联动
适用：
　　EXCEL入门不久的同学
　　数据统计后数据源不会再发生变化
[attach]43998[/attach]　　

方法五：数据透视表法
优点：
　　操作方法简单
　　可以跨工作表、跨工作簿操作（下图为节约时间只以《公积金》为例）
　　数据源更新后汇总表能联动更新
缺点：
　　当分表特别多时，每个分表的区域都要选择一次
　　表格结构更改增删困难
适用：
　　EXCEL入门不久的同学
　　数据统计后数据源会发生变化
　　对表格美观度要求不高
[attach]43999[/attach]

方法六：表格转变为数据库结构 （每个月做表的时候就把数据放在一张工作表上）
优点：
　　每个表头都可以进行筛选
　　最终的统计工作即使是EXCEL新手也可以在几分钟之内完成
　　一般常用函数的基本用法均可满足日常统计要求
　　运算速度快
缺点：
　　不符合大部分人的做表习惯和视觉习惯
适用：
　　所有分表尚未形成的使用者
　　总表数据小于最大限制（2003版EXCEL限制65536行，2007及以上版本EXCEL限制1048576行）
[attach]44000[/attach]
就现实工作来讲，推荐第六种方法。

作者: yanwei82123300 时间: 2011-5-13 19:53
谢谢分享！！！

作者: xdxkm99 时间: 2012-1-7 09:36
感谢分享！方法总结的好全面

作者: naima1119 时间: 2012-6-4 16:17
谢谢楼主无私奉献

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)