EXCEL实验指导书.doc

课程与实验项目对照表课程名称实验项目编号实验项目名称备注统计学实验一Excel变量数列编制实验实验二Excel分布特征值计算实验实验三Excel趋势方程拟合、综合指数计算实验实验四Excel抽样区间估计、回归分析实验实验一: Excel变量数列编制实验一、实验名称和性质所属课程课程：统计学 实验名称Excel变量数列编制实验学时2实验性质验证 综合 设计必做/选做必做 选做二、实验目的1、了解组距式变量数列的构成；2、了解等距分组中组距、组数、全距的关系；3、掌握组距式变量数列的编制原理；4、学会应用Excel软件“数据分析”功能菜单编制变量数列。三、实验的软硬件环境要求硬件环境要求：计算机1台，带Windows操作系统使用的软件名称、版本号以及模块：Microsoft Office 2000及以上四、知识准备前期要求掌握的知识：变量数列的编制方法；Excel软件的基本操作实验相关理论或原理：变量数列：按数量标志分组，形成的各组按顺序排列，然后列出各组的单位数。组距式数列：每一组由变量值的一个变动区间构成。适用资料：（1）连续型变量或变量值多、变动范围大的离散型变量。计算各组的频数或频率、累计频数或累计频率，最后形成次数分布表或分布图。实验流程：生成图表对话框中输入相关数据打开“直方图”对话框原始数据、组限输入五、实验材料和原始数据根据抽样调查，得有关样本资料如表1表1某月X市50户居民购买消费品支出资料(单位：元插入表头)：83088012301100118015801210146011701080105011001070137012001630125013601270142011801030870115014101170123012601380151010108608101130114011901260135093014201080101010501250116013201380131012701250根据以上数据，以 900、1000、1100、1200、1300、1400、1500、1600为组限，对居民户月消费支出额编制组距式变量数列，并计算居民户月消费支出额的累计频数和频率。同时对分组资料用统计表和统计图的形式来加以表现。六、实验要求和注意事项1、预习实验指导书中实验一的所有内容，结合课程中所讲解的理论，理解该实验的内容、步骤及目的；2、复习变量数列编制的原理及Excel的基本操作方法；3、认真观察实验结果，记录结果；4、对实验结果作简要分析总结。七、实验步骤和内容1.数据输入。分别输入“居民消费品支出”和组限（见附图2 ）2.执行菜单命令工具数据分析，调出“数据分析”对话框，选择“直方图”选项，调出“直方图”对话框。注意，若“数据分析”命令没有出现在“工具”菜单上，则应先使用工具加载宏命令来加载“分析工具库”。3.在“直方图”对话框中，输入相关数据，见附图1。输入区域：$A$1:$A$51接收区域：$B$1:$B$9，接收区域的数值应按升序排列输出区域：$C$1（为输出结果左上角单元格地址）选中“标志”复选框。柏拉图：选中此复选框，可以在输出表中同时按降序排列频率数据。如果此复选框被清除，Excel 将只按升序来排列数据。累积百分比：选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略累积百分比。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。本例中选中“累积百分比”和“图表输出”两个复选框。附图1 直方图对话框八、实验结果和总结实验结果见附图图截取的小一点只需要把有用部分截出来2；附图2 实验结果样本数据在10001100，11001200，12001300 三组中的频率最大，分别为16%、22%、22%(三组合计占60%)，从直方图中明显看出分布存在右偏倾向。九、实验成绩评价标准1、优（或A）：独立完成实验报告，实验步骤、结论正确，字迹端正2、良（或B）：实验步骤、结论基本正确，字迹比较端正3、中（或C）：实验步骤、结论出现错误较多，字迹比较端正4、及格（或D）：不能独立完成实验报告，或实验步骤、结论出现重大错误，或字迹潦草，态度不够认真。5、不及格（或E）：态度很不认真实验二: Excel分布特征值的计算一、实验名称和性质所属课程课程：统计学 实验名称Excel分布特征值计算实验学时2实验性质验证 综合 设计必做/选做必做 选做二、实验目的1、熟悉分布特征值的概念、种类；2、熟悉分布特征值的计算；3、学会应用Excel软件“数据分析”菜单计算有关分布特征值；4。学会阅读显示结果。三、实验的软硬件环境要求硬件环境要求：计算机1台，带Windows操作系统使用的软件名称、版本号以及模块：Microsoft Office 2000及以上四、知识准备前期要求掌握的知识：有关分布特征值的计算方法；Excel软件的基本操作实验相关理论或原理：数据分布特征包含三个不同的侧面：1、分布的集中趋势，即怎样将一个变量的多个观察值汇总为一个数值，并且使这个数值具有原数据的中心趋势或平均值，以反映各数据向其中心值靠拢或聚集的程度。2、分布的离散程度，即怎样汇总变量彼此之间的差别，反映各数据远离其中心值的趋势。3、分布的偏态和峰度，反映数据分布的形状，用偏度指标和峰度指标反映。实验流程：输出结果对话框中输入相关数据同、打开“描述统计”对话框输入原始数据五、实验材料和原始数据实验材料：见实验一表1所示资料。根据实验一表1资料，统计集中趋势、离散趋势等分布特征值及其他相关指标。六、实验要求和注意事项1、预习实验指导书中实验二的所有内容，结合课程中所讲解的理论，理解该实验的内容、步骤及目的；2、复习数据分布特征值的计算及Excel的基本操作方法；3、认真观察实验结果，记录结果；4、对实验结果作简要分析总结。七、实验步骤和内容1.数据输入（见附图3）2.执行菜单命令工具数据分析，调出“数据分析”对话框，选择“描述统计”选项，调出“描述统计”对话框。3.在“描述统计”对话框中，输入相关数据，见附图3：输入区域：A1:A51分组方式：逐列选中“标志位于第一行”复选框，若分组方式为“逐行”，则为“标志位于第一列”。如果输入区域没有标志项，该复选框将被清除，Microsoft Excel将在输出结果中生成适宜的数据标志。输出区域：C1选中“汇总统计”复选框。描述统计工具可生成以下统计指标，按从上到下的顺序，其中包括样本的平均值、标准误差()、中位数(Medium)、众数(Mode)、样本标准差(S)、样本方差(S2)、峰度值、偏度值、极差(Max-Min)、最小值(Min)、最大值(Max)、样本总和、样本个数(n)和一定显著水平下总体均值的置信区间。4. “确定”。附图3“描述统计”对话框八、实验结果和总结实验结果见附图上下这两个图不清晰，最好重新截图图的标号不要放在图中，以便编辑4；附图4实验结果从输出结果可以知道，该月 X 市 50户居民购买消费品支出的平均水平是1 196.2元，众数是1250元；样本最大数据值是1630元，最小值是810元，样本标准差是188.65 元；偏度小于0，说明样本分布呈负(左)偏状态，峰度也小于0，分布是低峰度的。九、实验成绩评价标准1、优（或A）：独立完成实验报告，实验步骤、结论正确，字迹端正2、良（或B）：实验步骤、结论基本正确，字迹比较端正3、中（或C）：实验步骤、结论出现错误较多，字迹比较端正4、及格（或D）：不能独立完成实验报告，或实验步骤、结论出现重大错误，或字迹潦草，态度不够认真。5、不及格（或E）：态度很不认真实验三: Excel趋势方程拟合、综合指数计算实验一、实验名称和性质所属课程课程：统计学 实验名称Excel趋势方程拟合、综合指数计算实验实验学时2实验性质验证 综合 设计必做/选做必做 选做二、实验目的1、掌握趋势方程拟合的原理；2、掌握综合指数的计算原理；3、学会应用Excel软件粘贴函数功能及单元格引用、公式输入等进行趋势方程拟合、综合指数计算。三、实验的软硬件环境要求硬件环境要求：计算机1台，带Windows操作系统使用的软件名称、版本号以及模块：Microsoft Office 2000及以上四、知识准备前期要求掌握的知识：指数曲线方程的拟合；综合指数的计算；Excel软件的基本操作实验相关理论或原理：指数曲线趋势是现象发展的常见趋势，当时间数列的环比发展速度（或增长速度）大体相同时，以拟合指数曲线为宜。指数曲线的方程式为：直接运用最小平方法求指数方程中的参数a、b十分复杂，为此可将指数曲线转化为直线的形式，先求出直线方程中的参数，然后再通过变量替换得到指数方程中的参数。具体做法为：对两边取对数，有：令，则上式变形为：该式为直线方程，应用最小平方法很容易求出式中的A和B，然后求A和B的反对数即得指数方程中的参数a和b，从而可确定指数方程。总指数是反映总体现象中，不能直接加总和不能直接对比的多种不同事物在数量上的总变动的一种特殊相对数，具体分为综合指数和平均数指数两种计算方法。拉氏综合指数将同度量因素固定在基期，帕氏综合指数则将同度量因素固定在报告期。实验流程：指数曲线拟合的实验流程使用组合键对话框中输入相关数据打开“LOGEST”函数对话框原始数据输入简要分析拉氏综合指数计算的实验流程简要分析输入公式、单元格引用等原始数据输入五、实验材料和原始数据指数曲线拟合的实验材料和原始数据实验材料见下表2表2某地区19811995年历年彩色电视机产量资料年份时间产量19811539.4 19822592.0 19833684.0 198441003.8 198551367.7 198661459.4 198771634.4 198881805.1 198992273.5 1990102448.1 1991112631.4 1992123467.8 1993133433.0 1994144383.0 1995154798.1 要求使用LOGEST函数对其拟合指数曲线，并预测19961999年产量。拉氏综合指数计算的实验材料和原始数据实验材料见下表3表3五种商品的销售资料下表重新用word编辑，不要用图及销售额计算表商品名称计量单位商品价格（元）销售量销售额（百元）P0P1q0q1P0q0P0q1P1q0大米猪肉食盐服装电视机百公斤公斤500克件台3001811004500360.020.00.8130.04300.0240084000100002400051026009500015002300061272001512010024000229507800171001502300027540864016800803120021930合计693707559078650要求计算拉氏价格指数和销售量指数。六、实验要求和注意事项1、预习实验指导书中实验三的所有内容，结合课程中所讲解的理论，理解该实验的内容、步骤及目的；2、复习趋势方程拟合的方法、综合指数的计算及Excel的基本操作方法；3、认真观察实验结果，记录结果；4、对实验结果作简要分析总结。七、实验步骤和内容指数曲线拟合的步骤和内容1、在A、B、C三列输入原始数据如附图5所示。附图5LOGEST对话框2、选择E2：F2区域，单击工具栏中的“粘贴函数”快捷键，弹出“粘贴函数”对话框，在“函数分类”中选择“统计”，在“函数名”中选择“LOGEST”函数，则打开LOGEST对话框，如附图5所示：在Known_ys、 Known_xs后分别输入C2:C16、B2:B16。3、按住Ctrl+Shift组合键，再按回车键或“确定”按钮，得到回归统计值计算结果如附图6中E2：F2单元格所示。附图重新截图6回归计算结果4、根据单元格E2，F2中的计算结果可以写出如下估计方程：5、可以根据上面的指数曲线方程计算预测值：在D2单元格中输入公式“=$F$2*$E$2B2” ，将D2单元格中的公式复制到D3：D20元格，各年的预测值便一目了然了。拉氏综合指数计算的步骤和内容1、输入相关数据，如附图 7 所示重新截图。附图7数据输入示意图2、在 F2 单元格中输入公式“=B2*D2”，在 G2 单元格中输入公式“=C2*D2”，在 H2 单元格中输入公式“=E2*B2”。3、选定 F2：H6 单元格，执行菜单命令：编辑填充向下填充，将 F2、G2、H2 单元格中的公式复制到它们下面四行的单元格中。4、在B8单元格中输入公式“=sum(G2:G6)/sum(F2:F6)”，在B9单元格中输入公式“=sum(H2:H6)/sum(F2:F6)”。再将B8、B9单元格的格式设为带有2位小数的百分比数字格式，即可得到如下结果(附图8)。附图8实验结果八、实验结果和总结指数曲线拟合的实验结果和总结实验结果见附图6；随着时间的变化，电视机产量呈指数曲线增长，其趋势方程为：。拉氏综合指数计算的实验结果和总结实验结果见附图8；销售量拉氏指数为113.38%，报告期与基期相比较，五种商品销售量有增有减，平均来讲上升了13.38%；价格的拉氏指数为108.97%，报告期与基期相比较，五种商品价格有增有减，平均来讲上升了8.9%九、实验成绩评价标准1、优（或A）：独立完成实验报告，实验步骤、结论正确，字迹端正2、良（或B）：实验步骤、结论基本正确，字迹比较端正3、中（或C）：实验步骤、结论出现错误较多，字迹比较端正4、及格（或D）：不能独立完成实验报告，或实验步骤、结论出现重大错误，或字迹潦草，态度不够认真。5、不及格（或E）：态度很不认真。实验四: Excel抽样区间估计、回归分析实验一、实验名称和性质所属课程课程：统计学 实验名称Excel抽样区间估计、回归分析实验学时2实验性质验证 综合 设计必做/选做必做 选做二、实验目的1、掌握区间估计的基本原理和方法；2、掌握一元线性回归方程拟合的原理和方法；3、学会应用Excel软件进行区间估计、一元线性回归方程的拟合。三、实验的软硬件环境要求硬件环境要求：计算机1台，带Windows操作系统使用的软件名称、版本号以及模块：Microsoft Office 2000及以上四、知识准备前期要求掌握的知识：抽样区间估计、一元线性回归方程的拟合、Excel软件的基本操作。实验相关理论或原理：抽样区间估计实验相关理论或原理：抽样调查是按照随机原则，从全部研究对象中抽取一部分单位形成样本进行观察，并根据样本的实际数据，对总体的数量特征作出具有一定可靠程度的估计和推断，从而达到对研究对象的认识的一种统计方法。区间估计是根据一定的精确度和可靠程度的要求，用样本指标和抽样误差去推断总体指标可能范围的一种估计方法。它是以样本指标推断总体指标的主要方法。即： 一元线性回归方程的拟合实验相关理论或原理：回归分析：根据变量之间的主从或因果关系，对变量之间的数量变化进行测定。建立一定数学模型，对因变量进行预测或估计的统计分析法。一元一次回归方程又称简单直线回归方程，其基本形式是： 式中，a为直线的截距；b是直线的回归系数。a、b都是待定参数。估计这些参数可用不同的方法，统计中使用最多的是最小平方法，用这个方法求出的回归线是原资料的最适线。就y与x回归线讲，就是。实验流程：抽样区间估计实验流程：定义变量名输入有关名称、公式原始数据输入一元线性回归方程实验流程：在对话框中输入有关内容打开“回归”对话框原始数据输入五、实验材料和原始数据抽样区间估计实验材料和原始数据麦当劳餐馆在7星期内抽查49位顾客的消费额(元)如下所示： 15 24 38 26 30 42 18 30 25 26 34 44 2035 24 26 34 48 18 28 46 19 30 36 42 2432 45 36 21 47 26 28 31 42 45 36 24 2827 32 36 47 53 22 24 32 46 26要求在概率为90%的保证程度下，估计顾客平均消费额的置信区间。一元线性回归方程拟合实验材料和原始数据变量x、y的资料如附图9：附图9原始数据要求：拟合y对x的一元线性回归模型。六、实验要求和注意事项1、预习实验指导书中实验四的所有内容，结合课程中所讲解的理论，理解该实验的内容、步骤及目的；2、抽样区间估计、一元线性回归方程的拟合的原理和方法及Excel的基本操作方法；3、认真观察实验结果，记录结果；4、对实验结果作简要分析总结。七、实验步骤和内容抽样区间估计实验步骤和内容：1、构造工作表。如附图10所示，首先在各个单元格输入以下的内容。其中，A1:A50单元格为样本数据：消费额；B2:B11单元格为计算过程中要用到的样本统