Excel的统计功能及其应用技巧方案

70
P(x7)0
1
(x10)20
e 222 0d x0.0668
2 02
用NORMDIST粘帖函数计算概率
本计算在编缉栏中的形式为NORMDIST(70，100，20，TRUE)
用NORMDIST粘帖函数计算函数值
本计算在编缉栏中的形式为NORMDIST(70，100，20，FALSE)
已知某品种成年猪体重的总体平均数=100kg， 总体标准差=20kg。如果我们希望淘汰30%体重最 轻的猪，问体重在多少kg以下的猪应给予淘汰。计 算公式如下：
Exce动 •桌面快捷方式启动 •“Microsoft Office”快捷方式启动
工作簿和工作表
•工作簿是计算和储存数据的文件 3个工作表
•工作表可以对数据进行组织和分析表格 256列和65536行组成
单元格和区域引用
引用就是标识工作表上的单元格或单元格 区域。通过引用，可以在公式中使用工作表不 同部分的数据，或者在多个公式中使用同一单 元格的数值。
请计算第一自由度等于2、第二自由度等于4 及F值等于18时的一尾概率
本计算在编缉栏中为FDIST（18，2，4）
用函数FINV计算临界F值
请计算df1=3，df2=10及一尾概率为0.05时的临界F值
本计算在编缉栏中为FINV（0.05，3，10）
EXCEL电子表格的模拟运算 功能
模拟运算表是工作表中的一个单元格区域，它 可以显示公式中某些值的变化对计算结果的影响。 模拟运算表为同时求解某一运算中所有可能的变化 值的组合提供了捷径，并且还可以将所有不同的计 算结果同时显示在工作表中，便于查找和比较。模 拟运算表有两种类型： •单变量模拟运算表 •双变量模拟运算表
请计算自由度等于1及卡方值等于3.84时的一尾概率
本计算在编缉栏中为CHIDIST（3.84，1）
用CHIINV粘贴函数计算临界值
请计算自由度为10及一尾概率为0.01时的2临界值
本计算在编缉栏中为CHIINV（0.01，10）
F分布
F分布的概率密度函数为：
f(F)(d1f)d21f(d1f 2d2f)
正态分布
正态分布的概率函数为：
f (x)
1
(x)2
e 22
2
x , 0
用符号N（，2） 表示
用正态分布粘帖函数的计算
NORMDIST粘帖函数：计算累积函数 NORMINV粘帖函数：计算逆函数
已知某品种成年猪体重的总体平均数=100kg， 总体标准差=20kg。试计算成年猪体重在70kg以下 的概率。计算公式如下：
利用标准正态分布函数计算概率
NORMSDIST粘贴函数：计算累积函数 NORMSINV粘贴函数 ：计算逆函数
已知某品种成年猪体重的总体平均数=100kg， 总体标准差=20kg。试计算成年猪体重在70kg以下 的概率。首先对70kg进行标准化，得u值等于-1.5， 计算公式如下：
1.5
P(u1.5)
使用公式和函数时出现的常见错误信息（二）
#NAME？ 错误原因：删除了公式中使用名称或使用了不存在的名称以及拼写 错误。 解决办法：确认使用的名称确实存在。 #NULL！ 错误原因：使用了不正确的区域运算或不正确的单元格引用。 解决办法：如果要引用2个不相交的区域，请使用联合运算符（逗 号）。例如，=SUM（B2：B5，D2：D5）表示对这2个不相交区域 的引用。 #NUM！ 错误原因：在需要数字参数的函数中使用了不能接受的参数或公式 产生的数字太大或太小，EXCEL不能表示。如在计算临界t值或F值 时，输入的概率值大于1或小于0。 解决办法：检查数字是否超出限定区域，函数内的参数是否正确。
分析工具库的安装
由“工具”菜单打开“加载宏”命令
数据分析工具外观
EXCEL电子表格提供的粘帖函数（一）
1、AVERAGE 2、BINOMDIST 3、CHIDIST 4、CHIINV 5、CHITEST 6、CONFIDENCE 7、CORREL 8、COVAR 9、FDIST 10、FINV 11、FTEST 12、GEOMEAN 13、HARMEAN 14、INTERCEPT 15、MAX 16、MEDIAN
使用公式和函数时出现的常见错误信息（一）
##### 错误原因：公式产生的结果太长，单元格容纳不下。 解决办法：适当增加列的宽度。 #NIV/0！ 错误原因：除数为零。在公式中，除数使用了空白单元格或包 含零值的单元格引用。 解决办法：修改单元格引用，或在用作除数的单元格中输入不 为零的值。 #N/A 错误原因：表示在函数和公式中没有可用的数值可以引用。 解决办法：检查公式中引用的元格的数据，并输入正确数据。
df
f (2)
(2 ) 2 1
2
e2
(
df
df
)2 2
2
2 0
卡方分布的计算实例
CHIDIST粘贴函数：计算单尾概率 CHIINV粘贴函数：计算临界值
CHIDIST函数积分公式为：
P(X2 x2) f(x2)dx2
CHIINV函数积分X2公式为：
P(X2x2) f(x2)dx2 X2
用CHIDIST粘贴函数计算概率
EXCEL电子表格提供的粘帖函数（二）
17、MIN 18、MODE 19、NORMDIST 20、NORMINV 21、NORMSDIST 22、NORMSINV 23、POISSON 24、SLOPE 25、STDEV 26、STDEVP 27、TDIST 28、TINV 29、TTEST 30、VAR 31、VARP 32、ZTEST
计算算术平均值 计算二项式分布的概率值 计算特定χ2分布的单尾概率值 计算一定单尾概率值时的χ2临界值 计算独立性检验的χ2值 计算总体平均值的置信区间 计算两组数据的相关系数 计算两组数据的协方差 计算特定F分布的单尾概率值 计算一定概率时的临界F值 计算二个样本方差之比F值的概率 计算几何平均数 计算调和平均数 计算直线回归的截距 计算最大值 计算一组给定数字的中位数
1
u2
e 2du 0.0668
2
用NORMSDIST粘帖函数计算概率
本例在编缉栏中的形式是NORMSDIST(-1.5)
当给定一尾概率值时，求其临界值就得利 用NORMSINV函数。假定现要计算当一尾概率 为0.025时，计算公式如下：
P(uu1)u 1
1
u2
e 2du0.025
2
用NORMSINV粘贴函数计算
计算最小值 计算一组数据的众数 计算正态分布的累积函数 计算正态分布累积函数的逆函数 计算标准正态分布的累积函数 计算标准正态分布累积函数的逆函数 计算泊松分布的概率 计算给定数据的直线回归系数 计算样本标准差 计算样本总体的标准差 计算学生氏-t分布的概率值 计算特定概率时学生氏-t分布的临界t值 计算t检验时的学生氏-t检验相关的概率 计算样本的方差 计算样本总体的方差 计算u-检验的双尾概率值
利用模拟运算计算临界t值表
单变量模拟计算结果
利用模拟运算计算临界F值表
双变量模拟计算结果
次数分布表
直方图对话框输入格式图示
次数分布表、柏拉图表及其累积频率
接收 15.9 23.9 31.9 39.9 47.9 55.9 63.9 71.9 79.9 87.9 95.9 103.9 111.9 119.9 其他
(d1f1)
F2
d2f
(d1f)(d2f) 22
(1d1fF)(d1f 2d2f) d2f
F>0
F分布的计算实例
FDIST粘贴函数：计算单尾概率 FINV粘贴函数：计算临界值
FDIST函数积分公式为：
P(F f) f(F)dF
F
FINV函数积分公式为：
P(Ff)f(F)dF
F
用函数FDIST计算一尾概率
本例在编缉栏中的形式是NORMSINV(0.025)
学生氏t分布
t分布的概率密度函数为
df1
f (t)
(
) 2
(1
t2
df1
)2
df()(df) df
2
t
学生氏t分布的计算实例
TDIST粘贴函数：单尾或双尾概率
X
P(xX) f (t)dt
TINV粘贴函数：临界t值 （二尾概率 ）
X
P(xX) f(t)dt
2
用TDIST粘贴函数计算二尾概率
例如现要计算自由度等于60，t值与平均数 相差2以上的2尾概率
本计算在编缉栏中输入TDIST（2，60，2）
用TINV粘贴函数计算临界值
请计算自由度为10且二尾概率为0.05时的临界t值
本计算在编缉栏中为TINV（0.05，10）
卡方分布
卡方分布概率密度函数为：
使用公式和函数时出现的常见错误信息（三）
#REF！ 错误原因：删除了由其它公式引用的单元格或将移动单 元格粘帖到由其它引用的单元格中。 解决办法：检查引用单元格是否被删除，或者启动相应 的应用程序。 #VALUE！ 错误原因：需要数字或逻辑值时输入了文本。 解决办法：确认公式或函数所需的运算符或参数正确， 并且公式引用的单元格中包含有效的数值。
频率 35 28 26 21 17 16 13 10 9 8 6 4 4 3 0
累积 % 17.50% 31.50% 44.50% 55.00% 63.50% 71.50% 78.00% 83.00% 87.50% 91.50% 94.50% 96.50% 98.50% 100.00% 100.00%
死亡4头和4头以下概率的计算公式为：
4
P(x4)P(x)0.85 x0
用粘帖函数BINOMDIST计算
死亡4头的概率计算
本计算在编缉栏中为BINOMDIST（4，10，0.30，FALSE）
死亡4头和4头以下概率的计算
本计算在编缉栏中为BINOMDIST（4，10，0.30，TRUE）
普哇松分布
频率 4 6 9 10 13 17 26 35 28 21 16 8 4 3 0
累积 % 2.00% 5.00% 9.50% 14.50% 21.00% 29.50% 42.50% 60.00% 74.00% 84.50% 92.50% 96.50% 98.50% 100.00% 100.00%
接收 71.9 79.9 63.9 87.9 55.9 95.9 47.9 39.9 31.9 103.9 23.9 15.9 111.9 119.9 其他
二项分布
二项分布的概率函数为
P(m)Cn mpmqnm
Cnm
n! m!(nm)!
平均数、方差和标准差
np 2 npq npq
二项分布概率的计算实例
已知某种猪病的死亡率为30%，现有10头病猪， 如不给予治疗，问死亡4头及死亡4头和4头以下的概 率为多少？
死亡4头的概率计算公式为：
P (4)C 1 40 0.340.760.200
单元格引用：B10，A10 区域引用：C5：C15，A5：B10
绝对引用与相对引用
相对引用：A6＝3×B5； 绝对引用：A6＝3×$B$5 行绝对引用：A6＝3×B$5 列绝对引用：A6＝3×$B5
分析工具库提供的统计分析方法
1、单因素方差分析 2、二因素交叉无重复的方差分析 3、二因素交叉有重复的方差分析 4、描述统计分析 5、二样本方差的F检验 6、编制频数分布表及绘制直方图 7、排位与百分位比排位 8、二样本均数差数的u检验 9、配对资料的t检验 10、等方差非配对资料的t检验 11、异方差非配对资料的t检验 12、多元回归和直线回归分析 13、计算多个变量二二之间的相关系数及协方差 14、进行随机和顺序抽样
P(xx1) x 12 0 12
(x10)20
e 222 0d x0.30
用NORMINV粘贴函数计算
本计算在编缉栏中的形式为NORMINV(0.30，100，20)
标准正态分布
标准正态分布的概率函数为：
f (u)
1
u2
e2
u
2
用符号N（0，1） 表示
标准化的公式为： u x
普哇松分布的概率函数
P(k) k e
k!
为常数，它等于平均数等于方差
==2
普哇松分布概率的计算实例
已知某一地区，出现怪胎的事件服从普哇松分 布P（2），请计算该地区出现3次怪胎的概率，及出 现3次和3次以下怪胎的概率为多少？
出现3次怪胎概率的公式为：
P(3)23 e2 0.1804 3!
出现3次和3次以下怪胎的概率计算公式为：
P (k 3 )3P (k)2 0e 22 1e 22 2e 22 3e 2 0 .857
k 0
0 ! 1 ! 2 ! 3 !
用POISSON粘帖函数计算
出现3次怪胎概率的计算
本计算在编缉栏中显示POISSON(3，2，FALSE)
出现3次和3次以下怪胎的概率计算
本计算编缉栏中显示POISSON(3，2，TRUE)