Excel数据透视表学习笔记03

五、在数据透视表中排序

对于数据列表，Excel按照：数字、文本、逻辑值、错误值（如#REF和#VALUE）和空白单元格的顺序对不同数据类型进行升序排序。降序则为：错误值、逻辑值、文本、数字和空白单元格。文本按照符号、英文字母、中文的顺序排序；中文文本默认按照拼音字母的英文顺序进行排序。

Excel中数据透视表与普通数据列表有着十分相似的排序功能和完全相同的排序规则。

5.1使用手动排序与自动排序

下面三种方法都能数据透视表中所要排序的列进行排序，也可设置根据哪一列降序（或升序）或者其他方式进行排序：

开始----编辑----排序和筛选

数据----排序和筛选

选中要排序的列，单击鼠标右键，排序----其他排序方式

局部排序：使用手动排序可以对数据透视表的局部数据进行排序，选中数据区域，开始----编辑----排序和筛选，可以设置升序或者降序排列。

设置自动排序：选中要排序的列，点击鼠标右键，排序----其他选项----自动排序，勾选每次更新报表时自动排序。

注：使用手动排序后，数据透视表数据源变更了数据，刷新或者重新打开数据透视表，变更的数据不会自动进行相应的排序，需要再次使用手动方法重新排序。

如果需要数据源更新时，数据透视表自动排序，在手动排序时，记得设置自动排序。

ps：书上写的数据透视表字段高级选项----自动排序选项，没找到哇。

5.2按笔划排序

Excel数据透视表中的中文字符排序是按照“拼音字母”顺序进行的，以姓名为例，排序先按姓名第一个字的拼音首字母在26个英文字母中的顺序进行，在首字母相同的情况下，依次比较第2、3个字母，第1个字完全相同，再依次比较姓名中的第2、3个字。

按照“笔划”的顺序来排列姓名，排序规则是：先按照字的笔划数多少排列，如果笔划数相同，则按照起笔顺序排列（横、竖、撇、捺、折），如果笔划数和起笔顺序都相同，则按照字形结构排列，先左右、再上下，最后整体字。但是在Excel中，按笔划排序的规则有所不同，先按字的笔划数多少排列，对于笔划数相同的汉字，Excel按照其内码顺序进行排列，而不是按笔划顺序进行排列。

选中任意一个要按笔划排序的列的任一单元格，单击鼠标右键----排序----其他排序选项----其他选项，取消勾选更新报表时自动排序，选择按笔划排序即可。（ps：不知道为什么不能同时选择更新报表时自动排序和按笔划排序）

5.3在数据透视表中自定义顺序排列

创建“自定义序列”：文件----选项----高级----常规----创建用于排序和填充的列表。

按照升序顺序创建“自定义序列”后，排序时按需选择升序或者降序排列即可。

ps：？书上写的排序方法不可行，如果不取消勾选更新报表时自动排序，无法选择主关键字排序顺序为自定义的序列，还有就是取消勾选更新报表自动排序后，会自动选择按笔划或者字母排列。

但是，在数据透视表工具----分析----数据透视表选项----汇总和筛选----排序，有一个复选框叫排序时使用自定义列表，勾选后，能够自动识别自定义序列用于升降序排列。

注：数据透视表中无论字段项目是纵向排列还是横向排列，均可以直接使用前面介绍的排序方法进行排序。

5.4在数据透视表中使用排序功能的两个例子

1、对销售报表中的销售业绩进行排序，显示按排名显示销售人员的销售数据。

单击销售人员字段----排序----其他排序方式----排序选项----降序排序，选择“求和项：订单金额”，单击“确定”，完成排序。

2、显示销售报表中金额最小的20%部分的数据

（1）对数据透视表设置自动筛选：选中数据透视表字段行区域的最右边一个单元格，这个单元格刚好不属于 数据透视表区域，数据----排序与筛选----筛选，完成对数据透视表的值字段设置自动筛选。

（2）单击“求和项：销售金额”字段右侧的自动筛选标志，数字筛选----前10项。

（3）在最左侧的下拉列表中选择“最小”，在中间的编辑框中输入20，在右侧的下拉列表中选择“百分比”，单击“确定”，完成筛选。

六、数据透视表的项目组合

 数据透视表通过对数字、日期、文本等不同数据类型的数据项采取多种组合方式，大大增强了数据透视表分类汇总的适应性。

6.1组合数据透视表内的文本型数据项

（1）选中数据透视表中所要组合的单元格区域，单击鼠标右键----组合；依次完成每个组合。

（2）对于数据透视表中增加的新字段，可以更改其名字，并依次将合并标志改名为对应的组合前的名字（按需来改也行）。

（3）为数据透视表添加分类汇总项，得到最后的汇总表。

手动组合方式比较灵活，适用于分类项不多的情况下使用，缺点是如果有新增数据项不在组合范围内，则需要重新组合。

6.2组合数据透视表内的数值型数据项

6.2.1按等长步长组合数值型数据项

单击字段名----组合，在打开的自动对话框中的“起始于”、“终止于”、“步长”按需求填入具体数值。单击确定即可；可以按需修改值字段的汇总方式。

这种组合方式对于组合范围内新增的数据可以自动归入对应的范围内，不需要重新调整组合范围。

6.2.2按不等距步长手动组合数值型数据项

选中数据透视表中要组合的每一组的数据所在行，其余步骤跟6.1中组合数据透视表中的文本型数据项一样。

6.2.3按不等距步长自动组合数值型数据项

当需要组合的数据项较多时，且要求新增数据能够自动进行组合，可以借助向数据源中添加辅助列的方法来实现不等距步长数据的自动组合。

（1）在数据源中添加一列辅助列，该辅助列可以利用公式完成，直接在数据源中就对数据实现组合。

（2）利用添加了“辅助列”的数据源创建数据透视表即可。

6.3组合数据透视表内的日期型数据

6.3.1对日期型数据按年月组合

对于日期型数据，数据透视表提供了更多的组合选项，可以按照秒、分、小时、日、月、季度、年等多种时间单位进行组合。

选中“日期”字段中的任意一个项目，单击鼠标右键----组合，可以选择起始日期、终止日期和步长，按需设置即可。

注：当数据源中的日期是跨年度的，在透视表中按“月”进行组合时应该同时也按“年”进行组合，否则不同年份的相同月份的数据会被组合到一起。

6.3.2对日期型数据按周组合

与按年月组合的方式差不多，只需要步长选择“日”，在“天数”中输入7即可。

6.4取消项目的组合

6.4.1取消手动组合

（1）完全取消手动组合

选中要取消的组合的字段名称，单击鼠标右键----取消组合。

（2）局部取消文本型组合项

选中该组合项中的任意一个单元格，单击鼠标右键----取消组合。

数据透视表允许同时选取多个组合项区域，通过上述操作批量取消多个局部组合项。

6.4.2取消数值型或日期型组合项

日期型数据也是一种特殊的数值型数据，取消日期型或者数值型组合项的方法完全相同。

选中日期型字段中的任意一项，单击鼠标右键----取消组合，取消组合后，该字段即可恢复数据源中日期表现方式。

注：只有手动组合的项目才能进行局部取消组合操作。

6.5“选定区域不能分组”及其解决方案

在数据透视表中对数据项进行组合时，“选定区域不能分组”是最常见的问题。导致分组失败的主要原因有三个：一是组合字段的数据类型不一致；二是日期数据格式不正确；三是数据源引用失效。

6.5.1组合字段数据类型不一致导致分组失败

最常见的是组合字段中存在空白。

（1）组合数据项中存在空白的解决方案

对数据源进行处理，将数据源中包含空白内容的记录删除，或者将空白内容替换为零值。

（2）分组字段数据中日期型或数值型数据与文本型的日期或数字并存

先用TYPE函数对数据字段进行测试，查找出文本型数据，将其改为相应的日期型或数值型数据，保持字段数据类型统一。

（3）数据透视表应用数据源时，采取了整列应用

这样会造成引用包括了数据源以外的大量空白区域，导致字段类型不一致。

可以采用公式将数据透视表的数据源定义为一个名称，形成动态源引用（ps：创建动态数据透视表的详细方法见之后的第九节）

6.5.2日期格式不正确导致分组失败

（1）使用TYPE函数测试数据源表中的日期，如果测试结果为2，表示为文本型数据。

（2）选中数据源表中的日期字段，<Ctrl+H>----查找和替换，“查找内容”和“替换内容”都输入“-”，全部替换后，该日期字段数据就会由文本型转换为日期型。

6.5.3数据引用区域失效导致分组失败

当数据透视视表的数据源表页被删除或引用外部数据源不存在时，数据透视表引用区域会产生原来数据源的路径和文件名称，保留一个失效的数据引用区域，从而导致分组失败。

更改数据源即可，数据透视表工具----分析----数据----更改数据源，更改完成后，刷新数据透视表即可。