Excel在调查统计中的应用
【摘 要】本文结合《马钢职工在当前改革中思想状况调查统计表》的实例,介绍使用Excel 97在调查统计表的应用,使广大应用人员能够自己动手参与调查统计工作,从而进一步减少对高级程序员的依赖性。
【关键词】EXCEL 数据库 调查 统计

一、 前 言
目前,市场调查、思想状况调查、民意测验等各种调查统计十分流行,怎样将大量的调查表,归纳成有价值的统计表呢?对于某些大公司或企业,常采纳大型数据库 + 程序来完成,对于小公司这样的开销太大了,也不值得。美国微软公司开发的Excel电子表格软件,其强大的统计功能和编程能力,完全可满足各类调查统计表的生成,其简单易用性更能为广大办公自动化人员所接受,从而进一步减少对高级程序员依赖性。本文结合《马钢职工在当前改革中思想状况调查统计表》的实例,介绍使用Excel 97在调查统计表的应用。为了节省篇幅从1000份调查表中只选区30份数据,同时基本条件及调查内容也只选取了一小部分,但这并不影响说明问题。

二、 数据库的建立
目前调查表多采用选择答案方式让被调查人员填写,这样做有两个好处,一是节省被调查人员的时间;二是限制了答题范围,避免了答非所问的情况,同时也便于统计。在《马钢职工在当前改革中思想状况调查统计表》中就采用了类似于多项选择题和单项选择题混合方式,从附表2统计表中可看出。Excel中可将数据清单用作数据库即数据清单中的列是数据库中的字段,数据清单中的每一行对应数据库中的一个记录。因此只要将题目号作为列标志。用1 ~ 9数字作为选中的答题号,填写在题目号下即可。对于多种答案可采用"*"分隔,参见附表1所示,采用这种方式对操作员来说非常简单,只要选择小键盘即可操作,在录用数据时,使用Excel的[冻结拆分窗口]命令是必要的,可大大减少行列错位错误。 Excel数据清单合并相当简单,在两个数据清单间执行"复制"与"粘贴"操作即可。故可以几个操作员同时进行录用数据工作,最后合并到一张数据清单即可。

三、 统计表的制作
统计表一般需要作两件事,一是根据筛选条件,进行数据筛选;二是将筛选出来的数据进行统计处理,如:求和、求最大值、求平均值等,应用最广泛的是求和统计,以便查看某项所占的比例,便于决策人员作出决策。在本例中需要分别统计各层次职工对改革的看法,在统计表中为了能表达筛选条件,故采用高级筛选比较适用,这样筛选条件与统计结果可在一张表中表达出来,便于人员查看分析,如附表2所示。数据的统计是本例中的关键,Excel中提供大量的工作表函数,其中CountIF(Range,Criteria)工作表函数就是计算某个区域中满足给定条件单元格的数目,使用它可以完成本例中单项选择答题的统计,如:在统计表中第一题目第①答题单元格(即D8 单元格中)输入=CountIF(数据库!F3:F32,1)/SUBTOTAL(3,数据库!F3:F32),由于Criteria准则项只允许以数字表达式或以字符串形式来精确地表达条件,如"=1"或"> 1"或"1"等,不能表达模糊的条件,即字符串有"1"存在的概念。这样就不能将"1*2*3"多项选择表示方式统计出来,因此,CountIF工作函数在本例中不能完全应用,为了使统计工作简单这就需要我们自定义工作函数uCountIF(区域,模糊条件)如下: '模糊条件求和 '
用于多项选择数据统计求和 '
---------------------------------------
Function uCountIF(区域, 模糊条件)
For Each r In 区域.Rows
If r.Hidden = False Then
For 计数 = 1 To Len(r.Value)
If Mid$(r.Value, 计数, 1) = 模糊条件 Then
求和 = 求和 + 1
Exit For
End If
Next End
If Next
uCountIF = 求和
End Function
注:此处只给出了最常用的模糊条件"="即单元格中存在某值条件,其它模糊条件如"〈〉"不存在、"〉="存在大于且等于某值等,如需要可与笔者联系。
由上述自定义函数可见,Excel宏编写相当简单,而且除关键词或保留词之外,均可使用汉字。统计工作函数定义完毕,就可使用,使用时应注意题目号、答题号与公式之间的关系,如在"统计表"中的第6题①号答题单元格,即D18单元格输入=uCountIF(数据库!F3:K32,1)/ SUBTOTAL(3,数据库!F3:F32),即F18单元格输入=uCountIF(数据库!K3:K32,2)/ SUBTOTAL(3,数据库!F3:F32)。以此类推,分别填入相应的单元格中即可。采用指定名称可大大简化公式输入,具体作法是:选择"数据库"表单中的题目1到题目10数据区,即(F2:O32)→[插入]→[名称]→[指定]→选择名称在首行→确定。

SUBTOTAL(3,数据区)工作表函数是求筛选区总数,由于筛选记录总数对所有列来说是相同的,故我们只需求"题目1"的筛选记录总数即可。在数据清单下空一行的单元格中即F34单元格中输入= SUBTOTAL(3,题目1),并定义此单元格名称为"筛选总数",其操作为:选F34单元格→[插入]→[名称]→[定义]输入→"筛选总数"→"确定"。则上述第6题①号答题D18单元格公式输入= uCountIF(题目6,1)/筛选总数,F18单元格公式为= uCountIF(题目6,2)/筛选总数。

四、 保护公式
为了防止我们的统计公式不小心被修改,在公式验证无误时,可采用单元格保护功能将其保护。具体作法是:选择要输入的区域如:附表2中的筛选条件区B4:F4→[格式]→[单元格]→"保护"卡→去"锁定"复选框→"确定",[工具] →[保护] →[保护工作表] →"确定",注意如果输入"口令"一定要记住。由于Excel在缺省情况下,所有单元格均被"锁定",所以除筛选条件区外所有单元格均被锁定不可更改数据,从而保护了数据公式的修改。如需修改公式,可先撤消保护工作表,在修改公式数据,最后别忘了保护。

五、 统计自动化
Excel中的宏是你的不知疲倦的助手,它记录你的操作,并按你的操作过程工作,由于我们统计汇总常需要变换不同的筛选条件,来达到我们所需的统计数据,每次变化条件都要重复高级筛选操作过程,即易出错,又使人厌烦,我们可以将此过程交给Excel宏去处理。具体作法如下:
步骤1:在"统计表"中的筛选条件区:填写条件,如在性别栏下填写"男"。
步骤2:[工具]→[宏]→[录制新宏]→宏名:宏1(也可自己取名);快捷键输入"S";→确定。
步骤3:点取"数据库"表单→点取数据清单中任意单元格→[数据]→[筛选]→[高级筛选]→确认数据区域无误,否则重新选择数据区域→点入条件区域→点取"统计表"表单→选择B3:F4条件区域→确定。
步骤4:点取"统计表"→点"打印预览"按钮→点"关闭"按钮。
步骤5:点"停止记录"按钮,宏录制完毕。
由于自定义工作函数uCountIF与宏1均使用"数据库"表单中的数据清单,所以在执行宏时,如果Excel的[工具]菜单中[选项]中"重新计算"卡中设置为自动重算,就会因宏在执行高级筛选过程中,改变了数据清单的排列方式,导致uCountIF自动更新,从而发生函数与过程冲突。因此要对宏1进行修改。在高级筛选前禁止自动重算,高级筛选完毕后在重置自动重算,从而避免在高级筛选处理过程中进入uCountIF函数。修改后的程序如下:
Sub 宏1()
'
' 宏1 宏表
' Jarking Ule记录的宏1998-10-2
'
' 快捷键: Ctrl+s
Application.ScreenUpdating = False
Sheets("数据库").Select
Range("E15").Select
① Application.Calculation = xlCalculationManual
* Range("a2:o32").AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=Sheets("统计表").Range("B3:F4"),Unique:=False
② Application.
Calculation = xlCalculationAutomatic Sheets("统计表").Select ActiveWindow.SelectedSheets.PrintPreview

End Sub
注意: ①②均为人工增加的语句,由于宏1是自动记录,每次记录均不完全雷同,但只要抓住标有"*"的高级筛选这条语句即可。禁止"自动重算"语句放在高级筛选语句前,重置"自动重算"'语句放在高级筛选语句后。为了防止屏幕闪烁,在宏的第一行增加关闭屏幕更新语句Application.ScreenUpdating = False。关闭屏幕更新可以加快宏的执行速度,而且看不到宏的执行过程。

六、 结束语
本文给出Excel 97在调查统计中的应用简例,从上述例子中可以看出Excel 97 的简单容易性和强大的编程能力,虽然使用了一些少量的技巧,那只不过是使操作过程更加自动化而已,即使不使用这些技巧,完全可以通过手工方式来完成。我们可以看出Excel 97在调查统计中的应用,是一个随着用户对Excel 97的操作熟练程度,逐步从手工→半自动化→全自动化的渐变的过程,这就是Excel 97软件的魅力所在。
Excel 97是一个集数据表、工作函数、VBA应用程序和强大的报表处理于一身的强大工具。从理论上说,它完全可以完成统计领域95% 以上的工作,而且使你用最段时间和最少的精力去完成你的工作。在本例中也可以只使用工作表函数如=IF(ISERROR(FVINDL"2",题目1),0,1),将数据分离成新的数据清单,然后进行筛选统计,完全可以完成本例统计,只不过麻烦一点而已,你不妨试一试。 参考文献 [1] 希望图书创作室,中文版OFFICE 95速查手册,科学出版社,1997 [2] EXCEL 97 帮助文件Vbaxl8.hlp