Excel在统计分析中的应用.ppt

1 1 一、计算机准备 二、分析工具库提供的统计分析方法 三、数据基本信息计算 四、图表制作 五、假设检验（一）t检验1.成对2.成组（二）U检验（z检验） 六、方差分析（一）单因素 （二）两因素 1、无重复 2、有重复 七、回归与相关 Excel在统计分析中应用 2 一、计算机准备 1、启动Excel ， 检查“工具”菜单中是否有“ 数据分析”命令。如果没有发现“数据分析”命令， 就表示未加载“分析工具库”。 2、加载“分析工具库”。步骤： 工具 加载宏 分析工具库 确定 工具 数据分析 进入统计分析功能 3 单向分类资料的方差分析 两向分组单独观测值试验资料的方差分析 两向分组有重复观测值资料的方差分析 描述性统计量计算 二总体方差的同质性检验 编制次数分布表及绘制统计图 二、二、分析工具库提供的统计分析方法 4 排位与百分比排位 两样本平均数u检验 配对资料的t检验 等方差非配对资料的t检验 异方差非配对资料的t检验 一元线性回归与多元线性回归分析 计算多个变量两两之间的相关系数及协方差 进行随机和顺序抽样 5 三、数据基本信息计算 （一）统计分析的粘贴函数 AVERAGE 计算算术平均值 BINOMDIST 计算二项式分布的概率值 CHIDIST 计算特定2分布的单尾概率值 CHIINV 计算一定单尾概率值时的2临界值 CHITEST 计算独立性检验的2值 CONFIDENCE 计算总体平均值的置信区间 CORREL 计算两组数据的相关系数 COVAR 计算两组数据的协方差 6 FDIST 计算特定 F 分布的单尾概率值 FINV 计算一定概率时的临界 F 值 FTEST 计算二个样本方差之比 F 值的概率 GEOMEAN 计算几何平均数 HARMEAN 计算调和平均数 INTERCEPT 计算直线回归的截距 MAX 计算最大值 MEDIAN 计算一组给定数字的中位数 MIN 计算最小值 7 MODE 计算一组数据的众数 NORMDIST 计算正态分布的累积函数 NORMINV 计算正态分布累积函数的逆函数 NORMSDIST 计算标准正态分布的累积函数 NORMSINV 计算标准正态分布累积函数的逆函数 POISSON 计算泊松分布的概率 SLOPE 计算给定数据的直线回归系数 STDEV 计算样本标准差 STDEVP 计算样本总体的标准差 8 TDIST 计算学生氏-t 分布的概率值 TINV 计算特定概率时学生氏-t 分布的临界t值 TTEST 计算t 检验时的学生氏-t检验相关的概率 VAR 计算样本的方差 VARP 计算样本总体的方差 ZTEST 计算Z检验的双尾概率值 9 9 （二）利用粘贴函数计算方法例举 1、算术平均数。=AVERAGE(数值区域) 2、几何平均数。=GEOMEAN(数据区域) 3、中数。=MEDIAN(数据区域) 4、极差。最大值=MAX(数据区域)；最小值=MIN(数据 区域)。极差=最大值-最小值 5、方差。=VARP(数据区域) 6、标准差。=STDEVP(数据区域) 7、变异系数。标准差/算术平均数 10 （三）描述统计 Descriptive Statistics 1313 四、图表制作 1、输入原始数据和各组下限【 可用功能：=MIN( ) =MAX( ) 】 2、工具 数据分析 直方图 3、输入区域：原始数据 接受区域：各组下限 输出区域：图标位置 图表输出 4、设定图表选项：图表类型；表题；坐标；选项等 14 342.1340.7348.4346.0343.4342.7346.0341.1344.0348.0 346.3346.0340.3344.2342.2344.1345.0340.5344.2344.0 343.5344.2342.6343.7345.5339.3350.2337.3345.3358.2 344.2345.8331.2342.1342.4340.5350.0343.2347.0340.2 344.0353.3340.2336.3348.9340.2356.1346.0345.6346.2 340.6339.7342.3352.8342.6350.3348.5344.0350.0335.1 340.3338.2345.5345.6349.0336.7342.0338.4343.9343.7 341.1347.1342.5350.0343.5345.6345.0348.6344.2341.1 346.8350.2339.9346.6339.9344.3346.2338.0341.1347.3 347.2339.8344.4347.2341.0341.0343.3342.3339.5343.0 表41 100听罐头样品的净重（g） 15 18291924221924222220 23202123212622232422 23242524222423242225 23252623222523202225 26252626252624232126 21232224242123242421 22232022232623242224 26282427232422262320 26252526252526252422 25262524252625252728 表4-2 100盒鲜枣每盒检出不合格枣数 16 【例5-1】为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随 机选取12位自愿者进行试验，服药后，间隔1个疗程，分别测其12位自愿者 的体重见下表，假设服药前后，除服用此减肥药剂外，其余的生活方式、生 活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有 显著减肥作用。 自愿者编号123456789101112 服药前体重x133145100156127122142110136166138110 1个疗程后体重y120122105133108110135105122145128108 五、假设检验 Hypothesis Testing (一)t检验 1、成对数据资料的平均数t检验 1717 工具 数据分析 t-检验：平均值的成 对二样本分析 变量1区域：B2:B14 变量2区域：C2:C14 假设平均值：0 标志： ：0.05 输出区域：D10 确定 18 19 结果分析: 所以否定H0 ，接受HA 即：服用新型减肥药剂的自愿者体重有极显著差异。 因此可得出结论：这种新型减肥药剂对人具有减肥作 用，效果极显著。 23 【例5-2】某番茄品种分别按3200株/667m2种植，各调查5 个小区，得产量见下表，试测验两种密度的小麦产量有无显著 差异。 x1x2 500530 535550 525545 520555 550520 23 2、成组数据资料的平均数t检验 2424 工具 数据分析 t 检验：双样本等方 差检验(t 检验：双样本异方差检验) 变量1区域：A1:A6 变量2区域：B1:C6 假设平均值：0 标志： ：0.05 确定 2525 t-检验: 双样本等方差假设 x1x2 平均526540 方差342.5212.5 观测值55 合并方差277.5 假设平均差0 df8 t Stat-1.3288212 P(T30 Z 检验（u检验）： 教材P79【例4-4】某食品厂在甲乙两条生产线上各测了30 个日产量如下表，试检验两条生产线的平均日产量有无显著 差异？ 甲生产线（kg）（x1） 747156547178 625762697363 617262707874 776554586362 596278536770 乙生产线（kg）（x1） 655354605669 584951536662 585866715356 607065585669 687052555557 先用粘贴函数 =VAR(数值)计 算方差 输入格式单行或单列 将方差数 值代入 用z-检验 推断结论 36 六、方差分析 Analysis of Variance （一）单因素试验方差分析 【例6-1】以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产 。在生产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行 选择。今选用5种除杂方法，每种方法做4次试验，试验结果见表5，试分析不同 除杂方法的除杂效果有无差异？设各总体服从正态分布，且方差相等。 除杂方法（Ai）除杂量（xij） A125.624.425.025.9 A227.827.027.028.0 A327.027.727.525.9 A429.027.327.529.9 A520.621.222.021.2 3737 工具 数据分析 方差分析：单因素方 差分析 输入区域：A3:E7 分组方式：行 标志位于第1列： ：0.01 确定 44 注意注意：对照具体事例，汇总显著水平对照具体事例，汇总显著水平0.050.05和和0.010.01两次计两次计 算结果，先将方差分析表做具体化修改完善。算结果，先将方差分析表做具体化修改完善。 F F测验：测验： F F =49.55=49.55 F F 0.010.01（4 4，1515） 4.894.89，所以，在显著，所以，在显著 性水平性水平0.010.01下拒绝原假设下拒绝原假设 H H0 0 ，认为，认为5 5种不同除杂方法的除种不同除杂方法的除 杂效果有极显著差异。杂效果有极显著差异。 但哪几个方法差异显著，哪几个方法差异不显著，尚但哪几个方法差异显著，哪几个方法差异不显著，尚 需进一步进行多重比较分析，但需进一步进行多重比较分析，但ExcelExcel分析工具尚不能自分析工具尚不能自 动完成，无法完成最后结论表述，需手工完成多重比较。动完成，无法完成最后结论表述，需手工完成多重比较。 45 1、两因素无重复试验的方差分析 【例6-2】某厂现有化验员3人，担任该厂牛奶酸度（T）的 检验。每天从牛奶中抽样一次进行检验，连续10天的检验分析 结果见下表。试分析3名化验员的化验技术有无差异，以及每 天的原料牛奶酸度有无差异。 化验员 B1B2B3B4B5B6B7B8B9B10 A111.7110.8112.3912.5610.6413.2613.3412.6711.2712.68 A211.7810.712.512.3510.3212.9313.8112.4811.612.65 A311.6110.7512.412.4110.7213.113.5812.8811.4612.94 （二）两因素试验方差分析 4646 工具 数据分析 方差分析：无重复双 因素 输入区域：A1:D5 标志： ：0.01 确定 继续手工完成结果推断和多重比较过程 52 表6-7 3种肥料施于3种土壤的小麦产量（g) 肥料种类 (A) 盆 土壤种类(B) B1(油砂)B2(二合)B2(白僵) A1 121.419.617.6 221.218.816.6 320.116.417.5 A2 112.013.013.3 214.213.714.0 312.112.013.9 A3 112.814.212.0 213.813.614.6 313.713.314.0 52 【例6-6】施用A1、A2、A3 3种肥料于B1、B2、B3 3种土壤，以小麦为指示作物 ，每处理组合种3盆，得产量结果(g)于表6-7。试作方差分析。 2、两因素有重复试验的方差分析 53 (A) B1(油砂) B2(二合) B2(白僵) A1 21.419.617.6 21.218.816.6 20.116.417.5 A2 121313.3 14.213.714 12.11213.9 A3 12.814.212 13.813.614.6 13.713.314 输 入 格 式 5454 工具 数据分析 方差分析：可重复双 因素 输入区域：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 整理后 继续手工完成结果推断和多重比较过程 6161 【例6-4】设有A、B、C、D、E这5个大豆品种，其 中E为对照，进行大区比较试验，成熟后分别在5块地 随机抽取4个样点，每点产量（kg）列入下表，试对A 、B、C、D四个品种做产量比较。 表 大豆品比试验结果 品种取 样 点 1234 A23212421 B21191818 C22232220 D19201918 E15161617 注意：方差分析表要对照试验做具体化修改完善注意：方差分析表要对照试验做具体化修改完善。 62 方差分析 差异源SSdfMSFP-valueF crit 组间101.3425.325 17.66861.52E-05 3.055568 组内21.515 1.433333 总计122.819 大豆品比试验方差分析表 差异源SSdfMSFF0.05F0.01 品种间101.3425.317.73.14.9 品种内（ 误差）