基于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.