统计学实验指导书(EXCEL2010)

PAGEPAGE28EXCEL实验指导书注意：由于简装版的EXCEL中没有“数据分析”菜单，因此需要进行以下操作步骤来添加该菜单，前提是计算机中必须有OFFICE2010的原始安装软件，否则下列操作无法完成。“文件”——“选项”——“加载项”——EXCEL加载项“转到”——“加载宏”——选择“分析工具库”——确定。完成以上操作后“数据”菜单中出现“数据分析”则操作成功。

实验一利用Excel对数据进行描述统计分析一、实验目的1、掌握Excel的基本知识2、学会应用Excel创建表格，输入和编辑信息3、熟练运用excel的公式和函数求各种统计指标4、利用Excel的分析工具对数据进行描述性统计5、掌握组距式变量数列的编制原理二、实验要求1、掌握Excel的基本操作方法2、通过练习，能够独立运用Excel进行数据整理和数据分析3、掌握利用Excel对数据进行分组编制的基本操作方法；三、实验内容1、分别用函数和数据分析工具计算这31个地区人口的总和、平均值、中位数、众数、标准差。表1-12008年全国各地区人口统计地区总人口(万人)地区总人口(万人)北京1695山东9417天津1176河南9429河北6989湖北5711山西3411湖南6380内蒙2414广东9544辽宁4315广西4816吉林2734海南854黑龙江3825重庆2839上海1888四川8138江苏7677贵州3793浙江5120云南4543安徽6135西藏287福建3604陕西3762青海554宁夏618新疆2131甘肃2628江西44002、根据抽样调查，某月X市50户居民购买消费品支出资料如下（单位：元）：表1-2某月X市50户居民购买消费品支出83088012301100118015801210146011701080105011001070137012001630125013601270142011801030870115014101170123012601380151010108608101130114011901260135093014201080101010501250116013201380131012701250根据以上数据，以900、1000、1100、1200、1300、1400、1500、1600为组限，对居民户月消费支出额编制组距式变量数列，并计算居民户月消费支出额的累计频数和频率。同时对分组资料用各种统计图的形式来加以表现。四、实验步骤用EXCEL函数计算这31个地区人口的总和、平均值、中位数、众数、标准差（1）如图1.1所示，在工作表中输入数据。（2）熟悉统计常用函数及其功能函数功能函数功能函数功能COUNT记数NORMSINVZ值计算MEDIAN中位数MAX最大值ABS绝对值MODE众数MIN最小值CHIINV卡方值QUARTILE四分位数SUM求和TINVT值计算VAR方差SUMSQ平方和FINVF值计算STDEV标准差AVERAGE平均值SQRT平方根GEOMEAN几何平均数（3）使用函数进行统计计算。（以求数据的总和为例）选定E2单元格，单击工具栏上的“公式——插入函数”命令，出现下图1.2所示对话框，在“选择类别”后的对话框中选择“全部”，在“选择函数”中选择求和函数“SUM”，然后点击确定。②将鼠标置于“Mumber1”右段的编辑框内，在工作表中选择B2：B32的区域，单击“确定”按钮，如下图1.3所示。③则在E2单元格中显示数据“130827”为所求的数据总和。④使用同样的方法分别求的表中数据的平均值、中位数、众数、标准差为下图1.4所示。2、用分析工具试计算这31个地区人口的总和、平均值、中位数、众数、标准差。（1）点击“数据—数据分析”，打开“数据分析”对话框，如下图1.5所示。（2）选择“描述统计”功能，单击“确定”，系统打开描述统计对话框，如下图所示。选定数据的输入区域为B2：B32单元格；分组方式选择“逐列”；选定一个输出区域为D9单元格；再选定“汇总统计”复选框，如图1.6。（3）系统输出2008年我国各地区人口描述统计的计算结果如图1.7所示。（4）在系统的输出中，“平均”即均值X，反映了全国各地区人口的平均水平；“标准误差”为样本均值的标准差，反映了用平均值代表性的大小，本例中标准误差的值很大，说明平均值的代表性差，各地区的人口数量差异比较大；“众数”即出现次数最多的标志值，由于本例中31个标志值互不相同，故没有众数；“标准差”为总体标准差；“方差”为总体方差；本例中峰度的值大于零，表示分布比正态分布更集中在平均数周围，分布呈尖峰状态；偏度的值大于零，说明分布呈正偏斜，即大部分标志值是大于平均值的。2、对居民户月消费支出额编制组距式变量数列，（1）数据输入。分别输入“居民消费品支出”和组限。如图1.8所示（2）执行菜单命令[数据]→[数据分析]，调出“数据分析”对话框，选择“直方图”选项，调出“直方图”对话框。（3）在“直方图”对话框中，输入相关数据，见下图1.9。输入区域：$A$1:$J$5接收区域：$A$7:$A$14，接收区域的数值应按升序排列输出区域：$B$7（为输出结果左上角单元格地址）选中“标志”复选框。柏拉图：选中此复选框，可以在输出表中同时按降序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略累积百分比。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。本例中选中“累积百分比”和“图表输出”两个复选框。（4）实验结果见下图1.10（5）由图中可知，对分组资料进行整理后可得如下结果表1-3某月X市50户居民购买消费品支出分组资料组名居民购买消费品支出分组居民户数1900以下52900～1000131000～11001041100～120010512001400771400～1500481500～1600291600以上1实验二Excel图表制作及编辑实验目的掌握Excel的图表创建熟悉Excel图表编辑技巧二、实验要求掌握Excel的基本图表创建操作方法。通过练习，能够独立运用Excel图表进行数据整理和数据分析。三．实验内容1．统计图表的建立；2．学习、练习Excel的图表编辑；四．实验步骤：1、统计图表的创建与编辑，以表2-1为例，建立柱形图。表2-12005—2009年我国普通高等教育、中等职业教育及普通高中招生人数（万人）年份普通高等教育中等职业教育普通高中20055046568782006546748871200756681084020086088128372009640874830（1）将表2-1中的数据输入Excel表格中，如下图所示（2）移动鼠标到工具栏“插入”按钮，点击图表中的窗口后，从中选择想要的图表类型，用鼠标单击该图形，如下图所示。（3）如下图所示出现空白图形，在图形的任何位置点击鼠标右键，在菜单中点击“选择数据”，给图形添加横轴和纵轴的数据。（4）点击“选择数据”按钮出现如图所示的对话框，在“图例项”下的“添加”中分别将“普通高等教育”“中等职业教育”“普通高中”三个系列的“系列名称”“系列值”添加到图中。在“水平轴标签”中“编辑”年份（6）如图2.6所示，在图表工具中选择图表布局类型，并分别修改“标题”、“横轴”、“纵轴（７）在如图所示，用鼠标右键单击图中某个系列的图标，出现如图所示的编辑对话框，点击“添加数据标签”，在图中显示每个系列的数值。（８）图表基本内容完成以后，需要修改某部分内容时，将鼠标移动到所修改部位，点击鼠标右键，在所列菜单中进行相应的修改工作，最终建立的图表如下图所示2、利用下表中的数据制作饼形图表2-2桂林电子科技大学信息科技学院教师职称分布情况表职称比例高级职称30%硕士学位教师35.8%本科学位教师34.2%（1）将表2-2中的数据输入Excel表格中，如图所示（2）移动鼠标到工具栏“插入”按钮，点击图表中的窗口后，从中选择想要的图表类型，用鼠标单击该图形，如下图所示。（3）如图所示，出现“选择源数据”窗口后，选择“图表数据区域”中选择要建立图表的数据区域A2:B4，再单击下一步按钮。(4)如图所示，在图表工具中选择图表布局类型“布局1”。（5）修改“图表标题”，并移动图中的数据位置，得到最终的饼形图如下：实验三Excel区间估计一、实验目的:掌握Excel区间估计方法。二、实验流程：1. 进入Excel.2. 使用直方图进行初步分析。3. 利用“描述统计”计算平均数和标准差。4. 进行区间估计。三、实验内容以下数据为50辆汽车传动系统出现故障时所行驶的实际里程数。8509232609594657743732534640903246459902393238964194219116803928576343665605858616434261978679985981710176995774121352695687427666998400017206925066770986992235662744256720211844453500792946454486813116269378318943173341852881381145340285586822567753988798求曾经出现过传动系统问题的汽车总体中在出现传动系统问题时所行驶里程均值的95%置信区间。四、实验步骤1、首先将分析数据输入Excel工作表Sheet1单元格A1:J5。如图所示：2、利用“函数”分别计算平均值和标准差（步骤同实验一），得到计算结果为平均里程为73342.1，标准偏差为24899.9。3、进行区间估计[1]. 单击存放结果的单元格E7。[2]. 在单元格E7输入公式：“=73342.1-CONFIDENCE(0.05,24899.9,50)”， 其中函数CONFIDENCE功能为返回均值的置信区间，其使用方法同实验一中所讲函数的使用方法相同，见下图。然后再按Enter键，得到结果为66440.34。[2]. 在在单元格E8输入公式：“=73342.1+CONFIDENCE(0.05,24899.9,50)”，按Enter键，得到结果为80243.86。得到区间估计结果为80243.86。如图所示实验四一元线性性回归模型一、实验目的1．掌握用excel一次性算出回归模型参数的方法和步骤；2．正确分析输出结果并得出正确的回归模型。二、实验内容：某地区1994年到2002年的人均收入和商品零售总额的数据如下：表4.1地区1994年到2002年的人均收入和商品零售总额年份人均收入X（元）商品零售总额(Y亿元)199445026199555032199668044199773062199881070199993089200010501032001116011520021250128试配合适当的回归方程；若2003年该地区人均收入为1300元，试估计2003年商品零售总额为多少？三、操作步骤1．在excel的工作表中输入如表4.1所示的人均收入X和商品零售总额Y的样本数据。2．点击主菜单中的“插入”菜单，在弹出的子菜单中点击“图表”选项，在标准类型选项下选择“XY散点图”，点击下一步，在弹出对话框的输入区域，拖动鼠标选择人均收入X和商品零售总额Y的样本值；系列产生在，选择列；得到散点图如图4.1所示。由图4.1可以看出人均收入X和商品零售总额Y之间存在很明显的线性关系，可以建立一元线性回归模型。3．点击主菜单中的“数据”菜单，在弹出的子菜单中点击“数据分析”在出现的数据分析对话框中选择回归，如图4.2所示。图4.2应用excel软件求回归分析相关参数4．点击图4.2所示对话框中的确定，弹出“回归”对话框，在Y值输入区域，拖动鼠标选择Y样本值C2:C10，在X值输入区域，拖动鼠标选择X样本值B2:B10,“置信度”输入95%，“输出区域”为A12，如图4.3所示。图4.3应用excel软件求回归分析相关参数5．点击图4.3所示中的确定，弹出回归分析有关参数的窗口，如图4.4所示。图4.4应用excel软件求回归分析相关参数四、结果分析“回归统计”中MultipleR为复相关系数；RSquare为可决系数R2=0.988786由图4.4的输出结果，可以得到本例中的回归系数为aˆ=-38.68166,bˆ=，0.1336577故所求回归方程为Yˆ-38.68166+0.1336577X，由于相关系数r=0.99可知两变量是高度相关的，说明该回归模型和回归方程合理反映了人均收入和商品零售总额的相关关系，可以用来进行预测和控制。当人均收入X=1300元时，用回归方程可以估计2003年商品零售总额为135.07335。实验五长期趋势分析一、实验目的1、掌握用移动平均法、趋势方程法和利用函数进行长期趋势分析的方法与步骤；2、测定发展速度和平均发展速度、增长量和平均增长量。二、实验内容：1、表5.1所示为我国2001-2009年国内生产总值，请根据所给数据，分别用移动平均法、趋势方程法和利用函数对2010年的国民生产总值进行预测。表5.1所示为我国2001-2009年国内生产总值年份国内生产总值（亿元）2001109655200212033320031358232004159878200518493720062163142007265810200831404520093353532、计算发展速度和平均发展速度、增长量和平均增长量的值三、实验步骤：1.移动平均法（1）在excel的工作表中输入如表5.1所示的年份和国民国民生产总值的样本数据。点击“数据—数据分析—移动平均”，出现数据分析对话框，如图5.1所示。图5.1数据分析对话框（2）点击“确定”按钮，出现“移动平均”对话框，在其中设置各个参数，输入区域为：$B$2:$B$10；输出区域为：$C$2；选中“图表输出”复选框。如图5.2所示。图5.2移动平均对话框（3）．点击“确定”按钮，即可得到经过移动平均之后的预测数值和绘制的长期趋势图，如图5.3所示图5.3用移动平均法得到的长期趋势线2、趋势方程法（1）点击主菜单中的“数据”菜单，在弹出的子菜单中点击“数据分析”，在出现的数据分析对话框中选择“回归”。弹出“回归”对话框，在Y值输入区域，拖动鼠标选择国内生产总值B2:B10，在X值输入区域，拖动鼠标选择年份值A2:A10,“置信度”输入95%，“输出区域”为C2，如图5.4所示。图5.4应用excel软件求回归分析相关参数(2)点击图5.4所示中的确定，弹出回归分析有关参数的窗口及模型拟合值图，如下图5.5所示。（3）由表中数据可得直线趋势方程为Yˆ-59956611.72+30005.63333X，将X=2010带入趋势方程可得，2010年我国国内生产总值的预测值为354711.27333、利用函数进行趋势预测（1）打开“公式”菜单，选择“插入函数”，在“函数分类”中选择“全部”，在“函数名”中选择“FORECAST”,按确定键，如下图5.5所示。（2）在弹出的“函数参数”对话框中的“X”中输入预测期的时间代码“2010”；在“KNOWN-YS”中输入国内生产总值的样本数据；在“KNOWN-XS”中输入时间代码；点击“确定”按钮。如下图5.6所示。（3）最后返回的数值“354711.3”即为对2010年我国国内生产总值的预测值。4、测定增长量和平均增长量根据1995-2001年河北省国内生产总值，计算逐期增长量、累计增长量和平均增长量。如图5.7所示：图5.7用EXCEL计算增长量和平均增长量资料及结果计算步骤如下：第一步：在A列输入年份，在B列输入国内生产总值。第二步：计算逐期增长量：在C3中输入公式：=B3-B2，并用鼠标拖曳将公式复制到C3：C8区域。第三步：计算累计增长量：在D3中输入公式：=B3-$B$2，并用鼠标拖曳公式复制到D3：D8区域。第四步：计算平均增长量（水平法）：在C10中输入公式：=（B8-B2）/5，按回车键，即可得到平均增长量。5、测定发展速度和平均发展速度以1995-2001年河北省国内生产总值为例，说明如何计算定基发展速度、环比发展速度和平均发展速度。如图5.8所示：图5.8用EXCEL计算发展速度和平均发展速度资料及结果计算步骤如下：第一步：在A列输入年份，在B列输入国内生产总值。第二步：计算定基发展速度：在C3中输入公式：=B3/$B$2，并用鼠标拖曳将公式复制到C3：C8区域。第三步：计算环比发展速度：在D3中输入公式：=B3/B2，并用鼠标拖曳将公式复制到D3：D8区域。第四步：计算平均发展速度（水平法）：选中C10单元格，单击插入菜单，选择函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函