excel中的概率统计(非常好的资料)教学提纲

合集下载

如何使用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 交叉处的单元格．如果要引用单元格区域，请输入区域左上角单元格的引用、冒号（：）和区域右下角单元格的引用．下面是引用的示例．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（12.6,13.4,11.9,12.8,13.0）=12.74如果要计算单元格中Ａ1到B20元素的平均数，可用AVERAGE(A1:B20)．1.2.2标准差计算标准差可依据样本当作变量或总体当作变量来分别计算，根据样本计算的结果称作样本标准差，而依据总体计算的结果称作总体标准差．(1)样本标准差Excel计算样本标准差采用无偏估计式，STDEV函数格式如下：STDEV（参数1，参数2，…，参数30）范例：STDEV（3，5，6，4，6，7，5）＝1.35如果要计算单元格中Ａ1到B20元素的样本标准差，可用STDEV(A1:B20)．(2)总体标准差Excel 计算总体标准差采用有偏估计式STDEVP 函数，其格式如下：STDEVP （参数1，参数2，…，参数30）范例：STDEVP （3，5，6，4，6，7，5）＝1.251.2.3 方差方差为标准差的平方，在统计上亦分样本方差与总体方差．(1)样本方差S 2=1)(2--∑n x x iExcel 计算样本方差使用VAR 函数，格式如下：VAR （参数1，参数2，…，参数30）如果要计算单元格中Ａ1到B20元素的样本方差，可用 VAR(A1:B20)．范例：VAR （3，5，6，4，6，7，5）＝1.81(2)总体方差S 2=n x x i ∑-2)(Excel 计算总体方差使用VARP 函数，格式如下：VARP （参数1，参数2，…，参数30）范例：VAR （3，5，6，4，6，7，5）＝1.551.2.4 正态分布函数Excel 计算正态分布时，使用NORMDIST 函数，其格式如下：NORMDIST（变量，均值，标准差，累积）其中：变量（x）：为分布要计算的x值；均值（μ）：分布的均值；标准差（σ）：分布的标准差；累积：若为TRUE，则为分布函数；若为FALSE，则为概率密度函数．范例：已知X服从正态分布，μ＝600，σ＝100，求P{X≤500}．输入公式＝NORMDIST（500，600，100，TRUE）得到的结果为0.158655，即P{X≤500}=0.158655．1.2.5正态分布函数的反函数Excel计算正态分布函数的反函数使用NORMINV函数，格式如下：NORMINV（下侧概率，均值，标准差）范例：已知概率P＝0.841345，均值μ＝360，标准差σ＝40，求NORMINV函数的值．输入公式＝NORMINV（0.841345，360，40）得到结果为400，即P{X≤400}=0.841345．注意：(1) NORMDIST函数的反函数NORMINV用于分布函数，而非概率密度函数，请务必注意；(2) Excel 提供了计算标准正态分布函数NORMSDIST(x)，及标准正态分布的反函数NORMSINV(概率)．Φ=P{X<2}．输入公式范例：已知Ｘ～Ｎ(0,1), 计算(2)=NORMSDIST(2)Φ=0.97725．得到0.97725，即(2)范例：输入公式=NORMSINV(0.97725) ，得到数值２．若求临界值uα(n)，则使用公式＝NORMSINV(1-α)．1.2.6t分布Excel计算t分布的值（查表值）采用TDIST函数，格式如下：TDIST（变量，自由度，侧数）其中：变量（t）：为判断分布的数值；自由度（v）：以整数表明的自由度；侧数：指明分布为单侧或双侧：若为1，为单侧；若为2，为双侧．范例：设T服从t(n-1)分布，样本数为25，求P（T>1.711）．已知t＝1.711，n=25，采用单侧，则T分布的值：＝TDIST(1.711,24,1)得到0.05，即P（T>1.711）=0.05．若采用双侧，则T分布的值：＝TDIST(1.711,24,2)得到0.1，即()1.7110.1P T >=． 1.2.7 t 分布的反函数Excel 使用TINV 函数得到t 分布的反函数，格式如下：TINV （双侧概率，自由度）范例：已知随机变量服从t (10)分布，置信度为0.05，求t 205.0(10)．输入公式＝TINV(0.05,10)得到2.2281，即()2.22810.05P T >=．若求临界值t α(n )，则使用公式＝TINV(2*α, n )．范例：已知随机变量服从t (10)分布，置信度为0.05，求t 0.05 (10)．输入公式＝TINV(0.1,10)得到1.812462，即t 0.05 (10)= 1.812462．1.2.8 F 分布Excel 采用FDIST 函数计算F 分布的上侧概率1()F x -，格式如下：FDIST(变量，自由度1，自由度2)其中：变量(x )：判断函数的变量值；自由度1(1n )：代表第1个样本的自由度；自由度2（2n ）：代表第2个样本的自由度．范例：设X 服从自由度1n =5，2n =15的F 分布，求P (X >2.9)的值．输入公式=FDIST(2.9,5,15)得到值为0.05，相当于临界值α．1.2.9 F 分布的反函数Excel 使用FINV 函数得到F 分布的反函数，即临界值12(,)F n n α，格式为：FINV(上侧概率，自由度1，自由度2)范例：已知随机变量X 服从F (9,9)分布，临界值α=0.05，求其上侧0.05分位点F 0.05(9,9)．输入公式=FINV(0.05,9,9)得到值为3.178897，即F 0.05(9,9)= 3.178897．若求单侧百分位点F 0.025（9,9），F 0.975（9,9）．可使用公式=FINV(0.025,9,9)=FINV(0.975,9,9)得到两个临界值4.025992和0.248386．若求临界值F α(n 1,n 2)，则使用公式＝FINV(α, n 1,n 2)．1.2.10 卡方分布Excel 使用CHIDIST 函数得到卡方分布的上侧概率1()F x -，其格式为：CHIDIST(数值，自由度)其中：数值(x )：要判断分布的数值；自由度(v )：指明自由度的数字．范例：若X 服从自由度v =12的卡方分布，求P (X >5.226)的值．输入公式＝CHIDIST(5.226,12)得到0.95，即1(5.226)F -=0.95或(5.226)F =0.05．1.2.11 卡方分布的反函数Excel 使用CHIINV 函数得到卡方分布的反函数，即临界值2()n αχ．格式为：CHIINV （上侧概率值α，自由度n ）范例：下面的公式计算卡方分布的反函数：＝CHIINV(0.95,12)得到值为5.226，即20.95(12)χ=5.226．若求临界值2αχ(n)，则使用公式＝CHIINV(α, n)． 1.2.12 泊松分布计算泊松分布使用POISSON 函数，格式如下：POISSON(变量，参数，累计)其中：变量：表示事件发生的次数；参数：泊松分布的参数值；累计：若TRUE ，为泊松分布函数值；若FALSE ，则为泊松分布概率分布值．范例：设Ｘ服从参数为４的泊松分布，计算P {X =6}及P {X ≤6}．输入公式=POISSON(6,4,FALSE)=POISSON(6,4,TRUE)得到概率0.104196和0.889326．在下面的实验中，还将碰到一些其它函数，例如：计算样本容量的函数COUNT ，开平方函数SQRT ，和函数SUM ，等等．关于这些函数的具体用法，可以查看Excel 的关于函数的说明，不再赘述．2 区间估计实验计算置信区间的本质是输入两个公式，分别计算置信下限与置信上限．当熟悉了数据输入方法及常见统计函数后，变得十分简单．2.1 单个正态总体均值与方差的区间估计：2.1.1 2已知时的置信区间置信区间为22x u x u n n αα⎛⎫-+ ⎝．例1 随机从一批苗木中抽取16株，测得其高度（单位：m ）为：1.14 1.10 1.131.15 1.20 1.12 1.17 1.19 1.15 1.12 1.14 1.20 1.23 1.11 1.141.16．设苗高服从正态分布，求总体均值μ的0.95的置信区间．已知σ =0.01(米)．步骤：(1)在一个矩形区域内输入观测数据，例如在矩形区域B3:G5内输入样本数据．(2)计算置信下限和置信上限．可以在数据区域B3:G5以外的任意两个单元格内分别输入如下两个表达式：=average(b3:g5)-normsinv(1-0.5*α)*σ/sqrt(count(b3:g5))=average(b3:g5)+normsinv(1-0.5*α)*σ/sqrt(count(b3:g5))上述第一个表达式计算置信下限，第二个表达式计算置信上限．其中，显著性水平α和标准差σ是具体的数值而不是符号．本例中，=0.05, 0.01σ=，上述两个公式应实际输入为=average(b3:g5)-normsinv(0.975)*0.01/sqrt(count(b3:g5))=average(b3:g5)+normsinv(0.975)*0.01/sqrt(count(b3:g5))计算结果为（1.148225, 1.158025）． 2.1.2 2未知时的置信区间置信区间为 22((x t n x t n n n αα⎛⎫--+- ⎝．例2 同例1，但σ未知．输入公式为：=average(b3:g5)-tinv(0.05,count(b:3:g5)-1)*stdev(b3:g5)/sqrt(count(b3:g5)) =average(b3:g5)-tinv(0.05,count(b:3:g5)-1)*stdev(b3:g5)/sqrt(count(b3:g5)) 计算结果为（1.133695, 1.172555）．2.1.3未知时2的置信区间：置信区间为2222122(1)(1),(1)(1)n nn ns sααχχ-⎛⎫ ⎪--⎪--⎪⎝⎭．例３从一批火箭推力装置中随机抽取10个进行试验,它们的燃烧时间(单位：s)如下：50.7 54.9 54.3 44.8 42.2 69.8 53.4 66.1 48.1 34.5试求总体方差2σ的0.9的置信区间(设总体为正态)．操作步骤:(1)在单元格B3:C7分别输入样本数据；(2)在单元格C9中输入样本数或输入公式=COUNT(B3:C7)；(3)在单元格C10中输入置信水平0.1．(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)/chiinv(0.1/2, count(b3:c7)-1)=(count(b3:c7)-1)*var(b3:c7)/chiinv(1-0.1/2, count(b3:c7)-1)2.2 两正态总体均值差与方差比的区间估计2.2.1 当12 =22 =2但未知时1-2的置信区间置信区间为 ()1212211(2)w x y t n n S n n α⎛⎫-±+-+ ⎪ ⎪⎝⎭．例４在甲，乙两地随机抽取同一品种小麦籽粒的样本，其容量分别为5和7，分析其蛋白质含量为甲：12.6 13.4 11.9 12.8 13.0乙：13.1 13.4 12.8 13.5 13.3 12.7 12.4蛋白质含量符合正态等方差条件，试估计甲，乙两地小麦蛋白质含量差μ１-μ２所在的范围．（取α＝0.05）实验步骤：(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)将显著性水平0.05输入到单元格E11中．(6)分别在单元格B13和B14输入=B11-B12-TINV(0.025,10)*SQRT((4*C11+6*C12)/10)*SQRT(1/ 5+1/7)和=B11-B12+TINV(0.025,10)*SQRT((4*C11+6*C12)/10)*SQRT(1/ 5+1/7)计算出置信区间的下限和上限．2.2.21和未知时方差比σ２１/σ２２的置信区间置信区间为22 112221221212211,(1,1)(1,1)s ss F n n s F n nαα-⎛⎫⎪⎪----⎪⎝⎭．例５有两个化验员A、B，他们独立地对某种聚合物的含氯量用相同的方法各作了10次测定．其测定值的方差分别是SA=0.5419，SB=0.6065．设σ2A和σ2B分别是A、B所测量的数据总体（设为正态分布）的方差．求方差比σ2A/σ2B的0.95置信区间．操作步骤：(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）22.3, 21.2, 19.2, 16.6, 23.1, 23.9, 24.8, 26.4, 26.6, 24.8, 23.9, 23.2, 23.3, 21.4,19.8, 18.3, 20.0, 21.5, 18.7, 22.4, 26.6, 23.9, 24.8, 18.8, 27.1, 20.6, 25.0, 22.5,23.5, 23.9, 25.3, 23.5, 22.6, 21.5, 20.6, 25.8, 24.0, 23.5, 22.6, 21.8, 20.8, 19.5,20.9, 22.1, 22.7, 23.6, 24.5, 23.6, 21.0, 21.3, 22.4,18.7, 21.3, 15.4, 22.9, 17.8,21.7, 19.1, 20.3, 19.8试以0.95的可靠性，对于该林地上全部林木的平均高进行估计．2. 从一批灯泡中随机抽取１０个进行测试，测得它们的寿命（单位：100h）为：50.7，54.9，54.3，44.8，42.2，69.8，53.4，66.1，48.1，34.5．试求总体方差的0.9的置信区间（设总体为正态）．3. 已知某种玉米的产量服从正态分布，现有种植该玉米的两个实验区，各分为10个小区，各小区的面积相同，在这两个实验区中，除第一实验区施以磷肥外，其它条件相同，两实验区的玉米产量（kg）如下：第一实验区：62 57 65 60 63 58 57 60 60 58第二实验区：56 59 56 57 60 58 57 55 57 55试求出施以磷肥的玉米产量均值和未施以磷肥的玉米产量均值之差的范围（α＝0.05）3假设检验实验实验内容：单个总体均值的假设检验；两个总体均值差的假设检验；两个正态总体方差齐性的假设检验；拟合优度检验．实验目的与要求：(1)理解假设检验的统计思想，掌握假设检验的计算步骤；(2)掌握运用Excel进行假设检验的方法和操作步骤；(3)能够利用试验结果的信息，对所关心的事物作出合理的推断．3.1单个正态总体均值μ的检验3.1.12已知时μ的U检验例1 外地一良种作物，其1000m2产量(单位：kg)服从Ｎ(800, 502)，引入本地试种，收获时任取５块地，其1000m2产量分别是800，850，780，900，820（kg），假定引种后1000m2产量Ｘ也服从正态分布，试问：=800kg 有无显著变化．(1)若方差未变，本地平均产量μ与原产地的平均产量μ０(2)本地平均产量μ是否比原产地的平均产量μ=800kg高．０=800kg低．(3)本地平均产量μ是否比原产地的平均产量μ０操作步骤：(1)先建一个如下图所示的工作表：(2)计算样本均值(平均产量)，在单元格D5输入公式=AVERAGE(A3:E3)；(3)在单元格D6输入样本数５；(4)在单元格D8输入U检验值计算公式=(D5-800)/(50/SQRT(D6)；(5)在单元格D9输入U检验的临界值=NORMSINV(0.975)；(6)根据算出的数值作出推论．本例中，Ｕ的检验值1.341641小于临界值1.959961,故接受原假设，即平均产量与原产地无显著差异．(7)注：在例1中，问题(2)要计算U检验的右侧临界值：在单元格D10输入U检验的上侧临界值=NORMSINV(0.95)．问题(3)要计算U检验的下侧临界值，在单元格D11输入U检验下侧的临界值=NORMSINV(0.05)．3.1.22未知时的t检验例２某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车内进行速度测试，得到行驶速度如下：250 238 265 242 248 258 255 236 245 261254 256 246 242 247 256 258 259 262 263该引擎制造商宣称引擎的平均速度高于250 km/h，请问样本数据在显著性水平为0.025时，是否和他的声明抵触？操作步骤：(1)先建如图所示的工作表：(2)计算样本均值：在单元格D8输入公式=AVERAGE(A3:E6)；(3)计算标准差：在单元格D9输入公式＝STDEV(A3:E6)；(4)在单元格D10输入样本数20．(5)在单元格D11输入t检验值计算公式=(D8-250)/(D9/(SQRT(D10))，得到结果1.06087；(6)在单元格D12输入t检验上侧临界值计算公式＝TINV(0.05, D10-1).欲检验假设H0：μ＝250；H：μ>250．１已知t统计量的自由度为(n-1)=20-1=19，拒绝域为t>t=2.093．由上面计算得025.0到t检验统计量的值1.06087落在接收域内，故接收原假设H0．3.2两个正态总体参数的假设检验3.2.1当12 =22 =2但未知时μ-μ的检验12在此情况下，采用t检验．例试验及观测数据同11.2中的练习题3，试判别磷肥对玉米产量有无显著影响？欲检验假设H０：μ1=μ2；H：μ1>μ2．１操作步骤：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”；(3)选定“t-检验：双样本等方差假设”．(4)选择“确定”．显示一个“t-检验：双样本等方差假设”对话框；(5)在“变量１的区域”输入Ａ2：Ａ11．(6)在“变量２的区域”输入Ｂ2：Ｂ11．(7)在“输出区域”输入D1，表示输出结果放置于D1向右方的单元格中．(8)在显著水平“α”框，输入0.05．(9)在“假设平均差”窗口输入0．(10)选择“确定”，计算结果如D1:F14显示．得到t值为3.03，“t单尾临界”值为1.734063．由于3.03>1.73，所以拒绝原假设，接收备择假设，即认为使用磷肥对提高玉米产量有显著影响．3.2.2σ２１与σ２２已知时12μ-μ的U检验例3 某班20人进行了数学测验，第１组和第２组测验结果如下：第１组：91 88 76 98 94 92 90 87 100 69第２组：90 91 80 92 92 94 98 78 86 91已知两组的总体方差分别是57与53，取α =0.05，可否认为两组学生的成绩有差异？操作步骤：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”；(3)选定“z-检验：双样本平均差检验”；(4)选择“确定”,显示一个“z-检验：双样本平均差检验”对话框；(5)在“变量1的区域”输入A2:A11；(6)在“变量2的区域”输入B2:B11；(7)在“输出区域”输入D1；(8)在显著水平“α”框，输入0.05；(9)在“假设平均差”窗口输入0；(10)在“变量1的方差”窗口输入57；(11)在“变量2的方差”窗口输入53；(12)选择“确定”，得到结果如图所示．计算结果得到z=-0.21106（即u统计量的值），其绝对值小于“z双尾临界”值1.959961，故接收原假设，表示无充分证据表明两组学生数学测验成绩有差异．3.2.3两个正态总体的方差齐性的Ｆ检验例５羊毛在处理前与后分别抽样分析其含脂率如下：处理前：0.19 0.18 0.21 0.30 0.41 0.12 0.27处理后：0.15 0.13 0.07 0.24 0.19 0.06 0.08 0.12问处理前后含脂率的标准差是否有显著差异？欲检验假设H０：σ２１=σ２２；H１：σ２１≠σ２２．操作步骤如下：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”； (3)选定“F-检验双样本方差”．(4)选择“确定”，显示一个“F-检验：双样本方差”对话框； (5)在“变量１的区域”输入A2:A8． (6)在“变量２的区域”输入B2:B9． (7)在显著水平“α”框，输入0.025． (8)在“输出区域”框输入D1． (9)选择“确定”，得到结果如图所示．计算出F 值2.35049小于“F 单尾临界”值5.118579，且P(F<=f)=0.144119>0.025,故接收原假设，表示无理由怀疑两总体方差相等．4 拟合优度检验拟合优度检验使用统计量221()ki i i i n np np χ=-=∑， (11.1) i i n np k 其中为实测频数，为理论频数，为分组数。

EXCEL的统计应用课程教学大纲

《EXCEL的统计应用》课程教学大纲课程类别：专业基础课学时数：72适用对象：统计实务授课单位：电子商务系执笔者：编写日期：审核人：审核日期：一、课程性质《EXCEL的统计应用》课程是针对统计实务专业开设的一门必修专业基础课，基于统计实务专业毕业生就业的相关工作任务中EXCEL应用的基本要求实施课程开发。

本课程是高职统计实务专业的第二门计算机课程，前导课程为计算机基础，学生已经具备基本的WINDOWS操作、文字处理、排版等基本技能，会EXCEL的基本操作。

本课程的学习结合了统计工作中的案例，把EXCEL融入到案例中，充分体现出了ECCEL在统计工作中的重要性，为统计实务专业学生毕业后走上专业工作岗位熟练地运用EXCEL这个工具完成工作任务打下了良好的基础。

二、课程目标本课程主要培养高职统计实务专业学生EXCEL的高级应用能力，让学生学会在工作中通过合理使用EXCEL这种工具，达到高效率完成统计工作的目的。

通过本课程的学习，使学生能够掌握利用EXCEL完成市场调查问卷设计、调查表统计、数据整理、描述统计、参数估计、假设检验、方差分析、相关分析、回归分析等工作。

与企业对统计实务专业学生EXCEL应用的基本能力要求紧密结合，将从各工作岗位的工作任务提炼出一个个的案例，通过案例的学习，培养学生的EXCEL应用基本能力，实现本课程的教学目标，对培养学生的职业素养起到了非常重要的作用。

1、知识目标：知道在统计工作的哪些环节中EXCEL可以提高工作效率；掌握统计知识与计算机知识的结合占。

2、技能目标：能利用EXCEL完成统计工作岗位中的典型工作任务；掌握常用统计函数的使用方法。

三、教学内容与要求任务一： EXCEL 基本操作1、教学目的与要求：本任务以EXCEL的基础知识为重点，主要介绍以下内容：EXCEL概述、如何启动EXCEL、EXCEL屏幕介绍、EXCEL的信息表示结构、工作簿文件的操作、工作簿中工作表的使用、工作表格的编排、如何输入数据、整行、整列、整张工作表和区域的选中、单元格、行、列的插入与删除、数据的移动与复制、数据的删除与替换、在公式中使用单元格引用、利用鼠标输入单元格引用、相对地址引用、绝对地址引用、图表的绘制、EXCEL文件的打印、打印纸设置、打印预览、打印、打印到文件、EXCEL帮助的使用、认识[帮助]菜单和OFFICE助手、如何获得帮助、选择OFFICE助手选项。

ecxel教学大纲

ecxel教学大纲Excel教学大纲Excel是一款功能强大的电子表格软件，广泛应用于商业、教育、科研等领域。

掌握Excel的基本操作和高级功能，对于提高工作效率和数据分析能力至关重要。

本文将为大家提供一份Excel教学大纲，帮助初学者系统学习Excel的各项技能。

第一部分：基础操作1. Excel界面介绍- 工作簿、工作表、单元格的概念和区别- 菜单栏、工具栏、快捷键的使用方法2. 单元格的基本操作- 输入数据、文本、公式等- 单元格格式设置：字体、对齐、边框等- 填充和清除单元格内容3. 数据的基本处理- 插入、删除行列- 复制、移动数据- 查找、替换数据4. 公式和函数的使用- 基本算术运算符的应用- 常用函数的介绍和示例：SUM、AVERAGE、MAX、MIN等- 使用函数进行数据分析和统计第二部分：高级功能1. 数据筛选和排序- 自动筛选和高级筛选- 按条件排序和多列排序2. 数据透视表- 创建和修改数据透视表- 使用数据透视表进行数据分析和汇总3. 图表的创建和编辑- 创建不同类型的图表：柱状图、折线图、饼图等 - 图表的格式设置和数据系列编辑4. 条件格式和数据验证- 根据条件设置单元格格式- 数据有效性的设置和限制第三部分：高级技巧1. 数据导入和导出- 从其他文件导入数据- 将Excel数据导出为其他格式文件2. 宏的录制和使用- 宏的基本概念和录制方法- 使用宏进行自动化操作3. 数据连接和外部数据源- 连接其他Excel文件和数据库- 利用外部数据源进行数据分析4. 数据分析工具的应用- 使用数据分析工具包进行统计分析- 利用傅里叶分析、回归分析等进行数据处理通过以上的教学大纲，学习者可以系统地学习Excel的基础操作、高级功能和高级技巧。

同时，还可以根据自身需求和兴趣，进一步深入学习Excel的其他功能和应用场景。

掌握Excel的技能，不仅可以提高工作效率，还可以为个人的职业发展打下坚实的基础。

概率论与数理统计实验_用Excel软件解决数理统计问题

用Excel 软件解决数理统计问题一实验目的学习、掌握用Excel 中求置信区间，作假设检验，作方差分析和回归分析.二实验的准备在微软Office 的Excel 中有许多函数用于数据处理, 其中有些涉及数理统计, 使用非常方便.Excel 在原安装中可能没有“数据分析”菜单，建立“数据分析”的步骤是：由“工具”菜单中选择“加载宏”，在弹出的加载宏对话框中选定“分析工具库”和“分析数据库－VBA 函数”，确定后“工具”菜单中增加了“数据分析”子菜单. 其中有“描述统计”，“协方差”，“相关系数”，“回归”，“方差分析”，“Z －检验”，“T －检验”，“F －检验”等工具.三实验内容1. 一般统计a) 平均数Excel 计算平均数用AVERAGE 函数，其格式如下：=AVERAGE(数据1，数据2，…，数据30)例如输入=A VERAGE(1,2,3,4,5)则得到平均数3. 若要得到位于工作表中E3至E12这组数据的平均数，则输入=A VERAGE(E3：E12)b) 样本标准差样本标准差的定义是1)(2--=∑n x xs iExcel 计算样本标准差的函数是STDEV ，其格式如下=STDEV(数据1，数据2，…，数据30)例如输入=STDEV(3,5,6,4,6,7,5)则得到这组数据的样本标准差1.35. 输入=STDEV(E3：E12)则得到位于E3至E12的这组数据的样本标准差.c) 样本方差样本方差的定义是1)(22--=∑n x x s iExcel 计算样本方差使用VAR 函数，格式为=VAR(数据1，数据2，…，数据30)例如输入=V AR(3,5,6,4,6,7,5)则得到这组数据的样本方差1.81. 输入=V AR(E3：E12)则得到位于E3至E12的这组数据的样本方差.2. 区间估计a) 估计均值已知方差, 估计均值时, 使用函数CONFIDENCE, 它的格式是:CONFIDENCE (显著性水平α, 总体标准差, 样本容量) 计算结果是nz σα2/. 再用样本均值加减这个值, 即得总体均值的置信区间.如果已知方差, 则先用函数SQRT 计算平方根, 得标准差, 再代入.如果已知一组样本值, 则还要用函数AVERAGE 计算样本均值, 然后才能计算置信区间. 例1 已知样本容量25=n , 总体的标准差100=σ, 样本均值950=X .取05.0=α. 求均值的置信区间.解在Excel 的一个单元 (例如A1) 内输入=CONFIDENCE(0.05, 100, 25) 用鼠标点击其它任意单元, 则公式所在单元显示39.19922. 这就是nz σα2/的值. 然后,在另一个单元格中输入=950-A1则显示910.8008. 这是置信区间的左端点. 同样方法可计算置信区间的右端点, 即得均值的置信区间.例2 对某种钢材的抗剪强度进行了10次测试，测得结果如下(单位: MPa)578, 572, 570, 568, 572, 570, 570, 596, 584, 572. 若已知抗剪强度服从正态分布),(2σμN ，且252=σ,求μ的95％的置信区间.解打开Excel 的一个新工作表. 在单元格B2，C2，…，K2内分别输入数据：578，572，570，…，572. 在单元格B3内输入=A VERAGE(B2: K2) 得到输出2.575=X . 在单元格B4内输入=STDEV(B2: K2)得到输出702.8=S . 在单元格B5内输入=CONFIDENCE(0.05, 5, 10)得到输出nz σα2/=5.394. 在单元格B6内输入=B3-B5得到置信下限为572.101，在单元格B7内输入=B3+B5得到置信上限为578.299. 因此置信区间为(572.101, 578.299)未知方差, 估计均值时, 没有这样的可以直接计算的函数, 需要一步一步计算. 例3 设总体服从正态分布. 已知样本容量16=n , 样本均值75.503=X ，样本标准差2022.6=S . 取05.0=α. 求均值μ的区间估计.解打开Excel 的一个新工作表. 先用函数TINV 求T 分布的分位点, 它的格式是＝TINV(显著性水平α,自由度1-n )在单元格B2内输入=TINV(0.05, 15)则这个单元将显示2.131451. 这就是)15()1(025.02/t n t =-α的值. 在单元格B3内输入 =B2*6.2022/SQRT(16) 显示3.304921. 这是nSn t )1(2/-α的值.在单元格B4内输入=503.75-B3得到置信下限为500.4451, 在单元格B3内输入=503.75+B3得到置信上限为507.0549.因此置信区间为(500.4451, 507.0549)例4 在例2中，设方差未知，求μ的95％的置信区间.解在例2中已经算得2.575=x , 702.8=S .而样本容量为10. 沿用例2中的工作表. 在单元格E4中输入=TINV(0.05, 9)得到)9()1(025.02/t n t =-α=2.26216, 在单元格E5中输入=E4*B4/SQRT(10)得到nS n t )1(2/-α=6.22539, 在单元格E6中输入=B3-E5得到置信下限为568.975，在单元格E7中输入=B3+E5得到置信上限为581.425.因此置信区间为(568.975, 581.425).注意: TINV(n ,α)给出的是T 分布的上2/α分位点.b) 估计方差估计方差时，要用到2χ分布或F 分布求2χ分布的上α分位点的函数为CHIINV, 它的格式为=CHIINV(2/α或者2/1α-,自由度1-n )例5 设总体服从正态分布. 已知样本容量9=n . 样本标准差007.0=S . 取05.0=α. 求总体方差的区间估计.解打开Excel 的一个新工作表，在单元格B2中输入=CHIINV(0.025,8) 显示17.53454()8(2025.0χ=). 在单元格C2中输入=CHIINV(0.975,8)显示 2.179725()8(2975.0χ=). 然后用公式⎪⎪⎭⎫ ⎝⎛-----)1()1(,)1()1(22/1222/2n S n n S n ααχχ计算置信区间. 在单元格B3中输入=8*0.007^2/B2显示0.00002236,在单元格C3中输入=8*0.007^2/C2显示0.0001798,因此总体方差的置信区间为(0.00002236,0.0001798).此外, 函数FINV 可以计算F 分布的上α分位点, 从而求方差比的置信区间.3. 假设检验a) 单个正态总体方差未知时均值的t 检验由于没有一个函数一次完成单个正态总体方差未知时均值的检验，需要分几步计算.所用的检验统计量为nS/X T 0μ-=可以用一般统计中介绍的方法计算检验统计量T 的观察值，再用区间估计中介绍的方法得到T 分布的上2/α分位点(双边检验时)，比较统计量T 的观察值t 和T 分布的上2/α分位点(拒绝域为：2/||αt t >),便可得到检验结果.例 6 设某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车内进行速度测试，得到行使速度如下：250 238 265 242 248 258 255 236 245 261 254 256 246 242 247 256 258 259 262 263该引擎制造商宣称引擎的平均速度每小时高于250km ，请问样本数据在显著性水平为0.025时是否和他的声明相抵触？解 (1) 打开Excel 的一个新工作表. 在单元格B3：F6输入样本数据，如下表(2)计算样本平均速度，在单元格D8中输入公式：＝A VERAGE(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)得到025.0t 的值为2.093.现在的检验问题是：250:0=μH ; 250:1>μH .拒绝域为025.0t t >,由上面的计算得到093.206087.1025.0=<=t t ,因此检验的结果是不拒绝原假设. 即无充分证据显示支持引擎制造商声明.b) 两个正态总体方差相等时均值差的t 检验为检验两个正态总体方差相等(但未知)时均值之差的假设：0210:d H =-μμ021:d H a ≠-μμ所用的检验统计量为2102111)(n n S d x x t w +--=(自由度为21n n +-2的t 分布)Excel 在计算时，使用“工具”，“数据分析”，“t-检验：双样本等方差假设”，就得到输出结果.例7 某化工试验中要考虑温度对产品断裂韧度的影响，在C 070，C 080条件下分别作了8次重复试验，侧得断裂韧度的数据如下：(单位：Mpa/m 2)C 070时 20.5 18.8 19.8 20.9 21.5 19.5 21.0 21.2 C 080时 17.7 2.03 20.0 18.8 19.0 20.1 20.2 19.1断裂韧度可以认为服从正态分布. 若已知两种温度的方差相等，1．问数学期望是否可以认为相等(05.0=α)？2．求两种温度时的数学期望差的置信区间(05.0=α).解 1.(1) 打开Excel 的一个新工作表. 在单元格A1中输入标记“70oC ”，在单元格B1中输入标记“80o C ”. 从A2到A9输入70o C 时的数据，从B2到B9输入80oC 时的数据.(2) 选定“工具”、“数据分析…”.(3) 选定“t-检验：双样本等方差假设”. (4) 选择“确定”，显示一个对话框. (5) 在“变量1区域”输入A1：A9. (6) 在“变量2区域”输入B1：B9. (7) 选中“输出区域”，并在框内输入D2，表示输出结果将放置于D2右下方的单元格中.(8) 打开“标志”复选框. 如果在“变量1区域”输入A2：A9，在“变量2区域”输入B2：B9，则不打开“标志”复选框. (9) 在“)(A α”内填临界值α为0.05.(10) 在“假设平均差”内填0. (11) 选择“确定”，得到结果如下表所示:在单元格E11中，显示统计量t 的值为2.160247，而在单元格E15中显示了临界值为2.14479，由于2.160247>2.14479，表示拒绝原假设: 认为两种温度下的数学期望不相等.2.利用上图所示的结果，也可以得到两个正态总体方差未知(但相等)时均值差的区间估计.由于检验统计量2111n n S YX T w +-=,现在已知Y X T ,,的值，因此T Y X n n S w -=+2111. 在单元格H5中输入=(E5-F5)/E11显示0.46291(=2111n n S w+)，再在单元格H6中输入 =H5*E15显示0.9928442(=)2(11212/21-+⋅+n n t n n S wα),再在单元格H8中输入 =E5-F5-H6显示0.0071558(置信下限)，再在单元格H9中输入=E5-F5＋H6显示1.9928442(置信上限)，因此得到均值差的置信区间为(0.0071558,1.9928442).注解在本例的Excel 输出表中，单元格E12给出了单边检验时的p 值：0.0242901，单元格E14给出了双边检验时的p 值：0.0485803. P-值的定义是：在原假设成立的条件下，检验统计量取其观察值及比观察值更极端的值(沿着对立假设方向)的概率. P-值也称作“观察”到的显著性水平. P-值越小，反对原假设的证据越强. 通常若P 低于5％，称此结果为统计显著；若P 低于1％，称此结果为高度显著.c) 两个正态总体方差是否相等的F 检验假设两总体服从正态分布，在均值未知时作两样本方差是否相等的检验：22210:σσ=H 22210:σσ≠H 检验统计量为222121)1,1(s s n n F =--(自由度为(1,121--n n )的F 分布)Excel 在计算时，使用“工具”，“数据分析”，“F-检验：双样本方差”，就得到输出结果.例8 由一台自动机床加工某型号零件，现在分别从同一月份上旬和下旬的产品中随意各取若干件，测定其直径，得如下数据(单位: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，表示输出结果将放置于D2右下方的单元格中.(7) 打开“标志”复选框. 如果在“变量1区域”输入A2：A9，在“变量2区域”输入B2：B8，则不打开“标志”复选框. (8) 在“)(A α”内填临界值α为0.05. (9) 选择“确定”，得到结果如下表:计算出的F 值为0.455618(=2221/S S ),注意单元格E11中给出的“F 单尾临界”值为0.258668,它是)6,7(95.0F 的查表值. 因为0.455618>0.258668，所以不拒绝原假设22210:σσ≥H . 因此检验结果认为下旬产品的加工精度未显著降低.4. 单因素方差分析用Excel 作单因素方差分析的步骤见下例.例9解 (1) 打开Excel 的一个新工作表. 在单元格A1输入标记“同学甲”，在单元格B1输入标记“同学乙”，在单元格C1输入标记“同学丙”. 从单元格A2至A5输入同学甲的100m 成绩，从单元格B2至B5输入同学乙的100m 成绩，从单元格C2至C5输入同学丙的100m 成绩. (2) 选取“工具”、“数据分析…”. (3) 选定“单因素方差分析”. (4) 选择“确定”，显示“单因素方差分析”对话框. (5) 在“输入区域”框输入A1:C5. (6) 在“分组方式”框选定“逐列”. (7) 选中“标志位于第一行(L )”.(8) 显著性水平“α”采用0.05.(9) 在输出选项中选中“输出区域)(O ”，在“输出区域”框中输入A7. (10) 选择“确定”，输出结果如下表所示.单元格A16：G21中显示的是方差分析表. 17行还有一些符号没有汉化. 符号“df ”表示“自由度”，“SS ”表示“偏差平方和”，“MS ”表示“方差”，“F ”为统计量F 的值，“P-value"为统计量F 的P-值. “F crit ”为统计量F 的临界值.从方差分析表知：05.0=α的临界值256.4)9,2(05.0=F . 因计算所得的统计量F 的值<=5263.2F 256.4)9,2(05.0=F ,故接收原假设. 不认为三个同学的100m 成绩有显著不同.5. 无重复双因素方差分析双因素无重复试验是不能区分交互作用的. 双因素无重复试验方差分析的试验数据（1）打开Excel 后在选定的工作表中设定和输入数据阵. （2）选取“工具”、“数据分析…”.（3）选定“方差分析：无重复双因素分析”选项. （4）填写“输入区域”框. （5）打开“标记”复选框(O). （6）填写显著性水平α(A)的值. （7）填写“输出区域”框. （8）选择“确定”，得到输出结果.解 (1)在单元格B1, C1, D1, E1分别输入地区1，地区2，地区3，地区4. 在单元格A2, A3, A4, A5分别输入季度一, 季度二, 季度三, 季度四.(2)在单元格B2, C2, D2, E2分别输入季度一的数据118,200,150,140; 在单元格B3, C3, D3, E3分别输入季度二的数据120,205,148,135; 在单元格B4, C4, D4, E4分别输入季度三的数据115,200,148,138; 在单元格B5, C5, D5, E5分别输入季度四的数据118,202,148,136.(3) 选取“工具”、“数据分析…”.(4) 选定“方差分析：无重复双因素分析”. (5) 在“输入区域”框填写A1:E5. (6) 选中“标记”复选框(有对勾). (7) 选定显著性水平α的值:0.05.(8) 在输出选项中选中输出区域(O):A8. (9) 选择“确定”，得到输出结果:由输出结果行间产生的离差平方和为8.6875(单元格B24), 自由度为3(单元格C24),所以均方和MSR=8.6875/3=2.895833(单元格D24), F 检验的统计量的值为0.675851, 这个检验统计量的P 值为0.588334(单元格E24), 它太大了. 而临界值为3.862539(单元格G24), 因此不否定原假设: 季度这个因素(行间)对结果无显著差异.又由列间产生离差平方和为15504.19(单元格B25), 自由度为3(单元格C25), 均方和MSC=15504.19/3= 5168.063(单元格D25), F 检验的统计量的值为1206.16(单元格E25), 检验统计量的P 值为4.881210-⨯(单元格F25), 而临界值为3.862539(单元格G25), 因此强烈地否定原假设: 地区这个因素(列间)对结果有显著差异.6. 一元线性回归在理解了一元线性回归的概念以后，可以用Excel 直接进行回归分析. 因此避免了复杂的计算过程. 例1较详细地说明了作线性回归的方法和步骤.(1)画出散点图. (2)求线性回归方程x b a y+=. (3)求的方差σ的无偏估计. (4)检验假设0:0=b H ,0:1≠b H . (5)若回归效果显著，求b 的置信水平为0.95的置信区间. 解 (1) 打开Excel 的一个新工作表. 在单元格A1输入标记“碳含量x ”，在单元格B1输入标记“电阻y ”. 从单元格A2至A8输入碳含量的值：0.10,0.30,...,0.95. 从单元格B2至B8输入电阻的值：15,18, (26)(2) 选取“工具”、“数据分析…”. (3) 选定“回归”. (4) 选择“确定”，显示“回归”对话框. (5) 在“Y 值输入区域”输入B1：B8. (6) 在“X 值输入区域”输入A1：A8. (7) 选中“标志L ”，不选中“常数为零”. (8) 选中“置信度F ”，在框内确定置信度为95%. (9) 选中“输出区域O ”，在框内填入A10. (10) 选中“线性拟合图”. (11) 选择“确定”，得到如下的输出表：在上面的输出表中，省略了“残差输出”和“概率输出”的内容.首先单元格A10:B17中的输出为回归分析的摘要表. 单元格A19:F23中的输出为线性回归的方差分析表. 符号“df ”表示“自由度”，“SS ”表示“偏差平方和”，“MS ”表示“方差”，“F ”为统计量F 的值，“Significance F"为统计量F 的P-值.其次，单元格A25:I27中显示的是回归系数的估计与检验. “Coefficient s ”表示“系数”，“Intercept ”表示“截距”,“t Stat ”表示统计量t 的观察值.现在来回答本题中提出的5个问题. (1)在线性回归的方差分析表的右边，可以找到一幅名为“含碳量x Line Fit Plot ”的图形，它就是散点图(图20.1).(2)从“Coefficient s ”的下面两格读出回归直线的截距为13.95839,斜率为12.55034.因此线性回归方程为x y 55034.1295839.13ˆ+=.(3)从方差分析表中的单元格D22读出ε的方差2σ的无偏估计为2ˆσ=0.043195. (4)因为单元格E27中显示出统计量t 的P-值很小(71014.1-⨯)，所以回归效果显著.(5)由单元格H27读出b 的置信下限为11.81796，单元格I27读出b 的置信上限为 13.28271，所以b 的置信水平为0.95的置信区间为(11.82,13.28).。

如何使用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 交叉处的单元格．如果要引用单元格区域，请输入区域左上角单元格的引用、冒号（：）和区域右下角单元格的引用．下面是引用的示例．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（12.6,13.4,11.9,12.8,13.0）=12.74如果要计算单元格中Ａ1到B20元素的平均数，可用AVERAGE(A1:B20)．1.2.2标准差计算标准差可依据样本当作变量或总体当作变量来分别计算，根据样本计算的结果称作样本标准差，而依据总体计算的结果称作总体标准差．(1)样本标准差Excel计算样本标准差采用无偏估计式，STDEV函数格式如下：STDEV（参数1，参数2，…，参数30）范例：STDEV（3，5，6，4，6，7，5）＝1.35如果要计算单元格中Ａ1到B20元素的样本标准差，可用STDEV(A1:B20)．(2)总体标准差Excel 计算总体标准差采用有偏估计式STDEVP 函数，其格式如下：STDEVP （参数1，参数2，…，参数30）范例：STDEVP （3，5，6，4，6，7，5）＝1.251.2.3 方差方差为标准差的平方，在统计上亦分样本方差与总体方差．(1)样本方差S 2=1)(2--∑n x x iExcel 计算样本方差使用VAR 函数，格式如下：VAR （参数1，参数2，…，参数30）如果要计算单元格中Ａ1到B20元素的样本方差，可用 VAR(A1:B20)．范例：VAR （3，5，6，4，6，7，5）＝1.81(2)总体方差S 2=n x x i ∑-2)(Excel 计算总体方差使用VARP 函数，格式如下：VARP （参数1，参数2，…，参数30）范例：VAR （3，5，6，4，6，7，5）＝1.551.2.4 正态分布函数Excel 计算正态分布时，使用NORMDIST 函数，其格式如下：NORMDIST（变量，均值，标准差，累积）其中：变量（x）：为分布要计算的x值；均值（μ）：分布的均值；标准差（σ）：分布的标准差；累积：若为TRUE，则为分布函数；若为FALSE，则为概率密度函数．范例：已知X服从正态分布，μ＝600，σ＝100，求P{X≤500}．输入公式＝NORMDIST（500，600，100，TRUE）得到的结果为0.158655，即P{X≤500}=0.158655．1.2.5正态分布函数的反函数Excel计算正态分布函数的反函数使用NORMINV函数，格式如下：NORMINV（下侧概率，均值，标准差）范例：已知概率P＝0.841345，均值μ＝360，标准差σ＝40，求NORMINV函数的值．输入公式＝NORMINV（0.841345，360，40）得到结果为400，即P{X≤400}=0.841345．注意：(1) NORMDIST函数的反函数NORMINV用于分布函数，而非概率密度函数，请务必注意；(2) Excel 提供了计算标准正态分布函数NORMSDIST(x)，及标准正态分布的反函数NORMSINV(概率)．Φ=P{X<2}．输入公式范例：已知Ｘ～Ｎ(0,1), 计算(2)=NORMSDIST(2)Φ=0.97725．得到0.97725，即(2)范例：输入公式=NORMSINV(0.97725) ，得到数值２．若求临界值uα(n)，则使用公式＝NORMSINV(1-α)．1.2.6t分布Excel计算t分布的值（查表值）采用TDIST函数，格式如下：TDIST（变量，自由度，侧数）其中：变量（t）：为判断分布的数值；自由度（v）：以整数表明的自由度；侧数：指明分布为单侧或双侧：若为1，为单侧；若为2，为双侧．范例：设T服从t(n-1)分布，样本数为25，求P（T>1.711）．已知t＝1.711，n=25，采用单侧，则T分布的值：＝TDIST(1.711,24,1)得到0.05，即P（T>1.711）=0.05．若采用双侧，则T分布的值：＝TDIST(1.711,24,2)得到0.1，即()1.7110.1P T >=． 1.2.7 t 分布的反函数Excel 使用TINV 函数得到t 分布的反函数，格式如下：TINV （双侧概率，自由度）范例：已知随机变量服从t (10)分布，置信度为0.05，求t 205.0(10)．输入公式＝TINV(0.05,10)得到2.2281，即()2.22810.05P T >=．若求临界值t α(n )，则使用公式＝TINV(2*α, n )．范例：已知随机变量服从t (10)分布，置信度为0.05，求t 0.05 (10)．输入公式＝TINV(0.1,10)得到1.812462，即t 0.05 (10)= 1.812462．1.2.8 F 分布Excel 采用FDIST 函数计算F 分布的上侧概率1()F x -，格式如下：FDIST(变量，自由度1，自由度2)其中：变量(x )：判断函数的变量值；自由度1(1n )：代表第1个样本的自由度；自由度2（2n ）：代表第2个样本的自由度．范例：设X 服从自由度1n =5，2n =15的F 分布，求P (X >2.9)的值．输入公式=FDIST(2.9,5,15)得到值为0.05，相当于临界值α．1.2.9 F 分布的反函数Excel 使用FINV 函数得到F 分布的反函数，即临界值12(,)F n n α，格式为：FINV(上侧概率，自由度1，自由度2)范例：已知随机变量X 服从F (9,9)分布，临界值α=0.05，求其上侧0.05分位点F 0.05(9,9)．输入公式=FINV(0.05,9,9)得到值为3.178897，即F 0.05(9,9)= 3.178897．若求单侧百分位点F 0.025（9,9），F 0.975（9,9）．可使用公式=FINV(0.025,9,9)=FINV(0.975,9,9)得到两个临界值4.025992和0.248386．若求临界值F α(n 1,n 2)，则使用公式＝FINV(α, n 1,n 2)．1.2.10 卡方分布Excel 使用CHIDIST 函数得到卡方分布的上侧概率1()F x -，其格式为：CHIDIST(数值，自由度)其中：数值(x )：要判断分布的数值；自由度(v )：指明自由度的数字．范例：若X 服从自由度v =12的卡方分布，求P (X >5.226)的值．输入公式＝CHIDIST(5.226,12)得到0.95，即1(5.226)F -=0.95或(5.226)F =0.05．1.2.11 卡方分布的反函数Excel 使用CHIINV 函数得到卡方分布的反函数，即临界值2()n αχ．格式为：CHIINV （上侧概率值α，自由度n ）范例：下面的公式计算卡方分布的反函数：＝CHIINV(0.95,12)得到值为5.226，即20.95(12)χ=5.226．若求临界值2αχ(n)，则使用公式＝CHIINV(α, n)．1.2.12 泊松分布计算泊松分布使用POISSON 函数，格式如下：POISSON(变量，参数，累计)其中：变量：表示事件发生的次数；参数：泊松分布的参数值；累计：若TRUE ，为泊松分布函数值；若FALSE ，则为泊松分布概率分布值．范例：设Ｘ服从参数为４的泊松分布，计算P {X =6}及P {X ≤6}．输入公式=POISSON(6,4,FALSE)=POISSON(6,4,TRUE)得到概率0.104196和0.889326．在下面的实验中，还将碰到一些其它函数，例如：计算样本容量的函数COUNT ，开平方函数SQRT ，和函数SUM ，等等．关于这些函数的具体用法，可以查看Excel 的关于函数的说明，不再赘述．2 区间估计实验计算置信区间的本质是输入两个公式，分别计算置信下限与置信上限．当熟悉了数据输入方法及常见统计函数后，变得十分简单．2.1 单个正态总体均值与方差的区间估计：2.1.1 2已知时的置信区间置信区间为22x u x u αα⎛⎫-+ ⎝．例1 随机从一批苗木中抽取16株，测得其高度（单位：m ）为：1.14 1.10 1.131.15 1.20 1.12 1.17 1.19 1.15 1.12 1.14 1.20 1.23 1.11 1.141.16．设苗高服从正态分布，求总体均值μ的0.95的置信区间．已知σ =0.01(米)．步骤：(1)在一个矩形区域内输入观测数据，例如在矩形区域B3:G5内输入样本数据．(2)计算置信下限和置信上限．可以在数据区域B3:G5以外的任意两个单元格内分别输入如下两个表达式：=average(b3:g5)-normsinv(1-0.5*α)*σ/sqrt(count(b3:g5))=average(b3:g5)+normsinv(1-0.5*α)*σ/sqrt(count(b3:g5))上述第一个表达式计算置信下限，第二个表达式计算置信上限．其中，显著性水平α和标准差σ是具体的数值而不是符号．本例中， =0.05, 0.01σ=，上述两个公式应实际输入为=average(b3:g5)-normsinv(0.975)*0.01/sqrt(count(b3:g5))=average(b3:g5)+normsinv(0.975)*0.01/sqrt(count(b3:g5))计算结果为（1.148225, 1.158025）．2.1.2 2未知时的置信区间置信区间为22((x t n x t n αα⎛⎫--+- ⎝．例2 同例1，但σ未知．输入公式为：=average(b3:g5)-tinv(0.05,count(b:3:g5)-1)*stdev(b3:g5)/sqrt(count(b3:g5)) =average(b3:g5)-tinv(0.05,count(b:3:g5)-1)*stdev(b3:g5)/sqrt(count(b3:g5)) 计算结果为（1.133695, 1.172555）．2.1.3 未知时 2的置信区间：置信区间为2222122(1)(1),(1)(1)n nn ns sααχχ-⎛⎫ ⎪--⎪--⎪⎝⎭．例３从一批火箭推力装置中随机抽取10个进行试验,它们的燃烧时间(单位：s)如下：50.7 54.9 54.3 44.8 42.2 69.8 53.4 66.1 48.1 34.5试求总体方差2σ的0.9的置信区间(设总体为正态)．操作步骤:(1)在单元格B3:C7分别输入样本数据；(2)在单元格C9中输入样本数或输入公式=COUNT(B3:C7)；(3)在单元格C10中输入置信水平0.1．(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)/chiinv(0.1/2, count(b3:c7)-1)=(count(b3:c7)-1)*var(b3:c7)/chiinv(1-0.1/2, count(b3:c7)-1) 2.2两正态总体均值差与方差比的区间估计2.2.1当 12 = 22 = 2但未知时 1- 2的置信区间置信区间为()122(2) x y t n n Sα⎛-±+-⎝．例４在甲，乙两地随机抽取同一品种小麦籽粒的样本，其容量分别为5和7，分析其蛋白质含量为甲：12.6 13.4 11.9 12.8 13.0乙：13.1 13.4 12.8 13.5 13.3 12.7 12.4蛋白质含量符合正态等方差条件，试估计甲，乙两地小麦蛋白质含量差μ１-μ２所在的范围．（取α＝0.05）实验步骤：(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)将显著性水平0.05输入到单元格E11中．(6)分别在单元格B13和B14输入=B11-B12-TINV(0.025,10)*SQRT((4*C11+6*C12)/10)*SQRT(1/ 5+1/7)和=B11-B12+TINV(0.025,10)*SQRT((4*C11+6*C12)/10)*SQRT(1/ 5+1/7)计算出置信区间的下限和上限．2.2.2 1和未知时方差比σ２１/σ２２的置信区间置信区间为 22112221221212211,(1,1)(1,1)s s s F n n s F n n αα-⎛⎫ ⎪ ⎪---- ⎪⎝⎭ ．例５有两个化验员A 、B ，他们独立地对某种聚合物的含氯量用相同的方法各作了10次测定．其测定值的方差分别是S A =0.5419，S B =0.6065．设σ2A 和σ2B 分别是A 、B 所测量的数据总体（设为正态分布）的方差．求方差比σ2A /σ2B 的 0.95置信区间．操作步骤：(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）22.3, 21.2, 19.2, 16.6, 23.1, 23.9, 24.8, 26.4, 26.6, 24.8, 23.9, 23.2, 23.3, 21.4,19.8, 18.3, 20.0, 21.5, 18.7, 22.4, 26.6, 23.9, 24.8, 18.8, 27.1, 20.6, 25.0, 22.5,23.5, 23.9, 25.3, 23.5, 22.6, 21.5, 20.6, 25.8, 24.0, 23.5, 22.6, 21.8, 20.8, 19.5,20.9, 22.1, 22.7, 23.6, 24.5, 23.6, 21.0, 21.3, 22.4,18.7, 21.3, 15.4, 22.9, 17.8,21.7, 19.1, 20.3, 19.8试以0.95的可靠性，对于该林地上全部林木的平均高进行估计．2. 从一批灯泡中随机抽取１０个进行测试，测得它们的寿命（单位：100h）为：50.7，54.9，54.3，44.8，42.2，69.8，53.4，66.1，48.1，34.5．试求总体方差的0.9的置信区间（设总体为正态）．3. 已知某种玉米的产量服从正态分布，现有种植该玉米的两个实验区，各分为10个小区，各小区的面积相同，在这两个实验区中，除第一实验区施以磷肥外，其它条件相同，两实验区的玉米产量（kg）如下：第一实验区：62 57 65 60 63 58 57 60 60 58第二实验区：56 59 56 57 60 58 57 55 57 55试求出施以磷肥的玉米产量均值和未施以磷肥的玉米产量均值之差的范围（α＝0.05）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产量Ｘ也服从正态分布，试问：=800kg 有无显著变化．(1)若方差未变，本地平均产量μ与原产地的平均产量μ０(2)本地平均产量μ是否比原产地的平均产量μ=800kg高．０(3)本地平均产量μ是否比原产地的平均产量μ=800kg低．０操作步骤：(1)先建一个如下图所示的工作表：(2)计算样本均值(平均产量)，在单元格D5输入公式=AVERAGE(A3:E3)；(3)在单元格D6输入样本数５；(4)在单元格D8输入U检验值计算公式=(D5-800)/(50/SQRT(D6)；(5)在单元格D9输入U检验的临界值=NORMSINV(0.975)；(6)根据算出的数值作出推论．本例中，Ｕ的检验值1.341641小于临界值1.959961,故接受原假设，即平均产量与原产地无显著差异．(7)注：在例1中，问题(2)要计算U检验的右侧临界值：在单元格D10输入U检验的上侧临界值=NORMSINV(0.95)．问题(3)要计算U检验的下侧临界值，在单元格D11输入U检验下侧的临界值=NORMSINV(0.05)．3.1.2 2未知时的t检验例２某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车内进行速度测试，得到行驶速度如下：250 238 265 242 248 258 255 236 245 261254 256 246 242 247 256 258 259 262 263该引擎制造商宣称引擎的平均速度高于250 km/h，请问样本数据在显著性水平为0.025时，是否和他的声明抵触？操作步骤：(1)先建如图所示的工作表：(2)计算样本均值：在单元格D8输入公式=AVERAGE(A3:E6)；(3)计算标准差：在单元格D9输入公式＝STDEV(A3:E6)；(4)在单元格D10输入样本数20．(5)在单元格D11输入t检验值计算公式=(D8-250)/(D9/(SQRT(D10))，得到结果1.06087；(6)在单元格D12输入t检验上侧临界值计算公式＝TINV(0.05, D10-1).欲检验假设H0：μ＝250；H：μ>250．１已知t统计量的自由度为(n-1)=20-1=19，拒绝域为t>t=2.093．由上面计算得025.0到t检验统计量的值1.06087落在接收域内，故接收原假设H0．3.2两个正态总体参数的假设检验3.2.1当 12 = 22 = 2但未知时μ-μ的检验12在此情况下，采用t检验．例试验及观测数据同11.2中的练习题3，试判别磷肥对玉米产量有无显著影响？欲检验假设H０：μ1=μ2；H：μ1>μ2．１操作步骤：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”；(3)选定“t-检验：双样本等方差假设”．(4)选择“确定”．显示一个“t-检验：双样本等方差假设”对话框；(5)在“变量１的区域”输入Ａ2：Ａ11．(6)在“变量２的区域”输入Ｂ2：Ｂ11．(7)在“输出区域”输入D1，表示输出结果放置于D1向右方的单元格中．(8)在显著水平“α”框，输入0.05．(9)在“假设平均差”窗口输入0．(10)选择“确定”，计算结果如D1:F14显示．得到t值为3.03，“t单尾临界”值为1.734063．由于3.03>1.73，所以拒绝原假设，接收备择假设，即认为使用磷肥对提高玉米产量有显著影响．3.2.2σ２１与σ２２已知时12μ-μ的U检验例3 某班20人进行了数学测验，第１组和第２组测验结果如下：第１组：91 88 76 98 94 92 90 87 100 69第２组：90 91 80 92 92 94 98 78 86 91已知两组的总体方差分别是57与53，取α =0.05，可否认为两组学生的成绩有差异？操作步骤：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”；(3)选定“z-检验：双样本平均差检验”；(4)选择“确定”,显示一个“z-检验：双样本平均差检验”对话框；(5)在“变量1的区域”输入A2:A11；(6)在“变量2的区域”输入B2:B11；(7)在“输出区域”输入D1；(8)在显著水平“α”框，输入0.05；(9)在“假设平均差”窗口输入0；(10)在“变量1的方差”窗口输入57；(11)在“变量2的方差”窗口输入53；(12)选择“确定”，得到结果如图所示．计算结果得到z=-0.21106（即u统计量的值），其绝对值小于“z双尾临界”值1.959961，故接收原假设，表示无充分证据表明两组学生数学测验成绩有差异．3.2.3两个正态总体的方差齐性的Ｆ检验例５羊毛在处理前与后分别抽样分析其含脂率如下：处理前：0.19 0.18 0.21 0.30 0.41 0.12 0.27处理后：0.15 0.13 0.07 0.24 0.19 0.06 0.08 0.12问处理前后含脂率的标准差是否有显著差异？欲检验假设H０：σ２１=σ２２；H１：σ２１≠σ２２．操作步骤如下：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”；(3)选定“F-检验双样本方差”．(4)选择“确定”，显示一个“F-检验：双样本方差”对话框；(5)在“变量１的区域”输入A2:A8．(6)在“变量２的区域”输入B2:B9．(7)在显著水平“α”框，输入0.025．(8)在“输出区域”框输入D1．(9)选择“确定”，得到结果如图所示．计算出F 值2.35049小于“F 单尾临界”值5.118579，且P(F<=f)=0.144119>0.025,故接收原假设，表示无理由怀疑两总体方差相等．4 拟合优度检验拟合优度检验使用统计量221()ki i i i n np np χ=-=∑， (11.1) i i n np k 其中为实测频数，为理论频数，为分组数。

统计学-以Excel为分析工具教学设计

统计学-以Excel为分析工具教学设计前言统计学作为管理学、经济学等学科中的重要分支，是每个学生都需要学习的课程之一。

随着信息技术的发展，Excel已经成为了统计学数据分析的重要工具，其强大的数据处理和分析能力受到了广泛的认可。

因此，以Excel为分析工具的统计学教学逐渐成为了当前统计学教育中的主流，本文将对基于Excel的统计学教学进行设计，希望能够为相关教师提供参考。

教学目标1.理解统计学的基本概念和方法；2.掌握Excel数据处理和分析的基本方法；3.能够熟练地运用Excel进行数据分析和统计学实证研究。

教学内容第一部分统计学基本概念1.1 统计学的发展历程1.统计学的来源与发展2.统计学的定义和作用3.统计学的分类和应用领域1.2 统计学的基本概念1.总体与样本2.变量与数据类型3.频数表与频率表4.相关系数与回归分析第二部分 Excel基本操作2.1 Excel基本功能介绍1.Excel工作界面2.Excel基本功能及其操作2.2 Excel数据处理与分析1.数据录入及格式设置2.数据分离与合并3.数据排序与筛选4.Excel函数与公式5.统计学分析常用函数6.Excel制图及表格制作第三部分统计学的实证应用3.1 描述性统计分析1.频数分析2.中央位置测度3.离散程度测度3.2 统计推断与假设检验1.统计假设检验相关概念2.单样本t检验、配对样本t检验、双样本t检验3.3 相关分析与回归分析1.相关分析相关概念2.回归分析相关概念教学方法1.授课法：讲授整个课程内容；2.互动法：通过课堂练习和分组讨论等交互式实践，加深学生和教师之间的互动和交流；3.实验法：在课程中设置实验环节，引导学生掌握Excel数据分析和统计学实证的方法。

教学评价1.课堂练习：在每个章节结束时，分发一些练习题进行巩固；2.课程论文：要求学生在课程结束时提交一篇数据分析和统计学实证的论文，提高学生的学术研究能力。

概率统计教学大纲

概率统计教学大纲概率统计教学大纲概率统计是一门应用广泛的数学学科，它研究的是随机现象的规律性。

在现代社会中，概率统计的应用无处不在，涉及到金融、医学、工程、社会科学等众多领域。

因此，制定一份科学合理的概率统计教学大纲对于培养学生的数据分析能力和决策能力至关重要。

首先，概率统计教学大纲应该包括基本概率理论的学习。

学生需要掌握概率的基本概念，如样本空间、事件、概率等，并了解概率的性质与运算规则。

此外，学生还应该学习概率分布的概念，包括离散型和连续型概率分布，如二项分布、正态分布等。

通过学习基本概率理论，学生能够理解随机现象的规律性，并能够进行概率计算和推理。

其次，概率统计教学大纲应该包括统计推断的学习。

统计推断是概率统计的重要应用领域，它通过样本数据对总体参数进行推断。

学生需要学习抽样方法和抽样分布的基本概念，了解点估计和区间估计的原理与方法。

此外，学生还应该学习假设检验的基本原理和方法，包括单样本检验、双样本检验和方差分析等。

通过学习统计推断，学生能够对现实问题进行数据分析和决策，提高问题解决能力。

再次，概率统计教学大纲应该包括统计模型的学习。

统计模型是概率统计的理论基础，它描述了观测数据与总体之间的关系。

学生需要学习常用的统计模型，如线性回归模型、逻辑回归模型等，并了解参数估计和模型诊断的方法。

此外，学生还应该学习多元统计分析的基本原理和方法，包括主成分分析、聚类分析和判别分析等。

通过学习统计模型，学生能够对复杂问题进行建模和预测，提高数据分析和决策的准确性。

最后，概率统计教学大纲应该包括实际案例的学习。

概率统计是一门应用学科，学生需要通过实际案例来应用所学知识。

教学大纲应该设计一些实际案例，让学生通过数据收集、整理和分析来解决实际问题。

通过实际案例的学习，学生能够将概率统计理论与实践相结合，提高数据分析和决策的能力。

综上所述，一份科学合理的概率统计教学大纲应该包括基本概率理论、统计推断、统计模型和实际案例的学习。

利用excel分析数据的概率与概率分布

(2)构建第二个样本和第三个样本的频数分布单元格H2:H6中的公式与单元格G2:G6中的公式相同，只是将单元格“$A$1:$A$10”变成单元格 “$A$1:$A$100”。
2021/5/8
上一页下一页返回本节首页
20
①将单元格G2中的公式复制到H2中。
②选择单元格H2，把公式中的“$A$1:$A$10”改成“$A$1:$A$100”。
▪ 输出区域：在此输出计算结果。如果输出表将覆盖已有的数据，Excel 会自动确定输出区域的大小并显示信息。
▪ 新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始粘贴计算结果。如果需要给新工作表命名，请在右侧的编辑框中键入名称。
▪ 新工作簿：单击此选项，可创建新工作簿，并在新工作簿的新工作表中粘贴计算结果。
2021/5/8
上一页下一页返回本节首页
22
▪ 接着要在单元格D14:F18中创建公式，以计算每个样本中各个随机变量所出现的频率。它的计算方法是用每一个样本的随机变量发生次数除以其次数总和。
▪ 可以采用列相对引用，行绝对引用的方法来计
算，方法如下：
①选择单元格D14，输入“=G2/G$7”，公式中的G7采用列相对引用，行绝对引用格式。
2021/5/8
上一页下一页返回本节首页
21
4. 构建频率分布为了把这些频数分布同概率分布相比较，需要先把这些频数转换成频率，方法如下：
①把单元格C1:C10中的内容复制到C13:C22。 ②把单元格G1:I1的内容复制到D13:F13中，作为
频率表的横行标题。 ③在G13单元格中输入“概率”。 ④把单元格D2:D6的内容复制到G14:G18。

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 交叉处的单元格．如果要引用单元格区域，请输入区域左上角单元格的引用、冒号（：）和区域右下角单元格的引用．下面是引用的示例．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 （12.6,13.4,11.9,12.8,13.0）=12.74如果要计算单元格中Ａ1到B20元素的平均数，可用 AVERAGE(A1:B20)．1.2.2 标准差计算标准差可依据样本当作变量或总体当作变量来分别计算，根据样本计算的结果称作样本标准差，而依据总体计算的结果称作总体标准差．(1)样本标准差Excel 计算样本标准差采用无偏估计式，STDEV 函数格式如下：STDEV （参数1，参数2，…，参数30）范例：STDEV （3，5，6，4，6，7，5）＝1.35如果要计算单元格中Ａ1到B20元素的样本标准差，可用 STDEV(A1:B20)．(2)总体标准差Excel 计算总体标准差采用有偏估计式STDEVP 函数，其格式如下：STDEVP （参数1，参数2，…，参数30）范例：STDEVP （3，5，6，4，6，7，5）＝1.251.2.3 方差方差为标准差的平方，在统计上亦分样本方差与总体方差．(1)样本方差S 2=1)(2--∑n x x iExcel 计算样本方差使用VAR 函数，格式如下：VAR （参数1，参数2，…，参数30）如果要计算单元格中Ａ1到B20元素的样本方差，可用 VAR(A1:B20)．范例：VAR （3，5，6，4，6，7，5）＝1.81(2)总体方差S 2=n x x i ∑-2)(Excel 计算总体方差使用VARP 函数，格式如下：VARP （参数1，参数2，…，参数30）范例：VAR （3，5，6，4，6，7，5）＝1.551.2.4 正态分布函数Excel 计算正态分布时，使用NORMDIST 函数，其格式如下：NORMDIST （变量，均值，标准差，累积）其中：变量（x ）：为分布要计算的x 值；均值（μ）：分布的均值；标准差（σ）：分布的标准差；累积：若为TRUE ，则为分布函数；若为FALSE ，则为概率密度函数．范例：已知X 服从正态分布，μ＝600，σ＝100，求P {X ≤500}．输入公式＝NORMDIST （500，600，100，TRUE ）得到的结果为0.158655，即P {X ≤500}=0.158655．1.2.5 正态分布函数的反函数Excel 计算正态分布函数的反函数使用NORMINV 函数，格式如下：NORMINV （下侧概率，均值，标准差）范例：已知概率P ＝0.841345，均值μ＝360，标准差σ＝40，求NORMINV 函数的值．输入公式＝NORMINV （0.841345，360，40）得到结果为400，即P {X ≤400}=0.841345．注意：(1) NORMDIST 函数的反函数NORMINV 用于分布函数，而非概率密度函数，请务必注意；(2) Excel 提供了计算标准正态分布函数 NORMSDIST(x)，及标准正态分布的反函数 NORMSINV(概率)．范例：已知Ｘ～Ｎ(0,1), 计算(2)Φ=P {X <2}．输入公式=NORMSDIST(2)得到0.97725，即(2)Φ=0.97725．范例：输入公式=NORMSINV(0.97725) ，得到数值２．若求临界值u α(n )，则使用公式＝NORMSINV(1-α)．1.2.6 t 分布Excel 计算t 分布的值（查表值）采用TDIST 函数，格式如下：TDIST （变量，自由度，侧数）其中：变量（t ）：为判断分布的数值；自由度（v ）：以整数表明的自由度；侧数：指明分布为单侧或双侧：若为1，为单侧；若为2，为双侧．范例：设T 服从t (n-1)分布，样本数为25，求P （T >1.711）．已知t ＝1.711，n =25，采用单侧，则T 分布的值：＝TDIST(1.711,24,1)得到0.05，即P （T >1.711）=0.05．若采用双侧，则T 分布的值：＝TDIST(1.711,24,2)得到0.1，即()1.7110.1P T >=． 1.2.7 t 分布的反函数Excel 使用TINV 函数得到t 分布的反函数，格式如下：TINV （双侧概率，自由度）范例：已知随机变量服从t (10)分布，置信度为0.05，求t 205.0(10)．输入公式＝TINV(0.05,10)得到2.2281，即()2.22810.05P T >=．若求临界值t α(n )，则使用公式＝TINV(2*α, n )．范例：已知随机变量服从t (10)分布，置信度为0.05，求t 0.05 (10)．输入公式＝TINV(0.1,10)得到1.812462，即t 0.05 (10)= 1.812462．1.2.8 F 分布Excel 采用FDIST 函数计算F 分布的上侧概率1()F x -，格式如下：FDIST(变量，自由度1，自由度2)其中：变量(x )：判断函数的变量值；自由度1(1n )：代表第1个样本的自由度；自由度2（n ）：代表第2个样本的自由度．范例：设X 服从自由度1n =5，2n =15的F 分布，求P (X >2.9)的值．输入公式=FDIST(2.9,5,15)得到值为0.05，相当于临界值α．1.2.9 F 分布的反函数Excel 使用FINV 函数得到F 分布的反函数，即临界值12(,)F n n α，格式为：FINV(上侧概率，自由度1，自由度2)范例：已知随机变量X 服从F (9,9)分布，临界值α=0.05，求其上侧0.05分位点F 0.05(9,9)．输入公式=FINV(0.05,9,9)得到值为3.178897，即F 0.05(9,9)= 3.178897．若求单侧百分位点F 0.025（9,9），F 0.975（9,9）．可使用公式=FINV(0.025,9,9)=FINV(0.975,9,9)得到两个临界值4.025992和0.248386．若求临界值F α(n 1,n 2)，则使用公式＝FINV(α, n 1,n 2)．1.2.10 卡方分布Excel 使用CHIDIST 函数得到卡方分布的上侧概率1()F x -，其格式为：CHIDIST(数值，自由度)其中：数值(x )：要判断分布的数值；自由度(v )：指明自由度的数字．范例：若X 服从自由度v =12的卡方分布，求P (X >5.226)的值．输入公式＝CHIDIST(5.226,12)得到0.95，即1(5.226)F -=0.95或(5.226)F =0.05．1.2.11 卡方分布的反函数Excel 使用CHIINV 函数得到卡方分布的反函数，即临界值2()n αχ．格式为：CHIINV （上侧概率值α，自由度n ）范例：下面的公式计算卡方分布的反函数：＝CHIINV(0.95,12)得到值为5.226，即20.95(12)χ=5.226．若求临界值2χ(n)，则使用公式＝CHIINV(α, n)．1.2.12 泊松分布计算泊松分布使用POISSON 函数，格式如下：POISSON(变量，参数，累计)其中：变量：表示事件发生的次数；参数：泊松分布的参数值；累计：若TRUE ，为泊松分布函数值；若FALSE ，则为泊松分布概率分布值．范例：设Ｘ服从参数为４的泊松分布，计算P {X =6}及P {X ≤6}．输入公式=POISSON(6,4,FALSE)=POISSON(6,4,TRUE)得到概率0.104196和0.889326．在下面的实验中，还将碰到一些其它函数，例如：计算样本容量的函数COUNT ，开平方函数SQRT ，和函数SUM ，等等．关于这些函数的具体用法，可以查看Excel 的关于函数的说明，不再赘述．2 区间估计实验计算置信区间的本质是输入两个公式，分别计算置信下限与置信上限．当熟悉了数据输入方法及常见统计函数后，变得十分简单．2.1 单个正态总体均值与方差的区间估计：2.1.1 2置信区间为22x u x u αα⎛⎫-+ ⎝．例1 随机从一批苗木中抽取16株，测得其高度（单位：m ）为：1.14 1.10 1.131.15 1.20 1.12 1.17 1.19 1.15 1.12 1.14 1.20 1.23 1.11 1.141.16．设苗高服从正态分布，求总体均值μ的0.95的置信区间．已知σ =0.01(米)．步骤：(1)在一个矩形区域内输入观测数据，例如在矩形区域B3:G5内输入样本数据．(2)计算置信下限和置信上限．可以在数据区域B3:G5以外的任意两个单元格内分别输入如下两个表达式：=average(b3:g5)-normsinv(1-0.5*α)*σ/sqrt(count(b3:g5))=average(b3:g5)+normsinv(1-0.5*α)*σ/sqrt(count(b3:g5))上述第一个表达式计算置信下限，第二个表达式计算置信上限．其中，显著性水平α和标准差σ =0.05, 0.01σ=，上述两个公式应实际输入为=average(b3:g5)-normsinv(0.975)*0.01/sqrt(count(b3:g5))=average(b3:g5)+normsinv(0.975)*0.01/sqrt(count(b3:g5))计算结果为（1.148225, 1.158025）．2.1.22置信区间为22((x t n x t n αα⎛⎫--+- ⎝．例2 同例1，但σ未知．输入公式为：=average(b3:g5)-tinv(0.05,count(b:3:g5)-1)*stdev(b3:g5)/sqrt(count(b3:g5)) =average(b3:g5)-tinv(0.05,count(b:3:g5)-1)*stdev(b3:g5)/sqrt(count(b3:g5))计算结果为（1.133695, 1.172555）．2.1.3 2的置信区间：置信区间为 2222122(1)(1),(1)(1)n n n n s s ααχχ-⎛⎫ ⎪-- ⎪-- ⎪⎝⎭．例３从一批火箭推力装置中随机抽取10个进行试验,它们的燃烧时间(单位：s)如下：50.7 54.9 54.3 44.8 42.2 69.8 53.4 66.1 48.1 34.5试求总体方差2σ的0.9的置信区间(设总体为正态)．操作步骤:(1)在单元格B3:C7分别输入样本数据；(2)在单元格C9中输入样本数或输入公式=COUNT(B3:C7)；(3)在单元格C10中输入置信水平0.1．(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)/chiinv(0.1/2, count(b3:c7)-1)=(count(b3:c7)-1)*var(b3:c7)/chiinv(1-0.1/2, count(b3:c7)-1) 2.2两正态总体均值差与方差比的区间估计2.2.112 =22 =21-2的置信区间置信区间为()122(2) x y t n n Sα⎛-±+-⎝．例４在甲，乙两地随机抽取同一品种小麦籽粒的样本，其容量分别为5和7，分析其蛋白质含量为甲：12.6 13.4 11.9 12.8 13.0乙：13.1 13.4 12.8 13.5 13.3 12.7 12.4蛋白质含量符合正态等方差条件，试估计甲，乙两地小麦蛋白质含量差μ１-μ２所在的范围．（取α＝0.05）实验步骤：(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)将显著性水平0.05输入到单元格E11中．(6)分别在单元格B13和B14输入=B11-B12-TINV(0.025,10)*SQRT((4*C11+6*C12)/10)*SQRT(1/ 5+1/7)和=B11-B12+TINV(0.025,10)*SQRT((4*C11+6*C12)/10)*SQRT(1/ 5+1/7)计算出置信区间的下限和上限．2.2.2 1未知时方差比σ２１/σ２２的置信区间置信区间为 22112221221212211,(1,1)(1,1)s s s F n n s F n n αα-⎛⎫ ⎪ ⎪---- ⎪⎝⎭ ．例５有两个化验员A 、B ，他们独立地对某种聚合物的含氯量用相同的方法各作了10次测定．其测定值的方差分别是S A =0.5419，S B =0.6065．设σ2A 和σ2B 分别是A 、B所测量的数据总体（设为正态分布）的方差．求方差比σ2A /σ2B 的 0.95置信区间．操作步骤：(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）22.3, 21.2, 19.2, 16.6, 23.1, 23.9, 24.8, 26.4, 26.6, 24.8, 23.9, 23.2, 23.3,21.4, 19.8, 18.3, 20.0, 21.5, 18.7, 22.4, 26.6, 23.9, 24.8, 18.8, 27.1, 20.6,25.0, 22.5, 23.5, 23.9, 25.3, 23.5, 22.6, 21.5, 20.6, 25.8, 24.0, 23.5, 22.6,21.8, 20.8, 19.5, 20.9, 22.1, 22.7, 23.6, 24.5, 23.6, 21.0, 21.3, 22.4,18.7,21.3, 15.4, 22.9, 17.8, 21.7, 19.1, 20.3, 19.8试以0.95的可靠性，对于该林地上全部林木的平均高进行估计．2. 从一批灯泡中随机抽取１０个进行测试，测得它们的寿命（单位：100h）为：50.7，54.9，54.3，44.8，42.2，69.8，53.4，66.1，48.1，34.5．试求总体方差的0.9的置信区间（设总体为正态）．3. 已知某种玉米的产量服从正态分布，现有种植该玉米的两个实验区，各分为10个小区，各小区的面积相同，在这两个实验区中，除第一实验区施以磷肥外，其它条件相同，两实验区的玉米产量（kg）如下：第一实验区： 62 57 65 60 63 58 57 60 60 58第二实验区： 56 59 56 57 60 58 57 55 57 55试求出施以磷肥的玉米产量均值和未施以磷肥的玉米产量均值之差的范围（α＝0.05）3假设检验实验实验内容：单个总体均值的假设检验；两个总体均值差的假设检验；两个正态总体方差齐性的假设检验；拟合优度检验．实验目的与要求：(1)理解假设检验的统计思想，掌握假设检验的计算步骤；(2)掌握运用Excel进行假设检验的方法和操作步骤；(3)能够利用试验结果的信息，对所关心的事物作出合理的推断．3.1单个正态总体均值μ的检验3.1.12已知时μ的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(0.975)；(6)根据算出的数值作出推论．本例中，Ｕ的检验值1.341641小于临界值1.959961,故接受原假设，即平均产量与原产地无显著差异．(7)注：在例1中，问题(2)要计算U检验的右侧临界值：在单元格D10输入U检验的上侧临界值=NORMSINV(0.95)．问题(3)要计算U检验的下侧临界值，在单元格D11输入U检验下侧的临界值=NORMSINV(0.05)．3.1.22未知时的t检验例２某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车内进行速度测试，得到行驶速度如下：250 238 265 242 248 258 255 236 245 261254 256 246 242 247 256 258 259 262 263该引擎制造商宣称引擎的平均速度高于250 km/h，请问样本数据在显著性水平为0.025时，是否和他的声明抵触？操作步骤：(1)先建如图所示的工作表：(2)计算样本均值：在单元格D8输入公式=AVERAGE(A3:E6)；(3)计算标准差：在单元格D9输入公式＝STDEV(A3:E6)；(4)在单元格D10输入样本数20．(5)在单元格D11输入t检验值计算公式=(D8-250)/(D9/(SQRT(D10))，得到结果1.06087；(6)在单元格D12输入t检验上侧临界值计算公式＝TINV(0.05, D10-1).欲检验假设H0：μ＝250；H１：μ>250．已知t统计量的自由度为(n-1)=20-1=19，拒绝域为t>t025.0=2.093．由上面计算得到t检验统计量的值1.06087落在接收域内，故接收原假设H0．3.2两个正态总体参数的假设检验3.2.112 =22 =2但未知时12μ-μ的检验在此情况下，采用t检验．例试验及观测数据同11.2中的练习题3，试判别磷肥对玉米产量有无显著影响？欲检验假设H０：μ1=μ2；H１：μ1>μ2．操作步骤：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”；(3)选定“ t-检验：双样本等方差假设”．(4)选择“确定”．显示一个“t-检验：双样本等方差假设”对话框；(5)在“变量１的区域”输入Ａ2：Ａ11．(6)在“变量２的区域”输入Ｂ2：Ｂ11．(7)在“输出区域”输入D1，表示输出结果放置于D1向右方的单元格中．(8)在显著水平“α”框，输入0.05．(9)在“假设平均差”窗口输入0．(10)选择“确定”，计算结果如D1:F14显示．得到t值为3.03，“t单尾临界”值为1.734063．由于3.03>1.73，所以拒绝原假设，接收备择假设，即认为使用磷肥对提高玉米产量有显著影响．3.2.2σ２１与σ２２已知时12μ-μ的U检验例3 某班20人进行了数学测验，第１组和第２组测验结果如下：第１组： 91 88 76 98 94 92 90 87 100 69第２组： 90 91 80 92 92 94 98 78 86 91已知两组的总体方差分别是57与53，取α =0.05，可否认为两组学生的成绩有差异？操作步骤：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”；(3)选定“z-检验：双样本平均差检验”；(4)选择“确定”,显示一个“z-检验：双样本平均差检验”对话框；(5)在“变量1的区域”输入A2:A11；(6)在“变量2的区域”输入B2:B11；(7)在“输出区域”输入D1；(8)在显著水平“α”框，输入0.05；(9)在“假设平均差”窗口输入0；(10)在“变量1的方差”窗口输入57；(11)在“变量2的方差”窗口输入53；(12)选择“确定”，得到结果如图所示．计算结果得到z=-0.21106（即u统计量的值），其绝对值小于“z双尾临界”值1.959961，故接收原假设，表示无充分证据表明两组学生数学测验成绩有差异．3.2.3 两个正态总体的方差齐性的Ｆ检验例５羊毛在处理前与后分别抽样分析其含脂率如下：处理前：0.19 0.18 0.21 0.30 0.41 0.12 0.27处理后：0.15 0.13 0.07 0.24 0.19 0.06 0.08 0.12 问处理前后含脂率的标准差是否有显著差异？欲检验假设H ０：σ２１=σ２２； H １：σ２１≠σ２２．操作步骤如下：(1)建立如图所示工作表：(2)选取“工具”—“数据分析”； (3)选定“F-检验双样本方差”． (4)选择“确定”，显示一个“F-检验：双样本方差”对话框； (5)在“变量１的区域”输入A2:A8． (6)在“变量２的区域”输入B2:B9． (7)在显著水平“α”框，输入0.025． (8)在“输出区域”框输入D1． (9)选择“确定”，得到结果如图所示．计算出F 值2.35049小于“F 单尾临界”值5.118579，且P(F<=f)=0.144119>0.025,故接收原假设，表示无理由怀疑两总体方差相等．4 拟合优度检验拟合优度检验使用统计量221()ki i i in np np χ=-=∑， (11.1) i i n np k 其中为实测频数，为理论频数，为分组数。

Excel在概率统计中的应用

2. 用Excel 做统计
※ 用Excel中的公式求样本统计量的值
AVERAGE
算数平均值
CORREL
两组数值得相关系数
DEVSQ
偏差平方和
CONFIDENCE 总体平均值的置信区间
LINEST
线性回归方程的参数
STDEV 样本标准差
COVAR 协方差
VAR
样本方差
2. 用Excel 做统计
※ 用数据数据分析功能-描述统计
第 8 章随机变量的数字特征第二讲用Excel做统计与检验
主讲教师黄宗媛行简单快捷的数据处理和计算，并绘制各种统计图表。而在概率统计中，假设检验是十分重要的一部分内容。
➢ Excel 如何计算统计量的值？ ➢ Excel 如何进行假设检验？ ➢ 结果如何读取？
2. 用Excel 做统计
※ 用数据数据分析功能-抽样
2. 用Excel 做统计
※ 用数据数据分析功能-t检验
2. 用Excel 做统计
※ 用数据数据分析功能-回归分析
2. 用Excel 做统计
※ 用数据数据分析功能-回归分析
2. 用Excel 做统计
※ 用数据数据分析功能-回归分析
yˆ 4.96x - 21.06
2. 用Excel 做统计
※ 用数据数据分析功能-回归分析
2. 用Excel 做统计
※ 用数据数据分析功能-方差分析
灯丝配料方案的优选
12345678 甲 1600 1610 1650 1680 1700 1700 1780 乙 1500 1640 1400 1700 1750 丙 1640 1550 1600 1620 1640 1600 1740 1800 丁 1510 1520 1530 1570 1640 1680

Excel在概率统计中的应用1-上传版

第一讲 Excel在概率统计中的应用主讲教师黄宗媛副教授第8章随机变量的数字特征1. 引言在概率统计理论中，很多地方都要计算并绘制图形，尤其在统计学部分，要涉及数据的收集、存储、整理以及各种统计方法的实际计算，这些都可以借助统计软件来完成。

计算分布函数，绘制分布图求数字特征求统计量的值假设检验※ 常用的统计软件从电子计算机出现至今，统计软件已经有了长足的发展。

一方面，经典的统计方法都已经在软件中实现；另一方面，新的软件极大推动了新的统计方法的研究与开发。

SAS SPSS SPlus Gauss等等 ExcelMATLAB※ M ATLAB （matrix&laboratory）矩阵实验室美国MathWorks公司出品的商业数学软件用于算法开发、数据可视化、数据分析以及数值计算特别提供了统计工具箱（Statistics Toolbox），支持范围广泛的统计计算任务※ ExcelMicrosoft Office 系列软件中的一个简单，易操作※怎样使用软件安装软件操作（选择合适的模块）绘编写程序读取结果如何选取合适的统计方法？※基本情况Excel具有强有力的数据库管理功能，丰富的宏命令和函数，强有力的决策支持工具。

具有以下特点：操作简便样式功能分析能力图表能力数据库管理能力宏语言功能连接和合并功能对象连接和嵌入功能※建立工作表1. 启动Excel时，计算机自动打开工作簿和工作表。

这时，一般显示三张工作表，即Sheet1 、Sheet2和Sheet3，可以根据要求对工作表进行命名或者建立新的工作表。

2. 新建一个工作簿有两种方法：一是将光标移动到主菜单栏上的“文件”菜单，在下拉式菜单中单击“新建”，在出现的“新建工作簿”对话框中双击“空白工作簿”即可。

第二种方法是，单击标准工具栏的新建按钮。

※Excel中的数据容量256列×65536行Excel最大的数据容量※Excel中的数据类型（1）标签。

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 交叉处的单元格．如果要引用单元格区域，请输入区域左上角单元格的引用、冒号〔：〕和区域右下角单元格的引用．下面是引用的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 〔12.6,13.4,11.9,12.8,13.0〕=12.74如果要计算单元格中Ａ1到B20元素的平均数，可用 AVERAGE(A1:B20)．1.2.2 标准差计算标准差可依据样本当作变量或总体当作变量来分别计算，根据样本计算的结果称作样本标准差，而依据总体计算的结果称作总体标准差．(1)样本标准差Excel 计算样本标准差采用无偏估计式，STDEV 函数格式如下：STDEV 〔参数1，参数2，…，参数30〕例：STDEV 〔3，5，6，4，6，7，5〕＝1.35如果要计算单元格中Ａ1到B20元素的样本标准差，可用STDEV(A1:B20)．(2)总体标准差Excel 计算总体标准差采用有偏估计式STDEVP 函数，其格式如下：STDEVP 〔参数1，参数2，…，参数30〕例：STDEVP 〔3，5，6，4，6，7，5〕＝1.251.2.3 方差方差为标准差的平方，在统计上亦分样本方差与总体方差．(1)样本方差S 2=1)(2--∑n x x iExcel 计算样本方差使用VAR 函数，格式如下：VAR 〔参数1，参数2，…，参数30〕如果要计算单元格中Ａ1到B20元素的样本方差，可用VAR(A1:B20)．例：VAR 〔3，5，6，4，6，7，5〕＝1.81(2)总体方差S 2=n x x i ∑-2)(Excel 计算总体方差使用VARP 函数，格式如下：VARP 〔参数1，参数2，…，参数30〕例：VAR 〔3，5，6，4，6，7，5〕＝1.551.2.4 正态分布函数Excel 计算正态分布时，使用NORMDIST 函数，其格式如下：NORMDIST 〔变量，均值，标准差，累积〕其中：变量〔x 〕：为分布要计算的x 值；均值〔μ〕：分布的均值；标准差〔σ〕：分布的标准差；累积：假设为TRUE ，如此为分布函数；假设为FALSE ，如此为概率密度函数．例：X 服从正态分布，μ＝600，σ＝100，求P {X ≤500}．输入公式＝NORMDIST 〔500，600，100，TRUE 〕得到的结果为0.158655，即P {X ≤500}=0.158655．1.2.5 正态分布函数的反函数Excel 计算正态分布函数的反函数使用NORMINV 函数，格式如下：NORMINV 〔下侧概率，均值，标准差〕例：概率P ＝0.841345，均值μ＝360，标准差σ＝40，求NORMINV 函数的值．输入公式＝NORMINV 〔0.841345，360，40〕得到结果为400，即P {X ≤400}=0.841345．注意：(1)NORMDIST 函数的反函数NORMINV 用于分布函数，而非概率密度函数，请务必注意；(2) Excel 提供了计算标准正态分布函数 NORMSDIST(x)，与标准正态分布的反函数 NORMSINV(概率)．例：Ｘ～Ｎ(0,1), 计算(2)Φ=P {X <2}．输入公式=NORMSDIST(2)得到0.97725，即(2)Φ=0.97725．例：输入公式=NORMSINV(0.97725) ，得到数值２．假设求临界值u α(n )，如此使用公式＝NORMSINV(1-α)．1.2.6 t 分布Excel 计算t 分布的值〔查表值〕采用TDIST 函数，格式如下：TDIST 〔变量，自由度，侧数〕其中：变量〔t 〕：为判断分布的数值；自由度〔v 〕：以整数明确的自由度；侧数：指明分布为单侧或双侧：假设为1，为单侧；假设为2，为双侧．例：设T 服从t (n-1)分布，样本数为25，求P 〔T >1.711〕．t ＝1.711，n =25，采用单侧，如此T 分布的值：＝TDIST(1.711,24,1)得到0.05，即P 〔T >1.711〕=0.05．假设采用双侧，如此T 分布的值：＝TDIST(1.711,24,2)得到0.1，即()1.7110.1P T >=． 1.2.7 t 分布的反函数Excel 使用TINV 函数得到t 分布的反函数，格式如下：TINV 〔双侧概率，自由度〕例：随机变量服从t (10)分布，置信度为0.05，求t 205.0(10)．输入公式＝TINV(0.05,10)得到2.2281，即()2.22810.05P T >=．假设求临界值t α(n )，如此使用公式＝TINV(2*α, n )．例：随机变量服从t (10)分布，置信度为0.05，求t 0.05 (10)．输入公式＝TINV(0.1,10)得到1.812462，即t 0.05 (10)= 1.812462．1.2.8 F 分布Excel 采用FDIST 函数计算F 分布的上侧概率1()F x -，格式如下：FDIST(变量，自由度1，自由度2)其中：变量(x )：判断函数的变量值；自由度1(1n )：代表第1个样本的自由度；自由度2〔2n 〕：代表第2个样本的自由度．例：设X 服从自由度1n =5，2n =15的F 分布，求P (X >2.9)的值．输入公式=FDIST(2.9,5,15)得到值为0.05，相当于临界值α．1.2.9 F 分布的反函数Excel 使用FINV 函数得到F 分布的反函数，即临界值12(,)F n n α，格式为：FINV(上侧概率，自由度1，自由度2)例：随机变量X 服从F (9,9)分布，临界值α=0.05，求其上侧0.05分位点F 0.05(9,9)．输入公式=FINV(0.05,9,9)得到值为3.178897，即F 0.05(9,9)= 3.178897．假设求单侧百分位点F 0.025〔9,9〕，F 0.975〔9,9〕．可使用公式=FINV(0.025,9,9)=FINV(0.975,9,9)得到两个临界值4.025992和0.248386．假设求临界值F α(n 1,n 2)，如此使用公式＝FINV(α, n 1,n 2)．1.2.10 卡方分布Excel 使用CHIDIST 函数得到卡方分布的上侧概率1()F x -，其格式为：CHIDIST(数值，自由度)其中：数值(x )：要判断分布的数值；自由度(v )：指明自由度的数字．例：假设X 服从自由度v =12的卡方分布，求P (X >5.226)的值．输入公式＝CHIDIST(5.226,12)得到0.95，即1(5.226)F -=0.95或(5.226)F =0.05．1.2.11 卡方分布的反函数Excel 使用CHIINV 函数得到卡方分布的反函数，即临界值2()n αχ．格式为：CHIINV 〔上侧概率值α，自由度n 〕例：下面的公式计算卡方分布的反函数：＝CHIINV(0.95,12)得到值为5.226，即20.95(12)χ=5.226．假设求临界值2αχ(n)，如此使用公式＝CHIINV(α, n)．1.2.12 泊松分布计算泊松分布使用POISSON 函数，格式如下：POISSON(变量，参数，累计)其中：变量：表示事件发生的次数；参数：泊松分布的参数值；累计：假设TRUE ，为泊松分布函数值；假设FALSE ，如此为泊松分布概率分布值．例：设Ｘ服从参数为４的泊松分布，计算P {X =6}与P {X ≤6}．输入公式=POISSON(6,4,FALSE)=POISSON(6,4,TRUE)得到概率0.104196和0.889326．在下面的实验中，还将碰到一些其它函数，例如：计算样本容量的函数COUNT ，开平方函数SQRT ，和函数SUM ，等等．关于这些函数的具体用法，可以查看Excel 的关于函数的说明，不再赘述．2 区间估计实验计算置信区间的本质是输入两个公式，分别计算置信下限与置信上限．当熟悉了数据输入方法与常见统计函数后，变得十分简单．2.1 单个正态总体均值与方差的区间估计：2.1.1 2时的置信区间置信区间为22,x u x u n n αασσ⎛⎫-+ ⎪⎝⎭．例1 随机从一批苗木中抽取16株，测得其高度〔单位：m 〕为：1.14 1.10 1.13 1.151.20 1.12 1.17 1.19 1.15 1.12 1.14 1.20 1.23 1.11 1.14 1.16．设苗高服从正态分布，求总体均值μ的0.95的置信区间．σ=0.01(米)．步骤：(1)在一个矩形区域输入观测数据，例如在矩形区域B3:G5输入样本数据．(2)计算置信下限和置信上限．可以在数据区域B3:G5以外的任意两个单元格分别输入如下两个表达式：=average(b3:g5)-normsinv(1-0.5*α)*σ/sqrt(count(b3:g5))=average(b3:g5)+normsinv(1-0.5*α)*σ/sqrt(count(b3:g5))上述第一个表达式计算置信下限，第二个表达式计算置信上限．其中，显著性水平α和标准差σ是具体的数值而不是符号．本例中， =0.05,0.01σ=，上述两个公式应实际输入为=average(b3:g5)-normsinv(0.975)*0.01/sqrt(count(b3:g5))=average(b3:g5)+normsinv(0.975)*0.01/sqrt(count(b3:g5))计算结果为〔1.148225, 1.158025〕．2.1.2 2未知时的置信区间置信区间为 22(1),(1)S S x t n x t n n n αα⎛⎫--+- ⎪⎝⎭．例2 同例1，但σ未知．输入公式为：=average(b3:g5)-tinv(0.05,count(b:3:g5)-1)*stdev(b3:g5)/sqrt(count(b3:g5)) =average(b3:g5)-tinv(0.05,count(b:3:g5)-1)*stdev(b3:g5)/sqrt(count(b3:g5)) 计算结果为〔1.133695, 1.172555〕．2.1.3 未知时2的置信区间：置信区间为2222122(1)(1),(1)(1)n n n n s s ααχχ-⎛⎫ ⎪-- ⎪-- ⎪⎝⎭．例３从一批火箭推力装置中随机抽取10个进展试验,它们的燃烧时间(单位：s)如下：50.7 54.9 54.3 44.8 42.2 69.8 53.4 66.1 48.1 34.5试求总体方差2σ的0.9的置信区间(设总体为正态)．操作步骤:(1)在单元格B3:C7分别输入样本数据；(2)在单元格C9中输入样本数或输入公式=COUNT(B3:C7)；(3)在单元格C10中输入置信水平0.1．(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)/chiinv(0.1/2, count(b3:c7)-1)=(count(b3:c7)-1)*var(b3:c7)/chiinv(1-0.1/2, count(b3:c7)-1)2.2 两正态总体均值差与方差比的区间估计2.2.1 当12 =22 =2但未知时1-2的置信区间置信区间为 ()1212211(2)wx y t n n S n n α⎛⎫-±+-+ ⎪ ⎪⎝⎭．例４在甲，乙两地随机抽取同一品种小麦籽粒的样本，其容量分别为5和7，分析其蛋白质含量为甲：12.6 13.4 11.9 12.8 13.0乙：13.1 13.4 12.8 13.5 13.3 12.7 12.4蛋白质含量符合正态等方差条件，试估计甲，乙两地小麦蛋白质含量差μ１-μ２所在的围．〔取α＝0.05〕实验步骤：(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)将显著性水平0.05输入到单元格E11中．(6)分别在单元格B13和B14输入=B11-B12-TINV(0.025,10)*SQRT((4*C11+6*C12)/10)*SQRT(1/5+1/7)和=B11-B12+TINV(0.025,10)*SQRT((4*C11+6*C12)/10)*SQRT(1/5+1/7) 计算出置信区间的下限和上限．2.2.21和未知时方差比σ２１/σ２２的置信区间置信区间为22112221221212211,(1,1)(1,1)s ss F n n s F n nαα-⎛⎫ ⎪⎪----⎪⎝⎭．例５有两个化验员A、B，他们独立地对某种聚合物的含氯量用一样的方法各作了10次测定．其测定值的方差分别是SA =0.5419，SB=0.6065．设σ2A和σ2B分别是A、B所测量的数据总体〔设为正态分布〕的方差．求方差比σ2A /σ2B的 0.95置信区间．操作步骤：(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〕22.3, 21.2, 19.2, 16.6, 23.1, 23.9, 24.8, 26.4, 26.6, 24.8, 23.9, 23.2, 23.3,21.4, 19.8, 18.3, 20.0, 21.5, 18.7, 22.4, 26.6, 23.9, 24.8, 18.8, 27.1, 20.6,25.0, 22.5, 23.5, 23.9, 25.3, 23.5, 22.6, 21.5, 20.6, 25.8, 24.0, 23.5, 22.6,21.8, 20.8, 19.5, 20.9, 22.1, 22.7, 23.6, 24.5, 23.6, 21.0, 21.3, 22.4,18.7,21.3, 15.4, 22.9, 17.8, 21.7, 19.1, 20.3, 19.8试以0.95的可靠性，对于该林地上全部林木的平均高进展估计．2. 从一批灯泡中随机抽取１０个进展测试，测得它们的寿命〔单位：100h〕为：50.7，54.9，54.3，44.8，42.2，69.8，53.4，66.1，48.1，34.5．试求总体方差的0.9的置信区间〔设总体为正态〕．3. 某种玉米的产量服从正态分布，现有种植该玉米的两个实验区，各分为10个小区，各小区的面积一样，在这两个实验区中，除第一实验区施以磷肥外，其它条件一样，两实验区的玉米产量〔kg〕如下：第一实验区： 62 57 65 60 63 58 57 60 60 58第二实验区： 56 59 56 57 60 58 57 55 57 55试求出施以磷肥的玉米产量均值和未施以磷肥的玉米产量均值之差的围〔α＝0.05〕3假设检验实验实验容：单个总体均值的假设检验；两个总体均值差的假设检验；两个正态总体方差齐性的假设检验；拟合优度检验．实验目的与要求：(1)理解假设检验的统计思想，掌握假设检验的计算步骤；(2)掌握运用Excel进展假设检验的方法和操作步骤；(3)能够利用试验结果的信息，对所关心的事物作出合理的推断．3.1单个正态总体均值μ的检验3.1.12时μ的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(0.975)；(6)根据算出的数值作出推论．本例中，Ｕ的检验值1.341641小于临界值1.959961,故承受原假设，即平均产量与原产地无显著差异．(7)注：在例1中，问题(2)要计算U检验的右侧临界值：在单元格D10输入U检验的上侧临界值=NORMSINV(0.95)．问题(3)要计算U检验的下侧临界值，在单元格D11输入U检验下侧的临界值=NORMSINV(0.05)．3.1.22未知时的t检验例２某一引擎制造商新生产某一种引擎，将生产的引擎装入汽车进展速度测试，得到行驶速度如下：250238 265 242 248 258 255 236 245 261254256 246 242 247 256 258 259 262 263该引擎制造商宣称引擎的平均速度高于250 km/h，请问样本数据在显著性水平为0.025时，是否和他的声明抵触？操作步骤：(1)先建如下列图的工作表：(2)计算样本均值：在单元格D8输入公式=AVERAGE(A3:E6)；(3)计算标准差：在单元格D9输入公式＝STDEV(A3:E6)；(4)在单元格D10输入样本数20．(5)在单元格D11输入t检验值计算公式=(D8-250)/(D9/(SQRT(D10))，得到结果1.06087；(6)在单元格D12输入t检验上侧临界值计算公式＝TINV(0.05, D10-1).欲检验假设H0：μ＝250；H：μ>250．１t统计量的自由度为(n-1)=20-1=19，拒绝域为t>t025.0=2.093．由上面计算得到t检验统计量的值1.06087落在接收域，故接收原假设H0．3.2两个正态总体参数的假设检验3.2.1当12 =22 =2但未知时12μ-μ的检验在此情况下，采用t检验．例试验与观测数据同11.2中的练习题3，试判别磷肥对玉米产量有无显著影响？欲检验假设H０：μ1=μ2；H１：μ1>μ2．操作步骤：(1)建立如下列图工作表：甲方乙方t-检验: 双样本等方差假设62565759甲方乙方6556平均60576057方差7.111111112.666666676358观测值10105857合并方差 4.888888895760假设平均差06055df186057t Stat 3.033899385855P(T<=t) 单尾0.00356934t 单尾临界 1.73406359P(T<=t) 双尾0.00713869t 双尾临界 2.10092204(2)选取“工具〞—“数据分析〞；(3)选定“ t-检验：双样本等方差假设〞．(4)选择“确定〞．显示一个“t-检验：双样本等方差假设〞对话框；(5)在“变量１的区域〞输入Ａ2：Ａ11．(6)在“变量２的区域〞输入Ｂ2：Ｂ11．(7)在“输出区域〞输入D1，表示输出结果放置于D1向右方的单元格中．(8)在显著水平“α〞框，输入0.05．(9)在“假设平均差〞窗口输入0．(10)选择“确定〞，计算结果如D1:F14显示．得到t值为3.03，“t单尾临界〞值为1.734063．由于3.03>1.73，所以拒绝原假设，接收备择假设，即认为使用磷肥对提高玉米产量有显著影响．3.2.2σ２１与σ２２时12μ-μ的U检验例3 某班20人进展了数学测验，第１组和第２组测验结果如下：第１组： 91 88 76 98 94 92 90 87 100 69第２组： 90 91 80 92 92 94 98 78 86 91两组的总体方差分别是57与53，取α=0.05，可否认为两组学生的成绩有差异？操作步骤：(1)建立如下列图工作表：(2)选取“工具〞—“数据分析〞；(3)选定“z-检验：双样本平均差检验〞；(4)选择“确定〞,显示一个“z-检验：双样本平均差检验〞对话框；(5)在“变量1的区域〞输入A2:A11；(6)在“变量2的区域〞输入B2:B11；(7)在“输出区域〞输入D1；(8)在显著水平“α〞框，输入0.05；(9)在“假设平均差〞窗口输入0；(10)在“变量1的方差〞窗口输入57；(11)在“变量2的方差〞窗口输入53；(12)选择“确定〞，得到结果如下列图．计算结果得到z=-0.21106〔即u统计量的值〕，其绝对值小于“z双尾临界〞值1.959961，故接收原假设，表示无充分证据明确两组学生数学测验成绩有差异．3.2.3两个正态总体的方差齐性的Ｆ检验例５羊毛在处理前与后分别抽样分析其含脂率如下：处理前：0.19 0.18 0.21 0.30 0.41 0.12 0.27处理后：0.15 0.13 0.07 0.24 0.19 0.06 0.08 0.12问处理前后含脂率的标准差是否有显著差异？欲检验假设H ０：σ２１=σ２２； H １：σ２１≠σ２２．操作步骤如下：(1)建立如下列图工作表：(2)选取“工具〞—“数据分析〞； (3)选定“F-检验双样本方差〞．(4)选择“确定〞，显示一个“F-检验：双样本方差〞对话框； (5)在“变量１的区域〞输入A2:A8． (6)在“变量２的区域〞输入B2:B9． (7)在显著水平“α〞框，输入0.025． (8)在“输出区域〞框输入D1．(9)选择“确定〞，得到结果如下列图．计算出F 值2.35049小于“F 单尾临界〞值5.118579，且P(F<=f)=0.144119>0.025,故接收原假设，表示无理由怀疑两总体方差相等．4 拟合优度检验拟合优度检验使用统计量221()ki i i i n np np χ=-=∑， (11.1) i i n np k 其中为实测频数，为理论频数，为分组数。

Excel在概率统计中的应用

Excel 在概率统计中的应用第一节基本概念随机变量——在一定范围内以一定的概率分布随机取值的变量。

随机变量（random variable ）表示随机现象（在一定条件下，并不总是出现相同结果的现象称为随机现象）各种结果的变量（一切可能的样本点）。

例如某一时间内公共汽车站等车乘客人数，电话交换台在一定时间内收到的呼叫次数等等，都是随机变量的实例。

随机变量可分为离散型随机变量和连续型随机变量定义2 若随机变量ξ可能取的值至多可列个(有限个或可列无限个)，则称ξ为离散型(discrete)随机变量。

对离散型随机变量，设{j x }为其可能取值的集合，关键问题是写出概率()i P x ξ=（简记作)(i x p 或i p ），i =1，2，…。

称1212()()()n n x x x p x p x p x ⎛⎫ ⎪⎝⎭为ξ的分布列(distribution sequence)，有时也就称它为ξ的概率分布。

有些离散型随机变量的分布除了用上述分布列表示外，还可以用数学的解析表达式来表示其概率分布。

1、二项分布二项分布即重复n 次的伯努利试验。

在每次试验中只有两种可能的结果，而且是互相对立的，是独立的，与其它各次试验结果无关，结果事件发生的概率在整个系列试验中保持不变，则这一系列试验称为伯努力试验。

二项分布的概率函数为：2、泊松分布泊松分布适合于描述单位时间（或空间）内随机事件发生的次数。

如某一服务设施在一定时间内到达的人数，电话交换机接到呼叫的次数，汽车站台的候客人数，机器出现的故障数，自然灾害发生的次数，一块产品上的缺陷数，显微镜下单位分区内的细菌分布数等等。

泊松分布的概率函数为：泊松分布P (λ)中只有一个参数λ ，它既是泊松分布的均值，也是泊松分布的方差。

3、几何分布几何分布（Geometric distribution）是离散型概率分布。

其中一种定义为：在第n次伯努利试验中，试验k次才得到第一次成功的机率。