預測函式整理：FORECAST、TREND、SLOPE、INTERCEPT 與 CORREL

「下個月大概會賣多少？」被問到這句的時候，我通常不想開統計軟體。Google 試算表有一組線性預測函式，FORECAST、FORECAST.LINEAR、TREND，加上拆開看迴歸線的 SLOPE、INTERCEPT，以及判斷資料有沒有關聯的 CORREL，這篇一次整理。

先說我個人的習慣：單點預測我用 FORECAST.LINEAR，一次要好幾個月就用 TREND，SLOPE 跟 INTERCEPT 多半是想看「每個月平均增加多少」的時候才用。這些函式都假設資料走的是一條直線，所以下面會再接三個情境，資料上下跳動、有淡旺季、想知道預測準不準的時候怎麼辦。

下圖是我選方法的順序，不確定的時候先畫折線圖，看形狀再決定。

← 左右滑動看完整的圖，點圖可放大

圖：先看資料的形狀，再決定用哪個方法。

範例資料

下面這組是 12 個月的營收（單位：千元），後面的函式都用它：

貼到試算表，A1 是「月」、B1 是「營收」，月份 1 到 12 在 A2:A13，營收在 B2:B13。

如果要手動預測第 13 個月，可以拿尺在折線圖上畫一條直線再延伸，不過每個人畫的線都不一樣。用最小平方法的話，要先算四個總和：x 的總和 78、y 的總和 1570、x 乘 y 的總和 10971、x 平方的總和 650，然後：

• 斜率 = (12 × 10971 − 78 × 1570) ÷ (12 × 650 − 78²) = 9192 ÷ 1716 ≈ 5.3566
• 截距 = (1570 − 5.3566 × 78) ÷ 12 ≈ 96.0152
• 第 13 個月 = 96.0152 + 5.3566 × 13 ≈ 165.65

手算一次就夠了，之後交給函式。以下函式顯示的小數，我都把儲存格調成 2 位小數。

預測值怎麼算

FORECAST

用已知的 x 和 y 配出一條直線，回傳指定 x 對應的 y。

語法：=FORECAST(x, 已知 y 範圍, 已知 x 範圍)

• x：要預測的那個點，這裡是月份。
• 已知 y 範圍：過去的營收。
• 已知 x 範圍：過去的月份。

注意 y 在前、x 在後，跟直覺的順序相反，很多人第一次會填反。

預測第 13 個月：

結果是 165.65，跟前面手算的一樣。

兩個範圍的列數要一樣多。如果寫成 =FORECAST(13, B2:B12, A2:A13)，會得到 #N/A。

FORECAST.LINEAR

FORECAST.LINEAR 的語法跟 FORECAST 一樣，算出來的結果也一樣。

語法：=FORECAST.LINEAR(x, 已知 y 範圍, 已知 x 範圍)

預測第 13 個月：

結果是 165.65。

我自己新寫的算式會用 FORECAST.LINEAR，因為名字比較清楚，知道它是線性的。舊檔案裡看到 FORECAST 就不用特地改。它一次只回傳一個值，要一次預測好幾個月，換 TREND。

TREND

TREND 同樣是用直線預測，差別是可以一次給很多個新的 x，結果直接排成一欄。

語法：=TREND(已知 y 範圍, [已知 x 範圍], [新 x 範圍], [有截距])

• 已知 y 範圍：過去的營收。
• 已知 x 範圍（選填）：過去的月份，不填的話會當成 1、2、3……。
• 新 x 範圍（選填）：要預測的 x，不填的話就是算出已知 x 的配適值。
• 有截距（選填）：預設 TRUE，填 FALSE 會強制直線通過原點。

一次預測第 13 到 15 個月，新 x 我用 SEQUENCE 產生 13、14、15：

結果會從公式所在的儲存格往下溢出三格：

在 Google 試算表，TREND 這種本來就回傳陣列的函式，直接按 Enter 就會自動溢出，不需要任何特殊按鍵。Excel 舊版要選好範圍再按 Ctrl+Shift+Enter，Google 試算表不用。在 Chrome 按 Ctrl+Shift+Enter（Mac 為 ⌘+Shift+Enter）會幫公式自動加上 ARRAYFORMULA，用在 TREND 上用不到。溢出的下方儲存格如果已經有資料，會顯示 #REF!，把擋住的內容清掉就好。

第四個參數我只在「確定營收 0 的時候 x 也是 0」才會填 FALSE。這組資料如果填了：

結果會變成 219.42，比 165.65 高出一大截，因為直線被硬壓過原點，斜率跟著變陡。一般情況放著不填就好。

預測值畫成折線圖，實線是實際、虛線是預測，一眼看得出有沒有接得上：

圖：預測虛線沿著實際資料的走向往右延伸，但它不知道之後會不會有突發狀況。

斜率、截距與相關係數

SLOPE

SLOPE 回傳那條直線的斜率，白話就是「x 每增加 1，y 平均增加多少」。

語法：=SLOPE(已知 y 範圍, 已知 x 範圍)

結果是 5.36，也就是營收平均每個月增加 5.36 千元。

y 在前、x 在後這件事在這裡更要小心。如果填反成 =SLOPE(A2:A13, B2:B13)，Google 試算表不會報錯，只會給你另一個數字 0.18，那是「營收每增加 1 千元，月份增加多少」，完全是另一回事。

INTERCEPT

INTERCEPT 回傳直線和 y 軸交會的位置，也就是 x = 0 時的 y。

語法：=INTERCEPT(已知 y 範圍, 已知 x 範圍)

結果是 96.02。這組資料的月份從 1 開始，沒有 0 月，所以 96.02 比較像「第 0 個月的起跑點」，單看它沒有太多意義。我用它是為了和 SLOPE 一起拼出預測值：

結果還是 165.65。

把兩個數字對上圖表：在圖表編輯器替系列加上趨勢線並顯示公式，圖上標出來的係數就是 SLOPE 和 INTERCEPT 的結果，5.36 和 96.02。

圖：趨勢線的公式就是 SLOPE 和 INTERCEPT 的結果。

CORREL

CORREL 回傳兩組數字的相關係數 r，範圍在 −1 到 1 之間，越接近 1 代表兩組數字一起往上升的關係越緊。

語法：=CORREL(y 範圍, x 範圍)

結果是 0.98，月份跟營收幾乎是同步上升的，直線預測在這組資料上合理。如果算出來只有 0.3 之類的低數字，就別拿直線預測了，直線貼不住資料。

兩個要記得的限制。第一，CORREL 只看直線關係，曲線的關係它看不到。第二，r 高只代表兩者一起變動，不代表其中一個造成另一個。

資料上下跳動的時候

上面那組資料其實還算乖。如果每個月忽高忽低，直線預測會被單月的異常拉著跑，這時候我會先做移動平均，把跳動磨平一點再看趨勢。

簡單移動平均是取最近 3 個月的平均，從第 3 個月開始算，C4 放：

往下複製。指數移動平均（EMA）給近期資料較高的權重，我用 α = 0.5，D2 先放 =B2，D3 開始放：

往下複製。三組數字放在一起：

三條線畫成折線圖，會比較容易看出差別：

圖：兩條平均線都比原始線平滑，也都慢了半拍。

如果把最後一格當作下個月的預測，移動平均是 153.67，EMA 是 155.71，都比 TREND 的 165.65 低一截。資料一路往上的時候，平均線永遠追不上，這是這類方法的特性。所以這組資料我不會用平均線預測，平均線比較適合用來「看」趨勢有沒有轉向。

α 要多大沒有標準答案，我通常從 0.3 開始試，越大越跟得上最新資料，也越容易跟著雜訊跳。

有淡旺季的資料

換一組有季節性的資料，兩年的月營收（千元）：

月份在 A2:A13，第 1 年在 B 欄，第 2 年在 C 欄。兩年的走勢很像，CORREL(B2:B13, C2:C13) 是 0.9986，旺季都在 7 月，淡季都在 2 月。

圖：兩年的高低峰落在同樣的月份，第 2 年整體墊高。

季節指數是「該月營收除以當年的月平均」，兩年各算一次再平均。D2 放：

往下複製到 D13，1 月是 0.76，7 月是 1.32，12 個指數平均起來是 1。

接著預測第 3 年的 1 月，也就是第 25 個月。先把兩年 24 個月接起來配一條趨勢線：

結果是 128.81。這是只看趨勢的答案，可是第 2 年 1 月才 91，淡季突然高出四成多，明顯不對。乘上 1 月的季節指數：

結果是 98.22，比第 2 年 1 月的 91 多了不到一成，看起來合理多了。

坦白說這個做法比較粗，指數是用每年各自的平均算的，沒有扣掉年與年之間的成長。資料只有兩年的時候我覺得夠用，資料多了再考慮更細的方法。

預測準不準

模型好不好，要拿已知答案的資料試。方法叫回測：只用前面的資料配直線，預測後面已經發生的月份，再比對。

回到第一組 12 個月資料，用前 9 個月預測第 10 到 12 個月。前面的移動平均已經用了 C 欄，回測請把月份與營收複製到新的工作表（同樣放在 A、B 欄），再從 C11 開始放：

往下複製到 C13，預測值是 151.14、156.72、162.31，實際是 143、155、163。

← 左右滑動看完整的圖，點圖可放大

圖：10 月實際比預測低 8.14，是這次回測最大的誤差。

誤差用兩個數字衡量。MAE 是平均絕對誤差，單位跟原始資料一樣：

結果是 3.52，也就是平均差 3.52 千元。MAPE 是平均絕對百分比誤差，看的是差了幾成：

結果是 0.0241，約 2.4%。這邊用 SUMPRODUCT 是因為它會把裡面的 ABS 當成陣列一起算，直接寫 =AVERAGE(ABS(...)) 的話，普通儲存格不一定會對整個範圍運算。

測試期只有 3 個點，這兩個數字參考就好。真正要拿去做決策，我會把測試期拉長，至少抓一整年。

預測也有它的上限。突發的事件模型看不到，一旦市場環境改變，過去的直線就不適用了。我自己做預算的時候，會同時算一個偏樂觀、一個偏保守的版本，讓看的人知道這個數字有多少範圍。

練習

答案是 176.36，跟 TREND 溢出的第 15 個月一樣。

預測值要畫成圖的話，可以參考 圖表完全指南；想把樂觀和保守兩種版本並排比較，可以看 情境分析。先把資料畫成折線圖，再挑方法，試著用你自己的資料做一次吧！我是喜特先生，Mr. Sheet，我們下個教學見！