用 ChatGPT 寫 Excel Power Query(M 語言)、Power BI DAX 公式與 Google Sheets Apps Script 自動化報表

每週重複手工合表、算同比環比與發彙總電郵既枯燥又容易點錯。本文提供讓 ChatGPT 精準生成 Power Query M 語言清洗腳本、Power BI DAX 動態度量值及 Google Sheets Apps Script 定時自動化代碼的實戰提示詞。

NNathaniel bigo··原創首發·AI 輔助撰寫
11 分鐘讀完
資料核對於 2026-11-19 · 依據官方文檔與公開資料整理 Plus 帳號

結論先說

  1. 先分清三者職責,不要用錯工具:底表清洗、拆分合並與去重交給 Power Query(M 語言);動態切片分析、同比環比和累計指標交給 DAX;雲端跨表格調度、定時觸發和自動發電郵交給 Google Apps Script。
  2. 向 ChatGPT 要代碼前,必須喂齊「表名 + 精確列名 + 3 行脫敏樣例 + 表間關係」:不給真實字段名和日期格式,模型生成的 M 代碼和 DAX 幾乎必然報「找不到列」或算出錯誤彙總。
  3. 寫 M 語言要求兼容「缺失列/新增列」與空值異常,寫 DAX 強制顯式說明日期表結構:提前約束邊界條件,報表下個月多出一列或遇到空數據時才不會刷新崩潰。
  4. 寫 Apps Script 嚴禁在 for 循環裏逐格調用 getValue / setValue:必須在提示詞裏強制要求「一次性批量讀取二維數組,在內存處理後再批量回寫」,速度可差出幾十倍且避免觸發超時限制。
  5. 複雜報表常涉及多輪報錯排查與上載脫敏樣例表驗證邏輯:免費版在高階推理模型與代碼交互次數上受限明顯,國內無外幣卡用戶可通過本站 ChatGPT Plus 自助充值 或 ChatGPT Plus 會員直充 獲得更穩定的代碼調試與數據分析體驗。

M 語言、DAX 與 Apps Script 分工對照

圖:本站根據 Microsoft Learn 與 Google 開發者文檔 整理的示意圖

本文根據 Microsoft Learn《Power Query M formula language reference》《DAX Reference》及 Google 開發者文檔《Google Apps Script: Google Sheets Guide》整理,資料核對於 2026-11-19。配圖為本站整理的示意圖,不是介面截圖。本站(bigolab.com)不是 OpenAI 官方網站。

適用於誰

  • 財務、電商運營與商業分析(BI)從業者,每週都要把幾十份渠道 CSV/Excel 清洗彙總,想用 Power Query(M 語言)實現一鍵刷新的人;
  • Power BI 報表搭建者,被 CALCULATE、FILTER、時間智能同比環比等 DAX 篩選上下文繞暈,希望讓 ChatGPT 寫出可直接粘貼且能解釋邏輯的度量值的人;
  • 跨境電商、遠程團隊與效率控,日常使用 Google Sheets 協作,想用 Apps Script 實現定時抓取數據、自動排版或按條件觸發電郵提醒的人。

一、實戰場景一:用 ChatGPT 寫 Power Query(M 語言)一鍵清洗多表

如果你平時只是在普通單元格里寫常規公式,可先看 用 ChatGPT 寫 Excel 公式與模板 或 ChatGPT 官方 Excel 加載項與 VBA 說明。一旦遇到「每週把文件夾裏 10 個格式略有不同的銷售表合併、去掉前 3 行無用標題、把橫向月份透視成縱向長表」這類重複清洗活,直接打開 Excel 或 Power BI 的「高級編輯器」,讓 ChatGPT 幫你生成整段 M 語言代碼:

text
請幫我寫一段可用於 Excel Power Query 高級編輯器的完整 M 語言(Power Query M)腳本。

數據源與結構:
- 輸入表來源:本地文件夾路徑 `C:\Reports\MonthlySales\` 下所有 `.xlsx` 文件(工作表名均為 `Sheet1`)
- 原始文件問題:每個文件前 2 行是合併標題和導出日期,第 3 行才是真實表頭,包含列:`SKU編碼`、`商品名稱`、`2026-01`、`2026-02`、`2026-03`(未來月份列會動態增加)
- 末尾常有一行 `總計` 彙總行需要剔除

清洗目標:
1. 自動合併文件夾下全部 `.xlsx`(排除以 `~$` 開頭的臨時鎖定文件),並保留來源文件名列;
2. 跳過前 2 行並將第 3 行提升為表頭,過濾掉 `SKU編碼` 為空或等於「總計」的行;
3. 對除 `來源文件`、`SKU編碼`、`商品名稱` 外的所有動態月份列執行「逆透視其他列」,生成 `月份` 和 `銷售額` 兩列;
4. 將 `銷售額` 安全轉換為數值型(遇到無法轉換的異常字符用 `try ... otherwise 0` 處理而非整表報錯);
5. 請在每一步 M 代碼前加簡潔中文註釋,並確保所有函數大小寫嚴格符合 Power Query M 規範。

把代碼粘貼進「高級編輯器」並替換成你的真實文件夾路徑,以後每週只需把新導出的報表丟進文件夾、點一下「全部刷新」,清洗步驟全自動完成。

二、實戰場景二:用 ChatGPT 寫準 Power BI DAX 度量值

寫 DAX 最容易踩的坑是把「行上下文」和「篩選上下文」弄混,導致明細行正確、總計行卻算錯。提問時一定要把數據模型裏的表名、連接關係和獨立日期表交代清楚:

text
我正在 Power BI Desktop 中構建銷售分析模型,現有表結構如下:
1. 事實表 `FactSales`:列包含 `OrderDate`(日期)、`CustomerID`、`ProductID`、`NetAmount`(實付金額)、`OrderStatus`(含「已支付」「已退款」)
2. 日期表 `DimDate`(已標記為日期表,連續無斷層):主鍵 `Date`,另有 `Year`、`Month`、`YearMonth`
3. 表間關係:`DimDate[Date]` 一對多單向篩選 `FactSales[OrderDate]`

請幫我編寫 3 個高性能 DAX 度量值,並逐行解釋每條語句的篩選上下文邏輯:
1. `有效淨銷售額`:僅統計 `OrderStatus = "已支付"` 的 `NetAmount` 彙總;
2. `去年同期銷售額 (YoY Base)`:嚴格基於 `DimDate[Date]` 計算去年同期有效淨銷售額;
3. `銷售額同比增長率`:使用 `DIVIDE` 防除零錯誤,去年同期為 0 或空時返回 `BLANK()`,且確保在可視化卡片和矩陣總計行均計算準確。

請同時提醒我:如果報表頁選了非整月區間,這三個公式可能出現什麼邊界偏差以及如何修正。

如果導入後依然報錯或數字不符,把脫敏後的幾行數據直接上載讓 ChatGPT 用內置沙盒反推驗證(玩法見 ChatGPT 分析 Excel 數據實戰),能很快揪出是關係方向錯了還是日期格式不連續。

額度與訂閱提示:反覆迭代調試 M 語言、DAX 複雜上下文以及 Google Apps Script 自動化腳本時,高階推理模型的報錯定位能力比免費輕量檔穩定得多。國內無外幣卡用戶可通過本站 ChatGPT Plus 充值指南 或免交密碼的 ChatGPT Plus 卡密兌換 解鎖高頻代碼生成額度;若團隊剛起步配置 Google Workspace 與 Sheets 協作環境,也可按需查閱 Google 帳號服務指南。

三、實戰場景三:用 ChatGPT 寫 Google Sheets Apps Script 定時自動化

在 Google Sheets 中點擊頂部選單「擴展程序 -> Apps Script」,即可把 ChatGPT 生成的 JavaScript 代碼粘進去實現定時匯表、異常標紅和電郵告警。為了防止腳本跑幾十行就超時,複製下面這條模板:

text
請幫我編寫一段符合 Google Apps Script V8 運行時規範的腳本,綁定在我的 Google Sheets 中。

工作表結構:
- 數據表名:`DailyOrders`
- 表頭在第 1 行,列分別為:A列 `日期`、B列 `訂單號`、C列 `渠道`、D列 `金額`、E列 `狀態`、F列 `異常標記`

自動化需求:
1. 添加 `onOpen()` 自定義選單「報表工具 -> 檢查異常並發送日報」;
2. 編寫主函數 `checkAndSendDailyReport()`:使用 `getValues()` 一次性讀取整表二維數組(嚴禁在循環內調用單元格 API),篩選出「昨日」所有 `狀態` 為 `待處理` 且 `金額 > 5000` 的高額預警訂單;
3. 對命中記錄在內存中給 `異常標記` 列寫入「需覆核」,最後用 `setValues()` 一次性回寫;
4. 若存在預警訂單,組裝簡潔 HTML 表格通過 `MailApp.sendEmail()` 發送給 `[email protected]`;若無預警則僅記錄 `Logger.log` 不發打擾電郵;
5. 代碼外請附帶簡明步驟:如何配置每日上午 9 點自動執行的時間驅動觸發器(Time-driven Trigger),以及首次授權時的注意事項。

生成代碼後,如需快速測試普通腳本片段或查看語法修正,可結合 用 ChatGPT 寫代碼與報錯排查教學 逐段驗證;表格導出後若要回流本地 Excel,也可參考 ChatGPT 表格無損導入 Excel 方法。

讓 ChatGPT 寫準報表代碼必給上下文

圖:本站根據 Microsoft Learn 與 Google Apps Script 文檔 整理的示意圖

常見問題

問:Power Query 的 M 代碼粘進去提示 Expression.Error: 找不到表的列 怎麼辦?

答:M 語言不僅嚴格區分大小寫,而且只要源文件表頭多了一個隱形空格或換了字段名就會立刻報錯。把完整紅色報錯截圖或報錯文字,連同當前步驟的真實表頭列表複製給 ChatGPT,要求它在 Table.TransformColumnNames 步驟中先統一執行 Text.Trim 去首尾空格,並改用容錯參數 MissingField.Ignore 或 MissingField.UseNull。

問:為什麼 ChatGPT 寫的 DAX 度量值在明細行完全正確,但表格底部的「總計」行數字卻對不上?

答:這是 DAX 初學者最常見的聚合錯覺——總計行並不是把上面各明細行結果相加,而是在去掉行維度篩選條件後把公式重新算了一遍。把原公式和表格的行字段名稱貼給 ChatGPT,直接要求「改用 SUMX(VALUES(維度列), [原度量值]) 修正總計行邏輯」即可解決絕大多數總計偏差。

問:Google Sheets Apps Script 首次運行彈出「Google 尚未驗證此應用」警告安全嗎?

答:這是自己編寫並綁定在私人表格裏的未上架腳本的正常安全提示。只要代碼是你親自審核並粘貼進去的、沒有包含陌生的外部請求網址(如可疑 UrlFetchApp.fetch 地址),在授權彈窗中點擊「高級 -> 轉至項目名稱」即可授權運行;切勿盲目運行來源不明的他人腳本。

問:國內沒有外幣卡,怎麼穩定開通 ChatGPT Plus / Pro 或團隊版以解鎖多文件上載與高階模型?

答:可前往本站 AI 會員充值總覽 進入 ChatGPT Plus 充值頁 獲取穩定的高階代碼模型與表格解析額度;若你日常需要頻繁處理超大型多表自動化工程與深度推理排錯,也可以對比升級 ChatGPT Pro 充值方案,需要配置 Google 協作環境的團隊還可按需了解 Google 帳號相關服務。支持支付寶、微信支付及 AlipayHK。實時價格以頁面展示為準;訂閱期內若掉訂閱按剩餘天數折退,因違反官方條款導致的封號不在質保範圍內。

相關教學

參考資料

  • Microsoft Learn:Power Query M formula language reference — https://learn.microsoft.com/en-us/powerquery-m/
  • Microsoft Learn:Data Analysis Expressions (DAX) Reference — https://learn.microsoft.com/en-us/dax/
  • Google Developers:Google Apps Script — Google Sheets Guide — https://developers.google.com/apps-script/guides/sheets

Nathaniel 的更多內容

  1. 01

    獨立開發者用 ChatGPT + Codex 跑通產品全流程:市場驗證、PRD、原型到代碼上線覆盤清單

    單人做獨立產品最容易陷入「閉門寫兩個月代碼上線零用戶」或「AI 寫到一半目錄越改越崩」。本文整理「ChatGPT 負責市場驗證、PRD 與覆盤 + Codex 負責工程實現與測試」的五階段雙軌流程,附起手提示詞與 7 項上線覆盤清單。

    ChatGPTCodex0
  2. 02

    用 ChatGPT 批量生成規範文件命名規則、整理混亂目錄樹與合併零散發票抬頭清單腳本

    面對滿桌面的「新建文件夾」「最終版2」和上百張零散電子發票,手工改名抄台帳耗時又容易漏重。本文分享先脫敏取樣、再用 ChatGPT 生成帶預演和回滾日誌的本地 Python 腳本,離線安全完成目錄整理與發票抬頭合併去重的實戰流程。

    ChatGPT0
  3. 03

    用 ChatGPT 做海外電郵營銷(EDM)受眾分層文案、A/B 主題行測試與防進垃圾箱自檢

    做海外獨立站或出海 SaaS 電郵營銷(EDM),最怕千人一面群發被直接送進垃圾箱。本文結合 Gmail/Yahoo 批量發件人規範,梳理用 ChatGPT 設計受眾分層文案、批量生成 A/B 主題行矩陣並開展發前垃圾詞與合規自檢的實戰提示詞。

    ChatGPT0
  4. 04

    用 ChatGPT 寫 Notion Formula 2.0 公式、Obsidian Dataview 查詢與 Airtable 自動化腳本

    給 Notion 數據庫寫多條件 Formula 2.0 公式、給 Obsidian 寫 Dataview 動態看板,或在 Airtable 裏寫自動化腳本,經常因為一個小括號或類型不匹配報錯半天。本文整理三大工具的通用上下文喂料規範與三套直接可複製的生成提示詞。

    ChatGPT其他 AI 工具0
  5. 05

    用 ChatGPT 處理複雜 JSON / YAML / XML 字段映射、格式轉換與校驗規則編寫實戰

    對接第三方 API、遷移 Kubernetes 配置或解析政企 XML 報文時,手工對照幾百行嵌套字段既慢又容易漏掉空值與類型邊界。本文整理用 ChatGPT 完成字段映射表梳理、生成確定性轉換腳本以及編寫 JSON Schema / Pydantic / XSD 校驗規則的實戰流。

    ChatGPT其他 AI 工具0
  6. 06

    用 ChatGPT 準備本科/碩士畢業論文答辯:提煉答辯 PPT 結構、模擬導師犀利追問與 3 分鐘自述稿

    畢業論文寫完後,要在 3–5 分鐘內講清研究價值並頂住答辯評委的連環追問並不輕鬆。本文整理在嚴格遵守高校學術規範的前提下,用 ChatGPT 提煉答辯 PPT 結構、打磨 3 分鐘口語自述稿並開展模擬導師犀利追問的排練流程與提示詞。

    ChatGPT0

同產品的其他教學

  1. 01

    用 ChatGPT 整理家庭裝修預算明細、隱蔽工程驗收清單與主材優缺點對比表實戰

    第一次裝修面對幾十頁施工報價單、密密麻麻的水電防水隱蔽項目與五花八門的主材參數,極易踩坑增項。本文梳理用 ChatGPT 審查裝修報價漏項、生成帶公式的預算明細表、定製工地隱蔽工程驗收單與主材對比表的實戰提示詞。

    ChatGPT0
  2. 02

    用 ChatGPT 做費曼學習法讀書共讀:反向追問檢驗自己是否真讀懂專業書籍與整理書單筆記

    讀完一本專業硬書常感覺「合上書就忘」,直接讓 AI 概括全書又容易產生懂了的錯覺。本文介紹用 ChatGPT 結合 Study Mode 與費曼學習法做反向追問共讀,通過通俗複述、挑刺盲區到沉澱書單卡片的完整流程與提示詞。

    ChatGPT0
  3. 03

    用 ChatGPT 做外貿產品多語種目錄、展會邀約與客戶跟進節奏表(附脫敏與本地化提示詞)

    做外貿展會與海外推廣時,手工翻譯十幾款產品目錄費時且容易留下一堆中式英文,展後客戶跟進也常缺節奏。本文梳理用 ChatGPT 先脫敏建術語庫、再批量產出多語種目錄、展前邀約函與展後 21 天跟進節奏表的實戰流程與提示詞。

    ChatGPT0
  4. 04

    用 ChatGPT 寫高質素技術專欄文章、開源項目 Release Notes 與版本更新日誌實戰

    讓 AI 直接憑空寫技術文章容易滿篇空洞套話,直接甩 Git 記錄寫更新日誌又常常漏掉破壞性變更。本文整理用 ChatGPT 結合真實代碼片段、壓測數據與 PR 摘要,高效產出深度技術專欄、GitHub Release Notes 與 CHANGELOG 的實戰工作流。

    ChatGPT0
  5. 05

    用 ChatGPT 做產品用戶訪談紀要編碼、提煉高頻痛點需求池與 RICE 優先級打分實戰

    手頭攢了十幾份用戶訪談逐字稿,手工貼便利貼歸納痛點耗時又容易帶入主觀偏見。本文分享用 ChatGPT 完成脫敏檢查、逐份開放式編碼、跨樣本聚類高頻痛點並搭建 RICE 優先級打分表的 5 步實戰流,附可直接複製的三段核心提示詞。

    ChatGPT0
  6. 06

    ChatGPT 網頁版上載超過 10 個文件或回形針附件按鈕變灰怎麼辦:打包合併與配額限制排查

    在 ChatGPT 網頁版批量分析文檔時,常遇到一次選超 10 個文件被攔截或輸入框回形針按鈕變灰。本文依據 OpenAI 文件上載與存儲說明,梳理 6 類常見報錯的分流排查法,提供 ZIP 打包、本地合併與 Projects 複用方案及可直接複製的解包提示詞。

    ChatGPT0

0 條評論

登入 後參與評論

還沒有評論,來搶沙發~