统计学原理实验

实验一   统计指标

●熟悉EXCEL中的有关“统计”函数和“常用”函数，掌握其功能。

实验资料

50名学生的英语考试成绩如下：

59  61  61  62  63    84  74  74  74

85  86  65  66  70  71  72  67  68  69

69  72  72  73  75  75  91  91  95  75

75  76  77  78  79  80  81  82  83  84

86  87  88  90  97  99  50  51  54  58

实验步骤

1、打开一个EXCEL工作表并在A列中输入变量数列数据，并排序。在B单元列列中输入各组的分组上限，一般取“10”的倍数减1，下限则默认为“10”的倍数。并且在第一个数值上方的单元格中键入有关的标志名称，以便在输出图表的分析结果中定义数据的名称。

图1-1                                          图1-2

2、从工具菜单中选择数据分析命令，弹出统计分析对话框，双击“描述统计”，显示对话框。在输入区域填入引用数据的范围（拖动光标选择即可），并给出输出区域（可选择在原工作表或输出到新的工作表，但要防止工作表覆盖了原表的内容）。同时勾选标志这一选项框。

3、为得到分布特征值，勾选“汇总统计”。

4、单击“确定”，得到分布特征值。

图1-3

实验二   统计图表

1、直方图

（1）把实验一中的数据（A列、B列）复制到一个新的工作表中，从工具菜单中下拉选择数据分析/直方图，点击确定按钮，打开直方图分析选项框。在“输入区域”中输入原始数据的引用范围，本题为$A$1:$A$51（或直接用光标圈选A列的数据），此外，勾选“标志”选项。

图2-1

（2）在“接收区域”输入含分组标志值单元范围的引用，通常分组上限以升序排列。

（3）给出输出区域，勾选“图表输出”或“累积百分比”，得到图表。并可对其进行修改（光标移至图块处，单击右键进行所需要的改动）。

图2-2                                      图2-3

2、绘制图表

（1）建立新工作表，输入数据。单击“插入”中的“图表”或直接点图表快捷图标，选择“标准类型”中的“折线图”。定义“系列”要注意选择“分类X轴标志”，“系列”只有“国内生产总值”。

图2-4

接着选择坐标轴名称（包括单位）等，根据自己的喜好对图表进行修改，以最明了的方式显示出GDP的变化趋势。同样的方法，做条形图。示图如下。

图2-5                                        图2-6

（2）绘制圆形图和环形图。

建立一个新工作表，输入数据，如下图。选择“图表”中的“饼图”。第一步是对于“数据区域”和“系列”的定义，一定要依据题意定义的准确；然后定义“标题”、“图例”和“数据标志”，如图。

图2-7                                               图2-8

单击“完成”，然后依据自己的喜好修改图表，力求直观、美观、明了。同理，做环形图。

图2-9                                     图2-10

3、制作散点图。

实验三   时间序列

实验材料：

表1-1    我国股市1998.7～2001.6筹资金额和股票成交额一览表       单位：亿元

二、演示过程：

（一）测定增长量和平均增长量

第一步：在A列输入年份，在B列输入资料1中的国内生产总值数据。

第二步：计算逐期增长量：在C3中输入公式：=B3-B2，并用鼠标拖曳将公式复制到C3：C8区域。

第三步：计算累计增长量：在D3中输入公式：=B3-$B$2，并用鼠标拖曳公式复制到D3：D8区域。

第四步：计算平均增长量（水平法）：在C10中输入公式：=（B8-B2）/5，按回车键，即可得到平均增长量。

图3-1用EXCEL计算增长量和平均增长量资料及结果

（二）测定发展速度和平均发展速度

第一步：在A列输入年份，在B列输入资料1中国内生产总值数据。

第二步：计算定基发展速度：在C3中输入公式：=B3/$B$2，并用鼠标拖曳将公式复制到C3：C8区域。

第三步：计算环比发展速度：在D3中输入公式：=B3/B2，并用鼠标拖曳将公式复制到D3：D8区域。

第四步：计算平均发展速度（水平法）：选中C10单元格，单击插入菜单，选择函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：D8即可。

图3-2    用EXCEL计算发展速度和平均发展速度资料及结果

（三）长期趋势

第一步：进入EXCEL工作表，按列输入时间序号和资料2中的时间数列数据。通常将时间t放在A列，数据放B列和C列。在数据右边留出足够空间输出移动平均的结果。

第二步：从工具菜单中选择数据分析，再选择“移动平均”“确定”，出现该对话框，对各项进行选择。

其中，输入区域的确定可用光标的拖拉选定方式，选中B列，注意应包含数据列标志的名称的单元格。输出区域一般选紧贴原时间数列右边一列左上方的单元格。

图3-1                                         图3-2

第四步：在“间隔”中输入平均间隔跨度，一般只适用于奇数项移动平均，若进行偶数项移动平均，还需要再做一次间隔跨度为2 的移动平均才能完成（可采用Average函数对一次平均的结果再做一次移动平均）。注意：输出的新时间序列应从D8开始，所以还应该把数据上移至D8。最后，利用前面的制作图表的方法把原时间序列和二次平均后得到的新数列表现在一张图表中，进行比较分析。本资料移动平均的结果发现实际值和预测值偏离较大，这是因为我国股市在很大程度上受的影响比较大。但总体上是向好的。

图3-3                                       图3-4

（四）季节变动

第一步：输入资料3中的有关销售收入的数据。

第二步：计算四项移动平均：在D3中输入“=SUM（C2：C4）/4”，并用鼠标拖曳将公式复制到D3：D19区域。

第三步：计算趋势值（即二项移动平均）T：在E4中输入“=（D3+D4）/2”，并用鼠标拖曳将公式复制到E4：E19区域。

第四步：剔除长期趋势，即计算Y/T：在F4中输入“=C4/E4”，并用鼠标拖曳将公式复制到F4：F19区域。

图3-5用EXCEL计算季节变动资料

第五步：重新排列F4：F19区域中的数字，使同季的数字位于一列，共排成四列。

第六步：计算各年同季平均数：在B29单元格中输入公式：=average(B25:B28)；在C29中输入公式=average(C25:C28)；在D29中输入公式=average(D24:27)；在E29中输入公式=average(E24:E27)。

第七步：计算调整系数：在B31中输入公式：=4/sum(B29:E29)

第八步：计算季节比率：在B30中输入公式：=B29*$B$31，并用鼠标拖曳将公式复制到单元格区域B30：E30，就可以得到季节比率的值，具体结果见图3-6：

图3-6   用EXCEL计算季节变动结果

实验四   抽样估计

（一）Excel抽取样本常用函数 

1．索引函数INDEX

索引函数 INDEX( ) 有两种形式：数组和引用。

⏹数组索引函数的语法为：INDEX(array,row_num,column_num)

⏹引用索引函数语法：INDEX(reference,row_num,column_num,area_num) 

2．随机数函数RAND( )

⏹随机数函数RAND( )用于生成一个大于等于 0 小于 1 的均匀分布随机数，每次计算时都将返回一个新的数值。其语法结构为：RAND( )。

3．取整函数CEILING

⏹取整函数CEILING将参数 Number 沿绝对值增大的方向，舍入为最接近的整数或基数 significance 的最小倍数。其语法结构为：CEILING(number,significance)。其中：Number为待舍入的数值。Significance为基数。 

实验内容：从50名学生考试成绩中抽取7名学生成绩。

实验步骤：

1利用实验一“考试成绩”资料，建立工作表。

图4--1

2选择C2：C8单元格，在工具栏中单击“函数”快捷按钮，打开“粘贴函数”对话框，在“函数分类”列表中选择 “数学与三角函数”，在“函数名”列表中选择“随机函数“RAND”，打开随机函数对话框如下图所示。 

图4--2

3随机函数对话框中不需要填加任何数据，按住Ctrl+Shift键，单击“确定”按钮，在单元格C中将显示一组随机数。 

⏹下面将0和1之间的数转换为整数。

④将单元格C中的公式改为“ =50*RAND( )”，按住Ctrl+Shift键，单击回车键。区域C2：C8中的各数值均扩大50倍。同时工作表重新计算一次，各值均发生改变。

⑤选定D2：D8单元格区域，打开“插入”菜单，选择“函数”选项，打开“粘贴函数”对话框。在“函数分类”中选择“数学与三角函数”，在“函数名”列表中选择“取整函数CEILING”，打开取整函数对话框如下图所示 。

图4--3

⑥在Number中输入单元格地址C2：C8，在Significance中输入1，按住Ctrl+Shift键，单击“确定”按钮。

⑦重复按F9键，在D2：D8各单元格中将出现介于1和50之间的随机整数，即将要抽取的样本点。 

⑧选定单元格区域E2：E8，在“插入”菜单中选择“函数”选项，打开“粘贴函数”对话框。在“函数分类”列表中选择“查找与引用”选项，在“函数名”列表中选择“索引函数INDEX”，打开选定参数对话框如下图所示。

图4--4

⑨在选定参数窗口中，选择引用(reference)，单击“确定”按钮，进入索引函数INDEX窗口如下图所

示 

图4--5

⑩在Reference中输入A2：A51，为抽取样本提供区域。在Row-num中输入D2：D8，说明在    Reference区域中要选哪些值。按住Ctrl+Shift组合键，单击“确定”按钮，即得到随机抽取的样本容量为7的样本。每按一次F9键，工作表都将重新抽取一次样本。 注意：使用这种方法抽取的样本有可能是重复的。

（二）区间估计

实验材料：  

    某饭店在7星期内对49位顾客的消费额（元）的抽查调查资料： 

15  24  38  26  30  42  18  30  25  26  34  44  20  35  24  26  34  48  18  28  46  19  30  36  42  24  32  45  36  21  47  26  28  31  42  45  36  24  28  27  32  36  47  53  22  24  32  46  26。求在概率90%的保证下，顾客平均消费额的估计区间。

第一步：把资料1中的数据输入到A2：A50单元格。

第二步：在C2中输入公式“=COUNT（A2：A50）”，C3中输入“=AVERAGE（A2：A50）”，在C4中输入“STDEV（A2：A50）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.90，在C7中输入“=C2-1”，在C8中输入“=TINV（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3-C9”，在C11中输入“=C3+C9”。在输入每一个公式回车后，便可得到上面的结果，从上面的结果我们可以知道，顾客平均消费额的置信下限为29.73536，置信上限为34.2。

图4-1 参数估计数据及结果

关于总体方差的估计、总体比例的估计等可按类似方法进行。

实验五  相关与回归实验

一、简单线性相关分析

1．Excel进行相关分析：    

（1）输数据: 将数据输入A1:C9单元格。

（2）绘制散点图: 

图1  简单相关系数及散点图

2. 计算相关系数

(1) 选择工具菜单之数据分析选项, 在分析工具框中“相关系数”。

相关系数对话框将显示为图2所示，它带输入输出的提示。

图2 相关系数对话框

1) 输入

输入区域：B1:C9 

分组方式：逐列

选择标志位于第一行

  2) 输出选项

       输出区域: A13

(2)．单击确定，Excel将计算出结果显示在输出区域中。

4. 相关系数假设检验

(1)在单元格F14中输入公式 =B15/SQRT((1-B15^2)/(8-2)) 计算得相关系数的t值为49.46

(2)在单元格F15中输入公式 =TDIST(ABS(F14),B-2,2) 计算得 p=0.0001

(3) 结论: 由于r=-0.96, 且p<0.05, 所以, 在0.05水平上拒绝原假设, 认为产品产量与单位成本间有负的线性相关关系

二、 简单回归分析

上面的简单相关分析只是说明两变量之间的线性关系密切的程度，如果要建立它们之间线性依存的关系式，就需用回归分析。可按下列步骤使用“回归”分析工具：

1. 输数据: 将数据输入A1:C9单元格。

2. 回归分析: 

(1) 选择工具菜单之数据分析选项, 在分析工具框中“回归”。回归对话框将显示为图3所示，

图3  回归分析对话框

1) 输入

Y值输入区域：C1:C9 

X值输入区域：B1:B9 

标志: 选择

常数为零: 只有当用户想强制使回归线通过原点(0,0)时才选此框

置信度: Excel自动包括了回归系数的95％置信区间。要使用其他置信区间, 选择该框并在Confidence Levet框中输入置信水平

2) 输出选项

     输出区域: D1

  3) 残差

残差(R)：选择此框可得到预测值和残差(Residual)。

残差图(D): 选择此框可得到残差和每一x值的图表。

标准残差(T)：选择此框可得到标准化的残差,每一残差被估计标准误差除)。这一输出可使曲线较容易分层。

线性拟合图(I)：选择此框可得到一含有y输入数据和拟合的y值的散点图。

4) 正态概率图: 绘制因变量的正态概率图

(2)．单击确定，Excel将计算出结果显示在输出区域中。

图4  回归分析结果

3. 回归解释

  拟合回归线的截距和斜率放在图4的总结输出中标记有“Coeffients’’的左下部。截距系数77.30769是线性回归方程中的常数项，x系数-0.80769是斜率。回归方程是：

y＝77.30769-0.80769 * x

图5 残差及拟合线

在图5所示的残差输出中，预测 y，有时又称拟合值，是用这个回归方程计算的单位成本的估计值。残差是实际值和拟合值之间的差值。

回答“拟合关系怎么样”问题的最通用的四个方法是标准误差，R2，t统计值和方差分析。标准误差0.83205显示在图4的单元E7中。作为残数的标准偏差，它衡量单位成本在回归线周围的分散情况，标准误差通常称为估计标准误差。    

R2（R Square），如图4的单元E5所示，衡量用回归线解释的因变量变化的比例。这一比例必击是0和1之间的一个数据，经常以百分数表示。这里，约有的94％的单位成本的变化是在线性方程中用产品产量做为预测因子来解释的。单元E6显示的Adjusted R square在用附加解释变量把此模型和其他模型比较时很有用。

实验六  统计指数

一、实验材料：某企业甲、乙、丙三种产品的生产情况资料（见电子表格5）：

二、演示过程：

（一）总指数

第一步：计算各个p0q0：在G2中输入“=C2*D2”，并用鼠标拖曳将公式复制到G2：G4区域。

第二步：计算各个p0*q1：有H2中输入“=C2*F2”，并用鼠标拖曳将公式复制到H2：H4区域。

第三步：计算Σp0q0和Σp0q1：选定G2：G4区域，单击工具栏上的“Σ”按钮，在G5出现该列的求和值。选定H2：H4区域，单击工具栏上的“Σ”按钮，在H5出现该列的求和值。

第四步：计算生产量综合指数Iq=Σp0q1/Σp0q0：在C6中输入“=H5/G5”便可得到生产量综合指数

注意：在输入公式的时候，不要忘记等号，否则就不会出现数值。

图6-1   用EXCEL计算总指数资料及结果

（二）平均指数

第一步：计算个体指数 k=q1/q0：在F2中输入“=D2/C2”。并用鼠标拖曳将公式复制到F2：F4区域。

第二步：计算k*p0q0并求和。在G2中输入“=F2*E2”并用鼠标拖曳将公式复制到G2：G4区域。选定G2：G4区域，单击工具栏上的：“Σ”按钮，在G5列出现该列的求和值。

第三步：计算生产量平均指数：在C7中输入“=G5/E5”即得到所求的值。

图6-2   用EXCEL计算平均指数资料及结果

（三）因素分析

第一步：计算各个p0*q0和∑p0q0：在G2中输入“C2*D2”，并用鼠标拖曳将公式复制到G2：G4区域。选定G2：G4区域，单击工具栏上的“∑”按钮，在G5出现该列的求和值。

第二步：计算各个p0*q1和∑p0*q1：在H2中输入“=C2*F2”，并用鼠标拖曳将公式复制到H2：H4区域。选定H2：H6区域，单击工具栏上的“∑”按钮，在H5出现该列的求和值。

第三步：计算各个p1*q1和∑p1*q1：在I2中输入“=E2*F2”，并用鼠标拖曳将公式复制到I2：I4区域。选定I2：I4区域，单击工具栏上的“∑”按钮，在I5出现该列的求和值。

第四步：计算总成本指数：在C6中输入“=I5/G5”，即求得总成本指数。

第五步：计算产量指数：在C7中输入“=H5/G5”，即得产量指数

第六步：计算单位成本指数：在C8中输入“=I5/H5”即求得单位成本指数

图6-3  用EXCEL进行因素分析资料及结果

实验报告

实验的流程：

第一，根据主题设计调查方案，包括：1．调查目的  ；2．调查的范围和对象（样本的选择）；3．调查时间 ；4．调查内容 ；5． 调查人员；6． 调查的组织和方法 ； 7． 调查方法 ；8.填表说明  。等内容，可以根据自身的实际情况进行相关内容的调整。

  第二，设计问卷，规定问卷调查的时间和对象进行问卷调查，收集原始数据；设计调查问卷的问题一般不要超过10个左右，能正确反映主题即可。

  第三，整理数据，编制分布数列，进行表格内容的说明。

  第四，从所列内容中，选择一个方面进行数量分析。分析研究主题的数量关系和数量特征，画出分布曲线图。

第五，结合上述特征，进行针对性的对策性研究，即统计决策的分析，结合其他所学专业知识提出的未来发展和关注措施不得低于5条。

实验报告主题：

1.河南农业大学本科生参加学术活动情况调查，包括的内容：

第一，学校学术活动的现状和影响调查；

第二，学术活动的改进情况调查；

第三，学术活动的目的性调查；

第四，学生参与学术活动的原因；

第五，学生参与学术活动的时间、目的、参与率等；

可以根据实际情况进行内容调整，但是主题是必须反映农大本科生的参加学术活动情况的详细调查分析，主要分析学校学术活动和学生目的性、学生参与率等方面的关系分析。

2.2014级河南农业大学本科生基本情况调查

第一，学生生源地调查

第二，学生性别比例调查

第三，学生中独生子女率调查

第一，学生月生活费支出情况

第二，生活费用来源调查

目的正确描述河南农业大学学生的基本情况与其费用支出的相关关系，并初步探析其二者之间的数量关系和数量特征。最终有针对性地提出解决对策和方法。

3.2015级河南农业大学新生填报志愿情况调查

第一，学生生源地调查

第二，学生高考分数情况调查

第三，新生填报志愿情况调查

第四，选择河南农业大学及其专业的原因调查

第五，选择专业的原因调查

可以对学生高考分数情况进行数量特征分析，目的正确分析和描述河南农业大学新生录取原因和调剂的原因，以及学生对其所上专业的了解程度及其选择专业的原因分析。

4.河南农业大学学生上课出勤率及其目的调查

第一，学生的课程安排情况调查

第二，学生学习目的情况

第三，学生逃课原因调查

第四，学生选择选修课的原因调查

对学生上课出勤情况进行数量特征探析，目的正确分析本科生的课程负担和课程安排的合理性，探析学生接受大学教育的动力所在，描述学生的心理动态。

5. 河南农业大学本科生的上网情况调查，包括的内容：

第一，上网目的的调查；

第二，每周上网时间的调查；

第三，上网费用支出的情况调查；

第四，校园网建设情况调查；

内容可以调整，但是必须根据农大学生的实际情况进行上网情况的详细调查分析，分析上网动机和上网费用、时间等方面的关系。

6.河南农业大学本科生的自习情况调查：

第一，学生自习频率的调查；

第二，学生自习的目的；

第三，学生自习时间的调查；

第四，学生自习的内容；

第五，学生自习与课后娱乐活动的关系调查；

内容可以调整，但是必须根据农大学生的实际情况进行详细调查分析，反映本科毕业生的自习真实想法和真实情况。

7.河南农业大学图书馆利用情况调查。

第一，图书馆用途调查；

第二，学生借书类型调查；

第三，学生使用图书馆时间调查；

第四，学生使用图书馆与其专业的关系调查。

对使用图书馆时间的情况进行数量特征分析，主要分析学生利用图书馆的时间和原因、专业等的关系，说明学生使用图书馆的基本情况。

8.河南农业大学本科生参加课余培训班的调查，包括的内容：

第五，参加培训目的和类型的调查；

第六，参加培训时间的调查；

第七，学生费用支出的情况调查；

第八，培训班培训内容的情况调查；

第九，学生的参加率调查

内容可以调整，但是必须根据农大学生的实际情况进行课余培训的详细调查分析，主要分析参加培训的参加率、目的和类型、内容、费用支出的关系探析。