EXCEL 中跨工作表表格汇总

总公司要求子公司都上报几种同样的报表,再把这些报表汇表生成汇总表,比如资产负债表等。

我的思路是,在一个 EXCEL 文件中,做多个工作表,每个工作表表示一个子公司,在工作表里相同的位置放同样格式的表格。

先做好基础数据表:

增加汇总表工作表,把表格样子做好:

在需要汇总的格子上“插入批注”:

批注内容写成“SUM”:

然后到“开发工具”里打开“ Visual Basic”编辑器,在“汇总表”里写事件:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
Private Sub Worksheet_Activate()
    Dim iRow As Integer
    Dim iCol As Integer
 
    iRow = 10
    iCol = 10
 
    Dim iAmount As Double
 
    For i = 1 To iRow
        For j = 1 To iCol
            If Cells(i, j).NoteText = "SUM" Then
                iAmount = 0
                For isheet = 1 To Sheets.Count - 1
                    If Sheets(isheet).Cells(i, j) <> "" And Left(Sheets(isheet).Cells(i, j), 1) <> "-" Then
                        On Error Resume Next
                        iAmount = iAmount + CDbl(Sheets(isheet).Cells(i, j))
                        If Err.Number <> 0 Then MsgBox ("Sheet " + CStr(isheet) + ":" + CStr(i) + "," + CStr(j) + ":" + Sheets(isheet).Cells(i, j))
                        On Error GoTo 0
                    End If
                Next isheet
 
                If iAmount <> 0 Then
                    Cells(i, j) = iAmount
                Else
                    Cells(i, j) = ""
                End If
            End If
        Next j
    Next i
End Sub

写好后,再回到 EXCEL 里,当工作表切换到“汇总表”时,所有标记“SUM”批注的格子就会自动汇总。