Excel - LINEST 函式

描述

LINEST 函式使用“最小二乘法”計算最佳擬合數據的直線統計資料，然後返回描述該直線的陣列。

您還可以將 LINEST 與其他函式結合使用，以計算其他型別的線性未知引數模型的統計資料，包括多項式、對數、指數和冪級數。

由於此函式返回一個值陣列，因此必須將其作為陣列公式輸入。

語法

LINEST (known_y's, [known_x's], [const], [stats])

引數

引數	描述	必填/可選
已知_y值	您已在關係式 y = mx + b 中知道的 y 值集合。如果已知_y值範圍位於單列中，則已知_x值的每一列都被解釋為一個單獨的變數。如果已知_y值範圍位於單行中，則已知_x值的每一行都被解釋為一個單獨的變數。	必填
已知_x值	您可能已在關係式 y = mx + b 中知道的 x 值集合。已知_x值範圍可以包含一個或多個變數集。如果只使用一個變數，則已知_y值和已知_x值可以是任何形狀的範圍，只要它們具有相同的維度。如果使用多個變數，則已知_y值必須是向量（即，高度為一行或寬度為一列的範圍）。如果省略已知_x值，則假定它與已知_y值大小相同的陣列 {1,2,3,...}	可選
常數	指定是否強制常數 b 等於 0 的邏輯值。如果常數為 TRUE 或省略，則 b 按常規計算。如果常數為 FALSE，則 b 設定為 0，並且調整 m 值以適合 y = mx。	可選
統計資料	指定是否返回附加回歸統計資料的邏輯值。如果統計資料為 TRUE，則 LINEST 返回附加回歸統計資料。結果，返回的陣列為 {mn, mn-1 ,..., m1, b; sen ,sen-1, ..., se1, seb; r2, sey; F, df; ssreg, ssresid}。如果統計資料為 FALSE 或省略，則 LINEST 僅返回 m 係數和常數 b。附加回歸統計資料如以下表格所示。	可選

附加回歸統計資料

序號	統計資料和說明
1	se1,se2,...,sen 係數 m1,m2,...,mn 的標準誤差值。
2	seb 常數 b 的標準誤差值 (當常數為 FALSE 時，seb = #N/A)。
3	r2 決定係數。比較估計的和實際的 y 值，其值範圍為 0 到 1。如果為 1，則樣本中存在完美的相關性——估計的 y 值與實際 y 值之間沒有差異。在另一個極端，如果決定係數為 0，則迴歸方程對預測 y 值沒有幫助。有關如何計算 r2 的資訊，請參見下面的註釋。
4	sey y 估計的標準誤差。
5	F F 統計量或 F 觀測值。使用 F 統計量確定因變數和自變數之間觀察到的關係是否偶然發生。
6	df 自由度。使用自由度可以幫助您在統計表中查詢 F 臨界值。將您在表中找到的值與 LINEST 返回的 F 統計量進行比較，以確定模型的置信水平。有關如何計算 df 的資訊，請參見下面的註釋。
7	ssreg 迴歸平方和。
8	ssreg 殘差平方和。有關如何計算 ssreg 和 ssresid 的資訊，請參見下面的註釋。

註釋

線的方程式為：

y = mx + b

或

y = m1x1 + m2x2 + ... + b
如果存在多個 x 值範圍，其中因變數 y 值是自變數 x 值的函式，則：
- m 值是對應於每個 x 值的係數，b 是一個常數值。
- 請注意，y、x 和 m 可以是向量。
LINEST 函式返回的陣列為 {mn, mn-1… m1, b}。
LINEST 還可以返回其他迴歸統計資料
您可以使用斜率和 y 截距來描述任何直線：
- 斜率 (m)：
  
  要找到直線的斜率（通常寫為 m），請取直線上的兩點 (x1,y1) 和 (x2,y2)。斜率等於
  
  (y2 - y1)/(x2 - x1)。
- Y 截距 (b)：
  
  直線的 y 截距（通常寫為 b）是直線與 y 軸相交點的 y 值。

直線的方程為 y = mx + b。一旦知道 m 和 b 的值，就可以透過將 y 值或 x 值代入該方程來計算線上的任何點。您還可以使用 TREND 函式。
當您只有一個自變數 x 時，您可以使用以下公式直接獲得斜率和 y 截距值：
- 斜率：
  
  =INDEX(LINEST(已知_y值,已知_x值),1)
- Y 截距：
  
  =INDEX(LINEST(已知_y值,已知_x值),2)
LINEST 函式計算出的直線的精度取決於資料的分散程度。資料越線性，LINEST 模型就越準確。
LINEST 使用最小二乘法確定資料的最佳擬合。當您只有一個自變數 x 時，m 和 b 的計算基於以下公式：

$$m=\frac{\sum \left ( x-\bar{x} \right )\left ( y-\bar{y} \right )}{\sum \left ( x- \bar{x}\right )^2}$$

其中 x 和 y 是樣本均值，即

x = AVERAGE(已知_x值)

y = AVERAGE(已知_y值)
線性和曲線擬合函式 LINEST 和 LOGEST 可以計算最適合資料的最佳直線或指數曲線。但是，您必須確定哪兩個結果最適合您的資料。您可以為直線計算 TREND(已知_y值,已知_x值)，或為指數曲線計算 GROWTH(已知_y值,已知_x值)。這些函式（省略已知_x值引數）返回沿該直線或曲線在實際資料點預測的一組 y 值。然後，您可以將預測值與實際值進行比較。您可能希望將兩者都繪製成圖表以進行視覺比較。
在迴歸分析中，Excel 為每個點計算估計點與其實際 y 值之間的平方差。這些平方差之和稱為殘差平方和，ssresid。然後，Excel 計算總平方和，sstotal。當常數引數 = TRUE 或省略時，總平方和是實際 y 值與 y 值平均值之間的平方差之和。
當常數引數 = FALSE 時，總平方和是實際 y 值的平方和（不從每個單獨的 y 值中減去平均 y 值）。然後，可以從以下公式中找到迴歸平方和，ssreg：ssreg = sstotal - ssresid。殘差平方和與總平方和相比越小，決定係數 r2 的值就越大，r2 是迴歸分析得到的方程解釋變數之間關係好壞的指標。r2 的值等於 ssreg/sstotal。
在某些情況下，一個或多個 X 列（假設 Y 和 X 在列中）在其他 X 列存在的情況下可能沒有額外的預測值。即，消除一個或多個 X 列可能會導致預測的 Y 值同樣準確。在這種情況下，應從迴歸模型中省略這些冗餘的 X 列。這種現象稱為“共線性”，因為任何冗餘的 X 列都可以表示為非冗餘 X 列倍數的總和。
LINEST 函式檢查共線性，並在識別冗餘的 X 列時將其從迴歸模型中刪除。刪除的 X 列在 LINEST 輸出中可以識別為除了 0 se 值之外還具有 0 係數。如果一個或多個列被刪除為冗餘，則 df 會受到影響，因為 df 取決於實際用於預測目的的 X 列的數量。
如果由於刪除了冗餘的 X 列而更改了 df，則 sey 和 F 的值也會受到影響。在實踐中，共線性應該相對較少見。但是，更有可能出現共線性的一種情況是，某些 X 列僅包含 0 和 1 值，作為實驗物件是否屬於特定組的指示符。如果 const = TRUE 或被省略，則 LINEST 函式有效地插入一個包含所有 1 值的附加 X 列來模擬截距。
當存在 k 列已知_x值並且由於共線性沒有從模型中刪除任何 X 列時，df 的值如下計算：
- 如果常數 = TRUE 或省略，則 df = n – k – 1
- 如果常數 = FALSE，則 df = n – k
在這兩種情況下，由於共線性而刪除的每個 X 列都會使 df 的值增加 1。
當輸入陣列常量（如已知_x值）作為引數時，請使用逗號分隔同一行中包含的值，並使用分號分隔行。分隔符字元可能因您的區域設定而異。
請注意，如果迴歸方程預測的 y 值超出了用於確定方程的 y 值範圍，則這些值可能無效。
LINEST 函式中使用的底層演算法與 SLOPE 和 INTERCEPT 函式中使用的底層演算法不同。當資料未確定且共線性時，這些演算法之間的差異會導致不同的結果。
除了使用LOGEST計算其他迴歸型別的統計資料外，還可以使用LINEST透過將x和y變數的函式作為LINEST的x和y序列來計算一系列其他迴歸型別。例如，以下公式：

=LINEST (y值, x值^COLUMN($A:$C))

當您有一列y值和一列x值需要計算三次（3階多項式）近似值時，此公式有效：

y = m1*x + m2*x^2 + m3*x^3 + b

您可以調整此公式來計算其他型別的迴歸，但在某些情況下，它需要調整輸出值和其他統計資料。
LINEST函式返回的F檢驗值與FTEST函式返回的F檢驗值不同。LINEST返回F統計量，而FTEST返回機率。
如果已知x陣列的長度與已知y陣列的長度不同，LINEST將返回#REF!錯誤值。
如果提供的已知x或已知y陣列中的任何值不是數字（這可能包括數字的文字表示形式，因為LINEST函式不將這些識別為數字），LINEST將返回#VALUE!錯誤值。
如果const或stats引數都不能計算為TRUE或FALSE，LINEST將返回#VALUE!錯誤值。

適用性

Excel 2007、Excel 2010、Excel 2013、Excel 2016

示例

advanced_excel_statistical_functions.htm

列印頁面