什麼是正確的數據儲存姿勢

根據之前大家的投票結果

餅乾決定每周二更新

報表自動化的實例和教程

然而，報表自動化中90%的問題

都出在原始數據上

這次，餅乾用個最簡單的例子

一共 6 × 9 個單元格

陪大家重新感受一下

什麼才是正確的數據儲存姿勢？

〇、欄位類型

首先，我們把欄位分成 2 類

作為 聚合依據 的維度 和 用於 計算數值 的度量

雖然可以互相轉換

但我們先簡單的認為

• 不可求和的是維度（黃）：日期、學號、姓名

• 可以求和的是度量（藍）：語文、數學、英語成績

而具體的分數稱為 度量值

拓展閱讀

更詳細的說明可以看之前的這篇

這裡有一些你可能需要了解的概念

一、主鍵（Key）

主鍵的叫法源於資料庫

唯一地標識表中的某一行（條）記錄的一個或多個維度欄位

比如這裡的【日期 + 學號】

日期和學號定了，數據的其他值（或者說行號）就定了

想想為什麼【日期 + 姓名】不是主鍵？

規範的數據儲存方式可以避免很多坑

因此，餅乾建議初學者在任何情況下

儘可能使用「表格」的形式儲存數據

並養成

• 修改表名稱

• 標記主鍵列

• 凍結標題行

的好習慣！（點開看動圖）

二、表類型

接下來的工作是一個定性的工作，即

判斷該份數據是維度表還是事實表

這兩個概念源於數據倉庫，先說餅乾的理解

• 維度表：同類型維度的集合，具有層次結構

• 事實表：由各維度表的主鍵及度量組成

試著把我們的數據拆分成標準的維度表和事實表

這裡的【日期】、【工號 + 姓名】

可以分別單獨記錄成維度表，一般加前綴 Dim

還記得么？標記主鍵列（黃字）

再提一點、標記公式列（藍底）

兩份維度表的主鍵【日期】和【學號】加上 3 種【成績】的度量就可以構成事實表，一般加前綴 Fact

和原始數據比，少了【姓名】欄位

也就是說，如果學號【2002】的【乙十】要改名成【丁王】

只需要在【Dim_學生信息】中修改 1 次

而在原先的數據需要修改 2 次

我們稱這種重複為 冗餘（rǒng yú）

一般地

• 由於維度表的體量較小且在 Excel 中可以通過公式構造，因此往往反範式設計，通過增加冗餘信息減少維度表的數量，提高查詢速度。

典型地，如日期表

我們直接構建 1 張維度表

而不是分【日期 + 年月】【年月 + 年份】兩張表

哪怕拆分後數據冗餘更少

• 而事實表則應該盡量避免除維度主鍵外的額外信息

三、數據維數

由於 Excel 本身

有【行】和【列】2 個方向

因此相同的數據也會有不同的存放方式

比如這樣兩份數據

我們把

• 維度只展開在行上的稱為【一維數據】或者【長數據】

• 維度同時展開在行和列上的稱為【二維數據】或者【寬數據】

比如右圖中【日期 + 學號】展開在行上，而【學科】展開在列上

由於

一維數據可以通過數據透視表做進一步分析

因此

我們總是希望以一維的形式儲存數據

但是

實際工作中的數據往往是二維的

二維數據在大部分情況下更為直觀

需要我們做進一步處理轉化成一維

這裡，餅乾推薦

使用 Power Query 完成二維轉一維

操作簡單，且二維數據更新後可以直接刷新得到新的一維數據

1、將表格數據導入 Power Query（2016起叫做查詢）

2、選擇被展開的維度逆透視（也可選擇【日期 + 學號】，然後逆透視其他列）

3、修改欄位名稱（默認為【屬性】和【值】）

4、將結果載入至 Excel 中

這裡提一下，如果選擇【僅創建連接】會保留這次 Power Query 查詢的完整過程，但是不載入到表格中。在之後的報表自動化套路中會經常出現。

四、數據儲存

首先，餅乾定義

將符合第一範式的數據稱為規範數據

• 有且僅有第一行是欄位名

• 沒有合併單元格

• 沒有多個信息在同一個單元格內

• 沒有空行、小計、合計
• 沒有合併單元格

注意！

沒有合併單元格！

重要的事情講 3 遍！

根據數據的獲取範式以及是否規範

餅乾建議採用如下策略對數據進行管理

1、非手工維護、規範數據

以文件名+日期的形式存在單獨文件夾中

• VBA 批量匯總

• Power Query 文件夾匯總

常見的如資料庫查詢結果、BI系統導出數據等

2、手工維護、規範數據

將數據縱向追加在同一個「表格」中

不同數據分多個 Sheet 或多份 Excel 均可

可以視情況使用二維數據，後續

• 通過 Power Query 轉一維

常見的如複製粘貼規範的網頁數據/系統數據/PDF數據

3、非手工維護、不規範數據

儘可能通過修改獲取方式或者代碼實現

每天一份相同格式和欄位的數據

或者

縱向追加在 Sheet 中（不強求用「表格」）

一般是通過 VBA 等進行的網頁抓取數據

在這一階段只需要做到格式統一即可，後續通過

• Power Query 以儲存過程的形式進行數據清洗

4、手工維護、不規範數據

這種情況常見於你的數據

是別人加工後的數據

更多地需要通過溝通解決

如果一定要提個建議的話

請把這篇文章推送給他

小結

數據處理中最大的問題是源頭問題

各中細節不是一篇文章可以講完的

看完這篇文章，餅乾希望你

• 能夠區分【事實表】和【維度表】

• 能夠將【二維數據】轉為【一維數據】

• 試著將數據以以下形式儲存

1. 縱向追加在同一個Sheet/表格

2. 以規範的名稱存在同一個文件夾

這是將會是後續報表自動化及數據分析的基礎！

關注餅乾，少加點班

返回目錄