实验八Excel 的VBA应用.docx

1、实验八Excel 的VBA应用实验八：Excel 2003的VBA应用【实验目的和要求】掌握Excel 2003VBA应用。【实验时数】2学时【实验内容】1. 新建文件夹按照实验一新建文件夹的方法，建文件夹，名为“实验8”，用来保存此次的作业。 2. 打开Excel2003，单击“文件”“保存”菜单命令，将此工作薄以“制作市场调查问卷.xls”为名保存在网络盘ncre（K:）wkskxx文件夹内的“实验8”文件夹。市场调查问卷在企业的生产和销售中均具有重要的作用，通过这种方式可以了解市场需求状况、消费者心态、产品销售状况等。此次实验运用Excel的VBA高级功能，制作电子版市场调查问卷，可以使

2、被调查用户在网上填写问卷，同时会自动地将问卷结果统计成数据清单，从而大大提高了调查问卷统计的效率。（1）调查问卷的具体内容（2）设置问卷标题 在“市场调查问卷.xls”工作薄中，将工作表“sheet1”重命名为“市场调查问卷”。 插入艺术字标题“口香糖市场调查问卷”。 单击“插入”“图片”“艺术字”，在“艺术字库”对话框选择一种合适的艺术字样式（如第3行4列）。 单击“确定”按钮，弹出“编辑艺术字文字”对话框，在“文字”框中输入标题“口香糖市场调查问卷”，字体（楷体-GB2312），字号（36）。 单击“确定”按钮，在出现的“艺术字”工具栏中单击“艺术字形状”按钮，从弹出的列表中选择“细上弯弧

3、”。 此时艺术字形状发生改变，适当调整艺术字的位置。如图8-1所示。（3）输入说明文字 单击“绘图”工具栏中的“文本框”按钮，在工作表上插入一文本框。 在文本框中输入说明文字，并设置字体为楷体-GB2312。利用鼠标拖动适当调整文本框的大小和位置。 选中文本框，单击鼠标右键，在快捷菜单中选择“设置文本框格式”，打开“设置文本框格式”对话框，如图8-2所示。在“颜色与线条”选项卡中，在“填充”选项组中的“颜色”下拉列表中选择“填充效果”选项。 在弹出的“填充效果”对话框，切换到“渐变”选项卡，在“颜色”选项组中选中“预设”单选按钮，在其右侧的下拉列表中选择“红木”选项，单击“确定”按钮。回到“设

4、置文本框格式”对话框，拖动“透明度”滚动条的滑块将其透明度调整为“”。如图8-3所示。单击“确定”按钮返回“设置文本框格式”对话框，然后在“线条”选项组中的“颜色”下拉列表中选择“无线条颜色”。设置完成的样式如图8-4所示。 图8-2 “设置文本框格式”对话框 图8-3 “填充效果”对话框图8-4 输入文字样式（4）使用分组框和单选按钮 在A13单元格中输入栏目标题“您的基本情况”。 单击“视图”菜单“工具栏”“窗体”，弹出“窗体”工具栏，在此工具栏中包含有多个按钮供用户使用。如图8-5所示。图8-5 窗体工具栏 添加分组框。单击“窗体”工具栏中的“分组框”按钮，此时光标变为“+”形状。按住鼠

5、标左键不放将其拖动至合适的位置释放，即可在工作表中添加一个分组框。默认情况下分组框左上角的文本文字为“分组框1”，将其重命名为“性别”。 添加选项按钮。单击“窗体”工具栏中的“选项按钮”按钮，按住鼠标左键不放，在“性别”分组框中添加一个单选按钮。将其命名为“男”并适当地调整其大小位置。图8-6 设置分组框和单选按钮 用鼠标右键单击选项按钮的边框，在快捷菜单中选择“设置控件格式”，在弹出的“设置控件格式”对话框，设置该单选按钮的填充效果为“红木”。 选中设置完成的选项按钮，单击“常用”工具栏的“复制”按钮，单击“粘贴”按钮复制一个相同的选项按钮，然后将其命名为“女”。如图8-6所示。 注意：单选

6、按钮添加完毕，单击工作表的其他位置可退出其编辑状态。要想使单选按钮再次进入编辑状态，应单击“绘图”工具栏中的“选择对象”按钮，即可进入编辑状态。（5）使用组合框为了方便用户输入“年龄、学历、职业、收入”等项目，这里使用组合框将其各个选项罗列出来供用户选择。 切换到sheet2工作表，将其重命名为“数据源”。在此工作表中的A1：D6区域输入“年龄、学历、职业、收入”等项目的数据源信息。如图8-7所示。图8-7 “数据源”工作表 切换回“市场调查问卷”工作表，选定E13单元格，输入“年龄”。单击“窗体”工具栏上的“组合框”按钮，在F13位置添加一个组合框。如图8-8所示。图8-8 添加组合框 用鼠

7、标右键单击该组合框，在快捷菜单中选择“设置控件格式”菜单打开“设置控制格式”对话框，切换到“控制”选项卡中。 将鼠标定位在“数据源区域”文本框中，单击其后的“折叠”按钮，此时该对话框即被折叠起来。单击“数据源”工作表中的A2：A6区域，这样有鼠标选中的区域就会出现在文本框中。单击“折叠”按钮还原对话框，在“下拉显示项数”文本框中输入“5”。如图8-9所示。 单击“确定”按钮返回工作表。取消组合框的选中状态，然后单击此组合框的下拉按钮，根据实际情况在下拉列表中选择相应的年龄段选项即可。 按照相同的方法再添加3个组合框，分别将其重命名为“学历”、“职业”和“收入”，并为其链接数据源工作表中相应的单

8、元格区域，然后适当调整各个项目的位置。如图8-10所示。图8-10 设置好的组合框样式（6）使用复选框在设置比如选择口香糖的品牌、标准以及吃口香糖的场合等题目时，被调查者一般都有多个选项，此时可以使用复选框来设置。 在“市场调查问卷”工作表的A20单元格输入“您是否吃过口香糖？（如果选择“是”，请继续下一题）”。在B22、D22单元格中设置两个单选按钮，方法如4。 在单元格A24中输入“您经常吃什么品牌的口香糖？（答案不超过五项）”。单击“窗体”工具栏中的“复选框”按钮，按住鼠标左键在工作表中拖动至适当位置，释放鼠标后即可添加一个复选框，其默认名为“复选框1”，将其更名为“箭牌”，并适当调整其

9、大小。 设置该复选框的格式：其填充效果为“预设颜色”下拉列表中的“金色年华II”。 根据实际需要复制7个相同格式的复选框，并将其更名为相应的口香糖品牌，设置效果如图8-11所示。图8-11 设置复选框 按照相同的方法设置选择口香糖的标准和吃口香糖的场合等题目的各个选项。如图8-12所示。图8-12 设置好的复选框的样式（7）制作市场调查问卷统计表市场调查问卷设计完成后，企业还需要对调查的结果进行统计，并对统计的结果进行分析，这才是制作市场调查问卷的最终目的。为了方便统计，可以设计一个自动统计调查结果的“市场调查问卷统计表”。制作完成的“市场调查问卷统计表”的基本模型如图8-13所示：图8-13

10、 “市场调查问卷统计表 打开“市场调查问卷”工作薄，将工作表sheet3重命名为“市场调查问卷统计表”。 在调查问卷中有单选题和多选题，一个单选题对应一个答案，一个多选题对应多个答案。为了便于记录，这里使用数字编号代表多选题的多个选项。这里以简捷的语言在工作表中输入问卷中每一个问题，以便于在一页中显示问卷中的所有题目。输入完毕可适当地调整字体大小、行高、列宽以及合并相应的单元格等。并添加边框。如上图8-13所示为其基本表格框架。（8）问卷和统计表之间的链接统计表创建完成之后还需要将其与调查问卷链接起来，只有这样才能实现调查结果的自动统计。 链接单选题 切换到“市场调查问卷”工作表中，选中“男”

11、单选按钮，单击鼠标右键在快捷菜单中选择“设置控件格式”菜单项将打开“设置控件格式”对话框，切换到“控制”选项卡中，在“值”选项组中选中“已选择”单选按钮。 将鼠标定位在“单元格链接”文本框中，单击工作表标签“市场调查问卷统计表”中的单元格B4，即可将链接的单元格显示在“单元格链接”文本框中。如图8-14所示。 单击“确定”按钮返回工作表中，此时如果在工作表“市场调查问卷”中选择的性别是“男”，在“市场调查问卷统计表”工作表单元格B4中显示的则为“1”；如果选中“女”，则在B4单元格中则为“2”。 按照相同的方法链接其他的单选按钮和组合框。如“年龄、学历、职业、收入、是否吃过”等项。 链接多选题

12、 切换到“市场调查问卷”工作表中，在“您经常吃什么品牌的口香糖？”题目中选中“箭牌”复选框，单击鼠标右键在快捷菜单中选择“设置控件格式”菜单项打开“设置控件格式”对话框，切换到“控制”选项卡中，在“值”选项组中选中“已选择”按钮，然后在“单元格链接”文本框中输入“市场调查问卷统计表！$H$4”。 单击“确定”按钮返回工作表中。此时如果已经选中“箭牌”复选框，在单元格H4中显示的则为一个或者多个“#”。选中此单元格可以发现在编辑栏中显示的是“TRUE”，即系统自动以“TRUE”和“FALSE”来表示复选框的选中和未选中状态。 按照相同的方法逐个将问卷中的其他复选框与统计表中的单元格相链接，并适当

13、地调整列宽，将结果全部显示出来。（9）添加按钮链接问卷与统计表之后，虽然此时统计表可以自动地记录调查结果，但是第一次填写的结果会被第二次填写的结果所覆盖，不能将每次的填写结果都记录下来。为了将每次填写的结果均记录下来，下面在表格中添加一个提交按钮。 切换到“市场调查问卷”工作表中，单击“窗体”工具栏中的“按钮”按钮，然后在表格的最下方拖动鼠标添加一个按钮，如图8-15所示。同时系统会自动地弹出“指定宏”对话框，如图8-16所示。 图8-15 添加按钮 图8-16 “指定宏”对话框 单击“新建”按钮打开【Microsoft Visual Basic】代码编辑窗口，即VBA窗口，用户可在此输入、编

14、辑以及运行宏。 根据实际情况，在【市场调查问卷统计表.xls-模块1（代码）】窗口中输入如下代码： 代码设置完成后单击“保存”按钮保存输入的代码，关闭VBE窗口返回工作表“市场调查问卷”中，将按钮重命名为“提交”。（10）统计并分析调查结果 假设要统计30份调查问卷，首先在“市场调查问卷统计表”工作表的“编号”列利用Excel的自动填充功能，完成编号的自动填充。 在工作薄中插入一新的工作表，将其命名为“统计结果”，然后输入列标题以及要统计的各个项目，如图8-17所示：图8-17 统计结果工作表 计算单选题的公式：选中单元格B3，输入公式“=COUNTIF（市场调查问卷统计表!$B$5:$B$3

15、4,A3）”，此公式计算统计表中显示出“1”（即性别为“男”）的统计结果。 利用鼠标拖动将B3单元格的公式复制到B4单元格，计算出性别为“女”的人数。 按照相同的方法为其他单选题（“年龄、职业、收入、是否吃过”）的统计结果设置相应的计算公式。 计算多选题的公式：选中单元格H3，输入公式“=COUNTIF（市场调查问卷统计表!H5:H34,”TRUE”）”，此公式计算统计表中“箭牌”复选框被选中的结果。 按照相同的方法在单元格H4、H5、H6、H7、H8、H9、以及H10中输入以下计算公式，分别计算各个品牌被选中的统计结果。=COUNTIF（市场调查问卷统计表!I5:I34,”TRUE”）=CO

16、UNTIF（市场调查问卷统计表!J5:J34,”TRUE”） =COUNTIF（市场调查问卷统计表!K5:K34,”TRUE”）=COUNTIF（市场调查问卷统计表!L5:L34,”TRUE”）=COUNTIF（市场调查问卷统计表!M5:M34,”TRUE”）=COUNTIF（市场调查问卷统计表!N5:N34,”TRUE”）=COUNTIF（市场调查问卷统计表!O5:O34,”TRUE”） 根据实际情况输入“标准”和“场合”多选题的统计公式。（11）组合相同的项目为了便于移动，可将“市场调查问卷”工作表中的各个项目的复选框组合起来。单击“市场调查问卷”工作表，切换到该工作表中。 单击“绘图”工

17、具栏的“选择对象”按钮选中“您是否吃过口香糖？”栏中的一个单选按钮，然后按住Ctrl键选中该栏目中的其他单选按钮，再从弹出的右键快捷菜单中选择“组合”“组合”菜单项。此时可将选中的单选按钮组合为一个整体。按住左键不放拖动鼠标可整体调整位置。如图所示。 按照相同的方法可将“市场调查问卷”工作表中其他栏目的复选框组合成一个整体。如图8-18所示。图8-18 组合（12）保护工作表为了保障调查数据的安全性，一般不允许任何人对设置好的调研问卷进行修改，为此可对工作表进行保护设置。 首先保护“市场调查问卷”工作表。单击“工具”“保护”“保护工作表”菜单命令。在打开的“保护工作表”对话框，如图8-19所示。在“取消工作表保护时使用的密码”文本框中输入自己定义的密码，单击“确定”按钮弹出“确认密码”对话框，在“重新输入密码”文本框中输入前面定义的密码，如图8-20所示。单击“确定”返回工作表即完成对工作表的保护。 图8-19 “保护工作表”对话框 图8-20 “确认密码”对话框 按照相同的方法保护“数据源”、“统计结果”工作表。至此完成“市场调查问卷”的设计。（13） 以上完成了一个网上市场调查的问卷设计，将此工作薄保存在“实验8”文件夹中。人力资源 - 企业的管家