基于excel的數據管理及其在公共衛生領域內的應用*

李 瀟 王 駿,2 董國慶 羅 慶 李洪興Δ

在進行統計分析之前，原始數據需要錄入到計算中。進行數據錄入的軟件大致有：數據庫軟件如ACCESS、EPI info、Epidata軟件等[1]，電子表格軟件如excel及WPS Spread Sheet，以及統計軟件如SPSS的數據錄入模塊SPSS Data Collection Data Entry。數據錄入軟件的選擇，均應遵循便于錄入、便于核查、便于轉換和便于分析的原則。便于錄入是指盡可能地減輕錄入工作量，例如用數字代替文字信息，進行一些自動邏輯跳轉等；便于核查是指要有記錄的唯一標識碼，以方便數據和紙質報表之間的比對；便于轉換是指能方便地導出各種格式，方便同其他軟件進行數據交換；便于分析是指變量的設置、命名以及順序設置等符合數據分析的習慣和要求。文獻中關于第一種類型的錄入軟件報告較多，在公共衛生科研以及實際工作應用也比較廣泛，但這種類型的軟件在一些大型調查中有其缺點，主要是對于一些基層不熟悉計算機操作的用戶來說難度較大，需要進行培訓或在錄入過程中進行輔導。excel軟件作為一個小型的數據處理軟件，在基層有著廣泛的用戶，本研究嘗試使用excel并結合VBA編程技術開發了寄生蟲調查數據錄入系統。

原理與方法

1.數據錄入界面的開發

excel中的數據錄入界面可以通過數據記錄單或自定義窗體來實現，但數據記錄單功能過于簡單，而自定義窗體用戶操作起來不方便，而且開發的難度相對較大。本系統直接使用了excel的工作表作為用戶界面。通過鎖定單元格操作，可以在excel的SHEET里制作與紙質報表完全一致的數據錄入界面。如下圖所示，合并單元格(a1∶g1)、鎖定單元格(如a列)、設置單元格格式背景色等以突出顯示需要錄入的單元格(如b3∶b5)，通過這些設置可以模擬Epidata的錄入效果，同時可以保護一些區域，使得錄入人員無法修改錄入界面。

圖1 土源性線蟲病調查錄入軟件界面

2.數據錄入質控措施的實現

數據錄入過程中的質量控制對于保證數據的結果準確性，減少錄入錯誤，減輕數據清洗工作量有非常積極的意義。本系統通過以下方法來進行數據錄入的質控。

(1)數據有效性規則的設定

通過excel的數據有效性設定，可以對錄入字段的值域、字段屬性、字段長度、出錯提示等進行設定[2]。舉例說明如下，如將省份限定為“四川”、“湖南”和“廣西”，可以選中B3單元格，然后單擊數據有效性選項卡，在有效性里選擇序列，然后選中另一個區域，在該區域內填寫“四川”、“湖南”和“廣西”，即可生成下拉列表。同時，在設置輸入信息里寫入“請選擇省份”，在出錯警告里填寫“省份信息填寫錯誤，請重新填寫！”。這樣用戶鼠標單擊省份這個單元格時，會給出提示信息，當用戶沒有按要求填寫數據時，會給出警示性信息。同理可設置上表中性別、日期等變量。

(2)設定工作表保護，防止用戶修改錄入界面

通過鎖定工作表的一些操作，可以防止錄入用戶篡改用戶界面，在使用Epidata錄入時，存在用戶修改QES文件的情況，使用excel的鎖定工作表并加密的操作完全可以避免這些問題。單擊保護工作表，并輸入開發者編碼，便可以保護工作區。

(3)隱藏不必要的功能菜單

通過使用VBA代碼可以進一步隱藏菜單欄、工作欄、行號、工作表標簽等，防止用戶通過這些菜單來修改用戶界面，具體代碼如下：

Application.CommandBars(1).Enabled=False

′隱藏 工作表 菜單欄

Application.CommandBars(3).Enabled=False

′隱藏 常用工具欄

Application.CommandBars(4).Enabled=False

′隱藏 格式工具欄

Application.CommandBars(14).Enabled=False

′隱藏 visual basic工具欄

Application.CommandBars(15).Enabled=False

′隱藏 WEB工具欄

Application.CommandBars(59).Enabled=False

′隱藏 邊框工具欄

Application.CommandBars(8).Enabled=False

′隱藏 窗體工具欄

Application.CommandBars(11).Enabled=False

′隱藏 公式審核工具欄

Application.CommandBars(19).Enabled=False

′隱藏 繪圖工具欄

Application.CommandBars(16).Enabled=False

′隱藏 控件工具箱工具欄

Application.CommandBars(7).Enabled=False

′隱藏 審閱工具欄

Application.CommandBars(5).Enabled=False

′隱藏 數據透視表 工具欄

Application.CommandBars(6).Enabled=False

′隱藏 圖表工具欄

Application.CommandBars(56).Enabled=False

′隱藏 圖片工具欄

Application.CommandBars(17).Enabled=False

′隱藏 退出設計模式工具欄

Application.CommandBars(55).Enabled=False

′隱藏 藝術字工具欄

(4)通過VBA來實現更為復雜的數據錄入質控

通過VBA強大的定制功能，可以實現更為復雜的數據邏輯校驗，現舉例說明。

例1 單變量的校驗

以變量格式舉例，如D6單元格中的某變量要求為文本而非數字，具體的VBA寫法如下，當用戶輸入數字字符后，會彈出“數據校驗提示，請輸入文本，而非數字！”的窗體提示。

If Target.Column=4 And Target.Row=6 Then

If VBA.IsNumeric(Range(“D6”).Value)=True And Range(“D6”)<>“”Then

MsgBox“數據校驗提示，請輸入文本，而非數字！”

Range(“D6”).Select

Application.SendKeys“{F2}”

End If

End If

例2 變量間邏輯關系的校驗

如有兩個變量分別為A1和A2，業務邏輯為A1(比如農村人口)必須小于A2(比如總人口)，假設A1在B6單元格，A2在B5單元格，則兩者之間的校驗寫法為：

If Target.Column=2 And Target.Row=6 Then

If Range(“B6”)>Range(“B5”)Then

MsgBox“農村人口應小于總人口”

Range(“B6”).Select

End If

End If

例3 防止數據的重復錄入

利用VBA，可以實現一些更為復雜的數據校驗，如表間關系校驗、關鍵字段重復校驗等，如下面的代碼實現了問卷編號的必填校驗及重碼校驗：

If Range(“C8”)=“”Or Range(“C9”)=“”Or Range(“E9”)=“”Then

MsgBox“關鍵變量存在缺失，請檢查標紅色星號的變量是否錄入！”

Exit Sub

End If

TEST=Range(“C9”).Value & Range(“E9”).Value

For aa=1 To Range(“B5”).Value

Dim bb,cc As String

bb=Worksheets(“表3匯總”).Range(“G”& aa)

cc=Worksheets(“表3匯總”).Range(“H”& aa)

If bb & cc=TEST Then

If MsgBox(“已存在相同的編碼記錄，是否覆蓋？”,vbYesNo+vbQuestion,“系統提示”)=vbNo Then

Exit Sub

Exit For

End If

End If

Next

3.數據管理功能的實現

通過使用VBA，還可以操作數據記錄指針，完成對excel表格中已錄入數據的瀏覽、查詢和刪除等操作，實現數據管理的各種功能。

如以下代碼實現根據問卷的個案編號查詢記錄的功能：

Private Sub sear_Click()

Dim a As Long

Dim Rng As Range

Set hz=Worksheets(“表4匯總”)

For i=2 To 25

If hz.Cells(i,7).Value=Range(“e5”).Value And hz.Cells(i,6).Value<>“”Then

Exit For

End If

Next

y=i

If y=26 Then

MsgBox“沒有符合要求的記錄”

Else

Range(“B4”)=y-1

Call fill

End If

End Sub

4 數據交換、匯總與統計分析

當有多個用戶錄入數據后，可以使用VBA代碼將數據導出到為獨立的excel文件(脫離錄入軟件的excel文件)，同時，通過VBA定制數據批量合并功能，研究者或項目管理人員可以通過合并工具把這些數據方便地合并到一起，之后可進一步利用excel的數據分析功能進行統計分析。

結果與討論

上述方法建立的excel寄生蟲病調查軟件被應用于土源性線蟲病的現場調查數據錄入工作，基層用戶無需培訓或只需要簡單培訓就能完成數據的錄入，瀏覽、查詢、導入和導出等基本操作，大大提高了工作效率，減少用戶的學習周期，降低了操作難度。

文獻報導的流行病學調查錄入軟件很多，如Epidata、Epi Info、Access、VFP等數據庫軟件，這些軟件都各有利弊，但對基層具體承擔錄入工作的用戶來說都需要花費時間進行學習，并且有的軟件學習曲線還相對比較陡峭，特別是一些專業的數據庫軟件，用戶可能還需要掌握一些程序設計的基礎知識。因此研究者或項目管理者在組織調查數據錄入時還需要組織專門的培訓。Epidata作為一個小型的數據錄入軟件，隨著公共衛生調查數據量的增大，用戶操作系統和操作習慣的變化，逐漸顯露出其自身的一些問題和不足[1,3]。如：(1)數據結構的問題：Epidata使用Rec文件來存儲文件，這是一個文本文件，數據之間使用空格來分割，這樣的優勢是文件小讀取速度也很快。但缺點也隨之而來，比如一個位置的數據錯誤可能影響到整條數據，常見的斷電故障就是數據存儲方式的問題。在“大數據”日益盛行的今天，把數據放在一個文本文件里，并非一個很好的選擇。(2)變量數及行數的限制問題：Epidata對變量的個數和記錄的個數都有限制；另外，它不支持中文變量名，如果用作客戶端的錄入工具，客戶端把數據導出后不易明白變量的含義。(3)問卷管理的問題：在一項調查活動中，問卷并非一張，一般會是針對不同對象的問卷組。在實際工作，一組問卷常常裝訂在一起，我們通常會錄入組1的表1，表2，表3…，再錄入組2的表1，表2，表3；而Epidata的設計模式是，把所有的表1錄入完畢再錄入表2，也就是錄入表單之間的跳轉很不方便，當然也可以勉強實現跳轉，但非常牽強。(4)數據查詢的問題：如果數據量很大的時候，如何實現復雜的查詢，如查詢某日到某日之間的數據。(5)質控文件的加密：用戶惡意修改chk文件的問題，有的甚至把chk文件改個名字，逃避校驗。(6)復雜的表間校驗：如果出現某個變量跨表校驗，Epidata就無法滿足要求了。(7)錄入表單只支持幾種簡單的控件，Windows系統下的一些錄入控件，如listbox，checkbox等都不支持。

本文介紹的基于excel的數據錄入軟件不僅可以避免以上提及的Epidata軟件的不足，還具有如下的優點：(1)由于基層用戶大多熟悉excel的基本操作，當把錄入軟件分發給基層用戶時，用戶很快就能熟悉軟件操作，很少或基本不需要專門的培訓；(2)可以把基本的功能做成模塊，如數據校驗模塊、數據錄入模塊、數據導入導出模塊，這樣可以大大節省開發周期，可以快速定制開發專用的錄入工具；(3)最終版本的錄入文件很小，壓縮后便于分發，由于excel的普及，用戶不需要額外運行和安裝其他軟件就可以運行;(4)數據經過校驗后，數據文件直接存儲為excel文件，方便數據后期交換與統計分析;(5)數據查詢、替換、修改、篩選等操作都可以在excel中方便的進行。。

參 考 文 獻

1.陳剛,李平.數據錄入軟件Epidata.中國醫院統計,2006,13(1):91-93.

2.李小軍.VBA實現excel數據錄入有效性檢查.電腦學習,2009,6:44-46.

3.孫玉環.基于Epidata與SAS系統的紙質問卷數據錄入質量控制技巧.中國衛生統計,2012,29(4):607-611.