用 AI 寫 Excel／Google Sheet 公式：白話描述需求，VLOOKUP、樞紐、函式一次搞定

每次要做報表，是不是常卡在「這個用 VLOOKUP 還是 XLOOKUP？」「為什麼又跳 #N/A？」一個欄位查找搞半小時，函式名稱永遠記不熟。其實，現在你完全不需要把函式背起來——只要會用白話把需求講清楚，AI 就能幫你把公式寫好、解釋給你聽，連除錯都一起包辦。

這篇要解決的問題：教你怎麼用 AI 寫出正確的 Excel 與 Google Sheet 公式，從 VLOOKUP、樞紐彙總到巢狀 IF、陣列公式，並學會請 AI 幫你除錯。 適合誰讀：常做報表卻不熟函式的上班族、行政人員、業務、店長、會計，以及想把試算表工時砍掉一半的人。 讀完你會得到：一套「描述需求→拿到公式→驗算」的標準流程、可直接複製的 Prompt 範本、Workflow 流程圖、一張「需求對應函式」的對照表，以及兩個台灣中小企業的實際導入成果。

為什麼大家寫公式總是卡關？

試算表的痛點不在於「不會用 Excel」，而在於人腦的思考方式和函式語法之間有一道翻譯牆。你心裡很清楚「我要根據訂單編號，去另一張表把客戶名稱抓過來」，但要把這句話翻成 =VLOOKUP(A2,客戶表!A:B,2,FALSE)，就得記住參數順序、絕對參照、第幾欄、要不要精準比對——一個環節錯，整欄就紅一片。

過去解法是上網查、翻教學、問同事，但每個情境的欄位都不一樣，範例很難直接套用。AI 的價值就在這裡：它能接住你的白話需求，幫你跨過那道翻譯牆，把「我想做什麼」直接轉成「可以貼上的公式」，而且還能解釋、能除錯。換句話說，你負責想清楚要什麼，函式語法交給 AI。

這跟我們在 用 AI 做資料分析 中強調的觀念一致：人決定方向與判斷，AI 處理重複、繁瑣、需要記憶的執行細節。公式只是整個資料工作流的一環——前面是清洗，後面是 視覺化 與 寫成報告，這篇先把「算對」這一塊徹底解決。

核心概念：把 AI 當「會函式的同事」

最有效的心態，是把 AI 想成一位就坐在你旁邊、函式很熟但不認識你資料的同事。他能力很強，但你不講清楚，他只能用猜的。所以你要做的，是把背景交代到他能直接動手的程度。

一個好的請求，通常包含四個要素，可以用下面這張表對照：

掌握這四點，你就不需要知道答案是 VLOOKUP、XLOOKUP 還是 INDEX/MATCH——描述問題，而不是指定工具，反而能讓 AI 挑出最適合的函式。這個「描述需求而非指定做法」的原則，也是寫好任何指令的核心精神；想把這套講需求的功力練扎實，ChatGPT Prompt 教學 與 Claude Prompt 教學 都從「怎麼把背景交代清楚」講起，和這裡完全相通。

一張表看懂：你的需求對應到哪個函式

很多人卡關，是因為腦中沒有「需求 → 函式」的地圖。你不必背它，但有個概念，下指令時會更精準，看 AI 的回覆也更踏實。

把這張表放在心裡，當 AI 回你一條公式時，你就能大致看懂「它走的是哪一路」，驗算起來更安心。

實際教學：五步驟用 AI 寫對公式

Step 1：講清楚你的資料長什麼樣

開頭先把欄位與資料範圍交代清楚。你不用截圖，用文字描述即可，例如：「我的工作表叫『訂單』，A 欄訂單編號、B 欄客戶代號、C 欄數量、D 欄單價，標題在第 1 列，資料從第 2 列到第 500 列。」

這一步最常被略過，卻最關鍵。AI 不知道你的標題在哪一列、資料到第幾列，就只能假設，產出的範圍很容易錯位。如果欄位很多，可以直接把第 1 列的標題整列貼給 AI，讓它對齊欄位名稱，後面公式的可讀性也會更好。

Step 2：用白話描述你要的結果

直接說你想得到什麼，不要說函式名稱。例如：「我想在 E 欄算出每筆訂單的小計，也就是數量乘單價。」或「我想在另一張『單價表』裡，依客戶代號查出對應的折扣率，填回訂單表的 F 欄。」

把「輸入是什麼、輸出放哪、規則是什麼」講出來，AI 就能判斷該用乘法、VLOOKUP 還是 SUMIFS。這跟開會後請 AI 整理重點是同一種能力——把模糊的口語需求結構化；如果你常需要把口頭討論變成可執行清單，用 AI 整理會議記錄 裡的「先給結構再給內容」技巧在這裡一樣管用。

Step 3：指定平台與版本

這一步決定 AI 能不能給你「貼上就能用」的公式。Excel 365 與 Google Sheet 支援 XLOOKUP、LET、LAMBDA 等新函式，但 2019 以前的舊版 Excel 不支援，硬貼上去會直接報錯。Google Sheet 還有 QUERY、ARRAYFORMULA、FILTER 等專屬函式，威力很強但 Excel 沒有。

一句「我用 Google Sheet」或「公司是 Excel 2016」，就能避免大半的相容問題。如果你不確定自己是哪個版本，也可以請 AI「同時給我新版與舊版兩種寫法」，自己挑能用的那條。

Step 4：跑出錯誤就貼回去除錯

公式不會一次到位很正常。如果出現 #N/A、#REF!、#VALUE! 等錯誤，把完整公式和錯誤訊息一起貼回給 AI，並補一句資料狀況，例如「查找的編號明明存在，卻還是 #N/A」。AI 通常能立刻判斷是格式不一致（文字 vs 數字）、有多餘空白、範圍沒鎖絕對參照等問題。

這種「貼錯誤→拿修正」的來回，本質上就是用前一步的輸出餵給下一步繼續優化，是 ChatGPT Prompt 教學 裡反覆強調的「多輪迭代」精神：不求一次到位，求每一輪都更靠近正確答案。

Step 5：要求解釋並驗算

拿到公式別急著全表套用。多加一句：「請逐段解釋這條公式在做什麼，並用我表裡第 2 列的資料示範算一次。」一來你能確認邏輯正確，二來下次遇到類似情境，你自己也學會了，不必再問。

驗算特別重要：AI 偶爾會把欄位編號數錯，或在巢狀 IF 的條件順序上出包。先驗一筆、確認對了再往下拉，是最省事的保險。

範例：Prompt 與 Workflow

以下是一個可以直接複製、改成你情境的萬用 Prompt 範本：

你是 Excel／Google Sheet 公式專家。我要請你幫我寫一條公式。

【平台】Google Sheet（如果是 Excel 請改這裡，並註明版本）
【資料結構】
- 工作表「訂單」：A 欄=訂單編號、B 欄=客戶代號、C 欄=數量、D 欄=單價，標題在第 1 列，資料第 2~500 列。
- 工作表「客戶」：A 欄=客戶代號、B 欄=客戶名稱、C 欄=折扣率。
【我想要的結果】
在「訂單」工作表的 E2，依照 B 欄的客戶代號，到「客戶」工作表查出對應的折扣率。
【特殊規則】
查不到時顯示 0，不要出現 #N/A；公式要能往下拉到第 500 列。
【請你輸出】
1. 一條可直接貼上的公式；
2. 逐段中文解釋；
3. 用第 2 列資料示範算一次，驗證結果。

把這個 Prompt 丟給 AI，它通常會回你一條像 =IFERROR(VLOOKUP(B2,客戶!A:C,3,FALSE),0) 的公式，外加白話解釋與驗算。需要更穩定的指令結構，可以搭配我們的 Prompt 產生器 快速生成，或對照 Claude Prompt 教學 把這個範本再打磨得更精準。

文字版 Workflow 流程圖（每次寫公式都照這條路走）：

描述資料結構（欄位／範圍）
        ↓
白話說出要的結果（輸入→輸出→規則）
        ↓
指定平台與版本（Excel 365／舊版／Google Sheet）
        ↓
AI 產出公式 + 逐段解釋
        ↓
貼上試算 → 有錯誤？
   ├─ 是 → 貼回公式＋錯誤訊息 → AI 修正（回上一步）
   └─ 否 → 驗算第一筆 → 正確 → 往下整欄套用 ✓
        ↓
（可選）把彙總結果交給下一段流程：視覺化 / 寫報告

這條流程的精神是「小步驗證、逐步放大」：先讓一格對，再拉整欄，最後存成你自己的 Prompt 範本重複用。算對之後，數字往往還要變成圖表或結論——這時就接到 用 AI 做資料視覺化 與 用 AI 寫報告，把整條「資料→公式→圖表→結論」的鏈路一次走完。

進階：更深入的一層

當你已經能順手用 AI 寫單一查找與加總，下一層的功力，是讓 AI 幫你處理**「會長大、會變動、需要重複用」的試算表**。這一段是把工具人升級成流程設計者的關鍵。

1. 用陣列公式取代「往下拉」。 傳統作法是寫一條公式、整欄往下複製，資料一多就拖累效能、新增列還要再拉。請 AI 改用 Google Sheet 的 ARRAYFORMULA 或 Excel 365 的溢出陣列：一條公式放在標題下方，整欄自動算完，未來新增訂單也會自動延伸。下指令時加一句「請用陣列公式讓整欄自動計算，不要我手動往下拉」即可。

2. 用 QUERY 一條抵一張樞紐。 Google Sheet 的 QUERY 能像寫 SQL 一樣同時做篩選、分組、排序。你不必會語法，只要白話描述：「請用 QUERY 幫我做出『每個客戶代號的訂單筆數與金額總和，並依金額由大到小排序』。」AI 會回你類似 =QUERY(訂單!A:D,"select B, count(A), sum(D) where D is not null group by B order by sum(D) desc label ...") 的公式，等同一張會自動更新的樞紐表。

3. 用 LET／LAMBDA 馴服長公式。 巢狀三四層的公式沒人想維護。請 AI「用 LET 把中間結果命名」，原本一長串會變成像程式一樣分段、可讀；如果是反覆出現的計算邏輯，甚至可請它包成 LAMBDA 自訂函式，整份檔案共用一套規則。這一步讓公式從「一次性魔法」變成「可維護資產」。

4. 把欄位資訊先結構化，再請 AI 寫。 進階使用者通常不是一次丟一個需求，而是先把整張表的欄位、型態、範圍、特殊規則整理成一段結構化說明，再連續請 AI 寫好幾條相關公式。這種「先建背景、再連續產出」的手法，正是 用 AI 做資料分析 的核心；把它和本篇的五步驟結合，你就能在一次對話裡，把一份報表所有公式一口氣請 AI 寫完。

5. 讓 AI 自我檢查。 寫完後加一句：「請列出這條公式在哪些資料情況下可能出錯（例如空值、重複鍵、文字數字混用），並各給一個防呆建議。」這等於請 AI 幫你做測試案例，把潛在的 #N/A、#REF! 在套用前就堵住。

常見錯誤

• 只說函式名稱，不說需求：直接喊「幫我寫 VLOOKUP」，AI 不知道要查什麼、查哪裡，只能瞎猜。請描述目標，讓 AI 挑函式。
• 沒講平台就要新函式：在舊版 Excel 貼 XLOOKUP 一定報錯。不指定版本，是相容問題的最大來源。
• 資料範圍交代不清：沒講標題在第幾列、資料到第幾列，公式範圍很容易位移，導致整欄偏一格。
• 跳過驗算就全表套用：AI 偶爾數錯欄位或弄反條件。沒驗第一筆就往下拉，錯誤會放大到整份報表。
• 格式不一致卻不察：查找值一邊是文字「001」、一邊是數字 1，VLOOKUP 永遠 #N/A。記得提醒 AI 檢查資料型態。
• 把真實機密資料整段貼上：客戶名單、金額直接貼給公開 AI 服務有外洩風險，務必去識別化。

最佳實務

• 用假資料描述結構：把欄位講清楚就好，例如「A 欄是編號、B 欄是金額」，不必貼真實內容，既安全又夠 AI 動手。
• 一律要求 IFERROR 包覆：請 AI 在公式外層加上錯誤處理，讓查不到時顯示空白或 0，報表才乾淨、不會一片紅。
• 把好用的 Prompt 存成範本：每種情境（查找、彙總、條件加總）各留一個範本，下次改幾個欄位名就能用。
• 要求解釋，把魚學起來：每次都請 AI 解釋邏輯，你會越來越懂，從「每次都問」進化到「自己改」。
• 複雜需求拆成多步：一條公式塞太多邏輯難維護，請 AI 用輔助欄分段，或改用樞紐分析、QUERY 處理。
• 善用 LET 命名：在 Excel 365／Google Sheet 中，請 AI 用 LET 把中間結果命名，長公式會好讀很多。

實際案例一：台中一家貿易公司，月結報表從一天變一小時

台中一家做五金外銷的中小企業，業務助理小芸每個月要彙整三百多筆訂單，做成給老闆看的「客戶別營收＋折扣後淨額」報表。

導入前：她全靠手動。先在訂單表一筆筆對客戶代號、抄客戶名稱，再用計算機算折扣後金額，最後人工分群加總。一份報表要花將近一整個工作天（約 7 小時），月底還常因為手抄打錯，被老闆退回重對。她不是不想用 VLOOKUP，而是每次遇到 #N/A 就崩潰，乾脆放棄。

導入後：她開始用上面那套 Prompt 範本。第一步，請 AI 寫出「依客戶代號查客戶名稱與折扣率」的 VLOOKUP（外層包 IFERROR）；第二步，請 AI 寫「淨額＝金額×(1−折扣率)」；第三步，把彙總需求丟給 AI，產出用 SUMIFS 做的客戶別加總，等同樞紐分析但會自動更新。遇到一次 #N/A，她把公式和錯誤貼回去，AI 立刻指出是客戶代號一邊有空白，加個 TRIM 就解決。

成果數據（該公司內部實測，數字依各自資料量而異）：

最關鍵的轉變不是「省了六小時」，而是小芸從「害怕公式的人」變成「會用 AI 駕馭公式的人」。她現在還把這套方法教給同部門同事，整個行政流程一起加速。這正呼應我們一貫的觀點：AI 真正的槓桿，不是取代人，而是讓原本不敢做的人也能做到、做好。

實際案例二：台北一間連鎖咖啡店，門市日報自動彙總

台北一間有六家分店的連鎖咖啡店，每天各門市把銷售數字填進共用的 Google Sheet，店長阿哲原本要在晚上把六張分頁手動複製貼進總表，再用計算機加總「品項別銷量＋各店營收」，常常忙到打烊還沒做完。

他改用本篇方法後，先把需求結構化丟給 AI：六個分頁欄位一致（A 欄品項、B 欄數量、C 欄金額），請 AI 用 QUERY 搭配 IMPORTRANGE 的概念，把六店資料彙整成一張「品項別總銷量、各店營收、當日總計」的看板。AI 給的是一條會自動更新的陣列公式——門市一填數字，總表當下就變。原本每天約 40 分鐘的手動彙總，降到接近零，阿哲只需要每天瞄一眼異常值。

更進一步，他把這張看板的數字接去做趨勢圖，方法直接照 用 AI 做資料視覺化；月底要交給總公司的營運摘要，則用 用 AI 寫報告 讓 AI 根據看板數字寫出一段有觀點的文字。一條「填數字 → 公式自動彙總 → 圖表 → 報告」的鏈路就此成形，這也是 用 AI 做資料分析 一直在談的「把零散動作串成自動流程」。

免責聲明：本文涉及的營收、折扣、淨額等數字僅為說明用途，案例數據為各公司內部實測、會因資料量與情境而異，不構成任何財務、會計或稅務建議。重要報表與對外申報文件，請務必經由人工複核或專業會計人員確認後再使用。

結論

用 AI 寫試算表公式，門檻其實低得超乎想像——你不需要背函式，只需要學會把需求講清楚。記住這條流程：講資料結構 → 白話說結果 → 指定平台 → 貼錯誤除錯 → 要解釋與驗算。再搭配一個可複製的 Prompt 範本，VLOOKUP、樞紐、巢狀 IF、QUERY、陣列公式都不再是障礙。而當你進到「進階」那一層，會發現自己不只是在寫公式，而是在設計一套會自動更新、可重複使用的報表流程。

下一步，建議你這樣走：先拿這篇的 Prompt 範本，挑一份手邊正在卡關的報表，用 Prompt 產生器 把指令調到順手；接著把算出來的彙總交給 用 AI 做資料視覺化 變成圖表；最後用 用 AI 做資料分析 把整條資料處理流程自動化。當你把「函式恐懼」交給 AI，省下的時間就能拿去做真正需要判斷力的事。

❓ 常見問題 FAQ

🔗 延伸閱讀

• 用 AI 做資料分析：從清洗到洞察
• 用 AI 做資料視覺化：把數字變圖表
• ChatGPT Prompt 寫法完整教學
• Claude Prompt 寫法完整教學
• 用 AI 寫報告：把數字變成結論