Excel的统计功能及其应用技巧

1、Excel的统计功能及其应用技巧由“开始”菜单启动桌面快捷方式启动“Microsoft Office”快捷方式启动Excel程序的启动程序的启动工作簿是计算和储存数据的文件 3个工作表工作表可以对数据进行组织和分析表格 256列和65536行组成工作簿和工作表工作簿和工作表 引用就是标识工作表上的单元格或单元格区域。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。 单元格引用：B10，A10 区域引用：C5：C15，A5：B10 单元格和区域引用单元格和区域引用相对引用：A63B5；绝对引用：A63$B$5行绝对引用：A63B$5列绝对引用：A63$B5绝

2、对引用与相对引用绝对引用与相对引用1、单因素方差分析2、二因素交叉无重复的方差分析3、二因素交叉有重复的方差分析4、描述统计分析5、二样本方差的F检验6、编制频数分布表及绘制直方图7、排位与百分位比排位8、二样本均数差数的u检验9、配对资料的t检验10、等方差非配对资料的t检验11、异方差非配对资料的t检验12、多元回归和直线回归分析13、计算多个变量二二之间的相关系数及协方差14、进行随机和顺序抽样分析工具库提供的统计分析方法分析工具库提供的统计分析方法由“工具”菜单打开“加载宏”命令分析工具库的安装分析工具库的安装数据分析工具外观数据分析工具外观1、AVERAGE计算算术平均值2、BINO

3、MDIST计算二项式分布的概率值3、CHIDIST计算特定2分布的单尾概率值4、CHIINV计算一定单尾概率值时的2临界值5、CHITEST计算独立性检验的2值6、CONFIDENCE计算总体平均值的置信区间7、CORREL计算两组数据的相关系数8、COVAR计算两组数据的协方差9、FDIST计算特定F分布的单尾概率值10、FINV计算一定概率时的临界F值11、FTEST计算二个样本方差之比F值的概率12、GEOMEAN计算几何平均数13、HARMEAN计算调和平均数14、INTERCEPT计算直线回归的截距15、MAX计算最大值16、MEDIAN计算一组给定数字的中位数EXCEL电子表格提供

4、的粘帖函数（一）电子表格提供的粘帖函数（一）17、MIN计算最小值18、MODE计算一组数据的众数19、NORMDIST计算正态分布的累积函数20、NORMINV计算正态分布累积函数的逆函数21、NORMSDIST计算标准正态分布的累积函数22、NORMSINV计算标准正态分布累积函数的逆函数23、POISSON计算泊松分布的概率24、SLOPE计算给定数据的直线回归系数25、STDEV计算样本标准差26、STDEVP计算样本总体的标准差27、TDIST计算学生氏-t分布的概率值28、TINV计算特定概率时学生氏-t分布的临界t值29、TTEST计算t检验时的学生氏-t检验相关的概率30、VA

5、R计算样本的方差31、VARP计算样本总体的方差32、ZTEST计算u-检验的双尾概率值EXCEL电子表格提供的粘帖函数（二）电子表格提供的粘帖函数（二）#错误原因：公式产生的结果太长，单元格容纳不下。解决办法：适当增加列的宽度。#NIV/0！错误原因：除数为零。在公式中，除数使用了空白单元格或包含零值的单元格引用。解决办法：修改单元格引用，或在用作除数的单元格中输入不为零的值。#N/A错误原因：表示在函数和公式中没有可用的数值可以引用。解决办法：检查公式中引用的元格的数据，并输入正确数据。使用公式和函数时出现的常见错误信息（一）使用公式和函数时出现的常见错误信息（一）#NAME？错误原因：删

6、除了公式中使用名称或使用了不存在的名称以及拼写错误。解决办法：确认使用的名称确实存在。#NULL！错误原因：使用了不正确的区域运算或不正确的单元格引用。解决办法：如果要引用2个不相交的区域，请使用联合运算符（逗号）。例如，=SUM（B2：B5，D2：D5）表示对这2个不相交区域的引用。#NUM！错误原因：在需要数字参数的函数中使用了不能接受的参数或公式产生的数字太大或太小，EXCEL不能表示。如在计算临界t值或F值时，输入的概率值大于1或小于0。解决办法：检查数字是否超出限定区域，函数内的参数是否正确。使用公式和函数时出现的常见错误信息（二）使用公式和函数时出现的常见错误信息（二）#REF！错

7、误原因：删除了由其它公式引用的单元格或将移动单元格粘帖到由其它引用的单元格中。解决办法：检查引用单元格是否被删除，或者启动相应的应用程序。#VALUE！错误原因：需要数字或逻辑值时输入了文本。解决办法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。使用公式和函数时出现的常见错误信息（三）使用公式和函数时出现的常见错误信息（三）二项分布二项分布mnmmnqpCmP)()!( !mnmnCmn平均数、方差和标准差npnpq2npq二项分布的概率函数为二项分布概率的计算实例二项分布概率的计算实例 已知某种猪病的死亡率为30%，现有10头病猪，如不给予治疗，问死亡4头及死

8、亡4头和4头以下的概率为多少？死亡4头的概率计算公式为：200. 07 . 03 . 0)4(64410 CP死亡4头和4头以下概率的计算公式为：85. 0)()4(40 xxPxP用粘帖函数用粘帖函数BINOMDIST计算计算死亡4头的概率计算本计算在编缉栏中为BINOMDIST（4，10，0.30，FALSE） 死亡4头和4头以下概率的计算本计算在编缉栏中为BINOMDIST（4，10，0.30，TRUE） 普哇松分布普哇松分布 普哇松分布的概率函数 为常数，它等于平均数等于方差 ekkPk!)(=2 普哇松分布概率的计算实例普哇松分布概率的计算实例 已知某一地区，出现怪胎的事件服从普哇松

9、分布P（2），请计算该地区出现3次怪胎的概率，及出现3次和3次以下怪胎的概率为多少？ 出现3次怪胎概率的公式为：出现3次和3次以下怪胎的概率计算公式为：1804. 0! 32)3(23eP8571. 0! 32! 22! 12! 02)() 3(2322212030eeeekPkPk用用POISSON粘帖函数粘帖函数计算计算出现3次怪胎概率的计算本计算在编缉栏中显示POISSON(3，2，FALSE) 出现3次和3次以下怪胎的概率计算本计算编缉栏中显示POISSON(3，2，TRUE) 正态分布正态分布 用符号N（，2） 表示正态分布的概率函数为：222)(21)(xexf0,xNORMDIS

10、T粘帖函数：计算累积函数粘帖函数：计算累积函数 NORMINV粘帖函数：计算逆函数粘帖函数：计算逆函数 已知某品种成年猪体重的总体平均数=100kg，总体标准差=20kg。试计算成年猪体重在70kg以下的概率。计算公式如下： 70202)100(0668. 02201)70(22dxexPx用正态分布用正态分布粘帖函数的粘帖函数的计算计算用NORMDIST粘帖函数计算概率 本计算在编缉栏中的形式为NORMDIST(70，100，20，TRUE) 用NORMDIST粘帖函数计算函数值 本计算在编缉栏中的形式为NORMDIST(70，100，20，FALSE) 已知某品种成年猪体重的总体平均数=1

11、00kg，总体标准差=20kg。如果我们希望淘汰30%体重最轻的猪，问体重在多少kg以下的猪应给予淘汰。计算公式如下： 12230. 02201)(202)100(1xxdxexxP用NORMINV粘贴函数计算 本计算在编缉栏中的形式为NORMINV(0.30，100，20) 标准标准正态分布正态分布 用符号N（0，1） 表示标准正态分布的概率函数为：2221)(ueufu标准化的公式为：xu利用标准正态分布函数计算概率利用标准正态分布函数计算概率 NORMSDIST粘贴函数：计算累积函数 NORMSINV粘贴函数 ：计算逆函数 已知某品种成年猪体重的总体平均数=100kg，总体标准差=20k

12、g。试计算成年猪体重在70kg以下的概率。首先对70kg进行标准化，得u值等于-1.5，计算公式如下： 5 . 120668. 021)5 . 1(2dueuPu用NORMSDIST粘帖函数计算概率 本例在编缉栏中的形式是NORMSDIST(-1.5) 当给定一尾概率值时，求其临界值就得利用NORMSINV函数。假定现要计算当一尾概率为0.025时，计算公式如下：12025. 021)(21uudueuuP用NORMSINV粘贴函数计算 本例在编缉栏中的形式是NORMSINV(0.025) 学生氏t分布 t分布的概率密度函数为 212)1 ()2()()21()(dfdftdfdfdftft学

13、生氏t分布的计算实例 TDIST粘贴函数：单尾或双尾概率 TINV粘贴函数：临界t值 （二尾概率 ）2)()(XdttfXxPXdttfXxP)()(用TDIST粘贴函数计算二尾概率 本计算在编缉栏中输入TDIST（2，60，2） 例如现要计算自由度等于60，t值与平均数相差2以上的2尾概率 用TINV粘贴函数计算临界值 请计算自由度为10且二尾概率为0.05时的临界t值 本计算在编缉栏中为TINV（0.05，10）卡方分布卡方分布 卡方分布概率密度函数为： 22122222)2()()(edffdfdf02卡方分布的计算实例 CHIDIST粘贴函数：计算单尾概率 CHIINV粘贴函数：计算临

14、界值 CHIDIST函数积分公式为： 22222)()(XdxxfxXPCHIINV函数积分公式为： 22222)()(XdxxfxXP用CHIDIST粘贴函数计算概率 请计算自由度等于1及卡方值等于3.84时的一尾概率 本计算在编缉栏中为CHIDIST（3.84，1） 用CHIINV粘贴函数计算临界值 请计算自由度为10及一尾概率为0.01时的2临界值 本计算在编缉栏中为CHIINV（0.01，10） F分布 F分布的概率密度函数为：)2(21)12(21212212111)1 ()2()2()2()()(dfdfdfdfFdfdfFdfdfdfdfdfdfFfF0 F分布的计算实例 FDI

15、ST粘贴函数：计算单尾概率FINV粘贴函数：计算临界值 FDIST函数积分公式为： FdFFffFP)()(FINV函数积分公式为：FdFFffFP)()(用函数FDIST计算一尾概率 请计算第一自由度等于2、第二自由度等于4及F值等于18时的一尾概率 本计算在编缉栏中为FDIST（18，2，4） 用函数FINV计算临界F值 请计算df1=3，df2=10及一尾概率为0.05时的临界F值 本计算在编缉栏中为FINV（0.05，3，10） EXCEL电子表格的模拟运算 功能 模拟运算表是工作表中的一个单元格区域，它可以显示公式中某些值的变化对计算结果的影响。模拟运算表为同时求解某一运算中所有可能

16、的变化值的组合提供了捷径，并且还可以将所有不同的计算结果同时显示在工作表中，便于查找和比较。模拟运算表有两种类型：单变量模拟运算表 双变量模拟运算表 利用模拟运算计算临界t值表 单变量模拟计算结果利用模拟运算计算临界F值表 双变量模拟计算结果次数分布表 直方图对话框输入格式图示 接收频率累积 %接收频率累积 %15.942.00%71.93517.50%23.965.00%79.92831.50%31.999.50%63.92644.50%39.91014.50%87.92155.00%47.91321.00%55.91763.50%55.91729.50%95.91671.50%63.926

17、42.50%47.91378.00%71.93560.00%39.91083.00%79.92874.00%31.9987.50%87.92184.50%103.9891.50%95.91692.50%23.9694.50%103.9896.50%15.9496.50%111.9498.50%111.9498.50%119.93100.00%119.93100.00%其他0100.00%其他0100.00%次数分布表、柏拉图表及其累积频率 次数分布图（直方图） 051015202530354015.931.947.963.979.995.9 111.9 其他组限频率离散型数据 白黑花白黑花白黑

18、白白白花白白花白白花白黑白白黑白白黑白白黑花白白花白白白白黑白白黑花白黑花白黑花白白白白白白白黑花白黑花白黑花白黑花白黑花白黑花杂一代白毛黑斑猪自交后的毛色分离情况 将白、黑和花分别转换成1、2和3，然后利用直方图工具求出频数分布表，再将1、2和3恢复成白、黑和花就得到了这三种毛色猪的次数分布表，其结果为白、黑和花三种毛色猪的次数分别为39、17和16头 描述统计分析描述统计分析描述统计分析的基本统计量（一）算术平均数nxx中位数)2(CnfiLMmdmdd样本方差1)(22nxxs描述统计分析的基本统计量（二）样本标准差1)(2nxxs标准误nssx偏斜度nxxm33)(3231mmg nx

19、xm22)( 偏斜度=0，分布对称，偏斜度为正值，分布正偏，即众数位于算术平均数的左侧，偏斜度为负值，分布负偏，即众数位于算术平均数的右侧描述统计分析的基本统计量（三）峰值（峭度）32242mmg置信半径xdfstR)(nxxm44)(峰值=0，正态分布，峰值为正值，曲线过于陡峭，峰值为负值，曲线过于平坦描述统计分析对话框200头大白母猪仔猪一月窝重的描述统计分析结果头大白母猪仔猪一月窝重的描述统计分析结果样本均数与总体均数差异显著性检验样本均数与总体均数差异显著性检验t检验检验无效假设为Ho：o备择假设为HA：o计算公式如下：xosxt1 ndfxdfostx)(xdfostx)(根据以上公

20、式可导出以下结论： 由此可知，当样本平均数落在已知的总体均数置信概率为（1-）的置信区间以外时，就表明在显著水平时差异显著 已知约克夏母猪体重的总体平均数o=130kg，现随机抽测10头母猪的体重，数据如下：121、127、103、132、157、133、130、139、140、136（kg），试检验该样本是否来自总体均数为130kg的总体。显著性检验计算结果显著性检验计算结果均数差异的显著性检验均数差异的显著性检验等方差假设时的成组资料t检验异方差假设时的成组资料t检验成对资料的t检验等方差假设时的成组资料t检验检验的基本公式2121xxsxxt2121) 1() 1(dfdfnndf212

21、1211121nndfdfsssssxx 现随机抽测8头大白猪与8头哈白猪经产母猪产仔数资料如下（单位：头）：异方差假设时的成组资料异方差假设时的成组资料t检验检验检验的基本公式2121xxsxxt222121xxxxsss2212)1 (1dfkdfkdf222211xxxsssk双样本等方差假设双样本等方差假设t检验结果检验结果t-检验: 双样本等方差假设大白猪哈白猪平均10.511.375方差23.428571435.125观测值88合并方差14.27678571假设平均差0df14t Stat-0.463151237P(T=t) 单尾0.325186838t 单尾临界1.7613092

22、5P(T=t) 双尾0.650373676t 双尾临界2.144788596 现随机抽测8头大白猪与8头哈白猪经产母猪产仔数资料如下（单位：头）：双样本异方差假设双样本异方差假设t检验结果检验结果大白猪哈白猪平均10.511.375方差23.428575.125观测值88假设平均差0df10t Stat-0.46315P(T=t) 单尾0.326586t 单尾临界1.812462P(T=t) 双尾0.653172t 双尾临界2.228139成对资料的成对资料的t检验检验检验的基本公式dsdt iiixxd21) 1( nnsssdd1 ndf 现用国产与进口的膘厚测定仪，对14头肥猪进行了测定

23、（单位：mm） ，数据如下：试检验两种仪器测定的结果有无显著差异？成对资料的成对资料的t检验结果检验结果进 口国 产平 均36.7142857136.5方 差36.6813186845.96153846观 测 值1414泊 松 相 关 系 数0.292254695假 设 平 均 差0df13t Stat0.104700722P(T=t) 单 尾0.459105728t 单 尾 临 界1.770931704P(T=t) 双 尾0.918211456t 双 尾 临 界2.16036824方差分析方差分析单因素方差分析二因素交叉无重复方差分析二因素交叉有重复方差分析单因素方差分析具有k组每组n个观察

24、值的数据模式处理观察值（xij）总和（Ti）平均（ix）1x11，x12，x13x1nT11x2x21，x22，x23x2nT22xkxk1，xk2，xk3xknTkkxTxSST=SSA+SSedfT=dfA+dfe单因素方差分析实例 现有5个不同品种的若干头母猪的窝产仔数资料见下表，试检验不同品种母猪平均窝产仔数的差异显著性。五个不同品种母猪产仔数的方差分析表差 异 源SSdfMSFP-valueF crit组 间68.96417.245.4556960.0038892.866081组 内63.2203.16总 计132.1624基本公式基本公式iijTnTxSS22，1iTndfiiiA

25、nTnTSS22，1 kdfAATeSSSSSS，ATedfdfdf单因素方差分析表单因素方差分析表变 异 来源自 由 度平 方 和均 方F 值组 间dfASSAMSA=SSA/dfAF=MSA/MSe组 内dfeSSeMSe=SSe/dfe总 的dfTSST单因素方差分析示意图二因素交叉无重复方差分析二因素交叉无重复观察值的资料模式B1，B2，B3BkA 的总和（Ti）A 的平均（ix）A1x11，x12，x13x1kT11xA2x21，x22，x23x2kT22xAnxn1，xn2，xn3xnkTn nxB 的总和（Tj）TB 的平均（ix）1x，2x，3x， kxxSST=SSA+SSB

26、+SSedfT=dfA+dfB+dfe平方和及自由度的简易计算公式如下：平方和及自由度的简易计算公式如下：nkTxSSijT22，1 nkdfTnkTkTSSiA22，1 ndfAnkTnTSSjB22，1 kdfBBATeSSSSSSSS，) 1)(1(kndfe二因素交叉无重复观察值的方差分析表二因素交叉无重复观察值的方差分析表变 异 来 源自 由 度平 方 和均 方F 值A 因 素 间dfASSAMSA=SSA/dfAFA=MSA/MSeB 因 素 间dfBSSBMSB=SSB/dfBFB=MSB/MSe组 内dfeSSeMSe=SSe/dfe总 的dfTSST二因素交叉无重复观察值的方

27、差分析实例二因素交叉无重复观察值的方差分析实例无重复双因素方差分析无重复双因素方差分析品种与饲料对猪增重的方差分析表差 异 源SSdfMSFP-valueF crit行 （ A）332.253110.75189.8571 2.47E-06 4.757055列 （ B） 10.525.2590.015625 5.143249误 差3.560.583333总 计346.2511二因素交叉有重复方差分析二因素交叉有重复观察值的资料模式B1B2BkA 的总和A 的平均A1x111x11rx121x12rx1k1x1krT11xT11T12T1kA2x211x21rx221x22rx2k1x2krT22

28、xT21T22T2kAnxn11xn1rxn21xn2rxnk1xnkrTn nxTn1Tn2TnkB 的总和T1T2TkTB 的平均1x2xkxxSST=SSA+SSB+SSAB+SSe，其中SSAB=SSA+SSB+SSABdfT=dfA+dfB+dfAB+dfe，其中dfAB=dfA+dfB+dfAB平方和及自由度的简易计算公式如下：nkrTxSSijkT22，1 nkrdfTnkrTkrTSSiA22，1 ndfAnkrTnrTSSjB22，1 kdfBnkrTrTSSijAB22，1 nkdfABBAABBASSSSSSSS，) 1)(1(kndfBAABTeSSSSSS，) 1(

29、rnkdfe二因素交叉无重复观察值的方差分析表变 异 来 源自 由 度平 方 和均 方F 值A 因 素 间dfASSAM SA=SSA/dfAFA=M SA/M SeB 因 素 间dfBSSBM SB=SSB/dfBFB=M SB/M SeAB 互 作dfABSSABM SAB=SSAB/dfBFB=M SAB/M Se组 内dfeSSeM Se=SSe/dfe总 的dfTSST二因素交叉有重复观察值的方差分析实例 为了考察饲料中钙和磷的含量对幼猪生长发育的影响，将钙（A）和磷（B）在饲料中的含量各分为4个水平进行试验，每个水平组合3头猪，经2个月饲养试验，得幼猪增重结果列于下表。试分析钙和磷

30、及它们之间的交互作用对幼猪生长发育的影响。不同钙磷用量的试验猪增重结果（单位：kg）可重复双因素分析示意图不同钙磷用量试验猪增重结果的方差分析差异源SSdfMSFP-valueF crit样本（A）44.51063314.836883.220740.0355762.901118列（B）383.73563127.911927.766694.92E-092.901118交互406.6585945.184289.8084555.11E-072.188763内部147.4133324.606667总计982.318147回归分析基本公式xxyyxSSSPb，xbyayx)(yyxxSPxy；2)(xx

31、SSx直线回归方程的显著性检验22) ()(yyyySSiyxxyxyxxyyxRSSSPSSbSPbSS22RyeSSSSSS回归方程显著性检验的方差分析表变 异 来 源平 方 和自 由 度均 方F 值回 归S SRdfR= 1M SR= S SR/dfRF= M SR/M Se离 回 归S Sedfe= n-2M Se= S Se/dfe总 平 方 和S Sydfy= n-1回归系数和回归截距的显著性检验byxsbt ，2 ndfxebSSMSsasat ，2 ndfxeaSSxnMSs2195%回归系数的置信区间为：95%回归截距的置信区间为：bnyxstb2,05. 0ansta2,0

32、5. 0 现有10只绵羊的胸围（x，cm）和体重（y，kg）的数据下表，请进行回归分析。直线回归计算实例直线回归计算实例复相关分析（回归统计）表Multiple R0.847488R Square0.718236Adjusted R Square0.683016标准误差4.51192观测值10yRSSSSR pnRinR2211上式中当方程中有截距时i取值为1，否则为0回归方程的方差分析dfSSMSFSignificance F回归分析1415.1406415.140620.392590.001961残差8162.859420.35742总计9578回归系数和回归截距的显著性检验表Coeffi

33、cients 标准误差t StatP-value下限 95.0%上限 95.0%Intercept -115.3750 40.6323-2.8395 0.0218-209.0734 -21.6766胸围（x） 2.54690.56404.51580.00201.24633.8474xy5469. 23750.115多元回归分析KAB 多元回归分析的矩阵表示ppppppSSSPSPSPSPSPSSSPSPSPSPSSA321223221113121pbbbbB321pyyyySPSPSPSPK321KAB1iioxbyb多元回归方程的显著性检验22) ()(yyyySSiyiyiRSPbSSRy

34、eSSSSSS多元回归方程显著性检验的方差分析表变异来源平方和自由度均方F值回归SSRdfR=pMSR=SSR/dfRF=MSR/MSe离回归SSedfe=n-1-pMSe=SSe/dfe总平方和SSydfy=n-1偏回归系数和回归常数的显著性检验biiisbt ，pndf1iiebiCMSsbipnistb1,05. 0偏回归系数及常数项的95%置信区间为bosbt0，pndf1iiieboCxnMSs21bopnostb1,05. 0多元回归计算实例 现有某猪场50头肥猪5项胴体性状资料的数据列于下表，其中瘦肉量依变量y，眼肌面积为x1、腿肉量为x2、腰肉量为x3、椎骨数为x4，拟配合估计

35、瘦肉量的最优回归方程。多元回归分析对话框示意图4321382. 06811. 18648. 10787. 1xxxy复相关分析（回归统计）表4个自变量3个自变量（剔除眼肌面积）Multiple R0.9274 Multiple R0.9262R Square0.8602 R Square0.8579Adjusted R Square0.8477 Adjusted R Square0.8487标准误差0.4537 标准误差0.4523观测值50 观测值50多元回归方程的方差分析4个自变量dfSSMSF概率回归分析456.9814.2469.190.00残差459.260.21总计4966.243

36、个自变量（剔除眼肌面积）dfSSMSF概率回归分析356.8318.9492.590.00残差469.410.20总计4966.24偏回归系数和回归常数的显著性检验表Coefficients标准误差t StatP-value下限 95.0%上限 95.0%Intercept-0.32002.1837-0.14650.8842-4.71814.0782眼肌面积（cm2）0.01810.02130.84770.4011-0.02490.0611腿肉量（kg）1.80680.19139.44370.00001.42152.1922腰肉量（kg）1.64160.33004.97420.00000.97

37、692.3062椎骨数（个）0.10790.07581.42300.1616-0.04480.2605Coefficients标准误差t StatP-value下限 95.0%上限 95.0%Intercept-1.07871.9857-0.54320.5896-5.07572.9184腿肉量（kg）1.86480.178110.46840.00001.50622.2234腰肉量（kg）1.68110.32575.16160.00001.02552.3367椎骨数（个）0.13820.06662.07380.04370.00410.2723相关系数与协方差的计算 简单相关系数的基本公式 yxx

38、yxySSSSSPr)(yyxxSPxy2)(xxSSx2)(yySSy简单相关系数的计算实例简单相关系数的计算实例 用多元回归的例题简单相关系数计算结果简单相关系数计算结果 瘦肉量(kg)眼肌面积(cm2)腿肉量(kg)腰肉量(kg)椎骨数(个）瘦肉量(kg)1 眼肌面积(cm2)0.5123221 腿肉量(kg)0.8746340.4576281 腰肉量(kg)0.6969870.3117540.5285191 椎骨数(个）0.1806670.451630.087310.0107831协方差的计算协方差的计算 协方差的基本公式 nSPxyxycov1covnSPxyxy总体协方差 样本协方

39、差 粘帖函数（COVAR函数）：总体协方差协方差分析工具法：样本协方差分析 协方差的计算实例协方差的计算实例 用多元回归的例题协方差计算结果协方差计算结果 瘦肉量(kg)眼肌面积(cm2)腿肉量(kg)腰肉量(kg)椎骨数(个）瘦肉量(kg)1.35267984 眼肌面积(cm2)2.3229806515.1988131 腿肉量(kg)0.435685310.764129180.18344184 腰肉量(kg)0.188994040.283362610.052775920.05435657 椎骨数(个）0.204693881.715204080.036428570.002448980.9489

40、7959偏相关系数的计算偏相关系数的计算 jjiiijnijCCCr 12任意级偏相关系数的基本公式如下： 上式计算所得的偏相关系数是（n-2）级的，即n个变量中有（n-2）个保持不变，其中Cij是简单相关系数矩阵的逆矩阵中第i行第j列的元素，Cii和Cjj逆矩阵主对角线上第i和第j个元素。偏相关系数下标中圆点前的下标表示相关的2个变量，圆点后的下标表示保持不变的变量。 偏相关系数的计算实例偏相关系数的计算实例 多元回归分析的例题用粘帖函数MINVERSE计算出相关系数矩阵的逆矩阵 简单相关系数矩阵的逆矩阵简单相关系数矩阵的逆矩阵 瘦肉量(kg)眼肌面积(cm2)腿肉量(kg)腰肉量(kg)椎

41、骨数(个）瘦肉量(kg)7.0275 -0.4698 -4.6751 -2.2740 -0.6248 眼肌面积(cm2)-0.4698 1.6711 -0.2760 -0.0407 -0.6453 腿肉量(kg)-4.6751 -0.2760 4.7219 0.8430 0.5479 腰肉量(kg)-2.2740 -0.0407 0.8430 2.1485 0.3325 椎骨数(个）-0.6248 -0.6453 0.5479 0.3325 1.3529 1371. 06711. 10275. 74698. 034512r8116. 07219. 40275. 76751. 424513r协方

42、差矩阵的逆矩阵协方差矩阵的逆矩阵 瘦肉量(kg)眼肌面积(cm2)腿肉量(kg)腰肉量(kg)椎骨数(个）瘦肉量(kg)5.1953 -0.1036 -9.3853 -8.3863 -0.5514 眼肌面积(cm2)-0.1036 0.1100 -0.1653 -0.0448 -0.1699 腿肉量(kg)-9.3853 -0.1653 25.7408 8.4421 1.3132 腰肉量(kg)-8.3863 -0.0448 8.4421 39.5265 1.4638 椎骨数(个）-0.5514 -0.1699 1.3132 1.4638 1.4256 1371. 01100. 01953.

43、51036. 034512r8116. 07408.251953. 53853. 924513r单因素一元协方差分析单因素一元协方差分析 各组平方和的计算公式为： iijTnTxSS221iTndfiiiAnTnTSS221 kdfAATeSSSSSSATedfdfdfiiijenTxSS22乘积和计算的基本公式如下： ，或iyxijijTnTTyxSP1iTndfiyxiyixiAnTTnTTSP1 kdfAATeSPSPSPATedfdfdfiyixiijijenTTyxSP协方差分析简表协方差分析简表 校正增重的方差分析变因dfSSxSSySPxybyxdfSSMSF总变异3169138

44、1889428156 307427.65 组间3675523183880 组内286238316576242750.389277129.13264.04 校正处理间3298.5299.510.377xxyyxSSSPb1TTdfdf1eedfdfeTAdfdfdfAAdfdf回归系数的计算公式校正自由度的计算公式校正自由度的计算公式总校正自由度组内校正自由度组间校正自由度或校正平方和的计算公式为校正平方和的计算公式为 总校正平方和TxTxyTyTSSSPSSSS,2,exexyeyeSSSPSSSS,2,组内校正平方和eTASSSSSS组间校正平方和校正均方的计算公式校正均方的计算公式AAAd

45、fSSMS组间校正均方eeedfSSMS 组内校正均方eAMSMSF F检验公式为协方差分析简表的构建协方差分析简表的构建 单元格输入形式G3=B3-1G5=B5-1G6=G3-G5H3=D3-（E3E3）C3H5=D5-（E5E5）C5H6=H3-H5I5=H5G5I6=H6G6J6=I6I5F5=E5C5 写入公式后的协方差分析简表写入公式后的协方差分析简表 协方差分析实例协方差分析实例 在研究3种不同饲料对猪的增重效果时，用24头始重不一样的幼猪分为3组，每组8头进行试验，各组的增重结果见下表。 分组变量原始数据Ix1513111212161417 y8583657680918490II

46、x1716181821221918 y9790100951031069994IIIx2224202325273032 y89918395100102105110原始数据经计算后所得的组平均数和总和原始数据经计算后所得的组平均数和总和 分组变量组平均数组总和Ix13.75110 y81.75654IIx18.625149 y98784IIIx25.375203 y96.875775原始数据经计算后所得的平方和和乘积和原始数据经计算后所得的平方和和乘积和 变因自由度x 平方和y 平方和乘积和总的23720.52555.9581080.75组间2545.251317.583659.875组内2117

47、5.251238.375420.875各项乘积和的计算各项乘积和的计算 根据乘积公式进行计算；计算相关系数，然后除以二个变量平方和的几何平均数；计算样本协方差，然后用自由度与样本协方差相乘；计算总体协方差，然后用样本含量与总体协方差相乘各项乘积和的计算，有以下几种方法：计算总体协方差，然后用样本含量与总体协方差相乘分组Array1Array2协方差样本含量乘积和IA1：A8B1：B813.81258110.5IIA9：A16B9：B168.125865IIIA17：A24B17：B2430.671888245.375 求和420.875校正增重的方差分析校正增重的方差分析 校正组间平均数的多重比较校正组间平均数的多重比较 用LSD法时公用均数差数标准误的计算公式为： ) 1(12,kSSSSnMSSexAxeyyji采用SSR法或q法检验时公用均数差数标准误的计算公式为： ) 1(1,kSSSSnMSSexAxeyyji70. 2) 13(25.17525.5451838.112jiyyS本例采用LSD法进行检验，计算公用均数