Excel在统计分析中的应用(全113页)

Excel统计分析 第一章 统计基础与数据描述 第二章 描述性统计 第三章 数据库统计函数 第四章 统计指数提纲 例1.1：上证180指数的月收益率统计基础与数据描述上证180指数时间指数收益率2002/12,828.952002/22,869.650.0143872002/32,941.430.0250142002/43,028.490.0295982002/52,773.89-0.084072002/63,299.060.189326C4=(B4-B3)/B3 例1.2 COUNTIF函数：计算区域内满足给定条件的单元格的个数 COUNTIF(Range,Criteria)，Range表示区域,Criteria表示条件统计基础与数据描述上证180按指数分组组号上证180累积频率观测值数目12400.01122400.0-2500.04332500.0-2600.09542600.0-2700.012352700.0-2800.016462800.0-2900.025972900.0-3000.028383000.0-3100.031393100.0-3200.0343103200.0-3300.0362C3=COUNTIF(例例1.1!B3:B38,=2400)D3=C3D4=C4-C3 例1.3 Frequency函数：返回区域内数据的频率分布 Frequency(data_array,bins_array)，data_array统计基础与数据描述上证180指数按收益率分组应用FREQUENCY函数间隔观测值数目-0.120-0.083-0.049060.04100.0850.1211合计35B5=-0.12 等差序列，步长等差序列，步长0.04，终止值，终止值=0.12C5=FREQUENCY(例例1.1!C4:C38,B5:B11)C13=SUM(C5:C12)例1.4 频率分布图统计基础与数据描述上证180按指数分组组号上证180观测值数目12400.0122400.0-2500.0332500.0-2600.0542600.0-2700.0352700.0-2800.0462800.0-2900.0972900.0-3000.0383000.0-3100.0393100.0-3200.03103200.0-3300.020123456789102400.02400.0-2500.02500.0-2600.02600.0-2700.02700.0-2800.02800.0-2900.02900.0-3000.03000.0-3100.03100.0-3200.03200.0-3300.0频率指数上证180按指数分组频率图 例1.5 相对频率分布图统计基础与数据描述上证180按指数分组组号上证180观测值数目 相对频率12400.010.027777822400.0-2500.030.083333332500.0-2600.050.138888942600.0-2700.030.083333352700.0-2800.040.111111162800.0-2900.090.2572900.0-3000.030.083333383000.0-3100.030.083333393100.0-3200.030.0833333103200.0-3300.020.05555563600.050.10.150.20.250.32400.02400.0-2500.02500.0-2600.02600.0-2700.02700.0-2800.02800.0-2900.02900.0-3000.03000.0-3100.03100.0-3200.03200.0-3300.0相对频率指数值上证180按指数分组的相对频率图D3=C3/$C$13 例1.6 累积频率分布图统计基础与数据描述上证180按指数分组组号指数值累积频率12400.0122400.0-2500.0432500.0-2600.0942600.0-2700.01252700.0-2800.01662800.0-2900.02572900.0-3000.02883000.0-3100.03193100.0-3200.034103200.0-3300.03605101520253035402400.02400.0-2500.02500.0-2600.02600.0-2700.02700.0-2800.02800.0-2900.02900.0-3000.03000.0-3100.03100.0-3200.03200.0-3300.0累积频率指数值上证180按指数分组累积曲线 例1.7 直方图统计基础与数据描述0.00%20.00%40.00%60.00%80.00%100.00%024681012-0.12-0.08-0.0400.040.080.120.160.2其他频率接收直方图频率累积%接收频率累积%-0.1200.00%-0.0838.57%-0.04934.29%0651.43%0.041080.00%0.08594.29%0.12197.14%0.16097.14%0.21100.00%其他0100.00%中心趋势 离中趋势 偏度 峰度描述性统计中心趋势：算术平均值NXNXXXXN21股票价格算术平均值代码证券名价格600000浦发银行7.24600004白云机场8.54600006东风汽车2.94600008首创股份8.2600009上海机场16.65600011华能国际6.97600015华夏银行4.05600016民生银行5.69600018上港集箱16.37600019宝钢股份6.17求和82.82算术平均值8.282股票价格算术平均值代码证券名价格600000浦发银行7.24600004白云机场8.54600006东风汽车2.94600008首创股份8.2600009上海机场16.65600011华能国际6.97600015华夏银行4.05600016民生银行5.69600018上港集箱16.37600019宝钢股份6.17算术平均值8.282C13=SUM(C3:C12)C14=C13/10C13=AVERAGE(C3:C12)中心趋势：几何平均值年的增长率为第irrXXXXXiiimmg,1121第一年投资第一年投资100元，亏损元，亏损50元，收益率元，收益率=(50-100)/100=-50%第二年投资第二年投资50元，盈利元，盈利50元，收益率元，收益率=(100-50)/50=100%两年平均收益率两年平均收益率=(-50%+100%)/2=25%上证180指数收益率几何平均值时间指数值收益率r1+r2003/122828.82004/13019.040.067251131.0672512004/23140.530.040241271.0402412004/33213.480.023228561.0232292004/42912.81-0.09356520.9064352004/52819.49-0.03203780.9679622004/62528.44-0.10322790.8967722004/72548.490.007929791.007932004/82479.16-0.02720430.9727962004/925910.045112051.0451122004/102452.73-0.05336550.9466352004/112490.280.015309471.0153092004/122362.07-0.05148420.948516采用GEOMEAN函数求几何平均值-0.01491中心趋势：几何平均值D16=GEOMEAN(D4:D15)-1 众数:出现频率最高的数中心趋势：众数年龄的众数20201921212018232022192121182121众数21B10=MODE(A2:B9)中位数:居中的数中心趋势：中位数非组数据的中位数812912101310141115111611181119中位数11.5B10=MEDIAN(A2:B9)调和平均数:数的倒数的算术平均值的倒数中心趋势：调和平均数XNH1求调和平均数X1/X50.20 80.13 120.08 160.06 170.06 90.11 200.05 调和平均10.13 B3=1/A3B10=1/SUM(B3:B9)离中趋势：方差1)(22nXxi日期价格200503077.65 200503087.78 200503097.57 200503107.45 200503117.41 200503147.37 200503157.16 200503167.16 200503177.13 200503187.19 样本方差0.052201 B13=VAR(B3:B12)离中趋势：标准差1)(2nXxi日期价格200503077.65 200503087.78 200503097.57 200503107.45 200503117.41 200503147.37 200503157.16 200503167.16 200503177.13 200503187.19 样本标准差0.228476 B13=STDEV(B3:B12)离中趋势：四分位数B13=QUARTILE(B3:B12,1)B14=QUARTILE(B3:B12,3)四分位数：位于25%和75%位置的数称为低四分位数和高四分位数,刻画数据相对于中位数的离散程度日期价格200503077.65 200503087.78 200503097.57 200503107.45 200503117.41 200503147.37 200503157.16 200503167.16 200503177.13 200503187.19 Q17.17 Q37.54 QUARTILE(Array,Quart)Quart=0,min1,低四分位数低四分位数2,中位数中位数3,高四分位数高四分位数4,max离中趋势：方差系数XCVB15=AVERAGE(B3:B14)B16=STDEV(B3:B14)C15=AVERAGE(C3:C14)C16=STDEV(C3:C14)B17=B16/B15C17=C16/C15 方差系数：标准差与均值之间的比值。消除了量纲影响采用方差系数度量分散程度日期东风汽车上海机场200503103.1716.06200503113.1616.55200503143.117.27200503153.116.82200503163.0916.6200503173.0216.65200503182.9716.52200503212.9416.65200503222.7117.17200503232.7416.9200503242.7616.86200503252.7516.79平均值2.96 16.74 标准差0.1756270.3158346方差系数0.0594 0.0189 偏度：偏斜度3)()2)(1(sxxnnni偏斜度股价偏斜度日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4偏斜度-0.9236 B21=SKEW(B3:B20)偏度：四分位数偏度系数)(2)()()(13213131223QQQQQQQQQQQ日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4Q15.93 Q26.10 Q36.34 四分位偏度系数0.1677019B21=QUARTILE(B3:B20,1)B22=QUARTILE(B3:B20,2)B23=QUARTILE(B3:B20,3)B24=(B23+B21-2*B22)/(B23-B21)偏度：Spearman偏度系数标准差平均值中位数）偏度系数(3Spearman日期价格200503016.4200503026.38200503036.44200503046.36200503076.24200503086.35200503096.29200503106.16200503116.12200503146.08200503155.99200503165.93200503175.97200503185.93200503215.94200503225.54200503235.36200503245.4平均值6.05 标准差0.333500 中位数6.1Spearman偏度系数-0.46 B21=AVERAGE(B3:B20)B22=STDEV(B3:B20)B23=MEDIAN(B3:B20)B24=3*(B21-B23)/B22峰度：峰值)3)(2()1(3)()3)(2)(1()1(24nnnsxxnnnnni峰值日期上证指数200503011,303.41200503021,287.45200503031,294.34200503041,287.71200503071,293.74200503081,318.27200503091,316.79200503101,286.23200503111,289.94200503141,293.50200503151,269.14200503161,255.59200503171,243.47200503181,227.40200503211,231.05200503221,206.92200503231,201.65200503241,208.19200503251,205.63峰值-1.36420044B22=KURT(B3:B21)峰度：矩峰度系数4241)(/)(nxxnxxii3，常峰态，正态分布，常峰态，正态分布3，高峰态，高峰态日期上证指数（Xi-X）2（Xi-X）4200503011,303.411534.87 2355814.24 200503021,287.45539.05 290570.80 200503031,294.34906.45 821658.19 200503041,287.71551.19 303806.92 200503071,293.74870.68 758092.01 200503081,318.272920.04 8526617.17 200503091,316.792762.28 7630174.09 200503101,286.23483.88 234143.94 200503111,289.94660.87 436747.56 200503141,293.50856.58 733727.33 200503151,269.1424.08 579.96 200503161,255.5974.70 5579.36 200503171,243.47431.09 185835.89 200503181,227.401356.64 1840479.55 200503211,231.051101.09 1212392.67 200503221,206.923284.74 10789502.01 200503231,201.653916.59 15339644.14 200503241,208.193140.78 9864477.36 200503251,205.633434.27 11794199.64 1,264.2328849.85 73124042.80 0.000001 B22=AVERAGE(B3:B21)C3=(B3-$B$22)2D3=(B3-$B$22)4C22=SUM(D3:D21)D23=D22/18/(C22/18)4Dfunctions(database,field,criteria)database：数据库单元格区域 field：字段 criteria：包含过滤条件的单元格区域DCOUNT,DCOUNTA,DSUM,DPRODUCT,DMAX,DMIN,DAVERAGE,DVAR,DSTDEV,DVARP,DSTDEVP,DGET数据库统计函数数据库方差、标准差函数利用DVAR和DSTDEV求样本方差标准差Gold 公司销售清单销售单号客户类型类别生产商型号数量单价总价销售员040500001个人台式电脑戴尔G2802900018000刘040500002单位台式电脑惠普H52031000030000王040500003个人笔记本惠普H32011500015000李040500004个人笔记本联想L2021200024000刘040500005单位台式电脑联想L325800040000王040500006单位台式电脑联想L322800016000孙040500006个人台式电脑戴尔G2805900045000王040500008个人笔记本戴尔G50021300026000李040500009单位台式电脑联想L322800016000李040500010个人笔记本戴尔G50061300078000王040500011单位台式电脑联想L325800040000刘040500012个人笔记本戴尔G50021300026000王040500013个人台式电脑惠普H52011000010000刘040500014单位笔记本惠普H32051500075000刘040500015单位台式电脑联想L326800048000刘总价样本方差25000 374250000=DVAR(A4:I19,H4,A21:A22)样本标准差19345.54212=DSTDEV(A4:I19,H4,A21:A22)统计指数：同等加权指数01PPIEp同等加权价格指数01QQIEq同等加权数量指数同等加权指数消费品价格（元）销量期初期末期初期末大米500540300330鸡蛋55.860006450盐77.3150155布匹8085600670电视机300031008083书2022500560同等加权消费价格指数 104.10%=SUM(C4:C9)/SUM(B4:B9)同等加权消费数量指数 108.10%=SUM(E4:E9)/SUM(D4:D9)统计指数：拉氏指数0001QPQPILp拉氏价格指数0010QPQPILq拉氏数量指数基期加权（拉氏）指数消费品价格（元）销量期初P0期末P1期初Q0期末Q1大米500540300330鸡蛋55.860006450盐77.3150155布匹8085600670电视机300031008083书2022500560P0*Q0P1*Q0P0*Q1大米150000=B4*D4162000=C4*D4165000=B4*E4鸡蛋300003480032250盐105010951085布匹480005100053600电视机240000248000249000书100001100011200479050=SUM(B12:B17)507895=SUM(D12:D17)512135=SUM(F12:F17)拉氏消费价格指数106.02%=D18/B18拉氏消费数量指数106.91%=F18/B18统计指数：帕氏指数1011QPQPIPp帕氏价格指数0111QPQPIPq拉氏数量指数现期加权（帕氏）指数消费品价格（元）销量期初P0期末P1期初Q0期末Q1大米500540300330鸡蛋55.860006450盐77.3150155布匹8085600670电视机300031008083书2022500560P1*Q0P0*Q1P1*Q1大米162000=C4*D4165000=B4*E4178200=C4*E4鸡蛋348003225037410盐109510851131.5布匹510005360056950电视机248000249000257300书110001120012320507895=SUM(B12:B17)512135=SUM(D12:D17)543311.5=SUM(F12:F17)帕氏消费价格指数106.97%=F18/B18帕氏消费数量指数106.09%=F18/D18统计指数：埃奇沃斯指数22100101QQPQQPIMp埃奇沃斯价格指数22100101PPQPPQIMq埃奇沃斯数量指数统计指数：费雪指数10110001QPQPQPQPIIIPLFp费雪价格指数01110010QPQPQPQPIIIPLFq费雪数量指数统计指数：资本加权指数0000101111111ISPSPSPSPInnnnC资本加权指数股票名称价格（元）发行在外的流通股股数(万股)期初P0期末P1期初S0期末S1A10.213.130003300B6.57.120003000C32.237.2600070000D13.515.725002650E9.81131003500F25.426.352007000G10.111.833003500H9.27.815001600I12.51428003000I0100P0*S0P1*S1 A30600=B4*D443230=C4*E4B1300021300C1932002604000D3375041605E3038038500F132080184100G3333041300H1380012480I3500042000515140=SUM(B16:B24)3028515=SUM(D16:D24)股票资本加权指数587.90=D25/B25*B14 某事件发生的概率为p，不发生的概率为q，则在N次实验中该事件发生X次的概率为又称伯努利分布BINOMDIST(number,trials,probability,cumulative)number：实验成功的次数trials：独立实验的次数probability：每次独立实验成功的概率cumulative：true返回累积分布，false返回密度函数概率分布：二项分布XNXXnXqpCP 股票上涨的概率为0.6，20个交易日中上涨5天的概率，以及上涨小于等于5天的概率概率分布：二项分布应用二项分布函数BINOMDIST求概率值N20X5p0.6上涨5天p(X)0.001294=BINOMDIST(C3,C2,C4,0)上涨小于5天F(X5)0.001612=BINOMDIST(C3,C2,C4,1)股票上涨的概率为0.6，求20个交易日中概率分布函数大于等于临界值0.75的最小天数CRITBINOM(试验次数,每次试验的成功概率,临界值)概率分布：二项分布应用二项分布函数CRITBINOM函数N20p0.60.75二项分布的分布函数值大于等于的最小X14=CRITBINOM(C2,C3,C4)在r次成功之前失败的次数X的概率(帕斯卡分布)NEGBINOMDIST(失败次数,成功次数,成功概率)灯泡合格概率0.8,求找到30个合格灯泡之前，需要测试10个不合格灯泡的概率概率分布：负二项分布krrkrXqpCP11应用负二项分布函数NEGBINOMDIST求概率值p0.8r30X10测试30个合格之前须测试10个不合格的概率0.08059=NEGBINOMDIST(C4,C3,C2)25(N)种股票，15(M)只沪市，10(N-M)只深市，从中选10(n)种，其中有5(x)只属于沪市的概率HYPGEODIST(成功次数,样本数,总样本成功次数,总样本数)概率分布：超几何分布NnMNxnxMXCCCNMnxhP),(应用超几何分布函数HYPGEOMDIST求概率N25M15n10 x510只股票中5只为上海交易所的概率0.231512=HYPGEOMDIST(E3,C3,E2,C2)随机事件发生的次数POISSON(事件数,期望值,返回的概率分布形式)平均每天呼叫20次，一天收到15次和小于等于15次呼叫的概率概率分布：泊松分布!XePXX20 x15收到15次呼叫的概率0.051649=POISSON(E2,C2,0)收到小于等于15此呼叫的概率0.156513=POISSON(E2,C2,1)股票收益率呈正态分布，均值为5%，标准差为2%，求收益率为4%对应的概率密度函数值概率分布：正态分布222/)(21),(xXeNf是标准差是均值，NORMDIST(x,均值均值,标准差标准差,返回的概率分布形式返回的概率分布形式)正态分布函数5.00%0.024%的收益率对应概率密度函数值17.60327=NORMDIST(0.04,C2,E2,0)小于等于4%的收益率概率0.308538=NORMDIST(0.04,C2,E2,1)股票收益率80%的可能性不超过某值，求这个值概率分布：正态分布NORMSINV(概率概率,均值均值,标准差标准差)正态分布函数5.00%0.0280%的概率下对应的临界值0.066832=NORMINV(0.8,C2,E2)概率分布：正态分布00.10.20.30.40.50.60.70.80.91-0.1-0.0500.050.10.150.2=0.01=0.02=0.03概率分布：正态分布0510152025-0.12-0.08-0.0400.040.080.120.160.2不同均值下正态分布概率密度函数图=0.03=0.05=0.074545一、计算机准备二、分析工具库提供的统计分析方法三、数据基本信息计算四、图表制作五、假设检验（一）t检验1.成对2.成组（二）U检验（z检验）六、方差分析（一）单因素 （二）两因素 1、无重复 2、有重复七、回归与相关ExcelExcel在统计分析中应用在统计分析中应用46一、计算机准备一、计算机准备1 1、启动、启动Excel Excel ，检查检查“工具工具”菜单中是否有“数据分析数据分析”命令。如果没有发现“数据分析”命令，就表示未加载“分析工具库”。2 2、加载、加载“分析工具库分析工具库”。步骤步骤：工具工具 加载宏加载宏 分析工具库分析工具库 确定确定 工具工具 数据分析数据分析 进入统计分析功能进入统计分析功能 47 单向分类资料的方差分析 两向分组单独观测值试验资料的方差分析 两向分组有重复观测值资料的方差分析 描述性统计量计算 二总体方差的同质性检验 编制次数分布表及绘制统计图分析工具库提供的统计分析方法分析工具库提供的统计分析方法48 排位与百分比排位 两样本平均数u检验 配对资料的t检验 等方差非配对资料的t检验 异方差非配对资料的t检验 一元线性回归与多元线性回归分析 计算多个变量两两之间的相关系数及协方差 进行随机和顺序抽样49三、数据基本信息计算三、数据基本信息计算（一）统计分析的粘贴函数AVERAGE 计算算术平均值BINOMDIST 计算二项式分布的概率值CHIDIST 计算特定2分布的单尾概率值CHIINV 计算一定单尾概率值时的2临界值CHITEST 计算独立性检验的2值CONFIDENCE 计算总体平均值的置信区间CORREL 计算两组数据的相关系数COVAR 计算两组数据的协方差50FDIST 计算特定 F 分布的单尾概率值FINV 计算一定概率时的临界 F 值FTEST 计算二个样本方差之比 F 值的概率GEOMEAN 计算几何平均数HARMEAN 计算调和平均数INTERCEPT 计算直线回归的截距MAX 计算最大值MEDIAN 计算一组给定数字的中位数MIN 计算最小值51MODE 计算一组数据的众数NORMDIST 计算正态分布的累积函数NORMINV 计算正态分布累积函数的逆函数NORMSDIST 计算标准正态分布的累积函数NORMSINV 计算标准正态分布累积函数的逆函数POISSON 计算泊松分布的概率SLOPE 计算给定数据的直线回归系数STDEV 计算样本标准差STDEVP 计算样本总体的标准差52TDIST 计算学生氏-t 分布的概率值TINV 计算特定概率时学生氏-t 分布的临界t值TTEST 计算t 检验时的学生氏-t检验相关的概率VAR 计算样本的方差VARP 计算样本总体的方差ZTEST 计算Z检验的双尾概率值5353（二）利用粘贴函数计算方法例举1、算术平均数算术平均数。=AVERAGE(数值区域)2、几何平均数几何平均数。=GEOMEAN(数据区域)3、中数中数。=MEDIAN(数据区域)4、极差极差。最大值=MAX(数据区域)；最小值=MIN(数据 区域)。极差=最大值-最小值5、方差方差。=VARP(数据区域)6、标准差标准差。=STDEVP(数据区域)7、变异系数变异系数。标准差/算术平均数54（三）描述统计 Descriptive Statistics 5757四、图表制作四、图表制作1、输入原始数据和各组下限【可用功能：=MIN()=MAX()】2、工具工具 数据分析数据分析 直方图直方图3、输入区域：原始数据 接受区域：各组下限 输出区域：图标位置 图表输出图表输出4、设定图表选项：图表类型图表类型；表题；坐标；选项等58342.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.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.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.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）591818292919192424222219192424222222222020232320202121232321212626222223232424222223232424252524242222242423232424222225252323252526262323222225252323202022222525262625252626262625252626242423232121262621212323222224242424212123232424242421212222232320202222232326262323242422222424262628282424272723232424222226262323202026262525252526262525252526262525242422222525262625252424252526262525252527272828 表表4-2 100盒鲜枣每盒检出不合格枣数盒鲜枣每盒检出不合格枣数60【例5-1】为了分析某种新型减肥药剂是否对人具有显著减肥作用，现随机选取12位自愿者进行试验，服药后，间隔1个疗程，分别测其12位自愿者的体重见下表，假设服药前后，除服用此减肥药剂外，其余的生活方式、生活条件均未变化。试问根据此试验结果，能否判断这种新型减肥药对人具有显著减肥作用。自愿者编号123456789101112服药前体重x1331451001561271221421101361661381101个疗程后体重y120122105133108110135105122145128108五、假设检验五、假设检验 Hypothesis Testing(一)t检验 1、成对数据资料的平均数t检验 6161工具工具 数据分析数据分析 t-检验：平均值的成检验：平均值的成对二样本分析对二样本分析变量1区域：B2:B14变量2区域：C2:C14假设平均值：0标志：0.05 输出区域：D10 确定确定6263105815.374566402.401.0tt结果分析结果分析:所以否定H0 ，接受HA 即：服用新型减肥药剂的自愿者体重有极显著差异。因此可得出结论：这种新型减肥药剂对人具有减肥作用，效果极显著。67 【例5-2】某番茄品种分别按3200株/667m2种植，各调查5个小区，得产量见下表，试测验两种密度的小麦产量有无显著差异。x1x2500530535550525545520555550520672、成组数据资料的平均数t检验 6868工具工具 数据分析数据分析 t t 检验：双样本等方检验：双样本等方差检验差检验(t(t 检验：双样本异方差检验检验：双样本异方差检验)变量1区域：A1:A6变量2区域：B1:C6假设平均值：0标志：0.05 确定确定6969t-检验:双样本等方差假设x1x2平均526540方差342.5212.5观测值55合并方差277.5假设平均差0df8t Stat-1.3288212P(T=t)单尾0.1102784t 单尾临界1.85954803P(T=t)双尾0.2205568t 双尾临界2.30600413结果分析：结果分析：t=1.33 t0.05=2.31tt0.05接受Ho即：两种密度小区产量无差异。70【例5-3】现有两种茶多糖提取工艺，分别从两种工艺中各取1个随机样本来测定其粗提物中的茶多糖含量，结果见表4。问两种工艺的粗提物中茶多糖含量有无显著差异？表5-3 两种工艺粗提物中茶多糖含量测定结果超 滤 法（x2)29.32 28.15 28.00 28.58 29.00 醇 沉 淀 法（x1）27.52 27.78 28.03 28.88 28.75 27.94 F F检验检验利用Excel 提供的“F检验：双样本方差”工具进行检验。71（2）利用Excel 提供的“F检验：双样本方差”工具进行检验。72结果分析：由F检验结果可得，F1.04430430 Z 检验（u检验）：教材P79【例4-4】某食品厂在甲乙两条生产线上各测了30个日产量如下表，试检验两条生产线的平均日产量有无显著差异？甲生产线（kg）（x1）747156547178625762697363617262707874776554586362596278536770乙生产线（kg）（x1）655354605669584951536662585866715356607065585669687052555557先用粘贴函数=VAR(数值)计算方差输入格式单行或单列输入格式单行或单列将方差数值代入 用z-检验58.226.301.0uu推断推断结论结论80六、方差分析六、方差分析 Analysis of VarianceAnalysis of Variance（一）单因素试验方差分析【例6-1】以淀粉为原料生产葡萄糖过程中，残留的许多糖蜜可用于酱色生产。在生产酱色之前应尽可能彻底除杂，以保证酱色质量。为此对除杂方法进行选择。今选用5种除杂方法，每种方法做4次试验，试验结果见表5，试分析不同除杂方法的除杂效果有无差异？设各总体服从正态分布，且方差相等。除杂方法（Ai）除杂量（xij）A125.624.425.025.9A227.827.027.028.0A327.027.727.525.9A429.027.327.529.9A520.621.222.021.28181工具工具 数据分析数据分析 方差分析：单因素方方差分析：单因素方差分析差分析输入区域：A3:E7分组方式：行标志位于第1列：0.01 确定确定88891、两因素无重复试验的方差分析【例6-2】某厂现有化验员3人，担任该厂牛奶酸度（T）的检验。每天从牛奶中抽样一次进行检验，连续10天的检验分析结果见下表。试分析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（二）两因素试验方差分析9090工具工具 数据分析数据分析 方差分析：无重复双方差分析：无重复双因素因素输入区域：A1:D5标志：0.01 确定确定继续手工完成结果推断和多重比较过程96表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.096 【例6-6】施用A1、A2、A3 3种肥料于B1、B2、B3 3种土壤，以小麦为指示作物，每处理组合种3盆，得产量结果(g)于表6-7。试作方差分析。2、两因素有重复试验的方差分析 97(A)(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输入格式9898工具工具 数据分析数据分析 方差分析：可重复双方差分析：可重复双因素因素输入区域：A1:D10每一样本的行数：3：0.05 确定确定104 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整理后整理后继续手工完成结果推断和多重比较过程105105 【例6-4】设有A、B、C、D、E这5个大豆品种，其中E为对照，进行大区比较试验，成熟后分别在5块地随机抽取4个样点，每点产量（kg）列入下表，试对A、B、C、D四个品种做产量比较。表 大豆品比试验结果品种取 样 点1234A23212421B21191818C22232220D19201918E15161617106方差分析差异源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品种内（误差）21.5151.4总变异122.819整理后：107 【例6-5】将A1、A2、A3、A4四种生长素，并用B1、B2、B3三种时间浸渍菜用大豆品种种子，45天后处理平均单株干物重（g）列入表6-5 试做方差分析。表6-5 生长素处理大豆的试验结果生长素（A）浸渍时间(B)B1B2B3A110910A2254A3131414A4121213TB374041108方差分析差异源SSdfMSFP-valueF crit行177359 78.666673.3E-05 4.757063列2.1666672 1.083333 1.444444 0.307547 5.143253误差4.560.75总计183.666711生长素处理大豆试验结果方差分析表差异源SSdfMSFF0.05F0.01生长素间177359.078.674.769.78浸渍时间间2.1221.11.445.1410.92误差4.560.75总变异183.711整理后：109109 教材P157【例6-1】某食品感官评定时，测得食品甜度与蔗糖质量分数的关系如下表，试求y对x的直线回归方程。七、直线回归与相关七、直线回归与相关某食品甜度与蔗糖质量分数蔗糖质量分数x（%）1.03.04.05.57.08.08.5甜度y15.018.019.021.022.623.826.0输入格式输入格式得回归方程为：xy2550.19585.13