Excel求置信区间的方法

应用Excel求置信区间

一、总体均值的区间估计

（一）总体方差未知

例：为研究某种汽车轮胎的磨损情况，随机选取16只轮胎，每只轮胎行驶到磨坏为止。记录所行驶的里程（以公里计）如下：

假设汽车轮胎的行驶里程服从正态分布，均值、方差未知。试求总体均值μ的置信度为0.95的置信区间。

步骤：

16

1.在单元格A1中输入“样本数据”，在单元格B4中输入“指标名称”，在单元格C4中输入“指标数值”，并在单元格A2：A17中输入样本数据。

2.在单元格B5中输入“样本容量”，在单元格C5中输入“16”。

3.计算样本平均行驶里程。在单元格B6中输入“样本均值”，在单元格C6中输入公式：“=AVERAGE（A2，A17）”，回车后得到的结果为41116.875。

4.计算样本标准差。在单元格B7中输入“样本标准差”，在单元格C7中输入公式：“＝STDEV(A2，A17)”，回车后得到的结果为1346.842771。

5.计算抽样平均误差。在单元格B8中输入“抽样平均误差”，在单元格C8中输入公式：“＝C7/SQRT(C5)” ，回车后得到的结果为33

6.7106928。

6.在单元格B9中输入“置信度”，在单元格C9中输入“0.95”。

7.在单元格B10中输入“自由度”，在单元格C10中输入“15”。

8.在单元格B11中输入“t分布的双侧分位数”，在单元格C11中输入公式：“ ＝

26

TINV(1-C9，C10)”，回车后得到α＝0.05的t分布的双侧分位数t＝2.1315。

9.计算允许误差。在单元格B12中输入“允许误差”，在单元格C12中输入公式：“＝C11*C8”，回车后得到的结果为717.6822943。

10.计算置信区间下限。在单元格B13中输入“置信下限”，在单元格C13中输入置信区间下限公式：“＝C6－C12”，回车后得到的结果为40399.19271。

11.计算置信区间上限。在单元格B14中输入“置信上限”，在单元格C14中输入置信区间上限公式：“＝C6＋C12”，回车后得到的结果为41834.55729。

（二）总体方差已知

仍以上例为例，假设汽车轮胎的行驶里程服从正态总体，方差为10002，试求总体均值μ的置信度为0.95的置信区间。

1.在单元格A1中输入“样本数据”，在单元格B4中输入“指标名称”，在单元格C4中输入“指标数值”，并在单元格A2：A17中输入样本数据。

2.在单元格B5中输入“样本容量”，在单元格C5中输入“16”。

36

3.计算样本平均行驶里程。在单元格B6中输入“样本均值”，在单元格C6中输入公式：“=AVERAGE（A2，A17）”，回车后得到的结果为41116.875。

4.在单元格B7中输入“标准差”，在单元格C7中输入“1000”。

5.计算抽样平均误差。在单元格B8中输入“抽样平均误差”，在单元格C8中输入公式：“＝C7/SQRT(C5)” ，回车后得到的结果为250。

6.在单元格B9中输入“置信度”，在单元格C9中输入“0.95”。

7. 在单元格B10中输入“自由度”，在单元格C10中输入“15”。

8. 在单元格B11中输入“标准正态分布的双侧分位数”，在单元格C11中输入公式：“＝NORMSINV(0.975)”，回车后得到α＝0.05的标准正态分布的双侧分位数Z0.05/2=1.96。

9.计算允许误差。在单元格B12中输入“允许误差”，在单元格C12中输入公式：“＝C11*C8”，回车后得到的结果为490。

10.计算置信区间下限。在单元格B13中输入“置信下限”，在单元格C13中输入置信区间下限公式：“＝C6－C12”，回车后得到的结果为40626.875。

11.计算置信区间上限。在单元格B14中输入“置信上限”，在单元格C14中输入置信区间上限公式：“＝C6＋C12，回车后得到的结果为41606.875。

二、总体方差的区间估计（μ未知）

例：假设从加工的同一批产品中任意抽取20件，测得它们的平均长度为12厘米，方差为0.0023平方厘米，求总体方差的置信度为95%的置信区间。

为构造区间估计的工作表，我们应在工作表的A列输入计算指标，B列输入计算公式，C列输入计算结果。

46

56

提示：

① 本表C 列为B 列的计算结果，当在B 列输入完公式后，即显示出C 列结果，这里只是为了让读者看清楚公式，才给出了B 列的公式形式。

② 统计函数“＝CHINV(α，df)”，给出概率水平为α、自由度为v 的χ2分布上侧分位数。

具体使用方法，可以在Excel 的函数指南中查看。

综上所述，我们有95%的把握认为该批零件平均长度的方差在0.00133至0.00491之间。

三、总体比例的区间估计

p n 3.01500450，==p 例：某研究机构欲调查某市大专以上学历的从业人员专业不对口的比率。于是随机抽取了一个由1500人组成的样本进行调查，其中有450人说他们从事的工作与所学专业不对口。试在95%的置信度下构造出该市专业不对口人员所占比率的置信区间。

）－p n(1由于样本容量很大，n ＝1500，样本比例 和

都大于5，故可用正态分布逼近。

构造区间估计的工作表，我们应在工作表的C列输入计算指标，D列输入计算公式，E列输入计算结果。

① 本表E列为D列的计算结果，当输入完公式后，即显示出E列结果，这里只是为了让读者看清楚公式，才给出了D列的计算公式。

② 统计函数“=CONFIDENCE（α，β，δ)”，给出置信水平为1-α、样本比例标准差为β和样本容量为δ的总体比例的误差范围。

具体使用方法，可以在Excel的函数指南中查看。

66