LINEST函數

LINEST函數

引子：本文的部分內容摘自Excel函數官方文檔，涉及到數理統計學，有很多統計專業術語，有點復雜，有興趣的朋友可以慢慢研究。

LINEST函數返回線性回歸方程的參數。

圖1

直線的公式為：y=mx+b

或者為：

其中，m為直線的斜率，b為截距。

LINEST函數返回的數組為：

什么情況下使用LINEST函數?

LINEST函數通過使用最小二乘法計算與現有數據最佳擬合的直線，來計算某直線的統計值，返回描述此直線的數組。它能夠計算斜率、截距、斜率標準差、截距標準差、判定系數、自由度、F統計、y標準差、回歸平方和、殘差平方和。

已知直線的x值和y值，求斜率和截距

根據1月至6月的銷售額，估算第9個月的銷售額

為一個x變量求10個統計數字

為兩個x變量求統計數字

LINEST函數語法

LEN函數有4個參數，其語法如下：

LEN(known_y’s,known_x’s,const,stats)

known_y’s: 必需，滿足線性擬合直線y=mx+b的一組已知的y值。如果known_y’s對應的單元格區域在單獨一列中，則known_x’s的每一列被視為一個獨立的變量。如果known_y’s對應的單元格區域在單獨一行中，則known_x’s的每一行被視為一個獨立的變量。

known_x’s：可選，滿足線性擬合直線y=mx+b的一組已知的x值。Known_x’s對應的單元格區域可以包含一組或多組變量，如果僅使用一個變量，那么只要known_x’s和known_y’s具有相同的維數，則它們可以是任何形狀的區域；如果用到多個變量，則known_y’s必須為向量（即必須為一行或一列）。如果省略known_x’s，則假設該數組為{1,2,3,…}，其大小與known_y’s相同。

const：可選，邏輯值，用于指定是否將常量b設置為0。如果const為TRUE或省略，則b按正常計算。如果const為FALSE，那么b將被設為0并調整m值使y=mx。

stats：可選，邏輯值，用于指定是否返回附加回歸統計值。如果stats為FALSE或者省略，則LINEST函數只返回系數m和常量b。如果stats為TRUE，則LINEST函數返回附加回歸統計值，此時返回的數組為：

其中，{se1,se2,…,sen}為系數m1,m2,…,mn的標準誤差值。Seb為常量b的標準誤差值（當const為FALSE時，seb=#N/A）。r2為判定系數，y的估計算與實際值之比，范圍在0到1之間，若為1，則樣本有很好的相關性，y的估計值與實際值之間沒有差別，相反，如果判定系數為0，則回歸公式不能用來預測y值。Sey為y估計值的標準誤差。F為F統計或F觀察值，使用F統計可以判斷因變量和自變量之間是否偶爾發生過可觀察到的關系。df為自由度，用于在統計表上查找F臨界值，將從表中相到的值與LINEST函數返回的F統計值進行比較可以確定模型的置信區間。Ssreg為回歸平方和。Ssresid為殘差平方和。

回歸分析時，Excel會計算每一點的y的估計值和實際值的平方差，這些平方差稱為殘差平方和ssresid。然后，Excel會計算總平方和（sstotal）。當參數const為TRUE或被省略時，總平方和是y 的實際值和平均值的平方差之和；當參數const為FALSE時，總平方和是y的實際值的平方和（不需要從每個y值中減去平均值）。回歸平方和（ssreg）可通過公式ssreg=sstotal-ssresid計算出來。殘差平方和與總平方和的比值越小，判定系數r2的值就越大。r2是用來判斷從回歸分析求得的公式是否足以說明變量之間關系的指示器，r2=ssreg/sstotal。

下圖2顯示了附加回歸統計值返回的順序。

圖2

當只有一個自變量x時，可直接利用下面公式得到斜率和截距值：

斜率：=INDEX(LINEST(known_y’s,known_x’s),1)

截距：=INDEX(LINEST(known_y’s,known_x’s),2)

LINEST函數陷阱

LINEST函數返回數值數線，因此必須以數組公式的形式輸入。

數據的離散程度決定了LINEST函數計算的直線的精確度。數據越接近線性，LINEST模型就越精確。LINEST函數使用最小二乘法來判斷數據的最佳擬合。當只有一個自變量x時，m和b是根據下面的公式計算的：

LINEST函數中使用的底層算法與SLOPE函數和INTERCEPT函數使用的底層算法不同。

LINEST函數返回的F檢驗值與FTEST函數返回的F檢驗值不同。FINEST函數返回F統計值，而FTEST返回概率。

示例1: 已知直線的x值和y值，求斜率和截距

如圖3所示，在單元格區域C5:C8中是直線y=mx+b的y值，單元格區域B5:B8中是該直線的x值。選擇單元格B12:C12，輸入數組公式：

=LINEST(B5:B8,C5:C8,,FALSE)

得到該直線的斜率m和截距b。

圖3

示例2: 根據1月至6月的銷售額，估算第9個月的銷售額

如圖4所示的工作表，列出了1月到6月的銷售額，現在據此估算9月份的銷售額，公式為：

=SUM(LINEST(B2:B7,A2:A7)*{9,1})

圖4

示例3: 為一個x變量求10個統計數字

如下圖5所示的工作表，在工作表單元格區域A2:B12中是統計數據，在工作表單元格區域E3:F7中使用數組公式：

=LINEST(B2:B12,A2:A12,TRUE,TRUE)

得到了關于統計數據的10個統計量。

圖5

示例4: 為兩個x變量求統計數字

如下圖6所示，LINEST函數可以對兩個x變量求統計數字。數據區域為單元格區域A2:C12，求解得到的結果區域為F4:E8，數組公式為：

=IFERROR(LINEST(C2:C12,A2:C12,TRUE,TRUE),””)

使用IFERROR函數避免出現#N/A錯誤值。

圖6

LINEST函數的作用還有很多，例如，還可以對多個x變量求統計數字，本文只是列舉了一些簡單的例子。

由于對統計學專業知識了解有限，文中可能有錯漏之處，請相關專業專家指正。

期待以后統計學知識學得更好一些后，再分享。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。