Excel LAG 函數是什麼？核心概念與應用場景
在數據分析領域，「LAG」指的是取得資料序列中前一筆（或多筆）資料的值，常用於財務、時間序列、庫存、用戶行為等分析。舉例來說，若你想比較本月與上月的業績差異、或分析每日用戶活躍度變化，LAG 功能就能大幅簡化你的計算流程。
值得注意的是，Excel 並未內建名為「LAG」的函數，但我們可以透過其他函數（如 OFFSET、INDEX）靈活實現同樣效果。
常見應用場景
- 財務數據分析：比較本期與上期收入、成本、毛利等變化。
- 時間序列分析：分析每日、每月、每季數據的波動趨勢。
- 庫存管理：追蹤庫存進出變化，預測未來需求。
- 用戶行為分析：計算用戶留存、活躍度變化等。
Excel 如何實現 LAG 功能？OFFSET 與 INDEX 公式詳解
雖然 Excel 沒有直接的 LAG 函數，但可以利用 OFFSET 或 INDEX 搭配 ROW 等函數，輕鬆取得前一行（或多行）的數據。
OFFSET 函數實現 LAG
OFFSET 會根據指定的起點、行數與列數偏移，返回目標單元格的值。
範例公式：
假設 B2:B13 為月度收入，C3 欲計算本月與上月收入差異：
=B3 - OFFSET(B3, -1, 0)
OFFSET(B3, -1, 0)取得 B2（上月收入）B3 - 上月收入得出本月變化
優點：語法直觀，適合簡單偏移
缺點：大量複製時效能較低，易因範圍錯誤導致 #REF! 錯誤
INDEX 函數實現 LAG
INDEX 透過指定範圍與行號，返回對應資料。
範例公式：
同樣以 B2:B13 為例，C3 欲取得上月收入：
=INDEX(B$2:B$13, ROW(B3)-ROW(B$2))
ROW(B3)-ROW(B$2)計算偏移行數（C3 對應 B2）- INDEX 返回上月資料
優點：效率高，適合大量數據，複雜應用更穩定
缺點：語法稍複雜，需注意行號計算
OFFSET 與 INDEX 比較表
| 特性 | OFFSET | INDEX |
|---|---|---|
| 易用性 | 直觀，易上手 | 需理解行號計算 |
| 效能 | 大量資料時較慢 | 效能較佳，適合大數據 |
| 靈活性 | 靈活偏移多行多列 | 適合動態範圍與複雜查找 |
| 常見錯誤 | 超出範圍易出現 #REF! | 行號計算錯誤易出現 #REF! |
實務案例：計算月度收入變化
假設你有以下月度收入資料：
| 月份 | 收入 |
|---|---|
| 1月 | 50,000 |
| 2月 | 53,000 |
| 3月 | 51,500 |
| 4月 | 55,200 |
你希望在 C 欄計算每月與上月收入的差異：
- 在 C3 輸入
=B3-OFFSET(B3,-1,0)或=B3-INDEX(B$2:B$13,ROW(B3)-ROW(B$2)) - 向下複製公式
- 第一筆（1月）因無上月資料，會出現錯誤，可用 IFERROR 或 IF 判斷處理
進階技巧：
若需計算「前兩月」的 LAG，可將偏移量改為 -2，或調整 INDEX 行號。
進階應用：多重 LAG、動態範圍與 VBA 自訂函數
多重 LAG（多期延遲）
若需取得前 N 期資料，只需調整偏移數：
- OFFSET：
=OFFSET(B3,-N,0) - INDEX：
=INDEX(B$2:B$13,ROW(B3)-ROW(B$2)-N+1)
動態範圍 LAG
若資料長度不固定，可用 COUNTA 動態計算範圍，或將範圍設為表格（Ctrl+T），公式自動延展。
VBA 自訂 LAG 函數
對於大量或複雜需求，可用 VBA 自訂函數：
Function LAG(rng As Range, n As Integer)
If rng.Row - n < rng.Worksheet.UsedRange.Row Then
LAG = CVErr(xlErrNA)
Else
LAG = rng.Offset(-n, 0).Value
End If
End Function
用法：=LAG(B3,1) 即取得上期資料。
常見錯誤與排查指南
| 問題描述 | 可能原因 | 解決方法 |
|---|---|---|
| #REF! 錯誤 | 偏移超出範圍 | 檢查公式起始位置，首行用 IF 判斷 |
| #VALUE! 錯誤 | 資料類型不符 | 確認資料皆為數值 |
| 結果不正確 | 行號計算錯誤 | 檢查 ROW 計算式，確保對應正確 |
| 公式複製異常 | 絕對/相對參照設錯 | 使用 $ 固定範圍 |
建議：將公式加上 IFERROR 或 IF 判斷，避免首行出錯。
LAG 功能在 Google Sheets、Power BI 的對比
| 工具 | 內建 LAG 支援 | 實現方式 | 適用場景與優缺點 |
|---|---|---|---|
| Excel | 無 | OFFSET、INDEX、VBA | 靈活度高，需手動設計 |
| Google Sheets | 無 | OFFSET、INDEX | 與 Excel 類似，支援即時協作 |
| Power BI | 有（DAX LAG） | DAX 函數（如 EARLIER） | 內建函數，適合大數據與自動化分析 |
補充：若需雲端協作與自動化，建議可考慮 Notion、Monday.com、ClickUp 等工具，這些平台在數據整合、流程自動化、團隊協作方面比 Excel 更具彈性，適合專案管理與跨部門合作。
LAG 應用流程圖與重點摘要
- 明確分析目標（如：比較本期與上期數據）
- 選擇合適公式（OFFSET 或 INDEX）
- 設計公式，注意首行/邊界處理
- 檢查結果，排查常見錯誤
- 進階應用（多重 LAG、動態範圍、VBA）
- 根據需求考慮自動化工具輔助
重點摘要表
| 步驟 | 公式範例 | 注意事項 |
|---|---|---|
| 單期 LAG | =OFFSET(B3,-1,0) | 首行需判斷 |
| 多期 LAG | =OFFSET(B3,-N,0) | N 為期數 |
| INDEX 實現 | =INDEX(B$2:B$13,ROW(B3)-ROW(B$2)) | 行號計算需正確 |
| 動態範圍 | 搭配 COUNTA、表格 | 範圍自動延展 |
| VBA 自訂 | Function LAG(…) | 適合大量或複雜需求 |
不同 Excel 版本與 LAG 功能支援度
- 桌面版 Excel：OFFSET、INDEX 皆支援，VBA 可用
- Excel 365/Web 版：同上，部分新函數（如 LAMBDA）可自訂 LAG
- 手機/平板版：部分函數受限，建議於桌面操作
合作夥伴工具推薦：提升數據自動化與團隊協作
若你希望將數據分析流程自動化、提升團隊協作效率，推薦以下工具：
- Monday.com：專案流程自動化，適合跨部門協作與數據追蹤
- Notion：彈性記錄、知識管理，適合複雜專案與資料整合
- ClickUp：AI 驅動任務管理，適合高效團隊協作
- pdfFiller / SignNow：PDF 數據處理與簽署，適合報表自動化
- Sanebox：郵件自動整理，提升溝通效率
- Coursera：專業 Excel 與數據分析課程，適合進階學習
結語
雖然 Excel 沒有內建 LAG 函數，但透過 OFFSET、INDEX 及進階技巧，你可以靈活實現各種延遲分析需求。若需大規模自動化、團隊協作或進階數據處理，建議結合 Monday.com、Notion、ClickUp 等現代化工具，或參考 Coursera 的專業課程，讓你的數據分析與專案管理更上一層樓。
