萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 辦公室軟體 >  >> Excel 教學 >> Excel 綜合

Excel 2016 預測工作表 (Forecast Sheet) 完整實戰:從歷史數據自動生成趨勢預測圖表,掌握季節性參數關鍵設定

Excel 2016 引入的「預測工作表」功能,能依歷史時間序列資料,自動套用 ETS (Exponential Triple Smoothing) 演算法,產生未來趨勢預測、信賴區間、並輸出完整圖表與資料表。關鍵在於正確設定季節性參數。本文以「每月經驗發文數」為例,完整示範從資料準備、建立預測工作表、調整季節性、驗證準確度、解讀輸出欄位的全流程。

功能簡介與適用情境

  • 功能位置:「資料」索引標籤 →「預測」群組 →「預測工作表」。
  • 演算法:ETS (指數三重平滑),支援趨勢 + 季節性。
  • 輸出:新工作表包含原始資料、預測值、信賴下限、信賴上限、預測圖表。
  • 適用:銷售額預測、網站流量預測、庫存需求規劃、人力排程、任何固定間隔的時間序列資料。

資料準備規範

  • 兩欄:時間欄(日期/時間格式,固定間隔:日/週/月/季/年)+ 數值欄(數值格式)。
  • 無空白列、無重複時間點(重複會依「彙總方式」合併)。
  • 建議至少 2-3 個完整季節週期(如月資料需 24-36 筆以上)。

建立預測工作表步驟

步驟 1:選取任一資料儲存格 → 點擊「預測工作表」

游標置於資料表任一儲存格 →「資料」→「預測工作表」。

步驟 2:初次預覽——預設「自動偵測季節性」常失效

對話盒顯示藍線(歷史)、橙線(預測)。若橙線呈直線(線性),表示季節性未正確偵測,需手動設定。

步驟 3:點擊「選項」展開進階參數

步驟 4:關鍵參數設定

參數設定值說明
預測結束2017/4/1從最後一期 (2016/4) 向後推 12 個月
季節性設定手動設定 = 12資料為月度,一年 12 個月為一季節週期
預測開始預設 (最後一期之後)可提前至較早期驗證準確度 (見下文)
信賴區間95% (預設)值越小,上下限範圍越窄
填補缺失值內插 (預設)依加權平均插補;可改 0 不插補
彙總重複項平均 (預設)同一時間點多筆資料的彙總方式

步驟 5:點擊「建立」生成預測工作表

新工作表包含:原始時間/數值、預測值、信賴下限、信賴上限、圖表。圖表顯示歷史藍線、預測橙線、灰色信賴區間帶。

驗證預測準確度:提前「預測開始」時間回測

將「預測開始」設為 2015/12/1(第二個週期結束點),預測 2016/1-4 的數值,與實際歷史資料對比。若橙線緊貼藍線,代表模型擬合度高、預測可信度高。

輸出欄位解讀

  • 趨勢預測:模型點估計值(最可能發生的數值)。
  • 信賴下限/上限:95% 信賴區間,實際值有 95% 機率落在區間內。
  • 季節性 = 12:代表模型偵測到每年 12 月週期重複模式(如範例中 5 月、10 月為高峰)。

進階:FORECAST.ETS 函數直接在儲存格計算

預測工作表底層呼叫 FORECAST.ETS、FORECAST.ETS.CONFINT、FORECAST.ETS.SEASONALITY、FORECAST.ETS.STAT 等函數。可直接在任意儲存格撰寫公式,靈活嵌入報表:
=FORECAST.ETS(目標日期, 數值範圍, 時間範圍, [季節性], [資料完整性], [彙總方式])

注意事項與限制

  • 歷史週期越多,準確度越高:建議 ≥ 3 個完整季節週期。
  • 非固定間隔資料:需先整理為固定間隔(可用樞紐表/公式填補)。
  • 劇烈結構性變化(如疫情、政策變革)模型無法預知,需人工判斷調整。
  • 資料筆數上限:單一預測最多 6000 筆歷史資料。

小結

Excel 2016 預測工作表讓非統計專業者也能輕鬆產出專業級時間序列預測。核心在於正確設定季節性參數(月資料=12、季資料=4、日資料=7/365),並善用「預測開始」回測驗證模型擬合度。輸出的信賴區間提供決策風險範圍,配合 FORECAST.ETS 函數彈性嵌入報表,是商業決策支援的強大工具。

copyright © 萬盛學電腦網 all rights reserved