如何使用Excel在歷史資料的基礎上預測業務未來的發展？
如果你是公司營銷部/市場部的一員，老闆希望在推廣渠道投入8，000元以達到15，000元的收入，你覺得這些錢夠嗎？
在這樣一個案例中，如能判斷這些錢夠不夠？一個常用的方法是使用資料分析中的迴歸分析預測。
迴歸分析是在我們業務歷史資料的基礎上，根據資料之間的相關關係搭建模型來預測未來發展的趨勢。
今天，我們來學習一下如何使用Excel來進行資料分析的迴歸分析。
什麼是迴歸分析
迴歸分析的本質是研究資料與資料之間的相關關係。
在統計學中，迴歸分析的種類繁多，但是在實際中，最常用的還是一元/多元線性迴歸，也就是研究一個或多個因素與另外一個因素的關係。
那些能引起其他因素變化的就是自變數，根據其他因素變化而變化的則是因變數。在上面的案例中，銷售額收入隨著投入成本的變化而變化，那麼銷售額就是因變數，成本則是自變數。
在這個基礎上建立的迴歸模型：Y（因變數）=a1X1（自變數1）+a2X2（自變數2）+a3X3（自變數3）+……+an Xn+b（一共有n個自變數，a1、a2、a3。。。an是未知的係數，b是誤差）
瞭解迴歸分析的基礎概念後，我們下面來說說使用Excel對資料進行迴歸分析。
迴歸分析的步驟
1.明確問題與目的
進行任何資料分析之前，第一件事永遠是確定我們面臨的問題和使用資料分析的目的。
在上面的案例中，我們遇到的問題是投入多少成本，才能達到老闆想要的收益。而資料分析的目的，則是預測達到這些收益所需要投入的成本。
明確目的後，就是整理資料。我們要按照時間順序把過去某段時間所有投入的成本和總收入進行歸納整理，歷史資料越多，預測就越準確。
我們根據歷史資料整理瞭如下表格：
2.確定自變數和因變數
在一次迴歸分析中，自變數可以有多個，但因變數只能有一個。
在我們的案例中，這次的因變數是銷售額Y，而在只考慮推廣渠道成本的情況下，自變數只有一個X，所以這次搭建的模型為：Y=aX+b
3.載入Excel資料分析功能
Excel的資料分析功能需要另外載入。它的具體位置在檔案-選項-載入項，在載入頁面勾選“分析工具庫”後，點選下面的“轉到”後再點選確定即可。
這時候，我們會發現在Excel資料功能欄下方的右側多出了一個“資料分析”按鈕。
4.進行迴歸分析，建立模型
載入完資料分析功能後，點選按鈕後，在彈窗中找到“迴歸”並選擇。
這時候，會彈出匯入資料的視窗。其中Y值就是因變數，X值是自變數。
點選右側的“⇧”後，長按滑鼠左鍵從第一行的資料拖動到最後一行，再按下Enter鍵即可快速匯入。Y值只能選擇一列的資料，而X值可以同時選擇多列。
這裡我們只需要選中“推廣渠道成本”一欄的資料即可。
如下圖所示。
匯入資料後，勾選下方“殘差”和“正態分佈”所有選項，點選確定即可進行迴歸分析。
▲ 迴歸分析後的頁面
可以看到Excel自動生成的模型圖表：
我們要關注的重點在中間“Coefficients”一欄中。其中，Intercept是指X=0時，Y的值，也就是誤差b；而“X Variable”則是指X的係數，也就a。
那麼這個模型的具體資料就出來了：Y=0。852X+5690。875（可以根據你們的需要取小數點後3-4位數，也可以直接使用這個資料）
5.對迴歸模型進行檢驗
模型搭建好了，並不是萬無一失的。
既然是預測，肯定會與真實資料有誤差。我們也可以觀察到在模型的散點圖上，並非所有真實資料全部都在這條模型線上，因為它是根據資料“擬合”的，並不是完全符合。
所以我們還需要對模型進行檢驗。一般來說，檢驗模型的準確率需要考察這幾個數字：
Multiple R（相關係數）
相關係數R的值出於-1~1之間，在-1~0的區間中，說明自變數越大，因變數就越小，而在0~1的區間中，則相反。
當相關係數R小於-0。8或大於0。8的時候，說明自變數與因變數有很強的相關性。這裡的相關係數高達0。983，說明成本與銷售額有非常強的相關性。
R Square（擬合係數）
擬合係數=相關係數的平方，它的數值範圍為0~1，數值越大，擬合的效果就越好。一般大於0。7時，可以認為擬合的效果符合預期。
這裡的擬合係數為0。96，非常接近1，說明模型的擬合效果很不錯。
Significance F（顯著性檢驗）
顯著性檢驗是指檢驗自變數和因變數的線性關係是否明顯，它的數值越小，說明真實資料離這條線越近，也就是模型越準確。
這裡的1。25E-08的意思是1。25× 10的-8次方（即0。00000001），幾乎接近於0，也就是說這個模型是比較準確的。
P-value（P值）
P值是用來檢測係數（即aX中的a）的顯著程度。P值同樣也是越小越好，一般來說，P值小於0。05時，這個模型才有統計學意義。
這裡的P值為2。4E-09，也是無限接近於0，說明這個模型的可信度比較高。
6.預測
檢驗完迴歸模型後，如果各數值都比較正常，那麼就可以根據最終確定的模型進行預測了。
在上面的案例中，最終確定的模型為：Y=0。852X+5690。875（這裡取小數點後3位）。
按照老闆的收入預期，也就是15，000=0。852X+5690。875，最後可以算出X為8320。569，也就是說，如果要達到老闆的預期收入，推廣渠道的成本最少應該為8320。569元。
關注公號“海綿職場圈”，一起高效工作，快樂摸魚！
