本教程解釋了最統(tǒng)一函數(shù)的語法，并顯示了如何在Excel中使用它來進行線性回歸分析。

Microsoft Excel不是一個統(tǒng)計程序，但是它確實具有許多統(tǒng)計功能。這樣的功能之一是Linest，該功能旨在執(zhí)行線性回歸分析和返回相關統(tǒng)計數(shù)據(jù)。在初學者的教程中，我們將僅在理論和基礎計算上輕輕觸摸。我們的主要重點是為您提供簡單的公式，可以輕松地為您的數(shù)據(jù)定制。

Excel Linest函數(shù) - 語法和基本用途

Linest函數(shù)計算了一條直線的統(tǒng)計信息，該直線解釋了自變量與一個或多個因變量之間的關系，并返回描述該行的數(shù)組。該功能使用最小二乘方法來找到最適合您的數(shù)據(jù)。該行的方程如下。

簡單的線性回歸方程：

多元回歸方程：

在哪里：

• Y-您要預測的因變量。
• X-您用于預測y的自變量。
• a-截距（指示線相交Y軸的位置）。
• b-斜率（表示回歸線的陡度，即y的變化速率如x變化）。

以其基本形式，Linest函數(shù)返回回歸方程的截距（a）和斜率（b）。可選地，它還可以返回回歸分析的其他統(tǒng)計信息，如本示例所示。

Linest函數(shù)語法

Excel Linest函數(shù)的語法如下：

在哪里：

• 已知_y （必需）是回歸方程中因Y值的范圍。通常，它是一個列或一行。
• 已知_x （可選）是獨立X值的范圍。如果省略，則假定它是與now_y相同的數(shù)組{1,2,3，...}。
• const （可選） - 確定應如何處理截距（常數(shù)A ）的邏輯值：
  • 如果是true或省略的，則正常計算常數(shù)A。
  • 如果為錯誤，則將常數(shù)A強加于0，并且計算斜率（ B系數(shù)）以適合y = bx。
• 統(tǒng)計（可選）是一個邏輯值，它決定是否輸出其他統(tǒng)計信息：
  • 如果為true，Linest函數(shù)將返回帶有其他回歸統(tǒng)計信息的數(shù)組。
  • 如果false或省略，Linest僅返回截距常數(shù)和坡度系數(shù)。

筆記。由于Linest返回一個值數(shù)組，因此必須通過按CTRL Shift Enter快捷方式將其作為數(shù)組公式輸入。如果以常規(guī)公式輸入，則僅返回第一個坡度系數(shù)。

Linest返回的其他統(tǒng)計數(shù)據(jù)

統(tǒng)計參數(shù)設置為TRUE指示Linest函數(shù)以返回以下統(tǒng)計信息進行回歸分析：

下圖顯示了Linest返回一系列統(tǒng)計信息的順序：

在最后三行中，＃n/a錯誤將出現(xiàn)在未填充數(shù)據(jù)的第三列和后續(xù)列中。這是Linest函數(shù)的默認行為，但是如果您想隱藏錯誤符號，請將您的Linest公式包含到IfError中，如本示例所示。

如何在Excel -Formula示例中使用Linest

Linest功能可能很棘手，尤其是對于新手而言，因為您不僅應該正確構建公式，還應正確解釋其輸出。在下面，您會發(fā)現(xiàn)一些在Excel中使用Linest公式的示例，希望有助于吸收理論知識:)

簡單的線性回歸：計算斜率和截距

為了獲取回歸線的截距和斜率，您以最簡單的形式使用Linest函數(shù)：為已知_Y的參數(shù)提供一系列因值范圍，以及已知_X參數(shù)的獨立值范圍。最后兩個參數(shù)可以設置為true或省略。

例如，在C2：C13和X值（廣告成本）中，B2：B13中的Y值（銷售編號），我們的線性回歸公式如下簡單：

=LINEST(C2:C13,B2:B13)

要在工作表中正確輸入它，請在同一行中選擇兩個相鄰的單元格，E2：f2在此示例中，鍵入公式，然后按Ctrl Shift Enter輸入以完成它。

該公式將返回第一個單元格（E2）中的斜率系數(shù)，并在第二個單元格中返回截距（F2）：

坡度約為0.52（四舍五入到小數(shù)點位置）。這意味著當x增加1時， y增加了0.52。

Y截距為負-4.99。它是x = 0時y的預期值。如果繪制在圖上，則是回歸線越過y軸的值。

將上述值提供給簡單的線性回歸方程，您將獲得以下公式，以根據(jù)廣告成本進行預測銷售編號：

y = 0.52*x - 4.99

例如，如果您在廣告上花費50美元，則應出售21雨傘：

0.52*50 - 4.99 = 21.01

也可以通過使用相應的函數(shù)或將Linest公式嵌套到索引中分別獲得斜率和截距值：

坡

=SLOPE(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),1)

截距

=INTERCEPT(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),2)

如下屏幕截圖所示，所有三個公式都產(chǎn)生相同的結果：

多個線性回歸：斜率和截距

如果您有兩個或多個自變量，請確保將它們輸入相鄰的列中，并將整個范圍提供給已知_X的參數(shù)。

例如，在d2：d13中的銷售數(shù)字（ y值）中，b2：b13中的廣告成本（一組x值）和c2：c13中的平均每月降雨（另一組x值），您使用此公式：

=LINEST(D2:D13,B2:C13)

由于公式將返回3個值的數(shù)組（2個斜率系數(shù)和截距常數(shù)），因此我們在同一行中選擇了三個連續(xù)的單元格，輸入公式，然后按CTRL Shift shipter serter快捷方式。

請注意，多個回歸公式以自變量的相反順序返回斜率系數(shù)（從右到左），即b _n ，b n，b _n-1 ，…，b ₂ ，b ₁ ：

為了預測銷售編號，我們將Linest公式返回的值提供給多重回歸方程：

y = 0.3*x ₂ 0.19* _x 1-10.74

例如，憑借50美元的廣告花費，平均每月降雨量為100毫米，預計您將出售大約23個雨傘：

0.3*50 0.19*100 - 10.74 = 23.26

簡單的線性回歸：預測因變量

除了計算回歸方程的A和B值外，Excel Linest函數(shù)還可以基于已知的自變量（x）估計因變量（y）。為此，您將Linest與總和或SUMPRODUCT函數(shù)結合使用。

例如，您可以根據(jù)上個月的銷售和10月的廣告預算50美元的銷售來計算下個月的傘銷售數(shù)量，例如10月：

=SUM(LINEST(C2:C10, B2:B10)*{50,1})

您可以將其作為單元格引用，而不是對公式中的X值進行硬編碼。在這種情況下，您也需要在某些單元格中輸入1常數(shù)，因為您不能將引用和值混合在數(shù)組常數(shù)中。

使用E2中的X值和F2中的常數(shù)1，以下兩個公式都可以享用：

常規(guī)公式（按Enter輸入）：

=SUMPRODUCT(LINEST(C2:C10, B2:B10)*(E2:F2))

數(shù)組公式（通過按Ctrl Shift Enter輸入）：

=SUM(LINEST(C2:C10, B2:B10)*(E2:F2))

為了驗證結果，您可以獲取相同數(shù)據(jù)的截距和斜率，然后使用線性回歸公式來計算y ：：

=E2*G2 F2

其中e2是斜率，g2是x值，而f2是截距：

多重回歸：預測因變量

如果您要處理幾個預測指標，則包括幾組不同的X值，包括數(shù)組常數(shù)中的所有這些預測變量。例如，廣告預算為$ 50（x ₂ ），平均每月降雨量為100毫米（x ₁ ），該公式如下：

=SUM(LINEST(D2:D10, B2:C10)*{50,100,1})

其中d2：d10是已知的y值，而b2：c10是兩組x值：

請注意陣列常數(shù)中X值的順序。如前所述，當使用Excel Linest函數(shù)進行多重回歸時，它將斜率系數(shù)從右到左返回。在我們的示例中，廣告系數(shù)首先返回，然后是降雨系數(shù)。要正確計算預測的銷售編號，您需要將系數(shù)乘以相應的X值，因此您按以下順序將數(shù)組常數(shù)的元素放在：{50,100,1}中。最后一個元素是1，因為Linest返回的最后一個值是不應更改的截距，因此您只需將其乘以1即可。

您可以在某些單元格中輸入所有X變量，而不是使用數(shù)組常數(shù)，而是像上一個示例中那樣引用公式中的這些單元格。

常規(guī)公式：

=SUMPRODUCT(LINEST(D2:D10, B2:C10)*(F2:H2))

陣列公式：

=SUM(LINEST(D2:D10, B2:C10)*(F2:H2))

其中F2和G2是X值，H2為1：

Linest公式：其他回歸統(tǒng)計數(shù)據(jù)

您可能還記得，要獲取回歸分析的更多統(tǒng)計信息，您將最新函數(shù)的最后一個論點置于真實。應用于我們的樣本數(shù)據(jù)，該公式采用以下形狀：

=LINEST(D2:D13, B2:C13, TRUE, TRUE)

由于列B和C列中有2個自變量，因此我們選擇了一個由3行（兩個X值截然）和5列組成的憤怒，輸入上述公式，按CTRL SHIFT ENTER ENTER ，并獲得此結果：

為了擺脫＃N/A錯誤，您可以像這樣筑巢到IfError中：

=IFERROR(LINEST(D2:D13, B2:C13, TRUE, TRUE), "")

下面的屏幕截圖演示了結果，并解釋了每個數(shù)字的含義：

在先前的示例中解釋了坡度系數(shù)和Y截距，因此讓我們快速了解其他統(tǒng)計數(shù)據(jù)。

確定系數(shù)（R ² ）。 r ²的值是將平方回歸總和除以總和的結果。它告訴您X變量解釋了多少個Y值。它可以是從0到1的任何數(shù)字，即0％至100％。在此示例中，r ²約為0.97，這意味著我們97％的因變量（傘銷售）是由自變量（廣告平均每月降雨）來解釋的，這是一個非常合適的！

標準錯誤。通常，這些值顯示回歸分析的精度。數(shù)字越小，您就越確定您的回歸模型。

f統(tǒng)計。您使用F統(tǒng)計量來支持或拒絕零假設。在確定總體結果是否顯著時，建議將F統(tǒng)計量與P值結合使用。

自由度（DF）。 Excel中的Linest函數(shù)返回剩余的自由度，這是DF總DF減去回歸DF 。您可以使用自由度在統(tǒng)計表中獲取F-臨界值，然后將F-臨界值與F統(tǒng)計量進行比較以確定模型的置信度。

正方形的回歸總和（又稱解釋的正方形總和或正方形的模型總和）。它是預測的y值與y的平均值之間的平方差的總和，該公式是按照此公式計算的：= ∑（? -?） ² 。它指示了您的回歸模型所解釋的因變量中有多少變化。

剩余的正方金總和。它是實際Y值與預測Y值之間平方差的總和。它表明您的模型無法解釋的因變量中有多少變化。與正方形總和相比，剩余的正方形總和越小，回歸模型越適合您的數(shù)據(jù)。

關于Linest功能，您應該知道的5件事

要在工作表中有效使用Linest公式，您可能想了解更多有關該功能的“內部力學”的信息：

1. now_y和nown_x 。在僅具有一組X變量的簡單線性回歸模型中，只要它們具有相同數(shù)量的行和列， nown_y和已知_x的范圍就可以是任何形狀的范圍。如果您使用一組以上的獨立X變量進行多個回歸分析，則已知_y必須是向量，即一排或一列的范圍。
2. 強迫常數(shù)為零。當const參數(shù)為真或省略時，計算a常數(shù)（截距）并包含在公式中：y = bx a。如果將const設置為false，則截距被認為是等于0，并從回歸方程式中省略了：y = bx。

   在統(tǒng)計數(shù)據(jù)中，數(shù)十年來一直在爭論是否有意義，是否有意義地將攔截常數(shù)為0。許多可靠的回歸分析從業(yè)人員認為，如果將截距設置為零（const = false）似乎很有用，那么線性回歸本身就是數(shù)據(jù)集的錯誤模型。其他人則認為，在某些情況下，在回歸不連續(xù)設計的背景下，常數(shù)可以被迫為零。通常，建議使用默認的const = true或在大多數(shù)情況下省略。

3. 準確性。由Linest函數(shù)計算出的回歸方程的準確性取決于數(shù)據(jù)點的分散。數(shù)據(jù)線性越多，最精確的Linest公式的結果就越準確。
4. 冗余x值。在某些情況下，一個或多個獨立X變量可能沒有其他預測值，并且從回歸模型中刪除此類變量不會影響預測Y值的準確性。這種現(xiàn)象被稱為“共線性”。 Excel Linest函數(shù)檢查共線性，并省略了其從模型中標識的任何冗余X變量。省略的X變量可以通過0系數(shù)和0標準誤差值識別。
5. Linest與斜率和截距。 Linest函數(shù)的基本算法與斜率和截距函數(shù)中使用的算法不同。因此，當源數(shù)據(jù)不確定或共線時，這些功能可能會返回不同的結果。

Excel Linest功能不起作用

如果您的Linest公式會引發(fā)錯誤或產(chǎn)生錯誤的輸出，則可能是由于以下原因之一：

1. 如果Linest函數(shù)僅返回一個數(shù)字（坡度系數(shù)），則很可能您以常規(guī)公式輸入它，而不是數(shù)組公式。請確保按CTRL Shift Enter以正確完成公式。當您執(zhí)行此操作時，公式將被封閉在公式欄中可見的{Curly Brackets}中。
2. #ref！錯誤。如果已知的_x和已知_y的范圍具有不同的維度，則會發(fā)生。
3. ＃價值！錯誤。如果已知_x或已知_y的數(shù)字至少包含一個空白單元格，文本值或文本表示，則會發(fā)生Excel無法識別為數(shù)字值的數(shù)字。同樣，如果無法評估const或stats參數(shù)為true或false，則會發(fā)生#Value錯誤。

這就是您在Excel中使用Linest進行簡單和多線性回歸分析的方式。要仔細觀察本教程中討論的公式，歡迎下面下載我們的示例工作簿。我感謝您閱讀，并希望下周在我們的博客上見到您！

練習工作簿下載

Excel Linest函數(shù)示例（.xlsx文件）

以上是Excel Linest功能與公式示例的詳細內容。更多信息請關注PHP中文網(wǎng)其他相關文章！