Excel之数据管理规范

标准化的数据源是数据透视表从多个角度汇总、分析和呈现数据的前提。不规则的数据源会导致数据透视表创建失败、汇总统计错误、自动组合失败等问题。本章重点介绍日常工作中对非标准数据源进行排序的原始数据管理规范和技术。

数据管理规范

工作数据源众多且复杂，缺乏标准化的原始数据给后续数据透视表的创建和使用带来诸多障碍。要获得标准化的数据源，首先必须了解以下数据管理规范。

(1)Excel工作簿的名称不能含有非法字符。

(2)数据源不得包含空数据行或空列。

(3)数据源列字段名不能重复。

(4)数据源不得包含连接的单元格。

(5)数据源不得包含多层标题且只有一个标题行。

(6)数据源不得包含汇总数据的小计行或合计行。

（七）数据源的数据格式必须统一、规范。

(8)可以放在单个工作表上的数据源不应拆分为多个工作表。

(9)可以放在一个工作簿中的数据源不应拆分为多个工作簿。

01Excel工作簿名称不能包含非法字符

工作簿名称不能包含“/:*?'”等非法字符。不禁止使用“[”或“]”字符，但如果用于创建数据透视表的工作簿名称包含“[”或“]”字符，则创建数据透视表将失败。提示“数据源引用无效”，如图1所示。

图1数据源引用无效

02数据源不得包含空数据行或列

默认情况下，数据透视表将数据作为数据源呈现在连续的非空列（行）中，因此您在创建数据透视表时只需选择数据源中的任意单元格。如果数据源包含空列或空行，则创建数据透视表时默认选择的数据区域不包含所有数据，如图2所示。

图2空行（列）导致数据透视表的默认数据源不完整

数据透视表不允许字段名称为空，因此如果引用空列的数据源来创建数据透视表，会提示字段名称错误，导致创建失败，如图3所示。

图3报告字段名称无效

03数据源字段名称不能重复

如果数据源列字段名称重复，则生成的数据透视表自动添加数字来区分多个字段会造成混乱，因此列字段名称不应重复，直观地反映列数据的含义。

04数据源不得包含连接的单元格

只有相连单元格左上角的单元格包含数据信息。如果数据源包含合并单元格，数据透视表可能不会产生预期的统计结果。

05数据源不得包含多层标头

无论数据源有多少个标题行，只有一行可以用作数据透视表中的字段。由于单元格串联，多行标题还可能具有空白或重复的字段名称，如图4所示。

图4合并单元格时，创建数据透视表时显示无效字段名

06数据源不得包含小计或总计行

某些ERP系统导出包含小计或总计行的数据源。如果数据源包含小计行或总计行，这将导致数据透视表被计数两次，从而返回不正确的结果。

07数据源的数据格式要统一、规范

如果数据源的数据格式不规范，就会导致数据透视表统计和汇总出现错误。例如，文本数字通常无法参与计算，导致汇总出错，组合非标准日期时无法进行自动分组，大大降低了工作效率。

08不要将数据源拆分为多个工作表

如果数据源驻留在多个工作表中，则必须使用多个合并计算区域、SQL语句或VBA代码为多个工作表创建数据透视表，这会给后续添加、更新和传输数据带来很多不便。

09不要将数据源拆分为多个工作簿

如果数据源在多个工作簿中，则不利于更新和传递数据透视表。

组织不规则数据的技巧

处理合并单元格：拆分合并单元格

图5显示了该公司的销售清单。“产品类别”字段包含合并的单元格。具体举措如下。

图5合并单元格分割并填充

01步骤1

选择A列中的一系列单元格，单击【开始】→【合并并居中】选项卡，如图6所示。

图6拆分合并单元格

02步骤2

按组合键或功能键打开[定位]对话框，然后单击[定位条件]按钮。在【位置条件】弹出窗口中选择【空值】，然后单击【确定】，如图7所示。

图7零值的定位

03第三步

在编辑框中输入公式“A2”，然后按组合键，如图8所示。

图片8批量填写单元格

04步骤4

选中A列数据区域，按下组合键复制该列，右键单击A列数据区域，在弹出的菜单中点击【粘贴选项】下的【数值】，如图。9

图9粘贴为特殊值