汽车整车转动惯量的计算与选取

应用统计上机实验指导目录实验1用Excel搜集与整理数据.................................................................................................11.1借助Excel搜集数据.......................................................................................................11.2用Excel进行统计分组...................................................................................................31.3用Excel作统计图...........................................................................................................6实验2用Excel计算描述统计量.................................................................................................72.1用函数计算描述统计量..................................................................................................72.2描述统计工具的使用.......................................................................错误!未定义书签。实验3用Excel进行参数估计和假设检验...............................................................................103.1用Excel进行区间估计.................................................................................................103.2用Excel进行假设检验.................................................................................................12实验4用Excel进行方差分析...................................................................................................144.1单因素方差分析............................................................................................................154.2无交互作用的双因素方差分析....................................................................................154.3有交互作用的双因素方差分析....................................................................................16实验5用Excel进行相关与回归分析.......................................................................................175.1用Excel进行相关分析.................................................................................................185.1.1利用函数计算相关系数.....................................................................................185.1.2用相关系数工具计算.........................................................................................185.2用Excel进行回归分析.................................................................................................195.2.1一元线性回归.....................................................................................................195.2.2多元线性回归.....................................................................................................20实验6时间序列分析和预测......................................................................................................216.1增减速度分析................................................................................................................21*6.2平稳序列预测..............................................................................................................216.2.1移动平均法.........................................................................................................216.2.2指数平滑法.........................................................................................................226.3趋势型序列的预测........................................................................................................226.4季节型序列的预测........................................................................................................236.4.1季节型多元回归预测.........................................................................................236.4.2复合型序列的分解预测.....................................................................................23实验项目学时进度实验1～实验34安排实验4～实验64社会调查数据搜集、分析、撰写71实验1用Excel搜集与整理数据【实验目的】掌握用Excel进行数据的搜集、整理和显示。【实验要求】1、验证本指导中的操作；2、在Excel中完成教材P25例的操作；3、在Excel中完成《应用统计分析技能训练》P12分析题。1.1借助Excel搜集数据搜集数据的有多种，可以采用统计报表、抽样调查或普查，尤以抽样调查较为常见，所以我们在这里介绍一下如何用Excel进行抽样。使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志。【例】假定有100个总体单位，从1到100编号。需要在这100个单位中抽取20个单位组成样本。【操作步骤】【方式一：产生随机数方式】1）使用函数产生随机数。使用Excel函数“RANDBETWEEN(a,b)”生成[a,b]区间范围内的随机整数。在Excel单元格中输入=RANDBETWEEN(1,100)然后通过填充（使用单元格填充柄）得到等于样本容量个数的随机数，这些随机数对应编号的个体即被抽出。（注：如果该函数不可用，并返回错误值#NAME?，请加载“分析工具库”宏。加载方法是：在“工具”菜单下选择“加载宏”，在弹出的对话框中勾选“分析工具库”，然后确定加载。如果您的Excel是不完整安装的，可能会提示插入Office安装光盘。）（2）使用宏工具产生随机数。单击“工具”菜单，选择“数据分析”（注：如果“工具”菜单中没有该项，也需如前加载“分析工具库”），从中选择“随机数发生器”。在弹出的对话框中如下图输入和选择，点确定后即可获得1~100范围的随机实数，再四舍五入获得随机整数（方法：重复点击工具栏“减少小数位数”按钮“”直至显示整数，或使用函数ROUND）。1【方式二：随机抽样方式】1、将各总体单位的编号输入工作表（无规律的非连续编号需手工逐个输入。连续编号可使用“填充”功能快捷输入）；2、单击“工具”菜单，选择“数据分析”，在弹出的对话框中选择“抽样”。3、弹出“抽样”对话框。如图1－1图1－1抽样对话框1）在“输入区域”框中输入总体单位编号所在的单元格区域，本例是A1:A100。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），注意点击“标志”复选框。2）在“抽样方法”中，有周期和随机两种抽样模式：“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。比如要在100个单位中抽取12个，则在“间隔”框中输入8。“随机”模式适用于简单随机抽样，只需在“样本数”框中输入样本容量即可。23）指定输出选项，这里我们指定“输出区域”为C1，单击确定后，即得到抽样结果。1.2用Excel进行统计分组用Excel统计分组的各组的频数，有两种方法：一是利用数组函数FREQUENCY；二是利用数据分析中的“直方图”工具。【例】教材P25例：某企业100名职工工资资料：480250440220350350420380360350350280340320520430500440480460580470330410420350380470610390400450390460290510320380410530440380470570260400470560430600580450420560520410520390480230320370550420370290310400300520360510470430540570460510480340550510450500470440370420480540300400530550530200650630540590这里采用的分组组限是（对一组数据如何分组和确定组限，请参见统计学教材）：200-260,260-320,320-380,380-440,440-500,500-560,560-620,620-680。【操作步骤】【方法一】利用FREQUENCY函数：1、将原始数据录入在A1:J10单元格；将分组上限输入A13:A20单元格（注意：分组的上限值应当按升序排列。对于数值型数据，Excel在统计时把“上限值”包含在了该组内，这与我们统计学上的习惯规定“上组限不在内”不同，因此，建议输入的分组上限值是：259、319、379、439、499、559、619、679。Excel将按(a,b]区间模式计数）。2、选中B13:B21单元格后，输入“=FREQUENCY(A1:J10,A13:A20)”，按组合键“Ctrl+Shift+Enter”，Excel即返回频数统计结果。如下图：33、在单元格C13编写公式计算频率“=B13/sum(B$13:B$20)”（注意恰当使用单元格的相对引用与绝对引用$），并向下填充得到各组频率；在单元格D13编写公式计算向上累计频数“=sum(B$13:B13)”，并向下填充得到各组累计频数。4、根据各组频数（或频率）绘制直方图，参见1.3节。【方法二】利用“直方图”工具：1、在“工具”菜单中单击“数据分析”选项，从其对话框的分析工具列表中选择“直方图”，打开直方图对话框。如图1－2所示：图1－2直方图对话框42、在“输入区域”输入需要分组的原始数据所在的单元格区域，本例为A1:J10。在“接收区域”输入定义接收区域边界值的单元格引用，本例已在A13:A20输入数值“259、319、379、439、499、559、619、679”，因而，接收区域中输入“A13:A20”。3、选择输出选项。这里选择B12为起始的“输出区域”。另外，如果在如图1-2的对话框中勾选“柏拉图”，可得到按降序排列的柱形图（帕累托图）；勾选“累计百分率”，Excel将在直方图上添加累积频率折线；勾选“图表输出”，可以得到直方图。图1－3频数分布和直方图应当注意，上图的输出实际上是一个“条形图”的形式，若要把它变成“直方图”形式，可按如下操作：用鼠标右键单击任一长方形柱条，在弹出的快捷菜单中选择“数据系列格式”，在之后弹出的对话框中点击“选项”标签，把“分类间距”宽度改为“0”，点击确定即可。如图1－4所示：图1－4数据系列格式对话框【练习】用Excel“工具”-“数据分析”中的“随机数发生器”产生300个服从正态分布（均值为1500，标准差为260）的数据，然后对其进行适当的统计分组，统计频数，并绘制出直方图（注意观察图形是否呈对称分布）。51.3用Excel作统计图Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。【例】这里采用教材P22表2.2的资料，看一下如何作饼图。按所有制分组企业数全民3204集体1286私营152中外合资212外商独资102其它44合计5000【操作步骤】1、选中某一单元格，单击“插入”菜单，选择“图表”选项（或直接点击工具栏“”图标），弹出图表向导对话框。2、在“图表类型”中选择“饼图”，然后在“子图表类型”中选择一种类型，然后单击下一步按钮，打开图表源数据对话框。3、在图表源数据对话框中“数据区域”填入数据所在区域，单击下一步按钮，得到如1－5所示“图表选项”对话框，根据需要勾选“百分比”，点击完成即可。图1－5“图表选项”对话框【“复合饼图”的绘制】：第1步操作同前，在第2步操作中，在“子图表类型”中选择“复合饼图”，则可以将占比较小的后4类提取组合进第二个饼图（在所得图形中用鼠标右键点击子图弹出快捷菜单，选择“数据系列格式”，然后在“选项”中输入第二个绘图区包含的类别数目）。6【操作练习】使用恰当的图形表现下列数据：（1）某城市家庭对住房状况的评价情况调查结果：回答类别户数(户)百分比(%)非常不满意248不满意10836一般9331满意4515非常满意3010合计300100.0（2）显示粮食单位面积产量与降雨量、温度的关系：温度降雨量(mm)产量(kg/hm2)62522508403450105046001362570014957400161105900实验2用Excel计算描述统计量【实验目的】用Excel计算描述统计量【实验要求】1、验证本指导手册中的操作；2、在Excel中完成《应用统计分析技能训练》P82练习一。2.1用函数计算描述统计量Excel中用于计算描述统计量的方法有两种：函数方法和描述统计工具方法。未分组资料可用函数计算，已分组资料可借助于Excel工作表编写相应公式计算。常用于描述统计指标计算的Excel函数如下表：7平均指标变异指标形态指标指标名称Excel函数说明算术平均数AVERAGE返回一组数据的算术平均值调和平均数HARMEAN返回数据集合的调和平均值几何平均数GEOMEAN返回正数数组或区域的几何平均值切尾平均数TRIMMEAN返回数据集的内部平均值。两头对称的一共去掉的数据个数，用参数以占数据总数的百分比给出众数MODE求一组数据的众数中位数MEDIAN返回给定数值集合的中位数四分位数QUARTILE参数取值与含义：0最小值；1下四分位数；2中位数；3上四分位数；4最大值平均差AVEDEV求一组数据与其均值的平均离差样本标准差STDEV计算基于给定样本的标准差总体标准差STDEVP计算总体的标准差样本方差VAR计算基于给定样本的方差总体方差VARP计算总体的方差峰度系数KURT返回数据集的峰度值偏态系数SKEW返回分布的偏斜度【例2.1.1】为了解某地毕业大学生的工资情况，随机抽取30人，月工资（元）如下：156013401600141015901410161015701710155014901690138016801470153015601250156013501560151015501460155015701980161015101440以计算“切尾平均数”为例：【方法一】直接输入函数名。假设已经将这30个人的工资数据输入A1:J3单元格，选中任一空白单元格，输入“=TRIMMEAN(A1:J3,0.1)”（函数TRIMMEAN将去除的数据点数目向下舍入为最接近的2的倍数。这里参数=0.1，30个数据点的10%等于3个数据点，所以函数TRIMMEAN将对称地在数据集的头部和尾部各去除一个数据），回车后可得去掉一个最大值和去掉一个最小值后的平均数，为1529.286.【方法二】插入函数方式。点击“插入”菜单，选择“函数”，从弹出的对话框中，“选择类别”为“统计”，然后在“选择函数”框中找到相应的函数命令，确定后再在弹出的对话框中输入函数所需参数即可。其余函数的操作与此相似，注意练习。对组距分组资料计算描述统计分析指标，只能借助Excel工作表自己编写公式完成。【例2.1.2】某班同学成绩分组统计资料如下表。计算平均数和标准差。成绩(分)组中值(x)学生数(f)60以下55460-70651270-80752480-9085690以上954合计—508该例需要计算加权算术平均数和按加权形式计算标准差。【操作步骤】1、加权算术平均数的计算公式为：xxf。（假定原始数据分组表在fA1:C7单元格，如图2-1所示）在D2单元格输入计算公式“=B2*C2”，然后向下填充至D6，在D7单元格对D2:D6求和得到xf，在D8计算平均数“=D7/C7”。(xx)2f2、这里使用的标准差计算公式为：f。在E2输入“=(B2-D$8)^2*C2”（注意单元格D8对“行”的绝对引用），然后向下填充至E6，在E7单元格对E2:E6求和，在D9计算标准差“=SQRT(E7/C7)”。图2-12.2描述统计工具的使用仍使用【例2.1.1】的数据，我们需要把数据调整到A1:A30单元格（调整到一列的原因是Excel会把每一列（或行）作为一个样本进行计算），然后按以下步骤操作：1、在“工具”菜单中选择“数据分析”选项，从弹出的对话框中选择“描述统计”，点击确定后打开描述统计对话框，如图2－2所示：9图2-2描述统计对话框2、在“输入区域”中输入数据所在的区域“A1:A30”，在“输出区域”中输入“C1”（指的是从这个位置开始放置输出结果），其他复选框可根据需要选定：勾选“汇总统计”，可给出一系列描述统计量；勾选“平均数置信度”，会给出用样本平均数估计总体平均数的置信区间；“第K大值”和“第K小值”会给出样本中第k个大值和第k个小值。单击确定，可得如图2－3所示输出结果。图2-3描述统计输出结果上面的结果中，平均是指样本均值；标准误差是指样本平均数的标准差；中位数、众数、标准差分别指样本中位数、众数、标准差（自由度为n-1）；峰度即峰度系数；偏度即偏度系数；区域实际上是极差（全距）。实验3用Excel进行参数估计和假设检验【实验目的】用Excel进行参数估计和假设检验.【实验要求】1、验证本指导手册中的操作；2、验证《应用统计分析技能训练》中相关操作；3、在Excel中完成《应用统计分析技能训练》P96练习四。在Excel中，进行参数估计只能使用公式和函数的方法，而假设检验除以上两种方法外，还可以使用假设检验工具。3.1用Excel进行区间估计3.1.1使用函数CONFIDENCE总体标准差已知时，对总体均值的区间估计为xz1/2，可使用函数nCONFIDENCE计算抽样的极限误差z1/2，调用格式为：n10CONFIDENCE(alpha,standard_dev,size)。其中，Alpha对应于置信度等于100*(1-alpha)%，Standard_dev为已知的总体标准差，Size为样本容量。【例】教材P78例5.1（《应用统计分析技能训练》P92例4.1）。如下图，在B2输入的值0.05，B3中输入标准差200，B4中输入样本容量16后，在B5编写公式=CONFIDENCE(B2,B3,B4)，在B6输入均值6200，然后在B7计算下限=B6-B5，在B8计算上限=B6+B5。3.1.2编写公式当总体标准差未知时，对总体均值的区间估计为xt1/2s，可以在Excel中n调用函数并编写公式计算该置信区间。函数STDEV计算样本标准差，TINV(alpha,df)返回双尾面积为alpha、自由度为df的t分布的临界值。Excel中的统计函数：NORMSDIST,TDIST,FDIST,CHIDIST分别计算标准正态分布、t分布、F分布、2x)；NORMSINV,TINV,FINV,CHIINV分别计算标分布的概率P(X准正态分布、t分布、F分布、2分布的临界值（分布函数的逆函数）。【例】一保险公司收集到由36位投保人组成的随机样本，他们的年龄如下：233539273644364246433133425345544724342839364440394938344850343945484532求投保人年龄90%的置信区间。【用函数计算】1、把数据输入（复制）到A1:F6单元格；2、在H1计算样本平均值x，输入“=AVERAGE(A1:F6)”；3、在H2计算样本标准差s，输入“=STDEV(A1:F6)”；s4、在H3计算估计精度t1/2n，输入“=TINV(0.1,36-1)*H2/SQRT(36)”。其中，0.111是对应于90%置信度的，36是样本容量。5、最后，在H4计算置信下限：=H1-H3在H5计算置信上限：=H1+H3关于总体方差的估计、总体比例的估计等可按类似方法进行。【借助描述统计工具】（总体标准差未知情形）将数据调整为一列，比如放置于A1:A36，然后点击“工具”→“数据分析”→“描述统计”，确定后可得下列结果：由上表输出结果可得投保人年龄90%的置信区间为：(39.5-2.19,39.5+2.19)=(37.31,41.69).3.2用Excel进行假设检验3.2.1直接计算检验统计量的值用Excel函数和公式计算假设检验统计量的值，然后与检验的临界值比较作出结论。检验统计量值的计算与参数估计类似。如，下面的命令是针对3.2.2节例题数据计算检验统计量的值的：=(AVERAGE(A1:E10)-1.35)/STDEV(A1:E10)*SQRT(50)3.2.2使用检验函数12通过检验函数得到检验的P值，与显著性水平比较，从而得出结论（若P值<，则否定原假设；否则不能否定原假设）。ZTEST,TTEST,FTEST,CHITEST—计算Z检验、t检验、F检验、2检验的P值.对ZTEST，下面的Excel公式可直接返回单尾P值：=MIN(ZTEST(array,μ0,sigma),1-ZTEST(array,μ0,sigma))计算双尾P值的公式：=2*MIN(ZTEST(array,μ0,sigma),1-ZTEST(array,μ0,sigma))在“array”位置输入原始数据所在区域；在“μ”位置输入参数的某一假定值；在“Sigma”0后输入已知的总体标准差（若总体标准差未知则可忽略不填，系统将自动使用样本标准差代替）。【例】一种机床加工的零件尺寸绝对平均误差为1.35mm。生产厂家现采用一种新的机床进行加工以期进一步降低误差。为检验新机床加工的零件平均误差与旧机床相比是否有显著降低，从某天生产的零件中随机抽取50个进行检验。利用这些样本数据，检验新机床加工的零件尺寸的平均误差与旧机床相比是否有显著降低？(=0.01)1.261.191.310.971.811.130.961.061.000.940.981.101.121.031.161.121.120.951.021.131.230.741.500.500.590.991.451.241.012.031.981.970.911.221.061.111.541.081.101.641.702.371.381.601.261.171.121.230.820.86提出原假设H0：1.35，H1：<1.35【操作步骤】本例为单边Z检验，故只需在某单元格输入=MIN(ZTEST(A1:E10,1.35),1-ZTEST(A1:E10,1.35))即可得出P值=0.004579。由于P值<=0.01，故否定H0。3.2.3假设检验工具的使用在Excel中，假设检验工具主要有四个，主要是针对双总体的，如图3－1所示：图3－1数据分析对话框“平均值的成对二样本分析”实际上指的是在总体方差已知的条件下，两个样本均值之13差的检验，准确的说应该是Z检验；“双样本等方差检验”是总体方差未知，但假定其相等的条件下进行的t检验；“双样本异方差检验”指的是总体方差未知，但假定其不等的条件下进行的t检验；“双样本平均差检验”指的是配对样本的t检验。【例】某饮料公司开发研制出一新产品，为比较消费者对新老产品口感的满意程度，该公司随机抽选一组消费者(8人)，每个消费者先品尝一种饮料，然后再品尝另一种饮料，两种饮料的品尝顺序是随机的，然后每个消费者对两种饮料分别进行评分(0分～10分)，评分结果如下表。取显著性水平=0.05，该公司是否有证据认为消费者对两种饮料的评分存在显著差异？旧饮料54735856新饮料66743976【操作步骤】1、输入数据到工作表。2、选择“工具”下拉菜单，并选择“数据分析”选项；在分析工具中选择“t检验：平均值的成对二样本分析”；3、当出现对话框后，在“变量1的区域”方框内输入变量1的数据区域；在“变量2的区域”方框内输入变量2的数据区域；在“假设平均差”方框内输入假设的差值(这里为0)；在“”框内输入给定的显著性水平，然后确定。可得如下结果：图3－2平均值的成对二样本分析结果在上面的结果中，我们可以根据P值进行判断，也可以根据统计量和临界值比较进行判断。本例采用的是双侧检验，P值为0.217，大于给定的显著性水平0.05，所以不能拒绝原假设，即不能认为消费者对两种饮料的评分存在显著差异；若用临界值判断，得出的结论是一样的，如本例t值为-1.357，|t|<2.365(临界值)，所以也是不能否定原假设。实验4用Excel进行方差分析【实验目的】掌握用Excel进行方差分析的方法。【实验要求】1、验证本指导手册中的操作；2、验证教材中相关例题的数据；3、在Excel中完成《应用统计分析技能训练》P98练习五。144.1单因素方差分析【例】样本由7家零售企业、6家旅游企业、5家航空公司和5家家电企业构成，消费者对该4个行业的投诉次数如下：表消费者对4个行业的投诉次数企业1234567零售业57664940345344旅游业683929455651航空公司3149213440家电制造业4451657758问：行业间的服务质量是否存在显著差异？原假设H0：μ1=μ2=μ3=μ4（行业之间的服务质量无显著差异）【操作步骤】1、点击“工具”菜单，选择“数据分析”，在分析工具中选择“方差分析：单因素方差分析”，然后点击确定。2、当对话框出现时，在“输入区域”方框内键入数据单元格区域，本例为B1:H4；选择“分组方式”，本例为“行”；在“”方框内，默认为0.05，可根据需要改变；在“输出选项”中选择输出区域，本例选A5。结果如下：从上表可见，由于F=3.4066>Fα=3.1274（也可以直接看检验的P值，若小于显著性水平α，则拒绝原假设），故拒绝H0，即认为行业之间的服务质量存在显著差异。4.2无交互作用的双因素方差分析【例】为了研究不同地点、不同季节大气飘尘含量的差异性，对地点（A）取三个不同水平，对季节（B）取四个不同水平，在不同组合（Ai,Bj）下各测得一次大气飘尘含量（mg/m2），结果见下表，试研究地点间的差异及季节间的差异对大气飘尘含量有无影响？(α＝0.01)因素B冬季春季夏季秋季15因素A11.1500.6140.4750.66721.2000.6200.4200.88030.9400.3790.2000.540解建立假设H0A:不同地点对大气飘尘含量无影响，H0B:不同季节对大气飘尘含量无影响。Excel操作步骤】1、将观测数据复制到Excel工作表；2、点击“工具”菜单，选择“数据分析”，在分析工具中选择“方差分析：无重复双因素分析”，然后点击确定。3、当对话框出现时，在“输入区域”方框内键入数据单元格区域B2:E4；将“”方框内默认的0.05改为0.01；在“输出选项”中选择输出区域A6。结果如下：由于FAαBα，故分别拒绝原假设0A0B，即认为地=23.83>F=10.92,F=88.76>F=9.78H、H点的不同、季节的不同分别都对大气飘尘含量有影响。4.3有交互作用的双因素方差分析【例】为分析4种化肥和3种小麦品种对小麦产量的影响，把一块试验田等分成24小块，对种子和化肥的每一组合种植2小块田，产量如下表，问品种、化肥及两者的交互作用对小麦产量有无显著影响？表小麦产量试验数据（单位：kg）化肥1化肥2化肥3化肥4品种1173174177172172176179173品种217517817417016173177175171品种3177174174169175174173169（考虑交互作用，每一组合至少要作2次试验）Excel实现操作】1、将观测数据复制到Excel工作表A1:E7（包括行标题和列标题）；2、点击“工具”菜单，选择“数据分析”，在分析工具中选择“方差分析：可重复双因素分析”，然后点击确定。3、当对话框出现时，在“输入区域”方框内键入数据单元格区域A1:E7；在“每一样本的行数”方框内键入重复试验次数(2)；在“输出选项”中选择输出区域A9。结果如下（最后部分）：方差分析差异源SSdfMSFP-valueFcrit样本(行因素_品种)8.083324.04174.40910.03673.8853列(列因素_化肥)90.8333330.277833.03030.00003.4903交互(交互作用)51.916768.65289.43940.00062.9961内部(误差)11120.9167总计161.833323可见，化肥因素和交互因素对产量的影响非常显著，品种因素在显著性水平0.05下也是显著的。因而，应注意化肥与品种的交互作用，从试验数据看，应把化肥3与品种1搭配起来安排生产。实验5用Excel进行相关与回归分析【实验目的】用Excel进行相关与回归分析【实验要求】1、验证本指导手册中的操作；2、验证教材中相关例题的数据；3、在Excel中完成《应用统计分析技能训练》P106练习六。【例5.1】某商业银行某年的主要业务数据如下：表11－1某商业银行某年的主要业务数据分行不良贷款各项贷款余本年累计应收贷款项目个本年固定资产编号(亿元）额（亿元）贷款（亿元）数（个）投资额（亿元）10.967.36.8551.921.1111.319.81690.934.8173.07.71773.743.280.87.21014.557.8199.716.51963.262.716.22.212.271.6107.410.71720.2812.5185.427.11843.891.096.11.71055.9102.672.89.11464.317110.364.22.11142.7124.0132.211.22376.7130.858.66.01422.8143.5174.612.726117.11510.2263.515.634146.7163.079.38.91529.9170.214.80.6242.1180.473.55.91125.3191.024.75.0413.4206.8139.47.22864.32111.6368.216.832163.9221.695.73.81044.5231.2109.610.31467.9247.2196.215.81639.7253.2102.212.01097.15.1用Excel进行相关分析用Excel进行相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析工具。5.1.1利用函数计算相关系数在Excel中，提供了两个计算两个变量之间相关系数的函数：CORREL和PEARSON，这两个函数是等价的。如计算“不良贷款”与“贷款项目个数”的相关系数，下述两种方式均可（此处假设例题5.1的数据位于Excel工作表的B2:F26）：=CORREL(B2:B26,E2:E26)或=PEARSON(B2:B26,E2:E26)5.1.2用相关系数工具计算【操作步骤】1、单击“工具”菜单，选择“数据分析”选项，在数据分析选项中选择“相关系数”；2、在弹出的相关系数对话框中（如图5－1）：在“输入区域”输入数据所在区域B2:F26，注意必须为相邻的单元格引用；再选择“分组方式”为“逐列”；在“输出区域”中输入H2，单击确定，即输出5个变量的相关系数矩阵结果。18图5－1相关系数对话框5.2用Excel进行回归分析Excel进行回归分析同样分函数和回归分析宏两种形式，其提供了9个函数用于建立回归模型和预测。这9个函数分别是：INTERCEPT返回线性回归模型的截距SLOPE返回线性回归模型的斜率RSQ返回线性回归模型的判定系数FORECAST返回一元线性回归模型的预测值STEYX计算估计的标准误TREND计算线性回归线的趋势值GROWTH返回指数曲线的趋势值LINEST返回线性回归模型的参数LOGEST返回指数曲线模型的参数但是，用函数进行回归分析比较麻烦，故一般使用回归分析工具。5.2.1一元线性回归仍以前例数据为例。对“不良贷款（Y）”与“各项贷款余额（X）”进行回归分析：【操作步骤】1、单击“工具”菜单，选择“数据分析”选项，在数据分析选项中选择“回归”；2、在弹出的回归对话框中：在“Y值输入区域”输入不良贷款数据所在区域“B2:B26”，在“X值输入区域”输入各项贷款余额数据所在区域“C2:C26”；3、若需残差图等，可勾选相应复选框；单击确定，即可输出如下分析结果。19在上面的输出结果中，第一部分为汇总统计，MultipleR指复相关系数，RSquare指判定系数（可决系数），AdjustedRSquare指调整的判定系数，标准误差指估计的标准误，观测值指样本容量；第二部分为方差分析，df指自由度，SS指平方和，MS指均方，F指用于回归方程显著性检验的F统计量，SignificanceF是F统计量对应的P值；第三部分包括：Intercept指截距，XVariable为自变量，Coefficient指系数，tStat指用于系数显著性检验的t统计量，P-value指t统计量对应的P值。由上述Excel输出结果得到本例的回归分析结果：（1）回归方程为：?y=-0.83+0.0379x（2）判定系数R2=0.712，说明各项贷款余额解释了不良贷款取值变动的71.2%。（3）不良贷款与贷款余额之间的线性关系显著性的F检验：对应的P值SignificanceF=1.18E-07<α=0.05（或根据F=56.75>F=4.28判断），说明不良贷款与贷款余额之间存在显著α的线性关系。（4）自变量x系数显著性的t检验：对应的P值为1.18E-07，说明x对y的影响是显著的。另外，利用回归方程进行预测：可在Excel中编写公式直接计算。如当“各项贷款余额”为400亿元时，“不良贷款”的预测值为“=-0.83+0.0379*400”。或参见6.3节，使用函数进行预测。5.2.2多元线性回归Excel操作基本同于一元线性回归。只是注意要将所有的自变量数据放置在Excel的相邻区域。20实验6时间序列分析和预测【实验目的】用Excel进行时间序列分析和预测【实验要求】1、验证本指导手册中的操作；2、验证教材中相关例题的数据；3、在Excel中完成《应用统计分析技能训练》P89练习二。6.1增减速度分析【例】下表是2000-2009年的四川省第三产业的地区生产总值（单位：亿元），计算增减指标和速度指标。时间2000200120022003200420052006200720082009第三产业的GDP1549.511739.811943.682189.682510.32836.733319.623881.64561.695198.8逐期增减量-累计增减量-环比发展速度%-定基发展速度%-环比增减速度%-定基增减速度%-增长1%的绝对额-【Excel操作】（假设本表处于Excel工作表的A1:K9区域。）1、计算逐期增减量：在C3中输入公式=C2-B2，并向右填充；2、计算累计增减量：在C4中输入公式=C2-$B2，并向右填充；3、计算环比发展速度：在C5中输入公式=C2/B2*100，并向右填充；4、计算定基发展速度：在C6中输入公式=C2/$B2*100，并向右填充；5、计算环比增减速度：在C7中输入公式=(C2-B2)/B2*100或=C3/B2*100或=C5-100，并向右填充；6、计算定基增减速度：在C8中输入公式=(C2-$B2)/$B2*100或=C4/$B2*100或=C6-100，并向右填充；7、计算增长1%的绝对额：在C9中输入公式=B2/100，并向右填充；8、计算平均发展速度：（1）通过计算环比发展速度的几何平均数获得：=GEOMEAN(C5:K5)（2）通过初期水平与末期水平之比开方获得：=(K2/B2)^(1/9)*100%计算平均增减速度：平均增减速度=平均发展速度-100%*6.2平稳序列预测6.2.1移动平均法【例】对下表居民消费价格指数进行分析。21年份CPI3期移动预测误差5期移动预测误差误差平方平均预测误差平方平均预测1990103.11991103.41992106.41993114.7104.310.4107.91994124.11995117.1110.36.845.81996108.31997102.8199899.2199998.62000100.42001100.7200299.22003101.22004103.92005合计－－－－－【Excel操作】1、在A2:A17输入年份，在B2:B17输入CPI的值。2、计算三项移动平均：在C5中输入“=SUM(B2:B4)/3”，并向下填充。或者在“数据分析”中选择“移动平均”，并在对话框中“输入区域”填“B2:B16”、“间隔”填“3”、“输出区域”填“C5”，确定即可。3、“预测误差”是指实际值与移动平均值的差值：输入“=B5-C5”并向下填充。4、计算误差平方：在E5中输入“=(B5-C5)^2”，并填充。6.2.2指数平滑法点击“工具”菜单选择“数据分析”命令，再选择“指数平滑”，在“输入区域”填入数据区域，在“阻尼系数”中填1-的值（0~1范围的常数），确定“输出区域”的位置即可。6.3趋势型序列的预测函数FORECAST(x,known_y's,known_x's)直接返回采用线性回归方程y=a+bx进行预测的预测值。其中，x为需要进行预测的数据点；known_y's为因变量数组或数据区域；known_x's为自变量数组或数据区域。226.4季节型序列的预测6.4.1季节型多元回归预测加入季节虚拟变量进行回归预测。6.4.2复合型序列的分解预测这里仅谈谈借助Excel计算季节指数。【例】某啤酒企业2000－2005年各季度的销量如下表。计算季节指数。年季度时间t销量Y移动平均中心化移比值动平均200011252232333730.0030.6251.208244262001153026383742483020021929210393115041235200311330214393155141637200411729218423195542038200512131222433235442441【操作步骤】1、假设销量数据录入在Excel表的D2:D25单元格（也可以用教材上P141例题的数据，见后面的附录。），在E4单元格输入计算4项移动平均的公式“=SUM(D2:D5)/4”，然后向下填充至E24。在F4单元格输入计算中心化移动平均（趋势值）的公式“=(E4+E5)/2”，然后向下填充至F23。在G4单元格计算季节比率（剔除长期趋势）“=D4/F4”。2、对B列进行自动筛选：分别筛选出1、2、3、4季度对应的季节比率，分别复制整23理为如下表格（B29:E34部分的数据）：3、计算各年同季平均数：在B35单元格输入“=AVERAGE(B29:B34)”并向右填充至E35。4、计算调整系数：在F35单元格计算季节比率的总平均“=AVERAGE(B35:E35)”。5、计算调整后的季节指数：在B36单元格输入“=B35/$F35”并向右填充至E36。附录：教材P141，计算季节指数的数据：年月收购额Y(万元)2001110250380490550620788991010601150122020021152543854935516227989911107511541222200312226038849555612期移2期移正Y/