1. 首頁 > 生活 > >

Excel 正態分布 怎么用excel做正態分布數據

小知識Excel 正態分布



文章插圖
Excel 正態分布 怎么用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位） 。
【Excel 正態分布 怎么用excel做正態分布數據】按照老板的收入預期，也就是15,000=0.852X+5690.875，最后可以算出X為8320.569，也就是說，如果要達到老板的預期收入，推廣渠道的成本最少應該為8320.569元 。