一、需求
市场调查的目的是为管理和决策部门提供准确的、可靠的、有效的当前信息,有时是为了制定长远性的战略性规划,有时是为制定某阶段或针对某问题的具体政策或策略提供参考依据。
正确的决策不是靠直觉和猜测得到的,缺乏充分依据的信息,可能导致错误的决策,在市场经济越来越发达的今天,全方位多层次的市场调查活动越来越频繁。那么利用Excel能在市场调查活动中起到什么作用呢? 本示例通过设计问卷调查及回收统计系统,展示【表单控件】、【ActiveX控件】及VBA编程的综合知识。
二、解决方案
利用Excel中的【表单控件】和【ActiveX控件】,可以快速方便地制作出精美实用的调查问卷,并将各控件与单元格绑定,用户在控件中所做的选择将自动记录在单元格中。制作的完成问卷下图所示。

将被访者的回答记录统一汇总在一个工作表后,如下图所示。可以统计各个问题选项的回答人数,进而做出统计图形及分析,汇总调查数据。

为了详细阐述该示例,下述内容将围绕以下3方面展开:
- 制作问卷
- 录入、保存、汇总数据
- 统计数据
三、制作问卷
制作问卷将分4个部分讲述:
(1)设置工作表、制作问卷的基本框架
(2)利用【表单控件】设置单选题、多选题等
(3)利用【ActiveX控件】设置单选题、多选题、填空题等
(4)利用【ActiveX控件】各控件的事件代码,实现逻辑控件
(1)设置工作表、制作问卷的基本框架
按照以下步骤设置工作表、制作问卷的文本框。
新建Excel工作簿,新建4个工作表(右击工作表标签,sheet1选择【插入】命令),修改工作表的标签如下图所示。

各工作表的功能如下:
- Survey:问卷工作表,是用户录入问卷数据的窗口。
- InputData:记录工作表survey中被访者回答问卷的数据,第1行是标题,第2行是数据。
- Total:问卷的汇总数据。
- Static:将工作表Total中的采集的问卷数据汇总。
InputData工作表第1行是标题,从B列至T列,单元格内容及单元格所在列的数据内容类型如下表所示。
标 题 | 性别 | 年龄 | 教育程度 | 职业 | 月收入 | 功能满意度 | 外观满意度 | 价格满意度 | 是否使用过 | 改进功能 | 改进质量 | 改进能耗 | 改进外观 | 其它改进 | 其它改进说明 | 使用过YYY产品 | 使用过ZZZ产品 | 使用过AAA产品 | 没用过类似产品 |
内 容 类 型 | 数字 | 数字 | 数字 | 数字 | 数字 | 数字 | 数字 | 数字 | 数字 | True或False | True或False | True或False | True或False | True或False | 文字内容 | True或False | True或False | True或False | True或False |
InputData工作表部分如下图所示。

Total工作表的第1行与InputData工作表基本相同,只是将A列作为序号列,记录问卷编号。部分Total工作表内容如下图所示。

Static工作表的第1行与InputData工作表基本相同,只是将A列修改为“选项”,选项个数是问卷中某个选项最多的问题对应的选项个数。部分Static工作表内容如下图所示。

与InputData和Total工作表不同的是,Static工作表中单元格A2数值是1同时也代表答案是True,单元格A3数值是2同时也代表答案是False,这样自B至T列的单元格均为统计Total工作表的数值,而不再出现True或者False答案。
激活工作表Survey,准备制作问卷。首先在第1行中输入问卷名称“关于XXXX产品的市场调查”,根据实际工作需要设置适当的字体、字号及背景,如下图所示。

选择【插入】→【形状】命令,展现可以绘制的各种形状与箭头等,如下图所示。

单击形状列表的【文本框】按钮,鼠标即会变成插入形状,在问卷标题下按住鼠标左键不放,拖动至适当大小后释放鼠标,得到空白文本框以备输入调查问卷前言或背景说明,如下图所示。

在文本框中输入文字“您好:……”,调整文本的格式,利用【开始】选项卡中设置字体、字号等常规功能设置字体样式。

在文本框的边框上右击鼠标,弹出如下图所示的快捷菜单。选择【设置形状格式】命令,弹出【设置形状格式】对话框,如下图所示,可以设置文本框的边框中文字方向、内部边距等。

在设置文本框格式的快捷菜单中选择【大小和属性】命令,弹出如下图所示的【大小和属性】对话框,选择【属性】选项卡,选中【大小和位置均固定】单选按钮,这样文本框就不会受单元格大小变化的影响。本示例中所有的控件的设计都需要采用这一设置。

按照上述步骤的方法,制作另一文本框,输入文字“您的基本情况(必答题):”,如下图所示。
在该文本框的【设置形状格式】对话框中,选择【文本框】选项卡,通过设置文字与文本框左上角的距离,将文字定位在文本框的左上角
右击文本框内任一位置,在弹出的快捷菜单中选择【置于底层】→【置于底层】命令,如下图所示。这样下面在文本框内放置的控件就可以显示在顶层。文本框只起到一个框架的作用。

四、利用【表单控件】制作单选题、多选题等
1.制作单选题及相应分组框:性别、年龄、教育程度、产品满意度调查
具体的操作步骤如下: 单击菜单【文件】-【选项】。选中【自定义功能区】-【“开发工具”选项卡】复选框,然后单击【确定】按钮,选项卡【开发工具】即显示在选项卡栏中。

可参考文章:
OFFICE之门:Excel-VBA基础(1):如何在菜单栏显示“开发工具”
选择【开发工具】→【插入】命令,展现如下图所示的控件列表,其中分组框、单选按钮、复选框、下拉列表框、标签的使用频率最高。

单击控件列表中的【分组框】按钮,鼠标变成黑十字形状,在工作表的文本框“您的基本情况(必答题):”内,按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的分组框,如下图所示。

单击分组框的标题“分组框 1”,可以修改为需要的标题。例如“性别”

释放鼠标后,刚制作的选项按钮处于可编辑状态,此时可以直接修改选项按钮的标题文字。如果光标离开该控件,则控件失去焦点,处于未激活状态,只能通过右击该控件,在弹出的快捷菜单中选择【编辑文字】命令,才可以编辑选项控件的标题。本示例将选项按钮的标题修改为“男”,如下图所示。

修改文字后,因为文字比较短,可以将鼠标放在选项按钮边框上,待鼠标变为如下图所示的形状时,再拖动鼠标,将选项按钮放置在合适的位置。可以通过拖放选项按钮边框上的句柄,修改选项按钮的大小。

右击选项按钮,在弹出的快捷菜单中选择【设置控件格式】命令,弹出如下图所示的对话框。可以设置选项按钮的填充颜色,单击颜色的下拉列表框,选择【填充效果】,可以将【选项按钮】填充为渐变色。

选择【设置控件格式】对话框的【控制】选项卡,如下图所示,在【单元格链接】的地址框中输入数据存入的单元格地址,一般用绝对地址,即单元格地址的行和列前都加上“$”符号,或者单击【单元格链接】文本框右侧的地址选择按钮,然后在目标单元格上单击,目标单元格的地址即自动写入【单元格链接】文本框。如果数据存放在选项按钮所在的工作表之外的其他工作表,引用单元格地址时需要包含工作表名称的完整引用。如下图所示,性别“男”选项按钮对应的数据存储在InputData工作表的B2单元格中,单元格引用地址为InputData!$B$2。

重复步骤,制作“女”选项按钮及“年龄”、“教育程度”、产品满意度分组框及其内部的选项按钮,如下图所示。

五、制作下拉列表框及相应标签:职业、月收入
下拉列表框在问卷制作中常用于项目非常多的单选题,利用下拉列表框可以节省空间,使问卷看起来更加整洁大方,制作步骤如下:
(1)选择【开发工具】→【插入】命令,在展示的表单控件列表中单击【组合框】按钮,鼠标变成形状,在工作表的文本框“您的基本情况(必答题):”内,按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的组合框,如下图所示。

(2) 右击下拉列表框区域,弹出快捷菜单,选择【设置控件格式】命令,弹出如下图所示的【设置控件格式】对话框。

(3)在当前工作表Survey单元格IT5:IT14和单元格IV5:IV14中录入下图所示的数据。

IS5:IS14单元格数据作为计数参考,本示例中不使用IS5:IS14中数据。选择【设置控件格式】对话框的【控制】选项卡,单击【数据源区域】文本框右侧的地址选择按钮,选择下图所示的单元格区域IT5:IT14。选择后的文本框如下图所示

(4)【单元格链接】中指定的单元格是存放下拉列表框被选中列表项目的数值,单击上图中的【单元格链接】文本框右侧的地址选择按钮,选择InputData工作表的单元格E2,按Enter键确认后,结果如上图所示。
(5)定义下拉列表项显示第一屏的项目条数,通过设置上图中的【下拉显示项数】来实现,例如“职业”的下拉列表项目有10项,如果【下拉显示项数】的值设置为8,则单击“职业”下拉列表框时,第一屏显示只有8项,其余2项需要利用垂直滚动条才能看到,如下图所示。

如果将【下拉显示项数】设置为10,则单击“职业”下拉列表框时,第一屏显示全部10项信息,垂直滚动条将不再显示。
(6)选择【开发工具】→【插入】命令,在展示的表单控件列表中单击【标签】按钮,鼠标变成黑十字形状。在工作表的文本框“您的基本情况(必答题):”内,按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的标签,如下图所示。

(7)单击标签,即可修改标签的标题,将“标签 1”修改为“职业:”,调整边框上的句柄,可以改变标签的大小,鼠标拖动标签边框,可改变标签的位置。修改后的标签和下拉列表框如下图所示。

(8)重复步骤,完成月收入的标签和下拉列表框制作,如下图所示。

六、制作复选框及分组框
具体的操作步骤如下:
(1)选择【开发工具】→【插入】命令,在展示的表单控件列表中单击【分组框】按钮,鼠标变成黑十字形状。在工作表问卷右下角空白处按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的分组框。单击标题,修改为“您认为XXX产品需要改进的地方是:”,如下图所示。

(2)选择【开发工具】→【插入】命令,在展示的表单控件列表中单击【复选框】按钮,鼠标变成黑十字形状。在分组框“您认为XXX产品需要改进的地方是:”中,按住鼠标左键不放,拖动至适当大小时释放鼠标,得到复选框,如下图所示。

(3)单击复选框标题,将默认标题“复选框 1”修改为“功能”。调整边框上的句柄,可以改变复选框的大小,鼠标拖动复选框边框,可改变复选框的位置。修改后的复选框如下图所示。

(4)重复步骤2-3,完成质量、耗电量、外观、其他复选框的制作,如下图所示。

七、利用【ActiveX控件】制作多选题、填空题等
ActiveX控件列表中有单选按钮、复选框、下拉列表框、标签、命令按钮、文本框等常用控件,修改ActiveX控件与表单控件不同,需要选择【开发工具】→【设计模式】命令,进入设计模式后,才能在控件上右击,选择查看【属性】或【查看代码】项目,在【属性】中可以修改控件的标题、外观等,【查看代码】可以修改控件对应的事件代码。再单击【设计模式】,使控件退出设计模式,进入运行模式,这时只能单击运行控件,而不能对控件作修改。
1、制作单选题和相应分组框
(1)选择【开发工具】→【插入】命令,在展示的表单控件列表中单击【分组框】按钮,鼠标变成黑十字形状,在工作表问卷右下角空白处按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的分组框,单击标题,修改为“请问您用过与XXX产品类似的产品吗?”。
(2)选择【开发工具】→【插入】命令,在展示的ActiveX控件列表中单击【选项按钮】按钮,鼠标变成黑十字形状。在分组框“请问您用过与XXX产品类似的产品吗?”中按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的选项按钮,如下图所示。

(3)右击选项按钮,弹出选项按钮快捷菜单。选择【属性】项目,弹出如下图所示的【属性】对话框。

(4)修改【Caption】项目值为“用过”,选项按钮的标题即被修改,如下图所示。

(5)调整选项按钮边框上的句柄,改变选项按钮的大小,鼠标拖动选项按钮边框,可改变选项按钮的位置。
(6)重复步骤2-5,制作选项按钮“没用过”,如下图所示。

2、制作多选题和分组框
(1) 选择【开发工具】→【插入】命令,在展示的表单控件列表中单击【分组框】按钮,鼠标变成形状。在工作表问卷右下角空白处按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的分组框,单击标题,修改为“您用过与XXX产品类似的产品是:”。
(2) 选择【开发工具】→【插入】命令,在展示的ActiveX控件列表中单击【复选框】按钮,鼠标变成形状,在分组框“您用过与XXX产品类似的产品是:”中按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的复选框,如下图所示。

(3)右击复选框,在弹出的快捷菜单中选择【属性】命令,如下图所示。

(4)修改【Caption】项目值为“YYY产品”,复选框的标题即被修改,调整复选框边框上的句柄,可以改变复选框的大小,鼠标拖动复选框边框,可改变复选框的位置。修改后的复选框如下图所示。

(5)重复步骤2-4,制作复选框ZZZ产品、AAA产品、以上都不是,如下图所示。

3、制作文本框
在前述制作分组框“您认为XXX产品需要改进的地方是:”,其中的复选框“其他,请注明:”后多了一个文本框控件,需要使用ActiveX控件,制作步骤如下。
(1)选择【开发工具】→【插入】命令,在展示的ActiveX控件列表中单击【文本框】按钮,鼠标变成黑十字形状。在表单控件“其他,请注明:”后,按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的文本框,如下图所示。

(2)调整文本框边框上的句柄,可以改变文本框的大小,鼠标拖动文本框边框,可改变文本框的位置。
八、设置【ActiveX控件】事件代码
制作完问卷后,需要设置各问题间或问题内部的逻辑关系,以免被访者出现错误回答。具体设置如下。
1、利用控件的Click事件,实现复选项的互相控制
分组“您用过与XXX产品类似的产品是:”中,当被访者选择排它选项“以上都不是”时,复选框“YYY产品”、“ZZZ产品”、“AAA产品”的无论是否已被选择,要全部置为未选择状态。反之,当复选框“YYY产品”、“ZZZ产品”、“AAA产品”任一产品被选择,则排它选项“以上都不是”要被置为未选择状态。
根据这一逻辑条件,需要在复选框“以上都不是”的Click事件中加入判断代码,从而设置本分组中其他复选框的状态,步骤如下:
(1)在Excel窗口中选择【开发工具】→【设计模式】命令,使ActiveX控件进入设计模式。
(2)在复选框“以上都不是”上右击,弹出如下图所示的快捷菜单。

(3)选择【查看代码】命令,进入VBE编辑窗口,系统自动为该复选框创建了Click事件过程:
Private Sub CheckBox4_Click()
End Sub
(4)在其中输入程序代码。
在过程中首先用语句“If CheckBox4.Value = True Then”判断复选框“以上都不是”是否已被选中,如果满足条件“CheckBox4.Value = True”,则将其他复选框的值设置为未选择(通过查看控件属性可以得到其他复选框的控件名称)。
Private Sub CheckBox4_Click()
If CheckBox4.Value = True Then
CheckBox1.Value = False
CheckBox2.Value = False
CheckBox3.Value = False
End If
End Sub
(5)右击复选框“YYY产品”,在弹出的快捷菜单中选择【查看代码】命令,进入VBE窗口中控件的Click事件代码。
Private Sub CheckBox1_Click()
End Sub
在事件代码中加入判断语句“If CheckBox1.Value = True Then”,判断本复选框是否已被选择,如果满足条件“CheckBox1.Value = True”,则设置排它选项“以上都不是”为未选择状态,完整代码如下:
Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox4.Value = False
End If
End Sub
同理,设置复选框“ZZZ产品”、“AAA产品”的Click事件过程如下:
Private Sub CheckBox2_Click()
If CheckBox2.Value = True Then
CheckBox4.Value = False
End If
End Sub
Private Sub CheckBox3_Click()
If CheckBox3.Value = True Then
CheckBox4.Value = False
End If
End Sub
以上是利用【ActiveX控件】中的复选框,实现调查问卷中问题的选项间互相控制的详细过程。
2、利用控件的Click事件,实现问卷的单选题控制多选题
示例问卷中,问题“请问您用过与XXX产品类似的产品吗?”(暂命名为Q1)与 问题“您用过与XXX产品类似的产品是:”(暂命名为Q2)有逻辑控制关系。
Q1中选择“用过”的被访者才可以回答问题Q2,而选择“没用过”的被访者不能回答Q2,即Q2中的各个复选框全要变为灰色,不可单击。
利用选项按钮的Click事件过程实现这一逻辑控制,具体步骤如下。
(1)制作公用宏iniUsed。
当选项按钮“用过”、“没用过”被选中时,Q2中各个复选框都要被设置为未选择的状态,以备被访者选择Q2中各复选框,由于均要被选项按钮“用过”、“没用过”的Click事件设置,所以将Q2中各个复选框设置为未选择的状态的功能单独分解制作宏iniUsed,提高程序效率。
Sub iniUsed()
CheckBox1.Value = False '设置复选框“YYY产品”为未被选择状态
CheckBox2.Value = False '设置复选框“ZZZ产品”为未被选择状态
CheckBox3.Value = False '设置复选框“AAA产品”为未被选择状态
CheckBox4.Value = False '设置复选框“以上都不是”为未被选择状态
End Sub
(2)右击选项按钮“用过”,在弹出的快捷菜单上选择【查看代码】命令,进入选项按钮的Click事件过程(通过查看选项按钮“用过”的属性,可以看到控件名称是OptionButton1)。在其中输入Click事件代码。
Private Sub OptionButton1_Click()
End Sub
在事件过程中,首先使用语句“CheckBox1.Enabled = True”将Q2中的各个复选框设置为被访者可以选中的状态,再调用宏iniUsed将Q2中的各个复选框初始化,数值全部置为未选择状态,最后再记录选项按钮的状态值,1代表“用过”,2代表“没用过”,状态值写在InputData工作表的J52单元格中。
选项按钮“用过”Click事件代码如下:
Private Sub OptionButton1_Click()
'设置各复选框可以使用
CheckBox1.Enabled = True
CheckBox2.Enabled = True
CheckBox3.Enabled = True
CheckBox4.Enabled = True
iniUsed '将各复选框值设置为未选中
'记录复选框“用过”的值
ThisWorkbook.Worksheets("inputdata").Range("$j$52").Value = 1
End Sub
(3)重复步骤,完成选项按钮“没用过”Click事件过程。完整代码如下:
Private Sub OptionButton2_Click()
'设置各复选框不可以使用
CheckBox1.Enabled = False
CheckBox2.Enabled = False
CheckBox3.Enabled = False
CheckBox4.Enabled = False
iniUsed '将各复选框值设置为未选中
'记录复选框“没用过”的值
ThisWorkbook.Worksheets("inputdata").Range("$J$52").Value = 2
End Sub
3、利用文本框制作问卷中的半开放题
在问卷设计中,问题的选项“其他”,经常需要被访者注明具体内容,调查业界称之为半开放题。“您认为XXX产品需要改进的地方是:”分组框中,“其他,请注明:”后的文本框即是半开放题,如下图所示。

当注明了其他项目的具体内容后,“其他”复选框也需要被选中,这样才符合逻辑。半开放题的制作步骤如下。
(1) 右击上图中“其他,请注明:”复选框后的文本框,在弹出的快捷菜单中选择【查看代码】命令,进入VBE窗口。文本框的名称是TextBox1,默认进入文本框的Change事件:
Private Sub TextBox1_Change()
End Sub
(2)需要使用的是文本框的LostFocus事件,即当文本框失去焦点时判断文本框的内容是否为空,所以在代码窗口的上方事件下拉列表中,选择文本框TextBox1的LostFocus事件,系统自动生成事件过程:
Private Sub TextBox1_LostFocus()
End Sub
(3)输入LostFocus事件代码。
在LostFocus事件过程中,首先利用判断语句检查文本框的内容是否为空:
If Trim(TextBox1.Text) <> "" Then
如果不为空,则将文本框之前的“其他,请注明”(名称是CheckBoxes(5))复选框选中,将文本框内容赋予InputData工作表的单元格P52。如果内容为空,则将“其他,请注明”(名称是CheckBoxes(5))复选框设置为未选中。
输入的完整代码如下:
Private Sub TextBox1_LostFocus()
'判断文本框的数值是否为空
If Trim(TextBox1.Text) <> "" Then
'如果文本框内容不为空,将文本框前的复选框设置为已选中
ActiveSheet.CheckBoxes(5).Value = 1
'文本框的内容赋予单元格P52
ThisWorkbook.Worksheets("InputData").Range("$P$52").Value =
Trim(TextBox1.Text)
Else
'如果文本框内容为空,将文本框之前的复选框设置为未选中
ActiveSheet.CheckBoxes(5).Value = 0
End If
End Sub
九、汇总调查数据
录入完成的数据保存在InputData工作表第2行,被访者在工作表survey中回答完问题后,需要保存已回答的问卷数值,即将InputData工作表第2行的数据汇总到Total工作表中,可以在工作表survey中添加一个命令按钮,通过单击该按钮实现上述功能。具体操作步骤如下。
(1)选择【开发工具】→【插入】命令,在展示的ActiveX控件列表中单击【命令按钮】按钮,鼠标变成黑十字形状。在工作表问卷左下角空白处按住鼠标左键不放,拖动至适当大小时释放鼠标,得到添加的命令按钮。右击命令按钮,在弹出的快捷菜单中选择【属性】命令,修改【Caption】属性为“保存已录入数据并初始化问卷”。
(2)右击命令按钮,在弹出的快捷菜单中选择【查看代码】命令,进入VBE窗口,默认命令按钮的Click事件过程:
Private Sub CommandButton1_Click()
End Sub
(3)输入命令按钮的程序代码。
命令按钮“保存已录入数据并初始化问卷”过程的流程如下图所示。

编制要点说明:
首先使用语句“If ThisWorkbook.Worksheets("InputData").Range("b2").Value <> "" Then”检查问卷中的必答题是否已全部回答。此判断中根据实际需要可以加入更多的条件,只有当满足该条件后,才能进入数据的汇总阶段。
使用以下语句将需要汇总的数据选择并复制:
ThisWorkbook.Worksheets("InputData").Range("B2:T2").Select
Selection.Copy
找到汇总工作表Total的最后一行,将复制的单元格内容粘贴到最后一行,完成数据汇总。
汇总数据后需要清除已入录问卷的所有痕迹,为下一份问卷的录入做好准备。例如,调用初始化问卷的宏iniSurvey,用语句“ThisWorkbook.Worksheets("survey").Range("a1").
Select”将单元格定位到问卷的开头,以备录入。
命令按钮Click事件过程的完整代码及详细注释如下所示。
Private Sub CommandButton1_Click()
Dim lastRow As Long
Application.ScreenUpdating = False '关闭屏幕刷新
'判断必填题是否已回答,如果回答了必填题才能合并数据,此处可以加入更多判断
'根据调查项目的严格程序,可以对所有必填题都作判断
If ThisWorkbook.Worksheets("InputData").Range("B2").Value <> "" Then
'选择本次录入的问卷题目
ThisWorkbook.Worksheets("InputData").Select
ThisWorkbook.Worksheets("InputData").Range("B2:T2").Select
Selection.Copy '复制本次录入的问卷题目
ThisWorkbook.Worksheets("Total").Select '进入合并数据的工作表“Total”
'读出工作表"Total"的最后一行的行数
lastRow = ThisWorkbook.Worksheets("Total").Range("a1048576").End
(xlUp).Row
'在上一行行号的基础上增加1,即为新增加的行。给新增加的一行数据加上序号(
'已有数据最后一行的行号,因为第一行是标题行,所以序号自然是上一行的行号)
ThisWorkbook.Worksheets("Total").Cells(lastRow + 1, 1).Value = lastRow
'选择需要粘贴数据的目标单元格区域中的第一个单元格
ThisWorkbook.Worksheets("Total").Cells(lastRow + 1, 2).Select
ActiveSheet.Paste '粘贴已复制的问卷数据
'选择刚粘贴了数据的行
ThisWorkbook.Worksheets("Total").Rows(lastRow + 1).Select
'将剪切复制模式关闭,避免出现剪切复制任务窗格
Application.CutCopyMode = False
'激活survey工作表
ThisWorkbook.Worksheets("InputData").Select
'将已选择的单元格区域清空
Selection.ClearContents
'调用初始化过程,将问卷所有题目都归到初始状态,以备录入新问卷
iniSurvey
Else '如果必填项没有填写,不能保存问卷数据,并给出提示
MsgBox "请填写必填项。"
End If
Application.ScreenUpdating = True ' 打开屏幕刷新
'选择"survey"工作表的左上角单元格,以备用户继续录入
ThisWorkbook.Worksheets("survey").Select
ThisWorkbook.Worksheets("survey").Range("a1").Select
End Sub
其中调用宏iniSurvey,作用是将问卷所有题目都归到初始状态,以备录入新问卷。宏iniSurvey使用循环语句逐一设置控件的初始状态,并借用11.2.4节制作的宏iniUsed初始化4个复选框,代码相对简单,完整的代码及注释如下:
Sub iniSurvey()
Dim I As Long
'循环将工作表中的所有单选按钮都置于待选择状态
For I = 1 To ActiveSheet.OptionButtons.Count
ActiveSheet.OptionButtons(I).Value = False
Next
'循环将工作表中的所有下拉列表框都置于待选择状态
For I = 1 To ActiveSheet.DropDowns.Count
ActiveSheet.DropDowns(I).Value = 0
Next
'循环将工作表中的所有复选框(表单控件)都置于待选择状态
For I = 1 To ActiveSheet.CheckBoxes.Count
ActiveSheet.CheckBoxes(I).Value = False
Next
'将从ActiveX控件列表中拖动生成的4个复选框置于待选择状态
iniUsed
'将从ActiveX控件列表中拖动生成的2个单选按钮置于待选择状态
OptionButton1.Value = False
OptionButton2.Value = False
'将从ActiveX控件列表中拖动生成的文本框内容设置为空
TextBox1.Text = ""
End Sub
十、统计调查数据
可以在工作表survey中添加一个命令按钮
(1)与制作“保存已录入数据并初始化问卷”的命令按钮一样(选择【开发工具】→【插入】命令),在问卷工作表survey中制作一个新的命令按钮,将其【Caption】属性修改为“汇总统计”。
(2)右击命令按钮,在弹出的快捷菜单中选择【查看代码】命令,进入VBE窗口,对应的单击事件过程是:
Private Sub CommandButton2_Click()
End Sub
(3)在该过程中输入程序代码。
编制要点说明:
该过程的核心在于先按照调查数据汇总表Total的行循环,再按照列循环,调查数据统计表static第一列的选项号码对应调查数据汇总表Total中每一个问题的选项号码。例如汇总表Total C列是年龄,第4行的值是3(指被访者在年龄问题上选择第3项“26-35岁”),那么该单元格在统计时,即在统计表static第4行(代表选项值3)C列的单元格中加上1。
例外的只有“True”和“False”两种答案的复选框,所以统计表static第1列中的数值1(位于第2行)同时也代表数据汇总表Total中的数值“TRUE”,第1列中的数值2(位于第3行)也代表数据汇总表Total中的数值“FALSE”。
首先是确定问卷中选项数量最多的问题的选项值,设置第1列选项数值,在程序中设定以下循环语句的终值,本示例中采用10,其实用100也可以,只是不存在的选项对应的统计值为空而已。
For I = 2 To 10
wkSheet2.Cells(I, 1).Value = I - 1
Next
用循环“For I = 2 To wkSheet1.Range("a1048576").End(xlUp).Row”遍历Total工作表的所有行,每一行代表一份问卷答案。
再用循环“For J = 2 To wkSheet1.Range("a1").End(xlToRight).Column” 遍历Total工作表每一行的所有列。
这两层嵌套的循环将读出Total工作表中存储的所有问卷答案。
用语句“Select Case UCase(Trim(wkSheet1.Cells(I, J).Value))”判断所读Total工作表第I行第J列单元格的数值:
如果是“True”,则用语句“wkSheet2.Cells(2, J) = wkSheet2.Cells(2, J) + 1”将static工作表第2行(A列的选项数值是1,代表答案TRUE)第J列的单元格数值中加1;
如果是“False”,则用语句“wkSheet2.Cells(3, J) = wkSheet2.Cells(3, J) + 1”将static工作表第3行(A列的选项数值是2,代表答案False)第J列的单元格数值中加1;
如果是1~10的数值,由用语句“wkSheet2.Cells(2+1, J) = wkSheet2.Cells(2, J) + 1”(行号2根据1~10的数值顺序变化为2~11,因为A列第2列才是选项值1)
完成统计后,用语句“wkSheet2.Range("a2").Select”回到工作表static的A2单元格,以便统计者使用数据。
完整的程序代码及详细注释如下:
Private Sub CommandButton2_Click()
Dim wkSheet1 As Worksheet '定义工作表
Dim wkSheet2 As Worksheet
Dim I As Long, J As Long '定义循环变量
'将变量赋予具体的工作表
Set wkSheet1 = ThisWorkbook.Worksheets("Total") '问卷汇总数据表
Set wkSheet2 = ThisWorkbook.Worksheets("static") '问卷统计表
'激活问卷统计表
wkSheet2.Activate
wkSheet2.Range("b2:t100").Select '首先清空之前的统计数据
Selection.ClearContents
'根据实际设计的问卷中最大选项个数生成相应数字作为选项值
'本例用最大值10,也可以为100,只是多余的选项值没有什么意义
For I = 2 To 10
wkSheet2.Cells(I, 1).Value = I - 1
Next
'循环统计问卷汇总数据表的第一行
For I = 2 To wkSheet1.Range("a1048576").End(xlUp).Row
'循环统计问卷汇总数据表的第一列
For J = 2 To wkSheet1.Range("a1").End(xlToRight).Column
'判断问卷汇总数据表中每个单元格的数值
Select Case UCase(Trim(wkSheet1.Cells(I, J).Value))
Case "TRUE" '在问卷统计表的第2行相应列的单元格加1
wkSheet2.Cells(2, J) = wkSheet2.Cells(2, J) + 1
Case "FALSE" '在问卷统计表的第3行相应列的单元格加1
wkSheet2.Cells(3, J) = wkSheet2.Cells(3, J) + 1
Case 1 '在问卷统计表的第2行相应列的单元格加1
wkSheet2.Cells(2, J) = wkSheet2.Cells(2, J) + 1
Case 2 '在问卷统计表的第3行相应列的单元格加1
wkSheet2.Cells(3, J) = wkSheet2.Cells(3, J) + 1
Case 3 '在问卷统计表的第4行相应列的单元格加1
wkSheet2.Cells(4, J) = wkSheet2.Cells(4, J) + 1
Case 4 '在问卷统计表的第5行相应列的单元格加1
wkSheet2.Cells(5, J) = wkSheet2.Cells(5, J) + 1
Case 5 '在问卷统计表的第6行相应列的单元格加1
wkSheet2.Cells(6, J) = wkSheet2.Cells(6, J) + 1
Case 6 '在问卷统计表的第7行相应列的单元格加1
wkSheet2.Cells(7, J) = wkSheet2.Cells(7, J) + 1
Case 7 '在问卷统计表的第8行相应列的单元格加1
wkSheet2.Cells(8, J) = wkSheet2.Cells(8, J) + 1
Case 8 '在问卷统计表的第9行相应列的单元格加1
wkSheet2.Cells(9, J) = wkSheet2.Cells(9, J) + 1
Case 9 '在问卷统计表的第10行相应列的单元格加1
wkSheet2.Cells(10, J) = wkSheet2.Cells(10, J) + 1
Case 10 '在问卷统计表的第11行相应列的单元格加1
wkSheet2.Cells(11, J) = wkSheet2.Cells(11, J) + 1
End Select
Next
Next
wkSheet2.Range("a2").Select '选择问卷统计表左上角单元格
End Sub
上述内容比较难以理解的地方在于将static工作表第1列作为问卷选项代码,并且数值1同时还代表答案TRUE,数值2同时还代表答案FALSE,仔细考虑一下选项数值的代换,整个过程就一目了然了。
根据每个问卷回答项目的统计,可以制作出每个问题所有选项的频度统计图。
十一、总结与经验积累
除了上述的示例内容,再扩展一下思路。
本示例是让被访者回答一个问卷工作表,或者说是将其他介质的问卷录入到Excel工作表中。例如计算机辅助电话调查,即一边给被访者打电话询问问题,一边在问卷工作表中选择相应答案。有没有方法让每个被访者回答每一个工作簿中的问卷工作表,最后再汇总呢?答案是肯定的。思路如下:
将只包含已设计好问卷的工作表survey发放给所有被访者,并将被访者回答的问卷数据保护并隐藏起来,确保不被误删除或修改,被访者回答完问卷后,将工作簿统一发送给组织者,这时再用VBA程序,逐一打开所有工作簿,读出固定位置的问卷数据、统计,并做出图表。这种方法与本章示例相比,需要引用FileSystemObject技术,逐一打开所有工作簿。