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代碼創建多工作表的數據透視表，且可能會給后期數據的添加、更新和文件的傳遞帶來諸多不便。

能在一個工作簿中放置的數據源不要拆分到多個工作簿中當數據源位于多個工作簿中時，不利于數據透視表的更新和傳遞。