admin管理员组文章数量:1530938
2023年12月28日发(作者:)
一、如何用
excel
统计调查问卷
有些人可能经常要对调查问卷进行统计,使用手写的方法(就是 在纸上写正字的方法) 虽然很方便, 不过不方便在一定条件下进行统 计,如在男士的条件下,使用诺基亚的手机有多少人。而用
excel
可以很方便得出此类统计结果,并且由于数据输入
excel
方便备查, 也可以导入
access
等数据库再进行较复杂的处理。另一方面,因为
excel
大部分人都会,所以可以几台电脑同时录入数据,汇总时只需 将数据复制到一个表中,简单快捷。
调查问卷的问题可以分为三类: 单选题、 多选题、主观 题。主观题比较难统计,本文不涉及这方面的。以下所说的用
excel
统计只涉及用
excel
算出答案个数和百分比的最基本的统计。 一、录入问卷原始数据
下面是一简化了的调查问卷:
手机使用的调查
1、您的姓别是:
1、先生
2、女士
2、您使用过的手机的品牌有:
1
、诺基亚
爱
2、摩托罗拉
3、索
4
、三星
5
、联想
6、其他
假设问卷有
10
份。录入数据如下图:
在此,有几个要注意的问题:
1
、录入数据前要对每张问卷进行编号 ,方便录入后问卷答案的备查, 也方便对数据排序后返回到原始录入的状态。
2
、录入时将问题的答案如
ABCD
等替换为
1234
,如上图。毕竟 录入数字比录字母要快得多。 因此在设计问卷时可考虑将问题的选项 设为
1234
,避免录入时头脑将字母转化为数字时出现错误。
3
、单选和多选题每道题的答案均只用一个单元格 ,这也是为了方便 录入。多选题的答案当然也可以分开,不过会影响录入速度,分开则 会方便以后使用其他软件对数据进行处理。
4
、如上图,将第一行冻结起来 ,使问卷数多时,录入时依然可看到 各个字段名,减少出错。 (冻结的方法 :选中要冻结行下边一行和要 冻结列右边一列的那个单元格
,
再从菜单里点窗口
--
冻结窗格就可以 了!
如要冻结第一行,只要点中
A2
单元格,然后菜单,冻结窗格)5、统计区暂时放在问卷原始数据的下面
o
6
、数据录入的两种方法 。一是直接在单元格中输入,左手方向键, 右手小键盘。 因为问卷的答案统一为数字, 所以直接输入也有较快的 速度。二是使用
excel
记录单输入,左手
Tab
键,右手小键盘。选 中数据区域的任意一个单元格,执行 数据T记录单”命令,打开记 录单”窗体(如下图),点 “新建”即可录入新记录,
Tab
键向下移动 光标,
Shift+Tab
向上移动光标,
Enter
键完成新记录的输入,并 自动打开新的录入窗口,方便下条记录的输入。
二、统计单选题
方法
1
、使用
countif
函数
如本例中,在单元格
B17
中输入
=COUNTIF($B$2:$B$11,1)
即可求出问题一中选第一个选项
的人的个数。 使用绝对引用可减少以后复制公式时出错, 当然也可以 不使用绝对引用。问题一
2
的方法同上。
方法
2
、使用自动筛选功能
点中数据区域的任意单元格, 然后菜单, 数据--
筛选--
自动筛选。 点中
B1
单元格右边的下拉按钮,选
1,此时可在状态栏看到 “在
10
条记录中找到
5
个”(如下图),将
5
填入
B17
就行了,减少了输 入公式的麻烦。然后再筛选
2,筛选完改回 “全部”,以免影响其他题 目的筛选。问卷有几百份时,需冻结字段行,才能使字段行和统计区 处在同一屏幕,方便进行筛选和输入筛选结果。如下图 三、统计多选题 方法
1
、使用
countif
函数
多选题的答案并不只含有
1
或
2
或
3
,而是几个,直接模仿使 用统计单
选题时的公式无效。因为
excel
默认将
12
、15
、
34
等字 符串看作数字串,
cooutif
无法算出数字串中含有几个
1
,几个
2, 但对文本格式字符串有效。所以 使用
coontif
前需将多选题的答案 数据转为文本格式 。( 转换方法 :突出显示数字列,然后使用 “数 据”->“分列”命令。在文本分列向导中,按两下
“下一步 ”,到达步骤
3,单击“列数据格式 ”下的“文本”,(如下左图)完成即可(效果如 下右图)。提示完成此向导后,可能会在一个或更多单元格中看到称 为错误指示器的小三角形。 如果感觉这些三角形令人分心, 则可以选
择此列, 然后通过仅删除一个三角形将其全部删除。 )接着,在
B20
输入公式
=COUNTIF(C2:C11,"*1*")
即可求出问题二的答案 中,含有选项
1
的个数。输入公式
=COUNTIF(C2:C11,"*1*5*")
,可求出含选项
1
和选项
5
的
答案的个数, 包括
15
,
125
,153
等。
注:只是将多选题答案的单元格格式改为文本,
countif
函数的结果 是不正确的。
方法
2
、使用自动筛选功能
同方法
1
,需要先使用 “分列”命令将数字转化为文本。之
后在多选题右边的下拉按钮中选 “自定义 ”,如下图,选 “包含 ”,右边 输入
1
或
2
或
3
可计出相应的个数。要求只含
15
,不包括
135
,
153
,可选择等于
15
。要求含
15
,包括
135
,153
之类,可选择 等于*1*5*
。
方法
3
、使用嵌套函数
不用改数据的格式,直接用公式。
含选项“
1”公式是
=SUMPRODUCT(ISNUMBER(FIND("
1 ", A1:A10 ))*1)
含选项“
2”公式是
=SUMPRODUCT(ISNUMBER(FIND("
2 ", A1:A10 ))*1)
含选项“
3”公式是
=SUMPRODUCT(ISNUMBER(FIND("
3 ", A1:A10 ))*1)
含选项“
5”公式是
=SUMPRODUCT(ISNUMBER(FIND("
5 ", A1:A10 ))*1)
含选项“
15
”公式是
=SUMPRODUCT(ISNUMBER(FIND(" 15 ", A1:A10 ))*1)
将上面公式中的黑体字改为相应的统计选项和统计区 域即可。不过上面求“15”的公式只能计算出完全是“15”的单元格的 个数,不包括
135
,156
之类。
注:上面的公式是我在百度知道上提问的答案( 感谢百度网友),公 式比较复杂,逻辑上个人还不是很懂,不过试了之后,答案是正确的。 推荐用方法1和2。
参考网址:
四、 有条件的筛选
如要求在男士中使用诺基亚的手机有多少人, 同样使用自动筛选
功能,可先对问题一进行筛选,然后再对问题二自定义筛选,选包含
1。具体参考上面的方法2。排序,分类汇总,高级筛选,数据透视 表等功能也是值得挖掘的功能。
五、 百分比算法
这个是最容易的。例:在
C17输入二B17/10
或二B17/$B$14
回车,得出小数,之后向下拖填充,接着把单元格格式改为百分比就 行了。
六、 尾声工作
1
、全部统计完后,最好将问卷的统计数字复制到另一个表中单 独保存,复制后,选 选择性粘贴”将数值保存下来,避免单元格位置 变动使公式的结果产生变化。
2
、因为在统计时统计结果是竖的,也可能是横的,本例中为竖, 此时可能需要转换一下横竖。即在
excel
中行转列
,
列转行。方法如 下:
EXCEL
中,首先复制欲转换的内容,然后选择编辑-选择性粘 贴-转置即可;若在
WORD
的表格,复制到
EXCEL
中,按照上述 操作,然后复制回来即可。
七、介绍一个调查问卷统计程序
网上有个调查问卷统计程序
2.0
,使用鼠标录入数据, 自动统计, 使用
access
文件作为数据库。装完后在其安装文件夹有详细的使用 说明,图文并茂。不过我对
access
不熟悉,不知如何多台机共同录 入数据并最终汇总, 并且程序默认的统计方法相比上面的方法还不是 很灵活,但也基本够用。
下载网址:
后记:我有时需要统计问卷,上网搜了之后,发现有这方面的问题, 但没这方面的文章,故写了这篇入门的文章。对自己来说,是对自己 经验的总结,希望也能对其他人有点点用。难免有错漏,望指出。
补充:使用字符串统计单选题。
将多道连续的单选题答案统一输入一个单元格中, 然后使用正文 中介绍的分列命令把数字串改为文本串。效果如下图: 接着使用
countif(1*)
统计第一题选
1
的有多少个。用
countif(?1*)
统计第二题选
1
的有多少个。 如此类推。
优点:减少录入数据时的按 键数。
PS:对问卷统计有兴趣的朋友可以再看一下本空间转载的一文
二、 调查问卷
Excel
全搞定
每年春节前后, 建筑工程施工现场相对比较清闲, 我们这些从事 建设工程质量监督的人员, 就抽空到居民家中, 开展一年一度的全市 住房状况调查工作。以前, 我们都是先向居民发放纸质调查表,由居 民填写后,再回收统计。这样做,既麻烦又不准确。今年,我们改用
Excel
制作的电子调查表,带上笔记
本电脑上门调查。这样做,既方 便了数据的统计处理, 又给居民一种新鲜感,
进一步激发了他们的参 与热情。
制作调查问卷的思路
在
Excel
中,我们先利用 “窗体 ”工具栏上的 “分组框、选项按钮、
复选框、组合框、按钮 ”等控件,制作成如图
1
所示的调查表,并将 每一位参与调查居民的选择结果保存在表格下方对应的单元格中。 然 后通过 “宏按钮”将选择结果依次复制到 “数据表”工作表中保存起来。 最后,利用函数和公式对数据进行统计处理, 并将统计结果保存在 “数
据统计 ”工作表中,随时查看调查的统计结果
实战制作调查表
在用
Excel
制作调查问卷前要进行一些准备工作, 以方便后面的制作 过程:
第一步:启动
Excel2003(
其他版本操作相似
),新建一工作簿文档, 取名保存
(如“工程调查
.xls
”。)
第二步
:
依次双击工作表标签
(sheet1
、sheet2
、sheet3)
,使其成 反白状态,再输入字符,将其重命名为 “调查表、数据表、数据统计 ”(见 图
2)
。
第三步
:
将
A1
至
L1
单元格合并后, 输入调查表标题
(
如“某某市住房 状
况调查表 ”;)将
A2
至
L2
单元格合并后,输入调查说明;并设置 好字体、字号等。
第四步:执行 视图T工具栏T窗体”命令,展开 窗体”工具栏(见图
2)。
好了,现在我们可以添加用于调查的各种控件了。
1.选项按钮的添加
:所谓“选项按钮 ”,就是我们通常所说的 “单选”按
钮。我们以 “年龄 ”调查项为例,来看看具体的添加过程
:
第一步:点击“窗体”工具栏上的 “分组框”按钮,然后在工作表中拖拉 出一个分组框,并将分组框名
(如“分组框
2”)修改为“年龄”(见图
3)
。
第二步:点击“窗体”工具栏上的 “选项按钮 ”按钮,然后在上述 “年龄” 分组框中拖拉出一个按钮来,并将按钮名
(
如 “选项按钮
4”)修改为相 应的调查项字符
(如“20岁以下 ”,见图
3)
第三步:重复上述操作,再添加若干
“选项按钮 ”。小提示 选中第
1
个 “选项按钮 ”,在按住
Ctrl
键的同时,拖动一下鼠标,复 制一个选项按钮,修改一下其中的字符即可快速制作出另一个
“选项 按钮”来。第四步
:双击其中任意一个 “选项按钮 ”,打开“设置控件格 式”对话框(见图
4)
,
切换到“控制”标签下,在 “单元格链接 ”右侧的方框中输入 “$C$50”,
确定返回。小提示
此步操作的目的是将 “年龄 ”调查项的选择结果保存在
C50
单元格中
(选择第1个、第2个……选项按钮”,该单元格分别显示出1、2……) 第五步
:调整好“分组框”、“选项按钮 ”的大小和位置。
2.
复选框的添加
:所谓“复选框”,就是允许一次性选择多个选项。我 们以
“存在哪些质量问题 ”调查项为例,来看看具体的添加过程
:
第一步
:制作一个 “存在哪些质量问题 ”分组框。
第二步
:点击“窗体”工具栏上的 “复选框 ”按钮,然后在上述 “分组框” 中拖拉出一个 “复选框 ”来,并将 “复选框 ”名(如“复选框
74”)修改为
相应的调查项字符
(
如 “屋面渗水 ”,见图
5)
。
第三步
:
双击刚才添加的第
1
个“复选框 ”按钮,打开 “设置控件格式 ” 对话框
(见图
4)
,切换到 “控制”标签下,在 “单元格链接 ”右侧的方框
中输入“$K$50‘
,确定返回。第四步:重复上述2、3两步操作,根 据调查内容,添加其他复选框。小提示
由于是 “复选框”,所以每一个 “复选框”的“单元格链接 ”地址是不同
的,需要逐一设置。第五步
:
调整好 “分组框”、 “复选框 ”的大小和位
置。
3.
组合框的添加
:
所谓“组合框 ”,就是我们通常所说的 “下拉框”。我
们以“学历”调查项为例,来看看具体的添加过程
:
第一步
:
在
L4
至
L11
单元格区域
(不一定非得是这个区域哟
)中,输 入各学历分类
(见图
6)
。
第二步
:
制作一个 “学历”分组框。小提示 其实,这里只有一个 “组合
框”,完全不需要添加一个 “分组框”。我们 之所以添加一个分组框,是为了保持调查表格式的统一。第三步
:
点 击“窗体”工具栏上的 “组合框 ”按钮,然后在上述 “分组框 ”中拖拉出一 个“组合框”来。
第四步:双击上述“组合框”,打开“对象格式”对话框(见图
7)
,在“数 据源区域”右侧的方框中输入“$L$4:$L$11
”,在 单元格链接”右侧 的方框中输入“$D$50‘
,确定返回。
第五步:调整好“分组框”、“组合框”的大小和位置
(见图
8)。
仿照上面的操作,完成其他调查项的制作过程。
保存调查数据
我们通过一个宏,将居民选择的结果
(显示在“调查表”工作表第
50
行相应的单元格中
)依次复制保存到 “数据表”工作表中,并通过一个 按钮来运行该宏。
第一步:切换到“数据表”工作表下,仿照图
9
的样式制作一张用来保 存数据的空白表格。
第二步:在
D1
单元格中输入公式
:=COUNTA(A:A)-2
,用于统计参 加调查的人数。
第三步:按“Alt+F11”组合键,进入“Visual Basic编辑器”窗口(见图
10)
第四步:在左侧 工程资源管理器”窗口中,选中“VBAProject(工程调 查.xls)选项,然后执行 插入T模块”命令,插入一个新模块。
第五步
:
将下述代码输入到右侧编辑区域中
(见图
10):
Sub
保存
()
Dim rs As Integer
Sheets("
调查表
").Select
Rows(50).Select
Sheets("
数据表
").Select
rs = Cells(1, 4)
Rows(rs + 3).Select
Cells(rs + 3, 1).Value = rs + 1
Sheets("
调查表
").Select
yMode = False
Range("A1").Select
MsgBox "
您的选择已经保存,感谢您对我们工作的支持,祝您新春 愉快!",
vbOKOnly, "
谢谢您"
End Sub
第六步:输入完成后,关闭 “Visual Basic
编辑器”窗口。 第七步:切换到“调查表”工作表中,点击 “窗体”工具栏上的 “命令”按 钮,然后在工作表中拖拉出一个 “命令 ”按钮来,此时系统自动弹出 “指 定宏”对话框(见图
11)
,选中上面编辑的 “保存”宏,确定返回。
第八步:将“命令”按钮名(如“按钮
75”)修改为“完成”字符。再调整好 按钮的大小,定位在调查表的右上角。
第九步
:同时选中
49
行和
50
行,右击鼠标,在随后弹出的快捷菜单 中,选择“隐藏”选项,将保存居民选择结果的两行隐藏起来;同时选 中
L
至
O
列,也将其隐藏起来。
小提示
将上述行、列隐藏起来的目的,只是为了美化调查表而已,如果不隐 藏,对调查表的使用没有任何影响。
第十步
:利用填充色等功能,对调查表进行一番美化。
至此,整个调查表制作完成。
开展调查 到了居民家中后, 切换到 “调查表”工作表下, 指导居民根据各自的实 际情况进行选择,选择完成后,点击一下右上角的 “完成 ”按钮,数据
即刻被保存到 “数据表 ”工作表相应的行中,同时弹出见图
12
所示的 一个对
话框,点击其中的 “确定 ”按钮进入下一个调查。
数据统计处理
我们利用
Excel
强大的函数和公式来完成调查数据的统计
处理工作
(这项函数和公式,我们完全可以在制作调查表的时候事先 制作好)
。我们以 “年龄”的统计为例,来看看具体的操作过程
:
第一步:切换到“数据统计 ”工作表中,仿照图
13
所示的样式,制作一 份空白表格。
第二步:分别选中E2、E3••…E7单元格,依次输入公
式:二COUNTIF(
数据表!C:C,1)、二COUNTIF(数据
表!C:C,2)……=COUNTIF(数据表!C:C,6)。
小提示
由于,我们在调查表中将年龄分为6种类型,分别用数字1、2 ....................
6
表示。因此,只要用COUNTIF函数分别统计数据表”
C列数值“1、
2……6的个数即可。
第三步
:
选中
F2
单元格,输入公式
:=E2/SUM($E$2:$E$7)
,确认 一下。再次选中
F2
单元格,将鼠标移至该单元格右下角成 “细十字 线状 ”时,按住左键向下拖拉至
F7
单元格,将上述公式复制到其中。 再同时选中
F2
至
F7
单元格区域, 单击一下 “格式”工具栏上的 “百分 比样式”按钮,将计算结果显示成百分比样式
(见图
13)
。
这种“细十字线状 ”,我们通常称之为 “填充柄 ”状态。
第四步
:仿照上述操作,完成其他数据的统计函数和公式的输入工作。
小提示
在进行 “有哪些质量问题 ”调查项的统计时,统计公式应该为
“二COUNTIF(数据表!K:K,"TRUE")
形式。这是因为, 复选框”选 中时,链接”的单元格显示为“
TRUE,复选框”没有选中时,链接” 的单元格显示为“FALSE或者空白。
其实,制作调查表,如果用
Microsoft Office InfoPath 2003
来实 现,可能更加方便,只不过其数据统计功能相对麻烦一些而已,感兴 趣的读者不妨一试。
版权声明:本文标题:怎样用excel统计调查问卷( 内容由热心网友自发贡献,该文观点仅代表作者本人, 转载请联系作者并注明出处:https://m.elefans.com/dongtai/1703756101a70344.html, 本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容,一经查实,本站将立刻删除。
发表评论