LINEST函式 使用最小二乘法對已知數據進行最佳直線擬合，並返回描述此直線的數組。函式返回數值必須以數組公式的形式輸入。 LINEST 函式還可返回附加回歸統計值。
基本介紹
- 中文名：LINEST函式
- 概述 ：LINEST函式 使用最小
- 含義：使用最小二乘法對已知數
- 語法：LINEST(known_y's,known
含義,語法,說明,示例,斜率和截距,簡單線性回歸,多重線性回歸,計算 T 統計,
含義
使用最小二乘法對已知數據進行最佳直線擬合，並返回描述此直線的數組。因為此函式返回數值數組，所以必須以數組公式的形式輸入。
直線的公式為：
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)
Known_y's 是關係表達式 y = mx + b 中已知的 y 值集合。
· 如果數組 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，則假設該數組為 {1,2,3,...}，其大小與 known_y's 相同。
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;r2,sey;F,df;ssreg,ssresid}。
· 如果 stats 為 FALSE 或省略，LINEST 函式只返回係數 m 和常量 b。
附加回歸統計值如下：
統計值 | 說明 |
se1,se2,...,sen | 係數m1,m2,...,mn的標準誤差值。 |
Seb | 常量b的標準誤差值（當const為FALSE時，seb=#N/A） |
r2 | 判定係數。Y的估計值與實際值之比，範圍在0到1之間。如果為1，則樣本有很好的相關性，Y的估計值與實際值之間沒有差別。如果判定係數為0，則回歸公式不能用來預測Y值。有關計算r2的方法的詳細信息，請參閱本主題後面的“說明”。 |
sey | Y估計值的標準誤差。 |
F | F統計或F觀察值。使用F統計可以判斷因變數和自變數之間是否偶爾發生過可觀察到的關係。 |
df | 自由度。用於在統計表上查找F臨界值。所查得的值和LINEST函式返回的F統計值的比值可用來判斷模型的置信度。 |
ssreg | 回歸平方和。 |
ssresid | 殘差平方和。 |
說明
· 可以使用斜率和 y 軸截距描述任何直線：
斜率 (m)：
通常記為 m，如果需要計算斜率，則選取直線上的兩點，(x1,y1) 和 (x2,y2)；斜率等於 (y2 - y1)/(x2 - x1)。
Y 軸截距 (b)：
通常記為 b，直線的 y 軸的截距為直線通過 y 軸時與 y 軸交點的數值。
直線的公式為 y = mx + b。如果知道了 m 和 b 的值，將 y 或 x 的值代入公式就可計算出直線上的任意一點。
· 當只有一個自變數 x 時，可直接利用下面公式得到斜率和 y 軸截距值：
斜率：
=INDEX(LINEST(known_y's,known_x's),1)
Y 軸截距：
=INDEX(LINEST(known_y's,known_x's),2)
· 直線和 LINEST 可用來計算與給定數據擬合程度最高的直線。這些不帶參數 new_x's 的函式可在實際數據點上根據直線來返回 y 的數組值，然後可以將預測值與實際值進行比較。還可以用圖表方式來直觀地比較二者。
· 回歸分析時，WPS表格 計算每一點的 y 的估計值和實際值的平方差。這些平方差之和稱為殘差平方和。然後 WPS表格 計算 y 的實際值和平均值的平方差之和。稱為總平方和（回歸平方和 + 殘差平方和）。殘差平方和與總平方和的比值越小，判定係數 r2 的值就越大，r2 是表示回歸分析公式的結果反映變數間關係的程度的標誌。
· 對於返回結果為數組的公式，必須以數組公式的形式輸入。
· 當需要輸入一個數組常量（如 known_x's）作為參數時，以逗號作為同一行中數據的分隔設定，以分號作為不同行數據的分隔設定。分隔設定可能因“區域設定”中或“控制臺”的“區域選項”中區域設定的不同而有所不同。
注意，如果 y 的回歸分析預測值超出了用來計算公式的 y 值的範圍，它們可能是無效的。
如果您將示例複製到空白工作表中，可能會更易於理解該示例。
示例
斜率和截距
A | B | |
1 | 已知 y | 已知 x |
2 | 1 | 0 |
3 | 9 | 4 |
4 | 5 | 2 |
5 | 6 | 3 |
公式 | 說明（結果） |
=LINEST(A2:A5,B2:B5,,FALSE) | 返回斜率(2) |
=INDEX(LINEST(A2:A5,B2:B5,,FALSE),2) | 返回截距(1) |
提示 示例中的公式也可以以數組公式輸入。在將公式複製到一張空白工作表的A7單元格後，選擇以公式單元格開始的區域 A7:B7。按 F2，再按 Ctrl+Shift+Enter。
簡單線性回歸
A | B | |
1 | 月 | 銷售 |
2 | 1 | 3100 |
3 | 2 | 4500 |
4 | 3 | 4400 |
5 | 4 | 5400 |
6 | 5 | 7500 |
7 | 6 | 8100 |
公式 | 說明（結果） |
=SUM(LINEST(B2:B7,A2:A7)*{9,1}) | 估算第9個月的銷售值(11000) |
通常，SUM({m,b}*{x,1}) 等於 mx + b，即給定 x 值的 y 的估計值。
多重線性回歸
假設有開發商正在考慮購買商業區裡的一組小型辦公樓。
開發商可以根據下列變數，採用多重線性回歸的方法來估算給定地區內的辦公樓的價值。
變數 | 代表 |
y | 辦公樓的評估值 |
x1 | 底層面積（平方英尺） |
x2 | 辦公室的個數 |
x3 | 入口個數 |
x4 | 辦公樓的使用年數 |
本示例假設在自變數（x1、x2、x3 和 x4）和因變數 (y) 之間存線上性關係。其中 y 是辦公樓的價值。
開發商從 1,500 個可選的辦公樓里隨機選擇了 11 個辦公樓作為樣本，得到下列數據。“半個入口”指的是運輸專用入口。
A | B | C | D | E | |
1 | 底層面積(x1) | 辦公室的個數(x2) | 入口個數(x3) | 辦公樓的使用年數(x4) | 辦公樓的評估值(y) |
2 | 2310 | 2 | 2 | 20 | 142,000 |
3 | 2333 | 3 | 2 | 12 | 144,000 |
4 | 2356 | 4 | 1.5 | 33 | 151,000 |
5 | 2379 | 4 | 2 | 43 | 150,000 |
6 | 2402 | 2 | 3 | 53 | 139,000 |
7 | 2425 | 4 | 2 | 23 | 169,000 |
8 | 2448 | 2 | 1.5 | 99 | 126,000 |
9 | 2471 | 2 | 2 | 34 | 142,900 |
10 | 2494 | 3 | 3 | 23 | 163,000 |
11 | 2517 | 4 | 4 | 55 | 169,000 |
12 | 2540 | 2 | 3 | 22 | 149,000 |
公式 |
=LINEST(E2:E12,A2:D12,TRUE,TRUE) |
注意 示例中的公式必須以數組公式輸入。在將公式複製到一張空白工作表後，選擇以公式單元格開始的區域 A14:E18。按 F2，再按 Ctrl+Shift+Enter。如果公式不是以數組公式輸入，則返回單個結果值 -234.2371645。
計算 T 統計
另一個假設檢驗可以檢驗示例中的每個斜率係數是否可以用來估算示例 3 中的辦公樓的評估價值。例如，如果要檢驗年數係數的統計顯著水平，用 13.268（單元格 A15 里的年數係數的估算標準誤差）去除 -234.24（年數斜率係數）。下面是 T 觀察值：
t = m4 ÷ se4 = -234.24 ÷ 13.268 = -17.7
如果查閱統計手冊里的表格，將會發現：單尾、自由度為 6、Alpha = 0.05 的 t 臨界值為 1.94。既然 t 的絕對值為 17.7，大於 1.94，則年數對於估算辦公樓的評估價值來說是一個顯著變數。用同樣方法，可以測試自變數的統計顯著水平。下面是每個自變數的 t 觀察值。
變數 | t觀察值 |
底層面積 | 5.1 |
辦公室個數 | 31.3 |
入口個數 | 4.8 |
使用年數 | 17.7 |
這些值的絕對值都大於 1.94；因此，回歸公式的所有變數都可用來估算區域內的辦公樓的評估價值。
