2.1　特殊结构表格的合并

不论是财务数据处理，还是其他数据处理（如生产日报表等），经常会遇到特殊结构表格数据的合并问题，这些特殊结构的表格，是依据实际管理需要设计的，很多情况下不是标准规范的一维表。

对于这样的表格，合并的主要目的是将各表格中对应的单元格数据进行加总，因此，我们可以采用合并计算工具、函数等方法来完成。

2.1.1　结构完全相同的特殊表格：当前工作簿内的多个工作表

图2-1是6个结构相同的表格，现在的任务是将这6个表格的数据加总到一个汇总表中，汇总表的结构也是同样的结构。

图2-1　结构完全相同的6个工作表

由于我们需要的是这几个工作表数据的加总结果，就有以下三种处理方法：

（1）如果仅仅是需要一个合计数，可以使用SUM函数。

（2）如果不仅仅是需要一个合计数，还希望在合并表上能够随时查看合计数的来源（明细数据），可以使用合并计算工具。

（3）如果要合并的工作表个数不定（目前是6个月数据，如果增加了7月数据、8月数据……），此时可以使用SUM函数，或者使用VBA。

1．使用SUM函数

使用SUM函数可以快速获取各表格的合计数，并且在工作表增加的情况下，可以快速添加到合并表中。

以图2-1所示的数据为例，使用SUM函数合并的基本方法和步骤如下。

步骤1 将某个工作表复制一份，重命名为“汇总”，删除标题以外的数据，如图2-2所示。

图2-2　设计汇总表

步骤2 将所有要加总的工作表移动到一起，中间不要有其他不相干的工作表。

步骤3 选择汇总表中要放置合计数的单元格区域，见图2-2。

步骤4 输入公式“=SUM()”，或者单击功能区的“自动求和”按钮，如图2-3所示。

图2-3　插入公式“=SUM()”

步骤5 单击第一个要加总的工作表标签，然后按住Shift键，再单击最后一个要加总的工作表标签，如图2-4所示，这样就得到了要加总的所有工作表的引用：

    =SUM('01月:06月'!)

其中，01月:06月表示引用包含第一个工作表“01月”和最后一个工作表“06月”在内的所有工作表。

步骤6 再单击要加总的第一个单元格（这里是B3），完成加总SUM公式，如图2-5所示。

    =SUM('01月:06月' !B3)

图2-4　引用所有要加总的工作表

图2-5　单击单元格B3，完成SUM公式

步骤7 按Ctrl+Enter组合键，就得到所有单元格的求和公式，也就得到了6个工作表的加总合计数，如图2-6所示。

图2-6　合计表

如果又有了“07月”工作表，只需要将该工作表置于工作表“01月”和“06月”之间，就自动将“07月”工作表数据加总到合并表，如图2-7所示（请与图2-6比较）。

图2-7　新增加的工作表数据，自动加总到合并表

如果仅仅是需要得到各工作表的合计数，这个合并表就完成任务了，不过，如果要查看每个工作表的原始数据，就需要切换到各工作表中查看，这就有点不方便了。

下面介绍合并工具方法，可以实现在同一个合并表中，既可以得到各工作表的合计数，也可以查看各工作表的原始数据，不需要一个一个切换到每个工作表。

2．使用合并计算工具

合并计算工具可以快速完成结构相同工作表的合并汇总，不论这些工作表结构多么复杂、有多少个合并单元格标题，只要它们结构完全相同即可。

以图2-1所示的数据为例，合并计算工具进行合并的方法和步骤如下。

步骤1 将某个工作表复制一份，重命名为“汇总”，删除标题以外的数据，参见前面的图2-2。

步骤2 选择要保存合并数据的单元格区域。

步骤3 单击“合并计算”命令按钮，如图2-8所示。

步骤4 打开“合并计算”对话框，如图2-9所示。

步骤5 单击“引用位置”输入框，然后拖动光标选择某个工作表的合并区域，再单击“添加”按钮，如图2-10所示。

步骤6 依此方法，将所有要合并的工作表数据区域添加完毕，如图2-11所示。

图2-8　“合并计算”命令按钮

图2-9　“合并计算”对话框

图2-10　选择并添加工作表的合并区域

图2-11　添加完所有要合并的工作表

步骤7 由于我们不仅需要得到合计数，还要得到每个工作表的明细数据，因此要选中对话框底部的“创建指向源数据的链接”复选框，如图2-12所示。

图2-12　选择“创建指向数据源的链接”

步骤8 单击“确定”按钮，得到所选工作表的合并结果，如图2-13所示。

图2-13　合并结果

步骤9 这个合并表，不仅有所选工作表的合计数，还有链接过来的每个工作表的明细数据，因此自动生成了两层分级显示 ，单击工作表左侧的二级按钮 ，就展开了工作表，可查看每个工作表的数据，如图2-14所示。

图2-14　展开合并表，查看各工作表的数据

步骤10 下面的工作，就是在A列各空单元格中输入相应的月份名称。

单击空单元格右侧的某个单元格，就可以看到该行是哪个工作表的数据，如图2-15所示。

步骤11 在A列顶部第一个部门上面的空单元格手动输入相应工作表（月份）名称，如图2-16所示。

图2-15　查看数据来自哪个工作表

图2-16　输入管理部的各月名称

步骤12 然后选择这几个单元格，批量复制到A列其他的空单元格中，如图2-17所示。

有一个快速输入各部门字段的月份名称的方法：选择手动输入的管理部顶部的6个月份单元格区域，按Ctrl+C键复制，然后选择A列下面所有的空单元格（可以通过定位的方法来快速选择），再按Ctrl+V键粘贴即可。

步骤13 为了使合并表格的阅读性更好，可以通过定位的方法，先选择合计行，再把合计行设置为不同的字体或颜色格式，如图2-18所示。

图2-17　完成的各部门字段的月份名称

图2-18　设置合计行的格式

3．使用VBA

由于这样的表格结构完全相同，当仅仅需要各工作表合计数时，使用VBA无疑是最简单的方法，它不限制工作表的个数，几个工作表也好，几百个工作表也好，只需一个按钮就可以完成合并计算。

针对图2-1所示的示例，扩展到工作表个数不定的场合，下面是VBA参考代码，其核心是循环单元格区域，将每个工作表的对应单元格数据相加。

    Sub 汇总()
      Dim i As Integer, j As Integer, k As Integer
      Dim wb As Workbook
      Dim ws As Worksheet
      Dim sh As Worksheet
      Set wb = ThisWorkbook
      Set ws = wb.Worksheets("汇总")
      For i = 1 To wb.Worksheets.Count
        Set sh = wb.Worksheets(i)
        If sh.Name < > "汇总" Then
          For j = 3 To 19
            For k = 2 To 7
              ws.Cells(j, k) = ws.Cells(j, k) + sh.Cells(j, k)
            Next k
          Next j
        End If
      Next i
    End Sub

2.1.2　结构完全相同的特殊表格：不同工作簿内的工作表

合并计算工具，除了适用于同一个工作簿内的多个工作表汇总外，还适合不同工作簿的工作表汇总，只要保证各工作簿的工作表结构相同即可。

例如，文件夹里有三个工作簿，分别保存三个城市门店各月的销售数据，如图2-19所示，每个工作表结构及数据示例如图2-20所示。现在的任务是将这三个工作簿的所有工作表数据合并起来。

图2-19　文件夹里的三个工作簿

图2-20　某个工作簿内的工作表

1．使用合并计算工具

这种跨工作簿合并计算稍微烦琐，但不复杂，主要步骤如下。

步骤1 使用“打开”命令按钮，打开所有要合并的工作簿。

步骤2 新建一个工作簿，设计汇总表结构（与某个原工作表相同，复制一个结构即可）。

步骤3 单击“合并计算”命令按钮，打开“合并计算”对话框（参考图2-9）。

步骤4 分别激活每个工作簿的每个工作表，添加区域，如图2-21所示。

图2-21　添加每个工作簿的工作表数据区域

步骤5 选中“创建指向源数据的链接”复选框，然后单击“确定”按钮，得到如图2-22所示的合并表。

图2-22　合并表数据

步骤6 关闭源数据工作簿。

步骤7 单击左侧边条上的二级按钮，展开合并表，如图2-23所示。

图2-23　展开合并表

步骤8 单击B列的各单元格，观察公式编辑栏的引用公式，可以看到每一行引用了哪个工作簿、哪个工作表的数据。

步骤9 在每个城市上手动插入空行，用SUM函数对该城市求和，并输入相应城市和门店名称，如图2-24所示。可以先处理1月份的数据，然后以此为参照，处理其他月份的数据。

图2-24　输入城市和地区名称

2．使用VBA

上述的合并计算方法比较麻烦，尤其是在要汇总的工作簿和工作表很多的场合，此时，可以使用VBA，单击一个按钮即可实现合并，得到各工作簿工作表的合计数。

针对上述案例，下面是参考代码，这里已确定了要汇总的具体工作簿。

    Sub 汇总()
      Dim wbx As Workbook
      Dim wsx As Worksheet
      Dim ws As Worksheet
      Dim i As Integer, j As Integer, k As Integer, l As Integer
      Dim arr As Variant, s
      Set ws = ThisWorkbook.Worksheets("汇总")
      arr = Array("北京.xlsx", "深圳.xlsx", "苏州.xlsx")
      For k = 0 To UBound(arr)
        Workbooks.Open Filename:=ThisWorkbook.Path & "\" & arr(k)
      Next k
      For i = 3 To 15
        For j = 2 To 9
          s = 0
          For k = 0 To UBound(arr)
            Set wbx = Workbooks(arr(k))
            For l = 1 To wbx.Worksheets.Count
              Set wsx = wbx.Worksheets(l)
              s = s + wsx.Cells(i, j)
            Next l
          Next k
          ws.Cells(i, j) = s
        Next j
      Next i
      For k = 0 To UBound(arr)
        Workbooks(arr(k)).Close savechanges:=False
      Next k
    End Sub

如果要汇总的工作簿有很多，也不清楚每个工作簿具体的名字，可以使用下面的代码将指定文件夹内的所有要汇总的文件搜索出来。

    Sub 搜索()
      Dim fPath As String
      Dim fName As String
      Dim f(1 To 100) As String
      Dim i As Long
      fPath = ThisWorkbook.Path & "\"
      fName = Dir(fPath, 0)
      i = 0
      Do While Len(fName) > 0
        f(i + 1) = fPath & fName
        fName = Dir()
        i = i + 1
      Loop
    End Sub

2.1.3　结构不完全相同的特殊表格

对于结构不完全相同的特殊表格，合并汇总没有通用的方法，而是需要结合具体情况，选择一个简单、高效的方法，例如，使用函数，使用PQ，等等。

图2-25是银行账户工作表，现在的任务是将各账户的当前余额汇总到账户余额表中。

图2-25　结构不完全相同的特殊工作表

针对这个案例，需要使用函数来快速计算。如果要汇总的工作表不多，可以一个一个工作表利用公式进行汇总计算。如果工作表很多，可以使用INDIRECT函数做间接引用，创建通用汇总公式。

下面是使用INDIRECT函数做计算汇总的参考公式。

单元格B2，计算各账户的收项累计：

    =SUM(INDIRECT(A2&"!C:C"))

单元格C2，计算各账户的支项累计：

    =SUM(INDIRECT(A2&"!D:D"))

单元格D2，计算各账户的当前余额： iQ6twMftrNo4BLo4144xI+Ek1+DOeKGj/zgGzBw9VvxlejA1rm7vdwaF1ZzbpBDz

    =LOOKUP(1,1/(INDIRECT(A2&"!E2:E1000")< >""),INDIRECT(A2&"!E2:E1000"))