excel預測分析怎麼用-tft每日頭條

前面在《數據分析方法論及案例分享》中有提到回歸分析，它主要用來探究：哪些維度會影響現狀指标。

在數據量不大的情況下，我們可以直接用excel來進行回歸分析。畢竟，相對于SPSS、Python來說，excel的獲得和操作真的容易很多。

回歸分析，分為線性回歸、非線性回歸。這次先以線性回歸為例，分享一下如何用excel進行分析，以及如何解讀分析結果。

【如何調出excel中的回歸分析工具】

在工具欄的左上角，點擊“文件”

excel預測分析怎麼用（用excel輕松找出影響因素）1

點擊文件

将左側導航欄拉到底部，點擊“選項”

excel預測分析怎麼用（用excel輕松找出影響因素）2

點擊選項

在彈出的面闆上，點擊“加載項”

excel預測分析怎麼用（用excel輕松找出影響因素）3

點擊加載項

在加載項面闆上，點擊“轉到（G）”

excel預測分析怎麼用（用excel輕松找出影響因素）4

點擊轉到

在可用加載宏列表中，勾選“分析工具庫”，并點擊“确定”

excel預測分析怎麼用（用excel輕松找出影響因素）5

勾選分析工具庫

回到第一步的工具欄，點擊“數據”，在分析欄裡就出現了“數據分析”工具

excel預測分析怎麼用（用excel輕松找出影響因素）6

點擊數據

【如何使用回歸分析工具】

接下來，我通過一個具體的案例進行操作和解讀。

1.導入要分析的數據列表，确定分析目的。

（1）數據列表的字段有“數據爬取時間”“銷量排名”“價格”“評論條數”“評分”“時間”，這是從某個電商平台上每天爬取的商品相關數據（共547天）：這個商品在547天中每天變化的銷量排名、價格、評論條數、評分等數據。

excel預測分析怎麼用（用excel輕松找出影響因素）7

商品相關數據

（2）本次回歸主要想探究：價格、評論條數、評分、時間這些維度對銷量排名的影響程度，哪些維度是真正有影響的因素；進一步找到預測未來變化的回歸公式，即Y=β0 β1X。

2.點擊“數據分析”，在彈出的面闆上選擇“回歸”，并點擊“确定”。

excel預測分析怎麼用（用excel輕松找出影響因素）8

選擇回歸

3.在回歸參數設置面闆上，輸入因變量Y，即“銷量排名”；輸入自變量X，即“價格”“評論條數”“評分”“時間”。因為我把列名也劃進變量區域了，所以要勾選“标志（L）”。置信度一般默認95%。

excel預測分析怎麼用（用excel輕松找出影響因素）9

設置參數

4.點擊“确定”，就會生成如下分析結論：

excel預測分析怎麼用（用excel輕松找出影響因素）10

分析結論

【如何解讀線性回歸分析結論】

從案例上看，excel共輸出了三組結果，前兩組是針對整體，即把所有自變量當作一個整體，第三組是針對個體，即每個自變量和因變量的關系。

excel預測分析怎麼用（用excel輕松找出影響因素）11

解讀分析結論

1.看整體

（1）R Square，即R2，表示因變量Y出現的變化，可以被所有自變量整體X解釋的比例。在線性的情況下，R2的值越大越好。如上圖所示，R2=0.4057，意味着有40.57%的“銷量排名”變化可以被“價格”“評論條數”“評分”“時間”共同解釋。因為列表中的數據隻是樣本，且自變量個數較多，所以看解釋性可以用Adjusted R Square（矯正後的R2）。

擴展知識：R2=1-（RSS/TSS），RSS=e12 e22 e32 …… en2（取最小化的RSS），ei=真實樣本的Y值-回歸線的Y值，TSS=（真實樣本的Y值-真實樣本的Y的均值）2。

Adjusted R2=1-[(n-1)(1-R2)/(n-p-1)]，n是樣本數量，p是變量個數。

（2）F，表示因變量Y和所有自變量整體X的關聯程度，越大越存在關聯。但并不意味着Y跟任一X都有關聯。

擴展知識：F=[(TSS-RSS)/p]/[RSS*(n-p-1)]

2.看個體

（1）P-value，越接近0，相關性越顯著。如上圖所示，價格、時間、評論條數、評分的P值都很接近0，說明每個自變量對銷量排名都顯著相關。

（2）t Stat，絕對值越大，相關性越顯著。正負符号代表相關的方向。如上圖所示，價格和評論條數的t值大于0，說明價格越高，評論條數越多，那麼銷量排名就越靠後；評分和時間的t值大于0，說明時間越久，評分越高，那麼銷量排名就越靠前。

3.預測未來變化的回歸公式

這個案例的線性回歸公式，結構應該是Y=β0 β1X價格 β2X評論條數 β3X評分 β4X時間。隻要求出β系數的值，就能獲得公式。

excel預測分析怎麼用（用excel輕松找出影響因素）12

回歸公式的β系數

Coefficients，表示系數列，其中Intercept是常數項系數，即β0；其餘變量的系數，對照Coefficients值，可以自行對号入座。

【如何提升線性回歸的解釋性】

案例的Adjusted R2=40.14%，這個解釋性并不強，其中一個原因是“銷量排名”的數據變化并不是線性的，因為排名數據是相對的，而其他自變量數據都是絕對的。所以要提升解釋性，可以先把幾個變量轉變成線性相關。

1.那麼如何轉變成線性相關呢？有個辦法就是将所有變量自然對數化，在excel中就是使用ln函數。這樣可以獲得變量間的相對百分比變化，比如價格提高百分之一，銷量排名變化了百分之幾。

excel預測分析怎麼用（用excel輕松找出影響因素）13

LN函數

2.用新的LN變量進行回歸分析，操作步驟就不重複了，直接看分析結果：

excel預測分析怎麼用（用excel輕松找出影響因素）14

提升解釋性

我們發現LN後，Adjusted R2=53.54%，提升了10個百分點，說明自變量整體對因變量變化的解釋性确實更強了。同時，評論條數的P值不再接近0，不再顯著相關，說明評論條數增加，并不怎麼影響銷量排名。

以上是關于回歸分析學習筆記的分享，在踐行費曼學習法的同時，也希望能幫助對數據分析有興趣的同學~