统计实验项目

《统计学原理》实验指导

实验一 统计指标

? 熟悉EXCEL中的有关“统计”函数和“常用”函数，掌握其功能。 实验资料

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

59 61 61 62 63 64 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 图2

2、 从工具菜单中选择数据分析命令，弹出统计分析对话框，双击“描述统计”，显示对话框。在输

入区域填入引用数据的范围（拖动光标选择即可），并给出输出区域（可选择在原工作表或输出到新的工作表，但要防止工作表覆盖了原表的内容）。同时勾选标志这一选项框。 3、 为得到分布特征值，勾选“汇总统计”。 4、 单击“确定”，得到分布特征值。

图3

1

实验二 统计整理

一、指定接受区域直方图

在应用此工具前，用户应先决定分布区间。否则，Excel将用一个大约等于数据集中某数值的平方根作区间，在数据集的最大值与最小值之间用等宽间隔。如果用户自己定义区间，可用2、5或10的倍数，这样易于分析。

对于工资数据，最小值是100，最大值是298。一个紧凑的直方图可从区间100开始，区间宽度用10，最后一区间为300结束，需要21个区间。这里所用的方法在两端加了一个空区间，在低端是区间“100或小于100”，高端是区间“大于300”。 参考图3.3，利用下面这些步骤可得到频率分布和直方图： 1．为了方便，将原始数据拷贝到新工作表“指定频数直方图”中。

2．在B1单元中输入“组距”作为一标记，在B2单元中输入100，B3单元中输入110，选取B2:B3，向下拖动所选区域右下角的+到B22单元。

3．按下列步骤使用“直方图”分析工具：

（1）选择工具菜单之数据分析选项, 在分析工具框中“直方图”。如图所示。

图1 数据分析工具之直方图对话框

1) 输入

输入区域：A1:A51

接受区域：B1:B22 (这些区间断点或界限必须按升序排列) 选择标志 2) 输出选项 输出区域: C1 选定图表输出

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

2

图2 数据分析工具之直方图对话框

Excel将把频率分布和直方图放在工作表中，如图3.3所示，输出表的C和D列中包括开始指定的界限。这些界限实际上是每一区间的上限，也就是说，界限实际上是边界。

图3 频数分布与直方图

为了使图表更像传统的直方图和更易于理解，可双击图表并对它做如下修改：

1．图例：因为只有一个系列的数据显示在图表中，所以不需要图例。单击图例(位于图表右侧的“频率”)并按Delete键。

2．图表区：绘图区是以X和Y轴为边界的矩形区域。通过在柱形上面单击可选取绘图区，单击鼠标右键并选择绘图区格式，将边框改为无并将区域改为无，单击确定。

3．条宽：在传统的直方图中，柱形是彼此相连接而不是分开的。选择某个柱形，单击鼠标右键，选择数据系列格式，并单击选择标签，将间距宽度从150％改为0％，单击确定。

4．X轴标志：选取x轴，单击鼠标右键，选择坐标轴格式，单击对齐标签，将方向从自动改为水平文本，在这种设置下，即使图表已重置尺寸，x轴标记也会变为水平的，单击确定。最后的直方图4.6

3

直方图765频率43210100120140160180200组距220240260280300图4 修改后的直方图

二、不指定接受区域直方图

在进行探索性分析时，为了方便，通常不指定接受区域作直方图，步骤如下： （1）选择工具菜单之数据分析选项, 在分析工具框中“直方图”。如图4所示。

1) 输入

输入区域：A1:A51 接受区域：(该处为空) 选择标志 2) 输出选项 输出区域: B1 选定图表输出 (2)．单击确定，得结果。

(3)按前面方法对直方图进行进一步修饰即得图3.5

图5 修改后的直方图

三、 计数数据的透视表与条图

4

步骤如下：

（1）选择数据菜单之数据透视表和图表报告选项, 如图所示。

(2)．选择数据源区域

（3）选定数据透视表位置，完成

5

（4）将“性别”作为行字段拖至G列，并将“性别”作为数据拖至数据项处，得下表结果

同理可得“文化程度”的透视表

此时如点击图形按钮，立即得到如下的透视图

6

（5）将“性别”作为行字段拖至行字段处，并将“文化程度”作为列字段拖至列字段处，将“性别”

或“文化程度”作为列字段拖至数据字段处得下表结果

7

实验三 统计图表

1、直方图

（1） 把实验一中的数据（A列、B列）复制到一个新的工作表中，从工具菜单中下拉选择数据分

析/直方图，点击确定按钮，打开直方图分析选项框。在“输入区域”中输入原始数据的引用范围，本题为$A$1:$A$51（或直接用光标圈选A列的数据），此外，勾选“标志”选项。

图2-1

（2） 在“接收区域”输入含分组标志值单元范围的引用，通常分组上限以升序排列。 （3） 给出输出区域，勾选“图表输出”或“累积百分比”，得到图表。并可对其进行修改（光标

移至图块处，单击右键进行所需要的改动）。

图2-2 图2-3

2、绘制图表

（1） 建立新工作表，输入数据。单击“插入”中的“图表”或直接点图表快捷图标，选择“标准

类型”中的“折线图”。定义“系列”要注意选择“分类X轴标志”，“系列”只有“国内生产总值”。

图2-4

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

8

图2-5 图2-6

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

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

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

图2-9 图2-10

3、制作散点图。

9

实验四 抽样估计

（一）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名学生成绩。 实验步骤： ① 利用实验一“考试成绩”资料，建立工作表。

图4--1 ② 选择C2：C8单元格，在工具栏中单击“函数”快捷按钮，打开“粘贴函数”对话框，在“函数分类”

列表中选择 “数学与三角函数”，在“函数名”列表中选择“随机函数“RAND”，打开随机函数对话框如下图所示。

图4--2

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

显示一组随机数。

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

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

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

10

图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%的保证下，顾客平均消费额的估计区间。

11

第一步：把资料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.26464。

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

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

12

实验五 相关与回归实验

一、简单线性相关分析

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

13

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

(3) 结论: 由于r=-0.9689, 且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)。

14

残差图(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中。作为残数的标准偏差，它衡量单位成本在回归线周围的分散情况，标准误差通常称为估计标准误差。

R（R Square），如图4的单元E5所示，衡量用回归线解释的因变量变化的比例。这一比例必

15

2

=average(E24:E27)。

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

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

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

21

实验六 抽样估计

（一）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 ⑤ 选择C2：C8单元格，在工具栏中单击“函数”快捷按钮，打开“粘贴函数”对话框，在“函数分类”

列表中选择 “数学与三角函数”，在“函数名”列表中选择“随机函数“RAND”，打开随机函数对话框如下图所示。

图2

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

显示一组随机数。

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

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

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

22

图3

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

⑦重复按F9键，在D2：D8各单元格中将出现介于1和50之间的随机整数，即将要抽取的样本点。 ⑧选定单元格区域E2：E8，在“插入”菜单中选择“函数”选项，打开“粘贴函数”对话框。在“函数分类”列表中选择“查找与引用”选项，在“函数名”列表中选择“索引函数INDEX”，打开选定参数对话框如下图所示。

图4

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

图5

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

（二）利用利用随机数发生器生成样本并理解区间估计的含义

随机数发生器可以采用不同的分布抽取样本，这里采用正态分布，并假定总体的均值为100，标准差为15。操作如下：

①打开一个新工作表，取名为“模拟区间”。

②单击“工具”菜单的“数据分析”选项，打开“数据分析”对话框。从“分析工具”栏中选择“随机数发生器”，单击“确定”按钮，打开“随机数发生器”对话框如图所示。

23

图6

③在“变量个数”中输入15，在“随机变量个数”中输入1000，这将生成1000个样本量为15的随机样本。

④在“分布”对话框中选择“正态”选项。在“参数”对话框中的“平均值”中输入100，“标准偏差”中输入15。

⑤在“输出选项”中选择“输出区域”，输入地址A2，这样就建立了1000个样本量为15的随机样本。 ⑥在P1:V1各单元格中分别输入“均值”、“标准差”、“标准误差”、“t 值”、“估计下限”、“估计上限”、“逻辑函数”，在单元格Y1中输入“置信度”。在单元格Y2中输入置信度95%，它表示有95%样本所构造的置信区间包含总体均值。

⑥在单元格P2中输入公式为“=AVERAGE(A2:O2)”，计算样本的均值，使用填充柄依次计算到P1001。 在单元格Q2中输入公式“=STDEV(A2:O2)”，计算样本的标准差，显示的值是18.51349，使用填充柄依次计算到P1001。

⑦ 在单元格R2中输入公式“=Q2/SQRT(15)”，计算标准误差，即样本标准差除以15的平方根，显示值为4.78016154。使用填充柄依次计算到P1001。

⑧ 选定单元格S2，在“插入”菜单中选择“函数”选项，打开“粘贴函数”对话框。在“函数分类”列表中选择 “统计”，在“函数名”列表中选择函数TINV（查找并了解该函数的功能！因为这儿样本容量n=15，所以查T分布表）。单击 “确定”按钮，打开TINV函数对话框如下页图所示。

图7

⑨在Probability中输入1－$Y$2，Y2要绝对引用，在Deg_freedom中输入15－1。单击“确定”按钮后，在单元格S2中显示的值是2.144789。把该值一直复制到S1001。

⑩在单元格T2中输入公式“=P2-S2*R2”，以计算估计下限，其值为84.2337319。在单元格U2中输入公式“=P2+S2*R2”，以计算估计下限，其值为104.7386039。

24

实验七 统计指数

一、实验材料：某企业甲、乙、丙三种产品的生产情况资料（见电子表格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”便可得到生产量综合指数

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

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

（二）平均指数

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

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

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

25

本文来源：https://www.bwwdw.com/article/nmsv.html