利用ecel進(jìn)行線(xiàn)性回歸分析

1、LINEST 函數(shù)通過(guò)使用“最小二乘法”計(jì)算最符合您的數(shù)據(jù)的直線(xiàn)來(lái)計(jì) 算直線(xiàn)的統(tǒng)計(jì)值，并返回描述該直線(xiàn)的 數(shù)組 。因?yàn)榇?函數(shù)返回?cái)?shù)值數(shù)組，所以必須以 數(shù)組公式 的形式輸入直線(xiàn)的公式為 y = mx + b 或 y = m1x1 + m2x2 + . + b (如果 x 值包含多個(gè)區(qū)域)，其中因變量 y 值是自變量 x值的函數(shù)。m值是與每個(gè)x指數(shù)值相對(duì)應(yīng)的系數(shù)，b 為常量值。請(qǐng)注意， y、x 和 m 可以是向量。 LINEST 函數(shù)返回的數(shù)組為 m n,m n-1,.,m 1,b。 LINEST 函數(shù) 還會(huì)返回附加回歸統(tǒng)計(jì)值。語(yǔ)法LINEST(known_ys, known_xs, const

2、, stats )以下圖示顯示了附加回歸統(tǒng)計(jì)值返回的順序?？梢允褂眯甭屎?y 軸截距描述任何直線(xiàn)：如果需要計(jì)算斜率(通常記為m),則選取直線(xiàn)上 的兩點(diǎn)(x1,y1)和(x2,y2)；斜率等于(y2 - y1)/(x2 - x1)。直線(xiàn)的y軸截距(通常記為b)為直線(xiàn)與y軸交點(diǎn) 的 y 值。 .直線(xiàn)的公式為 y = mx + b 。如果知道 m 值和 b 值， 可以將 y 值或 x 值代入公式來(lái)計(jì)算直線(xiàn)上的任意點(diǎn) 還可以使用 TREND 函數(shù)。如果只有一個(gè)自變量X,可以使用以下公式直接算出斜率和 y 軸截距值：計(jì)算斜率,使用 =INDEX(LINEST(known_ys, known_xs), 1

3、 )計(jì)算 Y 軸截距,使用 =INDEX(LINEST(known_ys, known_xs), 2 ) LINEST 計(jì)算出的直線(xiàn)的精確度取決于數(shù)據(jù)的離散 程度。數(shù)據(jù)越接近線(xiàn)性, LINEST 模型就越精確。 LINEST 使用最小二乘法確定最適合數(shù)據(jù)的直線(xiàn)。只有一個(gè)自變量 X 時(shí), m 和 b 可根據(jù)以下公式算出m =其中 X 和 y 是樣本平均值,即, X = AVERAGE(known xs) 和 y = AVERAGE(known_ys)。 直線(xiàn)擬合和曲線(xiàn)擬合函數(shù) LINEST 和 LOGEST 可計(jì) 算最適合您的數(shù)據(jù)的最佳直線(xiàn)或指數(shù)曲線(xiàn)。但是, 您必須確定兩個(gè)結(jié)果中哪個(gè)最適合您的數(shù)

4、據(jù)。可以 使用 TREND(known_ys, known_xs ) 計(jì)算直線(xiàn), 或使用 GROWTH(known_ys, known_xs ) 計(jì)算 指數(shù)曲線(xiàn)。這些不含new_xs參數(shù)的函數(shù)，返冋根 據(jù)您的實(shí)際數(shù)據(jù)點(diǎn)、沿著直線(xiàn)或曲線(xiàn)計(jì)算的一組 y 值。然后可以比較計(jì)算出的值與實(shí)際值。也可以通 過(guò)圖表方式直觀(guān)地進(jìn)行比較。在冋歸分析中，Excel計(jì)算每個(gè)點(diǎn)的y計(jì)算值與實(shí) 際值之間的平方差。這些平方差之和稱(chēng)為殘差平方 和ss resid。然后，Excel計(jì)算總平方和ss total。 當(dāng)const = TRUE或被省略時(shí)，總平方和為y的實(shí) 際值與平均值的平方差之和。當(dāng) const = FALSE

5、時(shí) 總平方和為y的實(shí)際值的平方和（不需要從每個(gè)y 值中減去平均值）。冋歸平方和 ss reg 可通過(guò)公式 算出：ss reg = ss total - ss resid。殘差平方和與總 平方和的比值越小，判定系數(shù) r 2的值就越大，該 系數(shù)是表示冋歸分析公式的結(jié)果反映變量之間關(guān)系 的程度的指標(biāo)。 r 2 = ss reg/ss total。 在某些情況下，一個(gè)或多個(gè) x 列（假設(shè) ys 和 xs位于列中）可能沒(méi)有其他x列中出現(xiàn)的計(jì)算值。換 言之，刪除一個(gè)或多個(gè)x列可能不會(huì)影響y計(jì)算值 的精確度。在這種情況下，這些多余的x列應(yīng)該從 冋歸模型中刪除。這種現(xiàn)象稱(chēng)為“共線(xiàn)”，因?yàn)槿魏?多余的x列可表示

6、為多個(gè)非多余x列之和。 LINEST將檢查是否存在共線(xiàn)，并在識(shí)別出之后從 冋歸模型中刪除任何多余的x列。由于包含0系數(shù) 和0 ses，因此LINEST輸出中能夠識(shí)別已刪除的x 列。如果一個(gè)或多個(gè)多余的列被刪除，則將影響 d f,因?yàn)閐 f取決于實(shí)際用于計(jì)算目的的x列的數(shù) 量。有關(guān)計(jì)算d f的詳細(xì)信息，請(qǐng)參閱下面的示例 4。如果由于刪除多余的x列而使d f發(fā)生更改，則 還會(huì)影響 se y 和 F 的值。實(shí)際應(yīng)用中,共線(xiàn)應(yīng)相對(duì) 極少發(fā)生。但是，當(dāng)某些x列僅包含0s和1s作 為實(shí)驗(yàn)中的對(duì)象是否屬于某個(gè)特定組的指標(biāo)時(shí),很 可能引起共線(xiàn)。如果 const = TRUE 或被刪除，則 LINEST將有效地插入所有1s的其他x列以便模 擬截距。如果某列中的 1 對(duì)應(yīng)每個(gè)男性對(duì)象，0 對(duì) 應(yīng)非男性對(duì)象，而另一中的 1 對(duì)應(yīng)每個(gè)女性對(duì)象， 0 對(duì)應(yīng)非女性對(duì)象，則后一列為多余列，因?yàn)槠渲?的項(xiàng)可通過(guò)從所有1s （由LINEST添加）的另一 列中減去“男性標(biāo)志”列中的項(xiàng)來(lái)獲得。.如果x列由于共線(xiàn)而未從模型中刪除，則df的計(jì) 算公式如下：如果 known_xs 存在 k 列且 const = TRUE 或忽略，那么 d f = n - k - 1。如果 co