LINEST 函數

描述

LINEST 函數使用「最小平方」法計算資料最適合的直線，以計算出該線的統計資料，然後傳回描述該線的陣列。 您也可以結合 LINEST 與其他函數來計算其他不明參數的線性模型統計資料，包括多項式、對數、指數和冪級數。 因為此函數傳回的是數值陣列，所以它必須以陣列公式的方式輸入。 本文中範例下面都有指示說明。

此直線的方程式為：

y = mx + b

-或-

y = m1x1 + m2x2 + ... + b

如果有多個範圍的 x 值，其中因變數 y 值是自變數 x 值的函數。 m 值為對應每一個 x 值的係數，而 b 則為常數值。 請注意 y、x 與 m 可以為向量。 LINEST 函數傳回的陣列為 {mn,mn-1,...,m1,b}。 LINEST 也可以傳回額外的迴歸統計值。

語法

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

LINEST 函數語法具有下列引數：

語法

• 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_y's 與 known_x's 可以是任何圖形的範圍，只要兩者有相同的維數即可。 如果使用多個變數，則 known_y's 必須是個向量 (也就是，高度為一列或寬度為一欄的範圍)。

  • 如果省略 known_x's，則假定它是與 known_y's 大小相同的陣列 {1,2,3,...}。

• const    選擇性。 這是指定是否強迫常數 b 等於 0 的邏輯值。

  • 如果 const 為 TRUE 或被省略了，b 會以正常方式計算。

  • 如果 const 為 FALSE，b 將被設定為 0，並調整 m 值使其符合 y = mx。

• stats    選擇性。 這是一個指定是否要傳回額外迴歸統計值的邏輯值。

  • 如果 stats 為 TRUE，LINEST 會 傳回額外的回歸統計值;因此，傳回的陣列為 {mn，mn-1,...,m1，b;sen，sen-1,...,se1，seb;r^2，sey;F，df;ssreg，ssresid}。

  • 如果 stats 為 FALSE 或省略，LINEST 就只會傳回 m 係數和常數 b。

    額外的迴歸統計值如下：

下圖顯示額外的迴歸統計值傳回的次序。

註解

• 您可用斜率和 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 (known_y's，known_x 的) ,1)

  Y 截距：
  =INDEX (LINEST (known_y's，known_x 的) ,2)

• LINEST 函數計算線的精確度取決於資料的散佈程度。 資料散佈的越具線性，LINEST 模型就越精確。 LINEST 使用最小平方法求出資料的最適合直線。 當僅有一個自變數 x 時，m 和 b 是根據以下公式計算而來：

  

  

  其中，x 及 y 為樣本平均數，也就是 x = AVERAGE(known x's) 及 y = AVERAGE(known_y's)。

• 線調整函數 LINEST 及曲線調整函數 LOGEST 可以計算出您資料的最適合直線或指數曲線。 不過，您必須決定哪個結果最適用於您的資料。 您可以用 TREND(known_y's,known_x's) 來計算直線，或 GROWTH(known_y's、known_x's) 來計算指數曲線。 在沒有 new_x's 引數時，這些函數會傳回沿著該線或曲線在實際的資料點所預測的 y 值陣列。 然後您就可以比較預測值和實際值。 您亦可以繪成圖表以視覺比較其差異。

• 在迴歸分析中，Excel 會針對每個點的估計 y 值及實際 y 值之間，計算該點的平方差。 這些的平方差的總和稱為剩餘平方和，ssresid。 Excel 接著會計算總平方和，sstotal。 當 const 引數 = TRUE 或省略時，總平方和為實際 y 值和平均 y 值之間平方差的總和。 當 const 引數 = False 時，總平方和則為實際 y 值 (不減去個別 y 值的平均 y 值) 的平方和。 然後可依據 ssreg = sstotal - ssresid 求得迴歸平方和，ssreg。 相對於總平方和，剩餘平方和越小，決定係數 r² 的值越大，這是一個指示器，說明回歸分析所產生的方程式如何解釋變數之間的關係。 r² 的值等於 ssreg/sstotal。

• 在大多數的例子中，一個或多個 X 欄 (假設 Y’s 及 X’s 位於欄中) 在其他 X 欄中或許沒有額外的預測值。 換句話說，排除一個或多個 X 欄可能會得到相同精確的預測 Y 值。 在該例中，多餘的 X 欄在迴歸模型中應省略。 此現象稱為「共線性」，因為任何多餘的 X 欄都可被表示為多重非多餘 X 欄的總和。 LINEST 會檢查共線性，並且在從迴歸模型中辨識出任何多餘的 X 欄時將之移除。 在 LINEST 輸出中被移除的 X 欄會以係數 0 及 0 se 值表示。 如果有一個或多個欄被當做是多餘的而遭移除，則 df 將會被影響，因為 df 是根據實際用於預測性目的之 X 欄的數量而定。 關於 df 計算的詳細資料，請參閱範例 4。 如果 df 因移除多餘的 X 欄而受到變更，也會連帶影響 sey 值及 F。 共線性在實際上應是很少見的。 然而，當某些 X 欄包括只有 0 及 1 的值做為指標 (不論在實驗中實驗對象是否為特定群組的成員) 時，就可能發生。 如果 const = TRUE 或省略，LINEST 函數會於所有 1 值實際插入額外的 X 欄以做為截距的模型。 如果您的每個實驗對象為男性時是 1，不是時為 0，以及您的每個實驗對象為女性時是 1，不是時為 0，則後欄是多餘的，因為只要從 LINEST 函數新增的所有 1 值的額外欄項目中減去「男性指標」欄位中的項目，即可取得該欄位中的項目。

• 基於共線性，在沒有任何 X 欄從模型中移除時，df 之值的計算方式如下：如果 known_x’s 有 k 個欄位，且 const = TRUE 或省略，則 df = n – k – 1。 如果 const = FALSE，則 df = n - k。 在這兩個例子中，因為共線性而移除的每個 X 欄會造成 df 的值增加 1。

• 當引數 (如 known_x's) 以常數陣列方式輸入時，請使用逗號 (,) 來分隔包含在同列中的各值，並使用分號 (;) 來分隔各列。 分隔字元可能依地區選項而有所不同。

• 請注意，如果由迴歸方程式所求得的 y 估計值超出您用以計算方程式的 y 值範圍，則該值可能不正確。

• LINEST 函數中所使用的基本演算法，不同於 SLOPE 及 INTERCEPT 函數中使用的基本演算法。 當資料未確定且未共線時，這些演算法之間的差異會導致不同的結果。 例如，如果 known_y's 引數的資料點為 0，而 known_x's 引數的資料點為 1：

  • LINEST 會傳回 0 的值。 LINEST 函數演算法是設計來傳回合理的共線資料結果，而在此情況中，至少可以找到一個答案。

  • SLOPE 與 INTERCEPT 會傳回 #DIV/0! 的錯誤。 SLOPE 和 INTERCEPT 函數的演算法是設計來尋找單一個答案，而在此情況下，可能有多個答案。

• 除了使用 LOGEST 計算其他迴歸類型的統計值之外，您可使用 LINEST 計算其他迴歸類型的範圍，只要輸入 x 和 y 變數的函數做為 LINEST 的 x 和 y 數列即可。 例如，下列公式：

  =LINEST(yvalues，xvalues^COLUMN($A:$C))

  當您只有單獨一欄 y 值和單獨一欄 x 值用來計算下列公式的立方 (順序 3 的多項式) 近似值時就可以使用：

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

  您可以調整此公式以計算其他迴歸類型，不過在某些情況下，需要調整輸出值和其他統計值。

• LINEST 函數傳回的 F 檢定值，與 FTEST 函數傳回的 F 檢定值不同。 LINEST 會傳回 F 統計值，而 FTEST 則傳回機率。

範例