Excel在统计中的应用与数据统计分析..docx

《Excel在统计中的应用与数据统计分析..docx》由会员分享，可在线阅读，更多相关《Excel在统计中的应用与数据统计分析..docx（35页珍藏版）》请在课桌文档上搜索。

1、STEP2:选择,工具菜STEP4:出现“描述统计”年份城镇居民可支配收入年份城镇居民可支配收入197834419922026.6197940519932577.41980477.619943496.21981500.4199542831982535.319964838.91983564.619975160.31986899.62000628019871002.220016859.61986899.62000628019871002.220016859.619881181.420027702.81989137920038472.219901510.220049421.619911700.620

2、0510493STEPI:用鼠标点击工作表中待分析数据的任一单元格。单的，数据分析“子菜单。STEP3:用鼠标双击数据分析工具中的“描述统计”选项。对话框,如图1-1所示。图1-3图表向导图STEP4：确定用于制作图表的数据区。Excel将自动把你前面所选定的数据区的地址放入图表数据区的内,单击下一步按钮,出现图1-4所示对话框。图1-4作图过程图STEP5:填写图表标题、X轴坐标名称、Y轴坐标名称,此处不用填写,单击下一步”。STEP6：选择图表输出的位置,然后单击“完成”按扭即生成（三数据透视表工具数据透视表是EXCel中强有力的数据列表分析工具.它不仅可以用来作单变量数据的次数分布或总和

3、分析.还可以用来作双变量数据的交叉频数分析、总和分析和其它统计量的分析。例l3:表1.2列出了学生两门功深评定结果.建立学生两门功课评定结果的交叉频数表。表1-2学生两门功课评定结果学号语文数学学号语文数学1001优差1006中良1002良中1007中优1003中中1008差良1004差中1009良中1005差差操作步骤:STEPl:输入数据,选中彳数据的任一单元格.然后选择数据”菜单的“数据透视表”子菜单,进入数据透视表向导。STEP2:选择“MicmsoAExcel数据清单或数据库”为数据源。图1-6所示,单击“下一步二语优艮卬差中中差良防mWe西a农其asO徵Jela旗EBQuRlSjS

4、议表Js)谓啥得分析豺需的数n理盒na,.：，”，“b.c热冕为*或家I8*:。外8W?据为(8)OB合并计算敷需区堡)I取拘JI下一步Q)|w1学号2.10013il00241003-51001.10057100681007918A1.loOg1112.13.MiShcclls图1-6STEP3:选择待分析的数据的区域,一般状况下Excel会自动依据当前单元格确定待分析数据区域,因此你只要干脆单击下一步按扭即可。STEP4:确定数据透视表的结构.在此例中.要建立的是一个交叉频数表.分别按语文和数学的成果对学生的人数进行交叉频数分析.因此可将三个按扭“学号、语文”、“数学”分别拖放到表格的指定

5、部位,并且双击“求和项:学号”,将其改为记数项,结果如图1-7所示。图1-7布局对话框STEP5:选择数据透视表的显示位置之后,单击“完成按扭”,可出现如图1-8所示的数据透视表.IABCDIEF1计数项:学号语文2数学差良优中总计32O2264i)255123622157息计543719图1-8Excel中的Rand（函数可以返回大于等于。小于1的匀称分布随机数.Rand（不带任何参数运行.每次计算时时都将返回一个新的数值。RAND（函数可以被用来作为不重复抽样调查的工具。例15:如图1/2所示有10个象征性的样本数据.欲从中随机抽取5个数据可按如下步骤操作：STEPI:选择B2单元格.输入

6、公式“=RANDr并回车。STEl2拖动B2单元格右下角的填充柄至Bli单元格.并在Bl单元格输入标题-RANDOM00STEP3:选取单元格B2至BII,右击选中的区域选择,复制”,再次右击选中的区域,选择,选择性粘贴”、单击选项数值”后,点击,确定“按扭。STEP4:选取单元格A2至BIl单元格,选择数据”菜单项下的排序子菜单STEP5:选取“RANDOM”为主要关键字.然后点击确定按扭。排序结果如图1-13所示A2至A6单元格的样本即为随机抽取的5个样本。Irandom2IO0.216698330.2340344I0.30234250.4372760.610631TT0.64232840

7、.656722920.6841050.8826741160.953918图1-13试验四由样本推断总体试验目的及要求驾驭利用Excel的几个函数.如求平均函数AVERAGE,标准差函数STDEV.T分布函数TlNV等的组合运用构造一个特地用于实现样本推断总体的EXed工作表0试脸内容及步骤下面的例子先计算样本的平均数和标准差.然后在肯定置信水平上估计总体均值的区间范围.操作步骤：STEPI:构造工作表，如图1-14所示,首先在各个单元格输入以下的内容,其中左边是变量名.右边是相应的计算公式.STEP2:为表格右边的公式计算结果定义左边的变量名。选定A4:B6.A8:B8和A10:B15单元格（

8、先选择第一部分,再按住CTR1.键选取另外两个部分.选择“插入”菜单的“名称”子菜单的“指定”选项.用鼠标点击“最左列”选项.然后点击“确定”按扭即可。1以样本均值推断经.休均值的置信区间2i!3样本就计量4样本个数三C0UNT（样本蛔g）5样本均值-AVERAGE（样本三据）6样本标准差=STDEV（样本数据）7用户输入8通信水平0.959i+算结果10描徉标值误怕次标注空/SQRTC样本个故11自由度=样本个概-11t值=TINV1置信水平，自由度*）13置信区间半径=t值抽样标准误14互信区间上屏=样本娜g百信区间半径，15置信区T盯T见=样制管值图1-14STEP3:输入样本数据,和用

9、户指定的置信水平0.95,如图附“3所示。STEP4:为样本数据命名。选定DI:DIl单元格,选择“插入”菜单的,名称“子菜单的“指定选项,用鼠标点击“首行”选项,然后点击“确定”按扭,得到图皿5所示的计算结果。图1-15结果说明:以上例子说明如何交叉组合运用Excel的公式和函数.以构造出一个能实现样本推断总体有关计算的Excel工作表C事实上,在用Excel进行数据统计处理之时,很多统计功能可以运用和上例类似的方法,通过组合运用Excel的各类统计函数和公式加以实现的。试验五假设检验试验目的及要求驾驭利用EXCel的正态分布函数Normsdist,推断函数IF等.构造一张能够实现在总体方差

10、已知状况下进行总体均值假设检验的Excel工作表。试脸内容及步骤例16利用Excel的正态分布函数NoRMSDIST、推断函数IF等.构造一张能够实现在总体方差已知状况下进行总体均值假设检验的Excel工作表。操作步骤：STEPl:选择“工具”菜单的“数据分析”子菜单.双击“I-检验:双样本等方差假设”选项.则弹出图1/9所示对话框。ABIC11.楔：双样本等方差假设23旧方法装柳间新方法相时间435.222222财31.555555565方差24.44U4U420.027777786观测值997方差22.236111118腮平均差09di1610tStat1.64948461711P(T三t

11、)单尾0.05926989912t单尾临取1.7458821913P(Tat)0.11853979914t双凰脚2.I1.99火821图1/9STEP2:分别填写变量1的区域SB$1().变量2的区域:SD$1:$D$10.由于我们进行的是等均值的检验.填写假设平均差为0.由于数据的首行包括标记项选择标记选项.所以选择“标记”选项.再填写显著水平为005.然后点击确定”按扭。则可以得到图I-20所示的结果。ABC11.桧验：双样本等方差假设23法装配时间新方法装配时间435,22222222131.555555565方差24.444444:4:4：20.027777786997合并方差22.2

12、36111118假设平均差09df1.610tStat1.6494触61711P(T=t)单尾0.05926989912t单是临黑1.745884:21913P(Tj(B26S)康结果=IF卡痂,量力瞄值I拒纳丽喝住不4联的三7拷颈油感生不相毋假设)图1-31结果分析:以上的操作步骤完成整个列联表的分析。其中B15单元格的卡方概率值与B24单元格的卡方统计量是表格的两个重要计算结果。其中卡方概率值等于I.3E-O7表明:假如总体的两类属性.即所在地区和所选地板类型,是不相关的,那么得到以上视察的样本的概率是0.00000013。这个概率几乎接近于0,所以可以认为总体的这两个属性是显著相关的。试

13、验九单因素方差分析试验目的及要求驾驭利用EXCel进行单因素方差分析、无重复双因素方差分析、有重复双因素方差分析的步骤和方法.并能够说明试验结果。试验内容及步骤单因素方差分析可用于检脸两个或两个以上的总体均值相等的假设是否成立。此方法是对双均值检蛤(如U检胎的扩充。检验假定总体是听从正太分布的,总体方差是相等的,并且随机样本是独立的。这种工具适用于完全随机化试验的结果分析。例1-10：如图1-32中所示,一产品制造商雇佣销售人员向销售商打电话。制造商想比较四种不同电话频率安排的效率.他从销售人员中随机选出32名.将他们随机安排到4种安排中.在一段时期内记录他们的销售状况已经在表中列出.试问其中

14、是否有一种安排会带来较高的销售水平。图1-33STEP3:单击“确定”按扭.得到图1-34的计算结果,图1-34结果分析：图134中表格的其次部分则是方差分析的结果。SS列分别给出了四个分组的组间方差、组内方差以及总方差DF列分别给出了对应方差的自由度.MS列是平均值方差,由SS除于DF得到,它是总体方差的两个估计值。F列是F统计量的计算结果,假如四个总体均值相等的假设成立的化.它应当听从F分布.即近似为I.它是最终的计算结果.通过将它与肯定置信水平下的F临界值Fcrit比较.可以推断均值相等的假设是否成立.在本例中.1.67761小于其次步0.94668.所以不能拒绝四个总体均值相等的假设。

15、P-valuc歹1.是单尾概率值,表明假如四个总体均值相等的假设成立的化,得到如上样本结果的概率是19.442%,即得到以上样本并不是小概率事务,同样也得操作步骤:STEPl:选择“工具,菜单的“数据分析子菜单.双击“回来”选项.弹出回来分析对话框。STEP2:按如下方式填写对话框:X值输入区域为SB$1:$BS12.Y值输入区域为$C$l:Sc$l2,并选择标记”和,线性拟合图”两个复选框STEP3:单击“确定”按扭即可.结果如图1-36所示。JIARYOUTPUT入垃纱产量（公斤）1.ineFitPBtultiple0.83863RSquare0.7033;tedRSc0.67033.标准

16、误差1.03146现测值1154.人均布产量（米）预测人均布产量（米）910人均铲量（公斤）SSHSPIdficanceF22.697122.627121.33380.001269.575151.0633132. 272316CoefflCIen标准误差tStstP-value-ver95,JDPer959F限95.0J限95.017Intercepi-18.2887.75798-2.3=730.04279-35.838-0.738-35.833-0.73797518纱产量（公8.464331.832564.61S350.001264.3187812.60994.3187312.6098802

17、图1-36结果分析:结果可以分为四个部分,第一部分是回来统计的结果包括多元相关系数、可决系数R2、调整之后的相关系数、回来标准差以及样本个数。其次部分是方差分析的结果包括可说明的离差、残差、总离差和它们的自由度以及由此计算出的F统计量和相应的显著水平。第三部分是回来方程的截距和斜率的估计值以及它们的估计标准误差、t统计量大小双边拖尾概率值、以及估计值的上下界。依据这部分的结果可知回来方程为Y=846433*X-18.288。第四部分是样本散点图,其中蓝色的点是样本的真实散点图.红色的点是依据回来方程进行样本历史模拟的散点。假如觉得散点图不够清楚可以用限标拖动图形的边界达到限制图形大小的目的，用

18、相同的方法可以进行多元线性方程的参数估计,还可以在自变量中引入虚拟变量以增加方程的拟合程度C对于非线性的方程的参数估计,可以在进行样本数据的线性化处理之后,再按以上步骤进行参数估计。（二相关系数计算试验内容及步骤STEPl:用鼠标点击表中待分析数据的任一单元格.STEP2:选择“工具”菜单的“数据分析”子菜单。STEP2:用鼠标双击数据分析工具中的“相关系数”选项.STEP4:填写完“相关系数”对话框,单击“确定，按扭即可得到各个变量的相关系数矩阵.结果如图1-37所示。图1-37结果说明:图-37下三角矩阵计算出三个变量x.y,z两两之间的相关系数.如变量x之间的相关系数为0929167.所

19、以可以推断x.y之间存在着较高的正线性相关关系。试验十二自回来模型的识别与估计试验目的及要求驾驭利用EXCel对时间序列的自回来模型进行识别与估计的步骤和方法.并能够说明试验结果。试验内容及步骤例112:如图138所示是自1999年4月I日起的20个交易日内的上证指数的时间序列,试用自回来模型加以拟合。ABC1日用上证据政Z2362511168.5IhEjfl2;:2-AVeKAGE(IiiFlIiIf)022lW.M=1iifar-AVEKAGc(iilSR)1036X31178.15=上证指hes3三(1Wnrtr1.l362641181.52-上证惜款-SVERA更Ct证哨知12IlTO

20、.82=IuHfir-AVSRAGE(XiiFSR)19363661166.24上让陪三?AVtRMS（上位稻政）143620911.47=上证旧步-AEAGE（上证增尊115362701171.6=上证样5-NBWR（上证指知163211IMd.06上让增FVERME（；让痞鸵）IT32T21137.16上证恬，-AVwM8（上证婚检）1.B362T31140=上证憎就-NHSR（上UPlWT）19362T61112.79=IiItsr-AVERAKE(I1.EfSfr)I20X2T71091.69工上证指坡-AVERAGE（上证指教）2132T81091.00-iiTtfir-VERAGE

21、(j1.TISUI图1-38操作步骤:STEPI:数据的零均值化处理。如图139中所示.在CI中输入序列名“Z”.在C2中输入公式”=上证指数AVERAGE（上证指数二然后在C2单元格中.拖动EXCer填充柄“将公式复制到C3至C22单元格,即可生成上证指数的零均值化序列。,6,6,0FFSET(F2,0,0,6”,MMU1.T(MINVERSE(OFFSET(H2,0,0,7,7QFFSET(F2,0,0,7”JMMU1.T(MlNVERSE(OFFSET(H2.00,8,8QFFSET(F2,0,0,8”。(说明1.在Excel中输入数组公式时.先用副标选定全部需放置结果的单元格地址范围然

22、后输入数组公式.例如“=MMU1.T(MlNVERSE(OFFSET(H2.0.0.22OFFSET(F200.2”,然后同时按下“CTR1.+SHIFT+回车”三个按键,完成数组公式的输入.公式会自动加上一对大括号,它由EXCel自动添入。STEP2:以上数组公式中包含的各个函数的含义及其用法请参看附表I0最终，将每一个方程组的最终一个解.用值复制的方式复制到PaC这一列.即可得到8个偏自相关系数，如图附39.表中H12:O19单元格的8列分别给出了8个数组公式计算的结果F12FI9单元格的内容即是所要求解的8个偏自相关系数。IEFGHIJK1.MH011.agac210.819067942

23、10.8190.612X4520.3270.1640.0388-0.01320.6115102280.8炉10.819X6120.4520.3270.1642.039430.451723870.6120.8191X8190.6120.4520.32720.164540.3272199780.4520.6120.81910.8190.6120.45170.32T650.1642156030.3270.4520.612X81910.8190.61150.452760.0387534840.1640.3270.452X6120.81910.81910.6128T-0.0097946470.0390.

24、164032?).4520.6120.81910.81998f.116257009-0.010.0390.1643270.4520.6120.8191110HjpacIl2i3145i67iS120.8190679420.8190967097109720967097209704OW13P.180361799-018-0205-02102-0204-0134-0202140.0259059520026004800020002-0015-008515-0.022714997UU-ZUIVu】yuH.16-0.2191402?-0220M3-0226023170.024732305002500580

25、115180.0846854690084703M19-0.310257383031图1-40STEP4:模型的识别与估计。自相关函数序列呈明显拖尾性.偏自相关函数序列在kl之后,都在区间(0438.0.438之间.因此可以认为自相关函数在Kl之后截尾.因此我们选用ARd模型进行数据拟合。复制C2:C20的数据,将之以值复制的形式复制到DD2l的单元格.并在Dl中填入标记项选择工具“菜单的数据分析”子菜单.双击“回来”选项,弹出回来分析对话框。按图附0所示的方式填写对话框。然后单击“确定”按扭,即可得到AR(I模型的估计结果。STEP5:按以上操作步骤可得到图1-41所示AR(I模型。图1-41

26、结果分析:零均值化模型的估计结果是Z=l.062S4*Z(l.还原成上证指数.最终的时间序列模型是:上证指数估计值-上证指数的平均值=1.06284(上一天上证指数-上证指数平均值。Excel与数据统计分析统计计算与统计分析强调与计算机亲密结合，Excel与数据统计分析旨在提高学生计算机的综合运用实力.用统计方法分析问题、制决问题而编写的“依据教材内容.也可以选择运用SPSS、QSTAT,Evievs.SAS、MINiTAB等统计软件。第三章统计整理3计量数据的频数表与直方图例3.1(3-1一、指定接受区域直方图在应用此工具前.用户应先确定分布区间。否则.Excel将用一个大约等于数据集中某数

27、值的平方根作区间.在数据绻的很大值与最小值之间用等宽间隔，假如用户自己定义区间,可用2、5或10的倍数,这样易于分析。对于工资数据,最小值是100,最大值是298。一个紧凑的直方图可从区间100起先.区间宽度用10.最终一区间为300结束,须要21个区间。这里所用的方法在两端加了一个空区间,在低端是区间”100或小于100”,高端是区间”大于300”。参考图3.3,利用下面这些步骤可得到频率分布和直方图：1 .为了便利,将原始数据拷贝到新工作表“指定频数直方图”中。2 .在Bl单元中输入“组距”作为一标记,在B2单元中输入100M3单元中输入110.选取B2:B3.向下拖动所选区域右下角的+到

28、B22单元。3 .按下列步骤运用“直方图”分析工具：回回(1.在分析工具框中“直方图二如图4所示。图3.1数据分析工具之直方图对话框1输入输入区域:A1:A5I接受区域:B1.B22(这些区间断点或界限必需按升序排列选择标记2输出选项输出区域:CI选定图表输出逋(2EXCCl将计算出结果显示在输出区域中。ABCDEF1工货级距组距烦率|2146100100131761101100141981201202|5227130130376298140HO3；67137150150181731601604591941701702102221801804拼4H2671901906121402002004聂

29、313175210210514196220220221152242302304S3.2数据分析工具之直方图对话植Excel将把频率分布和直方图放在工作表中,如图3.3所示,输出表的C和D列中包括起先指定的界限。这些界限事实上是每一区间的上限,也就是说,界限事实上是边界.图3.3频数分布与宜方图为了使图表更像传统的直方图和更易于理解,可双击图表并对它做如下修改：1 .图例:因为只有一个系列的数据显示在图表中,所以不须要图例,单击图例（位于图表右恻的“频率”并按DeEe键。2 .图表区:绘图区是以X和丫轴为边界的矩形区域，通过在柱形上面单击可选取绘图区.单击鼠标右键并选择绘图区格式.将边框改为无并

30、将区域改为无.单击确定。3 .条宽:在传统的直方图中.柱形是彼此相连接而不是分开的。选择某个柱形.单击鼠标右键.选择数据系列格式.并单击选择标签.将间距宽度从150%改为0间距4 .X轴标记:选取X间距轴.单击鼠标右键.选择坐标轴格式.单击对齐标签.将方向从自动改为水平文本.在这种设置下.即使图表已重簧尺寸.X轴标记也会变为水平4.6间距图3.4修改后的直方图二、不指定接受区域直方图在进行探究性分析时.为了便利,通常不指定接受区域作直方图.步骤如下：（1间距宽度.在分析工具框中“直方图:如图4所示。I输入输入区域:AI:A5I接受区域：（该处为空选择标记2轮出选项输出区域：Bl选定图表输出确定

31、(3按前面方法对直方图进行进一步修饰即得图3.5910111213X*146176198227298137173194222267140175接收100128.3156.6184.9213.1其他241.4269.7频率1461312759DEFGHIH方用图3.5修改后的直方图32计数数据的透视表与条图ABC“隹收一频率1461001176128.34198156.66227184.913298213.112137241.47173269.75IM其他2222267140175而II-I步躲如下:确定(1.如图4所示。(2.选择数据源区域1方法双表加茎亮达iQg向导-3步骤N2请凝人战逢定要建立数据遴现表的数据诅区域i三E.SD:I透设表!$里1：加透6|否tfl)-I回取消I上一步电)I下一步IWI(3选定数据透视表位置.完成逸定区域|：SlIM：11t!f邮t)I画IIwI(4将“性别”作为行字段拖至G列.并将“性别”作为数据拖至数据项处.得下表结同理可得“文化程度”的透视表GHIJK计数项:性别计数项:文化程度性01汇总文化程度汇总男12初中8女3高中UT2总计151文盲1小学2中专1总计15此时如点击图形按钮,马上得到如下的透视图黑即1高中技工文小隼中专20男721112111