统计学

福建农林大学经济与管理学院旅游学院

实验报告

课程名称：统计学

专业年级：2010金融

学号：102257004058

学生姓名: 温映雪

指导教师：张玉容

成绩:

2012年6 月6 日

实验一：EXCEL的数据整理与显示

一、实验目的及要求：

(一)目的

1．了解EXCEL的基本命令与操作、熟悉EXCEL数据输入、输出与编辑方法；

2．熟悉EXCEL用于预处理的基本菜单操作及命令；

3．熟悉EXCEL用于整理与显示的基本菜单操作及命令。

(二)内容及要求

根据学生实验数据2011-2012,

1．用Excel制作一张学生生源的频数分布表，并绘制一张柱状图，反映不同生源的学生人数分布情况。

2．对学生的体重进行等距分组，整理成频数分布表，并绘制直方图。

3．编制生源与专业交叉分布的列联表，并画出雷达图，比较两个专业的学生生源的分布是否相似。

二、仪器用具

硬件：计算机（安装Windows98 、Windows2000 或Windows XP或以上）

软件：EXCEL

三、实验原理

统计中数据整理与显示的相关理论。

四、实验方法与步骤

步骤：1、本题利用数据透视表和图表向导

（1）在excel的菜单中选择“数据”中选择“数据透视表和数据透视图”，圈中题目要求的数据区域，点击”布局”，把”生源”按钮拖到”行”和”数据”中对话框中。最后”确定”导出题目要要求的数据透视表。

（2）把上一步中得到的数据透视表选中后，在表格格式中将转化成三线表表格式如下表1-1并命名。

（3）选定表，利用菜单栏中“图表向导”中的柱状图模式，选定后导出表格，标题命名，x轴和y轴分别为“生源地”和“人数”。

2、本题运用到FREQUENCY、CHITEST函数

（2）在excel中下面的空白区域，接着使用“FREQUENCY函数Date_array选择区域”E2:E181”，”Bins_array”选择分界点区域，然后同时按下Shift+CTRL+Enter”即可，整理得表1-2

（3）运用图表向导，按步骤一，建立题目所需直方图，见图1-2

3、本题利用数据透视表和图表向导利用“CHITEST函数”

(1)与同步骤1到布局后，增加”专业”拖到”行”中，导出题目所需要的数据透视表。

（2）整理得到列联表1-3

（3）人数除以总数得到表1-4

（4）图表向导把上表转为图1-3

（7）利用“CHITEST函数”取出P值Actual_range空白键中输入实际值，Expected_range 中输入期望值，得到P值。

五、实验结果与数据处理

表1-1 数据透视图后的生源分布表

生源人数比例

北京、天津、上海390.216667

省会城市270.15

中等城市350.194444

县城370.205556

小城镇、集镇170.094444

农村250.138889

总计1801

学生生源分布图1-1

各体重频数表1-2

体重 频数

频率

40-50 58 0.32 50-60 70 0.39 60-70 40 0.22 70-80 11 0.06 80以上

1

0.01

体重分布图1-2

生源分布图

51015202530354045京

津

沪

省

会

城

市

中

等

城

市

县

城

小

城

镇

集

镇

农

村

生源

人数

2040608040-50

50-60

60-70

70-80

80以上

b

体重

人数

b

专业和城市列联表1-3

专业 北京、天津、上海

农村 省会城市 县城 小城镇、集镇

中等城市 总计

会计 25 13 14 23 12 23 110 物流 14 12 13 14 5 12 70 总计

39

25

27

37

17

35

180

专业与城市频率比例表1-4

北京、天津、上海 农村 省会城市 县城 小城镇、集镇

中等城市 会计 0.227272727

0.12 0.127273 0.21 0.109090909 0.209091 物流

0.2

0.17

0.185714

0.2

0.071428571

0.171429

0.10.20.3

京、津、沪农村

省会城市

县城

小城镇、集镇

中等城市

两专业生源分布图

会计物流

图1-3 不同专业学生生源分布图

实验结论分析:

1、城市化受教育普及率就越高，这个学校中来自城市最多，当然也能发现二三线城市以及农村教育普及率也在提高，并在他们努力下，生源和大城市学生差不多。

2、体重在50-60公斤的人数最多，且相对集中，考虑到学生的男女差别，体重水平还是不错的，且体重在80公斤以上的人数几乎没有。

3、两个专业的生源分布相似程度不高，偏向有所差异但并不大，没有根据生源地的所处位置而出现较大的差异。

六、讨论与结论

1、在建立“数据透视表和数据透视图”中，一开始搞不懂要把“生源”这个按钮往哪里放，经过测试发现，放哪里其实都可以得到所要的数据，就是美观程度不同的问题。

2、刚开始不知道表格必须再excel中处理，结果把表格直接放到word中，结果发现不能进行表格修改，增加了不少的作业负担。

3、哪些表格需要出现在报告中，哪些是不需要的，表格的好坏在于能否在表达真实数据的同时美观。

实验报告评分表

学生姓名温映雪学号102257004058 专业

年级

2010金融

实验项目

名称实验一：EXCEL的数据整理与显示

实验

学时

3学时

评价项目权重评价内容

评价结果

得分A B C D

实验态度20% 实验态度端正，遵守实验室守则，严格按照

实验要求进行操作。

20 16 14 12

实验过程30% 实验项目符合大纲，实验方法科学；步骤操

作合理，逻辑条理清晰，符合指导书要求。

30 24 21 18

实验结论

与讨论

30% 实验结论正确，分析、讨论深入。30 24 21 18

实验报告描述20%

语言精炼、流畅、准确、灵活，逻辑性强；

结构严谨规范，条理清晰，布局合理，系统

严密。

20 16 14 12

总分

教师签名

实验二：EXCEL的数据特征描述、列联分析、多元回归分析

一、实验目的及要求：

(一)目的

熟悉EXCEL用于数据描述统计、列联分析、多元回归的基本菜单操作及命令。

(二)内容及要求

根据学生实验数据2011-2012，

1．对学生的身高进行描述统计，说明学生身高的一些基本特征。

2．对不同户口中贫困生的分布情况编制列联表，并分析贫困生的数量的多少是否与户口的不同是有关。

3．根据学生的体育成绩(y)与学生的年龄(x1)、体重(x2)、身高(x3) 和性别（x4）建立一个多元线性回归模型，并判断此模型对于解释学生体育成绩差异有无意义。

二、仪器用具

硬件：计算机（安装Windows98 、Windows2000 或Windows XP或以上）

软件：EXCEL

三、实验原理

统计中数据整理与显示的相关理论。

四、实验方法与步骤

步骤：1、利用数据透视表和数据透视图

（1）在excel中选择工具菜单中的数据分析加载描述统计在输入区域选中区域得到表2-1。

2、CHITEST函数利用

（1）选择excel中数据菜单中的“数据透视表和数据透视图”，选择表格区域，选择布局按钮，先把”户口”拖到”列”中去，再把是否为贫困生”拖到行和数据。导出数据透视表。

（2）整理数据透视表得列联表。表2-2.

（3）求得期望值。见表2-3

（4）利用表2-3用函数CHITEST求出P值，Actual_range对话框中输入实际值，Expected_range对话框输入期望值，得到P值。

3、利用线性回归方程进行预测

（1）因为考虑男生女生的公平性，所以需要把女生的体育成绩转化为与男生一样是1000米的成绩。选择自动筛选后性别“女”把女生的体育成绩除以0.8，同理把男生的成绩除以1或者保持不变，最后取消自动筛选。

（2）为方便设表用查找替换把性别男换成0，女换成1。

（3）利用回归数据分析，Y值输入区域输入成绩，”X值输入区域”输入体重、性别、年龄、身高、导出结果。结果见表2-4、表2-5、表2-6。

五、实验结果与数据处理

身高基本特征分析表2-1

身高

平均164.7722222

标准误差0.595129756

中位数163.5

众数155

标准差7.984503536

方差63.75229671

峰度-0.696982464

偏度0.248125331

区域36

最小值150

最大值186

求和29659

观测数180

最大(1) 186

最小(1) 150

置信度(95.0%) 1.174372756

对比城市和农村人口对比列联表表2-2

城市户口农村户口总计

否122 36 158

是14 8 22

总计136 44 180

实际值与期望值表对比表2-3

实际值 期望值 122 119.3777778 14 16.62222222 36 38.62222222 8

5.377777778

P 值为0.587504735

p>a,接受原假设，关系显著。

多元线性回归统计表2-4

回归统计

Multiple R

0.796933541 R Square 0.635103069 Adjusted R Square 0.626762567 标准误差 25.01996211

观测值

180

多元线性回归方差分析模型表2-5

DF

SS MS F Significance F 回归分析 4 190671.3073 47667.82682 76.14687017

2.77007E-37

残差 175 109549.7382 625.9985041

总计

179

300221.0455

多元线性回归方差模型表2-6

1、根据峰度和偏度身高为扁平分布和右偏分布。学生的平均身高为164.7722222cm ，中位数为163.5cm ，身高为155cm 的人数最多，最高身高为186cm ，最矮为150cm ，

2、由significance 值> =0.5，所以贫困生的数量的多少是否与户口的无关。

3、根据学生的体育成绩(y )与学生的年龄(x 1)、体重(x 2)、身高(x 3) 和性别（x 4）建立一

Coefficients 标准误差 t Stat P-value Lower 95% Upper 95% Intercep t 404.1300464 82.63214364 4.890712363 2.26465E-06 241.0462213 567.2138716 年龄 -1.667124492 2.046021902 -0.814812632 0.41628704 -5.705178732 2.370929749 体重 0.306909074 0.339211687 0.904771521 0.366830338 -0.362563325 0.976381473 身高 -0.919511592 0.427452815 -2.151141737 0.032836446 -1.763137778 -0.07588540 性别

56.9308765

6.201458795

9.180239421

1.20333E-16

44.69160039

69.17015261

个多元线性回归模型为一下模型：

此模型可被回归方程的解释力度为62.6762567%，根据表中数据，该模型的F 检验和T 检验都没有通过，所以不适用。

六、讨论与结论

1、在老师没有讲要将女生和男生的成绩进行公平的换算时我一直在想这个问题要这么处理，经过讲解后，明白为了保证数据的公平性必须保证数据在同一水平上。

2、和实验一一样，我遇到了列联表行列选择的问题，在考虑那个作为列或者行会更美观和直接，但是因为是2×2的列联表，所以本题基本无影响。

1234

404.13004641.6671244920.3069090740.91951159256.9308765y x x x x =-+-+

实验报告评分表

学生姓名温映雪学号102257004058 专业

年级

2010金融

实验项目名称实验二：用EXCEL展示数据的分布特征、

列联分析、多元回归

实验

学时

3学时

评价项目权重评价内容

评价结果

得分A B C D

实验态度20% 实验态度端正，遵守实验室守则，严格按照

实验要求进行操作。

20 16 14 12

实验过程30% 实验项目符合大纲，实验方法科学；步骤操

作合理，逻辑条理清晰，符合指导书要求。

30 24 21 18

实验结论

与讨论

30% 实验结论正确，分析、讨论深入。30 24 21 18

实验报告描述20%

语言精炼、流畅、准确、灵活，逻辑性强；

结构严谨规范，条理清晰，布局合理，系统

严密。

20 16 14 12

总分

教师签名

实验三：时间序列分析

一、实验目的及要求：

(一)目的

掌握EXCEL用于移动平均、线性趋势分析的基本菜单操作及命令。

(二)内容及要求

综合运用统计学时间序列中的移动平均、季节指数运算、时间序列因素分解、图形展示等知识，并结合经济学等方面的知识，对一家大型百货公司最近几年各季度的销售额数据（见Book13）的构成要素进行分解，并绘制图形进行分析。

二、仪器用具

硬件：计算机（安装Windows98 、Windows2000 或Windows XP或以上）

软件：EXCEL

三、实验原理

时间序列分析中的移动平均分析原理、季节指数原理等。

四、实验方法与步骤

利用四步移动平均分析和指数原理进行操作。

1、将数据转置到另外一个空白表格。

2、在单元格中输入公式=AVERAGE(C5:C8)计算平均值。

3、计算中心化移动平均值，在单元格中输入公式=AVERAGE(D6:D7)，拖拉复制公式计算后面的值。销售额数据的移动平均值及其被剔除后的比值3-1

4、销售量除以第三步求得的值，输入公式，然后拖拉结果。

5、转置销售量表格得表3-2

6、把每一季度按年份求、求平均，在B58单元格中输入公式=SUM(B48:B57)，然后拖拉复制表格B58:F58；在D6单元格中输入公式=AVERAGE(B48:B57)，然后拖拉复制表格；在F48单元格中输入公式=SUM(B48:E48)，继续快捷拖拉复制，最后在F59单元格中输入公式=AVERAGE(B59:E59)调整系数，见表3-1

7、在单元格B60中输入公式=B59/H59，然后拖拉公式，见表3-1和表3-3、图3-1

8、将需要用到的数据：年/季度，时间标号，销售额，季节指数。复制到空白表格。销售额(Y)除以季节指数(S)得到季节分离后的时间序列(Y/S)表3-4、图3-2

9、回归数据分析，y值输入区域输入季节分离后的时间序列(Y/S)， x值输入区域输入时间标号导出表格见表表3-5、3-6、3-7

10、把时间标号带入模型得到回归后的趋势(T)，用季节指数(S)乘以回归后的趋势(T)得到最终预测值，用销售额(Y)减最终预测值得到预测误差。

具体见图3-3

五、实验结果与数据处理

销售额数据的移动平均值及其被剔除后的比值3-1

比值

年/季度时间标号销售额移动平均值中心化后的

移动平均值

1991/1 1 993.1

2 2 971.2 1542.925

3 3 2264.1 1713.05 1627.9875 1.39073549

4 4 1943.3 1953.12

5 1833.0875 1.06012397

1992/1 1 1673.6 2369.05 2161.0875 0.77442491

2 2 1931.5 2653.125 2511.0875 0.76918865

3 3 3927.8 2820.325 2736.725 1.4352191

4 4 3079.6 2975.6 2897.962

5 1.06267766

1993/1 1 2342.4 2930.525 2953.0625 0.79321044

2 2 2552.6 3278.825 3104.675 0.82217946

3 3 3747.5 3506.825 3392.825 1.10453678

4 4 4472.8 3929.97

5 3718.4 1.20288296

1994/1 1 3254.4 4480.875 4205.425 0.77385758

2 2 4245.2 4955.95 4718.4125 0.89970938

3 3 5951.1 5118.

4 5037.17

5 1.18143602

4 4 6373.1 5333.57

5 5225.9875 1.21950158

1995/1 1 3904.2 5658.95 5496.2625 0.71033725

2 2 5105.9 6223.

3 5941.125 0.85941636

3 3 7252.6 6618.05 6420.675 1.12956971

4 4 8630.

5 6840.9 6729.475 1.28249232

1996/1 1 5483.2 7221.775 7031.3375 0.77982318

2 2 5997.

3 7244.3 7233.0375 0.82915373

3 3 8776.1 7154.

4 7199.3

5 1.21901283

4 4 8720.6 7167.82

5 7161.1125 1.21777168

1997/1 1 5123.6 7371.85 7269.8375 0.70477504

2 2 6051 7277 7324.425 0.82613994

3 3 9592.2 7231.7 7254.35 1.32226871

4 4 8341.2 7425.32

5 7328.5125 1.13818459

1998/1 1 4942.4 7252.3 7338.8125 0.67346045

2 2 6825.5 7347.775 7300.0375 0.9349952

3 3 8900.1 7364.65 7356.2125 1.20987533

4 4 8723.1 7222.7

5 7293.7 1.19597735 1999/1 1 5009.9 7001.925 7112.3375 0.70439571

2 2 6257.9 6787.55 6894.7375 0.90763426

3 3 8016.8 7049.9 6918.725 1.1587106

4 4 7865.6 6940.3

5 6995.125 1.12444024 2000/1 1 6059.3 6875.85 6908.1 0.87712975

2 2 5819.7 6941.5 6908.675 0.84237571

3 3 7758.8

4 4 8128.2

各季节指数计算表3-2

年/季 1 2 3 4 合计

1991 1.39073549 1.06012397 2.45085947 1992 0.774425 0.769189 1.4352191 1.06267766 4.04151032 1993 0.79321 0.822179 1.10453678 1.20288296 3.92280963 1994 0.773858 0.899709 1.18143602 1.21950158 4.07450456 1995 0.710337 0.859416 1.12956971 1.28249232 3.98181564 1996 0.779823 0.829154 1.21901283 1.21777168 4.04576142 1997 0.704775 0.82614 1.32226871 1.13818459 3.99136827 1998 0.67346 0.934995 1.20987533 1.19597735 4.01430833 1999 0.704396 0.907634 1.1587106 1.12444024 3.89518081 2000 0.87713 0.842376 1.71950546 ∑ 6.791414 7.690793 11.1513646 10.5040523 36.1376239 平均0.754602 0.854533 1.23904051 1.16711693 1.00382289 季节指数0.751728 0.851278 1.23432184 1.16267216 4

四个季节的季节指数表3-3

季节指数

1 0.751728

2 0.851278

3 1.234322

4 1.162672

0.20.40.60.811.21.41

2

3

4

销售额的季节变动图

季度

季节指数

指数

图3-1销售额的季节变动图

销售额预测值表3-4

年/季度 时间标号

销售额(Y) 季节指数(S) 季节分离后的时间序列(Y/S)

回归后的趋势(T) 最终预测值 预测误差 1991/1 1 993.1 0.75173 1321.089859 2207.0982 1659.137091 -666.037 2 2 971.2 0.85128 1140.872658 2370.8046 2018.214219 -1047.01 3 3 2264.1 1.23432 1834.286596 2534.511 3128.402299 -864.302 4 4 1943.3 1.16267 1671.408382 2698.2175 3137.142323 -1193.84 1992/1 5 1673.6 0.75173 2226.337718 2861.9239 2151.387802 -477.788 2 6 1931.5 0.85128 2268.941041 3025.6303 2575.653066 -644.153 3 7 3927.8 1.23432 3182.152243 3189.3367 3936.667985 -8.86798 4 8 3079.6 1.16267 2648.726009 3353.0432 3898.48996 -818.89 1993/1 9 2342.4 0.75173 3116.021434 3516.7496 2643.638513 -301.239 2 10 2552.6 0.85128 2998.549781 3680.456 3133.091913 -580.492 3 11 3747.5 1.23432 3036.080129 3844.1625 4744.93367 -997.434 4 12 4472.8 1.16267 3847.000161 4007.8689 4659.837597 -187.038 1994/1 13 3254.4 0.75173 4329.2265 4171.5753 3135.889224 118.5108 2 14 4245.2 0.85128 4986.854003 4335.2818 3690.530761 554.6692 3 15 5951.1 1.23432 4821.351956 4498.9882 5553.199356 397.9006 4 16 6373.1 1.16267 5481.424773 4662.6946 5421.185234 951.9148 1995/1 17 3904.2 0.75173 5193.63511 4826.401 3628.139935 276.0601 2 18 5105.9 0.85128 5997.921854 4990.1075 4247.969608 857.9304 3 19 7252.6 1.23432 5875.777117 5153.8139 6361.465041 891.135 4 20 8630.5 1.16267 7422.986694 5317.5203 6182.532871 2447.967 1996/1 21 5483.2 0.75173 7294.129408 5481.2268 4120.390647 1362.809 2 22 5997.3 0.85128 7045.053122 5644.9332 4805.408455 1191.892 3 23 8776.1 1.23432 7110.058124 5808.6396 7169.730726 1606.369 4 24 8720.6 1.16267 7500.480594 5972.3461 6943.880508 1776.719 1997/1 25 5123.6 0.75173 6815.764779 6136.0525 4612.641358 510.9586 2 26 6051 0.85128 7108.134734 6299.7589 5362.847303 688.1527 3

27

9592.2

1.23432

7771.230904

6463.4653

7977.996412

1614.204

4 28 8341.2 1.16267 7174.163329 6627.1718 7705.22814

5 635.9719 1998/1 29 4942.4 0.75173 6574.720088 6790.8782 5104.892069 -162.492

2 30 6825.5 0.85128 8017.943089 6954.5846 5920.28615 905.2138

3 31 8900.1 1.23432 7210.518147 7118.2911 8786.262097 113.8379

4 32 8723.1 1.16267 7502.630813 7281.997

5 8466.575781 256.5242 1999/1 33 5009.9 0.75173 6664.51322

6 7445.7039 5597.14278 -587.243

2 34 6257.9 0.85128 7351.18102 7609.4104 6477.724997 -219.825

3 35 8016.8 1.23432 6494.902516 7773.1168 9594.527783 -1577.73

4 36 7865.6 1.16267 6765.10563 7936.8232 9227.923418 -1362.32 2000/1 37 6059.3 0.75173 8060.497214 8100.5296 6089.393492 -30.0935

2 38 5819.7 0.85128 6836.425667 8264.2361 7035.163845 -1215.46

3 39 7758.8 1.23432 6285.880855 8427.9425 10402.79347 -2643.99

4 40 8128.2 1.16267 6990.964654 8591.6489 9989.27105

5 -1861.07 2001/1 41 0.75173 8755.3554 6581.644203

2 42 0.85128 8919.0618 7592.602692

3 43 1.23432 9082.7682 11211.05915

4 44 1.16267 9246.4747 10750.61869

回归统计表3-5

回归统计

Multiple R 0.882769191

R Square 0.779281444

Adjusted R Square 0.773473061

标准误差1031.833918

观测值40

回归方差分析表3-6

回归方差分析表3-7

Coefficients 标准误差t Stat P-value Lower 95% Upper 95% Intercept 2043.391732 332.51 6.15 3.5974E-07 1370.26 2716.52

时间标号163.7064301 14.13 11.58 4.93466E-14 135.09 192.32

模型为

2043.391732163.7064301x

y=+

df SS MS F Significance F

回归分析 1 142842908.6 142842908.6 134.1649538 4.93466E-14 残差38 40457886.9 1064681.234

总计39 183300795.5

2000

4000

6000

8000

10000

12000

1991/1

2

34

1992/1

23

4

1993/12

34

1994/1

23

4

1995/12

34

1996/1

23

4

1997/12

34

1998/1

23

4

1999/12

34

2000/1

23

4

季节分离后的序列及其趋势图

年份/季度

销售量

销售额(Y)

季节分离后的时间序列(Y/S)

季节分离后的趋势(T)

图3-2季节分离后的序列及其趋势图

02000

40006000800010000

120001

4

7

101316192225283134374043

销售额的预测图

年份/季度

最终预测值

销售额(Y)最终预测值

图3-3销售额的预测图

实验结论分析：

1．下半年为销售旺季，上半年相对较弱。

2．由回归统计表可知，解释力度R Square 与Adjusted R Square 的值相差不大，解释力度约为77%，由于F 检验和T 检验均没有通过，所以可靠性值得怀疑。很有可能是非线性模型

3．销售量与时间存在的趋势和季节关系。

六、讨论与结论

1、操作不当，经常会出现选定区域不正确的问题，导致表格多次修改。

2、在运用公式时选择相对引用和绝对引用要根据题意选择，绝对应用要加$。在实验时由于经常用到公式复制可以应用“填充柄”的功能。

实验报告评分表

学生姓名温映雪学号102257004058 专业

年级

2010金融

实验项目

名称实验三:复合型时间序列分解预测EXCEL处理

实验

学时

4学时

评价项目权重评价内容

评价结果

得分A B C D

实验态度20% 实验态度端正，遵守实验室守则，严格按照

实验要求进行操作。

20 16 14 12

实验过程30% 实验项目符合大纲，实验方法科学；步骤操

作合理，逻辑条理清晰，符合指导书要求。

30 24 21 18

实验结论

与讨论

30% 实验结论正确，分析、讨论深入。30 24 21 18

实验报告描述20%

语言精炼、流畅、准确、灵活，逻辑性强；

结构严谨规范，条理清晰，布局合理，系统

严密。

20 16 14 12

总分

教师签名