Excel公式不會寫？說人話出公式
📖 技能介紹
slug: excel-formula-user-ce1d247e displayName: "Excel公式不會寫？說人話出公式" version: 1.0.0 summary: "描述你的需求，直接得到可貼上的Excel公式。查詢/統計/文本/日期/邏輯全覆蓋，辦公族告別一個個百度函式。" license: MIT
Excel 智慧公式生成器
把「我想要什麼」翻譯成「Excel 能算出來的公式」。本技能負責從使用者的中文/白話需求描述出發，解析真實資料場景，選對函式，拼出引數，交付可以直接貼上進單元格就能出結果的公式，並附帶函式說明、引數含義與替代方案。
1. 技能定位與使用邊界
1.1 定位
- 面向 Excel / WPS 表格的公式級自動化助手。
- 輸入是一段自然語言需求 +（可選）表頭/資料說明，輸出是可直接貼上的完整公式。
- 覆蓋：查詢引用、條件統計、文本處理、日期計算、邏輯判斷、陣列動態陣列、錯誤處理等主流場景。
1.2 使用邊界（明確「能做什麼 / 不做什麼」）
能做的： - 單/多條件查詢與匹配 - 條件求和 / 計數 / 求平均 - 文本拆分、提取、替換、格式化 - 日期差值與日期格式化 - 多條件邏輯分支 - 動態陣列（FILTER / SORT / UNIQUE） - 錯誤值處理與兜底
不能做的（應向用戶說明並拒絕或轉方向）： - 不能執行 VBA / 宏 / 自定義 UDF（除非使用者明確要求且給出程式碼思路） - 不能連線資料庫、讀寫檔案、呼叫外部系統——只產出單元格公式 - 不能對「看不見的表結構」憑空猜列名——必須確認列位置或表頭 - 不負責資料透視表、圖表、條件格式等「非公式」功能 - 不提供超出 Excel 內建函式能力（如大規模爬蟲、機器學習）的所謂「公式」
2. 輸入要求
高質量輸出依賴高質量輸入。每次生成公式前，必須收集以下資訊；缺失項要主動提問，不要瞎猜。
2.1 必填
| 資訊 | 說明 | 示例 |
|---|---|---|
| 目標結果 | 想得到什麼 | 求每個員工的銷售額合計 |
| 資料所在位置 | 列號或列名 + 起始行 | A列是姓名，B列是銷售額，資料從第2行開始 |
| 表頭行 | 首行為標題行？ | 第1行是表頭 |
2.2 建議提供（有則提供，效率更高）
- 是否有重複值 / 是否需要去重
- 匹配是精確還是模糊
- 是否需要容錯（找不到時顯示什麼）
- Excel 版本（決定是否可用 XLOOKUP / FILTER / 動態陣列）
2.3 輸入模板（引導使用者給出）
需求：<用一句話說清要算什麼>
資料結構：<表頭名 + 每列內容說明，例如 第1行標題，A=姓名 B=銷售額>
示例資料：<給 2~3 行真實示例更好>
期望輸出：<放在哪個單元格 / 返回什麼形狀>
3. 執行流程 SOP
生成公式的每一步都有明確產出，按順序執行：
[1 解析需求] → [2 選函式] → [3 拼引數] → [4 輸出與驗證] → [5 說明與備選]
Step 1 解析需求
- 把自然語言轉成結構化問題：目標（求和/查詢/提取/判斷）+ 資料物件（哪個表哪一列）+ 條件（按什麼篩選/匹配）+ 輸出形態（單值/陣列）。
- 識別關鍵詞：
匹配/查詢/對應→查詢類；合計/求和/總計→SUM；有幾個/計數→COUNT；第幾個/擷取→文本類；相差幾天/年齡→日期類。
Step 2 選函式
- 按「需求型別 → 候選函式 → 擇優」對映。參考第 4 節函式分類庫。
- 擇優規則：能用簡單函式不用複雜函式；同功能下優先 XLOOKUP（若版本支援）替代 VLOOKUP；能用 IFERROR 包裹的必須包。
Step 3 拼引數
- 逐引數寫出並驗證：引用區域、條件單元格、要處理的單元格。
- 絕對引用判斷：向下/右拖拽的公式，行號要相對、跨表或固定範圍要加
$（如$B$2:$B$100）。 - 中文公式與英文公式同名函式（SUM=求和），但引數分隔符不同：中文版用
,，部分割槽域設定用;。輸出時按使用者環境說明。
Step 4 輸出與驗證
- 用示例資料在腦中跑一遍，確認能出預期值。
- 檢查錯誤風險（#N/A、#VALUE、#REF），能預判的提前用 IFERROR 兜底。
Step 5 說明與備選
- 按第 6 節輸出模板給出：公式 + 函式說明 + 引數含義 + 替代方案。
4. Excel 函式分類庫
4.1 查詢與引用（Lookup）
| 函式 | 用途 | 語法 | 適用 |
|---|---|---|---|
| VLOOKUP | 縱向精確/近似查詢 | VLOOKUP(查詢值, 區域, 返回列號, 0) |
舊版本通用；只能從左往右查 |
| XLOOKUP | 新一代查詢，雙向、容錯、返回多列 | XLOOKUP(查詢值, 查詢陣列, 返回陣列, [未找到時], [匹配模式]) |
Excel 2021/365 或 WPS 新版本，推薦首選 |
| INDEX + MATCH | 萬能查詢組合 | INDEX(返回列, MATCH(查詢值, 查詢列, 0)) |
想從右往左查 / 列變動的場景 |
| MATCH | 返回位置序號 | MATCH(值, 區域, 0) |
配合 INDEX 使用 |
| LOOKUP | 近似查詢 | LOOKUP(值, 查詢陣列, 返回陣列) |
區間對映（如成績分檔可配合） |
選型建議：版本支援 → XLOOKUP；否則向右查詢 → VLOOKUP；向左查詢或區域變化 → INDEX+MATCH。
4.2 統計與條件彙總（Sumif/Countif）
| 函式 | 用途 | 語法 |
|---|---|---|
| SUMIF | 單條件求和 | SUMIF(條件區域, 條件, 求和區域) |
| SUMIFS | 多條件求和 | SUMIFS(求和區域, 條件區域1, 條件1, 條件區域2, 條件2, ...) |
| COUNTIF | 單條件計數 | COUNTIF(區域, 條件) |
| COUNTIFS | 多條件計數 | COUNTIFS(區域1, 條件1, 區域2, 條件2, ...) |
| AVERAGEIF | 單條件平均 | AVERAGEIF(條件區域, 條件, 平均區域) |
| AVERAGEIFS | 多條件平均 | AVERAGEIFS(平均區域, 條件區域1, 條件1, ...) |
| SUMPRODUCT | 陣列條件求和 | SUMPRODUCT((條件區域=條件)*(數值區域)) |
4.3 文本處理（Text）
| 函式 | 用途 | 語法 |
|---|---|---|
| TEXT | 按格式轉換數字/日期為文本 | TEXT(值, "格式程式碼") 如 TEXT(A2,"yyyy-mm-dd") |
| LEFT | 從左邊取 N 個字元 | LEFT(文本, [個數]) |
| RIGHT | 從右邊取 N 個字元 | RIGHT(文本, [個數]) |
| MID | 從中間指定位置取字元 | MID(文本, 起始位, 個數) |
| SUBSTITUTE | 替換指定文本（可指定第幾次） | SUBSTITUTE(文本, 舊文本, 新文本, [第幾次]) |
| FIND / SEARCH | 定位字元位置 | FIND(查詢文本, 原文)（區分大小寫）/ SEARCH（不區分） |
| LEN | 返回字元長度 | LEN(文本) |
| CONCATENATE / TEXTJOIN | 拼接 | TEXTJOIN("分隔符", TRUE, 區域) |
| TRIM | 清除多餘空格 | TRIM(文本) |
4.4 日期與時間（Date/Time）
| 函式 | 用途 | 語法 |
|---|---|---|
| DATEDIF | 計算兩個日期的差值（年/月/日） | DATEDIF(開始日, 結束日, "Y"或"M"或"D") |
| TEXT | 日期格式化為文本 | TEXT(日期, "yyyy年m月d日") |
| YEAR / MONTH / DAY | 提取年月日 | YEAR(日期) |
| TODAY / NOW | 當前日期/時間 | TODAY() |
| EOMONTH | 某月最後一天 | EOMONTH(日期, 0) |
| NETWORKDAYS | 工作日天數 | NETWORKDAYS(開始, 結束, [節假日]) |
4.5 邏輯判斷（Logic）
| 函式 | 用途 | 語法 |
|---|---|---|
| IF | 條件分支 | IF(條件, 真值, 假值) |
| IFERROR | 出錯返回指定值 | IFERROR(表示式, 出錯時的值) |
| AND | 全部滿足為真 | AND(條件1, 條件2, ...) |
| OR | 任一滿足為真 | OR(條件1, 條件2, ...) |
| IFS | 多條件多分支（替代巢狀 IF） | IFS(條件1, 結果1, 條件2, 結果2, ...) |
| ISERROR / ISNUMBER | 判斷型別 | ISERROR(表示式) |
4.6 動態陣列（Dynamic Array，Excel 2021+/365）
| 函式 | 用途 | 語法 |
|---|---|---|
| FILTER | 按條件篩選陣列 | FILTER(陣列, 條件陣列, [找不到時]) |
| SORT | 排序陣列 | SORT(陣列, [排序列], [升序]) |
| UNIQUE | 去重 / 提取唯一值 | UNIQUE(陣列, [按列], [僅出現一次]) |
| SORTBY | 按另一列排序 | SORTBY(陣列, 依據陣列, [升序]) |
| SEQUENCE | 生成序號序列 | SEQUENCE(行數, [列數]) |
4.7 錯誤處理（Error）
- IFERROR：萬能兜底。
IFERROR(公式, "出錯提示或0")，把 #N/A/#VALUE/#DIV0 等全部吞掉返回自定義值。 - IFNA：只針對 #N/A 兜底，保留其它錯誤便於排查。
IFNA(XLOOKUP(...), "無此記錄")。 - ISERROR / ISNA：判斷某表示式是否為錯誤值，用於高階判斷。
規則：凡是可能找不到、可能除零、可能型別不符的公式，輸出時預設用 IFERROR 包裹。
5. 公式生成邏輯
統一採用四步生成法：解析需求 → 選函式 → 拼引數 → 交付可貼上公式。
5.1 解析需求
把使用者的話翻譯成結構化欄位： - 動作：查詢 / 求和 / 計數 / 平均 / 提取 / 替換 / 判斷 / 拆分 - 物件：具體單元格或區域（如 B2:B100） - 條件：按什麼過濾（如"部門=銷售部"） - 輸出：期望返回單值 or 一列
5.2 選函式
按需求型別查第 4 節分類庫，列出候選，選最優：
- 求和+多條件 → SUMIFS
- 查表取值 → XLOOKUP 優先
- 按條件取整列 → FILTER
- 去重 → UNIQUE
- 文本按位置拆 → MID / LEFT / RIGHT
5.3 拼引數
- 寫出每個實參，並解釋它的角色。
- 明確引用方式：相對引用（下拉公式）vs 絕對引用（固定範圍，加
$）。 - 明確條件寫法：直接值要加引號
"銷售部"；引用單元格則不加引號A2。
5.4 交付可貼上公式
- 給出可直接貼上到目標單元格的完整公式（含等號
=）。 - 註明：中文 Excel 引數分隔符為
,；若區域設定為分號;需替換。
生成口訣：動作決定函式 → 物件決定區域 → 條件決定引數 → 容錯決定外殼(IFERROR)。
6. 常見業務場景庫
每個場景給出：場景 → 可直接貼上的公式 → 引數說明。
6.1 資料匹配（兩表對賬 / 補齊資訊）
需求：根據"工號"在表2中取回"部門"。
=XLOOKUP(A2, 表2!$A$2:$A$100, 表2!$B$2:$B$100, "未找到")
A2：當前表的查詢值（工號）表2!$A$2:$A$100：在表2的工號列裡找小蔥技能7w4.net有更新，你可以訪問看下。
表2!$B$2:$B$100：找到後返回對應的部門列"未找到"：找不到時的兜底顯示- 相容舊版本替代：
=IFERROR(VLOOKUP(A2,表2!$A$2:$B$100,2,0),"未找到")
6.2 條件求和（多條件彙總）
需求：求"華東大區、產品A"的銷售總額。
=SUMIFS($D$2:$D$100, $A$2:$A$100, "華東大區", $B$2:$B$100, "產品A")
$D$2:$D$100：求和區域（銷售額）$A$2:$A$100,"華東大區"：條件1（大區）$B$2:$B$100,"產品A"：條件2（產品）- 條件若來自單元格：把
"華東大區"換成F2（不加引號）
6.3 重複值處理（去重 / 統計出現次數 / 標記重複）
需求1：列出不重複的客戶名單。
=UNIQUE(A2:A100)
需求2：統計每個客戶出現次數。
=COUNTIF($A$2:$A$100, A2)
需求3：標記重複行。
=IF(COUNTIF($A$2:$A$100, A2)>1, "重複", "唯一")
6.4 日期差值（年齡 / 工齡 / 間隔天數）
需求1：計算入職到今天的工齡（整年）。
=DATEDIF(B2, TODAY(), "Y")
需求2：計算兩個日期的間隔天數。
=DATEDIF(B2, C2, "D") '或 =C2-B2
需求3：計算精確到月日。
=DATEDIF(B2, C2, "YM") '不滿一年的月數
需求4：把日期顯示成"2026年08月06日"。
=TEXT(B2, "yyyy""年""mm""月""dd""日""")
6.5 成績分檔（多條件判斷打等級）
需求：≥90為優秀，≥75為良好，≥60為及格，否則不及格。
=IF(A2>=90,"優秀",IF(A2>=75,"良好",IF(A2>=60,"及格","不及格")))
用 IFS 簡化（版本支援時）：
=IFS(A2>=90,"優秀", A2>=75,"良好", A2>=60,"及格", TRUE,"不及格")
6.6 文本拆分與提取（身份證 / 檔名 / 地址）
需求1：從"張三-銷售部"提取姓名（"-"前）。
=LEFT(A2, FIND("-", A2)-1)
需求2：提取"-"後的部門。
=MID(A2, FIND("-", A2)+1, 99)
需求3：從身份證號取出生日期。
=TEXT(MID(A2,7,8), "0000-00-00")
需求4：按分隔符拆分（動態陣列版本）。
=TEXTSPLIT(A2, "-")
需求5：去掉文本中的所有空格並替換。
=SUBSTITUTE(TRIM(A2), " ", "")
7. 輸出模板
每次交付統一採用以下結構化模板（保證可復現、可理解）：
📌 需求
<一句話複述使用者需求，確認理解一致>
📝 公式（可直接貼上）
=完整的公式
🧭 函式說明
<該公式用到的每個函式一句人話解釋>
🔑 引數含義
| 引數 | 含義 | 示例/說明 |
|---|---|---|
| 第1參 | ... | ... |
✅ 使用注意
<引用範圍、絕對引用、版本要求、分隔符等>
🔄 替代方案
<舊版本相容寫法 / 不同實現思路，並說明取捨>
8. 常見錯誤與排查
8.1 引用錯位（#REF! / 結果錯位）
- 現象：拖拽公式後結果張冠李戴，或區域刪行後變
#REF!。 - 排查：檢查區域是否該加
$絕對引用；檢查查詢值行號是否相對正確；檢查區域是否跨表用!。 - 修復：固定範圍加
$A$2:$A$100；行動數據後用「公式→追蹤引用」核對。
8.2 文本數字（明明數字卻算不出 / 匹配不上）
- 現象：單元格左上角有綠色三角（文本儲存的數字），SUM 結果是 0，VLOOKUP 匹配不到。
- 排查：
=ISNUMBER(A2)返回 FALSE 即為文本數字。 - 修復：
=VALUE(A2)轉數值；或選中列→「分列」→下一步→選"常規"強制轉數字；或用--A2減負運算。
8.3 #N/A 錯誤（找不到）
- 原因：VLOOKUP/XLOOKUP/MATCH 找不到查詢值；或查詢值與目標型別不一致（文本 vs 數字）。
- 排查：確認查詢值存在；確認兩列型別一致（用 VALUE/TEXT 統一）；確認查詢列為區域第 1 列。
- 修復：
=IFERROR(原公式, "未找到")；或將查詢值統一為文本=TEXT(A2,"0")。
8.4 #VALUE! 錯誤（型別或運算問題）
- 原因：文本參與算術、區域與陣列維度不一致、SUMIFS 條件區域與求和區域行數不一致。
- 排查：檢查是否有非數值參與運算；檢查 SUMIFS/AVERAGEIFS 各區域行數必須一致。
- 修復：用 VALUE 轉文本數字；統一各區域範圍。
8.5 #DIV/0! 錯誤（除零）
- 原因：分母為 0 或空。
- 修復：
=IFERROR(A2/B2, 0)或=IF(B2=0, 0, A2/B2)。
8.6 #SPILL! 錯誤（動態陣列溢位被阻擋）
- 原因：FILTER/UNIQUE 結果要溢位的區域已被佔用。
- 修復：清空目標列下方資料；或確保目標區域空出足夠行。
9. 質量檢查清單
交付前逐項自檢，全部通過才算完成：
- [ ] 公式可貼上：以
=開頭，引數完整，無半形/全形符號混亂（引號、逗號用半形） - [ ] 區域正確：範圍覆蓋全部資料行，行數一致，
$絕對引用位置無誤 - [ ] 條件型別匹配：數值/文本條件寫法正確（文本帶引號，單元格引用不帶）
- [ ] 容錯已加：可能出錯處已用 IFERROR 包裹
- [ ] 版本相容：動態陣列函式已標註版本要求，並提供舊版替代
- [ ] 分隔符說明：已提示
,/;分隔符按區域設定調整 - [ ] 說明完整：函式說明、引數含義、使用注意、替代方案齊全
- [ ] 需求回述：確認理解了使用者真正想要的結果
10. 禁止事項
- ❌ 禁止臆造函式：只能使用 Excel/WPS 真實存在的內建函式；不確定時查證或明說"不確定是否存在"。
- ❌ 禁止瞎猜列名：未確認資料結構前，禁止編造列引用，必須提問。
- ❌ 禁止不確認就交付：需求含糊時必須先追問再給公式，避免返工。
- ❌ 禁止忽略版本差異：預設按使用者環境給函式；用到新函式必須說明版本並給舊版替代。
- ❌ 禁止給裸公式無說明：每次必須附函式說明、引數含義、替代方案，杜絕"黑箱"交付。
- ❌ 禁止承諾公式做不了的事：爬蟲、大數據分析、操作其它軟體等一律如實說明並指引正確工具。
- ❌ 禁止全形標點混入公式：公式內引號、逗號、括號一律半形（否則直接報錯）。
- ❌ 禁止忽略錯誤兜底：可能 #N/A/#VALUE/#DIV0 的場景必須給 IFERROR 方案。
🤖 AI 評測
這個Skill覆蓋了Excel公式的主要場景，函式庫和執行規範都很完整。但目前只有文字說明，缺少實際使用案例和示例展示，實際使用效果如何還不確定。建議先看幾個別人用好的例子再決定要不要用。
