用Excel解决数理统计问题

用Excel解决数理统计问题

在微软Office的Excel中有许多函数用于数据处理,其中有些涉及数理统计,使用非常方便。

Excel在原安装中可能没有“数据分析”菜单，建立“数据分析”的步骤是：由“工具”菜单中选择“加载宏”，在弹出的加载宏对话框中选定“分析工具库”和“分析数据库——VBA函数”，确定后“工具”菜单中增加了“数据分析”子菜单。其中有“描述统计”，“协方差”，“相关系数”，“回归”，“方差分析”，“Z—检验”，“T—检验”，“F—检验”等工具。 一、

常用统计量

1．平均数

Excel计算平均数用AVERGE 函数，其格式如下：

=AVERGE （数据1，数据2，…，数据30） 例如输入

=AVERGE （1，2，3，4，5）

则得到平均数3，若要得到工作表中位于E3至E12这组数据的平均数，则输入 =AVERGE （E3：E12）

2、样本标准差 样本标准差的定义是

s??(xi?x)2n?1

Excel计算样本标准差的函数是STDEV，其格式如下： =STDEV （数据1，数据2，…，数据30） 例如输入

=STDEV （3，5，6，4，7，5） 则得到这组数据的样本标准差1.35.输入 =STDEV (E3:E12)

则得到工作表中位于E3至E12的这组数据的样本标准差。 3、样本方差 样本方差的定义是

s2?(x?i?x)2n?1

Excel计算样本方差使用VAR函数，格式为

=VAR （数据1，数据2，…，数据30） 例如输入

=VAR （3，5，6，4，7，5） 则得到这组数据的样本方差1.81.输入 =VAR （E3：E12）

则得到工作表中位于E3至E12的这组数据的样本方差。 二、

区间估计

1、 估计均值

已知方差，估计均值时，使用函数CONFIDENCE，它产格式是：

=CONFIDENCE （显著性水平α，总体标准差，样本容量） 计算结果是z??2n。再用样本均值加减这个值，即得总体均值的置信区间。

如果已知方差，则先用函数SQRT计算平方根，得标准差，再代入。

如果已知一组样本值。则还要用函数AVERGE计算样本均值,然后才能计算置信区间。 例1 已知样本容量n?25，总体的标准差??100，样本均值X?950，取??0.05. 解: 在Excel的一个单元(例如A1)内输入 =CONFIDENCE (0.05，100，25)

用鼠标点击其他任意单元，则公式所在单元显示39.19922。这就是z?在另一个单元 格中输入 =950-A1

则显示910.8008. 这是置信区间的左端点。同样方法可计算置信区间的右端点。即得均 值的置信区间。

例2 对某种钢材的抗剪强度进行了10次测试，测得结果如下（单位：MPa）

?2n的值。然后，

578, 572, 570, 568, 572, 570, 570, 596, 584, 572

2若已知抗剪强度服从正态分布N(?,?)，且??25，求?的95%的置信区间。

2解 打开Excel的一个新工作表。在单元格B2，C2，…，K2内分别输入数据：578， 572，570，…，572。在单元格B3内输入 =AVERAGE （B2：K2） 得到输出x?575.2. 在单元格B4内输入 =STDEV (B2：K2) 得到输出s?8.702. 在单元格B5内输入

=CONFIDENCE (0.05，5，10) 得到输出z??2n?5.394. 在单元格B6内输入

?B3?B5

得到置信下限为572.101, 在单元格B7内输入 ?B3?B5

得到置信上限为578.299. 因此置信区间为 (572.101，578.299).

未知方差，估计均值时，没有这样的可以直接计算的函数，需要一步一步计算。

例3 设总体服从正态分布，已知样本容量n?16，样本均值x?503.75,样本标准差

s?6.2002取??0.05.求均值?的区间估计.

解 打开Excel的一个新工作表，先用函数TINV求T分布的分位点，它的格式是

(显著性水平?，自由度n?1) TINV在单元格B2内输入

=TLNV (0.05，15)

则这个单元将显示2.131451. 这就是t?(n?1)?t0.025(15)的值，再在单元格B3内输

2入

?B2*6.2022SQRT(16)

显示3.304921。这是t?(n?1)2sn的值，在单元格B4内输入

?503.75?B3

得到置信下限为500.4451，再在单元格B5内输入 ?503.75?B3

得到置信上限为507.0549. 因此置信区间为 (500.4451，507.0549). 例4 在例2中，设方差未知，求?的95%的置信区间。

解 在例2中已经算得x?575.2,s?8.702，而样本容量为10。沿用例2中的工作表,在单元格E4中输入

=TLNV (0.05，9)

得到t?(n?1)?t0.025(9)?2.26216，再在单元格E5中输入

2 =E4*B4/SQRT(10) 得到t?(n?1)2sn?6.22539，在单元格E6中输入

?B3?E5

得到置信下限为568.975，再在单元格E7中输入 ?B3?E5

得到置信上限为581.425. 因此置信区间为 (568.975，581.425)

注意 TINV (?,n)给出的是T分布的上?2.估计方差

估计方差时，要用到?分布或F分布。求?分布的上?分位点的函数为CHIINV，它的格式为

=CHIINV (?2或者1??2，自由度n?1)

例 设总体服从正态分布，已知样本容量n?9，样本标准差s?0.007。取??0.05，求总体方差的区间估计.

解 打开Excel的一个新工作表,在单元格B2中输入 =CHIINV (0.025，8)

222分位点。

显示17.53454(??20.025(8))，在单元格C2中输入

=CHIINV (0.975，8)

?(n?1)s2显示2.179725(??0.975(8))。然后用公式?2,???(n?1)2?2(n?1)s2??计算置信区

2?1??2(n?1)??间，在单元格B3中输入

?8*0.007^2/B2 显示0.00002236，在单元格C3中输入 ?8*0.007^2/C2

显示0.0001798，因此总体方差的置信区间为 (0.00002236，0.0001798).

此外，函数FINV可以计算F分布的上?分位点，从而求方差比的置信区间。

三、 假设检验

1. 单个正态总体方差未知时均值的t检验

由于没有一个函数一次完成单个正态总体方差未知时均值的检验，需要分几步计算，所用的检验统计量为 T?X??0S/n

可以用一般统计中介绍的方法计算统计量T和观察值，再用区间估计中介绍的方法得到T分布的上?/2分位点 （双边检验时），比较统计量T的观察值t和T分布的上?/2分位点 （拒绝域为：t?t?/2），便可得到检验结果。

例 设某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车内进行速度测试，得到行驶速度如下：

250 238 265 242 248 258 255 236 245 261 254 256 246 242 247 256 258 259 262 263

该引擎制造商宣称引擎的平均速度高于250 km/h ，请问样本数据在显著性水平为 0.025 时是否和他的声明相抵触?

解 (1) 打开Excel的一个新工作表，单元格B3：F6输入样本数据，如下图

（2）计算样本平均速度，在单元格D8中输入公式： =AVERAGE（B3：F6） 得到平均速度252.05。

（3）计算标准差，在单元格D9中输入公式： =STDEV （B3：F6） 得到标准差8.64185。

（4）在单元格D10中输入样本数20。

（5）在单元格D12中输入T检验值的计算公式： =（D8-250）/（D9/SQRT（D10）） 得到t的值为1.06087。

（6）在单元格D13中输入公式： =TINV （0.05，19） 得到t0.025的值为2.093。

最后的计算结果如下表：

现在的检验问题是：

H0:??250;H1:??250

拒绝域为t?t0.025，由上面的计算得到t?1.06087?t0.025?2.093，因此检验的结果是不拒绝原假设，即无充分证据显示支持引擎制造商声明。

2．两个正态总体方差相等时均值的t检验

为检验两个正态总体方差相等 （但未知）时均值之差的假设： H0:?1??2?d0,H1:?1??2?d0 所用的检验统计量为 t?(X1?X2)?d0Sw11?n1n2(自由度为n1?n2?2和t分布)

Excel在计算时，使用“工具”，“数据分析”，“t-检验：双样本等方差假设”，就得到输出结果。

例 某化工厂试验中要考虑温度对产品断裂韧度的影响，在70℃，80℃条件下分别作了8次重复试验，测得断裂韧度的数据（单位：MPa/m）如下： 70℃时 20.5 18.8 19.8 20.9 21.5 19.5 21.0 21.2 80℃时 17.7 2.03 20.0 18.8 19.0 20.1 20.2 19.1 断裂韧度可以认为服从正态分布。若已知两种温度的方差相等，

1、 问数学期望是否可认为相等 (??0.05)？

2、 求两种温度时的数学期望差的置信区间(??0.05)。

解 1. （1）打开Excel的一个新工作表，在单元格A1中输入标记“70度C”，在单元格B1中输入标记“80度C”。从A2到A9输入70℃时的数据，从B2到B9输入80℃时的数据。 （2）选定“工具”、“数据分析……” （3）选定“t-检验：双样本等方差假设”。 （4）选择“确定”，显示一个对话框。 （5）在“变量1区域”输入A1：A9。 （6）在“变量2区域”输入B1：B9。

2 （7）选中“输出区域”，并在框内输入D1，表示输出结果将放置于从D1开始右下方主单元格中。

（8）在“标志”复选框中打上“√”。如果在“变量1区域”输入A2：A9，在“变量2区域”输入B2：B9，则不选中“标志”复选框。 （9）在“?(A)”内填临界值?为0.05。 （10）在“假设平均差”内填0 。

（11）选择“确定”，得到结果如下图所示。

在单元格E10中，显示统计量t的值为2.160246999，而在单元格E14中显示了临界值为2.144786681，由于2.160246999>2.144786681，表示拒绝原假设，认为两种温度下的数学期望不相等。

2．利用上图所示的结果，也可以得到两个正态总体方差未知（但相等）时均值差的区间估计。

由于检验统计量T?X?YSw11?n1n2，现在已知T，因此SwY的值，X，

11X?Y??。 n1n2T在单元格H4中输入

?(E4?F4)/E10 显示0.46291(?Sw11?)，在单元格H5中输入 n1n2 ?H4*E14 显示0.99284331(?Sw11??t?(n1?n2?2))，在单元格H7中输入

2n1n2 ?E4?F4?H5

显示0.00715669（置信下限），在单元格H9中输入 ?E4?F4?H5

显示1.99284331（置信上限），因此得到均值差的置信区间为（0.00715669，1.99284331）。

注解： 在本例的Excel输出表中，单元格E11给出了单边检验时的P-值：0.024290144，

单元格E13给出了双边检验时的P-值：0.048580288，P-值的定义是：在原假设成立的条件下，检验统计量取其观察值及比观察值更极端的值（沿着对立假设方向）的概率。P-值也称作“观察”到的显著性水平。P-值越小，反对原假设的证据越强，通常若P低于5%，称此结果为统计显著；若P低于1%，称此结果为高度显著。

3．两个正态总体方差是否相等的F检验

假设两总体服从正态分布,在均值未知时作两样本方差是否相等的检验：

2222 H0:?1??2,H1:?1??2

检验统计量为

S12 F(n1?1,n2?1)?2(自由度为(n1?1,n2?1)的F分布)

S2Excel在计算时，使用“工具”，“数据分析”，“F-检验：双样本方差”，就得到输出结果。

例 由一台自动机床加工某型号零件，现在分别从同一月份上旬和下旬产品中随意各取若

干件，测定其直径，得如下数据（单位：mm）

上旬产品： 20.5 19.8 19.7 20.4 20.1 20.0 19.0 19.9 下旬产品： 19.7 20.8 20.5 19.8 19.4 20.6 19.2

假设刀具磨损是引起变化的唯一原因，问检验结果是否表明表明精度显著降低了（??0.05）？

解 （1）打开Excel的一个新工作表，在单元格A1输入“上旬产品”，在单元格B1输入“下旬产品”，从单元格A2至A9输入上旬产品的数据，从单元格B2至B8输入下旬产品的数据。

（2）选取“工具”、“数据分析……”；

（3）选取“F-检验：双样本方差”，选择“确定”。 （4）“在变量1的区域”输入A1：A9； （5）“在变量2的区域”输入B1：B8；

（6）选中“输出区域”，并在框内输入D2，表示输出结果将放置于D1右下方的单元格中。 （7）在“标志”复选框中打上“√”。如果在“变量1区域”输入A2：A9，在“变量2区域”输入B2：B8，则不选中“标志”复选框。 （8）在“?(A)”内填临界值?为0.05。 （9）选择“确定”，得到结果如下图所示。

计算出的F值为0.545618（?s1s2），注意单元格E11中给出的“F单尾临界”值为0.258668，

22它是F0.95(7,6)的查表值，因为0.455618>0.258668，所以不拒绝原假设H0:?1??2。因

22此检验结果认为下介产品的加工精度未显著降低。

四、 方差分析

单因素方差分析的试验数据如下： 试验批号 因素水平 A1 A2 … An 1 2… j… 行平均 X11 X12? X1j… X1n1 X1 X2 … X21 X22? X2j? X2n2 … … … … Xm1 Xm2 Xmj? Xmnm Xn

本文来源：https://www.bwwdw.com/article/az5h.html