Skip to content

Productivity › 試算表 › 資料準備流程

Excel 資料準備流程 (Data Preparation Workflow) ​

分析品質通常先被資料品質限制。資料準備把原始表逐步變成 乾淨 → 型別正確 → 補齊資訊 → 可篩選 → 可彙總 的分析底稿;順序對了,後面的公式、樞紐分析與圖表才不會一直返工。

流程 ​

text
1. 匯入 ──► 2. 檢查型別與格式 ──► 3. 去重、修錯、補缺
   ──► 4. 衍生欄位(文字 / 日期)──► 5. 旗標與分類(邏輯函數)
   ──► 6. 查找補資訊 ──► 7. 用樞紐分析表檢查是否可分析
步驟工具檢查點
1. 匯入資料 → 從文字/CSV、Power Query分隔符號拆對了嗎?多檔的欄位語意與粒度一致嗎?
2. 型別數值格式、ISNUMBER、ISTEXT日期被讀錯?ID 前導零消失、被轉科學記號?
3. 清理排序 / 篩選、移除重複、尋找與取代、TRIM重複是錯誤還是合法事件?
4. 衍生文字、日期函數、快速填入衍生欄位抽查幾列是否正確
5. 分類IF、IFS、AND / OR每列都有分類?沒有落入意外的 fallback?
6. 補資訊XLOOKUP有多少列查不到(#N/A)?
7. 檢查樞紐分析表類別拼法一致?日期能分組?數值能加總?

1–2. 匯入與型別 ​

問題症狀處理
日期被讀成文字或錯誤格式靠左對齊、無法排序或分組匯入時指定欄位型別;DATEVALUE;資料 → 資料剖析
數字被當文字綠色三角形警告、SUM 為 0「轉換成數字」、VALUE()、乘以 1
ID 前導零消失、長數字變科學記號00123 → 123;17 位數尾數變 0匯入時把欄設為「文字」
基因名稱被轉成日期MARCH1 → 1-Mar同上;用 Power Query 匯入
編碼錯誤中文亂碼從文字/CSV 匯入時選 65001: Unicode (UTF-8)

型別確認先於大量寫公式。多來源資料要先確認每份的欄位語意與粒度(每列是一筆交易?一位客戶?一天?)一致,否則合併後的數字沒有意義。

3. 清理 ​

工具用途注意
排序 / 篩選最便宜的初步 QA:空白、混雜格式、拼字不一致、不合理數值篩選下拉清單直接列出所有相異值,拼字錯誤一目了然
移除重複 (Remove Duplicates)依選定的 key 欄移除重複列先問「這是重複紀錄,還是合法的重複事件?」;用哪幾欄判斷重複?刪之前先複製一份原始資料
快速填入 (Flash Fill, Ctrl + E)依範例推斷模式:從全名拆姓、格式化電話規則複雜時會猜錯,務必抽查;結果是靜態值,來源變了不會更新
填滿數列 (Fill Series)連續日期、編號—
TRIM、CLEAN、PROPER去多餘空白、不可列印字元、統一大小寫外部系統匯出的文字欄幾乎都需要

4. 衍生欄位 ​

函數語法見 Excel 函數。

目的公式例
檢查 ID 長度是否一致=LEN(A2),再篩選不等於預期長度的列
組合鍵 (composite key)=A2 & "_" & B2
多欄合併成一欄、略過空白=TEXTJOIN(", ", TRUE, C2:E2)
帳齡 / 逾期天數=TODAY() - D2
星期幾(週一 = 1)=WEEKDAY(D2, 2)
預估完成日(排除週末與假日)=WORKDAY(D2, 10, Holidays)

5. 旗標與分類 ​

很多準備工作不是「算答案」,而是把原始值轉成容易篩選、分組、聚合的欄位。

目的公式例
高價值客戶旗標=IF(AND(C2>=1000, D2="VIP"), 1, 0)
任一條件成立=IF(OR(E2="Late", F2>30), "risk", "ok")
排除某類=IF(NOT(G2="Test"), "keep", "drop")
多層分類=IFS(C2>=1000, "high", C2>=500, "mid", TRUE, "low")

巢狀 IF 超過三層就難以檢查;把門檻寫成一張對照表再用查找,規則改了只要改表。

6. 查找補資訊 ​

依 ID 從對照表帶回名稱、類別:=XLOOKUP(A2, Ref[ID], Ref[Name], "not found")。舊版用 VLOOKUP(..., FALSE),第 4 參數一定要寫 FALSE(精確比對),否則預設近似比對會默默回傳錯的值。橫向表頭用 HLOOKUP。

完成後篩選 "not found" / #N/A,數一下有多少列沒對上,並找出原因(型別、空白、拼字)。

7. 用樞紐分析表檢查 ​

樞紐分析表 (PivotTable) 能快速暴露尚未準備好的資料:

現象代表
同一類別出現多次(Taipei、taipei )大小寫、空白不一致
日期欄無法「群組」有文字型日期或空白
數值欄只能「計數」不能「加總」混入文字
(空白) 項目很多關鍵欄位缺值

樞紐分析做得順,就能進入 資料分析流程。

何時離開 Excel ​

訊號改用
每週重做同一套清理Power Query
多來源、多檔案整併Power Query、pandas
公式又長又難查錯、需要版本控管Python / R 腳本
資料量讓活頁簿明顯變慢資料庫、pandas

通用的資料清理觀念(缺值、離群值、重複)見 資料品質 與 資料整理與 EDA。