EXCEL在檢核調查資料的應用



王文心
[原文刊載於SRDA學術調查研究資料庫通訊第8期,2004.3]

在今日資訊科技發達的時代,市場上發展出為數不少的專業統計軟體,例如:SPSSSASSTATA等,專為量化資料整理與分析的需求而設計,這些軟體固然在處理量化資料的速度及功能上有其優點,但是當考慮到價格時,卻著實讓不少人望而卻步。
EXCEL—一種幾乎每一台電腦都擁有的基本軟體,也具有建立、整理和計算資料的功能,同樣可以用來檢核或是分析資料。在本文中,我們將以調查研究專題中心在今年三月初舉辦的「調查研究資料整理與檢誤研習班」所使用的資料檢核範例[1],來介紹如何以EXCEL來檢核資料。建議讀者可先至「學術調查研究資料庫」網站[2],下載完整的問卷、過錄編碼簿、資料檔與SPSS講義,配合本文親自操作,將更有助於瞭解如何應用EXCEL來檢核調查資料。
1.重覆樣本編號的檢查
檢查受訪者的樣本編號是否發生重複的情形,當一個樣本編號次數分配值大於1時,則此樣本編號為重複的,應調出原始回卷重新確認。
方法:資料à樞鈕分析表及圖報表,以次數分配結果進行檢查。
步驟A:開啟Excel資料檔後,選取「樞鈕分析表及圖報表」








步驟B:選取分析資料來源








步驟C:選取資料範圍






步驟D:將變項加入「列欄位」及「資料欄位」



 

步驟E:將游標置於「加總的ID」上,出現十字符號後,按滑鼠右鍵兩下,將「加總」更改為「項目個數」。


 

步驟F:檢查合計>1次者,即樣本編號重覆者。於2上面按滑鼠右鍵兩下,可檢視原始資料(註:顯示的資料與步驟C時選取的範圍有關)。



2.類別變項不合理值的檢查
不合理值是藉由執行次數分配,找出變項中未定義的選項數值。以「A2」【性別】為例,檢查該變項是否出現研究者所定義「1」:男性、「2」:女性之外的數值。
方法:資料à樞鈕分析表及圖報表,以次數分配結果進行檢查。
以「A2」【性別】變項為例,同「1.重覆樣本編號檢查」的步驟A~步驟E,得到結果如下,發現樣本編號為47者,性別變項的資料出現「3」,不在研究者定義的數值之內。





3.連續變項的檢查
連續性變項若執行次數分配時,會因為數值分布廣,而不易用肉眼檢查,以下以「A3」【年齡】為例,分別說明如何利用「敘述統計」或「直條圖或XY散布圖」來查看資料分布的情形。由於上述兩種方式不一定可檢查出所有的錯誤,所以最後我們介紹利用撰寫語法的方式,直接將不應出現的數值列出,例如:受訪者年齡不應超過90歲,或是不得小於15歲,我們建議讀者看完資料分布的情形之後,一定要再利用語法檢查,才能直接檢查出所有可能的錯誤。
方法1
步驟A:工具→資料分析敘述統計





步驟B:當輸入範圍出現「|」時,用滑鼠選取要進行敘述統計的欄位(以A3年齡變項為例),並選擇輸出選項。
步驟C:按確定得到結果






註:新增資料分析功能方式:工具增益集分析工具箱à確定
方法2:利用圖形,以直條圖或XY散布圖觀察是否有界外值。




 


方法3:撰寫語法,將超出範圍的值列出。
           語法的意義,請參考第31頁到第34頁。
步驟A:於fx列輸入語法,按Enter



步驟B:欲重覆執行同樣邏輯時,先選取已有公式的儲存格,將游標放在右下方,出現「+」時向下拖曳即可。





上圖,「列出超過90歲者」的欄位中若出現「1」時,代表該樣本年齡超過90歲。
4.以點選方式進行邏輯檢誤
           包括類別變項對類別變項(以A5A6為例),以及類別變項對連續變項(以A8A9為例),都可以用以下介紹三種方式:(1)交叉列聯表;(2)排序;(3)篩選,來進行邏輯檢誤。
邏輯:
A5A6:依題目設計,A5【教育程度】選擇「1」:不識字到「5」:高中者,其A6【學歷或考試之科系】應選擇「0」:跳答;A5【教育程度】選擇「6」:高職到「10」:博士者,其A6【學歷或考試之科系】應選擇「1」:文到「10」:其他。
A8A9:依題目設計,A8【工作時數】回答超過40小時者,A9【未達工作時數原因】應選擇「0」:跳答;A8【工作時數】回答未超過40小時者,其A9【未達工作時數原因】應選擇「0」:跳答以外的數值。
方法1:資料à樞鈕分析表及圖報表,以交叉列聯表結果進行檢誤
同「1.重覆樣本編號檢查」的步驟A~步驟C
步驟D:分別將進行邏輯檢核的項目加到「欄欄位」和「列欄位」,再將資料放入「資料欄位」。




步驟E:將「加總」更改為「項目個數」,即可檢查結果。



依據前面說明的邏輯,共有兩筆資料(粗框處)邏輯出現錯誤。
步驟F:在邏輯錯誤的儲存格上按兩下,即可得知該樣本詳細資訊。


進行類別變項對連續變項的檢誤時,建議將連續變項放在「列欄位」,便於檢視結果,如下圖所示。






 
方法2:資料排序,檢視結果。




這種方式的缺點是:(1)不夠清楚,容易沒看出有錯誤的地方;(2)三個變項以上不方便使用。

方法3:篩選
步驟A:資料à篩選à自動篩選,下拉箭頭選擇「自訂」。




步驟B:設定條件,例如A5設定大於5”(設定後箭頭變成藍色),A6設定等於0”




 

步驟C:按確定,得到依自訂條件篩選的結果。


因為只能列兩個條件,此方法有使用上的限制。欲移除自動篩選時,只要重新執行一次「資料篩選→自動篩選」。
           上面舉的例子是有關跳答邏輯,然而問卷中還會存在所謂的一般性邏輯問題,這些邏輯不會在問卷上特別標示,研究者也應就其問卷前後題目有邏輯之處進行檢誤。以A1【與戶長之關係】與A4【婚姻狀況】為例進行檢核,結果如下:






           共有兩筆資料出現邏輯問題,分別是(A1,A4)選(2,1)和(8,1)(粗框處),即受訪者是戶長的配偶、或是戶長子女的配偶,卻回答未婚。
5.以撰寫語法方式進行邏輯檢核
           在正式示範撰寫語法的方式之前,我們先分別用其他的例子來讓您熟悉「IF」、「AND」和「OR」的用法。
A. IF語法介紹
如果您指定的情況結果為 TRUE,則傳回一個值,若結果為 FALSE,則傳回另一個值。IF 函數可用以測試數值和公式的條件。
語法      IFlogical_test,value_if_true,value_if_false
1

A


1
資料


2
50


3
公式
結果
說明

=IF (A2<=100," 預算內","超出預算")
預算內
如果上述的數字小於或等於 100,則公式會顯示 "預算內"。否則,函數將會顯示 "超出預算"

=IF (A2=100,SUM (B5:B15)"")

如果上述的數字為 100,則將計算範圍 B5:B15 內的和。否則,將傳回空白內文 ("")

2

A

B
1
實際費用

預估費用
2
1500

900
3
500

900
4
500

925

公式
結果
說明

=IF (A2>B2,"超出預算","OK")
超出預算
檢查第一排是否超出預算

=IF (A3>B3,"超出預算","OK")
OK
檢查第二排是否超出預算
B. AND語法介紹
如果所有的引數都是 TRUE 就會傳回 TRUE;如果有一或多個引數是 FALSE 就會傳回 FALSE
語法    AND(logical1,logical2,logical3….)
logical1,logical2, ...,係指您要測試的 1 30 個條件,可能是 TRUE FALSE
1

A

B
1
公式
結果
說明
2
=AND(TRUE, TRUE)
TRUE
所有的引數都是 TRUE
3
=AND(TRUE, FALSE)
FALSE
一個引數是 FALSE
4
=AND(2+2=4, 2+3=5)
TRUE
所有的引數評估為 TRUE

2

A


1
資料


2
50


3
104



公式
結果
說明

=AND(1<A2, A2<100)
TRUE
因為 50 介於 1 100

=IF(AND(1<A3, A3<100), A3, "值超出範圍")
值超出範圍
因為未符合介於 1 100之間的條件,故顯示訊息(值超出範圍)。若符合條件時,顯示A3

=IF(AND(1<A2, A2<100), A2, "值超出範圍")
50
符合介於 1 100之間的條件,故顯示正確時的訊息”A2的值


C. OR語法介紹
如果有任何一個引數的邏輯值為 TRUE,即傳回 TRUE;唯有所有引數的邏輯值均為 FALSE時,才會傳回 FALSE
語法    ORlogical1,logical2,…..
Logical1,logical2,...   1 30 個您想要測試其為 TRUE FALSE的條件。
1

A

B
1
公式
結果
說明
2
=OR(TRUE)
TRUE
一引數為 TRUE
3
=OR(1+1=1,2+2=5)
FALSE
所有的引數結果為 FALSE
4
=OR(TRUE,FALSE,TRUE)
TRUE
最少有一個引數為 TRUE
瞭解「IF」、「AND」和「OR」的用法之後,以下我們可以用不同的思考方向來撰寫,執行方法同第27頁。
範例1A5A6的邏輯寫法
=IF(AND(6<=F2,F2<=10,G2=0),"邏輯錯誤","正確")
思考方式:教育程度高職及高職以上者,若回答學歷或科系時是跳答,則是邏輯錯誤,反之,邏輯正確。
=IF(OR(AND(F2<6,G2=0),AND(6<=F2,F2<=10,G2<>0)),"正確","邏輯錯誤")
思考方式:符合下列任一種填答方式,就屬邏輯正確:(1)教育程度在高職以下,不用回答科系(跳答);(2)教育程度在高職及高職以上,有回答科系。
=IF(AND(G2=0,OR(F2=6,F2=7,F2=8,F2=9,F2=10)),"邏輯錯誤","正確")
思考方式:若沒有回答科系者,其教育程度若是高職、專科、大學、碩士或博士中的任何一種,其邏輯是錯誤的,反之,正確。
範例2A8A9的邏輯寫法
=IF(OR(AND(I2>=40,J2<>0),AND(0<I2,I2<40,J2=0),AND(I2=0,J2<>0)),"邏輯錯誤","正確")
思考方式:符合(1)工作時數超過40小時,但多回答了工作原因,或(2)有工作但工作未達40小時,卻未回答不到工作時數的原因,或(3)沒有工作,卻也回答未達工作時數的原因,都屬於邏輯錯誤。

範例3A3A5的邏輯寫法
=IF(AND(D2<18,F2>=8),"錯誤","正確")
思考方式:未滿18歲,但是已在唸大學或有大學以上的學位,符合此條件者為邏輯錯誤。
範例4:複選題的邏輯寫法
先將複選題加總至AG欄位中,再撰寫語法執行檢查。
=IF(AND(AG2<>0,OR(AA2=0,AB2=0,AC2=0,AD2=0,AE2=0,AF2=0)),”錯誤”,”正確”)
思考方式:全部跳答時,總合應為0,當總合不等於0時,如果有任何一個答案不是過錄為0時,便是邏輯錯誤。
=IF(OR(AND(AG23=0,AND(AA23=0,AB23=0,AC23=0,AD23=0,AE23=0,AF23=0)),(AND(AG23<>0,AND(AA23<>0,AB23<>0,AC23<>0,AD23<>0,AE23<>0,AF23<>0)))),"正確","錯誤")
思考方式:全部跳答時,總合應為0。共有兩種情況是符合邏輯正確的:(1)總合為0,且每一個答項都過錄為0(2)總合不為0,且每一個答案都要過錄成0以外(即1”2”)的過錄碼。
語法執行後若發現結果不正確時,可利用:工具à公式稽核à評估值公式,檢查語法中哪一個地方邏輯錯誤。
以上我們共介紹了如何用EXCEL進行樣本檢誤、不合理值檢誤與邏輯檢誤,可以發現,不論是利用哪一種軟體,其基本原理大致相同,最重要的是,研究者要非常熟悉問卷結構及前後邏輯關係,並且能夠細心與耐心地條列出各種需要確認資料正確性的狀況。在此,我們希望能喚起研究者對資料檢核的重視,再次叮嚀您在正式分析資料之前,一定記得要利用您慣用的電腦軟體,依據本文提到的觀念,確實做好資料品質控制。


行政院主計處
問卷範例
 
臺灣地區人力資源訪問表(摘錄)
調 查 對 象:戶內年滿十五歲者
樣本編號:□□□□
1.與戶長之關係(如父、母、長女、次子、媳婦、孫女等)
1.(1)□戶長    (5)□父母        (9)□孫子女之配偶    (13)□其他親屬
(2)□配偶    (6)□祖父母      (10)□兄弟姊妹之配偶  (14)□其他
(3)□子女    (7)□兄弟姊妹    (11)□配偶之父母
(4)□孫子女  (8)□子女之配偶  (12)□配偶之兄弟姊妹
□□
2.性別
2. (1)□男           (2)□女
3.年齡
3.足歲                        
□□□
4.婚姻狀況
4.(1)□未婚                      (3)□離婚、分居
(2)□有配偶(含與人同居)      (4)□配偶死亡
5.教育程度(請填最高學歷)
5.(1)□不識字    (5)□高中     (8)□大學      (10)□博士
(2)□自修      (6)□高職     (9)□碩士
(3)□國小      (7)□專科(五專前三年劃記高職)  
(4)□國(初)中              (如圈選(6)(7)(8)(9)(10)請接6,餘轉7
□ □
6.學歷或考試之科系
6.(1)□文 (3)□商、管理 (5)□工 (7)□醫   (9)□教育        (接7
(2)□法 (4)□理       (6)□農 (8)□軍警 (10)□其他         
□ □
7.上週你主要在做什麼事?
7.在做    (1)□從事某種工作
工作    (2)□利用課餘或假期工作          (接8
(3)□家事餘暇從事工作
      (4)□有工作而未做(轉11
      (5)□無工作在找工作或已找工作在等待結果(轉14
      (6)□想工作而未去找工作(轉13
      (7)□求學及準備升學(兼有工作者圈(2)
      (8)□料理家務(兼有工作者圈(3)
(9)□高齡(65歲以上)、殘障               (停)
(10)□現役軍人、監管人口、失蹤人口
(11)□其他                       
□ □
8.上週你工作幾小時?
8.             小時(工時合計未達40小時者接9,餘轉19
□□□
9.上週你工作未達40小時的主要原因是什麼?
9.(1)□家務太忙                     (7)□工作本身不需40小時 
(2)□功課太多                      (8)□業務不振             
(3)□不願多做        (轉19    (9)□天氣惡劣或災害影響               (接10
(4)□傷病                          (10)□其他                
(5)□例假、事假、特別假(不含病假)      
(6)□季節關係                                  (接10
□□


[1] 文末列出於做為範例的部份問卷,供讀者了解變項意義。
[2]「學術調查研究資料庫」網址:http://srda.sinica.edu.tw/「相關資源」-->「參考文件」:2004年資料整理與檢核之實務。建議下載資料項目:
           1.課程講義
           2.EXCEL資料檔

留言

這個網誌中的熱門文章

使用Python進行資料整理 – 初探Pandas

SAS、SPSS、STATA 統計軟體檔案格式轉換介紹

資料整理與檢誤經驗談—以SPSS程式進行邏輯檢查