如何利用 Excel 進行統計分析 慈濟醫學中心 / 研究部王仁宏 /Paul Wang paulwang@tzuchi.com.tw 分機 7651
課程目標 讓同仁能熟悉利用 Excel 進行下列分析 描述性統計 統計量 交叉表 統計圖 推論性統計 相關分析 差異分析 迴歸分析 [ 註 ] 此次課程講義內容皆以 Excel 2007 進行示範教學!!
Outline 準備工作 醫學研究常用統計方法 如何利用 Excel 進行 描述性統計分析 統計量 交叉表 統計圖 如何利用 Excel 進行 推論性統計分析 相關分析 差異分析 迴歸分析
注意事項 淡定
準備工作 1 安裝分析工具箱 1 Windows 圖案 Excel 選項 增益集 執行 選擇分析工具箱 3 5 2 4
準備工作 2 啟動巨集功能 1 Windows 圖案 Excel 選項 信任中心設定 啟用所有巨集 3 點選 信任中心設定 4 2 點選 啟用所有巨集
醫學研究常用統計方法 有人針對社會科學與教育類 醫學護理類 管理類, 每大類搜尋 20~30 篇論文, 然後將這些論文使用的統計方法整理如下 : 醫護類會用到大量的描述統計 (22%) ANOVA(18%) 和 T 檢定 (18%) 其他統計方法 (6~12%): 皮爾森相關 線性迴歸 無母數 邏輯斯迴歸 醫護類的論文很少使用到 SEM 和 ANCOVA
如何利用 Excel 進行統計分析 Step 1. 掌握研究的主要分析目的 Step 2. 依資料特性選擇合適的統計方法 Step 3. 利用 Excel 提供之相關功能完成分析 分析工具箱 樞紐分析 圖表 統計函數 巨集 ( 自己或他人已撰寫完成之 VBA 巨集 )
研究主題 範例資料說明 門診病人對醫療服務品質的看法 (2006) 問卷設計四大構面 : ( 共 19 題 ) 醫療專業 (5) 等候時間 (4) 硬體設施 (6) 人員服務 (4)
範例資料蒐集方式說明 抽樣方法 便利抽樣 資料蒐集 利用醫院門診時段收集問卷 為了避免在相同門診時段重覆收案及考量收案代表性 週一至週五上 下午的門診時段到該院批價領藥的等候區, 隨機選取門診病人填答問卷並當場回收問卷, 年幼的病人則透過親人來回答問卷內容 盡量考慮蒐集不同科別門診病患 ( 降低 selection bias) 問卷回收狀況 問卷發放時間 2006/9/1~2006/12/31 總共回收 502 份問卷, 其中 427 份為有效問卷 (85.06%)
範例資料 (SPSS) 資料經 coding 後輸入, 反向題有轉向計分
如何利用 EXCEL 進行 描述性統計分析?
統計量 集中區勢 離散趨勢 Mean Median Mode Max/Min Quartile(Q1,Q3) Range Standard Deviation 描述統計 -- 統計量 & 統計圖 目的 : 讓分析者可以在短時間內瞭解資料的分佈狀況與訊息 Histogram Pie Chart Boxplot
描述性統計分析 -- 統計量
集中趨勢量數 vs 差異量數 集中趨勢量數 (measures of central tendency) 代表一組資料中, 各個個體的某種特性有共同的趨勢存在之量數, 又因其可反映該組資料觀測值集中的位置, 又稱為位置量數 (location measures) 較常用的集中趨勢量數有平均數 加權平均數 中位數 眾數與百分位數 差異量數 (dispersion measures) 在衡量一組資料中, 各個觀測值之間的差異或離散的程度 ( 故差異量數亦稱為離散量數 ) 重要的差異量數, 包括全距 四分位差 平均偏差 標準差 ( 與變異數 ) 及變異係數
平均數 / 中位數 / 眾數與資料分佈 ( 偏態 ) 的關係 偏態係數 (a) 左偏 (b) 右偏 (c) 對稱 注意 : 偏態的方向描述是極端值方向 平均數中位數眾數 (a) 眾數中位數平均數 (b) 平均數 = 中位 = 眾數 (c)
利用 Excel 計算統計量 (1/2) Excel 資料 資料分析 選擇 敘述統計 相關設定 1 2 3 4
利用 Excel 計算統計量 (2/2) 針對所選取之資料可自動計算出常用之統計量!!
描述性統計分析 -- 交叉表 / 列聯表
利用 Excel 進行交叉分析 (1/3) 2 Excel 選取資料 插入 樞紐分析表 相關設定 1 樞紐分析表利用表格的方式幫助分析者掌握 資料分佈情況 ( 次數 百分比 統計量 ) 快速檢視類別變項間的相關性
利用 Excel 進行交叉分析 (2/3) 設定包括 : 列 欄及篩選欄位設定, 值的呈現方式
利用 Excel 進行交叉分析 (3/3) 值的呈現方式 可選擇 值欄位設定 顯示方式 Count 列百分比
描述性統計分析 -- 統計圖
常見統計圖形 不同類別間之差異 單組資料之分佈 Bar Graph Histogram Boxplot 不同類別間之差異 Line Graph 時間變化趨勢
長條圖 (Bar Graph) Excel 選取資料 插入 直條圖 相關設定 1 2 版面配置 可進行標籤 座標軸 誤差線等調整 1 2
長條圖 (Bar Graph) 範例 針對課程練習資料利用樞紐分析計算統計量後繪製
直方圖 (Histogram) Excel 資料 資料分析 直方圖 相關設定 1 2 1 2 3
直方圖 (Histogram) 範例 點選 Bar 按右鍵 選擇資料數列格式 設定 無間距 1 3 2
盒形圖 (Box Plot) Excel 將資料複製到 Excel sheet 以滑鼠拖曳選擇所需資料 按 繪盒形圖 即可!! ( 應用他人撰寫之巨集 ) 1 2
盒形圖 (Box Plot) 範例 此圖形可用於比較不同組資料測量結果之散佈情況
折線圖 (Line Graph) Excel 選取資料 插入 折線圖 相關設定 1 2 版面配置 可進行標籤 座標軸 誤差線等調整 1 2
折線圖 (Line Graph) 範例
如何利用 EXCEL 進行 推論性統計分析?
母體與樣本 母體 Population X 1, X 2,,X N Sampling Experiment 樣本 Sample x 1,,x n H 0 vs H 1 Parameter 參數 推論 Inference 描述 Descriptive Statistics 統計量
推論性統計分析 -- 相關分析
質性 ( 類別 ) 資料的分析 質性 ( 類別 ) 資料特質 不能作個人量化量測, 它是關於有沒有存在某種特質的資料, 例如 : 有無抽菸 / 喝酒 / 嚼檳榔 依照感興趣的特質將資料分組, 例如 : 高血壓 老年人 觀測到的是次數 用來將資料分組的表格稱為列聯表 (Contingency Table) 關心什麼? 兩個變數有沒有相關? 獨立性 危險因子有疾病沒有疾病總計 有暴露 a b a+b 沒有暴露 c d c+d 不同族群中某項特質的分佈是否相似? 同質性 實驗組 vs 對照組治療成功的比例有差? 比例差異顯著性
分析方法 樣本為獨立樣本 卡方檢定 1 < 注意事項 > 2X2 列聯表, 所有格子的期望值都要大於 5 更大的列聯表, 各格的期望值不要小於 1, 且期望值小於 5 的格數不要超過 20% 如果發生上述情況, 應採用 Fisher Exact Test 樣本為配對或非獨立 McNemar s Test 2 適用時機 :Matched Before-and-after design 發生車禍前後是否習慣性繫安全帶?
範例 -- 卡方檢定 Example: 欲探討性別與睡眠困擾之相關性 獨立樣本 觀測值 (O) E ij =n P i P j (i) 期望值 (E) (j) 性別和睡眠困擾間是有相關性 男 > 女
利用 Excel 進行卡方檢定 Excel 輸入資料 利用統計函數即可計算 P 值與檢定統計量 (X 2 ) 可利用 CHIINV 或公式進行計算 可利用 CHITEST 進行計算
範例 --McNemar Test Example: 醫院想分析評鑑與員工發生睡眠困擾是否有關假設去年沒有評鑑, 今年有評鑑相依樣本 a c b d P 值 =0.039 評鑑顯著增加睡眠困擾的比例
利用 Excel 進行 McNemar 檢定 Excel 輸入資料 利用統計函數即可計算 P 值與檢定統計量 (X 2 ) 可利用 CHITEST 進行計算 可利用 CHIDIST 進行計算
推論性統計分析 -- 差異分析
差異比較 (1/2) 統計學家發明了許多統計檢定方法 單一樣本檢定平均數 Z test(σ 已知 ), T test(σ 未知 ) 兩組樣本檢定平均數 Independent T Test ( 獨立 ) Paired T Test ( 相依 ) 中位數 Wilcoxon rank-sum test ( 獨立 ) Wilcoxon signed-rank test ( 相依 ) Mean 有差異 A B Variation 有差異 A B
差異比較 (2/2) 兩組或多組樣本檢定平均數 For 2 Groups F=t 2 方法一 利用 T Test 進行兩兩比較 過度檢定 (overtesting) 需要選擇合適的顯著水準, 讓 Overall Type I error 控制 方法二 變異數分析 (ANOVA, F-test) 檢定所有組別平均數是否有顯著差異的整體量測方式 前提假設 1. 所有觀測值是獨立的 每個觀測值彼此不相關 2. 每一組內觀測值呈常態分布 3. 變異同質性 每一組內變異數與他組相同 ANOVA 只要樣本數夠大 每組樣本數相近效果就不錯!!
利用 Excel 進行 Independent T Test(1/2) Excel 資料 資料分析 選擇 t 檢定 相關設定 1 2 3
利用 Excel 進行 Independent T Test(2/2) Example: 檢定受訪對象 ( 男 vs 女 ) 之平均年齡是否相等!! 自動計算檢定統計量及 P-value 男 / 女年紀有顯著差異!!
利用 Excel 進行 Paired T Test(1/2) Excel 資料 資料分析 選擇 t 檢定 ( 成對 ) 相關設定 1 2 3
利用 Excel 進行 Paired T Test(2/2) Example: 檢定減重前後體重是否有顯著降低 3 公斤以上!! 自動計算檢定統計量及 P-value 減重效果顯著超過 3kg!!
利用 Excel 進行 ANOVA(1/3) Excel 資料 資料分析 選擇 單因子變異數分析 相關設定 1 2 3
利用 Excel 進行 ANOVA(2/3) Example: 比較四台機器的每小時平均產能是否相同!!
利用 Excel 進行 ANOVA(3/3) F 檢定之 P-value=0.018<0.05 不同機器產能不盡相同!!
推論性統計分析 -- 迴歸分析
使用時機 兩個連續變數之關係 當兩個變項之間存有某種連動的變化趨勢, 則稱他們之間是相關的 研究常需同時審視兩個變項的資料 (ex: 身高 體重 ) 兩個變項之間是否有關連 (relationship) 關連的強弱 統計圖形 散佈圖 (Scatter Plot) 統計量 相關係數 (Correlation Coefficient) 瞭解兩個變項之間的線性關係
相關性的強弱 完全正相關 完全負相關 正相關 負相關 可用相關係數 (r) 的大小進行判斷越接近 +1 or -1 代表線性相關程度越強!! 沒有直線關係 沒有直線關係但有非線性相關
各種相關係數 皮爾森相關係數 (Pearson correlation coefficient) 主要是測量兩連續變數間關係的強弱 需符合常態假設 斯皮爾曼等級相關係數 (Spearman rank C.C.) 主要是測量兩等級變項間關係的強弱 不需常態假設 Spearman 等級相關係數屬於無母數的統計方法它對離群質較不敏感!! 通常用於非常態之連續變數相關或是兩個序位變項間的一致性!!
迴歸分析 (Regression) 迴歸 (regression) 以一個自變項 (independent variable) 的變化來預測或解釋另一個應變項 (dependent) 的變化 < 例如 > 1. 以身高來預測體重 2. 以指考成績來預測大一第一學期的成績 3. 以年齡來預測血中膽固醇濃度 4. 預測食鹽攝取量對血壓值的影響 5. 以氣溫來預測飲料的銷售量
相關係數與迴歸分析 (1)
相關係數與迴歸分析 (2)
利用 Excel 進行迴歸分析 (1/3) Excel 資料 資料分析 選擇 迴歸 相關設定 1 2 3
利用 Excel 進行迴歸分析 (2/3) Example: 某運輸公司需分析司機每日行駛哩數與時間的關係!!
行駛時間 (Y) 利用 Excel 進行迴歸分析 (3/3) 行駛時間 vs 行駛哩數 10 9 8 7 6 5 4 3 2 1 0 y = 0.0671x + 1.1285 R² = 0.6014 0 20 40 60 80 100 120 行駛哩數 (X1) 行駛時間 (Y) 線性 ( 行駛時間 (Y)) 檢定之 P-value=0.008<0.05 行駛哩數與時間顯著相關!!
邏輯斯迴歸分析 邏輯斯迴歸 (Logistic Regression) 當我們考慮的迴歸模型中依變數為二元類別資料 (ex: 有病 / 沒病 ) 時則此迴歸模型稱為邏輯斯迴歸 自變數可為連續型或類別型變數 當自變數只有一個稱為簡單邏輯斯迴歸 (Simple) 當自變數超過一個以上稱為多元或複邏輯斯迴歸
範例 -- 簡單邏輯斯迴歸 (1/2) X 為連續型 要注意應變數 (Y) 的 coding!! 模型探討的是 Internal Value=1 的發生機率 i.e. 發生睡眠困擾的機率 年紀與是否有睡眠困擾是顯著相關!! 年紀每增加一歲, 有睡眠困擾的勝算增加為 1.099 倍 ( 即 exp(0.095))!!
範例 -- 簡單邏輯斯迴歸 (2/2) X 因為有 3 類所以需要產生兩個虛擬變數!! X 為類別型
利用 Excel 進行邏輯斯迴歸分析 (1/2) Excel 將資料複製到 Excel sheet 按 Logistic 即可!! ( 應用他人撰寫之巨集 ) 1 2 [ 註 ]A 欄為 Y 之資料內容 /C~H 欄則依序為 X1~X6 之資料內容!!
利用 Excel 進行邏輯斯迴歸分析 (2/2) Example: 利用高中學生智商預測物理成績是否及格!! 資料共有 55 位及格 /45 位不及格 學生智商 (X1) 越高物理越容易及格 ( 智商每增加一單位, 物理成績及格的機率是原來的 1.25 倍 )!!
資料型態與適用統計方法
Review 準備工作 醫學研究常用統計方法 如何利用 Excel 進行 描述性統計分析 統計量 交叉表 統計圖 如何利用 Excel 進行 推論性統計分析 相關分析 差異分析 迴歸分析
Reference 統計學與 Excel 資料分析之實習應用 ( 王文中, 2012) 統計分析實務與應用 :Excel 2007( 王鴻儒, 2008)
靜思語 : 知識要用心體會, 才能變成自己的智慧 感謝聆聽 Q & A