Excel 使用数据透视表合并数据

前面讲过的几种对数据的合并都只是单纯地将数据表合并起来,如果用户希望将数据表合并的同时还能将合并后的数据进行升序或降序排列,这时用户应该进行怎样的操作呢?使用数据透视表合并数据可解决这一问题。

使用数据透视表合并数据的具体操作步骤如下。

步骤1:打开工作表。选择“插入”选项卡“表格”单元组,单击“数据透视表”按钮,弹出如图23-38所示的“创建数据透视表”对话框。

图23-38 “创建数据透视表”对话框

步骤2:在对话框“选择一个表或区域”文本框中选择或输入单元格区域,例如这里选择“Sheet1!$A$2:$C$6”,单击“确定”按钮。

步骤3:选中右侧窗格“选择要添加到报表的字段”列表框中的“学生姓名”、“成绩”、总分(含加分)复选框,此时的工作表如图23-39所示。

步骤4:使用同样的方法为“成绩2”创建数据透视表,结果如图23-40所示。

步骤5:单击“Sheet4”工作表标签,然后单击单元格A11。

步骤6:选择“数据”选项卡“数据工具”单元格中的“合并计算”按钮,弹出“合并计算”对话框。

步骤7:在对话框中进行如下设置,如图23-41所示。

图23-39 创建数据透视表

图23-40 为“成绩2”创建数据透视表

图23-41 “合并计算”对话框

步骤8:单击“确定”按钮,此时的工作表如图23-42所示。

图23-42 使用数据透视表合并数据

由图23-42可以看出,使用数据透视表合并的数据,不但对数据表进行了合并,还对学生姓名进行了排序。如果用户想要进行更多的设置,则在数据透视表的单元格区域中右击,从弹出的菜单中选择“数据透视表选项”命令,弹出如图23-43所示的“数据透视表选项”对话框。在对话框中单击各个选项卡,然后对其进行设置,最后单击“确定”按钮即可。

图23-43 “数据透视表选项”对话框

Excel 使用公式对数据进行合并计算

除了以上两种合并计算外,还可以使用公式对数据进行合并计算。在公式中使用对要组合的其他工作表的单元格引用或三维引用(三维引用:对跨越工作簿中两个或多个工作表的区域的引用),因为没有可依赖的一致位置或分类。

通常情况下使用公式对数据进行合并计算时会有两种情况,一种是要合并计算的数据位于不同工作表上的不同单元格中,另一种是位于不同工作表上的相同单元格中。

· 要合并计算的数据位于不同工作表上的不同单元格中。

步骤1:在当前工作表中,复制或输入要用于合并计算数据的行标签或列标签。

步骤2:选择任意单元格用来存放合并计算数据,例如这里选择单元格B2。

步骤3:输入一个公式,其中包括对每个工作表上源单元格的单元格引用,对于每个单独的工作表都有一个引用。例如,要将Sheet1工作表中单元格B4、Sheet2工作表上单元格F7和Sheet3工作表上单元格C9中的数据合并到主工作表的单元格B2中,此时可以单元格B2中输入公式“=sum(Sheet1!B4,Sheet2!F7,Sheet3!C9)”,如图23-36所示。

图23-36 合并不同工作表不同单元格中的数据

步骤4:输入公式后,按回车键即可。

· 要合并计算的数据位于不同工作表的相同单元格中。

步骤1:在当前工作表中,复制或输入要用于合并计算数据的行标签或列标签。

步骤2:选择任意单元格用来存放合并计算数据,例如这里选择单元格B4。

步骤3:输入一个包含三维引用的公式,该公式使用指向一系列工作表名称的引用。例如,要将工作表Sheet1到Sheet3(包括Sheet1和Sheet3)上单元格A4中的数据合并到主工作表的单元格B4中,此时在单元格B4中输入公式“=SUM(Sheet1:Sheet3!A4)”,如图23-37所示。

图23-37 合并不同工作表中相同单元格中的数据

步骤4:公式输入完毕,按回车键即可。

Excel 按类别对数据进行合并计算

如果用户希望Excel能够根据行列标题的内容人性化地进行合并计算,则可以使用“按类别”对数据进行合并计算的方式。

使用“按类别”对数据进行合并计算的方式,需要在“合并计算”对话框中的“标签位置”列表框中选中“首行”或“最左列”复选框,也可以同时选中两个复选框。例如需要合并的源数据表如图23-28所示,“成绩1”和“成绩2”中的字段排列顺序并不相同。

按类别对数据进行合并计算的具体操作步骤如下。

步骤1:选择作为合并计算的结果的存放起始位置,如这里选择单元格E9。

步骤2:选择“数据”选项卡“数据工具”单元组中的“合并计算”按钮,弹出“合并计算”对话框。

步骤3:在对话框“函数”列表中选择“求和”,在引用位置文本框中依次添加“成绩1”和“成绩2”数据所在的单元格区域“A2:C6”、“E2:G6”。

图23-28 按位置合并忽略行列标题

步骤4:如果在“标签位置”列表框中选择“首行”复选框,如图23-29所示。

图23-29 “合并计算”对话框

步骤5:单击“确定”按钮,得到如图23-30所示的结果。

图23-30 “首行”合并计算

步骤6:如果在“标签位置”列表框中选择“最左列”复选框,如图23-31所示。

步骤7:单击“确定”按钮,此时得到的结果如图23-32所示。

图23-31 “合并计算”对话框

图23-32 “最左列”合并计算

步骤8:如果在“标签位置”列表框中只选择“最左列”复选框,单击“确定”按钮。接着再次打开“合并计算”对话框,并在对话框中勾选“首行”,如图23-33所示。

图23-33 “合并计算”对话框

步骤9:单击“确定”按钮,此时得到的结果如图23-34所示。

图23-34 “首行”和“最左列”合并计算

注意:如果用户直接选择“首行”和“最左列”复选框,单击“确定”按钮后,会出现如图23-35所示的结果,最左上角的单元格是空白的。

图23-35 直接选择两个复选框后的结果

与图23-28的合并结果进行比较不难发现,在使用按类别的合并方式时,如果源数据表中数据记录的排列顺序不同,如图23-28所示的“成绩1”和“成绩2”中“学生姓名”的排列顺序是不同的,在“按类别”合并计算的过程中,Excel会自动地根据记录标题的分类情况,合并相同类别中的数据内容,如图23-30所示。

提示在使用按类别合并时,源数据工作表必须包含行或列标题,并且在“合并计算”对话框中选中相应的复选框。

在按类别对数据进行合并计算时,首先要选中“合并计算”对话框中的“首行”和“最左列”两个复选框,才能实现按类别合并计算。

  • 如果用户需要根据列标题进行分类合并计算时,则选中“首行”复选框;如果用户需要根据行标题进行分类合并计算时,则选中“最左列”复选框;如果用户需要同时根据行标题和列标题进行分类合并计算是,则同时选中“首行”和“最左列”两个复选框。
  • 如果源数据表中没有列标题或行标题,只有数据记录,而这时用户又选择了“首行”和“最左列”,则Excel将源数据表中的第1行和第1列分别默认作为行标题和列标题。

通过以上两个实例,可以简单地总结出合并计算功能的一般性规律。

  • 当数据表中的列标题和行标题完全一致时,合并计算所进行的操作将是按相同的行或列的标题项进行计算,这些计算可能包含求和、计数以及求平均值等。
  • 当数据表中的行标题和列标题不相同时,合并计算则会进行分类合并的操作,即将不同的行或列的数据根据内容进行分类合并,有相同标题内容的合成一条记录,不同标题内容的则形成并列的多条记录,最后形成的表格中包含了源数据表中所有的行标题和列标题。

Excel 按位置对数据进行合并计算

合并计算的方法有两种,一种是按位置对数据进行合并计算,另一种是按类别对数据进行合并计算。本节将通过图23-25所示的实例,介绍如何按位置对数据进行合并计算的具体操作步骤。

如图23-25所示的两个数据表格,使用合并计算可以轻松地对“成绩1”和“成绩2”进行汇总,具体操作步骤如下。

图23-25 目标数据

步骤1:选择作为合并计算的结果的存放起始位置,例如这里选择单元格A9。

步骤2:选择“数据”选项卡“数据工具”单元组中的“合并计算”按钮,弹出“合并计算”对话框。

步骤3:在对话框“函数”列表中选择“求和”,在引用位置文本框中依次添加“成绩1”和“成绩2”的数据所在的单元格区域“A2:C6”、“E2:G6”,如图23-26所示。

图23-26 “合并计算”对话框

步骤4:单击“确定”按钮,结果如图23-27所示。

用户可以看到图23-27中按位置合并后的数据内容,它不包含行标题和列标题。在按位置进行合并的方式中,Excel不注重多个源数据表的行列标题内容是否一致,它只是单纯地对相同表格位置上的数据进行了合并计算。

图23-27 按位置合并后的结果

Excel 6个工作表的数据能一次性合并计算吗?

图6.70中包含6个车间的职工产量数据,现要求将6个工作表的产量和不良品都合并到“汇总”表中。

图6.70 待合并的6个车间产量表

解题步骤

Excel提供了合并计算工具,可以用它对任意工作表的相同区域执行合并,合并时既可以选择求和,也可以选择求平均、求最大值/最小值等,本例展示求和过程,具体步骤如下。

1.进入“汇总”工作表,选择A1单元格。

2.单击功能区的“数据”→“合并计算”,弹出“合并计算”对话框。

3.在对话框中将函数保持为默认的“求和”,然后单击对话框中间的浏览按钮(图标为),选择1月工作表中的A1:C11区域,最后单击“添加”按钮,将区域地址添加到下方的列表中,效果如图6.71所示。

4.继续添加其他5个工作表的A1:C11区域到列表中,且选择下方的“首行”、“最左列”复选框。图6.71和图6.72是分别添加1个和6个引用位置后的设置界面。

图6.71 添加第一个引用位置

图6.72 添加所有引用位置

5.单击“确定”按钮后,在“汇总”工作表中会产生图6.73所示的合并结果。

图6.73 合并结果

知识扩展

1.合并计算工具用于计算比较规则的数据,应该多个工作表的格式一致,数据和文本的分布方式也一致,否则合并就没有意义了。

2.对话框中的“标签位置”用于控制合并时的行标题与列标题,标题不参与合并(指求和、求平均或最大值/最小值等运算)。其中“首行”代表列标题,最左列代表行标题。

3.“合并计算”对话框中的“指向源数据的链接”用于控制合并计算结果是否跟随数据源相应地更新,当此项目选中时表示数据源更新时合并结果也相应地更新。

请在封面中缝指定的QQ群下载文件和视频教材,从而方便练习,同时验证本案例中的技巧与操作结果。

疑难场景

这是一个二元一次方程组,能否在不用公式的前提下得到方程组的解?

解题步骤

Excel的规划求解工具可以解多元方程,不过默认状态下无法调用此工具,需要安装后才能使用。安装并解题的具体步骤如下。

1.按组合键<Alt+T+I>,打开如图6.74所示的“加载宏”管理器。

2.选择“规划求解加载项”复选框,然后单击“确定”按钮保存设置,此时打开“数据”选项卡,可以看到“规划求解”菜单,其界面如图6.75所示。

图6.74 加载宏管理器

图6.75 规划求解菜单

3.假设A1单元格代表X,A2单元格代表Y,那么在B1单元格输入公式“=A1^2+A2^2”,此公式用于表明第一个方程中X和Y的关系。

4.继续在B2单元格中输入公式“=3*A2-A1”,此公式用于表明第二个方程中X和Y的关系。

5.单击功能区的“数据”→“规划求解”,弹出“规划求解参数”对话框。

6.在“设定目标”右方输入 B1,然后选择下方的“目标值”复选框,且在右方输入 52,表示B1单元格的公式的计算结果是52时才停止求解。

7.将“通过更改可变单元格”设置为A1:A2,表示通过不断改变A1和A2单元格的值来测试方程的解,直到找到目标后停止。设置界面如图6.76所示。

设置目标和可变单元格

设置目标和可变单元格

Excel 2019利用数据透视表合并数据

前面讲过的几种对数据的合并,都只是单纯地把数据表合并起来。如果用户希望在将数据表合并的同时还希望合并后的数据可以按升序或降序的顺序排列,这时,用户应该进行怎么样的操作呢?

利用数据透视表合并数据,可解决这一问题。

利用数据透视表合并数据的具体操作步骤如下。

STEP01:打开“成绩求和.xlsx”工作簿,在“Sheet1”工作表中选择任意单元格,这里选择B2单元格,切换至“插入”选项卡,单击“表格”组中的“数据透视表”按钮,打开“创建数据透视表”对话框,如图8-60所示。

STEP02:在“请选择要分析的数据”列表区域中单击“选择一个表或区域”单选按钮,然后设置“表/区域”为“Sheet1!$A$2:$C$6”单元格区域,并在“选择放置数据透视表的位置”列表区域中单击选择“新工作表”单选按钮,最后单击“确定”按钮完成数据透视表的创建,如图8-61所示。

单击“数据透视表”按钮

图8-60 单击“数据透视表”按钮

图8-61 设置数据透视表区域

STEP03:选择新建的数据透视表,打开“数据透视表字段”对话框,在“选择要添加到报表的字段”列表框中依次勾选“学生姓名”“成绩”“总分(含加分)”复选框,此时的工作表如图8-62所示。

STEP04:使用同样的方法为“成绩2”单元格区域创建数据透视表,结果如图8-63所示。

图8-62 添加报表字段

图8-63 为“成绩2”创建数据透视表

STEP05:切换至“Sheet4”工作表,然后选择A18单元格。切换至“数据”选项卡,在“数据工具”组中单击“合并计算”按钮,打开如图8-64所示的“合并计算”对话框。在对话框中单击“函数”选择框右侧的下拉按钮,在展开的下拉列表中选择“求和”选项,在“引用位置”文本框中依次添加“成绩1”和“成绩2”数据透视表所在的单元格区域“$A$3:$C$8”和“$A$11:$C$16”,然后在“标签位置”列表框中同时勾选“首行”复选框和“最左列”复选框,最后单击“确定”按钮完成合并计算。

设置合并计算函数

图8-64 设置合并计算函数

STEP06:此时,工作表中会显示利用数据透视表合并计算的结果,如图8-65所示。

可以看出,使用数据透视表合并的数据,不但对数据表进行了合并,还对学生姓名进行了排序。如果用户想要进行更多的设置,可在数据透视表的单元格区域中单击鼠标右键,在弹出的隐藏菜单中选择“数据透视表选项”命令,打开如图8-66所示的“数据透视表选项”对话框。在对话框中可以任意切换至各个选项卡,然后对其进行设置,最后单击“确定”按钮即可。

使用数据透视表合并数据

图8-65 使用数据透视表合并数据

“数据透视表选项”对话框

图8-66 “数据透视表选项”对话框

Excel 2019利用公式进行合并计算图解

除了以上两种合并计算以外,还可以使用公式对数据进行合并计算。在公式中使用要组合的其他工作表的单元格引用或三维引用(三维引用指对跨越工作簿中两个或多个工作表的区域的引用),因为没有可依赖的一致位置或分类。

通常情况下,使用公式对数据进行合并计算时会有两种情况,一种是要合并计算的数据位于不同工作表上的不同单元格中,二是位于不同工作表上的相同单元格中。

一、要合并计算的数据位于不同工作表上的不同单元格中,具体操作步骤如下:

打开“公式合并计算.xlsx”工作簿,切换至“Sheet1”工作表,选择任意单元格用来存放合并计算数据,这里选择B23单元格。在单元格中输入一个合并公式,其中包括对每个工作表上源单元格的单元格引用,对于每个单独的工作表都有一个引用。例如,要将Sheet1工作表中单元格B4、Sheet2工作表上单元格F7和Sheet3工作表上单元格C9中的数据合并到主工作表的B23单元格中并求和,此时可以在B23单元格中输入合并公式“=SUM(Sheet1!B4,Sheet2!F7,Sheet3!C9)”,然后按“Enter”键即可返回合并求和计算的结果,如图8-58所示。

合并不同工作表不同单元格中的数据

图8-58 合并不同工作表不同单元格中的数据

二、要合并计算的数据位于不同工作表的相同单元格中,具体操作步骤如下:

在当前工作表中,复制或输入用于合并计算数据的行标签或列标签,选择任意单元格用来存放合并计算数据,这里选择B36单元格。在单元格中输入一个包含三维引用的公式,该公式使用指向一系列工作表名称的引用。例如,要将工作表Sheet1到Sheet3(包括Sheet1和Sheet3)上单元格A4中的数据合并到主工作表的B36单元格中并求和,此时在B36单元格中输入公式“=SUM(Sheet1:Sheet3!A4)”,然后按“Enter”键即可返回合并计算的结果,如图8-59所示。

合并不同工作表中相同单元格中的数据

图8-59 合并不同工作表中相同单元格中的数据

Excel 2019按类别合并计算图解

如果用户希望Excel能够根据行列标题的内容智能化地进行合并计算,则可以使用“按类别”对数据进行合并计算的方式。

使用“按类别”对数据进行合并计算的方式,需要在“合并计算”对话框中的“标签位置”列表框中选中“首行”或“最左列”复选框,也可以同时选中两个复选框。

按类别对数据进行合并计算的具体操作步骤如下。

STEP01:选择作为合并计算的结果的存放起始位置,例如这里选择A9单元格,切换至“数据”选项卡,在“数据工具”组中单击“合并计算”按钮,打开如图8-50所示的“合并计算”对话框。

STEP02:在对话框中单击“函数”选择框右侧的下拉按钮,在展开的下拉列表中选择“求和”选项,在引用位置文本框中依次添加“成绩1”和“成绩2”数据所在的单元格区域“$A$2:$C$6”和“$E$2:$G$6”,然后在“标签位置”列表框中勾选“首行”复选框,单击“确定”按钮完成合并计算,如图8-50所示。

STEP03:此时,工作表中会显示按“首行”标签合并计算的结果,如图8-51所示。

图8-50 设置合并计算标签位置

图8-51 “首行”合并计算

STEP04:重新选择作为合并计算的结果的存放起始位置,例如这里选E9单元格,切换至“数据”选项卡,在“数据工具”组中单击“合并计算”按钮,打开如图8-52所示的“合并计算”对话框。默认函数设置和引用位置设置,然后在“标签位置”列表框中添加勾选“最左列”复选框,最后单击“确定”按钮完成合并计算。

STEP05:此时,工作表中会显示按“首行”标签和“最左列”标签合并计算的结果,如图8-53所示。

图8-52 添加勾选标签位置

“首行”和“最左列”合并计算

图8-53 “首行”和“最左列”合并计算

STEP06:重新选择作为合并计算的结果的存放起始位置,例如这里选择A16单元格,切换至“数据”选项卡,在“数据工具”组中单击“合并计算”按钮,打开如图8-54所示的“合并计算”对话框。默认函数设置和引用位置设置,然后在“标签位置”列表框中取消勾选“首行”复选框,最后单击“确定”按钮完成合并计算。

STEP07:此时,工作表中会显示按“最左列”标签合并计算的结果,如图8-55所示。

图8-54 取消勾选“首行”复选框

图8-55 “最左列”合并计算

STEP08:选择按“首行”标签合并计算出的单元格区域“A9:C13”,切换至“数据”选项卡,单击“数据工具”组中的“合并计算”按钮,打开“合并计算”对话框。在“标签位置”列表框中添加勾选“最左列”复选框,然后单击“确定”按钮,如图8-56所示。

STEP09:此时,工作表中会显示按“最左列”标签合并计算的结果,如图8-57所示。

添加勾选标签位置

图8-56 添加勾选标签位置

添加“最左列”标签效果

图8-57 添加“最左列”标签效果

在使用按类别的合并方式时,如果源数据表中数据记录的排列顺序不同,“成绩1”和“成绩2”中“学生姓名”的排列顺序是不同的,在“按类别”合并计算的过程中,Excel会自动地根据记录标题的分类情况,合并相同类别中的数据内容。

在使用按类别合并时,源数据工作表必须包含行或列标题,并且在“合并计算”对话框中选中相应的复选框。

在按类别对数据进行合并计算时,首先要选中“合并计算”对话框中的“首行”或“最左列”两个复选框,才能实现按类别合并计算。如果用户需要根据列标题进行分类合并计算,则选中“首行”复选框;如果用户需要根据行标题进行分类合并计算,则选中“最左列”复选框;如果用户需要同时根据行标题和列标题进行分类合并计算,则同时选中“首行”和“最左列”两个复选框。

如果源数据表中没有列标题或行标题,只有数据记录,而这时用户又选择了“首行”和“最左列”,则Excel将源数据表中的第1行和第1列分别默认为行标题和列标题。

如果用户同时选中“首行”和“最左列”两个复选框,Excel将按照源数据表中的数据的单元格位置进行计算,但不会自动分类。

通过以上的两个实例,可以简单地总结出合并计算功能的一般性规律:

1)当数据表中的列标题和行标题完全一致时,合并计算所进行的操作是按相同的行或列的标题项进行计算,这些计算包含求和、计数及求平均值等。

2)当数据表中的行标题和列标题不相同时,合并计算则会进行分类合并的操作,即把不同的行或列的数据根据内容进行分类合并,把有相同标题内容的合成一条记录,不同标题内容的则形成并列的多条记录,最后形成的表格中将包含源数据表中所有的行标题和列标题。

Excel 2019按位置合并计算

合并计算的方法有两种,一种是按位置对数据进行合并计算,二是按类别对数据进行合并计算。本节将通过具体实例介绍按位置对数据进行合并计算的具体操作步骤。

打开“按位置合并计算.xlsx”工作簿,工作表中会显示如图8-44所示的两个数据表格,使用合并计算可以轻松地对“成绩1”和“成绩2”进行汇总,具体操作步骤如下。

STEP01:选择作为合并计算的结果的存放起始位置,例如这里选择A9单元格,切换至“数据”选项卡,在“数据工具”组中单击“合并计算”按钮,打开“合并计算”对话框,如图8-45所示。

STEP02:在对话框中单击“函数”选择框右侧的下拉按钮,在展开的下拉列表中选择“求和”选项,单击“引用位置”文本框右侧的单元格引用按钮,在打开的文本框中输入“成绩1”数据所在的单元格区域“$A$3:$C$6”,并单击“添加”按钮,如图8-46所示。

图8-44 目标数据

单击“合并计算”按钮

图8-45 单击“合并计算”按钮

STEP03:在“合并计算”对话框中再次单击“引用位置”文本框右侧的单元格引用按钮,在打开的文本框中输入“成绩2”数据所在的单元格区域“$E$3:$G$6”,并单击“添加”按钮,如图8-47所示。

设置计算函数

图8-46 设置计算函数

图8-47 添加引用位置

STEP04:所有引用位置添加完成后,效果如图8-48所示。最后单击“确定”按钮即可完成合并计算,按位置合并计算的结果如图8-49所示。

图8-48 完成设置

按位置合并后的结果

图8-49 按位置合并后的结果

用户可以看到按位置合并后的数据内容,它不包含行标题和列标题。在按位置进行合并的方式中,Excel不注重多个源数据表的行列标题内容是否一致,它只是单纯地对相同表格位置上的数据进行了合并计算。