27 農業試驗所技術服務．2006年09月．第67期
生
物
統
計
農試所農藝組 呂秀英 魏夢麗 呂椿棠
作者：呂研究員秀英
連絡電話︰04-23302301-125
一、前 言
本系列報告在介紹如何使用Excel進 行農業科學研究中資料分析的方法，前三 篇已介紹了敘述統計量、t檢定、F檢定、 χ2檢定，而本篇重點則在說明如何利用 Excel來進行相關與迴歸。二、相關與迴歸的意義
利用兩個變數資料通常可以研究相 關和預測的問題。相關是指兩個變數之 間關聯的強度，瞭解相關通常有二種方 式，一為繪製資料散佈圖，另為計算相 關係數(correlation coefficient)。而當兩 變數有相關時，則可進行簡單迴歸分析 (simple regression analysis)，通常可由 一個自變數(independent variable, 預測 變數, X)，來預測一個依變數(dependent variable,被預測變數, Y)。當欲利用多 個 自 變 數 ( X1, X2, … ) 來 預 測 一 個 依 變 數(Y)時，則使用複迴歸分析(multiple regression analysis)。 相關與迴歸是使用最頻繁的統計方 法，但兩者間的意義常常被混淆不清。兩 者之試驗目的不同：相關分析，全稱為簡 單相關分析，是用來測定兩變數(無依變 數與自變數之分)之間是否存在簡單的直 線關係，當相關分析結果顯著，表示兩變 數間存在直線關係，但若分析結果不顯 著，就不能認定兩者間無任何關係或互相 獨立，因為也有可能是其他二次以上的關 係，故其正確結論是兩變數間無直線關 係；迴歸分析則是用來描述依變數(Y)受 到自變數(X)的影響程度，而希望能夠尋 得一個方程式，使其能藉由X來預測Y。 再者在統計上，相關與迴歸的資料性質也 不同：用以進行相關分析的兩個變數都必 須為不可自由變動的隨機變數，例如兩種 調查性狀間之關係；而迴歸分析的Y為隨 機變數但X卻最好是可自由變動或控制的 固定值，例如乾物重受施肥用量之影響， 乾物重為隨機變數而施肥用量為試驗者自 行給定的固定值，因為這是在以最小平方 法(least squared method)估計迴歸係數 的傳統方法所要求的前提。若迴歸分析 中的X為隨機變數，則迴歸係數之估計應 改採用最大概度法(maximum likelihood method)，但一般統計軟體的內設都是使 用傳統方法，除非去更改設定或另外購買 進階模組，才能在最大概度法的模式下操 作迴歸分析。 無論是相關或迴歸，解釋的是變異 的問題，因此最好都不要將變異和因果混 為一談。我們不能光憑相關或迴歸顯著存 在，就認定X是造成Y結果的原因，因為 兩者之間有可能是其他變數所引起的間接 關係或影響。用Excel解決農業研究資料統計
分析的方法(四)－相關與迴歸
用Excel解決農業研究資料統計
分析的方法(四)－相關與迴歸
28 農業試驗所技術服務．2006年09月．第67期
生
物
統
計
三、相關分析之求算步驟及結果
解讀
Excel的「工具」中的「資料分析」 提供了很多現成的統計分析工具，只需指 出資料所在的單格和提供必要的參數，該 工具就會使用適宜的統計或工程函數，對 資料做處理，給出相應的結果，比起輸入 公式和插入函數，更為簡便。但在安裝 Excel 時，通常不會自動安裝此工具。若 在「工具」中沒有發現「資料分析」，首 先要勾選「工具」中的增益集，從其「增 益功能」內勾選「分析工具箱」後便可完 成安裝。Excel在「資料分析」裡提供了 「相關係數」的計算，但不提供相關係數 的顯著性測驗，不過對此我們仍然可以 透過輸入公式或函數獲得。以4個變數間 的相關分析為例說明，資料如圖一所示， 首先進入「工具」中的「資料分析」選擇 「相關係數」，其操作步驟如下(圖一)： 指定「輸入範圍」－可用輸入範圍位 址或用滑鼠拉選範圍；如本例指定的 輸入範圍若包含變數標記在內，可直 1. 接輸入範圍為相對位址A1:D9。(倘使 用滑鼠拉選範圍的方式，輸入範圍自 動顯示絕對位址$A$1: $D$9)。 依資料輸入格式選擇「分組方式為逐 欄或逐行」－因本例為4個變數分欄輸 入，故勾選逐欄。 勾選「類別軸標記是在第一列上」－ 因本例在指定範圍時，將變數標記一 起納入，故必須勾選此項；但若只有 指定資料本身，未將標記納入，則此 項毋須勾選。 在「輸出選項」中勾選分析結果所在 位置－可以自行決定要放在同一工作 表內某處「輸出範圍」內、「新工作 表」或「新活頁簿」。如本例希望將 輸出結果和資料放在一起，故勾選輸 出範圍並指定位址。 最後按「確定」，即給出輸出結果－ Excel會以矩陣形式來列出相關係數的 結果。如圖一所示之本例結果。 由於Excel的「工具」中的「資料分 析」並不提供相關係數的顯著性測驗，因 2. 3. 4. 5. 圖一、用Excel「工具」中的「資料分析」求4個變數間的相關係數。29 農業試驗所技術服務．2006年09月．第67期
生
物
統
計
此必須接著以輸入公式或函數來進行。相 關係數(r)的顯著性測驗可利用t檢定，公 式如下： 式中n為樣本數，t(α/2, n-2)為α顯著 水準下自由度等於n-2的雙尾 t 臨界值。 t 統 計 值 的 計 算 ， 可 利 用 E x c e l 的 T I N V 函 數 ， 其 格 式 為 ： TINV(probability, degree_freedom)， probability為雙尾機 率 值 ( 故 若 指 定 顯 著 水準為0.05，只需直 接 輸 入 0 . 0 5 即 可 ， 不 必 再 除 以 2 ) ， 而 degree_freedom為自 由 度 。 而 E x c e l 的 絕 對值及開方根函數分 別為ABS及SQRT。 因 此 ， 以 本 例 的 X 1 和 X 2 兩 變 數 間 的 相 關係數0.9454而言 ( 位 址 在 G 4 ) ， 自 由 度 =8-2=6，其t檢定的實 測值和臨界值的Excel 公式寫法如下： t 實測值= A B S ( G 4 )/(SQRT(1-G4*G4))/6) t 臨界值= TINV(0.05, 6) 各得到 7 . 1 0 5 5和2.4469，t 的實 測值大於其臨界值，表 示X1和X2兩變數間存 在顯著的正相關關係。四、簡單迴歸分析之求算步驟及
結果解讀
假設欲探討相對濕度(X,%)增加對某 揮發性化合物之量損失(Y,mg)的影響程 度，其資料輸入如圖二所示。進入Excel 的「工具」中的「資料分析」選擇「迴 歸」，其操作步驟如下(圖二-(A))： 圖二、用Excel「工具」中的「資料分析」進行迴歸分析。(A)操作畫面， 及(B)輸出結果。 (A) (B)30 農業試驗所技術服務．2006年09月．第67期
