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

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

2、宏”命令分析工具库的安装数据分析工具外观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、VAR计

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

4、名称确实存在。#NULL！错误原因：使用了不正确的区域运算或不正确的单元格引用。解决办法：如果要引用2个不相交的区域，请使用联合运算符（逗号）。例如，=SUM（B2：B5，D2：D5）表示对这2个不相交区域的引用。#NUM！错误原因：在需要数字参数的函数中使用了不能接受的参数或公式产生的数字太大或太小，EXCEL不能表示。如在计算临界t值或F值时，输入的概率值大于1或小于0。解决办法：检查数字是否超出限定区域，函数内的参数是否正确。使用公式和函数时出现的常见错误信息（二）二项分布平均数、方差和标准差二项分布的概率函数为二项分布概率的计算实例 已知某种猪病的死亡率为30%，现有10头病猪，如不给

5、予治疗，问死亡4头及死亡4头和4头以下的概率为多少？死亡4头的概率计算公式为：死亡4头和4头以下概率的计算公式为：死亡4头和4头以下概率的计算本计算在编缉栏中为BINOMDIST（4，10，0.30，TRUE） 普哇松分布 普哇松分布的概率函数 为常数，它等于平均数等于方差 =2 普哇松分布概率的计算实例 已知某一地区，出现怪胎的事件服从普哇松分布P（2），请计算该地区出现3次怪胎的概率，及出现3次和3次以下怪胎的概率为多少？ 出现3次怪胎概率的公式为：出现3次和3次以下怪胎的概率计算公式为：用POISSON粘帖函数计算出现3次怪胎概率的计算本计算在编缉栏中显示POISSON(3，2，FALS

6、E) 出现3次和3次以下怪胎的概率计算本计算编缉栏中显示POISSON(3，2，TRUE) 正态分布 用符号N（，2） 表示正态分布的概率函数为：用NORMDIST粘帖函数计算概率 本计算在编缉栏中的形式为NORMDIST(70，100，20，TRUE) 用NORMDIST粘帖函数计算函数值 本计算在编缉栏中的形式为NORMDIST(70，100，20，FALSE) 用NORMINV粘贴函数计算 本计算在编缉栏中的形式为NORMINV(0.30，100，20) 标准正态分布 用符号N（0，1） 表示标准正态分布的概率函数为：标准化的公式为：利用标准正态分布函数计算概率 NORMSDIST粘贴函

7、数：计算累积函数 NORMSINV粘贴函数 ：计算逆函数 已知某品种成年猪体重的总体平均数=100kg，总体标准差=20kg。试计算成年猪体重在70kg以下的概率。首先对70kg进行标准化，得u值等于-1.5，计算公式如下： 用NORMSDIST粘帖函数计算概率 本例在编缉栏中的形式是NORMSDIST(-1.5) 当给定一尾概率值时，求其临界值就得利用NORMSINV函数。假定现要计算当一尾概率为0.025时，计算公式如下：用NORMSINV粘贴函数计算 本例在编缉栏中的形式是NORMSINV(0.025) 学生氏t分布 t分布的概率密度函数为 学生氏t分布的计算实例 TDIST粘贴函数：单

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

9、 本计算在编缉栏中为CHIDIST（3.84，1） 用CHIINV粘贴函数计算临界值 请计算自由度为10及一尾概率为0.01时的2临界值 本计算在编缉栏中为CHIINV（0.01，10） F分布 F分布的概率密度函数为：F0 F分布的计算实例 FDIST粘贴函数：计算单尾概率FINV粘贴函数：计算临界值 FDIST函数积分公式为： FINV函数积分公式为：用函数FDIST计算一尾概率 请计算第一自由度等于2、第二自由度等于4及F值等于18时的一尾概率 本计算在编缉栏中为FDIST（18，2，4） 用函数FINV计算临界F值 请计算df1=3，df2=10及一尾概率为0.05时的临界F值 本计算

10、在编缉栏中为FINV（0.05，3，10） EXCEL电子表格的模拟运算 功能 模拟运算表是工作表中的一个单元格区域，它可以显示公式中某些值的变化对计算结果的影响。模拟运算表为同时求解某一运算中所有可能的变化值的组合提供了捷径，并且还可以将所有不同的计算结果同时显示在工作表中，便于查找和比较。模拟运算表有两种类型：单变量模拟运算表 双变量模拟运算表 利用模拟运算计算临界t值表 单变量模拟计算结果利用模拟运算计算临界F值表 双变量模拟计算结果次数分布表 直方图对话框输入格式图示 接收频率累积 %接收频率累积 %15.942.00%71.93517.50%23.965.00%79.92831.50

11、%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.92642.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%其他01

12、00.00%次数分布表、柏拉图表及其累积频率 次数分布图（直方图） 离散型数据 白黑花白黑花白黑白白白花白白花白白花白黑白白黑白白黑白白黑花白白花白白白白黑白白黑花白黑花白黑花白白白白白白白黑花白黑花白黑花白黑花白黑花白黑花杂一代白毛黑斑猪自交后的毛色分离情况 将白、黑和花分别转换成1、2和3，然后利用直方图工具求出频数分布表，再将1、2和3恢复成白、黑和花就得到了这三种毛色猪的次数分布表，其结果为白、黑和花三种毛色猪的次数分别为39、17和16头 描述统计分析描述统计分析的基本统计量（一）算术平均数中位数样本方差描述统计分析的基本统计量（二）样本标准差标准误偏斜度 偏斜度=0，分布对称，偏斜

13、度为正值，分布正偏，即众数位于算术平均数的左侧，偏斜度为负值，分布负偏，即众数位于算术平均数的右侧描述统计分析的基本统计量（三）峰值（峭度）置信半径峰值=0，正态分布，峰值为正值，曲线过于陡峭，峰值为负值，曲线过于平坦描述统计分析对话框200头大白母猪仔猪一月窝重的描述统计分析结果样本均数与总体均数差异显著性检验t检验无效假设为Ho：o备择假设为HA：o计算公式如下：根据以上公式可导出以下结论： 由此可知，当样本平均数落在已知的总体均数置信概率为（1-）的置信区间以外时，就表明在显著水平时差异显著 已知约克夏母猪体重的总体平均数o=130kg，现随机抽测10头母猪的体重，数据如下：121、12

14、7、103、132、157、133、130、139、140、136（kg），试检验该样本是否来自总体均数为130kg的总体。显著性检验计算结果均数差异的显著性检验等方差假设时的成组资料t检验异方差假设时的成组资料t检验成对资料的t检验等方差假设时的成组资料t检验检验的基本公式 现随机抽测8头大白猪与8头哈白猪经产母猪产仔数资料如下（单位：头）：异方差假设时的成组资料t检验检验的基本公式双样本等方差假设t检验结果 现随机抽测8头大白猪与8头哈白猪经产母猪产仔数资料如下（单位：头）：双样本异方差假设t检验结果成对资料的t检验检验的基本公式 现用国产与进口的膘厚测定仪，对14头肥猪进行了测定（单位：

15、mm） ，数据如下：试检验两种仪器测定的结果有无显著差异？成对资料的t检验结果方差分析单因素方差分析二因素交叉无重复方差分析二因素交叉有重复方差分析单因素方差分析具有k组每组n个观察值的数据模式SST=SSA+SSedfT=dfA+dfe单因素方差分析实例 现有5个不同品种的若干头母猪的窝产仔数资料见下表，试检验不同品种母猪平均窝产仔数的差异显著性。五个不同品种母猪产仔数的方差分析表基本公式单因素方差分析表单因素方差分析示意图二因素交叉无重复方差分析二因素交叉无重复观察值的资料模式SST=SSA+SSB+SSedfT=dfA+dfB+dfe平方和及自由度的简易计算公式如下：二因素交叉无重复观察

16、值的方差分析表二因素交叉无重复观察值的方差分析实例无重复双因素方差分析品种与饲料对猪增重的方差分析表二因素交叉有重复方差分析二因素交叉有重复观察值的资料模式平方和及自由度的简易计算公式如下：二因素交叉无重复观察值的方差分析表二因素交叉有重复观察值的方差分析实例 为了考察饲料中钙和磷的含量对幼猪生长发育的影响，将钙（A）和磷（B）在饲料中的含量各分为4个水平进行试验，每个水平组合3头猪，经2个月饲养试验，得幼猪增重结果列于下表。试分析钙和磷及它们之间的交互作用对幼猪生长发育的影响。不同钙磷用量的试验猪增重结果（单位：kg）可重复双因素分析示意图不同钙磷用量试验猪增重结果的方差分析回归分析基本公式

17、直线回归方程的显著性检验回归方程显著性检验的方差分析表回归系数和回归截距的显著性检验95%回归系数的置信区间为：95%回归截距的置信区间为： 现有10只绵羊的胸围（x，cm）和体重（y，kg）的数据下表，请进行回归分析。直线回归计算实例复相关分析（回归统计）表上式中当方程中有截距时i取值为1，否则为0回归方程的方差分析回归系数和回归截距的显著性检验表多元回归分析多元回归分析的矩阵表示多元回归方程的显著性检验多元回归方程显著性检验的方差分析表偏回归系数和回归常数的显著性检验偏回归系数及常数项的95%置信区间为多元回归计算实例 现有某猪场50头肥猪5项胴体性状资料的数据列于下表，其中瘦肉量依变量y

18、，眼肌面积为x1、腿肉量为x2、腰肉量为x3、椎骨数为x4，拟配合估计瘦肉量的最优回归方程。多元回归分析对话框示意图复相关分析（回归统计）表多元回归方程的方差分析偏回归系数和回归常数的显著性检验表相关系数与协方差的计算 简单相关系数的基本公式 简单相关系数的计算实例 用多元回归的例题简单相关系数计算结果 瘦肉量(kg)眼肌面积(cm2)腿肉量(kg)腰肉量(kg)椎骨数(个）瘦肉量(kg)1眼肌面积(cm2)0.5123221腿肉量(kg)0.8746340.4576281腰肉量(kg)0.6969870.3117540.5285191椎骨数(个）0.1806670.451630.087310

19、.0107831协方差的计算 协方差的基本公式 总体协方差 样本协方差 粘帖函数（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

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

21、751 -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 协方差矩阵的逆矩阵 瘦肉量(kg)眼肌面积(cm2)腿肉量(kg)腰肉量(kg)椎骨数(个）瘦肉量(kg)5.1953 -0.1036 -9.3853 -8.3863 -0.5514 眼肌面积(cm2)

22、-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 单因素一元协方差分析 各组平方和的计算公式为： 乘积和计算的基本公式如下： ，或协方差分析简表 校正增重的方差分析变因dfSSxSSySPxybyxdfSSMSF总变异31691381889428156307427.65组间3675523183880组内2

23、86238316576242750.389277129.13264.04校正处理间3298.5299.510.377回归系数的计算公式校正自由度的计算公式总校正自由度组内校正自由度组间校正自由度或校正平方和的计算公式为 总校正平方和组内校正平方和组间校正平方和校正均方的计算公式组间校正均方组内校正均方F检验公式为协方差分析简表的构建 单元格输入形式G3=B3-1G5=B5-1G6=G3-G5H3=D3-（E3E3）C3H5=D5-（E5E5）C5H6=H3-H5I5=H5G5I6=H6G6J6=I6I5F5=E5C5 写入公式后的协方差分析简表 协方差分析实例 在研究3种不同饲料对猪的增重效果

24、时，用24头始重不一样的幼猪分为3组，每组8头进行试验，各组的增重结果见下表。 分组变量原始数据Ix1513111212161417y8583657680918490IIx1716181821221918y9790100951031069994IIIx2224202325273032y89918395100102105110原始数据经计算后所得的组平均数和总和 分组变量组平均数组总和Ix13.75110y81.75654IIx18.625149y98784IIIx25.375203y96.875775原始数据经计算后所得的平方和和乘积和 变因自由度x 平方和y 平方和乘积和总的23720.52

25、555.9581080.75组间2545.251317.583659.875组内21175.251238.375420.875各项乘积和的计算 根据乘积公式进行计算；计算相关系数，然后除以二个变量平方和的几何平均数；计算样本协方差，然后用自由度与样本协方差相乘；计算总体协方差，然后用样本含量与总体协方差相乘各项乘积和的计算，有以下几种方法：计算总体协方差，然后用样本含量与总体协方差相乘分组Array1Array2协方差样本含量乘积和IA1：A8B1：B813.81258110.5IIA9：A16B9：B168.125865IIIA17：A24B17：B2430.671888245.375求和420.875校正增重的方差分析 校正组间平均数的多重比较 用LSD法时公用均数差数标准误的计算公式为： 采用SSR法