EXCEL中的统计分析

EXCEL中的统计分析

EXCEL中的统计分析（一）

　　一、安装“分析工具库”

　　 “工具”、“加载宏”命令，然后在“加载宏”对话框中选定“分析工具库”，再按“确定”钮，“数据分析”这一项就出现在工具菜单栏中。

　　二、应用Excel 来处理成对比较( Paired Comparison) 的假设检验

　　a. 如病毒在番茄上产生的病斑数

1	处理	1	2	3	4	5	6	7
2	A	10	13	8	3	5	20	6
3	B	25	12	14	15	12	27	18

　    b. 在“工具”栏中选择“数据分析”命令，得对话框，选定“t 检验:平均值的成对二样本分析”的分析，工具，按“确定”钮。

c. 在“ t 检验：平均值的成对二样本分析”的对话框中，按照提示在变量1 的区域(1) 中输入＄B＄2 : ＄H ＄2；在变量2 的区域(2) 中输入＄B ＄3 :＄H ＄3；α的值设为0. 01 ，按“确定”输入。选择新的工作表组，Excel 给出结果(参见表3) 。

　　表3 　t 检验: 成对双样本均值分析

t-检验: 成对双样本均值分析
	变量 1	变量 2
平均	9.285714286	17.57142857
方差	33.23809524	37.61904762
观测值	7	7
泊松相关系数	0.607344754
假设平均差	0
df	6
t Stat	-4.149920843
P(T<=t) 单尾	0.003005811
t 单尾临界	3.142668403
P(T<=t) 双尾	0.006011621
t 双尾临界	3.70742802

　　表中变量1 为A 处理，变量2 为B 处理。t Stat即t 值为- 4. 14992 ，t 双尾临界即t0. 01 = 3. 707428

　　三、应用Excel 来处理方差分析

　　a. 在新工作表中输入数据，即在A1～A6 、B1～B6 、C1～C6 、D1～D6 分别输入各组、各处理的数据，如表5。

表5 　生长素处理豌豆的试验结果数据表

序号	A	B	C	D
1	60	62	61	60
2	65	65	68	65
3	63	61	61	60
4		67	63	61
5	62	65	62
6	61	62	62	65

　　b. 在“工具”栏选择“数据分析”命令，弹出“分析工具”对话框“方差分析：无重复双因素分析”，单击“确定”按钮，在接着弹出的对话框中输入数据范围，＄A＄1：＄D＄6，α值设为0. 05。

　　c. 单击“确定”钮，得分析表，如表6 

表6 方差分析

方差分析：无重复双因素分析
SUMMARY	观测数	求和	平均	方差
行 1	4	243	60.75	0.916666667
行 2	4	263	65.75	2.25
行 3	4	245	61.25	1.583333333
行 4	4	255	63.75	6.25
行 5	4	253	63.25	2.25
行 6	4	250	62.5	3
列 1	6	375	62.5	3.5
列 2	6	382	63.6666667	5.466666667
列 3	6	377	62.8333333	6.966666667
列 4	6	375	62.5	5.9

方差分析
差异源	SS	df	MS	F	P-value	F crit
行	65.875	5	13.175	4.5966314	0.009918	2.901295
列	5.458333333	3	1.81944444	0.630413859	0.606622	3.287382
误差	43.29166667	15	2.88611111
总计	114.625	23

　　从表中得到二因素各自的平均值及行(处理间) 、列(区组) 、误差SS 值、df 值、MS 值等，其中F临界值为F0. 05。

　　注明：另有单因素方差分析；可重复双因素方差分析；无重复双因素方差分析；相关系数；协方差；描述统计；指数平滑；F检验双样本方差；傅立叶分析；直方图；随机数发生器；排位与百分比排位；回归；抽样；t-检验:成对双样本均值分析；t-检验:双样本等方差假设；t-检验:双样本异方差假设；z-检验:双样本平均差检验。[共19种]

四、应用Excel 来处理双因素方差分析

单击“工具”菜单中的“数据分析”命令，在出现的“数据分析”列表框中选择“方差分析：可重复双因素分析”，再单击“确定”按钮，屏幕将跳出的“方差分析：可重复双因素分析”对话框。在“输入”框的“输入区域”输入$B$2:$E$11，“每一样本的行数”中输入3，α取0.01；在“输出选项”框的“输出区域”输入$A$13或新工作簿，如图：

表1 3种肥料施于3种土壤的小麦产量

盆	b2(1)	b2(2)	b2(3)
a1 1	21.4	19.6	17.6
2	21.2	18.8	16.6
3	20.1	16.4	17.5
a2 1	12	13	13.3
2	14.2	13.7	14
3	12.1	12	13.9
a3 1	12.8	14.2	12
2	13.8	13.6	14.6
3	13.7	13.3	14

方差分析：可重复双因素分析

方差分析：可重复双因素分析
SUMMARY	b2(1)	b2(2)	b2(3)	总计
a1 1
观测数	3	3	3	9
求和	62.7	54.8	51.7	169.2
平均	20.9	18.26666667	17.2333333	18.8
方差	0.49	2.773333333	0.30333333	3.5725
a2 1
观测数	3	3	3	9
求和	38.3	38.7	41.2	118.2
平均	12.76666667	12.9	13.7333333	13.13333333
方差	1.543333333	0.73	0.14333333	0.81
a3 1
观测数	3	3	3	9
求和	40.3	41.1	40.6	122
平均	13.43333333	13.7	13.5333333	13.55555556
方差	0.303333333	0.21	1.85333333	0.605277778
总计
观测数	9	9	9
求和	141.3	134.6	133.5
平均	15.7	14.95555556	14.8333333
方差	15.8775	7.215277778	3.8225

方差分析
差异源	SS	df	MS	F	P-value	F crit
样本	179.3807407	2	.6903704	96.67225549	2.36E-10	6.012905
列	3.960740741	2	1.98037037	2.134530938	0.147277	6.012905
交互	19.24148148	4	4.81037037	5.184830339	0.005873	4.579036
内部	16.7	18	0.92777778
总计	219.282963	26

单击“方差分析：可重复双因素分析”对话框中的“确定”按钮即可获得方差分析结果。由图可看出样本（肥类）、交互（肥类+土类）效应都是极显著的，而列（土类）无显著差异。

五、应用Excel 来进行数据的描述统计

　　

可以得到平均标准误差，中位数，众数，标准差，方差，峰度，偏度，区域，最小值，最大值，求和，观测数，最大(1)，最小(1)，置信度(99.0%)

我们常常用到的RSDRSD即Relative Standard Deviation.叫相对标准偏差，也称变异系数(CV).RSD=标准差/平均值*100% 常用它表示精密度，不能立刻得到可进行再运算。eg.

12	12	21	14	14
13	22	34	22	31
21	11	33	14	21

单击“工具”菜单中的“数据分析”命令，在出现的“数据分析”列表框中选择“描述统计”。可在“描述统计”中选择区域及逐行或者逐列执行。

我选择三行进行逐行分析结果如下。

另外 我还在每数字栏选择了“设置单元格格式”——“小数位数”选择为3

行1		行2		行3
平均	14.600	平均	24.400	平均	20.000
标准误差	1.661	标准误差	3.723	标准误差	3.795
中位数	14.000	中位数	22.000	中位数	21.000
众数	12.000	众数	22.000	众数	21.000
标准差	3.715	标准差	8.325	标准差	8.485
方差	13.800	方差	69.300	方差	72.000
峰度	3.614	峰度	-0.882	峰度	0.782
偏度	1.841	偏度	-0.243	偏度	0.855
区域	9.000	区域	21.000	区域	22.000
最小值	12.000	最小值	13.000	最小值	11.000
最大值	21.000	最大值	34.000	最大值	33.000
求和	73.000	求和	122.000	求和	100.000
观测数	5.000	观测数	5.000	观测数	5.000
最大(1)	21.000	最大(1)	34.000	最大(1)	33.000
最小(1)	12.000	最小(1)	13.000	最小(1)	11.000
置信度(95.0%)	4.613	置信度(95.0%)	10.336	置信度(95.0%)	10.536

六、EXCEL求数据之间的相关系数计算与思考

WORD里面数据处理的相关系数，对于数据而言其实是线性相关的系数。如

编号	Cu含量	Zn含量
S01	63.65	175.02
S02	52.38	112.15
S03	136.96	226.12
S04	77.26	188.34
S05	54.93	72.42
S06	155.69	143.4
S07	69.09	172.02
S08	88.01	211.3
S09	62.07	152.4
S10	76.42	172.19

这是百度知道求问的数据相关关系，数据的相关其实是分析的第一步，接下来再考察其它之间的关系的必有步骤。有两三种方法均可以得到相关系数。

方法一、用CORREL函数

选中数据——插入函数——统计——CORREL函数，输入数组1与数组2，确定即可以看到两组数据的关系已经显示了。上例为0.4183834

0.4183834	Cu含量	Zn含量
S01	63.65	175.02
S02	52.38	112.15
S03	136.96	226.12
S04	77.26	188.34
S05	54.93	72.42
S06	155.69	143.4
S07	69.09	172.02
S08	88.01	211.3
S09	62.07	152.4
S10	76.42	172.19

二、插入图表——XY散点—…—完成——图表中选中趋势线为线性（假定为线性），选项——显示公式化，选得到方程。y = 0.5428x + 117.13 ，R2 = 0.175

这里可以看到描述为线性时R2 = 0.175，正好为0.4183834的平方值（在线性相关的计算中是这样的，p是度量随机变量X与Y之间线性相关密切程度的数字特征，而R2）。

解释的意思是，数据的此线性方程可以解释数据的17.5%。

三、工具——数据分析——相关系数，选好区域后得到为0.4183834

	列 1	列 2
列 1	1
列 2	0.4183834	1

上面的相关系数的算法，但是线性相关的一种表示。默认下都是线性计算而来。虽然得到了线性的相关系数，可一般情况下并不是很好的描述。还可能是幕指数，对数，或者自定义的等等其它关系。这时用到的方法还是找其中的有效关系。上例的0.4183834的解释及的图象均可以看出线性拟拿关系并不好。

而很多数据在非线性模式下，他们的相关系数是很好的。如

目数粒度对照表

目数	粒度um
5	3900
10	2000
15	1190
20	840
25	710
30	590
35	500
40	420
45	350
50	297
60	250
80	178

通过插入图表——XY散点—…—完成——图表中选中趋势线为乘幕布，选项——显示公式化，选得到方程。y = 239x^(-1.0975)，且R2 = 0.9981

很明显R2解释数据的此线性方程可以解释数据的99.8%。而工具——数据分析——相关系数，选好区域后得到为-0.567352682.

	目数	粒度um
目数	1
粒度um	-0.720934848	1

——————可见，最重要的最难的是寻找到一个合适的解释模型。

注区分：协方差的统计与相关系数的方法相似，统计结果同样返回一个输出表和一个矩阵，分别表示每对测量值变量之间的相关系数和协方差。不同之处在于相关系数的取值在 -1 和 +1 之间，而协方差没有限定的取值范围。相关系数和协方差都是描述两个变量离散程度的指标。

在EXCEL中如何实现四舍六入五留双

=ROUND(A1，2)-(MOD(A1*10^3，20)=5)*10^(-2) 保留当数点后二位

=ROUND(A1，3)-(MOD(A1*10^4，20)=5)*10^(-3) 保留当数点后二位

………………

解释：ROUND(A1，2)意思是对A1中的数取小数点后二位，且第三位遵守四舍五入的规律。这样的一个效果就是五入的时候，可能在第二位为奇或者偶数的时候进了一位，而在在第二位为偶数的时候进了一位是不正确的，有必要减去这个0.01。

(MOD(A1*10^3，20)=5)*10^(-2)当A1*1000后（保证第三位计入）除以20的模（MOD）等于五时，这样得减去0.01。这里只能是除于20才能保证奇偶数的不同。