excel中的概率统计.docx
《excel中的概率统计.docx》由会员分享，可在线阅读，更多相关《excel中的概率统计.docx（61页珍藏版）》请在冰豆网上搜索。
excel中的概率统计
数理统计实验
1Excel基本操作
1.1单元格操作
1.1.1单元格的选取
Excel启动后首先将自动选取第A列第1行的单元格即A1（或a1）作为活动格，我们可以用键盘或鼠标来选取其它单元格．用鼠标选取时，只需将鼠标移至希望选取的单元格上并单击即可．被选取的单元格将以反色显示．
1.1.2选取单元格范围（矩形区域）
可以按如下两种方式选取单元格范围．
（1）先选取范围的起始点（左上角），即用鼠标单击所需位置使其反色显示．然后按住鼠标左键不放，拖动鼠标指针至终点（右下角）位置，然后放开鼠标即可．
（2）先选取范围的起始点（左上角），即用鼠标单击所需位置使其反色显示．然后将鼠标指针移到终点（右下角）位置，先按下Shift键不放，而后点击鼠标左键．
1.1.3选取特殊单元格
在实际中，有时要选取的单元格由若干不相连的单元格范围组成的．此类有两种情况．
第一种情况是间断的单元格选取．选取方法是先选取第一个单元格，然后按住[Ctrl]键，再依次选取其它单元格即可．
第二种情况是间断的单元格范围选取．选取方法是先选取第一个单元格范围，然后按住[Ctrl]键，用鼠标拖拉的方式选取第二个单元格范围即可．
1.1.4公式中的数值计算
要输入计算公式，可先单击待输入公式的单元格，而后键入＝（等号），并接着键入公式，公式输入完毕后按Enter键即可确认．.如果单击了“编辑公式”按钮或“粘贴函数”按钮，Excel将自动插入一个等号．
提示：
（1）通过先选定一个区域，再键入公式，然后按CTRL+ENTER组合键，可以在区域内的所有单元格中输入同一公式．
（2）可以通过另一单元格复制公式，然后在目标区域内输入同一公式．
公式是在工作表中对数据进行分析的等式．它可以对工作表数值进行加法、减法和乘法等运算．公式可以引用同一工作表中的其它单元格、同一工作簿不同工作表中的单元格，或者其它工作簿的工作表中的单元格．下面的示例中将单元格B4中的数值加上25，再除以单元格D5、E5和F5中数值的和．
=（B4+25）/SUM（D5:
F5）
1.1.5公式中的语法
公式语法也就是公式中元素的结构或顺序．Excel中的公式遵守一个特定的语法：
最前面是等号（＝），后面是参与计算的元素（运算数）和运算符．每个运算数可以是不改变的数值（常量数值）、单元格或区域引用、标志、名称，或工作表函数．
在默认状态下，Excel从等号（＝）开始，从左到右计算公式．可以通过修改公式语法来控制计算的顺序．例如，公式=5+2*3的结果为11，将2乘以3（结果是6），然后再加上5．因为Excel先计算乘法再计算加法；可以使用圆括号来改变语法，圆括号内的内容将首先被计算．公式=（5+2）*3的结果为21，即先用5加上2，再用其结果乘以3．
1.1.6单元格引用
一个单元格中的数值或公式可以被另一个单元格引用．含有单元格引用公式的单元格称为从属单元格，它的值依赖于被引用单元格的值．只要被引用单元格做了修改，包含引用公式的单元格也就随之修改．例如，公式“=B15*5”将单元格B15中的数值乘以5．每当单元格B15中的值修改时，公式都将重新计算．
公式可以引用单元格组或单元格区域，还可以引用代表单元格或单元格区域的名称或标志．
在默认状态下，Excel使用A1引用类型．这种类型用字母标志列（从A到IV，共256列），用数字标志行（从1到65536）．如果要引用单元格，请顺序输入列字母和行数字．例如，D50引用了列D和行50交叉处的单元格．如果要引用单元格区域，请输入区域左上角单元格的引用、冒号（：
）和区域右下角单元格的引用．下面是引用的示例．
单元格引用范围
引用符号
在列A和行10中的单元格
A10
属于列A和行10到行20中的单元格区域
A10:
A20
属于行15和列B到列E中的单元格区域
B15:
E15
从列A行10到列E行20的矩形区域中的单元格
A10:
E20
行5中的所有单元格
5:
5
从行5到行10中的所有单元格
5:
10
列H中的所有单元格
H:
H
从列H到列J中的所有单元格
H:
J
1.1.7工作表函数
Excel包含许多预定义的，或称内置的公式，它们被叫做函数．函数可以进行简单的或复杂的计算．工作表中常用的函数是“SUM”函数，它被用来对单元格区域进行加法运算．虽然也可以通过创建公式来计算单元格中数值的总和，但是“SUM”工作表函数还可以方便地计算多个单元格区域．
函数的语法以函数名称开始，后面是左圆括号、以逗号隔开的参数和右圆括号．如果函数以公式的形式出现，请在函数名称前面键入等号（＝）．当生成包含函数的公式时，公式选项板将会提供相关的帮助．
使用公式的步骤：
A.单击需要输入公式的单元格．
B.如果公式以函数的形式出现，请在编辑栏中单击“编辑公式”按钮．
C.单击“函数”下拉列表框右端的下拉箭头．
D.单击选定需要添加到公式中的函数．如果函数没有出现在列表中，请单击“其它函数”查看其它函数列表．
E.输入参数．
F.完成输入公式后，请按ENTER键．
1.2几种常见的统计函数
1.2.1均值
Excel计算平均数使用AVERAGE函数，其格式如下：
AVERAGE（参数1，参数2，…，参数30）
范例：
AVERAGE（,,,,）=
如果要计算单元格中Ａ1到B20元素的平均数，可用AVERAGE（A1:
B20）．
1.2.2标准差
计算标准差可依据样本当作变量或总体当作变量来分别计算，根据样本计算的结果称作样本标准差，而依据总体计算的结果称作总体标准差．
（1）样本标准差
Excel计算样本标准差采用无偏估计式，STDEV函数格式如下：
STDEV（参数1，参数2，…，参数30）
范例：
STDEV（3，5，6，4，6，7，5）＝
如果要计算单元格中Ａ1到B20元素的样本标准差，可用STDEV（A1:
B20）．
（2）总体标准差
Excel计算总体标准差采用有偏估计式STDEVP函数，其格式如下：
STDEVP（参数1，参数2，…，参数30）
范例：
STDEVP（3，5，6，4，6，7，5）＝
1.2.3方差
方差为标准差的平方，在统计上亦分样本方差与总体方差．
（1）样本方差
S2=
Excel计算样本方差使用VAR函数，格式如下：
VAR（参数1，参数2，…，参数30）
如果要计算单元格中Ａ1到B20元素的样本方差，可用VAR（A1:
B20）．
范例：
VAR（3，5，6，4，6，7，5）＝
（2）总体方差
S2=
Excel计算总体方差使用VARP函数，格式如下：
VARP（参数1，参数2，…，参数30）
范例：
VAR（3，5，6，4，6，7，5）＝
1.2.4正态分布函数
Excel计算正态分布时，使用NORMDIST函数，其格式如下：
NORMDIST（变量，均值，标准差，累积）
其中：
变量（x）：
为分布要计算的x值；
均值（μ）：
分布的均值；
标准差（σ）：
分布的标准差；
累积：
若为TRUE，则为分布函数；若为FALSE，则为概率密度函数．
范例：
已知X服从正态分布，μ＝600，σ＝100，求P{X≤500}．输入公式
＝NORMDIST（500，600，100，TRUE）
得到的结果为，即P{X≤500}=．
1.2.5正态分布函数的反函数
Excel计算正态分布函数的反函数使用NORMINV函数，格式如下：
NORMINV（下侧概率，均值，标准差）
范例：
已知概率P＝，均值μ＝360，标准差σ＝40，求NORMINV函数的值．输入公式
＝NORMINV（，360，40）
得到结果为400，即P{X≤400}=．
注意：
（1）NORMDIST函数的反函数NORMINV用于分布函数，而非概率密度函数，请务必注意；
（2）Excel提供了计算标准正态分布函数NORMSDIST（x），及标准正态分布的反函数NORMSINV（概率）．
范例：
已知Ｘ～Ｎ（0,1）,计算
=P{X<2}．输入公式
=NORMSDIST
（2）
得到，即
=．
范例：
输入公式=NORMSINV，得到数值２．
若求临界值uα（n），则使用公式＝NORMSINV（1-α）．
1.2.6t分布
Excel计算t分布的值（查表值）采用TDIST函数，格式如下：
TDIST（变量，自由度，侧数）
其中：
变量（t）：
为判断分布的数值；
自由度（v）：
以整数表明的自由度；
侧数：
指明分布为单侧或双侧：
若为1，为单侧；若为2，为双侧．
范例：
设T服从t（n-1）分布，样本数为25，求P（T>）．
已知t＝，n=25，采用单侧，则T分布的值：
＝TDIST,24,1）
得到，即P（T>）=．
若采用双侧，则T分布的值：
＝TDIST,24,2）
得到，即
．
1.2.7t分布的反函数
Excel使用TINV函数得到t分布的反函数，格式如下：
TINV（双侧概率，自由度）
范例：
已知随机变量服从t（10）分布，置信度为，求t
（10）．输入公式
＝TINV,10）
得到，即
．
若求临界值tα（n），则使用公式＝TINV（2*α,n）．
范例：
已知随机变量服从t（10）分布，置信度为，求（10）．输入公式
＝TINV,10）
得到，即（10）=．
1.2.8F分布
Excel采用FDIST函数计算F分布的上侧概率
，格式如下：
FDIST（变量，自由度1，自由度2）
其中：
变量（x）：
判断函数的变量值；
自由度1（
）：
代表第1个样本的自由度；
自由度2（
）：
代表第2个样本的自由度．
范例：
设X服从自由度
=5，
=15的F分布，求P（X>的值．输入公式
=FDIST,5,15）
得到值为，相当于临界值α．
1.2.9F分布的反函数
Excel使用FINV函数得到F分布的反函数，即临界值
，格式为：
FINV（上侧概率，自由度1，自由度2）
范例：
已知随机变量X服从F（9,9）分布，临界值α=，求其上侧分位点（9,9）．输入公式
=FINV,9,9）
得到值为，即（9,9）=．
若求单侧百分位点（9,9），（9,9）．可使用公式
=FINV,9,9）
=FINV,9,9）
得到两个临界值和．
若求临界值Fα（n1,n2），则使用公式＝FINV（α,n1,n2）．
1.2.10卡方分布
Excel使用CHIDIST函数得到卡方分布的上侧概率
，其格式为：
CHIDIST（数值，自由度）
其中：
数值（x）：
要判断分布的数值；
自由度（v）：
指明自由度的数字．
范例：
若X服从自由度v=12的卡方分布，求P（X>的值．输入公式
＝CHIDIST,12）
得到，即
=或
=．
1.2.11卡方分布的反函数
Excel使用CHIINV函数得到卡方分布的反函数，即临界值
．格式为：
CHIINV（上侧概率值α，自由度n）
范例：
下面的公式计算卡方分布的反函数：
＝CHIINV,12）
得到值为，即
=．
若求临界值
（n），则使用公式＝CHIINV（α,n）．
1.2.12泊松分布
计算泊松分布使用POISSON函数，格式如下：
POISSON（变量，参数，累计）
其中：
变量：
表示事件发生的次数；
参数：
泊松分布的参数值；
累计：
若TRUE，为泊松分布函数值；若FALSE，则为泊松分布概率分布值．
范例：
设Ｘ服从参数为４的泊松分布，计算P{X=6}及P{X≤6}．输入公式
=POISSON（6,4,FALSE）
=POISSON（6,4,TRUE）
得到概率和．
在下面的实验中，还将碰到一些其它函数，例如：
计算样本容量的函数COUNT，开平方函数SQRT，和函数SUM，等等．关于这些函数的具体用法，可以查看Excel的关于函数的说明，不再赘述．
2区间估计实验
计算置信区间的本质是输入两个公式，分别计算置信下限与置信上限．当熟悉了数据输入方法及常见统计函数后，变得十分简单．
2.1单个正态总体均值与方差的区间估计：
2.1.1?
2已知时?
的置信区间
置信区间为
．
例1随机从一批苗木中抽取16株，测得其高度（单位：
m）为：
．设苗高服从正态分布，求总体均值μ的的置信区间．已知σ=（米）．
步骤：
（1）在一个矩形区域内输入观测数据，例如在矩形区域B3:
G5内输入样本数据．
（2）计算置信下限和置信上限．可以在数据区域B3:
G5以外的任意两个单元格内分别输入如下两个表达式：
=average（b3:
g5）-normsinv*
）*
/sqrt（count（b3:
g5））
=average（b3:
g5）+normsinv*
）*
/sqrt（count（b3:
g5））
上述第一个表达式计算置信下限，第二个表达式计算置信上限．其中，显着性水平
和标准差
是具体的数值而不是符号．本例中，?
=,
，上述两个公式应实际输入为
=average（b3:
g5）-normsinv*sqrt（count（b3:
g5））
=average（b3:
g5）+normsinv*sqrt（count（b3:
g5））
计算结果为（,）．
2.1.2?
2未知时?
的置信区间
置信区间为
．
例2同例1，但
未知．
输入公式为：
=average（b3:
g5）-tinv,count（b:
3:
g5）-1）*stdev（b3:
g5）/sqrt（count（b3:
g5））
=average（b3:
g5）-tinv,count（b:
3:
g5）-1）*stdev（b3:
g5）/sqrt（count（b3:
g5））
计算结果为（,）．
2.1.3?
未知时?
2的置信区间：
置信区间为
．
例３从一批火箭推力装置中随机抽取10个进行试验,它们的燃烧时间
（单位：
s）如下：
试求总体方差
的的置信区间（设总体为正态）．
操作步骤:
（1）在单元格B3:
C7分别输入样本数据；
（2）在单元格C9中输入样本数或输入公式=COUNT（B3:
C7）；
（3）在单元格C10中输入置信水平．
（4）计算样本方差：
在单元格C11中输入公式=VAR（B3:
C7）
（5）计算两个查表值：
在单元格C12中输入公式=CHIINV（C10/2,C9-1），在单元格C13中输入公式=CHIINV（1-C10/2,C9-1）
（6）计算置信区间下限：
在单元格C14中输入公式=（C9-1）*C11/C12
（7）计算置信区间上限：
在单元格C15中输入公式=（C9-1）*C11/C13．
当然，读者可以在输入数据后，直接输入如下两个表达式计算两个置信限：
=（count（b3:
c7）-1）*var（b3:
c7）/chiinv2,count（b3:
c7）-1）
=（count（b3:
c7）-1）*var（b3:
c7）/chiinv2,count（b3:
c7）-1）
2.2
两正态总体均值差与方差比的区间估计
2.2.1当?
12=?
?
22=?
?
2但未知时?
1-?
2的置信区间
置信区间为
．
例４在甲，乙两地随机抽取同一品种小麦籽粒的样本，其容量分别为5和7，分析其蛋白质含量为
甲：
乙：
蛋白质含量符合正态等方差条件，试估计甲，乙两地小麦蛋白质含量差μ
-μ
所在的范围．（取α＝）
实验步骤：
（1）在A2:
A6输入甲组数据，在B2:
B8输入乙组数据;
（2）在单元格B11输入公式＝AVERAGE（A2:
A6），在单元格B12中输入公式=AVERAGE（B2:
B8），分别计算出甲组和乙组样本均值．
（3）分别在单元格C11和C12分别输入公式=VAR（A2:
A6），=VAR（B2:
B8），计算出两组样本的方差．
（4）在单元格D11和D12分别输入公式=COUNT（A2:
A6），=COUNT（B2:
B8），计算各样本的容量大小．
（5）将显着性水平输入到单元格E11中．
（6）分别在单元格B13和B14输入
=B11-B12-TINV,10）*SQRT（（4*C11+6*C12）/10）*SQRT（1/5+1/7）
和
=B11-B12+TINV,10）*SQRT（（4*C11+6*C12）/10）*SQRT（1/5+1/7）
计算出置信区间的下限和上限．
2.2.2?
1和?
?
未知时方差比σ
/σ
的置信区间
置信区间为
．
例５有两个化验员A、B，他们独立地对某种聚合物的含氯量用相同的方法各作了10次测定．其测定值的方差分别是S
=，S
=．设σ
和σ
分别是A、B所测量的数据总体（设为正态分布）的方差．求方差比σ
/σ
的置信区间．
操作步骤：
（1）在单元格B2,B3输入样本数，C2，C3输入样本方差，D2输入置信度．
（2）在B4和B5利用公式输入
=C2/（C3*FINV（1-D2/2,B2-1,B3-1））
和
=C2/（C3*FINV（D2/2,B2-1,B3-1））
计算出A组和B组的方差比的置信区间上限和下限．
2.3练习题
1.已知某树种的树高服从正态分布，随机抽取了该树种的60株林木组成样本．样本中各林木的树高资料如下（单位：
m）
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
试以的可靠性，对于该林地上全部林木的平均高进行估计．
2.从一批灯泡中随机抽取１０个进行测试，测得它们的寿命（单位：
100h）为：
，，，，，，，，，．
试求总体方差的的置信区间（设总体为正态）．
3.已知某种玉米的产量服从正态分布，现有种植该玉米的两个实验区，各分为10个小区，各小区的面积相同，在这两个实验区中，除第一实验区施以磷肥外，其它条件相同，两实验区的玉米产量（kg）如下：
第一实验区：
62576560635857606058
第二实验区：
56595657605857555755
试求出施以磷肥的玉米产量均值和未施以磷肥的玉米产量均值之差的范围（α＝）
3假设检验实验
实验内容：
单个总体均值的假设检验；两个总体均值差的假设检验；两个正态总体方差齐性的假设检验；拟合优度检验．
实验目的与要求：
（1）理解假设检验的统计思想，掌握假设检验的计算步骤；
（2）掌握运用Excel进行假设检验的方法和操作步骤；（3）能够利用试验结果的信息，对所关心的事物作出合理的推断．
3.1单个正态总体均值μ的检验
3.1.1?
2已知时μ的U检验
例1外地一良种作物，其1000m2产量（单位：
kg）服从Ｎ（800,502），引入本地试种，收获时任取５块地，其1000m2产量分别是800，850，780，900，820（kg），假定引种后1000m2产量Ｘ也服从正态分布，试问：
（1）若方差未变，本地平均产量μ与原产地的平均产量μ
=800kg有无显着变化．
（2）本地平均产量μ是否比原产地的平均产量μ
=800kg高．
（3）本地平均产量μ是否比原产地的平均产量μ
=800kg低．
操作步骤：
（1）先建一个如下图所示的工作表：
（2）计算样本均值（平均产量），在单元格D5输入公式=AVERAGE（A3:
E3）；
（3）在单元格D6输入样本数５；
（4）在单元格D8输入U检验值计算公式=（D5-800）/（50/SQRT（D6）；
（5）在单元格D9输入U检验的临界值=NORMSINV；
（6）根据算出的数值作出推论．本例中，Ｕ的检验值小于临界值,故接受原假设，即平均产量与原产地无显着差异．
（7）注：
在例1中，问题
（2）要计算U检验的右侧临界值：
在单元格D10输入U检验的上侧临界值=NORMSINV．问题（3）要计算U检验的下侧临界值，在单元格D11输入U检验下侧的临界值=NORMSINV．
3.1.2?
2未知时的t检验
例２某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车内进行速度测试，得到行驶速度如下：
250238265242248258255236245261
254256246242247256258259262263
该引擎制造商宣称引擎的平均速度高于250km/h，请问样本数据在显着性水平为时，是否和他的声明抵触
操作步骤：
（1）先建如图所示的工作表：
（2）计算样本均值：
在单元格D8输入公式=AVERAGE（A3:
E6）；
（3）计算标准差：
在单元格D9输入公式＝STDEV（A3:
E6）；
（4）在单元格D10输入样本数20．
（5）在单元格D11输入t检验值计算公式=（D8-250）/（D9/（SQRT（D10）），得到结果；
（6）在单元格D12输入t检验上侧临界值计算公式＝TINV,D10-1）.
欲检验假设
H0：
μ＝250；H
：
μ>250．
已知t统计量的自由度为（n-1）=20-1=19，拒绝域为t>t
=．由上面计算得到t检验统计量的值落在接收域内，故接收原假设H0．
3.2两个正态总体参数的假设检验
3.2.1当?
12=?
?
22=?
?
2但未知时
的检验
在此情况下，采用t检验．
例 试验及观测数据同中的练习题3，试判别磷肥对玉米产量有无显着影响
欲检验假设
H
：
μ1=μ2；H
：
μ1>μ2．
操作步骤：
（1）建立如图所示工作表：
（2）选取“工具”—“数据分析”；
（3）选定“t-检验：
双样本等方差假设”．
（4）选择“确定”．显示一个“t-检验：
双样本等方差假设”对话框；
（5）在“变量１的区域”输入Ａ2：
Ａ11．
（6）在“变量２的区域”输入Ｂ2：
Ｂ11．
（7）在“输出区域”输入D1，表示输出结果放置于D1向右方的单元格中．
（8）在显着水平“α”框，输入．
（9）在“假设平均差”窗口输入0．
（10）选择“确定”，计算结果如D1:
F14显示．
得到t值为，“t单尾临界”值为．由于>，所以拒绝原假设，接收备择假设，即认为使用磷肥对提高玉米产量有显着影响．
3.2.2σ
与σ
已知时
的U检验
例3某班20人进行了数学测验，第１组和第２组测验结果如下：
第１组：
918876989492908710069
第２组：
90918092929498788691
已知两组的总体方差分别是57与53，取α=，可否认为两组学生的成绩有差异
操作步骤：
（1）建立如图所示工作表：
（2）选取“工具”—“数据分析”；
（3）选定“z-检验：
双样本平均差检验”；
（4）选择“确定”,显示一个“z-检验：
双样本平均差检验”对话框；
升级会员 