excel实习报告.doc

统计学实验Excel实习学院：经济与管理学院班级：09经济姓名：赵宛新学号：2009165161实验一Excel基本操作（用Excel搜集与整理数据）实验目的：掌握用EXCEL进行数据的搜集整理和显示实验步骤：一、用Excel搜集数据【例1-1】某百货公司连续40天的商品销售额（单位：万元）如下：图：1-1总体连续40天的商品销售额表输入各总体单位的编号后，可按以下步骤操作：第一步：单击工具菜单，选择数据分析选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项），打开数据分析对话框，从中选择抽样。如图1-2所示：图1-2数据分析对话框第二步：单击抽样选项，弹出抽样对话框。如图1-3图1-3 抽样对话框第三步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$J$4，系统将从A列开始抽取样本，然后按顺序抽取B列至J列。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。第四步：在抽样方法项下，有周期和随机两种抽样模式：“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。如我们要在40个总体单位中抽取10个，则在“间隔”框中输入4。“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。第四步：指定输出区域，在这里我们输入$A$8，单击确定后，即可得到抽样结果，如图1-4图1-4等距抽样结果二、用Excel进行统计分组【例1-2把某百货公司连续40天的商品销售额资料输入工作表，如图1-5所示：图1-5工人月产量资料然后按以下步骤操作。第一步：在工具菜单中单击数据分析选项，从其对话框的分析工具列表中选择直方图，打开直方图对话框。如图1-6所示：图1-6直方图对话框第二步：在输入区域输入$A$2:$J$5，在接收区域输入$A$12:$A$14。接收区域指的是分组标志所在的区域，假定我们把分组标志输入到A12:A14单元格，注意这里只能输入每一组的上限值，即29,39,49。第三步：选择输出选项，可选择输入区域、新工作表组或新工作薄。我们在这里选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格（代表输出区域的左上角），这里我们推荐只输入一个单元格（本例为A12），因为我们往往事先并不知道具体的输出区域有多大。第四步：选择图表输出，可以得到直方图；选择累计百分率，系统将在直方图上添加累积频率折线；选择柏拉图，可得到按降序排列的直方图。图1-7频数分布和直方图三、用Excel作统计图【例1-3】我们这里采用保险公司2004年资产情况资料，看一下如何作饼图。首先把数据输入到工作表中，如图1-10所示：图1-10保险公司2004年资产情况按以下步骤可作出饼图：第一步：选中某一单元格，单击插入菜单，选择图表选项，弹出图表向导对话框。如图1-11：图1-11图表向导对话框第二步：在图表类型中选择饼图，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。然后单击下一步按钮，打开源数据对话框。如图1-12所示：图1-12源数据对话框第三步：在源数据对话框中填入数据所在区域，单击完成按钮，即可得如1-13所示的饼图：图1-13饼图实验二 描述统计分析过程（用EXCEL计算描述统计量）实验目的：用EXCEL计算描述统计量实验步骤：EXCEL中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。一、 用函数计算描述统计量常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。一般来说，在Excel中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。这里我们仅介绍如何用函数计算。(一)众数【例1-4】：为了解某百货公司商品销售情况，随机抽取40天，销售额如下：41,25,29,47,38,34,30,38,43,40,46,36,45,37,37,36,45,43,33,44,35,28,46,34,30,37,44,26,38,44,42,36,3737,49,39,42,32,36,35。用函数方法求众数，应先将40天的销售额数据输入A1：A40单元格，然后单击任一空单元格，输入“=MODE（A1：A40）”，回车后即可得众数为37(二)中位数仍采用上面的例子，单击任一空单元格，输入“=MEDIAN（A1：A40）”，回车后得中位数为37。(三)算术平均数单击任一单元格，输入“=AVERAGE（A1：A40）”，回车后得算术平均数为37.85。(四)标准差单击任一单元格，输入“=STDEV（A1：A40）”，回车后得标准差为5.959478552。二、 描述统计工具量的使用仍使用上面的例子，我们已经把数据输入到A1：A40单元格，然后按以下步骤操作：第一步：在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框，如图1-14所示：图1-14描述统计对话框第二步：在输入区域中输入$A$1:$A$40，在输出区域中选择$C$1，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第K大值和第K小值会给出样本中第K个大值和第K个小值。第三步：单击确定，可得输出结果，如图1-15所示：图1-15描述统计输出结果上面的结果中，平均指样本均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数；标准偏差指样本标准差，自由度为n-1；峰值即峰度系数；偏斜度即偏度系数；区域实际上是极差，或全距。实验三 用EXCEL进行参数估计和假设检验实验目的：用EXCEL进行参数估计和假设检验实验步骤：在EXCEL中，进行参数估计只能使用公式和函数的方法，而假设检验除以上两种方法外，还可以使用假设检验工具。一、用EXCEL进行区间估计【例1-5】：某百货公司随机抽取40天的商品销售额如下：41,25,29,47,38,34,30,38,43,40,46,36,45,37,37,36,45,43,33,44,35,28,46,34,30,37,44,26,38,44,42,36,3737,49,39,42,32,36,35。求在概率90%的保证下，商品平均销售额的估计区间。如图1-16：图1-16参数估计数据及结果计算方法如下：第一步：把数据输入到A2：A41单元格。第二步：在C2中输入公式“=COUNT（A2：A41）”，C3中输入“=AVERAGE（A2：A41）”，在C4中输入“STDEV（A2：A41）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.90，在C7中输入“=C2-1”，在C8中输入“=TINV（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3-C9”，在C11中输入“=C3+C9”。在输入每一个公式回车后，便可得到上面的结果，从上面的结果我们可以知道，顾客平均消费额的置信下限为36.26238，置信上限为39.43762。关于总体方差的估计、总体比例的估计等可按类似方法进行。二、用EXCEL进行假设检验用EXCEL函数和公式进行假设检验与参数估计类似，这里介绍一下假设检验工具的使用。在EXCEL中，假设检验工具主要有四个，如图1-17所示：图1-17数据分析对话框平均值的成对二样本分析实际上指的是在总体方差已知的条件下两个样本均值之差的检验，准确的说应该是Z检验，双样本等方差检验是总体方差未知，但假定其相等的条件下进行的t检验，双样本异方差检验指的是总体方差未知，但假定其不等的条件下进行的t检验，双样本平均差检验指的是配对样本的t检验。我们介绍一下Z检验。【例1-6】：某电灯生产厂家为了提高灯泡的使用寿命而采用了新型灯泡制造技术取代原有技术，现从原有技术和现有技术中各抽一个样本进行寿命测试（表示寿命的一种考核指标）其结果如下：原有技术（X） 102.0 99.5 104.0 100.5 101.8 102现有技术（Y） 99.8 100.0 102.0 98.5 103.0 100.0根据以往经验知寿命，且，试在水平上比较新技术在使用寿命有无显著提高。计算步骤如下：第一步：输入数据到工作表。第二步：单击工具菜单，选择数据分析选项，弹出对话框后，在其中选择双样本平均差分析，弹出对话框如图1-18所示：图1-18双样本平均差分析对话框第三步：按上图所示输入后，按确定按钮，得输出结果如图1-19：图1-19双样本平均差分析结果在上面的结果中，我们可以根据P值进行判断，也可以根据统计量和临界值比较进行判断。如本例采用的是单尾检验，其单尾P值为0.17，大于给定的显著性水平0.05，所以应该接受原假设，即新型灯泡技术没有明显提高；若用临界值判断，得出的结论是一样的，如本例Z值为0.938194，小于临界值1.644854，由于是右尾检验，所以也是接受原假设。实验四 用EXCEL进行方差分析实验目的：学会在计算机上利用EXCEL进行单因素方差分析和有交互作用的双因素方差分析以及无交互作用双因素方差分析。实验步骤：在EXCEL中，进行方差分析可以使用公式和函数的方法，除以上两种方法外，最常用的是使用方差分析工具。方差分析可以用来检验来多个均值之间差异的显著性，可以看成是两样本t检验的扩展。统计学原理中涉及的方差分析主要包括单因素方差分析、两因素无交互作用的方差分析和两因素有交互作用的方差分析三种情况。虽然Excel可以进行这三种类型的方差分析，但对数据有一些限制条件，例如不能有缺失值，在两因素方差分析中各个处理要有相等的重复次数等；功能上也有一些不足，例如不能进行多重比较。而在方差分析方面SPSS的功能特别强大，很多输出结果已经超出了统计学原理的范围。一、单因素方差分析用Excel进行方差分析时对数据格式有特殊的要求，不能使用我们前面讲过的列表格式，而应使用一种特殊的表格形式：在单因素方差分析中因素的不同水平作为表格的列（或行），在不同水平下的重复次数作为行（或列）；在双因素方差分析时以一个因素的不同水平作为列，以另一个因素以及试验的重复次数作为行。这种设计使得Excel最多只能进行两个因素的方差分析。【例1-7】：根据年龄对于购房意愿是否有显著影响，并找到了相关数据。试用方差分析的方法比较在5%的显著性水平下能否认为购房者年龄对他们的统计购房意愿有显著影响。在Excel中，先对数据按学习兴趣排序，然后把统计成绩整理成表1-1的形式。从分组的结果看，在不同的因素水平下的观测个数并不相同。这种情况下的方差分析与等重复的方差分析原理完全相同，只是计算公式上稍有不同。当然使用统计软件进行计算时完全看不出这种影响。 表1-1 按不同年龄分组的购房意愿x/y如果有合适的房源，会出手没有购房意愿打算再观望一段时间，看清房价走势再出手小计20岁及以下5(27.78%)5(27.78%)8（44.44%）1821-2525(27.78%)33(36.67%)32(35.56%)9026-3036(57.14%)4(6.35%)23(36.51%)6331-4030(65.22%)5(10.87%)11(23.91%) 4641-502(33.33%)1(16.67%)3(50.00%)651-600(0.00%)0(0.00%)2(100.00%)260以上0(0.00%)0(0.00%)0(0.00%)0在Excel的分析工具库中选择“方差分析：单因素方差分析”，指定相应的数据区域和显著性水平，点击“确定”后的输出结果如表1-2和1-3。表1-2 单因素方差分析的汇总表方差分析：单因素方差分析组观测数求和平均方差行 131863行 23903019行 336321259行 434615.33333333170.3333行 53621行 6320.6666666671.333333行 73000表1-3 单因素方差分析的方差分析表方差分析差异源SSdfMSFP-valueF crit组间2438.952386406.49206356.2721040.002262.847726组内907.3333331464.80952381总计3346.2857120从以上分析结果可知，方差分析中得出的p-值等于0.00226小于0.05，因此应拒绝零假设，检验的结论是年龄对于购房意愿有显著影响。通过这次试验分析，了解并学会了利用excel来进行单因素方差分析，对于以后市场调查分析数据会有很大的帮助，通过这次数据分析，直观的感受到年龄对于购房意愿的影响，其中，“如果有合适的房源，会出手”的有93%都是21-40岁的人，这部分人有购买意愿也具有购买的能力，这也反应了现实问题，年轻人为买房而承受着很大的压力。在我看来买房是一件很重要的事，但不用为了买房而忽略其他重要的事。 二、用Excel进行无重复双因素方差分析无重复的双因素方差分析可以同时分析两个因素对因变量的影响。下面我们来看一个简单的例子。【例1-8】：为了比较三个不同专业毕业生的收入设计了以下实验：从三个专业毕业两年的毕业生中，按毕业时的平均学习成绩（分为6个等级）各选择一名学生进行调查，调查结果如表1-4。显著性水平=0.01。试分析不同专业毕业生的收入有显著差异吗？学习成绩对收入有显著影响吗？1-4 不同专业毕业生的月收入（百元）平均成绩会计工商经济A+ 524743A 44 3937B+ 34 3529B 372833C+ 343328C 292624在Excel表格中输入相应的数据，从“数据分析”中选择“方差分析：无重复双因素分析”，在对话框中输入数据区域、显著性水平和输出方式，得到方差分析表如表1-5。从分析结果可以看出，行因素（成绩）的p-值为7.77E-06，列因素（专业）的p-值为0.003332，都远远小于0.01，可见两个因素对毕业后的收入都有显著影响。表1-5 无重复双因素方差分析表差异源SSdfMSFP-valueF crit行822.44445164.488931.905177.77E-063.325835列109.7778254.8888910.646550.0033324.102821误差51.55556105.155556总计983.777817三、用Excel进行有重复双因素方差分析与无重复的双因素方差分析不同，有重复的双因素方差分析可以分为有交互作用和无交互作用两种情况。Excel只能分析有交互作用的情况。【例1-9】： 一家保险公司在A、B、C、D四个地区销售汽车、健康和财产三种保险。为了比较不同地区三类专业销售人员的收入，随机收集了一组数据（表1-6）。假设数据是正态的和等方差的。试在=0.05的显著性水平下分析各因素及其交互作用的影响是否显著。表1-6 保险销售人员的月收入数据（百元）地区ABCD产品类型143.242.941.543.341.543.138.739.537.839.139.842.5242.339.242.839.938.938.642.138.939.641.140.639.2341.241.838.344.638.939.837.743.142.140.638.243.9在Excel表格中输入相应的数据，从“数据分析”中选择“方差分析：有重复双因素分析”，在对话框中输入数据区域、显著性水平和输出方式，并指明每种处理重复的次数（这里为3次）（图1-20）。注意在有重复双因素分析的“输入区域”中要包括行和列的分组状况，否则Excel会报错。最后得到的方差分析表如表1-7。从分析结果可以看出，样本因素（行因素，即产品类型）的p-值为0.44，说明销售的保险类型对销售人员的平均收入没有显著差异。可见两个因素对毕业后的收入都有显著影响；列因素（地区）的p-值为0.1790.05，说明地区因素对收入有显著影响；两个因素的交互作用的p-值为0.00930.05，说明二者的交互作用对销售人员的平均收入有显著影响。图1-20 有重复双因素方差分析的对话框 表1-7 有重复双因素方差分析表差异源SSdfMSFP-valueF crit样本4.17722222.0886110.8477840.4407943.402826列13.0963934.3654631.7719770.179383.008787交互54.9561169.1593523.7178560.0093462.508189内部59.12667242.463611总计131.356435实验五 用EXCEL进行时间序列分析实验目的：用EXCEL进行时间序列分析实验步骤：一、测定增长量和平均增长量【例1-10】：根据2004-2010年外资公司资产情况，计算逐期增长量、累计增长量和平均增长量。如图1-21图1-21用EXCEL计算增长量和平均增长量资料及结果计算步骤如下：第一步：在A列输入年份，在B列输入外资公司资产。第二步：计算逐期增长量：在C3中输入公式：=B3-B2，并用鼠标拖曳将公式复制到C3：C8区域。第三步：计算累计增长量：在D3中输入公式：=B3-$B$2，并用鼠标拖曳公式复制到D3：D8区域。第四步：计算平均增长量（水平法）：在C10中输入公式：=（B8-B2）/5，按回车键，即可得到平均增长量。二、测定发展速度和平均发展速度【例1-11】：以2004-2010年外资公司资产情况为例，说明如何计算定基发展速度、环比发展速度和平均发展速度。如图1-22图1-22用EXCEL计算发展速度和平均发展速度资料及结果第一步：在A列输入年份，在B列输入外资公司资产。第二步：计算定基发展速度：在C3中输入公式：=B3/$B$2，并用鼠标拖曳将公式复制到C3：C8区域。第三步：计算环比发展速度：在D3中输入公式：=B3/B2，并用鼠标拖曳将公式复制到D3：D8区域。第四步：计算平均发展速度（水平法）：选中C10单元格，单击插入菜单，选择函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：D8即可。三