name: excel-find-duplicates description: | Read-only scan of Excel files to find duplicate rows by specified column(s), outputting duplicate row number lists. Does not modify the original file. Typically used in conjunction with excel-delete for safe, format-preserving deduplication. 只讀掃描 Excel 檔案，按指定列查詢重複行，輸出重複行號列表。不修改原檔案。通常配合 excel-delete 使用實現安全的格式無損去重。 Trigger keywords: "find duplicates" "check duplicates" "scan duplicates" "duplicate rows" "what are the duplicates" 觸發詞包括"查重""找重複""檢查重複""重複行""有哪些重複"。

This skill is read-only, no side effects. Follows [[excel-safe-workflow]] Scout→Analyze two-step approach. 本技能只讀不寫，安全無副作用。遵循 [[excel-safe-workflow]] 勘察→分析兩步。

7w4.net小蔥技能。

Excel Find Duplicates (Read-only) / Excel 查重（只讀）

Function / 功能

1. Scan for duplicate rows by specified column(s) (or multi-column combination) / 按指定列（或多列聯合）掃描重複行
2. Output duplicate statistics and row number list / 輸出重複統計和行號列表
3. Results can be directly passed to [[excel-delete]] for deletion / 結果可直接傳給 [[excel-delete]] 執行刪除

Step 0: Requirement Parsing / 第零步：需求解析

Step 1: Scout (Read-only Scan) / 第一步：勘察（只讀掃描）

import pandas as pd

FILE = 'target.xlsx' / FILE = '目標檔案.xlsx'
KEY_COL = 'Column Name / 列名'      # Key column name / 關鍵列名
KEEP = 'first'        # 'first'=keep first occurrence / 保留首次 / 'last'=keep last / 保留末次

# pandas efficient read (C engine, seconds-level) / pandas 高效讀取（C引擎，秒級）
df = pd.read_excel(FILE)

total = len(df)
mask = df[KEY_COL].duplicated(keep=KEEP)
dup_indices = df.index[mask].tolist()
dup_excel_rows = [i + 2 for i in dup_indices]  # +2: pandas 0-index → Excel row number (row 1=header) / pandas 0-index → Excel行號（第1行=表頭）

print(f'Total rows: {total} / 總行數: {total}')
print(f'Unique values: {total - len(dup_excel_rows)} / 唯一值: {total - len(dup_excel_rows)}')
print(f'Duplicate rows: {len(dup_excel_rows)} ({len(dup_excel_rows)/total*100:.1f}%) / 重複行: {len(dup_excel_rows)}')
print(f'Row range: {min(dup_excel_rows)} ~ {max(dup_excel_rows)}' if dup_excel_rows else 'No duplicates / 無重複')

Multi-Column Joint Dedup / 多列聯合查重

KEY_COLS = ['Col1 / 列名1', 'Col2 / 列名2']  # Multi-column joint / 多列聯合
mask = df.duplicated(subset=KEY_COLS, keep=KEEP)

Step 2: Output Results / 第二步：輸出結果

if not dup_excel_rows:
    print('✅ No duplicates / 無重複資料')
else:
    print(f'\nDuplicate row number list (total {len(dup_excel_rows)} rows) / 重複行號列表（共{len(dup_excel_rows)}行）:')
    print(dup_excel_rows[:20])  # First 20 / 前20個
    if len(dup_excel_rows) > 20:
        print(f'... and {len(dup_excel_rows)-20} more rows / 還有{len(dup_excel_rows)-20}行')

    # Pass to excel-delete for use / 傳遞給 excel-delete 使用
    # Format: [row number list], sort descending then delete_rows one by one / 格式: [行號列表], 從大到小排序後逐個 delete_rows

Working with excel-delete / 與 excel-delete 配合

Find-duplicates output directly feeds into delete input: / 查重輸出直接作為刪除輸入：

excel-find-duplicates → [2, 5, 8, 3, 12, ...] → excel-delete delete bottom-to-top / 從下到上刪除

Delete-side code / 刪除側程式碼：

# Receive find-duplicates results / 接收查重結果
dup_rows = [2, 5, 8, 3, 12, ...]  # From excel-find-duplicates / 來自 excel-find-duplicates

# Delete bottom-to-top (critical! avoids row number shifting) / 從下到上刪除（關鍵！避免行號偏移）
for row in sorted(dup_rows, reverse=True):
    ws.delete_rows(row)

Notes / 注意事項

1. Read-only / 只讀：Does not modify original file, safe to run / 不修改原檔案，放心跑
2. Row numbers are Excel row numbers / 行號是 Excel 行號：Row 1 = header, Row 2 = first data row / 第1行=表頭，第2行=第一條資料
3. Large files / 大檔案：pandas reading 168MB/330K rows takes ~150s / pandas 讀取 168MB/33萬行約 150s
4. Null values / 空值：Multiple rows with None in the key column are treated as "duplicates", only the first is kept / 關鍵列為 None 的多個行會被視為"重複"，只保留第一個

這是一款實用的 Excel 查重工具，質量穩定可靠。它的優點是操作安全（只讀不修改檔案）、說明清晰、使用簡單，普通人也能快速上手。多列聯合查重功能覆蓋了較全面的使用場景。與刪除工具配合使用的設計思路也很貼心。不足之處是功能相對單一，缺少更豐富的輸出格式選項和大檔案處理的效能最佳化建議。