《Excel統(tǒng)計分析與決策（第2版）》第11章 多元回歸分析

Excel統(tǒng)計分析與決策第11章多元回歸分析主要內(nèi)容11.1多元回歸分析的基本內(nèi)容11.2非線性回歸分析11.3品質(zhì)變量回歸分析多元回歸分析的基本內(nèi)容多元回歸模型與回歸方程參數(shù)估計回歸方程的似合優(yōu)度顯著性檢驗11.1多元回歸分析的基本內(nèi)容銷售額與廣告媒體的關(guān)系例11.1哪種廣告渠道更有效。選擇“VCD”工作表。在“工具”菜單中選擇“數(shù)據(jù)分析”選項，打開“數(shù)據(jù)分析”對話框。在“數(shù)據(jù)分析”對話框中，于“分析工具”列表中選擇“回歸”選項，單擊“確定”按鈕進入“回歸”對話框。11.1多元回歸分析的基本內(nèi)容在“Y值輸入?yún)^(qū)域”中輸入A1：A21單元格，它代表銷售額的數(shù)據(jù)范圍。在“X值輸入?yún)^(qū)域”中輸入B1：C21單元格，這里包括“廣播”與“電視”兩個自變量，回歸工具要求自變量之間必須是相鄰的，不能隔開。選中標志。選擇95%的置信度。11.1多元回歸分析的基本內(nèi)容在“輸出區(qū)域”中輸入D1單元格，表示輸出結(jié)果的起點。單擊“確定”按鈕。得多元回歸計算結(jié)果如圖所示。11.1多元回歸分析的基本內(nèi)容回歸輸出結(jié)果解釋回歸統(tǒng)計表方差分析表回歸參數(shù)表

11.1多元回歸分析的基本內(nèi)容非線性關(guān)系的線性化的基本內(nèi)容多項式模型對數(shù)模型冪函數(shù)指數(shù)模型11.2非線性回歸分析成本產(chǎn)量多項式模型擬合例11.2

擬合二項成本函數(shù)。選擇“多項式”工作表。繪制成本產(chǎn)量散點圖。11.2非線性回歸分析在B欄中任選一個單元格，打開“插入”菜單，選擇“列”選項，則在A、B兩列之間增加一列，成本數(shù)據(jù)將被移到C列。新列必須放在“產(chǎn)量”列旁邊，Excel要求自變量是相鄰的。在單元格B1中輸入“產(chǎn)量平方”。在單元格B2中輸入公式“=A2^2”以計算A2單元格數(shù)值的平方，并復(fù)制到B3：B51區(qū)域中的各個單元格中。11.2非線性回歸分析在“工具”菜單中選擇“數(shù)據(jù)分析”選項，打開“數(shù)據(jù)分析”對話框。在“數(shù)據(jù)分析”對話框中，于“分析工具”列表中選擇“回歸”選項，單擊“確定”按鈕,進入“回歸”對話框。在“Y值輸入?yún)^(qū)域”中輸入C1：C51，它代表成本的數(shù)據(jù)范圍。在“X值輸入?yún)^(qū)域”中輸入A1：B51，這里包括“產(chǎn)量”與“產(chǎn)量平方”兩個自變量，回歸工具要求自變量之間必須是相鄰的，不能隔開。11.2非線性回歸分析選中標志。選擇95%的置信度。在“輸出區(qū)域”中輸入D1單元格，表示輸出結(jié)果的起點。單擊“確定”按鈕。得多元回歸計算結(jié)果如圖所示。11.2非線性回歸分析產(chǎn)量收益對數(shù)模型擬合例11.3建立模型說明產(chǎn)量收益之間的關(guān)系。打開“對數(shù)”工作表。制作產(chǎn)量收益散點圖，看出二者呈現(xiàn)出較好的對數(shù)形式。11.2非線性回歸分析利用回歸分析工具得到分析結(jié)果如圖所示。11.2非線性回歸分析生產(chǎn)函數(shù)（冪函數(shù)）模型擬合例11.4

試對某省近年工業(yè)產(chǎn)值、固定資產(chǎn)投資和職工工資資料擬合適當(dāng)模型加以分析。生產(chǎn)函數(shù)是典型的多元冪函數(shù)，其形式為：Y=AKαLβ。對冪函數(shù)公式兩邊同時取對數(shù)：

Ln(Y)＝Ln(A)＋α*Ln(K)＋β*Ln(L)用Ln(y)作為標準線性回歸的因變量，Ln(K)、Ln(L)為自變量進行擬合。11.2非線性回歸分析詳細過程參照成本產(chǎn)量多項式模型，結(jié)果如圖：11.2非線性回歸分析國內(nèi)生產(chǎn)總值指數(shù)模型擬合例11.5擬合國內(nèi)生產(chǎn)總值的指數(shù)增長模型。詳細過程參照成本產(chǎn)量多項式模型，下圖是回歸分析結(jié)果：11.2非線性回歸分析兩種表現(xiàn)的品質(zhì)變量回歸分析可以將品質(zhì)變量指定0值或1值，從而使其在回歸模型中作為自變量。這些0/1變量有時也稱為虛擬變量或啞變量。與在多元回歸分析中使用數(shù)量變量一樣，使用這些啞變量也可建立模型。啞變量只有兩個值0或1，當(dāng)啞變量用于表示某單一特征時，具有此特征的變量等于1，不具有此特征的變量表示為0。11.3品質(zhì)變量回歸分析例11.6使用計算機學(xué)生的統(tǒng)計成績高于不使用計算機學(xué)生的統(tǒng)計成績？選擇“計算機”工作表?；貧w分析工具要求所有的自變量數(shù)據(jù)是鄰近的，所以需移開C列數(shù)據(jù)，建立啞變量。11.3品質(zhì)變量回歸分析在C欄中任選一個單元格，打開“插入”菜單，選擇“列”選項，則在B、C兩列之間增加一列，“是否使用計算機”數(shù)據(jù)將被移到D列。C列用于放置啞變量。在單元格C1中輸入“計算機”。在單元格C2中輸入條件函數(shù)IF的表達式“=IF(D2=”是“,1,0)”，式中表示如果真則為1，如果不真則為0。單元格C2顯示值1，將其復(fù)制到C3：C21各單元格中。C列中的數(shù)值便是啞變量的值，作為自變量使用。11.3品質(zhì)變量回歸分析在“工具”菜單中選擇“數(shù)據(jù)分析”選項，打開“數(shù)據(jù)分析”對話框。在“數(shù)據(jù)分析”對話框中，于“分析工具”列表中選擇“回歸”選項，單擊“確定”按鈕，進入“回歸”對話框。在“Y值輸入?yún)^(qū)域”輸入地址為A1：A21。在“X值輸入?yún)^(qū)域”輸入地址為B1：C21。11.3品質(zhì)變量回歸分析選擇“標志”和95%的置信度。在“輸出區(qū)域”中輸入E2，單擊“確定”按鈕，得計算結(jié)果如圖所示。11.3品質(zhì)變量回歸分析多種表現(xiàn)的品質(zhì)變量回歸分析例11.7擬合售價的預(yù)測模型。選擇“房地產(chǎn)”工作表。11.3品質(zhì)變量回歸分析在單元格E1、F1和G1中分別輸入“高”、“中”和“低”。在單元格E2中輸入公式：＝IF(D2＝“高”，1，0)。在單元格F2中輸入公式IF(D2＝“中”，1，0)。在單元格G2中輸入公式：IF＝(D2＝“低”，1，0)。如果三個公式輸入正確，單元格E2：G2的內(nèi)容是0，0，1。將單元格E2：G2的內(nèi)容復(fù)制到E3：G16區(qū)域中的各單元格。11.3品質(zhì)變量回歸分析由于回歸分析工具要求變量區(qū)域必須鄰接，故需對各列位置進行調(diào)整。選中D列，點擊鼠標右鍵，在彈出的工具條中選擇“剪切”；再選中H列，點擊鼠標右鍵，在彈出的工具條中選擇“插入剪切單元格”，則調(diào)整后的工作表如圖所示。11.3品質(zhì)變量回歸分析打開“數(shù)據(jù)分析”對話框。在“數(shù)據(jù)分析”對話框中，于“分析工具”列表中選擇“回歸”選項，單擊“確定”按鈕,進入“回歸”對話框。1