Excel 数据透视表筛选汇总结果

在使用数据透视表时,除了可以对汇总数据进行排序和更改汇总方式之外,还可以对汇总的结果进行筛选。使用筛选功能可以完成许多复杂的操作,下面通过实例说明筛选汇总结果的具体操作步骤。

步骤1:打开要对汇总结果进行筛选的数据透视表,如图24-35所示。

步骤2:右击“步步高”,然后指向弹出菜单中的“筛选”命令,并从子菜单中选择一种筛选方式,例如此处单击“仅保留所选项目”命令,如图24-36所示。筛选结果如图24-37所示。

图24-35 打开数据透视表

图24-36 单击“仅保留所选项目”命令

步骤3:右击“步步高”,然后选择“筛选”|“从‘产品名称’中清除筛选”命令,清除筛选结果,此时数据透视表将恢复到打开时的状态。

步骤4:单击“业务员”右侧的箭头,然后在弹出框中只选中“陈圆圆”和“李红艳”两个复选框,如图24-38所示。

图24-37 筛选结果

图24-38 只选中“陈圆圆”和“李红艳”两个复选框

步骤5:单击“确定”按钮,可以看到筛选结果如图24-39所示。

步骤6:单击“业务员”右侧的箭头,然后在弹出框中单击“从‘业务员’中清除筛选”命令,清除筛选结果。

步骤7:单击“业务员”右侧箭头,然后在弹出框中选择“值筛选”|“大于”命令,如图24-40所示。

步骤8:在“值筛选(业务员)”对话框中设置各选项,以筛选销售额大于或等于30000的业务员,如图24-41所示。

步骤9:单击“确定”按钮,可以看到筛选结果如图24-42所示。

图24-39 筛选结果

图24-40 选择“值筛选”|“大于”命令

图24-41 设置值筛选选项

图24-42 筛选结果

Excel 更改数据透视表的汇总方式

默认情况下,数据透视表的汇总方式为求和汇总,也可以根据需要将其更改为其他汇总方式,如平均值、最大值、最小值、计数等。下面通过实例说明如何更改数据透视表的汇总方式。

步骤1:打开要更改汇总方式的数据透视表。

步骤2:右击数值区域的任一单元格,然后指向弹出菜单中的“值汇总依据”,从子菜单中选择一种汇总方式,如此处单击“计数”命令,如图24-32所示。更改汇总方式后的数据透视表如图24-33所示。

提示:也可以右击数值区域的任一单元格,然后单击弹出菜单中的“值字段设置”命令,打开“值字段设置”对话框,在“值汇总方式”选项卡“计算类型”列表中选择一种汇总方式,如图24-34所示。

图24-32 单击“计数”命令

图24-33 更改汇总方式后的数据透视表

图24-34 从“计算类型”列表中选择一种汇总方式

Excel 更改数据透视表的排序方式

在数据透视表中可以方便地对数据进行排序,下面通过实例介绍具体操作步骤。

步骤1:打开要进行排序操作的数据透视表文件,如图24-27所示。

步骤2:单击“产品名称”列右侧箭头,然后单击弹出菜单中的“降序”命令,如图24-28所示。

图24-27 打开数据透视表文件

图24-28 单击“降序”命令

步骤3:经过以上操作,产品名称更改为按降序排列,如图24-29所示。

图24-29 产品名称更改为按降序排列

步骤4:单击“产品名称”列右侧箭头,然后单击弹出菜单中的“其他排序选项”命令,打开“排序(产品名称)”对话框。

提示:也可以单击“产品名称”列下方的任一单元格,然后选择“数据透视表工具”|“选项”|“排序”命令,打开“排序(产品名称)”对话框。

步骤5:选中“升序排序(从A到Z)依据”单选按钮,并在下方列表中选择“产品名称”,如图24-30所示。

步骤6:单击“确定”按钮,将产品名称的排序方式改回升序。

提示:如果要设置更多的排序选项,可以单击“排序(产品名称)”对话框中的“其他选项”按钮,打开“其他排序选项(产品名称)”对话框,然后设置自动排序、主关键字排序次序、排序依据、方法等选项,如图24-31所示。例如,如果希望每次更新报表时都自动排序数据,则选中“每次更新报表时自动排序”复选框。

图24-30 选中“升序排序(从A到Z)依据”单选按钮

图24-31 “其他排序选项(产品名称)”对话框

Excel 显示与隐藏字段列表

在默认情况下,当选中数据透视表中的任一单元格时,在窗口右侧就会显示数据透视表字段列表。如果数据透视表占用屏幕空间比较大,而暂时又不需要使用字段列表时,可以将其隐藏,当需要时再将其显示出来。

如果要隐藏字段列表,可以按照以下步骤进行操作。

步骤1:右击数据透视表中的任一单元格。

步骤2:单击弹出菜单中的“隐藏字段列表”命令,如图24-25所示。隐藏字段列表后的窗口如图24-26所示。

图24-25 单击“隐藏字段列表”命令

图24-26 隐藏字段列表后的窗口

如果要显示字段列表,可以按照以下步骤进行操作。

步骤1:右击数据透视表中的任一单元格。

步骤2:单击弹出菜单中的“显示字段列表”命令。

Excel 刷新数据透视表

如果修改了工作表中数据透视表的源数据,数据透视表并不会自动随之发生相应的变化,需要用户手动进行刷新,下面通过实例介绍刷新数据透视表的具体操作步骤。

步骤1:打开包含要修改的数据透视表及源数据的工作表,如图24-22所示。

步骤2:修改工作表中的源数据,如图24-23所示。

图24-22 打开工作表

图24-23 修改源数据

步骤3:右击数据透视表中的任意单元格,然后单击弹出菜单中的“刷新”命令。刷新数据透视表前后的结果如图24-24所示。

图24-24 刷新数据透视表前后的结果

Excel 移动数据透视表

有时可能需要移动数据透视表的位置,以便在原来的位置插入工作表单元格、行或列等其他内容。如果要移动数据透视表,可以按照以下步骤进行操作。

步骤1:单击要移动的数据透视表。

步骤2:单击“分析”选项卡“操作”组中的“移动数据透视表”命令,打开“移动数据透视表”对话框,如图24-21所示。

图24-21 “移动数据透视表”对话框

步骤3:在“选择放置数据透视表的位置”下,执行以下操作之一。

  • 如果要将数据透视表放入一个新的工作表(从单元格A1开始),则单击“新工作表”单选按钮。
  • 如果要将数据透视表放入现有工作表,则选择“现有工作表”单选按钮,然后在要放入数据透视表的单元格区域中单击第一个单元格。

步骤4:单击“确定”按钮。

Excel 复制数据透视表

通过复制数据透视表,可以有效地备份数据。如果要复制数据透视表,可以按照以下步骤进行操作。

步骤1:打开要复制的数据透视表,并单击数据透视表中的任一单元格,此时在功能区将显示“数据透视表工具”。

步骤2:单击“分析”选项卡中“操作”组的“选择”|“整个数据透视表”命令,将整个数据透视表选中,如图24-19所示。

图24-19 单击“整个数据透视表”命令

步骤3:在数据透视表中右击,然后单击弹出菜单中的“复制”命令,此时的数据透视表周围出现一个虚框,如图24-20所示。

图24-20 数据透视表周围出现一个虚框

步骤4:选择要将数据透视表复制到的位置所在的单元格,然后右击单元格并单击弹出菜单中的“粘贴”命令,将数据透视表粘贴到该位置。

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列分别默认作为行标题和列标题。

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

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