在Excel中汇总和分析数据时,经常需要将多张工作表表中的数据全部汇总到一张数据透视表中,便于汇总分析,此时就需要使用到多重数据合并数据表功能,其创建方法与单张数据透视表的创建方法略有不同。

在Excel中,多重合并数据透视表对于数据及项目排序需要注意以下特征:

(1)透视表会将选定的原表区域中的所有内容默认为数字。如果被选定区域中包含字符,则自动转换为数字。例如,"姓名""所属部门"项目中的内容在透视表中将全部显示为"0"。

(2)透视表中对原表中的项目名称按首字字母自动排序。而前面制作的工资表中的项目名称是按照一定的逻辑顺序排列的,透视表将会打乱原表中的项目顺序及逻辑关系。因此,在创建多重合并数据透视表之前必须做好两步简单的准备工作。

具体步骤如下。

第 1 步 打开"员工工资数据透视表.xlsx"文件,批量合并"工号""姓名"及"所属部门"内容。按住"Ctrl"键,依次单击 3 张工作表标签,即多选工作表;在 D 列前插入一列→ D2 单元格输入项目名称,如输入"员工姓名"在 D3 单元格中输入公式"=CONCATENATE(A3,B3,C3)"或 "=&A3&B3&C3",将公式向下填充至 D4:D14区域的所有单元格中即可,如下图所示。

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(1)

第 2 步 在项目名称前批量添加序号。在第 3行上方插入一行作为临时辅助行,在 E3 单元格中设置公式"="0"&COLUMN()-4&E2",返回结果为"01 基本工资"。公式中 COLUMN() 返回单元格所在列号,即 E3 单元格返回"5",而此时要使其返回为"1",减去 4列即可,即"COLUMN()-4";将公式复制、粘贴至 F3:R3 区域的所有单元格中。选中 E3:R3 区域→复制并选择性粘贴"数值"至 E2:R2 区域中→运用"查找与替换"功能将N2:R2 区域单元格中的"0"替换为空值→删除第3 行→取消多选工作表,如下图所示。

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(2)

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(3)

准备工作做好以后,即可开始创建数据透视表,操作步骤如下。

第1步 快速调出创建多重合并数据透视表功能。Excel 2016 版本的功能区中的"插入透视表"选项并无"多重合并计算数据区域"功能,而是隐藏在"自定义功能区"中,这里可通过快捷键打开此项功能的对话框:按下"Alt D"组合键→弹出提示框→再按"P"键即可打开"数据透视表和数据透视图向导"对话框,如下图所示。

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(4)

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(5)

接下来跟着"向导"创建透视表。

第 2 步 选中"步骤 1"中的"多重合并计算数据区域"与"数据透视表"单选按钮→单击"下一步"按钮;在"第 2a 步"对话框中选中"请指定所需的页字段数目"区域中的"自定义字段"单选按钮→单击"下一步"按钮,如下图所示。

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(6)

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(7)

第 3 步 在"第 2b 步"对话框中依次选定 3 张工作表中需要合并汇总数据的区域→单击"添加"按钮后,所选区域即全部列示在"所有区域 :"列表框中;在页字段数目区域选中"1"单选按钮;依次选中"所有区域 :"列表框中的区域→设置每一区域的字段名称;单击"下一步"按钮;选中"步骤 3"对话框"数据透视表显示位置 :"区域中的"新工作表"单选按钮→单击"完成"按钮即可,如下图所示。

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(8)

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(9)

至此,"工资数据透视表"制作完成,其初始效果如下图所示。

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(10)

第 4 步 数据透视表中 P 列"总计"中的数据是将 B:O 区域中的每行数据横向汇总后的总额,而工资表中的每列数据是根据同一行次中的数据,通过不同的运算方式而来,因此 P 列"总计"金额毫无意义,可以将其删除。选中 P5 单元格并右击,选择快捷菜单中的"删除总计"选项即可,如下图所示。

多个工作表怎么数据透视(如何将多张工作表中的数据汇总到一张透视表中呢)(11)

,