Excel/Sheets:公式讓 AI 寫

把需求說清楚,讓 AI 寫公式

AI 寫公式的成敗,九成決定在你的描述有沒有說清楚欄位結構。最省事的格式是這樣:

我有一張 Excel 表:
- A 欄是「員工編號」
- B 欄是「姓名」
- E 欄是「部門」
- 另一張工作表叫「薪資表」,A 欄是員工編號,B 欄是月薪

請幫我在第一張表的 F 欄寫一個公式,根據員工編號去薪資表查對應月薪,填入每一列。

這段 prompt 給了:兩張表的名字、各欄的意義、你想做什麼——AI 拿到這些就能直接寫出正確的 VLOOKUP:

=VLOOKUP(A2, 薪資表!$A:$B, 2, 0)

它通常還會附上逐項解釋:A2 是查詢值、薪資表!$A:$B 是查詢範圍(加 $ 鎖定)、2 代表回傳第二欄、0 代表精確比對。你貼進去,往下拖曳複製,完成。

再一個進階案例:SUMIF 條件加總

我的表格:
- A 欄是「業務員姓名」
- B 欄是「銷售金額」
想在另一格計算「王小明的總銷售額」。
請給我 SUMIF 公式。

AI 給的公式:

=SUMIF(A:A, "王小明", B:B)

要換成動態條件(把名字放在 D1),讓你可以在 D1 輸入不同人名自動計算:

=SUMIF(A:A, D1, B:B)

這就是為什麼你不用背公式語法——你把需求說清楚,AI 連變形版都一起給你。

樞紐分析表也能請 AI 指引

樞紐分析表(Pivot Table)是整理大量資料最強的工具之一,但設定介面讓很多人放棄。你可以這樣問:

我有一張銷售記錄表,欄位有:日期、業務員、產品類別、銷售金額。
我想看「每位業務員在各產品類別的銷售合計」。
請告訴我樞紐分析表要怎麼設定(列、欄、值各放什麼)。

AI 會告訴你:列區域放「業務員」、欄區域放「產品類別」、值區域放「銷售金額」並選「加總」——步驟清楚到你不用摸索。

公式出錯:把錯誤訊息貼給 AI 修

公式出現錯誤代碼時,大部分人的反應是一直盯著看或刪掉重來。正確做法是把錯誤整包丟給 AI。你只需要說清楚三件事:你的公式長什麼樣、錯誤訊息是什麼、你的欄位長什麼樣。

我的公式是:
=VLOOKUP(A2, 薪資表!A:B, 2, 0)

結果顯示 #N/A

我的薪資表 A 欄是員工編號,格式是數字;
第一張表 A 欄的員工編號是文字格式。
請幫我修正。

AI 會分析:文字和數字格式不同,VLOOKUP 找不到對應值。解法是在公式裡加格式轉換:

=VLOOKUP(VALUE(A2), 薪資表!$A:$B, 2, 0)

或者反過來,把薪資表的欄位轉成文字:

=VLOOKUP(A2, ARRAYFORMULA(TEXT(薪資表!$A:$A,"0")), 2, 0)

不同的錯誤代碼有不同的含義,你不用記,貼給 AI 就好。但你值得知道常見的幾個:

看到這些,就把「錯誤訊息 + 公式 + 欄位描述」一起貼給 AI,省下猜謎的時間。

資料清理:亂表變乾淨

這是最消耗上班族時間、但 AI 最擅長的任務之一。典型的髒資料長這樣:

• 日期欄混用「2024/1/5」「Jan 5, 2024」「20240105」三種格式
• 姓名欄有全形空格、半形空格、多餘空格
• 地址欄把「縣市」和「詳細地址」合在同一格
• 同一個人的名字有「王小明」和「王 小明」兩種

在 AI 聊天介面描述清理需求,請它給你對應的公式或步驟:

我的試算表 A 欄是姓名,有些名字中間有多餘空格(例如「王  小明」)。
請給我一個公式,在 B 欄輸出去掉多餘空格後的乾淨姓名。

AI 的答案:

=TRIM(A2)

TRIM 會移除文字頭尾以及中間多餘的空格。要處理全形空格,加一層 SUBSTITUTE:

=TRIM(SUBSTITUTE(A2, " ", " "))

(第一個引號內是全形空格,第二個是半形空格。)

拆分欄位:地址合在一起要分開,或者姓名欄裡有「姓+名+電話」全混在一起:

我的 A 欄資料格式是「台北市信義區△△路△△號」,
我想把「縣市」(前三個字)和「後面的詳細地址」分到 B、C 兩欄。
請給我 Excel 公式。

AI 給:

B2 = LEFT(A2, 3)
C2 = MID(A2, 4, LEN(A2)-3)

去除重複值:Excel 內建「資料 → 移除重複項」已夠用,但如果你需要「找出哪些列是重複的,不要直接刪」:

我的 A 欄是客戶 Email,我想在 B 欄標記哪些是重複的(第二次出現以後都標「重複」)。
請給我公式。

AI 給:

=IF(COUNTIF($A$2:A2, A2) > 1, "重複", "")

這個公式很聰明:它的查詢範圍會隨著列數往下擴展($A$2:A2),所以第一次出現的不標、第二次以後都標「重複」。

上傳檔案讓 AI 直接分析

如果你的資料量大、問題複雜、不想自己打欄位結構,可以直接把 Excel 或 CSV 上傳給 AI 讓它讀取並回答問題。這是「模式 2」的實際操作。

ChatGPT Plus(每月 20 美元):支援 .xlsx、.csv 上傳,單檔最大約 50MB。上傳後直接在聊天框問:

這是我們上半年的銷售資料。
請告訴我:
1. 哪個月份的銷售額最高?
2. 哪位業務員的業績成長最快?
3. 有沒有看起來異常的資料(遠高於或遠低於平均的記錄)?

ChatGPT 會讀取你的檔案、用 Python 計算,然後直接用文字告訴你結論,甚至畫出圖表。

Claude Pro(每月 20 美元):支援 .xlsx、.csv,單檔最大 30MB,每次對話最多上傳 20 個附件。上傳後同樣直接問問題即可。

三個關鍵限制,用之前要知道:

限制 1:AI 讀的是「儲存格顯示值」,不是公式計算邏輯。 如果你的表格裡有 VLOOKUP 或 IF 公式,AI 看到的是算出來的結果數字,不是公式本身。所以 AI 說「這格是 100」,但你的公式有 bug 導致這個 100 是錯的,AI 不會發現——它只看結果。

限制 2:VBA 巨集、加密、複雜合併儲存格很容易出問題。 含有 VBA 巨集的 .xlsm 檔案,AI 無法執行巨集;有密碼保護的 Excel 必須先移除密碼才能上傳;大範圍的合併儲存格可能讓 AI 讀取時對齊跑掉。最保險的做法:複製你要分析的資料到新工作表、取消所有合併儲存格、存成 .csv、再上傳。

限制 3:AI 的分析結論要驗證。 AI 算出「第三季業績 523 萬」,你要自己用 Excel 驗一下數字對不對。AI 在處理大量資料時偶爾會算錯,結論性數字一律自己比對。把 AI 當分析助理,不當最終答案。

圖表類型怎麼選:讓 AI 幫你決定

很多人做圖表的習慣是「打開 Excel 直接選柱狀圖」,結果資料是時間序列,用柱狀圖反而難讀。圖表選錯了,報告看起來專業度直接掉一半。

最快的做法:把你的資料情境說給 AI 聽,讓它告訴你選哪種圖:

我有一份資料:月份(1月到12月)對應每月業績金額。
我想在 PowerPoint 報告裡展示業績的走勢,讓人一眼看出成長或下滑。
請問我應該用哪種圖表?為什麼?

AI 的典型回答會是:用折線圖。因為你強調的是「走勢」和「時間連續變化」,折線圖在視覺上最能呈現趨勢方向。如果你改成「比較各部門業績」,AI 就會改推柱狀圖——因為這是比較靜態類別,不是連續變化。

幾個常用的判斷原則(讓 AI 幫你套用):

你不用背這個表。下次做報告時把你的資料情境和目的貼給 AI,它會直接幫你決定。

實戰:亂表變報表

把前面學到的東西組合起來,走一遍完整流程。情境:你收到業務部門傳來的原始銷售記錄,要整理成主管可以看的月報。

第一步:描述你拿到的表長什麼樣,讓 AI 規劃清理步驟

我收到一份銷售記錄 Excel,狀況如下:
- A欄:日期,格式混亂(有些是2024/3/5、有些是March 5、有些是20240305)
- B欄:業務員姓名,有些有多餘空格
- C欄:產品名稱,英文大小寫不一致(有些是"iPhone"有些是"iphone")
- D欄:金額,有些含逗號(例如"1,200")導致無法計算
- 資料大約 800 列

我要做的最終目標:一張按月份統計各業務員銷售額的樞紐分析表。
請告訴我清理步驟和公式,按優先順序排列。

AI 會給你一份有邏輯的清理計畫:先統一日期格式(用 TEXT 或 DATEVALUE)、清理姓名(TRIM)、統一大小寫(PROPER 或 UPPER)、去掉金額的逗號(SUBSTITUTE + VALUE)——按順序做,不會漏掉步驟。

第二步:資料乾淨後,請 AI 告訴你樞紐分析表的設定方式

清理完成後,我有一張表:
- A欄:日期(已統一為YYYY/MM/DD)
- B欄:業務員姓名(已清理)
- C欄:銷售金額(純數字)

我想做一個樞紐分析表:列是月份、欄是業務員名字,值是銷售金額加總。
請告訴我怎麼設定。

AI 會逐步告訴你:選取 A 到 C 欄資料 → 插入樞紐分析表 → 把「日期」拖到列區域並按月分組 → 把「業務員姓名」拖到欄區域 → 把「銷售金額」拖到值區域並設為加總。五分鐘搞定一張過去要花半天的報表。