操作篇 07_数据分析工具.doc

7Excel的数据分析工具与图表工具操作练习文件：操作篇 07_练习库05.xcl7.1描述统计工具常用统计分析术语：对于一组数据（即样本观察值），要想获得它们的一些常用统计量，可以使用Excel提供的统计函数来实现。例如AVERAGE(平均值)、STDEV(样本标准差)、VAR(样本方差)、KURT(峰度系数)、SKEW(偏度系数)、MEDIAN(中位数，即在一组数据中居于中间的数)、MODE(众数，即在一组数据中出现频率最高的数值)等。但最方便快捷的方法是利用Excel提供的描述统计工具，它可以给出一组数据的许多常用统计量，包括：平均值标准差区域计数标准误差(标准误)样本方差最大值第K个最大值中值(中位数)峰值(样本峰度)最小值第K个最小值模式(众数)偏斜度(样本偏度)总和置信度项 目步 骤对“操作篇_07_练习库_05”中“Sheet1”的“总分”项进行描述统计分析。1. 在“操作篇_07_练习库05”中选中“sheet1”2. 工具数据分析描述统计确定3在“输入区域”对话窗中输入i1:i2694. 选中“标志位在第一行”或者：3在“输入区域”对话窗中输入i2:i2694. 不选中“标志位在第一行”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该表数据解释：l “总分”项的平均分为68.77239；标准误为0.50796；中位数为69；众数为70；标准差为8.315663 （注意：此处是估算的标准差，而非计算的标准差。计算的标准差应为：）；方差为69.15025；峰值(峰度)-0.14525；偏斜度（偏度）为-0.31406：全距（区域）为44；最低分为45；最高分为89；分数总和为18431；有效数据为268个。l 从“平均数”、“中值”与“众数”这三者的接近、峰值和偏斜度也非常接近0这两点可以大致判定这些数据来自一正态分布总体（或可以说：该总体呈正态分布）。l 标准差的平方就是方差。l 标准差除以样本数的平方根就是标准误差（标准误）。附：Excel 2000版总分平均68.77239标准误差0.50796中值69模式70标准偏差8.315663样本方差69.15025峰值-0.14525偏斜度-0.31406区域44最小值45最大值89求和18431计数268课堂练习与思考对“操作篇 07_练习库05”中“Sheet2”进行描述统计操作，看看有何发现。7.2排位与百分比利用Excel的这个功能，可以便捷地做出一个数据列表，把某个总体中每个成员（数据）在该总体中的大小次序排名和相应的百分比排位一一列出。操作：针对“操作篇 07_练习库05.xcl”Sheet 2中的总分进行操作。l 工具数据分析排位与百分比排位确定l 输入区域：输入D1:D37 (选中“标志位”)或者输入D2:D37（不选“标志位”）l 分组方式：列l 输出区域，指的是数据输出的位置，比如我们选F1得到如下结果（本处篇幅所限，只选前10名的数据）：点成绩排位百分比90921100.00%1089299.30%4388398.60%7988498.00%3987597.30%2087696.60%9287796.00%3687895.30%6487994.70%23861094.00%7.3直方图工具对于上例中“总分”项的描述统计，如果要粗略地了解其分布情况，可以使用直方图来实现。一般手工作直方图的具体步骤是： 先根据数据的最大值、最小值取一个区间(a，b)，其下限比最小的数据稍小，其上限比最大的数据稍大。 将这一区间分为k个小区间，小区间的长度记为，称为组距。小区间的端点称为组限。通常当n较大时k取1020，当n50时，则k取56。k值取得过大会出现某些小区间内频数为零的情况(般应设法避免)。 计算出落在每个小区间内的数据的频数f(即数据的个数)。 最后自左至右依次在各个小区间上作以(f/n)/ 为高的小矩形。这样的图形就叫直方图。直方图的意义：直方图中小矩形的面积就等于数据落在该小区间的频率fn。由于当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.149.154.159.164.169.174.179.184.189.1c.输入组限端点。在“练习库05”的Sheet1中我们把上面这十个表示组限端点的数字输入到区域N2:N11中。d.操作项 目步 骤对“操作篇_07_练习库_05”中“Sheet1”的“总分”项画出直方图。1. 完成上述a-c步骤2. 工具数据分析直方图3在“输入区域”对话窗中输入I1:I2694在“接收区域”对话窗中输入组限数据所在的单元格区域N1:N115选中“标志”6在“输出区域”对话窗中输入P17选中“图表输出”8确定操作完毕，得到如下所示的（图1）：图1：根据直方图，我们可以大致看出某项目（如某次测试的成绩）的分布图形，从而判定该项目所蕴涵的意义。分布图形一共分六种： 正态分布 说明测试结果与学生的实际情况一致，各种难度的项目比例合理。 正偏态分布 说明试题难度偏高，难度较大的项目比例偏大。呈这种分布的试题有利于将成绩优秀的学生和中等程度的学生区别开，但不利于将中等程度的学生和成绩较差的学生区别开。 负偏态分布 说明试题难度偏低，难度较低的项目比例偏大。呈这种分布的试题有利于将成绩较差的学生和中等程度的学生区别开，但不利于将中等程度的学生和成绩优秀的学生区别开。 尖峰态/陡峭型分布 说明试题中同等难度的项目较多，梯度偏小。呈这种分布的试题几乎不能将不同程度的学生区分开，分数分布过于集中。 低峰态/平坡型分布 说明试题中各种难度的项目比例接近，梯度较大。呈这种分布的试题区分度较高，但分数之间的差异偏大。 双峰型分布 说明试题存在两极分化现象，即难度偏高的和难度偏低的项目较多，而中等难度的项目偏少，项目难度的分布缺乏梯度，不够合理。呈这种分布的试题可以区别中等程度的学生，但不利于区别出成绩优秀的学生和成绩较差的学生。说明：l “直方图”工具中的“柏拉图”选项：选中时数据按降序排列l “接收区域”空白时，Excel会自动给出区间7.4图表类型选择点击上述得到的直方图区域，就会出现一个“图表”工具栏，点击“图表类型”下拉菜单，可选择所需要的各种图表，如面积图、三维面积图、条形图、雷达图、折线图、散点图、馅饼图，等等。7.5积差相关描述操作库：操作篇 07_练习库05.xclSheet 1操作步骤：项 目步 骤对“操作篇07_练习库_05”中“Sheet1”进行相关分析1. 工具数据分析相关系数确定2在“输入区域”对话窗中输入B:I3选中“标志位于第一行”4在“输出区域”对话窗中输入L15确定操作结果如下：听写听力完形语法阅读快速作文应用总分听写1听力0.3469371完形0.022722-0.182851语法0.0574050.0526270.1833731阅读0.1870630.201439-0.065690.1659141快速0.265376-0.243410.0649920.1296530.0003541作文0.3181440.2440860.3240970.0734150.266545-0.215131应用0.2896410.0944010.102998-0.081630.1381670.1790420.0969111总分0.7898930.4704890.259380.4252830.5089280.2878290.5390790.3309881从该表格的数据中我们就能一目了然地得到各个项目分之间的相关系数。比较各个相关系数，可以帮助我们从中找到一些规律，来指导我们的教学和科研。例如，从上表中可以看出，“总分”与“听写”的相关系数达到了0.79，如果别的测试也证明如此，我们就可根据这个发现，用听写