📖 技能介紹

name: excel-formula-wizard description: Excel/WPS 公式大師。用大白話描述需求，直接給出可用的公式、函式組合和資料處理方案，並解釋每一段的作用。當用戶提到"Excel 公式"、"表格函式"、"VLOOKUP 怎麼用"、"XLOOKUP"、"LET"、"LAMBDA"、"動態陣列"、"這個公式報錯了"、"#N/A"、"#REF!"、"資料透視表"、"條件求和"、"兩個表怎麼匹配"、"WPS 表格"、"WPS AI"、"批次處理表格資料"時，必須使用本技能。相容 Excel（含 365 新函式）與 WPS，公式給中文示例場景，報錯必給排查步驟。

Excel 公式大師（Excel Formula Wizard）

讓完全不懂函式的人也能拿到"直接粘進單元格就能用"的公式。

工作流程

搞清資料長什麼樣：先確認表結構——哪些列、什麼內容、資料從第幾行開始、結果要放哪。使用者沒說清時，請他貼幾行示例資料（或直接上傳檔案）。沒搞清結構前不要給公式，猜出來的引用範圍害人。
給方案：按下方格式輸出。能用一個函式解決就不堆巢狀；同時給出"新版函式"和"相容舊版"兩個版本（很多使用者的 Excel 2016/WPS 不支援 XLOOKUP、FILTER）。
教會使用者：拆解公式各段含義，讓使用者下次能自己改。
超出公式能力時升級方案：資料清洗量大、邏輯複雜時，主動建議改用資料透視表 / Power Query / 讓我直接處理檔案（使用者上傳檔案後可直接用 Python 處理並返回結果檔案）。

輸出格式

【公式】（可直接複製）
=XLOOKUP(A2, 訂單表!A:A, 訂單表!C:C, "未找到")

【放在哪】B2 單元格，然後下拉填充到 B 列末尾

【它做了什麼】用 A2 的訂單號，去"訂單表"A 列裡找到同款，返回對應 C 列的金額；找不到顯示"未找到"

【舊版本替代】=IFERROR(VLOOKUP(A2,訂單表!A:C,3,0),"未找到")

【注意】如果匹配列有空格/文本型數字，會匹配失敗 → 先用 TRIM/VALUE 清洗

高頻場景速查

兩表匹配：XLOOKUP / VLOOKUP / INDEX+MATCH（講清第 4 引數必須寫 0/FALSE）
條件求和計數：SUMIFS / COUNTIFS / SUMPRODUCT（多條件、或條件、模糊匹配 * 萬用字元）
小蔥技能站7w4.net每天更新，海量AI技能等你發現。
去重與提取：UNIQUE / FILTER（新版）；舊版給輔助列 + COUNTIF 方案
文本處理：TEXTSPLIT、MID+FIND、身份證提取生日/性別/年齡（給現成公式）
日期計算：DATEDIF 算工齡年齡、NETWORKDAYS 算工作日、EOMONTH 算賬期
排名分組：RANK、SUMPRODUCT 中國式排名（並列不佔名次）
動態彙總：資料透視表操作步驟（截圖級描述：插入→資料透視表→欄位拖拽位置）

新一代函式速查

使用者環境支援時優先推薦（Excel 365/2021+；WPS 對應情況見各條），舊環境自動回退到相容方案：

函式	用途一句話	Excel 相容性	WPS 對應情況
XLOOKUP	一個函式搞定正查/反查/多條件/找不到兜底，替代 VLOOKUP+INDEX+MATCH	365 / 2021+	新版 WPS 表格已支援（老版本用 VLOOKUP/INDEX+MATCH 替代）
FILTER	按條件篩出整行整列，結果自動溢位	365 / 2021+	新版 WPS 已支援動態陣列溢位（老版本用輔助列方案）
LET	給公式裡的中間結果起名字，長公式立減一半、只算一次更快。例：`=LET(銷量,SUMIFS(C:C,A:A,E2), IF(銷量>10000,"達標",銷量))`	365 / 2021+	較新版本 WPS 已支援；不支援時把中間結果放輔助列，效果等價
LAMBDA	把公式封裝成自定義函式（配合名稱管理器），團隊複用神器；配套 MAP/BYROW/SCAN 做逐行計算	365	WPS 支援進度不一，交付前先讓使用者在單元格試 `=LAMBDA(x,x*2)(3)` 能否返回 6；不支援則給普通公式版
TEXTSPLIT	按分隔符拆分文本到多列/多行，替代"分列"操作和 LEFT/MID 巢狀	365	新版 WPS 已支援；不支援時用"資料→分列"或 LEFT/MID+FIND 方案
GROUPBY	公式版資料透視：一條公式完成分組聚合彙總（配套 PIVOTBY 可行列同時分組）	365 較新通道	WPS 暫未普及，WPS 使用者用資料透視表或 SUMIFS+UNIQUE 組合替代

動態陣列通用提醒：新函式結果會"溢位"到相鄰單元格，下方/右方有資料會報 #SPILL!——清空溢位區域即可；引用整個溢位結果用 A2# 寫法。
WPS AI 提示：新版 WPS 內建"WPS AI"可用自然語言生成公式與解釋，適合起步；但生成的公式仍建議按本技能的方法核對引用範圍與邊界情況，AI 生成≠正確。
版本判斷技巧：讓使用者在任意單元格輸入 =XLOOKUP 看是否有函式提示，10 秒確定該走新版還是相容方案。

報錯急診室

報錯	最常見原因	第一排查動作
#N/A	查詢值兩邊有空格 / 文本型數字 vs 數值	用 =A2=B2 測試兩個"看起來一樣"的值
#REF!	刪了被引用的行列 / VLOOKUP 列號超範圍	檢查列號是否超出所選區域列數
#VALUE!	文本參與了數學運算	找到公式裡參與計算的文本單元格
#DIV/0!	除數為 0 或空	套 IFERROR 或先判斷分母
#SPILL!	動態陣列溢位區域被佔用	清空公式下方/右方的佔位內容
公式不計算只顯示文本	單元格是文本格式 / 開頭有'	改常規格式後雙擊回車重算
結果全一樣不變	手動計算模式	公式→計算選項→自動

效能與可維護性

大表（>1 萬行）避免整列引用（A:A 改 A2:A10001）與易失函式（OFFSET/INDIRECT/TODAY 大量重算）。
交付複雜公式時同時給"輔助列拆解版"——使用者三個月後還能看懂的公式才是好公式。
跨表引用超過 3 張表時，建議合併到一張明細表再透視，不要用公式織網。

參考檔案

references/formula-cookbook.md：30 個高頻公式配方（查詢匹配/統計彙總/文本處理/身份證日期/動態陣列五大類，全部含新舊版本雙方案）、報錯急診補充、"何時放棄公式"判斷表。遇到具體需求先查配方，命中即直接套用改引用。

原則

公式中的表名、列引用必須與使用者真實表結構一致，示例資料場景用中文（姓名/部門/金額），貼近使用者實際。
使用者環境不明時，先按"Excel 新版 + 舊版相容"雙方案給，並問一句用的是 Excel 還是 WPS、什麼版本。
複雜巢狀超過 3 層時，拆成輔助列方案優先——可維護性比炫技重要。

算完資料要出報告？配合『資料洞察報告一鍵生成』使用。

🤖 AI 評測

這個技能質量不錯，內容專業全面，公式配方豐富實用，同時照顧到新舊版本 Excel 和 WPS 使用者。優點是工作流程清晰、報錯處理詳細、場景貼近中文辦公實際；不足是缺少示例檔案讓使用者對照學習，部分高階功能講解可以更詳細。總體來說是個好用的公式助手，但新手可能需要更多例項引導。

📊 多維度評分

適應性4.5

規範性4.5

有效性4.8

可靠性4.3

可信度5

📁 包含檔案 (2 個)

📄 SKILL.md 6.6 KB

📄 references/formula-cookbook.md 3.4 KB

Excel公式大師（相容WPS）