Excel在统计中的应用与数据统计分析

Excel在统计中的应用

Excel与数据统计分析

一、试验说明

（一中文Excel简介

MicrosoftExcel是美国微软公司开发的Windows环境下的电子表格系统，它是

目前应用最为广泛的办公室表格处理软件之一。自Excel诞生以来Excel历经了

Excel5.0xExcel95sExcel97和Excel2000等不同版本。随着版本的不断提高,Excel

软件的强大的数据处理功能和操作的简易性渐渐走入了一个新的境界，整个系统的

智能化程度也不断提高，它甚至可以在某些方面推断用户的下一步操作，运用户操作

大为简化。Excel具有强有力的数据库管理功能、丰富的宏吩咐和函数、强有力的

决策支持_1_具、图表绘制功能、宏语言功能、样式功能、对象连接和嵌入功能、连

接和合并功能，并且操作简捷，这些特性，已使Excel成为现代办公软件重要的组成部

分。

由于大家对Excel的常用办公功能都比较熟识，本试验重点介绍Excel在统计分

析中的应用。

（二试验目的与要求

本试验重点介绍Excel在统计分析中的应用，包括Excel在描述统计中的应用以

及Excel在推断统计中的应用，要求学生娴熟驾驭运用Excel

进行统计分析的方法，并能够对分析结果进行说明。

二、试验

试验一Excel在描述统计中的应用

试验目的及要求

要求学生驾驭运用Excel进行描述统计分析、绘制各种图表和运用数据透视表

工具的技术。

试验内容及步骤

（一描述统计分析

例1「：表1」是1978-2005年我国城馍居民可支配收入数据，试求城馍居民可支

配收入时间序列的基本统计量。

表1-11978-2005年我国城镇居民可支配收入（元

年份城镇居民可年份城镇居民可

支配收入支配收入

197834119922026.6

197940519932577.4

1980477.619943496.2

1981500.419954283

1982535.319964838.9

1983564.619975160.3

1986899.620006280

19871002.220016859.6

1986899.620006280

19871002.220016859.6

19881181.420027702.8

1989137920038472.2

19901510.220049421.6

19911700.6200510493

STEP1:用鼠标点击工作表中待分析数据的任一单元格。STEP2:选择“工具”菜

单的“数据分析”子菜单C

STEP3:用鼠标双击数据分析工具中的“描述统计”选项。STEP4:出现“描述统计”

对话框，如图1-1所示。

图1-1

对话框内各选项的含义如下：

输入区域:在此输入待分析数据区域的单元格范围。

分组方式:假如须要指出输入区域中的数据是按行还是按列排列,则单击“行’或

“列二

标记位于第一行例:假如输入区域的第一行中包含标记项（变量名测选中“标记

位于第一行”复选框;假如输入区域的第一列中包含标记项，则选中“标记位于第一

列”。

复选框:假如输入区域没有标记项，则不选仟何复选框,Excel将在输出表中牛成

相宜的数据标记。

均值置信度:若须要输出由样本均值推断总体均值的置信区间，则选中此复选框,

然后在右侧的编辑框中输入所要运用的置信度。例如，置

信度95%可计算出的总体样本均值置信区间为10,则表示:在5%的显著水平下

总体均值的置信区间为（X-10,X+10。

第K个最大/小值:假如须要在输出表的某一行中包含每个区域

的数据的第k个最大/小值，则选中此复选框。然后在右侧的编辑框中，输入k的

数值。

输出区域:在此框中可填写输出结果表左上角单元格地址,用于控

制输出结果的存放位置。

新工作表:单击此选项,可在当前工作簿中插入新工作表，并由新工作表的A1单

元格起先存放计算结果。假如须要给新工作表命名厕在右侧编辑框中键入名称。

新工作簿:单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算

结果。

汇总统计:指定输出表中生成下列统计结果，则选中此复选框。

STEP5:填写完“描述统计”对话框之后，按，确定，.按扭即可。结果如图1-2所示。

ElicrosoftExcel-Book1

文件m蜩缭如<RE（y）a\a）格式（o工且（I）助88）甯口3）*»）«）

!/）•@.网_•*2.B/U三三三日名孽_•

31列1

32

平均

333384.860714

标准

误

590.1963594

21中位

35数1863.6

众湫

差

跄枸准#N/A

37方差3123.025583

38嘘度9753288.794

-0.51564107

39涮度

40区埔0.838973153

41最小10M9.6

值

42343.4

43值10493

44数94776.1

4「a28

4610493

最小a

47

信

置“3.4

度

毁1210.982887

49

；除图◎）・7自博图&Q）・、、口

图1-2描述统计结果

结果说明:描述统计工具可生成以下统计指标，按从上到下的依次其中包括样本

的平均值、标准误差、组中值、众数、样本标准差、样本方差、峰度值、偏

度值、极差、最小值、最大值、样本总和、样本个数和肯定显著水平下总体均

值的置信区间。

（二绘图操作

例1-2:仍以例1-1的数据为例，绘制城镇居民可支配收入时间序列的散点图。

STEP1:拖动鼠标选定数值区域A2:C12,不包括数据上面的标记项。

STEP2:选择“插入”菜单的“图表”子菜单,进入图表向导，图1-3所示。

STEP3:选择“图表类型”为“散点图”，然后单击“下一步工

L3

：文何以)惯用(T捶入⑴格式B)IRQ)窗口⑺««)«)

：朱体丘・B/U毒手李

f*197R

AIB图*向号4寸建之1用表类至

年份城填居民可支配收

19781343

0

98兜1

2

兜

兜3S6q

4652

98兜5739

L98€899

19871002

19881181

19891373

19901510

19911700

19922026

19932577I)尸一方3花璃＜t)]

iqqd,,346

H\I^A/Sbeet2/Sheet3/

:是SlaA-；自信脚&)•\、Op:J」-4用口

图1-3图表向导图

STEP4:确定用于制作图表的数据区。Excel将自动把你前面所选定的数据区的

地址放入图表数据区的内，单击下一步按钮,出现图1-4所示对话框。

--15>,

«性①）惕®粗图«）（V格式如Tfll）表帙6）电口（I）期勖值）

源数据

公1"8

B

21972343.

319794c

41980477.

E00.

535.

564.

652.

739.

899.

)002.fids区域6）-收入*AT2

1181.

系列产生在:。行用

1373.

©列」）

1510.

1700.

2c26.

2577.

181004343G.

，»|\^A/Sheet2/Sheet；/

［耳浦Ik上一步＜1）I■一步S）〉）

心团母）•si&m@)•

图1-4作图过程图

STEP5:填写图表标题、X轴坐标名称、Y轴坐标名称,此处不用填写，单击“下一

步:

STEP6:选择图表输出的位置，然后单击“完成，按扭即生成

（三数据透视表工具

数据透视表是Excel中强有力的数据列表分析工具。它不仅可以用来作单变量

数据的次数分布或总和分析，还可以用来作双变量数据的交叉频数分析、总和分析

和其它统计量的分析。

例1-3:表1-2列出了学牛两门功课评定结果,建立学牛两门功课评定结果的交叉

频数表。

表1-2学生两门功课评定结果

学号语文数学学号语文数学

1001优1006中良

1002良中1007中优

1003中中1008差良

1004差中1009良中

1005差：.差

操作步骤:

STEPI:输入数据，选中有数据的任一单元格.然后选择“数据”菜单的“数据透视

表“子菜单，进入数据透视表向导。

STEP2:选择“Micros。仕Excel数据清单或数据库”为数据源。图1-6所示，单击

“下一步”。

Excel-Book1|np<J

♦史］文件9喇偌嗔）稹图⑼插入UJ格式也J_LA<L）数163JMUQP阳助小）-fl1x

国；宋体Z,J2▼Bzy里萋堇T€津潇・A

C20

致抿话和表和数据话被图向导-3步骤2im区।

之

^€语r

「-

1r37.请搐定待分析数据的数据涯芟鳌

10优

2o1F*..1.ILr-.OfiiceExcel数j?克）表或数龙虎色）

10艮

-1_o2。外部数据照国）

10中

4o3O多重合井i+苴数据区般（£）

104差

5o

1o05差

6_

1o06中

77

1o0中

80明希切逑的强表英筌.

o0差

10®数据透视表支）

9o0艮

19o数珞透视图m数籍透视表）5）

10

12,

13I

H<>»i\Sheetl/S>I下一步理J>I|初9

：法民4）▼7目法图杉u▼\、LJ。±J」TI：二里J3、才▼*。A▼==

姓绿数字

图1-6

STEP3:选择待分析的数据的区域，一般状况下Excel会自动依据当前单元格确

定待分析数据区域，因此你只要干脆单击“下一步”按扭即可。STEP4:确定数据透视

表的结构，在此例中，要建立的是一个交叉频数表,分别按语文和数学的成果对学生的

人数进行交叉频数分析，因此可将三个按扭“学号”、“语文”、“数学”分别拖放到表格

的指定部位，并且双击“求和项:学号''，将其改为记数项，结果如图1-7所示。

图1-7布局对话框

STEP5:选择数据透视表的显示位置之后、单击“完成按扭”，可出现如图1-8所示

的数据透视表。

图1-8

结果说明:如图1-8的结果所示，数据透视表可以作为一个交叉频数分析工具。

完成数据透视表之后，可按须要修改数据表的显示格式。例如，假如想要把表格中的

频数替换成为百分比数。可以用鼠标右击频数的任一单元格，选择“字段”子菜单，单

击“选项”按扭，将“数据显示方式”替换成为“占总和的百分比”，然后单击“确定”按扭

即可。按同样方式,可将数据透视表修改成为其它不同样式。

试脸二Excel中的二项分布工具

试验目的及要求

驾驭利用Excel的BINOMDIST的函数计算二项分布的概率以及累积概率。

试验内容及步骤

例14一个推销员打了六个电话，推销胜利的概率是0.3,建立推销胜利次数的概

率分布图表。可以按以下步骤建立推销胜利次数的概率分布图表。

STEPI:如图1-9所示，先在Excel之下建立好概率分布表格的框架。

ABCDEF

二分布翻率分布三

1jjj

2试验总次数6

3每次成功概率0.3

4

5

6成功次数(k)P(Y=k)P(Y<=k)|P(Y<k)|P(Y>k)P(Y>=k)

70___!

81

92

103

114

125

136

图1-9

STEP2:如图1-10所示，先在B7至F7单元格分别输入概率计算公式。

ABCDEF

1二项分布概率分布表

2试验总次数6

3每次成功概率0.3

4

5概率

6成功次数(k)P(Y=k)P(Y<=k)P(Y<k)P(Y>k)P(V>=k)

°

=BINOlvIDIST(A7r5,03,0)=BINQMDIST(A7,6Q31)=CT-B7=1-C7=1-D7

81

92

103

114

125

136

图1-10

STEP3:公式的拷贝。选取B7至F7单元格，拖动"填充柄''至F13单元格

即可完成公式的拷贝操作。结果图1-11所示。

ABCDEF

1二项分布暇至分布表

2试验总次数6

3每次成功概率0.3

4

5概率

6成功次数(k)P(Y-k)P(Y<=k)P(Y<k)P(Y-k)p(y>=k)

x0|=BINOMDIST(A7,5,。3,0)|=BINQMDI5T(AZ6,031)=CT-B7=L-C7=1-Di

81

92

103V

114

125

136

图1-11

STEP4:下面起先创建二项分布图表。选取B7至B13单元格.选取“插入”菜单

的“图表”子菜单。

STEP5:选择“柱状图”，然后单击“下一步”。

STEP6:单击“系列”标签，单击“分类（X轴标记”框,并用鼠标选取A7至A13单元

格为图表X轴的轴标，然后单击“下一步工

STEP7:分别键入图表名称“二项分布图”,X轴名称“胜利次数”,Y轴名称“胜利概

率”，单击“完成”按扭即可生成二项分布图表。

结果说明：如图1-H所示，利用Excel的BINOMDIST的函数可以计算出二项分

布的概率以及累积概率。BINOMDIST函数可以带四个参数，各参数的含义分别是：

试验胜利的次数,试验的总次数，每次试验中胜利的概率,是否计算累积概率。

四个参数是一个逻辑值,假如为TRUE,函数BINOMDIST返回累积分布函数，假

如为FALSE,返回概率密度函数。

另外,EXCEL还供应了其它分布的函数,如函数CRITBINOM;函数

HYPGEOMDIST;

函数NEGBINOMDIST:函数POISSON:正态分布函数NORMDIST:函数

NORMSDIST:函数NORMSINV:t分布函数TDIST:

有爱好的同学可以自己探讨。

试验三随机抽样工具

试验目的及要求

驾驭利用Excel的BINOMDIST的函数计算二项分布的概率以及累积概率。

试验内容及步骤

Excel中的Rand（函数可以返回大于等于0小于1的匀称分布随机数,Rand（不带

任何参数运行，每次计算时时都将返回一个新的数值。RAND（函数可以被用来作为

不重复抽样调查的工具。

例1-5:如图1・12所示有10个象征性的样本数据，欲从中随机抽取5个数据可按

如下步骤操作：

STEP1:选择B2单元格，输入公式』RAND（"并回车。

STEP2:拖动B2单元格右下角的填充柄至Bl1单元格，并在B1单元格输入标题

“RANDOM”。

STEP3:选取单元格B2至Bl1,右击选中的区域选择“复制”，再次右

击选中的区域，选择“选择性粘贴”，单击选项“数值”后，点击“确定”按扭。

STEP4:选取单元格A2至Bl1单元格，选择“数据”菜单项下的排序子菜单。

STEP5:选取“RANDOM”为主要关键字，然后点击“确定”按扭。排序结果如图1-

13所示,A2至A6单元格的样本即为随机抽取的5个样本。

AJB

1NoIrandorn

2100.216G96

*30.2340M

410.302342

59j.-

690.61网1

7T0.64232

840.656722

920.68924

50.882674

1160.953918

图1-13

试验四由样本推断总体

试验目的及要求

驾驭利用Excel的几个函数,如求平均函数AVERAGE,标准差函数STDEV、

T分布函数TINV等的组合运用构造一个特地用于实现样本推断总体的Excel工作

表O

试验内容及步骤

下面的例子先计算洋本的平均数和标准差，然后在肯定置信水平上估计总体均

值的区间范围。操作步骤：

STEP1:构造工作表，如图1-14所示，首先在各个单元格输入以下的内容,其中左

边是变量名，右边是相应的计算公式。

STEP2:为表格右边的公式计算结果定义左边的变量名。选定A4:B6,A8:B8和

A10:BI5单元格（先选择第一部分,再按住CTRL键选取另外两个部分，选择“插入”菜

单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项，然后点击“确定”按狂即

可。

AB

1以样本均值推断总体均值的置信区间

,>1|I

3样本统计量

4样本个数X0UNT（样本数据）

5样本均值=AVERAGE（样本设据）

6样本标准差二STDEV（样本数据）

7用户输入

8百信水平0.95

9计算结臬

10持归有色误」样本标;底'/3QKTC杵本个故＞

11自由度二'样本个蚊-1

12曲=TINV（1J置信水平'，’自由度’）

13置信区间半径='1值"'抽样标准误’

14置信区间上杲二'样本均值'置信区间半径’

15置信因而下界卜样本均僮V置信区而半径’

图1-14

STEP3:输入样本数据，和用户指定的置信水平0.95,如图附-13所示。

STEP4:为样本数据命名。选定DLD11单元格，选择“插入”菜单的“名称”子菜单

的“指定”选项，用鼠标点击“首行'选项，然后点击“确定”按扭，得到图M5所示的计算

结果。

AB1CD

1以样本均值推断总体均值的置信区间样本效据

228.5

3样本统计量26.4

|4样本个数1033.5

5样本均值31.434.3

6样本标准差2.81424945635.9

7用户输入29.6

8置信水平0.9531.3

9计算结臬31.1

10才蟒标准误0.88994381830.9

11自932.5

12信2.262158887

13置信区间半径2.013194318

14置信区间上界23.38680568

15置信区间卜职33.41319432

图1-15

结果说明:以上例子说明如何交叉组合运用Excel的公式和函数，以构造出一个

能实现样本推断总体有关计算的Excel工作表。事实上，在用Excel进行数据统计处

理之时，很多统计功能可以运用和上例类似的方法,通过组合运用Excel的各类统计

函数和公式加以实现的。

试验五假设检验

试验目的及要求

驾驭利用Excel的正态分布函数NORMSDIST,推断函数IF等,构造一张能够

实现在总体方差已知状况下进行总体均值假设检验的Excel工作表。

试验内容及步骤

例1-6:利用Excel的正态分布函数NORMSDIST,推断函数IF等，构造一张能

够实现在总体方差已知状况下进行总体均值假设检验的Excel工作表。

操作步骤:

STEP1:构造工作表，如图1-16所示，首先在各个单元格输入以下的内容,其中左

边是变量名，右边是相应的计算公式。

STEP2:为表格右边的公式计算结果定义左边的变量名。选定

A3:B4,A6:B8,A1O:A11,A13:A15和A17:B19单元格、选择“插入”菜单的“名称”子菜单

的“指定”选项，用鼠标点击“最左列”选项，然后点击“确定”按扭即可。

人「，c

1户麻好心的作H个2怖

22H-f>

3样本个■正1Ozn.4

4"木’匀,ss.n

»*入3<.3|

C9.9033.9

73&2d3

B京信叱产0.9631-3

QifHK5M:11

1。仙”行MWL1.TbOyatJaTH3(J.B

11廿单”佰—12.O4TB17*3013az.a

12皿网

13EdlifK低一，.

性版抬印.

1-qpj'i'HX-O020303562

XG双他1根&女

：内何化值-1.UtJ2

1O电I©

1Q双［则同普*中••••1*•

图1-16

STEP3:输入样本数据，以及总体标准差、总体均值假设、置信水平数据。如图

1-17所示。

STEP4:为样本数据命名。选定C1:C11单元格，选择“插入”菜单的“名称”子菜单

的“指定”选项，用鼠标点击“首行、选项，然后点击“确定”按扭彳导到如图M7中所示的

计算结果。

7

»

Q

图1-17

结果说明:如图1-17所示,该例子的检验结果不论是单侧还是双侧均为拒绝H。

假设。所以,依据样本的计算结果，在5%的显著水平之下，拒绝总体均值为35的假

设。同时由单侧显著水平的计算结果还可以看出,在总体均值是35的假设之下.样本

均值小于等于31.4的概率仅为0.020303562o

试验六双样本等均值假设检验

试验目的及要求

驾驭利用Excel数据分析中供应双样本等均值假设检验工具进行假设检验的方

法，并能够说明试验结果。

试验内容及步骤

例1-7:双样本等均值检验是在肯定置信水平之下，在两个总体方差相等的假设

之下，检验两个总体均值的差值等于指定平均差的假设是否成立的检验。假设某工

厂为了比较两种装配方法的效率,分别组织了两组员工，每组9人，一组采纳新的装配

方法，另外一组采纳旧的装配方法。18个员工的设备装配时间图1-18中表格所示。

依据以下数据，是否有理由认为新的装配方法更节约时间？

LABCD

1.旧方法装配时间—组别新方法装配时间

21132235

31-37231

4135229

5138225

6141234

7144240

81352Z7

9131232

10134231

图1-18

操作步骤:

STF.PI:选择“工具”菜单的“数据分析”子菜单，双击”检验:双样本等方差假设”

选项，则弹出图1-19所示对话框。

A_____B_

1L线：双样本笨方差假设

2

3_______________旧方法装配时间而方法装配时间

4135.22222222]31,55555556

5方差21.4444444420.02777778

6观测值T99

7合并方差22.23611111

I____0

8假设平均差

9df16

10tStat1.649钻4617

11P(T<=t)单尾0.059269899

12tM®战1.745884219

13P(T<=t)双星0.118539799

14：t双里兔2.119904:821

图1-19

STEP2:分别填写变量1的区域:变量2的区域:$D$1:$D$10,由于我

们进行的是等均值的检脸，填写假设平均差为0,由于数据的首行包括标记项选捧标

记选项，所以选择“标记”选项，再填写显著水平a为0.05,然后点击“确定”按扭。则可

以得到图1・20所示的结果。

ABC

1L检验：双样本零方差假设

2

3旧方法装期时间新方法装配时间

4平均135,222222221131.55555556

5方差24：.444n20.02777778

6观测值99

7合并方差22.23611111

8平均差0

9df16

10tStat1.649434:617

11P(T<=t)单尾0.059269899

121单尾临界1.715834219

13P(T<=t)双尾0.118539799

142.11992821

图1-20

结果分析:如图1-20中所示，表中分别给出了两组装配时间的平均值、方差和样

本个数。其中，合并方差是样本方差加权之后的平均值.Df是假设检验的自由度它等

于样本总个数减2,1统计量是两个样本差值减去假设平均差之后再除于标准误差的

结果,“P(T<=t单尾”是单尾检验的显著水平,“t单尾临界”是单尾检验t的临界

值,“P(Tv=t双尾”是双尾检验的显著水平Jt双尾临界”是双尾检验t的临界值。由下

表的结果可以看出t统计量均小于两个临界值,所以在5%显著水平下，不能拒绝两

个总体均值相等的假设，即两种装配方法所耗时间没有显著的不同。

Excel中还供应了以下类似的假设检验的数据分析工具，它们的名称和作用如下:

检验:双样本异方差假设”

“t-检验:成对双样本均值分析”

“Z-检验:双样本均值分析”

试验七正态性的卡方检验

试验目的及要求

驾驭利用Excel进行总体卡方分布检验、总体泊松分布、总体超几何分布等其

它分布的检验的方法步骤，并能够说明试验结果。

试验内容及步骤

卡方检验检验可以用来推断所观测的样本是否来自某一特定分布的总体，这种

检验亦称为一样性检验。

例1-8:已知某样本的相关统计量和分组频数分布如图1-21所示,试用卡方检验

推断该样本是否来自一正态总体。

「IBC

1正态蛤1n2检验

2

3隹本个^

200110

5_

6|分组下界分组上界真实须致

715015

8'15016054

916017078

1017018042

1118011

12I累积食I200

图1-21

操作步骤：

STEPI:创建变量名，选定A3:C4单元格，选择“插入”菜单的“名称”子菜单的“指

定''选项，用鼠标点击“首行”选项，然后点击“确定”按扭即可。

STEP2:计算预期正态概率值。如图1-22表中所示，在D6单元格输入标记项，在

D7:D11单元格输入公式,分别计算各组的预期正态概率值，

在D12计算累积概率值。

6预期正态概率

7产N0RMDISTCB7,样本均值,样本标准差,1）

8=N0RMDIST（B8,样本均值,样本标准差,1）-NORMDIST<A8,样本均值,样本株准差，1）

9_=NORMDIST（B9,样本均值样本标准差,1）-NCMDIST（A9,样本均值,样本株准差，1）

10_=NORMD工ST（B10,洋水均值样本标准差，l）-WRMDIST（A10,样本均值,样本标准差，1）

11=1-NORNDIST（AL1,样本均值，样本标准差，1）

12=SUM(D7:D11)

图1-22

STEP3:计算预期频数值。如图1-23所示，在E6单元格输入标记项，在E7:E11

单元格输入公式，分别计算各组的预期频数，在E12计算累积频数值。

6

7]=NORMDIST(B7,14^±

81二KORMDIST(B8,样出

91NORMDIST(B9,样本上

10l=NORMr)IST(B10,^

11J=l-ffORMDIST(ALl,样

12=SrJH(D7:Dll)

图1-23

STEP4:计算X2统计量。如图1-24所示，在F6单元格输入标记项，在F7:F11分

别输入计算公式，分别计算X2值，在E12计算X2平方和，这项就是最终计算出的X2

统计量。在E13单元格输入标记项“卡方统计量”，为以后的引用作打算。先选中

F12、F13两个单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠

标点击“尾行”选项，然后点击“确定”按扭即可。

图1-24

STEP5:如图1-25所示,分别在A14到B20单元格输入自由度、X2概率值、置

信水平、临界值、X2检验结果几项的标记值及计算公式。其中的自由度二区间分

段数-正态分布参数个数-1=5-2-1=2。

图1-25

STEP6:结果如图1-26所示。

AB,c■D1F___|

11正态性的g检验

N

3样本个数样本均值样本标准差

420016410

5

6分组1、畀分组上畀真实领数预期正态概率卡方值

7150150.0807567110.082073

8150160540.2638215920.0289383

9160170780.3811686320.0409231

10170180420.2194537750.0814512

11180110.0547992890.000147

12累20010.2335326

13卡方统计量

14自_____2

15卡方概率值0.889793097

16

17置信水平0.01

18临界值9.210351036

19卡方检验结臬接受总、体为正太分布的假设

图1-26

结果分析:如图1-26所示，依据以上操作步骤可以得到表中的计算结果。按同样

的方法可以作总体泊松分布、总体超几何分布等其它分布的检验。此类统计应用也

是由Excel各类公式和函数综合运用而实现的，为了以后运用便利，和上面的一些例

子一样，一般须要将整个表格的计算框架和标记项排列好,再保存成文件，以后只要对

数据项稍作修改即可很快得到计算结果。假如对Excel宏语言较为熟识，还可以将

它编成一个宏语言程序，加入Excel的工具栏，这样以后运用起来更为便利。

试验八列联表分析

试验目的及要求

列联表分析常常用来推断同一个调查的对象的两个特性之间是否存在明显相关

关系。该试验要求学生驾驭利用Excel实现列联表分析的方法步骤.并能够说明试验

结果。

试验内容及步骤

例1-9:如图1-27所示，表中是某装修公司的调查报告，试用列联表分析方法分析

在顾客的所在地区和所选房子的地板类型之间是否存在明显的相关关系。

ABcIF

1列联奏分析

2

3真实频数地区1地区2地区3地区4行点裁

4木用地板7281223115

5的岩地板2610163385

6方?石地板710141950

7列总数105284275250

图1-27

操作步骤：

STEP1:建立期望频数表。如图1-28所示，先建立期望频数表的框架，然后在B10

单元格输入公式“=B$7*$F4/$F$7”,再利用“填充柄”将公式复制到表格的其它单元格,

最终利用Excel的求和函数sum计算行和与列和。

STEP2:计算X2概率值。在A15单元格输入标记项“卡方概率值”,先点击B15

单元格，从“插入”菜单中“函数”子菜单，选择“统计函数”中的“CHITEST”函数，单占“确

定按扭，然后在弹出的对话框中分别添入实际频数范围“B4:E6”和预期频数范围

“B10:E12"。最终单击“确定”按扭即可得到计算结果1.3E-07,如图1-29所示。

ABCDEF

1列跌表分析

2

3直实频数地区1地区2地区3地区4行点数

4木质地板7281223115

5钢砖地板261016_3385

6大理石地板710141950

7列总数105284275250

8

9期望颁数地区1地区2地区3地区4总数

10木质地板48.312.8819.3234.5115

11钢徜地板35.79.5214.2825.585

12龙板215.68.41550

13总数105284275250

14

[

15卡后概率值11E-07I

图1-29

STEP3:建立X2统计表。如图1-30所示，先建立表格的框架，然后在B20单元格

输入公式“=(B4-B10八2/B10”,再利用填充柄将公式复制到表格的其它单元格。最终

计算X2卡方统计量,分别在A24与B24单元输入标记项与计算公式。

ABCDJE1F」

1歹感表分析

2

3直实蛭数地区1地区2地区3地区4行回救

4木角地板7281223115

5羽砖地板2610163385

图1-30

STEP4:进行假设检验。如图1-31所示，分别输入置信水平、临界值和假设睑验

的结果其中CHIINV函数的自由度二(第一类属性的分类数-1*(其次类属性的分类数

-1=(3-1*(4-1=6。

AB

26詈信水平0.01

27临果值=CHITN7(B26,6)

28避结果卡词计宴1肥?通号睦两幅在不相融假设;接受所幅性不相劫假设")

图1-31

结果分析:以上的操作步骤完成整个列联表的分析。其中,B15单元格的卡方概

率值与B24单元格的卡方统计量是表格的两个重要计算结果。其中卡方概率值等

于1.3E-07表明:假如总体的两类属性，即所在地区和所选地板类型，是不相关的，那么

得到以上视察的样本的概率是0.00000013。这个概率几乎接近于0,所以可以认为总

体的这两个属性是显著相关的。

试验九单因素方差分析

试验目的及要求

驾驭利用Excel进行单因素方差分析、无重复双因素方差分析、有重复双因素

方差分析的步骤和方法，并能够说明试验结果。

试验内容及步骤

单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立。

此方法是对双均值检验(如t-检验的扩充。检验假定总体是听从正太分布的,总体方

差是相等的，并且随机样本是独立的。这种工具适用于完全随机化试验的结果分

析。例1-10：如图1-32中所示，一产品制造商雇佣销售人员向销售商打电话。制造商

想比较四种不同电话频率安排的效率，他从销售人员中随机选出32名,将他们随机安

排到4种安排中，在一段时期内记录他们的销售状况已经在表中列出,试问其中是否

有一种安排会带来较高的销售水平。

ABCD

1单因素方差分析

2

3计划1计划2计划3出加

43613914：31

540,454：343

632543846

744534=043

833464：136

941423549

1044353746

1142393748

图1-32

操作步骤：

STEPI:选择“工具”菜单的“数据分析”子菜单，双击“方差分析:单因

素方差分析''选项，弹出单因素方差分析对话框°STEP2:按图1-33所示方式填

写对话框。

方差分析：单因素方差分析BB

输入----------------------・—:

确定

输入区域（D：国

『取消

分组方式：C歹U（©

「行®帮助®I

P标志位于第一行也）

a（A）:|o.05

输出选项----------------------------------------

「输出区域9）：一

G新工作熟旦（E）：

「新工作薄（给

图1-33

STEP3:单击“确定”笈扭，得到图1-34的计算结果。

图1-34

结果分析:

图1-34中表格的其次部分则是方差分析的结果。SS列分别给出了四个分组的

组间方差、组内方差以及总方差,DF列分别给出了对应方差的自由度,MS列是平均

值方差，由SS除于DF得到，它是总体方差的两个估计值。F列是F统计量的计算结

果，假如四个总体均值相等的假设成立的化，它应当听从F分布，即近似为1,它是最终

的计算结果，通过将它与肯定置信水平下的F临界值Fcrit比较，可以推断均值相等

的假设是否成立，在本例中,1.67761小于其次步0.94668，所以不能拒绝四个总体均值

相等的假设。P-value列,是单尾概率值，表明假如四个总体均值相等的假设成立的化,

得到如上样本结果的概率是19.442%，即得到以上样本并不是小概率事务，同样也得

到不能拒绝四