统计与数据分析编写提纲.docx
《统计与数据分析编写提纲.docx》由会员分享，可在线阅读，更多相关《统计与数据分析编写提纲.docx（62页珍藏版）》请在冰豆网上搜索。
统计与数据分析编写提纲
田间试验与统计分析上机实习指导
——利用Excel进行统计分析
黄亚群常金华祝丽英编
生物类专业用
河北农业大学教材出版科
保定2003.7
概述
MicrosoftExcel电子表格具有强大的统计分析功能，利用电于表格可以解决生物科学、农业试验和生产实际中数据的常见的统计分析问题。
其统计分析过程主要通过内置的“分析工具库”和粘贴函数来完成。
一、分析工具库的安装
在默认的情况下，Excel并没有安装“分析工具库”，只有在安装后才能使用。
可先在“工具”菜单中检查一下是否有“数据分析”条目。
如没有，可按以下步骤安装：
通过“工具”菜单中打开“加载宏”表单，选择“分析工具库”，再按“确定”。
见图1-1。
图１-1“加载宏’滚单
打开“工具”菜单，选定“数据分析”并单击它，就可得到图1-2。
图1-2
二、分析工具库提供的统计分析方法
分析工具库提供的常用统计分析方法如下：
1单向分组资料的方差分析
2无重复资料的方差分析
③ 有重复资料的方差分析
④ 描述性统计量计算
⑤ 二总体方差的同质性检验
6编制频数分布表及绘制直方图
7排位与百分值比排位
8二样本均数差数的U检验
9配对资料的t检验
10等方差非配对资料的t检验
11异方差非配对资料的t检验
12一元线性回归与多元线性回归分析
13计算多个变量两两之间的相关系数及协方差
14进行随机和顺序抽样
15抽样
三、统计分析的粘贴函教
Excel提供的函数种类
●财务函数
●数据库函数
●日期与时间函数
●文本函数
●数学和三角函数
●逻辑函数
●统计函数
●信息函数
●查找与引用函数
●工程函数
在统计函数模块中有76个统计函数，这些函数覆盖了统计基本理论与分析方法。
Excel提供的常用统计分析粘贴函数有：
1.AVEDEV返回数据点与其平均值的绝对偏差的平均值
2.AVERAGE返回参数的平均值
3.AVERAGEA返回参数的平均值，包括数字、文本和逻辑值
4.BETADIST返回累积的Beta概率密度函数
5.BETAINV返回累积的Beta概率密度函数的反函数
6.BINOMDIST返回一元二项式分布概率
7.CHIDIST返回chi平方分布的单尾概率
8.CHIINV返回chi平方分布的反单尾概率
9.CHITEST返回独立性检验值
10.CONFIDENCE返回总体平均值的置信区间
11.CORREL返回两个数据集之间的相关系数
12.COUNT计算参数列表中数字的个数
13.COUNTA计算参数列表中值的个数
14.COVAR返回协方差，即成对偏移乘积的平均数
15.CRITBINOM返回使累积二项式分布小于等于临界值的最小值
16.DEVSQ返回偏差的平方和
17.EXPONDIST返回指数分布
18.FDIST返回F概率分布
19.FINV返回反F概率分布
20.FISHER返回Fisher变换
21.FISHERINV返回反Fisher变换
22.FORECAST根据线性趋势返回值
23.FREQUENCY以向量数组的形式返回频率分布
24.FTEST返回F检验的结果
25.GAMMADIST返回gamma分布
26.GAMMAINV返回反gamma累积分布
27.GAMMALN返回gamma函数的自然对数，Γ（x）
28.GEOMEAN返回几何平均值
29.GROWTH根据指数趋势返回值
30.HARMEAN返回调和平均值
31.HYPGEOMDIST返回超几何分布
32.INTERCEPT返回线性回归线截距
33.KURT返回数据集的峰值
34.LARGE返回数据集中第k个最大值
35.LINEST返回线性趋势的参数
36.LOGEST返回指数趋势的参数
37.LOGINV返回反对数正态分布
38.LOGNORMDIST返回累积对数正态分布函数
39.MAX返回参数列表中的最大值
40.MAXA返回参数列表中的最大值，包括数字、文本和逻辑值
41.MEDIAN返回给定数字的中值
42.MIN返回参数列表中的最小值
43.MINA返回参数列表中的最小值，包括数字、文本和逻辑值
44.MODE返回数据集中出现最多的值
45.NEGBINOMDIST返回负二项式分布
46.NORMDIST返回正态累积分布
47.NORMINV返回反正态累积分布
48.NORMSDIST返回标准正态累积分布
49.NORMSINV返回反标准正态累积分布
50.PEARSON返回Pearson乘积矩相关系数
51.PERCENTILE返回区域中的第k个百分位值
52.PERCENTRANK返回数据集中值的百分比排位
53.PERMUT返回给定数目对象的排列数
54.POISSON返回Poisson分布
55.PROB返回区域中的值在上下限之间的概率
56.QUARTILE返回数据集的四分位数
57.RANK返回某数在数字列表中的排位
58.RSQ返回Pearson乘积矩相关系数的平方
59.SKEW返回分布的偏斜度
60.SLOPE返回线性回归直线的斜率
61.SMALL返回数据集中的第k个最小值
62.STANDARDIZE返回正态化数值
63.STDEV基于样本估算标准偏差
64.STDEVA基于样本估算标准偏差，包括数字、文本和逻辑值
65.STDEVP计算基于整个样本总体的标准偏差
66.STDEVPA计算整个样本总体的标准偏差，包括数字、文本和逻辑值
67.STEYX返回通过线性回归法预测每个x的y值时所产生的标准误差
68.TDIST返回学生的t分布
69.TINV返回学生的t分布的反分布
70.TREND返回沿线性趋势的值
71.TTEST返回与学生的t检验相关的概率
72.VAR基于样本估算方差
73.VARA基于样本估算方差，包括数字、文本和逻辑值
74.VARP基于整个样本总体计算方差
75.VARPA基于整个样本总体计算方差，包括数字、文本和逻辑值
76.ZTEST返回z检验的双尾P值
四、Excel的常用基本概念
（一）工作簿和工作表
MicrosoftExcel工作簿是计算和储存数据的文件，每一个工作簿都可以包含多张工作表，因此可在单个文件中管理各种类型的相关信息。
使用工作表可以对数据进行组织和分析。
可以同时在多张工作表上输入并编辑数据，并且可以对来自不同工作表的数据进行汇总计算。
在创建图表之后，既可以将其置于原数据所在的工作表上，也可以放置在单独的图表工作表上。
工作表的名称显示于工作簿窗口底部的工作表标签上。
单击工作表标签即可进入该工作表。
当前所在的工作表称为活动工作表，在它的标签上标有单下划线。
可以在同一工作簿内或两个工作薄之间对工作表进行改名、添加、删除、移动或复制等操作。
在默认情况下，每个工作薄由3个工作表组成，用户可以根据需要添加更多的工作表。
每个工作表由256列和65536行组成。
行和列相交形成单元格，它是存储数据的基本单位。
列用英文字母表示，开始是单个英文字母，然后是2个英文字母组合，即由A～IV共256列。
行用阿拉伯数字表示，由1～65536。
每个单元格的定位可以通过该单元格所对应的行数及列标来确定，如B6就表示B列第6行的单元格。
在活动工作表众多的单元格中，其中有一单元格含有粗边框线，该单元格称为活动单元格，在该单元格中可以输入和编缉数据。
每进入一个工作表时，A1自动为活动单元格。
在活动单元格的右下角有一小黑方块，这个黑方块称为填充柄，利用此填充柄可以填充相邻单元格区域的内容。
（二） 单元格和区域引用
引用是指调用工作表中的数据用于计算分析，它是通过标识工作表上的单元格或区域来实现的。
通过引用，指明在计算时所使用的数据在工作表中的位置，可以使用工作表中不同区域的数据，或者在多个计算公式中使用同一单元格的数值。
还可以引用同一工作簿不同工作表、不同工作簿、甚至其他应用程序中的数据。
引用不同工作簿中的数据称为外部引用，引用其他程序中的数据称为远程引用。
如果要引用某一单元格的数据，可在计算公式中输入该单元格的标识，例如要引用在列B和行10这一单元格的数据，在计算公式中输入B10。
或者用鼠标点击该单元格。
如果要引用某个区域的数据，可在计算公式中输入该区域左上角单元格的标识，后面跟一个冒号（：
），后面再写上该区域右下角单元格的标识。
例如，如果要引用从单元格C5到单元格C15这一区域，可输入C5：
C15，如果要引用从单元格A5到单元格B10这一区域，可输入A5：
B10。
（三）绝对引用与相对引用的区别
当生成公式时，对单元格或区域的引用通常基于它们与公式单元格的相对位置。
如在单元格A6中包含公式=3B5；MicrosoftExcel将在距单元格A6上面一个单元格和右面一个单元格处的单元格中查找数值。
这就是相对引用。
当复制使用相对引用的公式时，被粘贴公式中的引用将被更新，并指向与当前公式位置相对应的其它单元格。
如单元格A6中的公式被复制到单元格A8中。
单元格A5中的公式已经改为=3B7，即从单元格A8上面和右面一个单元格处的单元格中查找数值。
如果在复制公式时，不希望引用发生改变，请使用绝对引用，绝对引用的方式是在相对引用列标和行数字前加上美元符号，即＄B＄5。
如在单元格A6中包含的公式=3＄B＄5，现在将公式复制到另一单元格中，则公式中的引用将不发生改变。
（四）计算出现的错误信息
输入计算公式及在公式中引用函数后、如果系统看不懂该公式，会在单元格中显示错误信息。
下面列出了一些常见的错误信息、可能产生的原因以及解决的办法。
＃＃＃＃＃
错误原因：
公式产生的结果太长，单元格容纳不下。
解决办法：
适当增加列的宽度。
＃NIV／0！
错误原因：
除数为零。
在公式中，除数使用了空白单元格或包含零值的单元格引用。
解决办法：
修改单元格引用，或在用做除数的单元格中输入不为零的值。
＃N／A
错误原因：
表示在函数和公式中没有可用的数值可以引用。
解决办法：
检查公式中引用的单元格的数据，并输人正确数据。
＃NAME？
错误原因：
删除了公式中使用名称或使用了不存在的名称以及拼写错误。
解决办法：
确认使用的名称确实存在。
＃NULL！
错误原因：
使用了不正确的区域运算或不正确的单元格引用。
解决办法：
如果要引用2个不相交的区域，请使用联合运算符（逗号）。
例如，=SUM（B2：
B5，D2：
D5）表示对这2个不相交区域的引用。
＃NUM！
错误原因：
在需要数字参数的函数中使用了不能接受的参数或公式产生的数字太大或太小，Excel不能表示。
如在计算临界t值或F值时，输入的概率值大于1或小于0。
解决办法：
检查数字是否超出限定区域，函数内的参数是否正确。
＃REF！
错误原因：
删除了由其他公式引用的单元格或将移动单元格粘贴到由其他引用的单元格中。
解决办法：
检查引用单元格是否被删除，或者启动相应的应用程序。
＃VALUE！
错误原因：
需要数字或逻辑值时输入了文本。
解决办法：
确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。
实验一数据整理
一、数据整理的内容
1.数据清单结构的建立与数据录入
数据清单是指包含相关数据的一系列工作表的数据行。
数据清单可以作为数据库使用，其中行表示记录，列表示字段。
数据清单的第一行中包含列标，通常称列的名称为变量或标志，或字段，行的名称为观察值，每一个单格中包含了一个变量所发生的数值。
2.数据排序与分组
一般来说，录入数据清单的数据是无序的，不能反映现象的本质与规律。
为了使用的方便，要将其进行排序、分组，以便使数据按要求排列，同时使性质相同的数据归为一组，从而让它们之间的差异性显示出来。
3.编制次数分布表与累计次数分布表
次数分布表可以表明各组间的单位数在总体中所出现的次数或所占的比重，揭示总体的内部结构特征。
累计次数分布则能够表明某一组段以上或以下数据所出现的次数或比重。
4.统计图
各种各样的统计图可以形象、直观地表明数据的分布形态与发展变化的趋势。
二、数据整理工具
1.数据排序
表1.1100个麦穗的每穗小穗数
18
15
17
19
16
15
20
18
19
17
17
18
17
16
18
20
19
17
16
18
17
16
17
19
18
18
17
17
17
18
18
15
16
18
18
18
17
20
19
18
17
19
15
17
17
17
16
17
18
18
17
19
19
17
19
17
18
16
18
17
17
19
16
16
17
17
17
16
17
16
18
19
18
18
19
19
20
15
16
19
18
17
18
20
19
17
18
17
17
16
15
16
18
17
18
16
17
19
19
17
Excel可以根据用户的要求对数据清单的行或列数据进行排序。
排序时，Excel将利用指定的排序顺序重新排列行、列或各单元格，从而使研究对象的规则性更加简洁地表现出来。
例1.1随机抽取100个小麦麦穗，计数每穗小穗数，未加整理的原始数据列于表1-1。
利用Excel操作步骤：
录入原始数据
在菜单中选择“数据”中的“排序”选项，弹出排序对话框（图2-1）。
图2-1图2-2
在排序对话框窗口中，选择“主要关键字”列表中的“每穗小穗数”作为排序关键字，并选择按“递增”排序，在“列表”中选择“有标题行”。
若没有输入“每穗小穗数”标题行，在“列表”中选择“无标题行”。
然后单击“确定”按钮，即可得到排序的结果如图2-2所示。
从数据排序结果可以看出，每穗小穗数从15个到20个变动范围的递增顺序排列。
若想进一步知道某一种类型小穗数有多少个，可利用Excel中的“分类汇总”功能。
在“数据”菜单中选择“分类汇总”选项，Excel便会自动地创建公式，生成自动分级显示的数据清单。
但要注意在分类汇总前需要先对数据进行排序。
在数据清单中使用分类汇总的方法如下：
先选择需要分类汇总的数据区域A1：
A100，然后选择“数据”菜单中的“分类汇总”选项，则打开“分类汇总”对话框如图2-3所示。
图2-3图2-4
在“分类字段”的下拉式列表中选择要进行分类的列标题，在“汇总方式”的下拉式列表中选择进行汇总的方式，本例中选择按“每穗小穗数”进行分类，选择按“计数”进行汇总，单击“确定”按钮，便得到分类汇总的结果如图2-4所示。
在对数据进行了分类汇总之后，可以观察到“每穗小穗数”为15个有6个，“每穗小穗数”为16个有15等等。
2.利用频数分布函数进行分组
频数分布函数（FEQUENCY）的语法形式为：
FREQUENCY（data_array，bins_array）
其中：
Data_array为用来编制频数分布的数据，Bins_array为频数或次数的接收区间。
频数分布函数（FEQUENCY）可以对一列垂直数组返回某个区域中数据的频数分布。
建立频数分布的步骤：
先对数据进行排序，以了解全部数据的变动范围；
选择全部数据的分组组数；
确定分组的组限；
最后对各组数值所出现的频数进行计数。
例1.2以例1.1数据为例说明之。
打开“100个每穗小穗数.xls”工作表。
在单元格C2中输入“分组”，在单元格D2中输入“频数”。
在C3：
C8区域中依次输入15，16，17，18，19，20，作为频数接受区域，它们分别表明每穗小穗数由15个、16个等的个数。
选定D3：
D8区域，在“插入”菜单中单击“函数”选项，打开“插入函数”对话框如图2-5及图2-6所示。
图2-5图2-6图2-7
在数据区域“Data_array”中输入单元格A2：
A101，在数据接受区间Bins_array中输入单元格C3：
C8，在对话窗口中可以看到其相应的频数是6，15，32，25，17，5，0。
由于频数分布是数组操作，所以，此处不能直接单击“确定”按钮，而应先按下F2，再按下Ctrl+Shift+Enter组合键，得到频数分布如图2-7所示。
另外，直接利用Excel函数公式也可以得到同样结果。
用鼠标选定单元格D3：
D9，注意不要释放选定区域。
在D3单元格中输入频数分布函数公式：
=FREQUENCY（A2：
A101，C3：
C8）
在这个公式中，数据区域为A2：
A101，接受区间为C3：
C8，按Ctrl+Shift组合键，同时敲“回车”键，得到频数分布与上面相同。
从频数分布结果可以看出，每穗小数以17个为最多，且以每穗小数17个为中心对称分布。
3.利用透视表进行数据整理
利用透视表进行数据整理可分为单变量表格与双变量表格，前者又称为单向表，后者又称为交叉表。
（1）单向表
例1.3某移动通讯公司要制定新一年的销售计划，需要向市场了解移动用户对数字移动电话类型的需求。
经过问卷设计，在某地区抽取了50个样本进行问卷调查，获得关于移动电话用户消费行为的一些数据资料。
试根据工作表中的数据分析该地区移动电话用户的电话类型偏好。
城乡
每周通话次数
数字移动电话类型
性别
城乡
每周通话次数
数字移动电话类型
性别
城市
20
神州行
男
城市
20
长白行
男
农村
20
神州行
男
城市
20
长白行
男
农村
40
神州行
男
城市
16
全球通
男
城市
30
全球通
男
城市
20
长白行
男
城市
10
全球通
男
城市
10
全球通
女
城市
20
全球通
女
城市
8
长白行
男
城市
20
长白行
男
城市
15
长白行
男
城市
40
神州行
男
城市
10
全球通
男
城市
60
全球通
男
城市
9
全球通
男
城市
20
全球通
男
城市
4
长白行
男
城市
20
全球通
男
城市
10
全球通
女
城市
20
全球通
男
城市
15
全球通
女
城市
20
全球通
男
城市
20
全球通
男
城市
20
长白行
男
城市
10
全球通
女
城市
20
全球通
男
城市
15
全球通
男
城市
25
全球通
男
城市
20
全球通
男
城市
30
全球通
男
城市
6
全球通
女
城市
7
全球通
女
城市
1
全球通
男
城市
20
全球通
男
城市
30
全球通
男
城市
10
全球通
男
城市
20
全球通
男
城市
10
神州行
男
城市
10
全球通
女
城市
13
全球通
男
城市
20
全球通
男
城市
30
全球通
男
城市
20
全球通
男
城市
40
全球通
男
城市
10
长白行
男
城市
30
全球通
男
城市
20
全球通
男
①打开“数据整理.xls”工作表。
②单击“数据”菜单中的“数据透视表和图表报告”选项，Excel弹出数据透视表向导对话框如图2-8所示。
图2-8图2-9
根据向导要求，第1步是指定“数据源类型”和确定所需创建的“报表类型”。
选择“数据清单或数据库”作为数据源，选择“数据透视表”作为报表类型。
最后单击“下一步”。
第2步是确定数据区域，本例中数据所覆盖区域为A1：
D51单元格，单击“下一步”按钮，向导给出第3步操作对话框窗口如图2-9所示。
第3步确定数据透视表的位置。
选择现有工作表，并利用鼠标将数据透视表的位置确定在E1单元格中，如果单击“完成”按钮，可以结束操作。
单击“布局”，Excel弹出图2-10对话框窗
图2-10
口，选择右边的“数字移动电话类型”字段，并将它拖到左边的“行”区，再选择“数字移动电话类型”字段，将其拖放在“数据”区域中，显示为“计数项：
数字移动电话类型”，以便进行数据
图2-11
汇总。
双击数据区域中的“计数项：
数字移动电话类型”字段，打开“数据透视表字段”对话框，在“汇总方式”列表中选择“计数”，如图2-12。
图2-12
如果想选择数据显示方式，可单击图右侧的“选项”按钮，则EXCEL弹出“数据显示方式”。
在“数据显示方式”的下拉选项中确定“占同列数据总和的百分比”选项，单击“确定”按钮，则版式调整完毕。
再单击“确定”按钮，回到数据透视表步骤3，如图2-13所示。
图2-13
单击数据透视表步骤3中的“完成”按钮，将数字移动电话类型移动到汇总表中得数字移动电话调查单向表如图2-14所示。
单向表描述了各移动电话用户对移动电话类型偏好的分布情况。
从图中可以看出“全球通”是该地区的主要消费特征，其次是“长白行”，因而该公司应当注重这两种电话类型的开发与服务。
图2-14
（2）两向表
两向表用于表示两个用文字表示的品质变量之间的关系。
如上例中，若该公司市场部经理希望根据上例中的调查结果，确认不同性别的用户，其移动电话类型偏好是否存在着不同。
打开“移动电话调查”工作表。
从“数据”菜单中选择“数据透视表和图表报告”选项，得到“数据透视表对话框”窗口。
在第1步之中选择“数据清单或数据库”与“数据透视表”，单击“下一步”按钮。
在第2步中，确定数据区域A1：
D51，单击“下一步”按钮。
在第3步中，确定数据透视表的位置，如将其位置调整到“H1”单元格。
然后，单击“布局”按钮，打开“布局”对话框如图2-15所示。
图2-15
将对话框右边的“数字移动电话类型”字段拖到左边的“行”区作为分组变量，再将右边的“性别”字段拖到“列”区，作为交叉分析的变量，最后将右边的“性别”字段拖放到“数据”区域中，并选择计数项。
如果希望结果是以百分比的形式显示，则可双击数据区域中的“计数项，性别”，则会弹出“数据透视表字段”对话框。
选择“占同列数据总和的百分比”选项，来表示所出现的频率，最后，单击“确定”按钮，又回到“布局”对话框。
单击“确定”按钮，回到第3步，要注意数据透视表的位置定在单元格H1，最后，单击“完成”按钮，得到交叉表如图2-16所示。
图2-16
从上面的交叉表中可以看出，对于移动电话类型来讲，其性别之间有较大的差异，女性偏好“全球通”。
当然这只是一种数据整理的结果，要想准确地说明移动电话消费是否存在着性别差异，还需要使用统计推断方法进行假设检验。
4．统
升级会员 