58

【多表汇总】合并12个月的报表并制作产品区域动态图表

在进行数据整理与汇总分析时，我们一直强调可把数据源汇总到同一张工作表中，即构建统一、全面、准确的数据源，然后再根据这样的数据源去制作数据透视表、商务图表等。然而实际工作中遇见的数据源却并非如此，它们通常是一个个分散开来制作的独立sheet的统计表格（见图5-89中1～12月的销售报表，分别列示在12张工作表中）。这就需要我们利用excel高效处理的手段，将其快速合并统计到一起，并制作出符合呈现需求的商务图表（见图5-90）。

在实际工作中，由于数据源表格设计的不规范造成数据源分散统计的情况较多，因此，快速、高效地对多个结构相同的表进行快速汇总并制作动态图表就显得尤为重要了。在本节内容中，将介绍excel多个结构相同的表，以合并计算、vba、sql三种不同方式的快速汇总，并制作出与其对应的动态图表相关的技巧内容。

图5-89

图5-90

581合并计算法汇总

具体步骤如下。

1启动合并计算功能。打开示例文件，依次按住alt+d组合键和alt+p组合键，打开【数据透视表和数据透视图向导】，选择【多重合并计算数据区域】后，单击【下一步】按钮（见图5-91）。

图5-91

2选择【自定义页字段】，单击【下一步】按钮（见图5-92）。

图5-92

3添加数据源。在【选定区域】中单击sheet1中的数据源区域，并且单击【添加】按钮。添加数据源后，在【请先指定要建立在数据透视表中的页字段数目】选择“1”，并在【字段1】中填写“1月”。同理，依次添加2月到12月的数据。添加完毕后，单击【下一步】按钮（见图5-93）。

图5-93

4导入excel。单击选择【数据透视表显示位置】，在【现有工作表】中选择任意一个空白位置，单击【确定】按钮（见图5-94）。

图5-94

5调整透视表的布局。在新插入的数据透视表的字段布局区域中，将“页1”标签移至【行】中，将“行”标签移至【列】中，即展示为产品名称在上，销售成本在下的布局。选中第10、11月的数据，用拖曳方式将其移动到12月数据之前，使月份呈现升序排序的效果（见图5-95）。

图5-95

6选择汇总列，单击【设计】选项卡，之后单击【分类汇总】→【不显示分类汇总】，即可完成基于透视表的合并计算功能，为后续的数据可视化呈现奠定基础（见图5-96）。

图5-96

说明：采用合并计算的方法，只适用于较为简单的数据源表格，例如数据源中只包含顶端或左侧两个标题列，各分表中的内容都是按照行列标题进行对应求和的情况。如果遇到行、列中有多个标题项目时，则该方案并不适用，比如在数据源中，增加了一列字段“区域”的情况。

582vba汇总

当数据源区域中包含多个标题项目（如图5-97所示，在a～c列）时，分别列示的字段为月份、区域和项目。在多列标签列的情况下，我们无法使用合并计算的方法，单独地对每个标签一一对应的多个月份的工作表的内容进行汇总。在这种情况下，我们往往需要手动地把每个月份工作表中的内容复制粘贴到一起，才能完成数据源的汇总工作。在excel中，基于相同逻辑的重复性动作，比如不断地复制粘贴等，通常可以采用vba的方法予以处理——解决重复性劳动。

图5-97

1打开示例文件，按alt+f11组合键，打开excelvba编辑器界面。单击【插入】选项卡，新建一个模块，excel自动将其命名为“模块1”，然后将vba代码复制到模块1中（见图5-98）。

代码如下：

图5-98

代码说明：

遍历当前每张工作表的名称，是否不等于（＜＞）“合并”，如果是的话，把该工作表中已使用的区域a1：g最大行进行复制，然后粘贴到“合并”工作表中从上至下的未填写内容的第一个空白行中。汇总完毕，弹出对话框提醒“合并完毕”。

代码的运行：直接单击工具栏上的绿色小三角即可运行（见图5-99）。

图5-99

2汇总分析。运行代码后，选中已经自动汇总到一起的数据源区域中的任意一个有字单元格。然后单击【插入】选项卡的【数据透视表】，在弹出的【创建数据透视表】对话框中，excel已经默认选取了数据源表中的连续区域作为透视来源，单击【确定】按钮。

3完善报表。在新建的数据透视表的工作表中，选中【数据透视表字段】区域中的各个字段，将其拖曳至字段列表的布局位置：将“月份”、“区域”、“项目”移至【行】；产品1～3移至【Σ值】，修改为“求和”（见图5-100）。

4透视表布局的优化。选中数据透视表区域后，单击【数据透视表工具】→【设计】选项卡，在【报表布局】中选择【以表格形式显示】，之后选择【重复所有项目标签】，这样即可将原先层叠似的报表布局更改为我们平时看到的数据透视表那样，一列一个字段地展示效果（见图5-101）。

图5-100

图5-101

小结：通过这样的方法，我们完成了多个结构相同表格的快速汇总。

583sql汇总

对于像582节示例中拥有多列标签结构相同的多张工作表进行合并时，除了使用vba汇总法以外，还可以使用sql方法进行汇总。

我们通常说的sql通常指sql服务器或者sql语言。在本例中，我们指的是使用sql语言来进行多表合并的实例化操作。

structuredquerynguage（结构化查询语言）简称sql，它是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

对于sql语言的学习是另一个专业的知识领域。在本书中，我们不对此做全面的讲解，但是会告诉大家一些基本的sql语法，以便进行商务图表的绘制。

select字段名fro表名]的含义：从某张表中取出某个字段。在sql中，用表示该表中的所有字段，比如：selectfro表名]。

如果需要将其他表中的内容合并到同一张表时，只需要在后面加上一个“unionall”的语句，即可依次合并指定表当中的字段内容。

所以，回到我们的示例文件中，将1～12月中每张工作表的所有字段全部合并到一起的sql语句如下：

说明：在excel中，关联的每张表的具体表名写法如下：[表名$]，即在工作表的名称后面需要加上美元符号$，并用英文状态下的方括号将其括起来。

在掌握了上述技巧之后，我们开始构建excel多表合并的数据透视表，以便为后续动态图表的制作奠定数据基础。

1插入透视表。新建一个空白的工作表，单击【插入】选项卡中的【数据透视表】按钮，在弹出的【创建数据透视表】对话框中选择【使用外部数据源】，单击【选择连接】按钮，在弹出的【现有连接】对话框中单击【浏览更多】按钮，找到示例文件中的表格位置后，选择该表并单击【确定】按钮（见图5-102）。

选择指定的工作簿，单击【确定】按钮，在弹出的【选择表格】对话框中选择任意一张工作表作为占位，如选择【＇10月$＇】，单击【确定】按钮。此时excel会将你所选的当前表作为数据源去创建一个透视表，并且按照图5-103所示，将它放在现有工作表即sheet1的a1单元格处开始创建。

图5-102

图5-103

2更改数据源。在新建的透视表中，单击【数据透视表工具】→【分析】选项卡，之后单击【更改数据源】右侧的小三角按钮，选择【连接属性】。

在弹出的【连接属性】对话框中选择【定义】选项卡，将【命令类型】更改为【sql】，在【命令文本】中输入我们提前准备好的sql语句，单击【确定】按钮。之后，excel会将该透视表所关联的数据源变更为1～12月的12张工作表中的所有数据源内容（见图5-104）。

图5-104

3制作透视表。数据源关联过来后，将对应的字段名称拖曳到透视表合适的字段区域中，完成1～12月数据的合并透视表制作，效果如图5-105所示。

图5-105

下面我们就可以基于这样一份汇总后的数据透视表，开始制作具有动态图表效果的财务年报动态图表了。

584制作动态年报

1制作绘图用的控件组

具体步骤如下。

1创建数据标签。在空白表格的a1：b13单元格区域中依次输入“选项”“产品”“类别”及其二类项目名称等。

2插入控件。单击【开发工具】选项卡中的【插入】→【组合框】，在空白区域绘制控件（见图5-106）。

图5-106

选中控件框后单击鼠标右键，选择【设置控件格式】，在弹出的【设置控件格式】对话框中，在【数据源区域】中选择b2：b5单元格区域，【单元格链接】为c2单元格，单击【确定】按钮。设置完成后，控件便可下拉选项选择不同的产品类别并在c2单元格处显示相同类型的序列号。比如：选择控件下拉列表中的【产品2】，会在c2单元格中显示【2】的结果，这表示【产品2】在数据来源区域b2：b5中排第2位（见图5-107）。同理，绘制【地区】的【控件】→【组合框】。

3统一格式。按住ctrl键选中两个控件框，单击【格式】选项卡，之后依次将【大小】中的【高度】设置为1，将【宽度】设置为3，并利用【排列】→【对齐】→【左对齐】的方式，将两个组合框进行快速、准确的对齐（见图5-108）。

图5-107

图5-108

4插入复选框。单击【开发工具】选项卡，之后单击【插入】→【复选框】，在空白区域绘制控件。选中已经插入的【复选框】，单击鼠标右键，选择【编辑文字】，将其显示内容修改为“销售收入”。

绘制完毕后，选中【销售收入】复选框，按ctrl+shift组合键向下拖曳控件框，进行快速复制，重复3次，快速完成【销售成本】【销售毛利】【毛利率】复选框的制作。并按照上例的方法，单击鼠标右键，选择【设置控件格式】，将每个控件链接到具体的单元格中，即c10、c11、c12、c13。设置完毕后，勾选复选框时，对应的单元格会呈现“true”的结果，反之则为“false”（见图5-109）。快速复制后的效果如图5-110所示。

图5-109

图5-110