本文將說明 Microsoft Excel 中 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的 必填項目。 這是在 y = mx + b 關係中一組已知的 y 值。
- 如果 known_y 的範圍位於單一欄中，則每一欄的 known_x 都會解譯為不同的變數。
- 如果 known_y 的範圍包含在單一列中，則每一列 known_x 都會 解譯為個別變數。
known_x 選用。 這是在 y = mx +b 關係中一組已知的 x 值。
- known_x範圍可以包含一組或多組變量。 如果只使用一個變數， 則 known_y 和known_x 可以 是任何形狀的範圍，只要它們具有相等的維度即可。 如果使用多個變數， known_y 必須是向量 (，也就是說，高度為一列或寬度為一欄) 的範圍。
- 如果省略 known_x's ，則會假設它是與 known_y 大小相同的陣列 {1,2,3,...}。
const 選用。 這是指定是否強迫常數 b 等於 0 的邏輯值。
- 如果 const 為 TRUE 或省略，則會正常計算 b。
- 如果 const 為 FALSE，則 b 會設定為等於 0，並將 m 值調整為符合 y = mx。
統計資料 選用。 這是一個指定是否要傳回額外迴歸統計值的邏輯值。
- 如果 stats 為 TRUE， 則 LINEST 會傳回其他迴歸統計資料;因此，傳回的陣列為 {mn，mn-1,...,M1，B;森，森-1,...,塞1，塞布;r2，sey;F，df;ssreg，ssresid}。
- 如果 stats 為 FALSE 或省略， 則 LINEST 只會傳回 m 係數和常數 b。
額外的迴歸統計值如下：
| 統計值 | 描述 |
|---|---|
| se1,se2,...,sen | 係數 m1,m2,...,mn 的標準誤差值。 |
| seb | 當 const 為 FALSE) 時，常數 b (seb = #N/A 的標準誤差值。 |
| r2 | 決定係數。 比較 y 的估計值與實際值，其值為 0 到 1。 如果是 1，表示樣本完全相關，即 y 的估計值與實際值沒有差異。 另一方面如果決定係數是 0，迴歸方程式對預測 y 值是沒有幫助的。 如需如何計算2 的資訊，請參閱本主題稍後的「備註」。 |
| sey | y 估計值的標準誤差。 |
| F | F 統計值或 F 觀察值。 使用 F 統計值來決定自變數和因變數間的關係是否是巧合的。 |
| df | 自由度。 使用自由度協助您於統計的表格中找出 F 臨界值。 將您在表格中找到的值與 LINEST 傳回的 F 統計值相互比較，以決定此模式的信賴區間。 有關如何計算 df 的詳細資訊，請參閱本主題下的＜註解＞。 範例 4 會示範 F 及 df 的用法。 |
| ssreg | 迴歸平方和。 |
| ssresid | 剩餘平方和。 有關如何計算 ssreg 及 ssresid 的詳細資訊，請參閱本主題下的＜註解＞。 |
下圖顯示額外的迴歸統計值傳回的次序。
註解
您可用斜率和 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's) ,1)
Y 截距：
=INDEX (LINEST (known_y's，known_x's) ,2)LINEST 函數計算線的精確度取決於資料的散佈程度。 資料散佈的越具線性，LINEST 模型就越精確。 LINEST 使用最小平方法求出資料的最適合直線。 當僅有一個自變數 x 時，m 和 b 是根據以下公式計算而來：
其中 x 和 y 是樣本平均值;也就是說，x = AVERAGE (已知的 x 的) ，y = AVERAGE (known_y 的) 。線調整函數 LINEST 及曲線調整函數 LOGEST 可以計算出您資料的最適合直線或指數曲線。 不過，您必須決定哪個結果最適用於您的資料。 您可以計算直線的趨勢 (known_y，known_x 的) ，或指數曲線的成長 (known_y known_x) 。 這些函數不含 new_x引 數，會傳回沿著該線或曲線在實際資料點處預測的 y 值陣列。 然後您就可以比較預測值和實際值。 您亦可以繪成圖表以視覺比較其差異。
在迴歸分析中，Excel 會針對每個點的估計 y 值及實際 y 值之間，計算該點的平方差。 這些的平方差的總和稱為剩餘平方和，ssresid。 Excel 接著會計算總平方和，sstotal。 當 const 引數 = TRUE 或省略時，總平方和是實際 y 值與 y 值平均值之間之平方差的總和。 當 const 引數 = FALSE 時，總平方和是實際 Y 值 (的平方和，並且不減去每個個別 Y 值) 的平均 Y 值。 然後可依據 ssreg = sstotal - ssresid 求得迴歸平方和，ssreg。 與總平方和相比，殘差平方和越小，決定係數 r2 的值就越大，這是回歸分析得出的方程解釋變量之間關係的程度的指標。 r2 的值等於 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 的值計算如下：如果有 k 欄的 known_x 且const = TRUE 或省略，則 df = n – k – 1。 如果 const = FALSE，則 df = n - k。 在這兩個例子中，因為共線性而移除的每個 X 欄會造成 df 的值增加 1。
輸入陣列常數 (，例如 known_x 的) 做為引數時，請使用逗號來分隔相同列中的值，以及使用分號分隔列。 分隔字元可能依地區選項而有所不同。
請注意，如果由迴歸方程式所求得的 y 估計值超出您用以計算方程式的 y 值範圍，則該值可能不正確。
LINEST 函數中所使用的基本演算法，不同於 SLOPE 及 INTERCEPT 函數中使用的基本演算法。 當資料未確定且未共線時，這些演算法之間的差異會導致不同的結果。 例如，如果 known_y 引數的資料點為 0，而 known_x 引數的資料點為 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 則傳回機率。
範例
範例 1：斜率和 Y 截距
請在下列表格中複製範例資料，再將之貼到新 Excel 工作表中的儲存格 A1。 若要讓公式顯示結果，請選取公式，按 F2，然後再按 Enter。 如有需要，您可以調整欄寬來查看所有資料。
| 已知的 y | 已知的 x |
|---|---|
| 1 | 0 |
| 9 | 4 |
| 5 | 2 |
| 7 | 3 |
| 結果 (斜率) | 結果 (y 截距) |
| 2 | 1 |
| 公式 (儲存格 A7:B7 中的陣列公式) | |
| =LINEST(A2:A5,B2:B5,,FALSE) |
範例 2：簡單的線性迴歸
請在下列表格中複製範例資料，再將之貼到新 Excel 工作表中的儲存格 A1。 若要讓公式顯示結果，請選取公式，按 F2，然後再按 Enter。 如有需要，您可以調整欄寬來查看所有資料。
| 月 | 銷售額 |
|---|---|
| 1 | $3,100 |
| 2 | $4,500 |
| 3 | $4,400 |
| 4 | $5,400 |
| 5 | $7,500 |
| 6 | $8,100 |
| 公式 | 結果 |
| =SUM (LINEST (B1：B6， A1：A6) *{9,1}) | $11,000 |
| 根據第 1 個月到第 6 個月的銷售額計算第 9 個月的銷售額估計值。 |
範例 3：多元線性迴歸
請在下列表格中複製範例資料，再將之貼到新 Excel 工作表中的儲存格 A1。 若要讓公式顯示結果，請選取公式，按 F2，然後再按 Enter。 如有需要，您可以調整欄寬來查看所有資料。
| 樓層面積 (x1) | 辦公室間數 (x2) | 出入口數 (x3) | 屋齡 (x4) | 估定值 (y) |
|---|---|---|---|---|
| 2310 | 2 | 2 | 20 | $142,000 |
| 2333 | 2 | 2 | 12 | $144,000 |
| 2356 | 3 | 1.5 | 33 | $151,000 |
| 2379 | 3 | 2 | 43 | $150,000 |
| 2402 | 2 | 3 | 53 | $139,000 |
| 2425 | 4 | 2 | 23 | $169,000 |
| 2448 | 2 | 1.5 | 99 | $126,000 |
| 2471 | 2 | 2 | 34 | $142,900 |
| 2494 | 3 | 3 | 23 | $163,000 |
| 2517 | 4 | 4 | 55 | $169,000 |
| 2540 | 2 | 3 | 22 | $149,000 |
| -234.2371645 | ||||
| 13.26801148 | ||||
| 0.996747993 | ||||
| 459.7536742 | ||||
| 1732393319 | ||||
| 在 A19) 中輸入 (動態陣列公式的公式 | ||||
| =LINEST(E2:E12,A2:D12,TRUE,TRUE) |
範例 4 - 使用 F 和 R2 統計資料
在上述範例中，決定係數或 r2 為 0.99675， (請參閱 LINEST) 輸出中的儲存格 A17，這表示自變量與銷售價格之間存在很強的關係。 您可以使用 F 統計值來判定這樣高的 r2 是否純屬巧合。
假定事實上所觀測的變數之間沒有關係，但您已抽出了 11 個非常特殊的辦公大樓樣本資料，以致於統計分析誤判有強烈關係。 這種總結出錯誤關係的機率稱為 Alpha。
LINEST 函數輸出中的 F 及 df 值可用來評估較高 F 值的偶發機率。 F 可以與已發佈的 F 分配表格中的臨界值相比較，或是使用 Excel 中的 FDIST 函數來計算較大 F 值的偶發機率。 適當的 F 分配具有 v1 和 v2 的自由度。 如果 n 是資料點的個數且 const = TRUE 或省略，則 v1 = n – df – 1 且 v2 = df。 (如果 const = FALSE，則 v1 = n – df 且 v2 = df。) FDIST 函數搭配語法 FDIST (F，v1，v2) ，會傳回偶然出現較高 F 值的機率。 在此範例中，df = 6 (儲存格 B18) 及 F = 459.753674 (儲存格 A18)。
假定 Alpha 值為 0.05、v1 = 11 – 6 – 1 = 4 且 v2 = 6，F 的臨界等級為 4.53。 由於 F = 459.753674 比 4.53 高出許多，而這麼高的 F 值極不可能是偶發的。 (當 Alpha = 0.05 時，當 F 超過臨界水平 4.53.) 時， known_y 和known_x 之間 沒有關係的假設將被拒絕 您可以在 Excel 中使用 FDIST 函數來獲得偶然出現這麼高的 F 值的機率。 例如，FDIST(459.753674, 4, 6) = 1.37E-7 的機率極小。 您可以推斷，無論是在表格中尋找 F 臨界等級，或使用 FDIST 函數，迴歸方程式都能夠有效地預測這個區域內的辦公大樓的估價。 請記住，一定要使用在前一段落中得出的 v1 和 v2 正確值。
範例 5：計算 T 統計值
另一個假設檢定可判斷每一個斜率係數在評估範例 3 中辦公大樓的估價時是否有用。 例如，檢定屋齡係數對於統計的意義，將 -234.24 (屋齡斜率係數) 除以 13.268 (在儲存格 A15 中屋齡係數的估計標準誤差)。 以下是 T 觀察值的公式：
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
如果 t 的絕對值夠高，則可以推斷出斜率係數在評估範例 3 中，辦公大樓的估價時是有用的。 下表顯示 4 個 T 觀察值的絕對值。
查閱統計學手冊中的表格時，您可找到自由度是 6 且 Alpha = 0.05 時，雙尾檢定的 T 臨界值為 2.447。 這個臨界值也可使用 Excel 的 TINV 函數來找到。 TINV(0.05,6) = 2.447。 因為 T (17.7) 的絕對值大於 2.447，所以屋齡是估算辦公大樓價值時的重要變數。 其他每一個自變數亦可以用相同的方式檢定其個別的統計意義。 以下項目是每個自變數的 T 觀察值：
| 變數 | T 觀察值 |
|---|---|
| 樓層面積 | 5.1 |
| 辦公室間數 | 31.3 |
| 出入口個數 | 4.8 |
| 屋齡 | 17.7 |
這些數值都大於 2.447 的絕對值；因此，所有使用於迴歸方程式中的自變數在預測這個區域辦公大樓的估計價值都是很有用的。