信息技术与数学教学 华东师范大学数学系 万福永 第八讲 若干个统计量的Excel实现

一、常见的概率分布 （一） 教育统计理论基础 （二）在Excel软件中的实现 （三）实际应用实例与Excel解答 二、集中量、差异量、标准分、相关系数等 （一）教育统计理论基础

一、常见的概率分布 （一） 教育统计理论基础 1. 二项分布：是一种离散型随机变量的概率分布

一、常见的概率分布 （一） 教育统计理论基础 2. 正态分布：是一种连续型随机变量的概率分布

一、常见的概率分布 （二）在Excel软件中的实现 1. BINOMDIST(k,n,p,0)：计算二项分布的分布律；

一、常见的概率分布 （二）在Excel软件中的实现 语法：BINOMDIST (Number，Trials，Probability，Cumulative) 参数：Number为实验成功的次数，Trials为独立实验的次数，Probability为一次实验中成功的概率，Cumulative是一个逻辑值，用于确定函数的形式。如果Cumulative为TRUE，则BINOMDIST函数返回累积分布函数，即至多Number次成功的概率；如果为FALSE，返回概率密度函数，即Number次成功的概率。　

一、常见的概率分布 （二）在Excel软件中的实现 1. BINOMDIST(k,n,p,0)：计算二项分布分布律； BINOMDIST(k,n,p,1)：计算二项分布累积分布。　　 实例：抛硬币的结果不是正面就是反面，第一次抛硬币为正面的概率是0.5，则掷硬币10次正面朝上6次的概率为“=BINOMDIST(6, 10, 0.5, FALSE)”，计算的结果等于0.205078。 累积概率为“=BINOMDIST(6, 10, 0.5, TRUE)”，计算的结果等于0.828125。

一、常见的概率分布 （二）在Excel软件中的实现 2. NORMDIST(x,µ,σ,0) ：计算正态分布N(µ,σ2)的概率密度函数 f(x) 在 x 处的函数值； NORMDIST(x,µ,σ,1) ：计算正态分布N(µ, σ2)累积分布函数 F(x) 在 x 的函数值。 【NORMDIST函数详解】： 用途：返回给定平均值和标准差的正态分布的概率密度函数/分布函数的值。 　　　　

一、常见的概率分布 （二）在Excel软件中的实现 语法：NORMDIST(X, Mean, Standard_dev, Cumulative) 参数：X为需要计算其分布的数值 ，Mean是分布的算术平均值，Standard_dev是分布的标准方差；Cumulative为一逻辑值，指明函数的形式。如果Cumulative为TRUE，则NORMDIST函数返回累积分布函数；如果为FALSE，则返回概率密度函数。

一、常见的概率分布 （二）在Excel软件中的实现 实例： 公式“=NORMDIST(42, 40, 1.5, FALSE)” 返回概率密度函数值： 0.109340 。 公式“=NORMDIST(42, 40, 1.5, TRUE)” 返回累积分布函数值： 0.908789 。

一、常见的概率分布 例1：一个学生做10题正误题时，做对不同题数的概率分布 (假设：做对每题的概率p=1/2；做错的概率为1/2) （三）实际应用实例与Excel解答 例1：一个学生做10题正误题时，做对不同题数的概率分布 (假设：做对每题的概率p=1/2；做错的概率为1/2) 做对题数 1 2 3 4 5 6 7 8 9 10 出现方式数 45 120 210 252

一、常见的概率分布 B3中输入的计算公式是=BINOMDIST(A3,$B$1,$B$2,0)， 而C3中输入的计算公式是=BINOMDIST(A3,$B$1,$B$2,1)；

正态分布图 偏正态分布

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 1. 算术平均数( ) 所有观察值总和除以总频数之和所得商。

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 2. 加权平均数( ) 是不同比重数据(或平均数)的平均数 或 2. 加权平均数( ) 是不同比重数据(或平均数)的平均数 或 N表示各组的频数；X 表示各组的平均数。

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 3. 全距（R） 4. 标准差（σ或S） 　一组数据中最大值和最小值之差表示，又称极差。 4. 标准差（σ或S） 　标准差概念：标准差是指离差平方和后平均的方根。

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 5.差异系数（变异系数） 两个群体测量单位不同，或虽测量单位相同，平均数相差很大时，不能用标准差比较他们离散程度，可利用差异系数。

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 6. 标准分 （1）概念：标准分是将原始分数（测验分数）与平均分数相减，再除以标准差所得的商。 甲同学： z（语文）=（73-48.3）/ 3.9=1.78 z（数学）=（79-66.9) / 18.5=0.65 z（英语）=（75-67.2) / 14=0.56

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 （2）标准分特点 标准分是以标准差为单位的，故称为标准分。它是一种相对地位分。 标准分有正负之分，一般在[-3，3]中（几率为99.74%) ，平均值为零。 标准分可比性根据在于标准正态分布。 T分数：T=100Z+500 (一般200≤T≤800）

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 7. 标准分应用 比较各个学生成绩在班级中地位； 比较某个学生两科或多科测验中所得分的优劣，精确地计算学生的总成绩。 确定等级评定的人数。

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 8. 相关系数 相关：是指两个或两个以上变量之间存在相互依存关系。如数学课成绩与数学竞赛成绩、数学与物理成绩等。 正相关 负相关 零相关

相关系数：用来描述两个变量相互之间变化方向及密切程度的数字特征量称为相关系数。最常用的是积差相关系数。 二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 相关系数：用来描述两个变量相互之间变化方向及密切程度的数字特征量称为相关系数。最常用的是积差相关系数。

二、集中量、差异量、标准分、相关系数等 （一） 教育统计理论基础 相关系数与相关程度表一览表 | r | ≤0.3 ≤0.3 0.3<|r| ≤0.5 0.5< |r| ≤0.8 >0.8 1.0 相关 程度 零相关 微相关 切实相关 密切相关 高度相关 完全相关

二、集中量、差异量、标准分、相关系数等 （二）在Excel软件中的实现 1. AVERAGE(A1:An) ：计算数据的均值。 　　用途：计算所有参数的算术平均值。 　　语法：AVERAGE(number1，number2，...)。 　　参数：Number1、number2、...是要计算平均值的1～30个参数。 　　实例：假设某次考试的成绩样本为A1=78、A2=45、A3=90、A4=12、A5=85，则公式“=AVERAGE(A1:A5)”返回62。

二、集中量、差异量、标准分、相关系数等 （二）在Excel软件中的实现 2. VAR(Al:An) ：计算数据的无偏方差； VARP(Al:An) ：计算数据的有偏方差。 　　【VAR函数详解】： 　　用途：估算样本无偏方差。 　　语法：VAR(number1，number2，...) 　　参数：Number1，number2，...对应于与总体样本的1到30个参数。 　　实例：假设抽取某次考试中的5个分数，并将其作为随机样本，用VAR函数估算成绩方差，样本值为A1=78、A2=45、A3=90、A4=12、A5=85，则公式“=VAR(A1:A5)”返回1089.5。

二、集中量、差异量、标准分、相关系数等 （二）在Excel软件中的实现 3. STDEV(A1:An)：计算数据的无偏标准差； STDEVP(A1:An)：计算数据的有偏标准差。 　　【STDEV函数详解】： 　　用途：估算样本的无偏标准差。它反映了数据相对于平均值(mean)的离散程度。 　　语法：STDEV(number1，number2，...) 　　参数：Number1，number2，...为对应于总体样本的1到30个参数。可以使用逗号分隔的参数形式，也可使用数组，即对数组单元格的引用。

二、集中量、差异量、标准分、相关系数等 （二）在Excel软件中的实现 3. STDEV(A1:An)：计算数据的无偏标准差； STDEVP(A1:An)：计算数据的有偏标准差。 　　注意：STDEV函数假设其参数是总体中的样本。如果数据是全部样本总体，则应该使用STDEVP函数计算标准差。同时，函数忽略参数中的逻辑值(TRUE或FALSE)和文本。如果不能忽略逻辑值和文本，应使用STDEVA函数。 　　实例：假设某次考试的成绩样本为A1=78、A2=45、A3=90、A4=12、A5=85，则估算所有成绩标准差的公式为“=STDEV(A1:A5)”，其结果等于33.00757489。

二、集中量、差异量、标准分、相关系数等 （二）在Excel软件中的实现 4. COVAR(A1:An,B1:Bn)：计算两组数据之间的样本协方差。 　　【COVAR函数详解】： 　　用途：返回协方差，即每对数据点的偏差乘积的平均数。利用协方差可以研究两个数据集合之间的关系。 　　语法：COVAR(array1，array2) 　　参数：Array1是第一个所含数据为整数的单元格区域，Array2是第二个所含数据为整数的单元格区域。 　　实例：如果A1=3、A2=2、A3=1、B1=3600、B2=1500、B3=800，则公式“=COVAR(A1:A3，B1:B3)”返回933.3333333。

二、集中量、差异量、标准分、相关系数等 （二）在Excel软件中的实现 5. CORREL(A1:An,B1:Bn)：计算两组数据之间的相关系数。 　　【CORREL函数详解】： 　　用途：返回单元格区域array1和array2之间的相关系数。它可以确定两个不同事物之间的关系，例如检测学生的物理与数学学习成绩之间是否关联。 　　语法：CORREL(array1，array2) 　　参数：Array1第一组数值单元格区域。Array2第二组数值单元格区域。 　　实例：如果A1=90、A2=86、A3=65、A4=54、A5=36、B1=89、B2=83、B3=60、B4=50、B5=32，则公式“=CORREL(A1:A5，B1:B5)”返回0.998876229，可以看出A、B两列数据具有很高的相关性。

二、集中量、差异量、标准分、相关系数等 （三）实际应用实例与Excel解答 例2：一个学生某门课期中考试成绩为72分，期末考试成绩为86分，而期考试占总成绩的40%，期末占60%，这个学生的学期总分是多少？

二、集中量、差异量、标准分、相关系数等 （三）实际应用实例与Excel解答 例3 ：某校初一共有3个班，某次语文测验中，一班50人均分为68，二班45人均分为75，三班40人均分为80，问全校初一语文的平均成绩？ 　　　　不能用：（68+75+80）/3=74.33

二、集中量、差异量、标准分、相关系数等 （三）实际应用实例与Excel解答 例4：某班甲乙两组在一次测验中的成绩分别为65，68，71，72，74（均分为70分）和 30，50，86，90，94（均分为70分）。如何评价两组的学习情况？

二、集中量、差异量、标准分、相关系数等 （三）实际应用实例与Excel解答 方法一：根据定义式计算 方法二：根据原始数据计算

二、集中量、差异量、标准分、相关系数等 （三）实际应用实例与Excel解答 例5：某校期末考试语文平均成绩为69.3分，标准差为11.2分；英语平均成绩为94.8，标准差为13.8分。问哪一学科离散程度大？

二、集中量、差异量、标准分、相关系数等 （三）实际应用实例与Excel解答 例6：设某考区已录取高中学生语文平均分为69分，标准差为12.5分，而未录取高中的学生语文平均分为40分，标准差为12.5分。比较他们语文成绩的离散程度。 CV1=12.5/69 *100%=18.12%； CV2=12.5/40 *100%=31.25%。 未录取学生的离散程度大。

二、集中量、差异量、标准分、相关系数等 （三）实际应用实例与Excel解答 例7：下表中是某班甲乙两同学的期末考试成绩，问：（1）甲同学的语文和数学哪科相对较好？ （2）甲同学和乙同学相比，哪一个学业成绩较好？ 甲 生 乙 生 项目 个人 成绩 所在班级 平均成绩 标准差 Z 语文 73 48.3 13.9 1.78 62 0.99 数学 79 66.9 18.5 0.65 85 0.98 英语 75 67.2 14 0.56 80 0.91 合计 227

二、集中量、差异量、标准分、相关系数等 （三）实际应用实例与Excel解答 例8：数学与物理、物理与英语相关性比较 数学 物理 英语 1 70 75 76 2 60 63 3 82 65 4 44 56 5 52 55 6 90 97 85 7 80 89 48 r 0.91 0.26