《统计学》实验指导书(3学分)

《统计学》实验指导书（3学分）
实验项目一：问卷数据的预处理
实验目的：
1. 掌握问卷在Excel中的录入方式；
2. 熟悉问卷数据的预处理。

实验要求和步骤：
一、学习问卷单选题、多选题以及开放题在Excel中的录入方法
1、单选题：
直接输入选择项A、B、C、D…等，或直接用1、2、3、4…数字表示选项，选中哪一项即在相应空格填上相应的字母或数字。

例：您的性别是（）： 1 男 2 女
其中：Q1、Q2…表示问卷的问题编号，第一列的1、2、3…表示不同的问卷。

2、多选题：
每个选项占一列，被选中记为1，未被选中记为0，若存在需要填写的文字则在相
应位置填写相应文字。

例：3、您光临本地的目的是（）
A商务会议单独一列，选中填1，没有选中填0
B学术研讨同上
C团体旅游同上
D婚礼宴席同上
E亲朋好友相聚同上
F其他_______ 单独一列，没有选中填0，选中直接将填写内容录入相应表格
若某人选择了DE，则录入情况如下：
Q3A Q3B Q3C Q3D Q3E Q3F
0 0 0 1 1 0
若某人选择了F，并填写内容为“工作调动”，则录入情况如下：
Q3A Q3B Q3C Q3D Q3E Q3F
0 0 0 0 0 工作调动
其中：Q3表示问卷的问题编号，A、B…等表示该题的选项，如Q3C则表示“团体旅游”
3、开放题：
例：10、请谈一下您对本地的印象__________
答案录入：在Q10 下方填写相应答案文字即可。

如：Q10
民风淳朴
二、学习对问卷数据进行检查
1、形式层面：录入的过程中及时进行数据有效性检查以防止问卷回答的非法值的出现例：您的性别是（）： 1 男 2 女（Excel性别一列录入的答案只可能为1或2）选中B2单元格，点击数据→数据的有效性，如下图：
在数据有效性的对话框中的“允许”菜单中选择“序列”，“来源”中输入“1,2”（以逗号隔开。

※“输入信息”选项中可以输入相关信息
※“出错警告”中可以根据需要选择相应选项，“警告”中还可以输入文字提示
2、逻辑层面：从逻辑一致性上再次核对数据是否有误或前后矛盾
例：5、您是否来过本地（）1、没有来过；2、来过
9、您为何选择本旅馆入住（）（多选题）
A、口碑好；
B、交通方便；
C、价格合理；
D、以前来过，感觉不错。

若第5题选择1，则第9题选项D被选中（记录为1）则存在逻辑性错误，需要在录入后进行逻辑检查，对出现逻辑性错误的问卷进行剔除。

在单元格中输入“=IF(M2=1,IF(L2=1,FALSE,TRUE),TRUE)”进行判断。

※小技巧：冻结窗口（视图→冻结窗口）
三、学习问卷数据的筛选（条件筛选）
数据→筛选→高级。

例：1、选出血型为O型的女性
若几个条件同时满足（“且”），则条件区域写在同一行
例：2、选出血型为O型或女性的问卷。

若几个条件只要满足其中之一即可（“或”），则条件区域写在不同行
作业：
1、选取小组调查问卷中至少两题（包含单选和多选），对至少五个被调查者的问卷答案
进行录入练习；
2、利用指定数据文件中的数据进行有效性练习和逻辑一致性判断；
3、利用指定数据文件中的相关数据练习高级筛选（条件筛选）。

实验项目二：问卷数据的整理与展示
实验目的：
1. 掌握用Excel进行分类和数值型数据整理（主要是出现次数）的方法；
2. 熟悉分段折线图、双饼图的制作。

实验要求和步骤：
一、学习用Excel进行数据整理（次数计算）
1、数值型数据：
例：对原始数据进行出生年份的统计，并计算每年出生的人数。

※用高级筛选列出所有出现的年份
※利用Frequency或countif函数计算各年份的人数
用Frequency函数：开始要选中数值产生区域，用组合键ctrl+shift+enter结束计算过程
用countif函数：逐行生成次数值，再将格式复制到其他行
2、分类数据
例：对原始数据中的血型进行统计，并计算每种血型的人数。

※用高级筛选列出所有出现的血型种类
※用lookup函数将分类变量血型转换成数值型（转换前先将筛选出的分类变量排序）※利用Frequency或countif函数计算数值型转换后的各血型人数
对筛选出的血型排序后进行数值型编码转换：
用lookup函数将原始数据中的所有血型数据进行数值型逐一指定：
（先将C2指定到D2中，再将E3:E6,F3:F6分别固定，双击一下D2即可得到其他值）
利用Frequency或countif函数计算次数结果：
二、熟悉分段折线图、双饼图的制作
1、分段折线图
例：将下表中上海市的能源消费总量按照2000~2005,2006~2010年分组作折线图展示
插入→折线图，生成空白图，右键“选择数据”，在选择数据源中点击“添加”按钮：
2、双饼图
例：某部门本月的支出如下表，请用双饼图更直观地展示。

做法：
※先将原始数据进行处理，确认各个饼要展示的内容，合计部分总和不应该出现在原始数据中，第二个饼的数据应该放在表的最后几行，处理后的数据如下：
选中原始数据→插入→复合饼图，得到初始饼图如下：
调整确认第二个饼图的内容。

第二个饼图代表工资总额，应该包括研发部、人力资源部、销售部、财务部4个部门，而默认生成的第二个饼只有3个部分，需要进行调整。

※选中饼图→右键“设置数据系列格式”→系列选项→第二个绘图区包含最后一个调整为4
可以再利用“添加数据标签”等选项对图进行进一步处理：
作业：
1、练习上述例题的操作过程，要求熟练掌握；
2、根据小组调查问卷的收集数据进行次数计算的练习；
3、自行选定国家（或某省市）统计年鉴部分数据，进行分段折线图和双饼图的练习。

实验项目三：描述统计工具的综合应用
实验目的：
1、熟悉相对指标的综合应用；
2、掌握数据分布特征的生成与各指标解释。

实验要求和步骤：
一、熟悉相对指标的综合应用
例：利用所学常见相对指标，对上海市近十年的GDP数据进行分析，并可以与其他地区进行适当对比，描述其发展趋势及现状。

※从上海市统计年鉴中选取近十年的GDP数据在EXCEL中整理成表格
※可以根据第一、二、三产业的数据与总值进行结构相对数的计算，并据此分析产业结构特征和变动趋势（可作图辅助分析）
※计算各年GDP的增幅（可计算动态相对数），并与国家各年计划增幅进行对比，计算计划完成相对指标
※可选取其他省市如江苏、浙江、北京等地相关数据进行比较相对指标的计算
※计算近十年GDP的平均增幅（几何平均数）
※将上述指标值结合图形体现的信息进行整理，并结合国家政策现状表述自己观点双坐标轴图的做法：
例：制作2000~2010年上海市各产业能源消费量的柱形图，并体现出第二产业所占比重的变化趋势。

选中表格除第一列外的其他数据→插入→柱形图
→右键“选择数据”→水平分类轴标签（编辑）→轴标签（选中原始数据的第一列2000~2010）→确定，生成初步图如下：
因第二产业比率因为以%为单位，无法显示在图中，用次级坐标轴展示更合理。

横轴上找到“隐藏”的第二产业比率数据，右键“设置数据系列格式”→系列格式“次坐标轴”→确定。

在返回的图中选中第二产业比率数据，右键“更改系列图表类型”→折线图，如下图：
二、数据分布特征的生成与各指标解释
例：根据50名学生统计学成绩分析其分布特征。

※将50名学生的成绩按列录入Excel（可根据需要决定是否排序）
数据→数据分析→描述统计（如无“数据分析”模块则需要先加载）
结果：
分数
平均 77.26
标准误差 1.384405862
中位数 77.5 众数 70
标准差 9.789227732 方差 95.82897959 峰度 0.267665526 偏度 -0.316862651
区域 45 最小值 50 最大值 95 求和 3863 观测数 50
置信度(95.0%) 2.782067687
作业：
理解上表中各指标值的计算原理，并能熟练应用。

实验项目四：相关与回归分析
实验目的：
1、熟练掌握Excel进行散点图的绘制及相关系数的计算；
2、熟练掌握运用Excel进行一元线性回归及多元线性回归。

实验要求和步骤：
一、散点图的绘制及相关系数的计算
例：根据所给数据分析连锁超市的规模（平方米）和年销售额（万元）之间的关系。

画出规模和年销售额二者的散点图。

（插入→散点图）
计算二者相关系数：数据→数据分析（若无此模块需要先加载）→相关系数
二、一元线性回归及多元线性回归的操作
1、一元线性回归
继续对上例中的规模与年销售额进行回归分析，建立一元回归模型并进行检验上图散点图中，选定散点，右键“添加趋势线”→线性：
建立一元线性回归模型：
数据→数据分析→回归
根据上图结果写出相应的方程表达式并进行检验及预测。

亦可参考残差图和拟合图判断拟合效果，如下：
2、多元线性回归（二元）
例：在上例基础上发现年促销费用对年销售额也有影响，试建立模型进行分析。

数据→数据分析→相关系数
建立二元线性回归模型：
数据→数据分析→回归（X值要集中在相邻的列）
根据上图结果写出相应的方程表达式并进行检验及预测。

亦可参考残差图和拟合图判断拟合效果，如下：
作业：练习课程讲解内容，并用Excel做课后练习题。