统计学实验指导书(Excel)

1《统计学》实验指导书【试验目的】通过实验教学，使学生验证并加深理解和巩固课堂教学内容，掌握常用统计分析方法在Excel、SPSS的应用，理解和掌握统计分析方法的应用原理、基本条件、实现步骤、结果的内涵等问题。通过实验，使学生能够结合具体任务和条件对社会经济问题进行初步的调查研究，结合自己的专业，在定性分析基础上做好定量分析，提高学生的科研能力和解决实际问题的能力，以适应社会主义市场经济中各类问题的实证研究、科学决策和经济管理的需要。【试验内容】一、Excel中的统计分析功能包括：1、数据的整理与显示，包括数据的排序与筛选、数据透视表与分类汇总、制作频数分布表和绘制各种统计图。2、计算描述统计量，选择适合的描述统计量反映统计数据的集中和离中趋势。3、利用Excel进行推断统计。4、利用Excel进行相关回归分析。试验一：数据的整理与显示（一）数据的筛选和排序1、数据的筛选在对统计数据进行整理时，首先需要进行审核，以保证数据的质量。对审核中发现的错误应尽可能予以纠正。如果对发现的错误无法纠正，或者有些数据不符合调查的要求而又无法弥补时，就要对数据进行筛选。数据筛选有两方面内容：一是将某些不符合要求的数据或有明显错误的数据予以剔除；二是将符合某种特定条件的数据筛选出来，不符合特定条件的数据予以剔除。数据筛选可借助于计算机自动完成。下面通过一个实例说明用Excel进行数据筛选的过程。【例】下表是8名学生4门课程的考试成绩数据（单位：分）。试找出统计学成绩等于75分的学生，英语成绩最高的前三名学生，4门课程都高于70分的学生。8名学生的考试成绩数据首先，选择“工具”菜单，并选择“筛选”命令。如果要筛选出满足给定条件的数据，可使用自动筛选命令。如图所示。2自动筛选(1)这时会在第一行出现下拉箭头，用鼠标点击箭头会出现下面的结果，如图自动筛选(2)要筛选出统计学成绩为75分的学生，选择75，得到结果：自动筛选(3)要筛选出英语成绩最高的前三名学生，可选择“前10个”，并在对话框中输入数据3，得到的结果如图所示。自动筛选(4)如果要筛选出4门课程成绩都大于70分的学生，由于设定的条件比较多，需要使用“高级筛选”命令。使用“高级筛选”时，必须建立条件区域。这时需要在数据清单上面至少留出3行作为条件区域，然3后把数据清单中含有筛选值的数据列复制粘贴到条件区域的一个空行，再在条件标志下面的一行中键入要匹配的条件。如图所示：自动筛选(5)然后，在高级筛选对话框中修改“数据区域”或“条件区域”。结果如图所示。高级筛选(1)单击“确定”后出现如图的结果.高级筛选(2)2、数据的排序数据排序就是按一定顺序将数据排列，其目的是为了便于研究者通过浏览数据发现一些明显的特征或趋势，找到解决问题的线索。排序还有助于对数据检查、纠错，为重新分组或归类提供依据。在某些场合，排序本身就是分析的目的之一。名义级数据，如果是字母型数据，排序有升序与降序之分，升序更常见些，因为升序跟字母的自然排列相同；如果是汉字型数据，排列方式很多，比如按汉字的首位拼音字母排列，这与字母型数据的排序完4全一样，也可按笔画顺序，其中也有笔画多少的升序与降序之分。交替运用不同方式排序，在汉字型数据的检查、纠错中十分有用。区间数据和比数据的排序只有两种，即递增和递减。排序后的数据亦称为顺序统计量（Orderstatistics）。无论是定性数据还是定量数据，其排序均可借助计算机完成。【例】有一张8名学生的学习成绩所构成的数据清单（单位：分），如下表所示。试按总成绩的递增顺序按列排序。表3.1.2学习成绩统计表所谓按列排序就是根据一列或几列中的数据清单进行排序。排序时，Excel将按指定字段的值和指定的“升序”、“降序”排序次序重新设定行。操作步骤如下：（1）单击数据区域内的任何一个单元格。（2）选取“数据”菜单中的“排序”命令，出现“排序”对话框，如图所示。排序(1)（3）在对话框中，单机“主要关键字”右边的下拉列表按钮，在字段下拉列表中选取主关键字段，如“总成绩”，如图所示。5排序(2)（4）制定“升序”还是“降序”，单击主要关键字右边的“递增”单选钮。（5）还可以用同样的方法选择“次要关键字”、“第三关键字”以及升降序。（6）排除字段名行。因为字段名行不参加排序，所以数据清单中如果含有字段名行，应单击“有标题行”单选钮将其排除，否则单击“没有标题行”单选钮。（7）单击“确定”按钮执行排序。各记录的行序被重新组织，记录1为总成绩最低的学生。如图所示。按“总成绩”排序的成绩单当对数据清单按列进行排序且只有一个排序关键字时，可以直接使用标准工具栏中的“升序”或“降序”工具按钮来完成排序，如图所示。操作步骤：（1）单击排序字段中的任意一个单元格。（2）单击工具栏中的“升序”或“降序”工具按钮。“升序”或“降序”工具按钮通常情况下，Excel是按列排序的，但也可以按行排序。所谓按行排序就是根据一行或几行中的数据清单进行排序。排序时，Excel将按指定行的值和指定的“升序”或“降序”排序次序重新设定列。操作步骤如下：（1）单击数据区域内的任何一个单元格。（2）选取“数据”菜单中的“排序”命令，出现“排序”对话框。（3）单击对话框中的“选项”按钮，出现“排序选项”对话框，如图所示。6按行排序(4)在“排序选项”对话框中的“方向”框中，选取“按行排序”单选钮。(5)单击“确定”按钮。以下步骤和按列排序的步骤相同。（二）利用直方图制作频数分布表在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累积频率，用于统计有限集中某个数值元素的出现次数。例如，在一个有20名学生的班级里，可以确定以字母打分（如A、B-等）所得分数的分布情况。直方图表会给出字母得分的边界，以及在最低边界与当前边界之间某一得分出现的次数。出现频率最多的某个得分即为数据组中的众数。“直方图”对话框接收区域（可选）在此输入接收区域的单元格引用，该区域应包含一组可选的用来定义接收区间的边界值。这些值应当按升序排列。只要存在的话，MicrosoftExcel将统计在当前边界点和相邻的高值边界点之间的数据点个数。如果某个数值等于或小于某个边界值，则该值将被归到以该边界值为上限的区间中。所有小于第一个边界值的数值将一同计数，同样所有大于最后一个边界值的数值也将一同记数。如果省略此处的接收区域，MicrosoftExcel将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。柏拉图选中此复选框，可以在输出表中同时按降序排列频率数据。如果此复选框被清除，MicrosoftExcel将只按升序来排列数据，即省略输出表中最右边的三列数据。累积百分率选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略累积百分比。图表输出选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。实例应用：7学生历次考试成绩统计，按照一定区间生成频数分布表，使用“直方图”分析工具来完成。（数据文件名：直方图.xls）操作步骤：1、打开数据“直方图.xls”，如图2、选择“数据分析”对话框中“直方图”，跳出“直方图”对话框。3、在“输入区域”对应编辑框输入学生成绩数据的引用。（$B$2:$B$15）4、在“接受区域”对应编辑框输入数据划分单元格的引用。（$A$18:$A$22）5、看是否在输入栏里是否选择了标志单元格，考虑选定“标志”。在输出选项中选择“新工作表组”，在其对应编辑框中输入输出工作表名称，如；“图表输出”。选择“帕拉图”、“累积百分率”、“图表输出”选项。6、单击确定。结果输出如下8在图中显示的统计结果中，可以看见输出的内容分为两部分，一部分是数据表示形式，一部分是直方图形式。在数据表部分，显示每个区间中的人数及累计百分率数值。通过该统计结果，我们可以知道，在第一次考试中有6人在100~85分之间，5人在85~75之间，1人在75~65之间，1人在65以下。应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：用鼠标左键单击任一直条，然后右键单击，在弹出的快捷菜单中选取数据系列格式，弹出数据系列格式对话框，如图1-8所示：图1-8数据系列格式对话框在对话框中选择选项标签，把间距宽度改为0，按确定后即可得到直方图，如图1-9所示：图1-9调整后的直方图（三）数据透视表假设我们已经建立了一张统计表，若要建立一个交叉式的复合分组统计表，可使用数据透视表功能。“数据透视表”对话框9打开数据透视表和数据透视图向导后，选择待分析数据的数据源及所需创建的报表类型，然后单击下一步选择数据区域，再单击下一步选择数据透视表的保存位置，最后单击完成。实例应用：如果我们已经建立了一张某单位部分人事统计表，利用数据透视表按性别统计各职称的基本工资总额（数据文件名：数据透视表.xls）操作步骤：1、打开数据“数据透视表.xls”，如图2、单击“数据”菜单选择“数据透视表和数据透视图”命令进入数据透视表和数据透视图向导，创建的报表类型选择数据透视表。3、单击下一步，键入或选定建立数据透视表的数据源区域，4、单击下一步，在数据透视表显示位置中选择新建工作表5、单击完成，则在新建工作表生成数据透视表107、将“职称”拖至行字段处；将“性别”拖至列字段处；将“基本工资”拖至数据项处。即得到数据透视表--按性别统计各职称的基本工资总额为内容的交叉式的复合分组统计表。（四）统计图统计图是统计资料另一种常用的表达方式。它是利用几何图形（点、线、面、形）或其它图形把所研究对象的特征、内部结构等相互关联的数量关系绘制成简明的图形。它由坐标系、图形、图例组成。统计图表示的数量关系形象、直观、明白，可以使人们一目了然地认识客观事物的状态、形成、发展趋势或在某地区上的分布状况等，故它在统计工作中使用得也非常广泛。图形的制作均可由计算机来完成。统计图有两种类型：一是单式图，一个图只用来显示一种现象的数量特征；二是复合图和叠加图，用一个图同时显示几种数量的分布或变化情况。下面介绍几种常用的统计图。1、条形图（Bar）。条形图可用于显示离散型变量的次数分布。最主要是显示顺序数据和分类数据的频数分布。条形图是用宽度相同的条形的高度或长短来表示数据的多少的图形。条形图可以横置或纵置，纵置时也称为柱形图。此外，条形图有单式、复式等形式。在表示分类数据的分布时，用条形图的高度或长度来表示各类别数据的频数或频率。绘制时，各类别可以放在纵轴，称为条形图；也可以放在横轴，称为柱形图。条形图（Bar）用于显示离散型变量的次数分布，用条形的高度来表示变量值的大小，如图所示。11类别数据条形图离散型变量次数分布条形图2、直方图（Histogram）和折线图。用于显示连续型变量的次数分布。直方图是用矩形的宽度和高度（即面积）来表示频数分布的图形。在平面直角坐标中，用横轴表示数据分组，纵轴表示频数或频率，这样，各组与相应的频数就形成了一个矩形，即直方图。在直方图中，实际上是用矩形的面积来表示各组的频数分布。在直方图基础上添加趋势线，形成折线图。例如根据资料绘制的直方图和折线图。某生产车间50名工人日加工零件频数分布直方图某生产车间50名工人日加工零件频数分布折线图直方图与条形图不同。首先，条形图是用条形的长度（横置时）表示各类别频数的多少，其宽度（表示类别）则是固定的；直方图是用面积表示各组频数的多少，矩形的高度表示每一组的频数或频率，宽度则表示各组的组距，因此，其高度与宽度均有意义。其次，由于分组数据具有连续性，直方图的各矩形通常是连续排列，而条形图则是分开排列。最后，条形图主要用于展示分类数据，而直方图主要用于展示数值型数据。3、圆形图（饼图Pie）。用于显示定类变量的次数分布。它是用圆形及圆内扇形的面积来表示数值大小的图形。饼图主要用于表示总体中各组成部分所占的比例，对于研究结构性问题十分有用。在绘制饼0246810121416105－110110－115115－120120－125125－130130－135135－140