excel與最小二乘法

1、1、最小二乘法是以誤差理論為依據(jù),在諸數(shù)據(jù)處理方法中,誤差最小,精確性最好。然而在實際教學過程中因其計算比擬繁雜,學生很少采用這一方法,影響了學生運用最小二乘法進行數(shù)據(jù)處理能力的培養(yǎng)。隨著計算機的普及,運用最小二乘法進行數(shù)據(jù)處理有了有力的工具,然而采用編寫程序的方法處理數(shù)據(jù)學生仍感到并不簡便。尋找簡便易學、容易掌握的計算方法是解決學生掌握最小二乘法進行數(shù)據(jù)處理的關(guān)鍵。筆者認為運用最常見的學生也比較了解的軟件Excel進行最小二乘法的計算,其過程簡便而且容易掌握。2運用Excel進行最小二乘法的計算Excel中有多種工具可用于最小二乘法的計算,其中的 “函數(shù) 、 “圖表向?qū)?、“?shù)據(jù)分析在處理數(shù)據(jù)

2、時各有特點,用于最小二乘法計算時不需要編寫程序,處理數(shù)據(jù)非常簡便。例:溫度變化時,測得某銅線的電阻,數(shù)據(jù)記錄在Excel中如表1 ,求在0時銅線的電阻及其溫度系數(shù)。 表1實驗數(shù)據(jù)記錄表A B C D E F G H I J K1t/2510 3010 3510 4010 4510 5010 5510 6010 6510 70102R/ Q11579 11611 11639 11670 11698 11727 11758 11787 11814 11846這一問題可以用Excel通過三種不同的方法進行最小二乘法計算。211運用Excel中的 “函數(shù) 進行計算Excel中有各類函數(shù)三百余個,分別用

3、于各種不同的計算。其中的線性回歸擬合線方程的斜率函數(shù)SLOPE線性回歸擬合線方程的截距函數(shù)INTERCEPT以及相關(guān)系數(shù)函數(shù)CORREL可用來確定線性方程y = ax + b的a、b兩個系數(shù)和計算相關(guān)系數(shù)以判別線性回歸 是否合理。如在上例中，在空白的單元格單擊插入菜單中的“僅函數(shù)在彈出的對話框中分別選中函數(shù)名為“SLOPE “INTERCEPT “CORREL勺函數(shù)，在各自的對話框中輸入存放數(shù)a據(jù)的單元格區(qū)域B2 : K2和B1 : K1便可獲得斜率a = R0 = 0. 00589 ;截距b = R0 = 1.433和 a相關(guān)系數(shù)R = 0. 9999的結(jié)果。由此可得在0 C時銅線的電阻為1

4、1433,溫度系數(shù)為=-3 - 1 a R = 4.108Cx10R0 0 212運用Excel中的 “圖表向?qū)нM行計算“圖表向?qū)?是Excel中繪制圖表的工具,提供有十多種 “圖表類型 。其中的“XYB點圖可用來進行回歸 分析，在生成一張數(shù)據(jù)分析圖時，并能方便地得到擬合線方程和相關(guān)系數(shù)的平方。單擊 插入菜單中的 圖表選 中“X微點圖在對話框 步驟之二的 數(shù)據(jù)區(qū)域中輸 入存放y軸數(shù)據(jù)的單元格區(qū)域B 2 : K2 ;在 “系列 選項的項圖1電阻溫度系數(shù)的測定( )“X值X中輸入存放x軸數(shù)據(jù)的單 元格區(qū)域B 1 : K1在對話框 步驟之三中確定圖形的名稱、坐標軸的標題以及網(wǎng)格線,在 確定圖表的插入

5、位置后就完成實驗數(shù)據(jù)分布圖。選中所作的圖表,在工具欄單擊“圖表 中的 “添加趨勢線,在彈出的對話框選項中 “類型 選 “線性 “選項 選中 “顯示公式和 “顯示R平方值 的復選框,便可得到擬合線方程和相關(guān)系數(shù)的平方。如圖1所示,擬合 線方程為y = 0. 0059x + 1. 4333及相關(guān)系數(shù)的平方R2 = 0. 9999 ,由此也可得在0時銅線的電阻為11433，溫度系數(shù) 為4. 108 X10 C213運用Excel中的 “數(shù)據(jù)分析進行計算“數(shù)據(jù)分析 是Excel中為了進行復雜統(tǒng)計或工程分析時節(jié)省步驟的一個專用工具。使用時單擊“工具 菜單中的 “數(shù)據(jù)分析 命令。如果“工具 菜單中沒有“數(shù)

6、據(jù)分析命令,(那么需要安裝“分析工具庫。 在 “工具 菜單中,單擊“加載宏 命令,在 “加載宏 對話框中)選中 “分析工具庫 。在彈出的“數(shù)據(jù)分析對話框中選中 “回歸 , 此工具可通過對一組觀 察值使用 最小二乘法直線擬合，進行線形回歸分析。在彈出的回歸對話框“Y直輸入?yún)^(qū)域、“X值輸入?yún)^(qū)域中分別輸入存放數(shù)據(jù)的單元格區(qū)域， 選擇輸出區(qū)域單項選擇按鈕并輸入要顯示結(jié)果的單元格,假設(shè)選中“線性擬合圖的復選框那么可同時生成圖表。單擊 “確 定 就完成了所有計算和作圖工作?！皵?shù)據(jù)分析 的結(jié)果有許多線性回歸分析的計算數(shù)值。在本例中不但計算出關(guān)系數(shù)R = 0. 9999 ;斜率a = 0100589;截距b

7、= 1.433 ,同時在 “標準誤差行中顯示測量值yi的標準 偏差s (y) = 010016 ,在 “標準誤差 列中顯示斜率a的標準偏差s ( a)= 2155 X10-和截距()b的標準偏差s b = 0100126等等分析數(shù)據(jù)。214各運算方法的特點比擬從上面Excel中三種計算方法中可看出:利用 “函數(shù) 運算方法簡單,但需要記住函數(shù)名稱,缺點是沒有圖表顯示;利用“圖表向?qū)н\算根據(jù)對話框,所見即所得操作簡單,數(shù)據(jù)和圖表都能顯示,缺點是運算步驟較多;利用 “數(shù)據(jù)分析運算過程簡單,運算結(jié)果和圖表可一并獲得,獲得的數(shù)據(jù)分析結(jié)果比前兩種方法要多而全,而過程那么簡便得多,其缺點正是得到的分析數(shù)據(jù)太

8、多,許多數(shù)據(jù)是初步進行最小二乘法計算時所不需要的,要能夠?qū)μ嗟臄?shù)據(jù)進行取舍。對它們各自特點比照方表2。表2 Excel中三種最小二乘法計算比擬表方法 所需步驟獲得信息函數(shù)2步/函數(shù)截距、斜率、相關(guān)系數(shù)圖表向?qū)?共5步 圖表、擬合線方程、相關(guān)系數(shù)的平方( ( ) ( ) ( ) )數(shù)據(jù)分析共2步截距、斜率、相關(guān)系數(shù)、標準偏差s y、s a、s b、圖表、等根據(jù)上面三種計算方法的特點,筆 者 認 為 在 運 用 最 小 二 乘 法 處 理 實 驗 數(shù) 據(jù) 時 以Excel中 “數(shù)據(jù)分析最為方便又易于掌握,獲得的信息也最多。3結(jié)束語在眾多的最小二乘法計算方法中,利用Excel來處理,不用編寫程序,

9、簡便易學,容易掌握,是解決學生掌握最小二乘法進行數(shù)據(jù)處理的很好工具,在培養(yǎng)學生數(shù)據(jù)處理能力方面很有幫助。應用EXCE會現(xiàn)最小二乘法計算的方法有：利用EXCEL數(shù)、利用數(shù)據(jù)分析工具、添加趨勢線等。 表格與公式編輯將最小二乘法計算過程，應用電子表格逐步完成計算，得到結(jié)果。 應用EXCEL勺統(tǒng)計函數(shù)A、LINEST使用最小二乘法對數(shù)據(jù)進行最正確直線擬合，然后返回描述此直線的數(shù)組。也可以將LINEST與其他函數(shù)結(jié)合以便計算未知參數(shù)中其他類型的線性模型的統(tǒng)計值，包括多項式、對數(shù)、指數(shù)和冪級數(shù)。因為此函數(shù)返回數(shù)值數(shù)組，所以必須以數(shù)組公式的形式輸入。B、SLOPE返回根據(jù)known_ys和known_xs中

10、的數(shù)據(jù)點擬合的線性回歸直線的斜率。斜率為直線上任意兩點的重直距離與水平距離的比值，也就是回歸直線的變化率。C、INTERCEPT利用現(xiàn)有的x值與y值計算直線與y軸的截距。截距為穿過的known_xs和known_ys數(shù)據(jù)點的線性回歸線與y軸的交點。當自變量為0零時，使用INTERCEP屈數(shù)可以決定 因變量的值。D、CORREL返回單元格區(qū)域array1和array2之間的相關(guān)系數(shù)。使用相關(guān)系數(shù)可以確定兩種屬性之間的關(guān)系。 添加趨勢線添加趨勢線的應用較其他方法直觀，可以用來完成直線回歸，也可以用來完成非線性回歸。具體方法不再贅述。 數(shù)據(jù)分析工具“回歸分析工具通過對一組觀察值使用 “最小二乘法直線

11、擬合來執(zhí)行線性回歸分析。本工具可用來分析單個因變量是如何受一個或幾個自變量的值影響的?！盎貧w分析對話框Y值輸入?yún)^(qū)域在此輸入對因變量數(shù)據(jù)區(qū)域的引用。該區(qū)域必須由單列數(shù)據(jù)組成。X值輸入?yún)^(qū)域在此輸入對自變量數(shù)據(jù)區(qū)域的引用。Microsoft Office Excel將對此區(qū)域中的自變量從左到右進行升序排列。自變量的個數(shù)最多為16。標志 如果數(shù)據(jù)源區(qū)域的第一行或第一列中包含標志項，請選中此復選框。如果數(shù)據(jù)源區(qū)域中沒有標志項，請去除此復選框，Excel將在輸出表中生成適當?shù)臄?shù)據(jù)標志。置信度如果需要在匯總輸出表中包含附加的置信度，請選中此選項。在框中，輸入所要使用的置信度。默認值為95%。常數(shù)為零如果要強制回歸線經(jīng)過原點，請選中此復選框。輸出區(qū)域在此輸入對輸出表左上角單元格的引用。匯總輸出表至少需要有七列，其中包括方差分析表、系數(shù)、y估計值的標準誤差、r2值、觀察值個數(shù)以及系數(shù)的標準誤差。新工作表單擊此選項可在當前工作簿中插入新工作表，并從新工作表的A1單元格開始粘貼計算結(jié)果。假設(shè)要為新工作表命名，請在框中鍵入名稱。新工