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

Excel的统计功能及其应用技巧由“开始”菜单启动桌面快捷方式启动“MicrosoftOffice”快捷方式启动Excel程序的启动工作簿是计算和储存数据的文件

3个工作表工作表可以对数据进行组织和分析表格

256列和65536行组成工作簿和工作表引用就是标识工作表上的单元格或单元格区域。通过引用，可以在公式中使用工作表不同部分的数据，或者在多个公式中使用同一单元格的数值。单元格引用：B10，A10

区域引用：C5：C15，A5：B10单元格和区域引用相对引用：A6＝3×B5；绝对引用：A6＝3×$B$5行绝对引用：A6＝3×B$5列绝对引用：A6＝3×$B5绝对引用与相对引用1、单因素方差分析2、二因素交叉无重复的方差分析3、二因素交叉有重复的方差分析4、描述统计分析5、二样本方差的F检验6、编制频数分布表及绘制直方图7、排位与百分位比排位8、二样本均数差数的u检验9、配对资料的t检验10、等方差非配对资料的t检验11、异方差非配对资料的t检验12、多元回归和直线回归分析13、计算多个变量二二之间的相关系数及协方差14、进行随机和顺序抽样分析工具库提供的统计分析方法由“工具”菜单打开“加载宏”命令分析工具库的安装数据分析工具外观1、AVERAGE 计算算术平均值2、BINOMDIST 计算二项式分布的概率值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电子表格提供的粘帖函数（一）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 计算样本的方差31、VARP 计算样本总体的方差32、ZTEST 计算u-检验的双尾概率值EXCEL电子表格提供的粘帖函数（二）#####错误原因：公式产生的结果太长，单元格容纳不下。解决办法：适当增加列的宽度。#NIV/0！错误原因：除数为零。在公式中，除数使用了空白单元格或包含零值的单元格引用。解决办法：修改单元格引用，或在用作除数的单元格中输入不为零的值。#N/A错误原因：表示在函数和公式中没有可用的数值可以引用。解决办法：检查公式中引用的元格的数据，并输入正确数据。使用公式和函数时出现的常见错误信息（一）#NAME？错误原因：删除了公式中使用名称或使用了不存在的名称以及拼写错误。解决办法：确认使用的名称确实存在。#NULL！错误原因：使用了不正确的区域运算或不正确的单元格引用。解决办法：如果要引用2个不相交的区域，请使用联合运算符（逗号）。例如，=SUM（B2：B5，D2：D5）表示对这2个不相交区域的引用。#NUM！错误原因：在需要数字参数的函数中使用了不能接受的参数或公式产生的数字太大或太小，EXCEL不能表示。如在计算临界t值或F值时，输入的概率值大于1或小于0。解决办法：检查数字是否超出限定区域，函数内的参数是否正确。使用公式和函数时出现的常见错误信息（二）#REF！错误原因：删除了由其它公式引用的单元格或将移动单元格粘帖到由其它引用的单元格中。解决办法：检查引用单元格是否被删除，或者启动相应的应用程序。#VALUE！错误原因：需要数字或逻辑值时输入了文本。解决办法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。使用公式和函数时出现的常见错误信息（三）二项分布平均数、方差和标准差二项分布的概率函数为二项分布概率的计算实例已知某种猪病的死亡率为30%，现有10头病猪，如不给予治疗，问死亡4头及死亡4头和4头以下的概率为多少？死亡4头的概率计算公式为：死亡4头和4头以下概率的计算公式为：用粘帖函数BINOMDIST计算死亡4头的概率计算本计算在编缉栏中为BINOMDIST（4，10，0.30，FALSE）

死亡4头和4头以下概率的计算本计算在编缉栏中为BINOMDIST（4，10，0.30，TRUE）

普哇松分布

普哇松分布的概率函数

为常数，它等于平均数等于方差

==2

普哇松分布概率的计算实例

已知某一地区，出现怪胎的事件服从普哇松分布P（2），请计算该地区出现3次怪胎的概率，及出现3次和3次以下怪胎的概率为多少？

出现3次怪胎概率的公式为：出现3次和3次以下怪胎的概率计算公式为：用POISSON粘帖函数计算出现3次怪胎概率的计算本计算在编缉栏中显示POISSON(3，2，FALSE)出现3次和3次以下怪胎的概率计算本计算编缉栏中显示POISSON(3，2，TRUE)

正态分布

用符号N（，2）

表示正态分布的概率函数为：NORMDIST粘帖函数：计算累积函数NORMINV粘帖函数：计算逆函数已知某品种成年猪体重的总体平均数=100kg，总体标准差=20kg。试计算成年猪体重在70kg以下的概率。计算公式如下：

用正态分布粘帖函数的计算用NORMDIST粘帖函数计算概率

本计算在编缉栏中的形式为NORMDIST(70，100，20，TRUE)用NORMDIST粘帖函数计算函数值

本计算在编缉栏中的形式为NORMDIST(70，100，20，FALSE)已知某品种成年猪体重的总体平均数=100kg，总体标准差=20kg。如果我们希望淘汰30%体重最轻的猪，问体重在多少kg以下的猪应给予淘汰。计算公式如下：

用NORMINV粘贴函数计算

本计算在编缉栏中的形式为NORMINV(0.30，100，20)标准正态分布

用符号N（0，1）

表示标准正态分布的概率函数为：标准化的公式为：利用标准正态分布函数计算概率

NORMSDIST粘贴函数：计算累积函数NORMSINV粘贴函数：计算逆函数已知某品种成年猪体重的总体平均数=100kg，总体标准差=20kg。试计算成年猪体重在70kg以下的概率。首先对70kg进行标准化，得u值等于-1.5，计算公式如下：

用NORMSDIST粘帖函数计算概率

本例在编缉栏中的形式是NORMSDIST(-1.5)

当给定一尾概率值时，求其临界值就得利用NORMSINV函数。假定现要计算当一尾概率为0.025时，计算公式如下：用NORMSINV粘贴函数计算

本例在编缉栏中的形式是NORMSINV(0.025)学生氏t分布

t分布的概率密度函数为

学生氏t分布的计算实例

TDIST粘贴函数：单尾或双尾概率

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时的一尾概率

本计算在编缉栏中为CHIDIST（3.84，1）用CHIINV粘贴函数计算临界值

请计算自由度为10及一尾概率为0.01时的2临界值

本计算在编缉栏中为CHIINV（0.01，10）F分布

F分布的概率密度函数为：F>0F分布的计算实例

FDIST粘贴函数：计算单尾概率FINV粘贴函数：计算临界值

FDIST函数积分公式为：

FINV函数积分公式为：用函数FDIST计算一尾概率

请计算第一自由度等于2、第二自由度等于4及F值等于18时的一尾概率

本计算在编缉栏中为FDIST（18，2，4）用函数FINV计算临界F值

请计算df1=3，df2=10及一尾概率为0.05时的临界F值

本计算在编缉栏中为FINV（0.05，3，10）EXCEL电子表格的模拟运算功能模拟运算表是工作表中的一个单元格区域，它可以显示公式中某些值的变化对计算结果的影响。模拟运算表为同时求解某一运算中所有可能的变化值的组合提供了捷径，并且还可以将所有不同的计算结果同时显示在工作表中，便于查找和比较。模拟运算表有两种类型：单变量模拟运算表双变量模拟运算表

利用模拟运算计算临界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.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%其他0100.00%次数分布表、柏拉图表及其累积频率

次数分布图（直方图）

离散型数据

白黑花白黑花白黑白白白花白白花白白花白黑白白黑白白黑白白黑花白白花白白白白黑白白黑花白黑花白黑花白白白白白白白黑花白黑花白黑花白黑花白黑花白黑花杂一代白毛黑斑猪自交后的毛色分离情况

将白、黑和花分别转换成1、2和3，然后利用直方图工具求出频数分布表，再将1、2和3恢复成白、黑和花就得到了这三种毛色猪的次数分布表，其结果为白、黑和花三种毛色猪的次数分别为39、17和16头

描述统计分析描述统计分析的基本统计量（一）算术平均数中位数样本方差描述统计分析的基本统计量（二）样本标准差标准误偏斜度偏斜度=0，分布对称，偏斜度为正值，分布正偏，即众数位于算术平均数的左侧，偏斜度为负值，分布负偏，即众数位于算术平均数的右侧描述统计分析的基本统计量（三）峰值（峭度）置信半径峰值=0，正态分布，峰值为正值，曲线过于陡峭，峰值为负值，曲线过于平坦描述统计分析对话框200头大白母猪仔猪一月窝重的描述统计分析结果样本均数与总体均数差异显著性检验t检验无效假设为Ho：o备择假设为HA：o计算公式如下：根据以上公式可导出以下结论：由此可知，当样本平均数落在已知的总体均数置信概率为（1-）的置信区间以外时，就表明在显著水平时差异显著已知约克夏母猪体重的总体平均数o=130kg，现随机抽测10头母猪的体重，数据如下：121、127、103、132、157、133、130、139、140、136（kg），试检验该样本是否来自总体均数为130kg的总体。显著性检验计算结果均数差异的显著性检验等方差假设时的成组资料t检验异方差假设时的成组资料t检验成对资料的t检验等方差假设时的成组资料t检验检验的基本公式现随机抽测8头大白猪与8头哈白猪经产母猪产仔数资料如下（单位：头）：异方差假设时的成组资料t检验检验的基本公式双样本等方差假设t检验结果现随机抽测8头大白猪与8头哈白猪经产母猪产仔数资料如下（单位：头）：双样本异方差假设t检验结果成对资料的t检验检验的基本公式现用国产与进口的膘厚测定仪，对14头肥猪进行了测定（单位：mm），数据如下：试检验两种仪器测定的结果有无显著差异？成对资料的t检验结果方差分析单因素方差分析二因素交叉无重复方差分析二因素交叉有重复方差分析单因素方差分析具有k组每组n个观察值的数据模式SST=SSA+SSedfT=dfA+dfe单因素方差分析实例现有5个不同品种的若干头母猪的窝产仔数资料见下表，试检验不同品种母猪平均窝产仔数的差异显著性。五个不同品种母猪产仔数的方差分析表基本公式单因素方差分析表单因素方差分析示意图二因素交叉无重复方差分析二因素交叉无重复观察值的资料模式SST=SSA+SSB+SSedfT=dfA+dfB+dfe平方和及自由度的简易计算公式如下：二因素交叉无重复观察值的方差分析表二因素交叉无重复观察值的方差分析实例无重复双因素方差分析品种与饲料对猪增重的方差分析表二因素交叉有重复方差分析二因素交叉有重复观察值的资料模式平方和及自由度的简易计算公式如下：二因素交叉无重复观察值的方差分析表二因素交叉有重复观察值的方差分析实例为了考察饲料中钙和磷的含量对幼猪生长发育的影响，将钙（A）和磷（B）在饲料中的含量各分为4个水平进行试验，每个水平组合3头猪，经2个月饲养试验，得幼猪增重结果列于下表。试分析钙和磷及它们之间的交互作用对幼猪生长发育的影响。不同钙磷用量的试验猪增重结果（单位：kg）可重复双因素分析示意图不同钙磷用量试验猪增重结果的方差分析回归分析基本公式直线回归方程的显著性检验回归方程显著性检验的方差分析表回归系数和回归截距的显著性检验95%回归系数的置信区间为：95%回归截距的置信区间为：现有10只绵羊的胸围（x，cm）和体重（y，kg）的数据下表，请进行回归分析。直线回归计算实例复相关分析（回归统计）表上式中当方程中有截距时i取值为1，否则为0回归方程的方差分析回归系数和回归截距的显著性检验表多元回归分析多元回归分析的矩阵表示多元回归方程的显著性检验多元回归方程显著性检验的方差分析表偏回归系数和回归常数的显著性检验偏回归系数及常数项的95%置信区间为多元回归计算实例现有某猪场50头肥猪5项胴体性状资料的数据列于下表，其中瘦肉量依变量y，眼肌面积为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.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.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.6751-2.2740-0.6248眼肌面积(cm2)-0.46981.6711-0.2760-0.0407-0.6453腿肉量(kg)-4.6751-0.27604.72190.84300.5479腰肉量(kg)-2.2740-0.04070.84302.14850.3325椎骨数(个）-0.6248-0.64530.54790.33251.3529协方差矩阵的逆矩阵

瘦肉量(kg)眼肌面积(cm2)腿肉量(kg)腰肉量(kg)椎骨数(个）瘦肉量(kg)5.1953-0.1036-9.3853-8.3863-0.5514眼肌面积(cm2)-0.10360.1100-0.1653-0.0448-0.1699腿肉量(kg)-9.3853-0.165325.74088.44211.3132腰肉量(kg)-8.3863-0.04488.442139.52651.4638椎骨数(个）-0.5514-0.16991.31321.46381.4256单因素一元协方差分析

各组平方和的计算公式为：

乘积和计算的基本公式如下：

， ， ， 或 协方差分析简表

校正增重的方差分析变因dfSSxSSySPxybyxdf′SS′MS′F总变异31691381889428156

307427.65

组间3675523183880

组内286238316576242750.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头始重不一样的幼猪分为3组，每组8头进行试验，各组的增重结果见下表。

分组变量原始数据Ix1513111212161417

y8583657680918490IIx1716181821221918

y9790100951031069994IIIx2224202325273032

y89918395100102105110原始数据经计算后所得的组平均数和总和

分组变量组平均数组总和Ix13.75110

y81.75654IIx18.625149

y98784IIIx25.375203

y96.875775原始数据经计算后所得的平方和和乘积和

变因自由度x平方和y平方和乘积和总的23720.52555.9581080.75组间2545.251317.583659.875组内21175.251238.375420.875各项乘积和的计算

根据乘积公式进行计算；计算相关系数，然后除以二个变量平方和的几何平均数；计算样本协方差，然后用自由度与样本协方差相乘；计算总体协方差，然后用样本含量与总体协方差相乘各项乘积和的计算，有以下几种方法：计算总体协方差，然后用样本含量与总体协方差相乘分组Array1Array2协方差样本含量乘积和IA1：A8B1：B813.81258110.5IIA9：A16B9：B168.125865IIIA17：