錦子老師

根據往年的資料或樣本資料，用Excel做銷售預測或成本分析，在各行業都有廣泛的應用。本單元講述了使用TREND函數和LINEST函數做銷售預測或成本分析的方法。

如上圖所示A欄為產品數量，B欄是對應的單個產品成本。要求計算：當生產50個產品時，相對應的成本是多少?

n  方法1：使用TREND函數。

在E2儲存格輸入公式：「=TREND(OFFSET(B1,MATCH(D2,A:A )-1,,2),OFFSET(A1,MATCH (D2,A:A)-1,,2),D2)」後，再按Enter鍵完成公式輸入。

公式說明：

Trend函數是做線性預測的函數，但本例中的A欄和B欄間並非線性關係(Y=AX+B)。所以需要分段插值。即在A欄查找到相鄰的小值和大值。如50為13~68。

MATCH(D2,A:A)-1：利用MATCH函數的模糊查找功能，找到比樣本小且最接近的值。如比50小的是13

OFFSET(B1,MATCH()-1,,2)：用OFFSET傳回最小值和最大值的所在B欄範圍。50對應到B欄的是B5:B6，同理A欄的範圍A5:A6

D2：是樣本值。本例是84

TRAND函數預測的結果是：91.6

n  方法2：利用LINEST函數

在E2儲存格輸入公式：「=D2*INDEX(LINEST($B$2:$B$8,$A$2:$A$8),1)+INDEX(LINEST ($B$2:$B$8,$A$2:$A$8),2)」後，再按Enter鍵完成公式輸入。

LINEST():如果我們知道A欄和B欄對應的線型關係式(Y=AX+B)，那麼我們可以直接把X值帶入求值。而LINEST函數可以根據兩組資料，直接取得A和B的值。如本例：LINEST($B$2:$B$8,$A$2:$A$8)可以傳回{-0.05,85.97}，其中-0.05是A，85.9是B。那麼關係式出來: Y=-0.05X+85.9

INDEX(LINEST(),1)可以取值第一個值，即A的值。同理當為2時可以取出B的值。

方法2預測的結果是：90.77824(由於預測原理不同，結果和方法不相同是正常的)