【進階】使用Excel進行回歸分析，預測真實值

昨天學習了Excel中的相關分析，在數據分析中，相關分析和回歸分析關係緊密，今天來學習下Excel中的回歸分析。

回歸分析（regressionanalysis)是確定兩種或兩種以上變數間相互依賴的定量關係的一種統計分析方法。

我們在得到兩組數據之間的相關程度之後，就可以使用回歸分析進行預測了，換言之，相關分析是回歸分析的基礎和前提，回歸分析是相關分析的深入和繼續。但只有當數據之間存在高度相關時，進行回歸分析尋求相關的具體形式才有意義。

在我們的日常數據分析過程中，回歸分析是應用十分廣泛的一種數據分析方法，該方法主要用於分析單個因變數是如何受到一個或多個自變數影響的。如分析某個產品的銷售情況與產品質量、價格、促銷活動、天氣等因素之間的關係。根據已知的一組數據，我們就可以知道這幾個因素對銷售額的影響，然後對同類產品的銷售額進行預測。

回歸分析的分類如下圖1所示：

實例應用：使用多元線性回歸分析預測銷售額

1、實例描述

某品牌汽車經銷商的經理了解到投放廣告對於汽車銷售額增長具有很大的作用，但是他並不明確在電視台投放廣告與在各個視頻網站投放廣告哪種方式對增加汽車銷售額更有效。在2017年1月，若在電視台和視頻網站分別投入的廣告費為20萬和30萬，那麼應估算汽車的銷售額為多少萬元？針對這種情況，經理手機本公司去年各月的汽車銷售額數據及每月在以上兩種媒介上投入的廣告費用數據，如下圖2所示。

2、實例分析

簡單根據上面的數據，我們並不能確定這兩種廣告投放渠道哪種更有效，所以，這裡我們使用Excel中的回歸分析方法，先檢驗這兩組數據與銷售額的相關性程度，隨後再根據回歸分析過程中所得到的線性回歸方程預測確定廣告費時的銷售額。

3、操作分析

使用Excel進行多元線性回歸分析的因變數是銷售額，自變數是兩種渠道的廣告費，具體步驟如下：

第1步：選擇回歸分析工具並設置參數。打開「數據分析」對話框，選擇「回歸」分析工具，單擊「確定」，如圖3所示，彈出「回歸」對話框，設置「Y值輸入區域」為「$D$2：$D$14」，「X值輸入區域」為「$B$2：$C$14」；勾選「標誌」「置信度」複選框，並設置置信度為「95%」；單擊「輸出區域」，並設置該區域為「$F$1」；單擊確定即可。如圖4所示。

第2步：顯示回歸分析結果。此時返回工作表，就能得到詳細的各項參數值。如圖5所示。

操作解析：

回歸分析的計算結果一共包括三個模塊：

（1）第一個模塊為回歸統計表，其中主要包含MultipleR、RSquare、AdjustedRSquare、標準誤差和觀測值。MultipleR為復相關係數，也就是前面說的相關係數，用來衡量x和y之間的相關程度大小，RSquare為複測定係數R2，其用來說明自變數解釋因變數變差的程度，從而測量同因變數y的擬合效果，AdjustedRSquare為調整后的複測定係數R2，標準誤差衡量擬合程度大小，值越小，說明擬合程度越好，觀測值指的是用於估計回歸方程數據的觀測值個數。

（2）第二個模塊為方差分析表。其主要作用是通過假設檢驗中的F-檢驗來判斷回歸模型的回歸效果。

（3）第三個模塊是回歸參數表。第一列表示截距，第二列表示對應模型的回歸係數，包括了截距和斜率，可以根據這個建立回歸模型。第三列為回歸係數的標準誤差，值越小，表明參數的精確度越高，第四列對應的是統計量t值，用於檢驗模型參數。第五列為各個回歸係數的P值，當P<0.05時，可以認為模型在α=0.05的水平上顯著，或置信度達到了95%。最後幾列為回歸係數置信區間的上限和下限。

4、決策分析

上面的結果中可以看到，R值為0.9863，表示廣告支出費與銷售額之間的關係為高度正相關，複測定係數為0.9727，表明用自變數可解釋因變數變差的97.27%，AdjustedRSquare為0.9666，說明自變數能說明因變數的96.66%，因變數剩餘的3.33%則由其他因素來解釋。

回歸參數表中，回歸方程的截距和兩個斜率分別為-316.29，9.13，51.06。又因為P值小於0.05，說明了這兩個自變數對汽車銷售額均有顯著影響，但是，兩個斜率中，視頻網站對應的回歸係數更大，說明在視頻網站上投放廣告更有效。

由此可得該回歸分析的線性回歸方程為：y=-316.29+9.13x1+51.06x2。

預測一下，當電視廣告費和視頻廣告費分別為20萬和30萬時，汽車銷售額的預測值為：y=-316.29+9.13*20+51.06*30=1398.11（萬）。

小結

上面就是今天的主題內容了，今天學習一下Excel中如何使用相關分析進行預測，這對數據分析很有幫助，可以看到不同渠道投放的廣告對我們銷售額的影響大小。希望通過上面的操作能幫助大家。如果你有什麼好的意見，建議，或者有不同的看法，我都希望你留言和我們進行交流、討論。