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
上一章目录下一章