外觀
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 |