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

圖:本站根據 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 方法。

圖:本站根據 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。實時價格以頁面展示為準;訂閱期內若掉訂閱按剩餘天數折退,因違反官方條款導致的封號不在質保範圍內。
相關教學
- ChatGPT 分析 Excel 數據:上載表格、清洗、透視與出圖(附可複製提問)
- 用 ChatGPT 做表格、寫 Excel 公式:在對話裏描述需求、上載表格、修公式、生成表格模板
- ChatGPT 怎麼用在 Excel 裏:官方 Excel 加載項安裝、公式報錯、整理數據,VBA 和宏支持嗎
- 用 ChatGPT 寫代碼怎麼用:代碼塊的複製、編輯、預覽、運行 Python,報錯了怎麼辦
- ChatGPT 生成的表格複製到 Excel 串行、公式變純文本怎麼辦:直接生成 .xlsx 文件與無損粘貼方法
參考資料
- 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
0 條評論
還沒有評論,來搶沙發~