Excel数据管理规范

Excel2016数据透视表应用大全

工作中的数据来源纷繁芜杂，没有规范的原始数据，这会给后期创建和使用数据透视表带来层层障碍。磨刀不误砍柴工，要得到规范的数据源，需要先了解以下数据管理规范。

（1）Excel工作簿名称中不能包含非法字符。

（2）数据源中不能包含空白的数据行和数据列。

（3）数据源不能包含多层表头，有且仅有一行标题行。

（4）数据源的列字段名称不能重复。

（5）数据源的列字段中不能包含由已有字段计算得出的字段。

（6）数据源不能包含对数据分类汇总的小计行或总计行。

（7）数据源不能包含合并的单元格。

（8）数据源中的数据格式要统一和规范。

（9）能在一个工作表中放置的数据源不要拆分到多个工作表中。

（10）能在一个工作簿中放置的数据源不要拆分到多个工作簿中。

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

创建数据透视表的工作簿名称中如果包含字符“［”或“］”，会导致无法创建数据透视表。提示“数据源引用无效”，如图2-1所示。

图2-1数据源引用无效

需要将Excel工作簿名称中的字符“［”或“］”去除，即可正常创建数据透视表。

数据源中不能包含空白的数据行或数据列

数据透视表的数据源中如果包含空列或空行，会导致创建数据透视表时默认选择的数据区域范围不能包含全部数据。数据透视表默认将连续非空列（行）字段的数据作为数据源，如果出现空列（行），数据源区域在默认选择时将被隔断，如图2-2所示。

图2-2空列导致数据透视表数据源不完整

当用户手动选择数据源为单元格区域A1:E1722时，在创建数据透视表时会提示数据源引用无效，导致数据透视表创建失败，如图2-3所示。

图2-3提示数据源引用无效

数据透视表的数据源中如果包含空行（行），虽然可以创建数据透视表，但是可能会在使用中返回非预期的结果。

数据源不能包含多层表头，有且仅有一行标题行

当数据源包含多层表头时，会导致创建数据透视表时选择的数据源区域不能包含全部标题行，只将最下方的一行标题行作为字段。图2-4所示的数据透视表的数据源不包含第一行。

需要将表格规范为包含且只包含一行标题行，如图2-5所示。

图2-4多层表头数据源创建数据透视表

图2-5只包含一行标题行的数据源

数据源的列字段名称不能重复

当数据源的列字段名称重复时，创建的数据透视表会自动在字段名称后加上数字以区分多个字段，这样的数据透视表字段列表可读性较差，在进行统计汇总时容易造成字段拖放混乱。因此，列字段名称应使其不重复且能直观反映该列数据代表的含义。

数据源的列字段中不能包含由已有字段计算得出的字段

当数据源中包含由已有字段计算得出的字段时，创建的数据透视表可能会返回错误的统计结果。

图2-6所示的数据源中，“回报率”是根据“消耗”和“收入”计算得出的，公式如下。

图2-6数据源包含计算字段

回报率=收入/消耗

以此数据源创建的数据透视表如图2-7所示，观察“回报率”字段下的分类汇总和列总计值，出现统计错误。

图2-7回报率被错误统计

规避方法是在数据透视表中插入计算字段，如图2-8所示。在数据透视表中插入计算字段的方法，请参阅10.5.1小节。

图2-8插入计算字段

数据源不能包含对数据分类汇总的小计行或总计行

有些ERP系统导出的数据源含有分类汇总的小计行或总计行。当数据源中包含小计行或总计行时，会导致创建的数据透视表在统计时重复求和，从而返回错误的结果。故在创建数据透视表前需要先删除多余的小计行与总计行。

数据源不能包含合并的单元格

由于合并的单元格中只有最左上角的单元格有数据信息，因此当数据源含有合并的单元格时，可能会导致数据透视表无法返回预期的统计结果。

数据源中的数据格式要统一和规范

当数据源中的数据格式不规范时，会导致数据透视表在统计与汇总时出错，如文本数字不能正常参与计算导致汇总时出错；不规范日期进行组合时不能自动分组，从而大大降低工作效率。

能在一个工作表中放置的数据源不要拆分到多个工作表中

数据源位于多个工作表中时，需要使用多重合并计算区域、SQL语句或VBA代码创建多工作表的数据透视表，且可能会给后期数据的添加、更新和文件的传递带来诸多不便。

能在一个工作簿中放置的数据源不要拆分到多个工作簿中当数据源位于多个工作簿中时，不利于数据透视表的更新和传递。

END