|
介紹統(tǒng)計(jì)學(xué)中的一元和多元線性回歸，并通過(guò)EXCEL自帶的統(tǒng)計(jì)函數(shù)LINEST、INDEX進(jìn)行手工計(jì)算，再通過(guò)EXCEL數(shù)據(jù)分析工具包進(jìn)行自動(dòng)計(jì)算。 由于很多復(fù)雜的EXCEL自動(dòng)化程序，需要用到自動(dòng)化計(jì)算，EXCEL數(shù)據(jù)分析工具并不適用自動(dòng)計(jì)算，反而EXCEL統(tǒng)計(jì)函數(shù)是很容易實(shí)現(xiàn)批量自動(dòng)計(jì)算。 所以本文重點(diǎn)介紹EXCEL統(tǒng)計(jì)函數(shù)的使用。 統(tǒng)計(jì)學(xué)上的線性回歸原理簡(jiǎn)介回歸是研究一個(gè)隨機(jī)變量y對(duì)另一個(gè)(x)或一組(x1，x2，…，xn)變量的相依關(guān)系的統(tǒng)計(jì)分析方法。其中y又叫因變量，x叫自變量。 簡(jiǎn)單的記憶方法：x是自身可以變動(dòng)的，y是因?yàn)閤的變化而變化的，就不會(huì)把自變量和因變量的意義搞亂。 線性回歸是自變量與因變量之間是線性關(guān)系的回歸。 一般來(lái)說(shuō)，因變量只有一個(gè)，自變量會(huì)有一個(gè)或多個(gè)。下面就按因變量的數(shù)量及類別為分：一元線性回歸、多元線性回歸。 一元線性回歸一元線性回歸是指一個(gè)因變量y只與一個(gè)自變量x有相關(guān)關(guān)系，通過(guò)公式可以表示為如下圖：
其中a稱為斜率，b稱為截距。 它的意思是當(dāng)x增減一個(gè)單位時(shí)，y會(huì)同樣增減a個(gè)單位的x，如a＝2時(shí)，x增加一個(gè)單位，y就增加2個(gè)單位x。 通過(guò)EXCEL統(tǒng)計(jì)函數(shù)LINEST來(lái)實(shí)現(xiàn)一元線性回歸分析，在EXCEL的A1到B10輸入如下數(shù)據(jù)：
使用LINEST線性回歸函數(shù)進(jìn)行手工計(jì)算。 LINEST函數(shù)可通過(guò)使用最小二乘法計(jì)算與現(xiàn)有數(shù)據(jù)最佳擬合的直線，來(lái)計(jì)算某直線的統(tǒng)計(jì)值，然后返回描述此直線的數(shù)組。 也可以將 LINEST 與其他函數(shù)結(jié)合使用來(lái)計(jì)算未知參數(shù)中其他類型的線性模型的統(tǒng)計(jì)值，包括多項(xiàng)式、對(duì)數(shù)、指數(shù)和冪級(jí)數(shù)。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。 LINEST語(yǔ)法LINEST(known_y’s, [known_x's], [const], [stats])
附加回歸統(tǒng)計(jì)值如下：
下面的圖示顯示了附加回歸統(tǒng)計(jì)值返回的順序。
在任意單元格中輸入=LINEST(B2:B10,A2:A10,TRUE,TRUE)，計(jì)算得出來(lái)的結(jié)果為94.33。Linest函數(shù)直接計(jì)算，返回的是第一個(gè)自變量的系數(shù)，LINEST返回的是一個(gè)數(shù)組，即上述的圖表。 如果要通過(guò)EXCEL數(shù)組來(lái)實(shí)現(xiàn)這種功能。選定A14:B18,在EXCEL地址欄輸入=LINEST(B2:B10,A2:A10,TRUE,TRUE)，然后同時(shí)按CTRL+SHIFT+ENTER，返回一個(gè)表格，表格中的每個(gè)單元格的公式顯示為：{=LINEST(B2:B10,A2:A10,TRUE,TRUE)}
對(duì)應(yīng)上表及上述的圖，解釋上表的各個(gè)參數(shù)的意義。
上述返回的統(tǒng)計(jì)值，最常用的是自變量的系數(shù)a和常量b，如果在EXCEL自動(dòng)化程序中，很少會(huì)用上述的返回一個(gè)表格的方式，因?yàn)長(zhǎng)INEST返回的是一個(gè)數(shù)組，可以通過(guò)index函數(shù)取得數(shù)組中的每一個(gè)值。 x的系數(shù)a可以在任意單元格式輸入=INDEX(LINEST(B2:B10,A2:A10,TRUE,TRUE),1,1)，截距b可以在任意單元格式輸入=INDEX(LINEST(B2:B10,A2:A10,TRUE,TRUE),1,2)，index函數(shù)第一個(gè)參數(shù)是指定一個(gè)數(shù)組，第二和第三個(gè)參數(shù)是指定返回的行列位置。 所以上述的一元線性回歸的擬合直線函數(shù)為y=94.34x+93.92，相關(guān)系數(shù)為0.97。 多元線性回歸多元線性回歸是指一個(gè)因變量y只與多個(gè)自變量x有線性相關(guān)關(guān)系，通過(guò)公式可以表示為如下圖：
a為每個(gè)自變量對(duì)因變量y的影響因素，我們以二元線性回歸為例，用EXCEL函數(shù)LINEST進(jìn)行分析。數(shù)據(jù)如下，填充在EXCEL的A1:C10中。
選定A14:C18，在地址欄中輸入=LINEST(C2:C10,A2:B10,TRUE,TRUE)，按CTRL+SHIFT+ENTER，返回一個(gè)表格，表格中的每個(gè)單元格的公式顯示為：{=LINEST(C2:C10,A2:B10,TRUE,TRUE)}，如下表：
此表格和一元線性回歸的表格一樣，只是多了一列，因?yàn)槎嗔艘粋€(gè)自變量。多出一列的內(nèi)容是另一個(gè)自變量的系數(shù)和它的標(biāo)準(zhǔn)誤差值。同樣可以通過(guò)用INDEX函數(shù)取得數(shù)據(jù)的每一個(gè)值。根據(jù)上表可以得到擬合的線性回歸函數(shù)y=69.17×1+6.88×2+87.37 用EXCEL數(shù)據(jù)分析工具進(jìn)行回歸分析接下來(lái)通過(guò)EXCEL數(shù)據(jù)分析工具實(shí)現(xiàn)上述一元線性回歸分析的計(jì)算，并可以驗(yàn)證上述的計(jì)算過(guò)程。如果你的EXCEL中找不到數(shù)據(jù)分析，請(qǐng)先為EXCEL添加數(shù)據(jù)分析工具的加載宏。
|
|
|
