下载掌阅APP，畅读海量书库
立即打开
格式不规范的数据源很容易导致数据透视表出现数据不完整或显示有误等问题。在创建数据透视表之前，有必要检查数据源的格式，对格式不规范的数据源进行必要的整理。数据源的常见问题有以下几个：
● 数据源缺少标题行。
● 数据源包含空单元格。
● 数据源包含空行和空列。
● 数据源的某些包含数值的单元格中包含空格。
● 数据源是二维表，同类信息分散在不同的列中。
本节将针对以上几个问题来介绍如何整理和修复格式不规范的数据源。
如果数据源中的每列数据的顶部没有标题，在创建的数据透视表中将自动使用每列中的第一项数据作为该列的字段标题，这样会导致数据透视表出现结构错误和数据丢失的问题。如图2-30所示的数据源的第一行没有标题，使用这种结构的数据源创建的数据透视表如图2-31所示。
图2-30 缺少标题行的数据源
图2-31 使用缺少标题行的数据源创建的数据透视表
解决方法：在数据源的第一行为各列数据添加可以概括描述数据含义的标题。如图2-32所示是为各列数据添加标题后的数据源。
图2-32 为数据源中的各列数据添加标题
虽然数据源中包含空单元格并不影响数据透视表的创建，但是在对数据透视表中的数据进行后续处理时可能会出现一些问题。
解决方法：将数据源中的空单元格使用同类型的默认值进行填充，数值型数据的空单元格使用0来填充，文本型数据的空单元格使用相同文本来填充。
如图2-33所示，A列包含合并单元格，虽然从显示的角度而言，这种格式便于查看，但是却不利于Excel处理，在创建数据透视表时会存在一些隐患。
图2-33 包含合并单元格的数据源
对于这种格式的数据，首先取消单元格的合并状态，然后为取消合并后出现的空单元格填充相应的文本。操作步骤如下：
（1）选择A列，然后按F5键，打开“定位”对话框，单击“定位条件”按钮，如图2-34所示。
（2）打开“定位条件”对话框，选中“空值”单选按钮，然后单击“确定”按钮，如图2-35所示。
图2-34 单击“定位条件”按钮
图2-35 选中“空值”单选按钮
（3）A列中的所有合并单元格将被全部选中，保持选中状态不变，在功能区的“开始”选项卡中单击“合并后居中”按钮，取消这些单元格的合并状态，如图2-36所示。
图2-36 单击“合并后居中”按钮
（4）取消单元格的合并后，会在A列出现一些空单元格。保持第（3）步操作后的选区不变，重复执行第（1）步和第（2）步操作，将选中A列位于数据区域中的空单元格。保持选区不变，输入一个等号，然后按一次“上”箭头键，如图2-37所示。
（5）按Ctrl+Enter快捷键，将在每个空单元格中填充位于该单元格上方的文字，如图2-38所示。
图2-37 在空单元格中输入公式
图2-38 将文字自动填入空单元格
如图2-39所示，E列是员工的工资，其中存在一些空单元格。由于E列数据都是数值，因此应该使用0来填充E列中的空单元格。操作方法与前面介绍的类似，只是省去了取消合并单元格的步骤，而且在选中空单元格后，不需要输入等号，只需输入0后按Ctrl+Enter快捷键即可，填充结果如图2-40所示。
图2-39 数据源中包含空单元格
图2-40 使用0填充空单元格
如果在数据源中包含空行或空列，那么在创建数据透视表时，Excel自动捕获的数据源范围会截止到空行或空列的位置，导致在创建的数据透视表中丢失部分数据。如图2-41所示，第6行和第16行是空行，创建数据透视表之前，活动单元格的位置将决定Excel默认使用哪个区域作为数据源：
● 如果活动单元格位于第6行以上的位置，Excel会将数据源的范围指定为A1:E5。
● 如果活动单元格位于第16行以下的位置，Excel会将数据源的范围指定为A17:E21。
● 如果活动单元格位于第6行与第16行之间的位置，Excel会将数据源的范围指定为A7:E15。
图2-41 数据源中包含空行
解决方法：删除数据源中的所有空行和空列，保持数据连续分布。如果数据源的行数和列数较少，可以通过Ctrl键和单击来同时选择多个空行或空列，然后右击选中的任意一行或一列，在弹出的菜单中选择“删除”命令执行删除操作。否则想要快速删除数量较多的空行或空列，需要使用其他方法。下面将分别介绍删除空行和空列的方法。
如图2-42所示，数据源包含多个空行，将这些空行删除的操作步骤如下：
（1）在数据区域右侧的一个空列中（如F列），输入从1开始的自然数序列，如图2-43所示。
图2-42 包含多个空行的数据源
图2-43 在一个空列中输入自然数序列
（2）在A列中单击任意一个包含数据的单元格，然后在功能区的“数据”选项卡中单击“升序”按钮（“降序”按钮也可以），对A列数据升序排列，如图2-44所示。
（3）升序排序后，数据源中的所有空行将位于数据区域的底部，如图2-45所示，选中这些空行并将其删除。
图2-44 对A列数据进行升序排列
图2-45 排序后的空行位于数据区域的底部
（4）在辅助列中单击任意一个包含数字的单元格，然后对该列进行升序排列，使数据恢复最初的位置，如图2-46所示。
图2-46 删除数据源中的所有空行
如图2-47所示，数据源包含多个空列，将这些空列删除的操作步骤如下：
图2-47 包含多个空列的数据源
（1）在A列数据下方的空单元格中输入下面的公式，统计A列包含数据的个数，如果公式返回0，则说明A列不包含数据。将该公式复制到同行的其他单元格，统计其他列包含数据的个数，如图2-48所示。
图2-48 使用公式统计各列包含数据的个数
（2）选择第（1）步输入公式的所有单元格，按Ctrl+F快捷键，打开“查找和替换”对话框的“查找”选项卡，在“查找内容”文本框中输入0。然后单击“选项”按钮，将“查找范围”设置为“值”，如图2-49所示。
（3）单击“查找全部”按钮，在展开的窗格下方显示了选区中所有包含0的单元格的相关信息，按Ctrl+A快捷键选中所有找到的单元格，如图2-50所示。
图2-49 设置查找选项
图2-50 通过查找功能选中所有空列中的一个单元格
（4）单击“关闭”按钮关闭“查找和替换”对话框。在工作表中右击第（3）步选中的任意一个单元格，在弹出的菜单中选择“删除”命令，然后在打开的“删除”对话框中选中“整列”单选按钮，如图2-51所示。
图2-51 选中“整列”单选按钮
（5）单击“确定”按钮，删除数据区域中的所有空列，最后删除包含公式的行即可。
如果数据源中的某些单元格中包含空格，那么在创建数据透视表之后，数据的统计结果可能会出错。错误通常来源于包含数值的字段，因为空格一旦出现在包含数值的单元格中，那么原来的数值就会被附加的空格转换为文本格式。
前面曾经介绍过，Excel默认对数值型数据执行求和计算，对文本型数据统计个数。因此，原本应该对数值进行求和计算，但是由于在数值中附加了空格，变成将数值当作文本来统计个数，导致统计结果出错。这类问题通常来源于由其他程序创建的数据。
解决方法：使用Excel中的TRIM函数删除单元格中的多余空格。如果单元格中还包含制表符、强制换行符等不可见的字符，可以使用CLEAN函数来删除它们。
二维表在统计和分析数据时非常有用，因为它可以同时在水平和垂直两个方向上呈现数据。但是对于创建数据透视表的数据源而言，二维表没有任何价值。然而，创建数据透视表之后得到的报表则是二维表。
如图2-52所示是一个二维表，它在行、列两个方向上展示了电视、空调、冰箱在三季度每个月的销售情况。
图2-52 二维表
解决方法：用于创建数据透视表的数据源必须是数据列表，它是一维表，因此需要将二维表转换为一维表。数据列表是由多行多列数据构成的信息集合，每一行是一条记录，每一列表示某一类信息。数据列表的顶部有一行标题，用于描述每列数据的含义。
如图2-53所示是将前面的二维表转换为一维表之后的结果，除了原来的“商品名称”列不变之外，需要在工作表中添加两列，一列为“月份”，一列为“销量”。然后将原来表格中的各个月份放置到新增的“月份”列中，将原来各个月份下的商品销量放置到新增的“销量”列中。
图2-53 将二维表转换为数据列表
