clean-data-xls

Anthropic ｜資料與分析｜原文｜ Apache-2.0

清理試算表裡的空白、大小寫、日期和重複資料，先列方案再動手

中文翻譯

---
name: clean-data-xls
description: 清理雜亂的試算表資料：去除多餘空白、修正不一致的大小寫、把文字格式的數字轉成數值、統一日期、移除重複，並標記型別混雜的欄位。資料雜亂、不一致，或需要在分析前先整理時使用。觸發詞包含「clean this data」、「clean up this sheet」、「normalize this data」、「fix formatting」、「dedupe」、「standardize this column」、「this data is messy」。
---

# 清理資料

清理作用中工作表或指定範圍裡雜亂的資料。

## 環境

- **如果在 Excel 裡執行（Office Add-in / Office JS）：**直接用 Office JS（`Excel.run(async (context) => {...})`）。用 `range.values` 讀取，用 `range.formulas = [["=TRIM(A2)"]]` 寫入輔助欄位的公式。「就地修改 vs 輔助欄位」的判斷依然適用。
- **如果是處理獨立的 .xlsx 檔：**用 Python/openpyxl。

## 工作流程

### 步驟 1：範圍

- 如果有給範圍（例如 `A1:F200`），就用它
- 否則用作用中工作表的整個已使用範圍
- 對每一欄做剖析：偵測它的主要型別（文字 / 數字 / 日期），並找出離群值

### 步驟 2：偵測問題

| 問題 | 要找什麼 |
|---|---|
| 空白 | 前後多餘的空格、連續兩個空格 |
| 大小寫 | 類別欄位裡不一致的大小寫（`usa` / `USA` / `Usa`） |
| 文字格式的數字 | 存成文字的數值；數字儲存格裡夾雜的 `$`、`,`、`%` |
| 日期 | 同一欄混用不同格式（`3/8/26`、`2026-03-08`、`March 8 2026`） |
| 重複 | 完全重複的列，以及近似重複（只差在大小寫/空白） |
| 空值 | 其他部分都有資料的欄位裡的空儲存格 |
| 型別混雜 | 一欄有 98% 是數字，卻夾了 3 筆文字 |
| 編碼 | 亂碼（`Ã©`、`â€™`）、不可列印的字元 |
| 錯誤 | `#REF!`、`#N/A`、`#VALUE!`、`#DIV/0!` |

### 步驟 3：提出修正方案

在改任何東西之前，先顯示摘要表：

| 欄位 | 問題 | 數量 | 建議修正 |
|---|---|---|---|

### 步驟 4：套用

- **優先用公式，不要寫死清理後的值**：只要清理後的輸出能用公式表達（例如 `=TRIM(A2)`、`=VALUE(SUBSTITUTE(B2,"$",""))`、`=UPPER(C2)`、`=DATEVALUE(D2)`），就把公式寫在相鄰的輔助欄位，不要在 Python 裡算好結果再蓋掉原始資料。這樣轉換過程才透明、可稽核。
- 只有在使用者明確要求，或沒有合理的公式可以對應時（例如編碼/亂碼修復），才用計算出來的值就地覆蓋
- 破壞性的操作（移除重複、填補空值、覆蓋原始資料），要先跟使用者確認
- 每一類修正做完之後（空白 → 大小寫 → 數字轉換 → 日期 → 去重），先給使用者看改了什麼的樣本，確認後再進行下一類
- 回報一份修改前/修改後的摘要，說明改了什麼

英文原文

---
name: clean-data-xls
description: Clean up messy spreadsheet data — trim whitespace, fix inconsistent casing, convert numbers-stored-as-text, standardize dates, remove duplicates, and flag mixed-type columns. Use when data is messy, inconsistent, or needs prep before analysis. Triggers on "clean this data", "clean up this sheet", "normalize this data", "fix formatting", "dedupe", "standardize this column", "this data is messy".
---

# Clean Data

Clean messy data in the active sheet or a specified range.

## Environment

- **If running inside Excel (Office Add-in / Office JS):** Use Office JS directly (`Excel.run(async (context) => {...})`). Read via `range.values`, write helper-column formulas via `range.formulas = [["=TRIM(A2)"]]`. The in-place vs helper-column decision still applies.
- **If operating on a standalone .xlsx file:** Use Python/openpyxl.

## Workflow

### Step 1: Scope

- If a range is given (e.g. `A1:F200`), use it
- Otherwise use the full used range of the active sheet
- Profile each column: detect its dominant type (text / number / date) and identify outliers

### Step 2: Detect issues

| Issue | What to look for |
|---|---|
| Whitespace | leading/trailing spaces, double spaces |
| Casing | inconsistent casing in categorical columns (`usa` / `USA` / `Usa`) |
| Number-as-text | numeric values stored as text; stray `$`, `,`, `%` in number cells |
| Dates | mixed formats in the same column (`3/8/26`, `2026-03-08`, `March 8 2026`) |
| Duplicates | exact-duplicate rows and near-duplicates (case/whitespace differences) |
| Blanks | empty cells in otherwise-populated columns |
| Mixed types | a column that's 98% numbers but has 3 text entries |
| Encoding | mojibake (`Ã©`, `â€™`), non-printing characters |
| Errors | `#REF!`, `#N/A`, `#VALUE!`, `#DIV/0!` |

### Step 3: Propose fixes

Show a summary table before changing anything:

| Column | Issue | Count | Proposed Fix |
|---|---|---|---|

### Step 4: Apply

- **Prefer formulas over hardcoded cleaned values** — where the cleaned output can be expressed as a formula (e.g. `=TRIM(A2)`, `=VALUE(SUBSTITUTE(B2,"$",""))`, `=UPPER(C2)`, `=DATEVALUE(D2)`), write the formula in an adjacent helper column rather than computing the result in Python and overwriting the original. This keeps the transformation transparent and auditable.
- Only overwrite in place with computed values when the user explicitly asks for it, or when no sensible formula equivalent exists (e.g. encoding/mojibake repair)
- For destructive operations (removing duplicates, filling blanks, overwriting originals), confirm with the user first
- After each category of fix (whitespace → casing → number conversion → dates → dedup), show the user a sample of what changed and get confirmation before moving to the next category
- Report a before/after summary of what changed