基于excel的數(shù)據(jù)管理及其在公共衛(wèi)生領(lǐng)域內(nèi)的應(yīng)用*

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

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

原理與方法

1.數(shù)據(jù)錄入界面的開發(fā)

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

圖1 土源性線蟲病調(diào)查錄入軟件界面

2.數(shù)據(jù)錄入質(zhì)控措施的實(shí)現(xiàn)

數(shù)據(jù)錄入過程中的質(zhì)量控制對于保證數(shù)據(jù)的結(jié)果準(zhǔn)確性，減少錄入錯(cuò)誤，減輕數(shù)據(jù)清洗工作量有非常積極的意義。本系統(tǒng)通過以下方法來進(jìn)行數(shù)據(jù)錄入的質(zhì)控。

(1)數(shù)據(jù)有效性規(guī)則的設(shè)定

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

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

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

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

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

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

′隱藏 數(shù)據(jù)透視表 工具欄

Application.CommandBars(6).Enabled=False

′隱藏 圖表工具欄

Application.CommandBars(56).Enabled=False

′隱藏 圖片工具欄

Application.CommandBars(17).Enabled=False

′隱藏 退出設(shè)計(jì)模式工具欄

Application.CommandBars(55).Enabled=False

′隱藏 藝術(shù)字工具欄

(4)通過VBA來實(shí)現(xiàn)更為復(fù)雜的數(shù)據(jù)錄入質(zhì)控

通過VBA強(qiáng)大的定制功能，可以實(shí)現(xiàn)更為復(fù)雜的數(shù)據(jù)邏輯校驗(yàn)，現(xiàn)舉例說明。

例1 單變量的校驗(yàn)

以變量格式舉例，如D6單元格中的某變量要求為文本而非數(shù)字，具體的VBA寫法如下，當(dāng)用戶輸入數(shù)字字符后，會(huì)彈出“數(shù)據(jù)校驗(yàn)提示，請輸入文本，而非數(shù)字！”的窗體提示。

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

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

MsgBox“數(shù)據(jù)校驗(yàn)提示，請輸入文本，而非數(shù)字！”

Range(“D6”).Select

Application.SendKeys“{F2}”

End If

End If

例2 變量間邏輯關(guān)系的校驗(yàn)

如有兩個(gè)變量分別為A1和A2，業(yè)務(wù)邏輯為A1(比如農(nóng)村人口)必須小于A2(比如總?cè)丝?，假設(shè)A1在B6單元格，A2在B5單元格，則兩者之間的校驗(yàn)寫法為：

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

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

MsgBox“農(nóng)村人口應(yīng)小于總?cè)丝凇?/p>

Range(“B6”).Select

End If

End If

例3 防止數(shù)據(jù)的重復(fù)錄入

利用VBA，可以實(shí)現(xiàn)一些更為復(fù)雜的數(shù)據(jù)校驗(yàn)，如表間關(guān)系校驗(yàn)、關(guān)鍵字段重復(fù)校驗(yàn)等，如下面的代碼實(shí)現(xiàn)了問卷編號(hào)的必填校驗(yàn)及重碼校驗(yàn)：

If Range(“C8”)=“”O(jiān)r Range(“C9”)=“”O(jiān)r Range(“E9”)=“”Then

MsgBox“關(guān)鍵變量存在缺失，請檢查標(biāo)紅色星號(hào)的變量是否錄入！”

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,“系統(tǒng)提示”)=vbNo Then

Exit Sub

Exit For

End If

End If

Next

3.數(shù)據(jù)管理功能的實(shí)現(xiàn)

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

如以下代碼實(shí)現(xiàn)根據(jù)問卷的個(gè)案編號(hào)查詢記錄的功能：

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 數(shù)據(jù)交換、匯總與統(tǒng)計(jì)分析

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

結(jié)果與討論

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

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

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

參 考 文 獻(xiàn)

1.陳剛,李平.數(shù)據(jù)錄入軟件Epidata.中國醫(yī)院統(tǒng)計(jì),2006,13(1):91-93.

2.李小軍.VBA實(shí)現(xiàn)excel數(shù)據(jù)錄入有效性檢查.電腦學(xué)習(xí),2009,6:44-46.

3.孫玉環(huán).基于Epidata與SAS系統(tǒng)的紙質(zhì)問卷數(shù)據(jù)錄入質(zhì)量控制技巧.中國衛(wèi)生統(tǒng)計(jì),2012,29(4):607-611.