实验8  数据分析工具

实例8.1  相关分析

1. 某财务软件公司在全国有许多代理商，为研究它的财务软件产品的广告投入与销售额的关系，统计人员随机选择10家代理商进行观察，收集到年广告投入费和月平均销售额的数据，如图8.1所示。用Excel的分析工具，分析广告投入与销售额的相关性。操作步骤如下：

① 建立数据模型：将数据输入到工作表中，如图8.1所示。

② 单击“工具/数据分析”，在弹出的“数据分析”对话框中选择“相关系数”，将弹出“相关系数”对话框，如图8.2所示。

图8.1  相关分析数据模型

设置对话框内容如图8.2所示。单击“确定”，输出结果如图8.3所示。

 

图8.2  “相关系数”对话框                             图8.3  相关分析结果

分析结果表明：相关系数 r=0.994198376，表示年广告投入费和月平均销售额之间存在高度正相关关系。

2. 某学校进行“综合学习”教学实验，经过一个学期的实验，在学期结束时，从实验班中抽取14名学生，记录了他们的语文、数学的成绩，如表8.1所示。

要求：检测他们语文和数学成绩的相关程度。

表8.1                                 成绩表

(3) 我国23个城市20##年的经济指标数据如图8.4所示。

要求：用Excel分别计算两对变量间的相关系数，看看哪组变量的相关性强。

图8.4    我国23个城市20##年的经济指标数据

 

实例8.2  方差分析

1. 国家统计局城市社会经济调查总队1996年在辽宁、河北、山西3省的城市中分别调查了5个样本地区，得到城镇居民人均年消费额（￥）数据如图8.5所示。

要求：用方差分析方法检验3省城镇居民的人均年消费额是否有差异（设α=0.05）。

操作步骤如下：

① 建立数据模型：将数据输入到工作表中，如图8.5所示。

② 单击“工具/数据分析”，在弹出的“数据分析”对话框中选择“方差分析：单因素方差分析”，将弹出对话框，如图8.6所示。

设置对话框内容如图8.6所示。单击“确定”，输出结果如图8.7所示。

运算结果：本例中F统计值是4.8963797，大于F临界值3.88529。所以，拒绝接受等均值假设，即认为3省城镇居民的人均年消费额有显著差距。从显著性分析上也可以看出，概率为0.02787495，小于0.05。

  

图8.6 “方差分析：单因素方差分析”对话框                     图8.7单因素方差分析结果

2. 某计算机教师欲了解自己所教的3个不同专业学生的计算机成绩是否与他们所属的专业有关，分别从统计、会计、金融3个专业中各随机抽取15名学生，将他们的考试成绩整理如表8.2所示。假设这3个不同专业学生在其他各方面条件基本相同。

要求：用方差分析方法检验这3个专业的学生计算机成绩有无显著差异。（设α=0.05）

表8.2                          成绩表

实例8.3  方案分析

某企业生产产品A、产品B、产品C，在20##年的销售额分别为200万元、400万元和300万元，销售成本分别为120万元、280万元和160万元。根据市场情况推测，20##年产品的销售情况有好、一般和差三种情况，每种情况下的销售额及销售成本的增长率如图8.8所示。

图8.8 产品销售资料及预计增长率

1. 建立方案

根据以上资料，建立分析方案：

（1）单击工作表的任一单元格，并设计方案计算分析格式，如图8.8所示，并在单元格G7中输入公式“=SUMPRODUCT(B3:B5,1+G4:G6)-SUMPRODUCT(C3:C5,1+H4:H6)”。

（2）将可变单元格分别进行命名，即单元格G4的名字为“产品A销售额增长率”，单元格H4的名字为“产品A销售成本增长率”，单元格G5的名字为“产品B销售额增长率”，单元格H5的名字为“产品B销售成本增长率”，单元格G6的名字为“产品C销售额增长率”，单元格H6的名字为“产品C销售成本增长率”，单元格G7的名字为“总销售利润”。

（3）单击“工具/方案”命令，弹出“方案管理器”对话框，如图8.9所示，单击【添加】按钮，系统弹出“添加方案”对话框，如图8.10所示。

     

图8.9【方案管理器】对话框               图8.10【添加方案】对话框

（4）在“添加方案”对话框中，“方案名”编辑框中输入“方案1 销售好”，“可变单元格”编辑框中输入“$G$4:$H$6”，单击【确定】按钮，弹出“方案变量值”对话框，如图8.11所示；

（5）在“方案变量值”对话框中输入每个可变单元格的值（这里要按行输入），完毕后单击【添加】按钮，弹出如图8.10所示的“添加方案”对话框，对第2个方案进行输入；待所有方案输入完毕后，单击“方案变量值”对话框中的【确定】按钮，返回到“方案管理器”对话框，如图8.12所示。此时，可单击【关闭】按钮，回到工作表。

     

图8.11 “方案变量值”对话框             图8.12 方案建立完毕后的“方案管理器”对话框

2. 显示方案

方案制定好后，任何时候都可以执行方案，查看不同的执行结果，方法如下：

（1）打开原工作表。

（2）单击“工具/方案”命令，弹出“方案管理器”对话框，如图8.12所示，选择要想查看的方案，单击【显示】按钮，则系统就自动显示出该方案的执行结果，如图8.8所示。

3. 修改、删除或增加方案

对做好的方案进行修改，只需在图8.12所示的“方案管理器”对话框中选中需要修改的方案，单击【编辑】按钮，系统弹出如图8.11所示的对话框，进行相应的修改即可。

若要删除某一方案，则在图8.12所示的“方案管理器”对话框中选中需要删除的方案，单击【删除】按钮。

若要增加方案，则在图8.12所示的“方案管理器”对话框中单击【添加】按钮，然后在图8.10所示的对话框填写相关的项目。

4. 建立方案报告

当需要将所有的方案执行结果都显示出来时，可建立方案报告，方法如下：

（1）在图8.12所示的“方案管理器”对话框中单击【摘要】按钮，弹出“方案摘要”对话框，如图8.13所示，在“结果类型”中选择“方案摘要”项，在“结果单元格”中输入“G7”，然后单击【确定】按钮，则系统在当前工作簿中自动建立一个名为“摘要”的工作表，如图8.14所示。

     

图8.13  “方案摘要”对话框                                   图8.14 方案报告

第二篇：操作篇 07_数据分析工具

计算机辅助英语教学与研究

（操作篇讲义）

浙江师范大学外语学院 夏建新

20xx年10月

7．Excel的数据分析工具与图表工具 操作练习对象：操作篇 07_练习库05.xcl

7.1 描述统计工具

常用统计分析术语：对于一组数据（即样本观察值），要想获得它们的一些常用统计量，可以使用Excel提供的统计函数来实现。例如AVERAGE(平均值)、STDEV(样本标准差)、VAR(样本方差)、KURT(峰度系数)、SKEW(偏度系数)、MEDIAN(中位数，即在一组数据中居于中间的数)、MODE(众数，即在一组数据中出现频率最高的数值)等。但最方便快捷的方法是利用Excel提供的描述统计工具，它可以给出一组数据的许多常用统计量，包括：

平均值 标准差 区域 计数

标准误差 (标准误) 样本方差 最大值 第K个最大值 中值(中位数) 峰值(样本峰度) 最小值 第K个最小值 模式(众数) 偏斜度(样本偏度) 总和 置信度

项 目

对“操作篇_07_练

习库_05”中

“Sheet1”的“总

分”项进行描述统

计分析。 步 骤 1. 在“操作篇_07_练习库05”中选中“sheet1” 2. 工具→数据分析→描述统计→确定 3．在“输入区域”对话窗中输入i1:i269 4. 选中“标志位在第一行”

或者：

3．在“输入区域”对话窗中输入i2:i269

4. 不选中“标志位在第一行”

5．在“输出区域”对话窗中输入L1 (这是确定统计结果的起始显示位置。也可以选别的位置，以布局合理为原则)。

6．选中“汇总统计”

7．确定

操作结果如下所示(EXCEL 97版)： 总分

平均 68.77239

标准误差 0.50796

中位数 69

众数 70

标准差 8.315663

方差 69.15025

峰度 -0.14525

偏度 -0.31406

区域 44

最小值 45

最大值 89

求和 18431

观测数 268

置信度

(95.0%) 1.000117

该表数据解释：

z “总分”项的平均分为68.77239；标准误为0.50796；中位数为69；众数为70；

标准差为8.315663 （注意：此处是估算的标准差，而非计算的标准差。）；方差为69.15025；峰值(峰度)-0.14525；偏斜度（偏度）为-0.31406：全距（区域）为44；最低分为45；最高分为89；分数总和为18431；有效数据为268个。 z 从“平均数”、“中值”与“众数”这三者的接近、峰值和偏斜度也非常接近0这

两点可以大致判定这些数据来自一正态分布总体（或可以说：该总体呈正态分布）。

z 标准差的平方就是方差。

z 标准差除以样本数的平方根就是标准误差（标准误）。

附：Excel 2000版 总分

平均 68.77239

标准误差 0.50796

中值 69

模式 70

标准偏差 8.315663

样本方差 69.15025

峰值 -0.14525

偏斜度

区域

最小值

最大值

求和

计数 -0.3140644458918431268

课堂练习与思考

对“操作篇 07_练习库05”中“Sheet2”进行描述统计操作，看看有何发现。

7.2 排位与百分比

利用Excel的这个功能，可以便捷地做出一个数据列表，把某个总体中每个成员（数据）在该总体中的大小次序排名和相应的百分比排位一一列出。

操作：针对“操作篇 07_练习库05.xcl”Sheet 2中的总分进行操作。

z 工具→数据分析→排位与百分比排位→确定

z 输入区域： 输入D1:D37 (选中“标志位”)

或者输入D2:D37（不选“标志位”）

z 分组方式：列

z 输出区域，指的是数据输出的位置，比如我们定在F1。

得到如下结果（本处篇幅所限，只选前10名的数据）：

点 成绩 排位 百分比

90 92 1 100.00%

10 89 2 99.30%

43 88 3 98.60%

79 88 4 98.00%

39 87 5 97.30%

20 87 6 96.60%

92 87 7 96.00%

36 87 8 95.30%

64 87 9 94.70%

23 86 10 94.00%

7.3 直方图工具

对于上例中“总分”项的描述统计，如果要粗略地了解其分布情况，可以使用直方图来实现。 一般手工作直方图的具体步骤是：

● 先根据数据的最大值、最小值取一个区间(a，b)，其下限比最小的数据稍小，其上限比最大的数据稍大。

● 将这一区间分为k个小区间，小区间的长度记为?，称为组距。小区间的端点称为组限。通常当n较大时k取10～20，当n<50时，则k取5～6。k值取得过大会出现某些小区间内频数为零的情况(—般应设法避免)。

● 计算出落在每个小区间内的数据的频数f(即数据的个数)。

● 最后自左至右依次在各个小区间上作以(f/n)/ ?为高的小矩形。这样的图形就叫直方图。

直方图的意义：直方图中小矩形的面积就等于数据落在该小区间的频率f／n。由于当n很大时，频率接近于概率，因而—般来说，每个小区间上的小矩形面积接近于概率密度曲线之下该小区间之上的曲边梯形的面积。所以，通常直方图的外轮廓曲线接近于总体x的概率密度曲线。这样直方图就直观地给出了数据的统计特性和分布情况。

用Excel可以非常方便地给出一组数据的直方图。具体步骤如下：

a. 定区间。在“练习库05”的Sheet1中，描述统计所给出的“总分”项的最小值（45）和最大值(89)，我们先确定一个区间[44.1, 89.1] (组限通常取比数据的精度高一位，即多一位小数点或个位数，以免数据刚好落在端点上)。这个区间能覆盖[45, 89]，否则统计结果会将最小值和最大值排除在外。

b. 定组距。将区间[44.1, 89.1]等分为9个小区间，即组距?=（89.1-44.1）/ 9=5。这样各小区间的端点从左至右依次为：

44.1 49.1 54.1 59.1 64.1 69.1 74.1 79.1 84.1 89.1

c. 输入组限端点。在“练习库05”的Sheet1中我们把上面这十个表示组限端点的数字输入到区域N2:N11中。

d. 操作

项 目

对“操作篇_07_练

习库_05”中

“Sheet1”的“总

分”项画出直方

图。 步 骤 1. 完成上述a-c步骤 2. 工具→数据分析→直方图 3．在“输入区域”对话窗中输入I1:I269 4．在“接收区域”对话窗中输入组限数据所在的单元格区域N1:N11 5．选中“标志”

6．在“输出区域”对话窗中输入图表的位置，比如P1

7．选中“图表输出”

8．确定

操作完毕，得到如下所示的（图1）：

图1：

根据直方图，我们可以大致看出某项目（如某次测试的成绩）的分布图形，从而判定该项目所蕴涵的意义。分布图形一共分六种：

? 正态分布 说明测试结果与学生的实际情况一致，各种难度的项目比例合理。

? 正偏态分布 说明试题难度偏高，难度较大的项目比例偏大。呈这种分布的试题有利于将成

绩优秀的学生和中等程度的学生区别开，但不利于将中等程度的学生和成绩较差的学生区别开。

? 负偏态分布 说明试题难度偏低，难度较低的项目比例偏大。呈这种分布的试题有利于将成

绩较差的学生和中等程度的学生区别开，但不利于将中等程度的学生和成绩优秀的学生区别开。

? 尖峰态/陡峭型分布 说明试题中同等难度的项目较多，梯度偏小。呈这种分布的试题几乎不

能将不同程度的学生区分开，分数分布过于集中。

? 低峰态/平坡型分布 说明试题中各种难度的项目比例接近，梯度较大。呈这种分布的试题区

分度较高，但分数之间的差异偏大。

? 双峰型分布 说明试题存在两极分化现象，即难度偏高的和难度偏低的项目较多，而中等难

度的项目偏少，项目难度的分布缺乏梯度，不够合理。呈这种分布的试题可以区别中等程度的学生，但不利于区别出成绩优秀的学生和成绩较差的学生。

说明：

z “直方图”工具中的“柏拉图”选项：选中时数据按降序排列

z “接收区域”空白时，Excel会自动给出区间

7.4 图表类型选择

点击上述得到的直方图区域，就会出现一个“图表”工具栏，点击“图表类型”下拉菜单，可选择所需要的各种图表，如面积图、三维面积图、条形图、雷达图、折线图、散点图、馅饼图，等等。

7.5 积差相关描述

操作练习对象：操作篇 07_练习库05.xcl Sheet 1 操作步骤：

项 目

对“操作篇07_练习库_05”中“Sheet1”进行相关分析

操作结果如下：

听写

听写

1

1听力

完形

1

0.183373

1

-0.06569 0.1659140.064992 0.1296530.324097 0.0734150.102998 -0.081630.25938 0.425283

语法

步 骤

1. 工具→数据分析→相关系数→确定 2．在“输入区域”对话窗中输入B:I 3．选中“标志位于第一行”

4．在“输出区域”对话窗中输入L1 5．确定

阅读 快速 作文

应用

1

总分

1

听力 0.346937

完形 0.022722 -0.18285语法 0.057405 0.052627阅读 0.187063 0.201439快速 0.265376 -0.24341作文 0.318144 0.244086应用 0.289641 0.094401总分 0.789893 0.470489

1

0.0003540.2665450.1381670.508928

1

-0.215130.1790420.287829

1

0.096911

0.539079 0.330988

从该表格的数据中我们就能一目了然地得到各个项目分之间的相关系数。比较各个相关系数，可以帮助我们从中找到一些规律，来指导我们的教学和科研。例如，从上表中可以看出，“总分”与“听写”的相关系数达到了0.79，如果别的测试也证明如此，我们就可根据这个发现，用听写的形式对学生进行简单的测试，以大致估算他们的学习水平；或在教学中，加大听写的力度，看一看对成绩的提高影响程度如何；或者对听写与总分的相关系数为何如此之高（快速阅读与阅读的相关系数如此之低，等等问题）作出自己的解释。

7.6 Task 7

对某个班级（或某几个班级，最好50人以上）某一次英语测试的成绩总分进行描述统计并做出直方图，然后根据得到的数据和图形说明该测试的成绩（总分）分布是否为正态分布。