Excel在统计分析中的应用

1、1 1一、计算机准备二、分析工具库提供的统计分析方法三、数据基本信息计算四、图表制作五、假设检验（一）t检验1.成对2.成组（二）U检验（z检验）六、方差分析（一）单因素 （二）两因素 1、无重复 2、有重复七、回归与相关ExcelExcel在统计分析中应用在统计分析中应用2一、计算机准备一、计算机准备1 1、启动、启动Excel Excel ， 检查检查“工具工具”菜单中是否有“数据分析数据分析”命令。如果没有发现“数据分析”命令，就表示未加载“分析工具库”。2 2、加载、加载“分析工具库分析工具库”。步骤步骤： 工具工具 加载宏加载宏 分析工具库分析工具库 确定确定 工具工具 数据分析数据

2、分析 进入统计分析功能进入统计分析功能 3 单向分类资料的方差分析 两向分组单独观测值试验资料的方差分析 两向分组有重复观测值资料的方差分析 描述性统计量计算 二总体方差的同质性检验 编制次数分布表及绘制统计图分析工具库提供的统计分析方法分析工具库提供的统计分析方法4 排位与百分比排位 两样本平均数u检验 配对资料的t检验 等方差非配对资料的t检验 异方差非配对资料的t检验 一元线性回归与多元线性回归分析 计算多个变量两两之间的相关系数及协方差 进行随机和顺序抽样5三、数据基本信息计算三、数据基本信息计算（一）统计分析的粘贴函数AVERAGE 计算算术平均值BINOMDIST 计算二项式分布的

3、概率值CHIDIST 计算特定2分布的单尾概率值CHIINV 计算一定单尾概率值时的2临界值CHITEST 计算独立性检验的2值CONFIDENCE 计算总体平均值的置信区间CORREL 计算两组数据的相关系数COVAR 计算两组数据的协方差6FDIST 计算特定 F 分布的单尾概率值FINV 计算一定概率时的临界 F 值FTEST 计算二个样本方差之比 F 值的概率GEOMEAN 计算几何平均数HARMEAN 计算调和平均数INTERCEPT 计算直线回归的截距MAX 计算最大值MEDIAN 计算一组给定数字的中位数MIN 计算最小值7MODE 计算一组数据的众数NORMDIST 计算正态分

4、布的累积函数NORMINV 计算正态分布累积函数的逆函数NORMSDIST 计算标准正态分布的累积函数NORMSINV 计算标准正态分布累积函数的逆函数POISSON 计算泊松分布的概率SLOPE 计算给定数据的直线回归系数STDEV 计算样本标准差STDEVP 计算样本总体的标准差8TDIST 计算学生氏-t 分布的概率值TINV 计算特定概率时学生氏-t 分布的临界t值TTEST 计算t 检验时的学生氏-t检验相关的概率VAR 计算样本的方差VARP 计算样本总体的方差ZTEST 计算Z检验的双尾概率值9 9（二）利用粘贴函数计算方法例举1、算术平均数算术平均数。=AVERAGE(数值区域

5、)2、几何平均数几何平均数。=GEOMEAN(数据区域)3、中数中数。=MEDIAN(数据区域)4、极差极差。最大值=MAX(数据区域)；最小值=MIN(数据 区域)。极差=最大值-最小值5、方差方差。=VARP(数据区域)6、标准差标准差。=STDEVP(数据区域)7、变异系数变异系数。标准差/算术平均数10（三）描述统计 Descriptive Statistics 1313四、图表制作四、图表制作1、输入原始数据和各组下限【 可用功能：=MIN( ) =MAX( ) 】2、工具工具 数据分析数据分析 直方图直方图3、输入区域：原始数据 接受区域：各组下限 输出区域：图标位置 图表输出图表

6、输出4、设定图表选项：图表类型图表类型；表题；坐标；选项等14342.1342.1340.7340.7348.4348.4346.0346.0343.4343.4342.7342.7346.0346.0341.1341.1344.0344.0348.0348.0346.3346.3346.0346.0340.3340.3344.2344.2342.2342.2344.1344.1345.0345.0340.5340.5344.2344.2344.0344.0343.5343.5344.2344.2342.6342.6343.7343.7345.5345.5339.3339.3350.2350.

7、2337.3337.3345.3345.3358.2358.2344.2344.2345.8345.8331.2331.2342.1342.1342.4342.4340.5340.5350.0350.0343.2343.2347.0347.0340.2340.2344.0344.0353.3353.3340.2340.2336.3336.3348.9348.9340.2340.2356.1356.1346.0346.0345.6345.6346.2346.2340.6340.6339.7339.7342.3342.3352.8352.8342.6342.6350.3350.3348.5348.

8、5344.0344.0350.0350.0335.1335.1340.3340.3338.2338.2345.5345.5345.6345.6349.0349.0336.7336.7342.0342.0338.4338.4343.9343.9343.7343.7341.1341.1347.1347.1342.5342.5350.0350.0343.5343.5345.6345.6345.0345.0348.6348.6344.2344.2341.1341.1346.8346.8350.2350.2339.9339.9346.6346.6339.9339.9344.3344.3346.2346.

9、2338.0338.0341.1341.1347.3347.3347.2347.2339.8339.8344.4344.4347.2347.2341.0341.0341.0341.0343.3343.3342.3342.3339.5339.5343.0343.0表表41 100听罐头样品的净重（听罐头样品的净重（g）练习：教材练习：教材P20【例例2-1】做直方图做直方图15181829291919242422221919242422222222202023232020212123232121262622222323242422222323242425252424222224242323242

10、4222225252323252526262323222225252323202022222525262625252626262625252626242423232121262621212323222224242424212123232424242421212222232320202222232326262323242422222424262628282424272723232424222226262323202026262525252526262525252526262525242422222525262625252424252526262525252527272828 表表4-2 100盒

11、鲜枣每盒检出不合格枣数盒鲜枣每盒检出不合格枣数练习：教材练习：教材P22表表2-4做柱形图做柱形图16【例5-1】为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随机选取12位自愿者进行试验，服药后，间隔1个疗程，分别测其12位自愿者的体重见下表，假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。自愿者编号123456789101112服药前体重x1331451001561271221421101361661381101个疗程后体重y120122105133108110135105122145128108五、

12、假设检验五、假设检验 Hypothesis Testing(一)t检验 1、成对数据资料的平均数t检验 1717工具工具 数据分析数据分析 t-检验：平均值的成检验：平均值的成对二样本分析对二样本分析变量1区域：B2:B14变量2区域：C2:C14假设平均值：0标志： ：0.05 输出区域：D10 确定确定1819105815. 374566402. 401. 0tt结果分析结果分析: 所以否定H0 ，接受HA 即：服用新型减肥药剂的自愿者体重有极显著差异。 因此可得出结论：这种新型减肥药剂对人具有减肥作用，效果极显著。练习：教材练习：教材P84P84【例例4-84-8】23 【例5-2】某番

13、茄品种分别按3200株/667m2种植，各调查5个小区，得产量见下表，试测验两种密度的小麦产量有无显著差异。x1x2500530535550525545520555550520232、成组数据资料的平均数t检验 2424工具工具 数据分析数据分析 t t 检验：双样本等方检验：双样本等方差检验差检验(t (t 检验：双样本异方差检验检验：双样本异方差检验) )变量1区域：A1:A6变量2区域：B1:C6假设平均值：0标志： ：0.05 确定确定2525t-检验: 双样本等方差假设x1x2平均526540方差342.5212.5观测值55合并方差277.5假设平均差0df8t Stat-1.32

14、88212P(T=t) 单尾0.1102784t 单尾临界1.85954803P(T=t) 双尾0.2205568t 双尾临界2.30600413结果分析：结果分析：t=1.33 t0.05=2.31tt0.05接受Ho即：两种密度小区产量无差异。练习：教材练习：教材P80【例例4-5】26【例5-3】现有两种茶多糖提取工艺，分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量，结果见表4。问两种工艺的粗提物中茶多糖含量有无显著差异？ 表5-3 两种工艺粗提物中茶多糖含量测定结果超 滤 法 （ x2) 29.32 28.15 28.00 28.58 29.00 醇 沉 淀 法 （ x1

15、） 27.52 27.78 28.03 28.88 28.75 27.94 F F检验检验利用Excel 提供的“F检验：双样本方差”工具进行检验。27（2）利用Excel 提供的“F检验：双样本方差”工具进行检验。28结果分析：由F检验结果可得，F1.04430430 Z 检验（u检验）： 教材P79【例4-4】某食品厂在甲乙两条生产线上各测了30个日产量如下表，试检验两条生产线的平均日产量有无显著差异？甲生产线（kg）（x1）747156547178625762697363617262707874776554586362596278536770乙生产线（kg）（x1）65535460566

16、9584951536662585866715356607065585669687052555557先用粘贴函数=VAR(数值)计算方差输入格式单行或单列输入格式单行或单列将方差数值代入 用z-检验58.226.301.0uu推断推断结论结论36六、方差分析六、方差分析 Analysis of VarianceAnalysis of Variance（一）单因素试验方差分析【例6-1】以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产。在生产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行选择。今选用5种除杂方法，每种方法做4次试验，试验结果见表5，试分析不同除杂方法的除杂效

17、果有无差异？设各总体服从正态分布，且方差相等。除杂方法（Ai）除杂量（xij）A125.624.425.025.9A227.827.027.028.0A327.027.727.525.9A429.027.327.529.9A520.621.222.021.2教材教材P103【例例5-1】3737工具工具 数据分析数据分析 方差分析：单因素方方差分析：单因素方差分析差分析输入区域：A3:E7分组方式：行标志位于第1列： ：0.01 确定确定44451、两因素无重复试验的方差分析【例6-2】某厂现有化验员3人，担任该厂牛奶酸度（T）的检验。每天从牛奶中抽样一次进行检验，连续10天的检验分析结果见下

18、表。试分析3名化验员的化验技术有无差异，以及每天的原料牛奶酸度有无差异。化验员B1B2B3B4B5B6B7B8B9B10A111.7110.8112.3912.5610.6413.2613.3412.6711.2712.68A211.7810.712.512.3510.3212.9313.8112.4811.612.65A311.6110.7512.412.4110.7213.113.5812.8811.4612.94（二）两因素试验方差分析教材教材P128【例例5-4】4646工具工具 数据分析数据分析 方差分析：无重复双方差分析：无重复双因素因素输入区域：A1:D5标志： ：0.01 确定

19、确定继续手工完成结果推断和多重比较过程52表6-7 3种肥料施于3种土壤的小麦产量（g)肥料种类(A)盆土壤种类(B)B1(油砂)B2(二合)B2(白僵)A1121.419.617.6221.218.816.6320.116.417.5A2112.013.013.3214.213.714.0312.112.013.9A3112.814.212.0213.813.614.6313.713.314.052 【例6-6】施用A1、A2、A3 3种肥料于B1、B2、B3 3种土壤，以小麦为指示作物，每处理组合种3盆，得产量结果(g)于表6-7。试作方差分析。2、两因素有重复试验的方差分析 53(A)

20、(A) B B1 1( (油砂油砂) ) B B2 2( (二合二合) ) B B2 2( (白僵白僵) ) A A1 1 21.421.419.619.617.617.621.221.218.818.816.616.620.120.116.416.417.517.5A A2 2 1212131313.313.314.214.213.713.7141412.112.1121213.913.9A A3 3 12.812.814.214.2121213.813.813.613.614.614.613.713.713.313.31414输入格式5454工具工具 数据分析数据分析 方差分析：可重复双方

21、差分析：可重复双因素因素输入区域：A1:D10每一样本的行数：3 ：0.05 确定确定60 3种肥料3种土壤小麦产量的方差分析表变异来源SSdfMSFF0.05F0.01肥料(A)间179.38289.6996.673.556.01土类(B)间3.9621.982.133.556.01肥料A土类B19.2444.815.182.934.58误差16.70180.93总变异219.2826整理后整理后练习：教材练习：教材P136【例例5-5】继续手工完成结果推断和多重比较过程6161 【例6-4】设有A、B、C、D、E这5个大豆品种，其中E为对照，进行大区比较试验，成熟后分别在5块地随机抽取4个样点，每点产量（kg）列入下表，试对A、B、C、D四个品种做产量比较。表 大豆品比试验结果品种取 样 点1234A23212421B21191818C22232220D19201918E1516161762方差分析差异源SSdfMSFP-valueF crit组间101.3425