EXCEL大问题——综合-跨工作簿、跨工作表统计

pureshadow

这是经常遇到的一个问题，案例如下图：
社保一个工作簿，分12个工作表；公积金一个工作簿，分12个工作表，现在要统计每个人全年社保和公积金的总和。

提供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入门不久的同学
　　数据统计后数据源不会再发生变化
　　

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


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

就现实工作来讲，推荐第六种方法。

yanwei82123300

谢谢分享！！！

xdxkm99

感谢分享！方法总结的好全面

xdxkm99

感谢分享！方法总结的好全面

naima1119

谢谢楼主无私奉献