excel 2010在统计中的应用——数据录入和整理

Excel 2010在统计中的应用统计数据的收集与整理预习一、 问卷的录入1、 在问卷回收后，请给每一份问卷编号，写在问卷右上角，以方便查询。2、 请给每一个问题编号，例如Q1、Q2等等3、 问卷录入例如：关于手机情况的调查问卷Q1:请问您现在是否有手机？ 1、有 2、没有（请跳答Q12题）Q2：请问您当初购买手机的原因？（多选，限选3项） 1、方便联络 2、追求流行 3、别人送的 4、同学间比较 5、手机价格下降 6、网内互打较便宜 7、其他Q3：请问您平均每月手机的话费约 元？Q3：请问您平均每月手机的话费约多少钱？ 1、20元及以下 2、2140元 3、4160元 4、6180元 5、81100元 6、101元及以上录入问卷，设计表格，“列”为问卷编号，“行”为问题编号，100份问卷单选题的编码与录入：给问题Q1的答案设置编码，例如答案“有”编码为1，“没有”编码为2问题Q3的每个选项题号就是一个编码16，为有序数据。若只是进行频率分析或交叉分析很方便，但如要计算各种统计量，就得将其由区间转换为组中值，才能进行计算或检验。多选题的编码与录入：多选题答案多个，编码和录入时要根据该题限制的答案数来保留列数，例如限选3项，多选题的答案编号为Q2_1、Q2_2、Q2_3填空题的编码与录入：若是数值型数据则将数值直接输入适当的列即可。数值型数据可不经任何转换，计算各种统计量例如均值、标准差、最大值、最小值等。若是答案多样的开放题，则需人工将答案分类整理编码后再录入到表格中。在问卷录入中可能会涉及到的小技巧（1） 填充 “编辑”>“填充”，再用填充柄逐个填充（2） 冻结窗格 使“问卷编号”列和“标题”行固定显示在A列和1行单击B2单元格，表示要将B2单元格左边的列和上方的行冻结“视图”>“窗口”，单击“冻结拆分窗格”二、 数据的清理1、 有效范围的清理：对数据中的奇异值进行清理问卷数据输入完成后，利用数据“筛选”功能，找出数据范围错误的个案。例如Q1是否有手机，选择答案只有两个编码“1”和“2”，若输入数据的Q1这列中找到了3、4、5等其它数据，则为错误数据，需要按照编号去重新检查问卷。（1） 进入筛选状态（2） 筛选数据。单击要处理的数据列单元格的下拉箭头按钮，单击输入的错误编码值（3） 单击确定即可找出该列中输入错误的数据行（4） 取消该列筛选，在该列的下拉菜单中单击勾选“全选”（5） 取消问卷筛选，在数据选项卡的“排序和筛选”组中单击“清除”按钮。2、 逻辑一致性的清理根据问卷中问题互相之间所存在的内在逻辑联系来检查前后数据之间的合理性。例如核对“是否有手机”与“平均月费”的数据。（1） 进入筛选状态（2） 筛选“是否有手机”等于“2”。（3） 在筛选后的基础上，在筛选“平均月费”不为“空白”另外“有手机”而未填“平均月费”也是一种错误。关于多项限选题的逻辑一致性问题，即多项限选题输入的编码值应互不相同。可以利用辅助列和公式实现。（1） 在多项限选题的右侧，插入一空白列（称为“辅助列”）。（2） 在“辅助列”的第1行，输入标题。本题输入“是否相同”（3） 在“辅助列”的第2行，输入核对公式。=IF(COUNTA(C17:E17)<2,"",IF(OR(C17=D17,C17=E17,D17=E17),"F","")如果多选题输入的编码个数在2个以下则无需核对，显示为“空”，如果核对3个购买原因中有两个相同，则显示错误信息“F”（4） 复制核对公式，利用填充柄（5） 降序排列“辅助列”，则有问题的问卷将显示在前，通过编号找到相应的原问卷，查看问题所在并加以更正。（6） 更正完成后可删除“辅助列”3、 清理重复输入的问卷同一份问卷被重复输入，在数据量大的情况下不易找出，可以利用“高级筛选”来去掉重复的部分。（1） 单击问卷数据的任意一个单元格。（2） 在“数据”选项卡的“排序和筛选”组中，单击“高级筛选”（3） 勾选“选择不重复的记录“，表示要将重复的记录排除（4） 单击选中“将筛选结果复制到其他位置“，并在“复制到”框中填写数据要复制到的起始单元格。三、 单变量的一维频率分析单选题的一维频率分布表可以利用Excel的COUNTIF函数和COUNTA函数实现。1、 COUNTIF函数的语法为COUNTIF(单元格区域，指定条件)该函数的功能是统计“单元格区域”中满足“指定条件”的单元格个数。2、 COUNTA函数的语法为COUNTA(参数1，【参数2】，)该函数的参数可以是数值、单元格或单元格区域。功能是统计非空单元格的个数。例如：数据“品牌支持率调查”，用COUNTIF函数求得支持各品牌的人数后，将其除以对该单选题的总回答人数，得到品牌支持率的分布情况。（1） 在问卷数据右边E1：H10区域输入标题，在E列输入品牌对应的编码16、输入品牌名称等。（2） 单击G2单元格，输入公式即可。（3） 向下复制G2公式，将求得人数。（4） 单击G8单元格，然后在“开始”选项卡的“编辑”组中，单击“自动求和”按钮，将自动得到公式“=SUM(G2:G7)”（5） 单击H2单元格，输入公式“=G2:$G$8”得到百分比，利用填充柄（6） 将H2单元格的格式设置为1位小数的百分比样式。单击H2单元格，在“开始”选项卡的“数字”组中，单击“百分比样式”按钮，再单击一次“增加小数位数”按钮。利用填充柄向下复制。（7） 在G10单元格输入公式“=COUNTA(A2:A200)”，统计非空单元格的格式，即根据问卷编号统计总调查人数。根据频率分布统计表绘制统计图绘制饼图的步骤：（1） 选取F1:F7（品牌倾向），然后在按住Ctrl键的同时，再选取H1:H7(百分比)，选取两个不连续的数据区域（包括标题“品牌倾向”和“百分比”）（2） 创建饼图，在“插入”选项卡的“图表”组中，单击“饼图”（3） 修饰饼图，“图表工具”>布局>标签>数据标签>其他数据标签>设置数据标签格式绘制柱形图的步骤绘制直方图的步骤例：某生产车间30名工人的日产零件数如下：试用函数FREQUENCY以组距为10进行等距式分组，第一组为100-110。148116128125129140109123137119127132114107124120135108113130110129132123118104123124140107解：第一步：在数据表格旁边输入分组的上限，要求输入值比上限小1。例如图中的“日产量 109 119 129 139 149”。第二步：工具>数据分析>直方图，“输入区域”是数据范围，“接收区域”是第一步中的分组数据范围，“输出区域”是表与图输出的位置。第三步：输出的直方图之间有空位，修饰图，鼠标点在直方图有色区域上，右键，选“数据系列格式”>“选项”>分类间距设置为零。调整输出图中的范围，将109改为100-109，相应地直方图坐标也会改变，再点直方图坐标调整坐标字体大小。