数据整理和数据描述

实验一数据整理和数据描述分析

一、实验目的和要求：

能熟练的进行统计数据的录入、分组、汇总及各种常用统计图表的绘制。

二、实验内容：

1、数据的排序 (1)

2、分类汇总 (1)

3、统计分组 (1)

4、数据透视分析 (10)

5、用Excel绘制统计图 (11)

6、描述性统计 (15)

三、实验步骤

1、数据的排序

①打开“数据整理.xls” 工作簿，选定“等候时间”工作表。

②利用鼠标选定单元格A1:B37区域

③在菜单中选择“数据”中的“排序”选项，则弹出排序对话框。

④在排序对话框窗口中，选择“主要关键字”列表中的“等候时间”作为排序关键字，并选择按“递增”排序。由于所选取数据中已经包含标题，所以在“当前数据清单”中选择“有标题行”，然后单击“确定”按钮，即可得到排序的结果。

2、分类汇总

先选择需要分类汇总的数据区域，然后选择“数据”菜单中的“分类汇总”选项，则打开“分类汇总”对话框。(分类汇总前最好先排序一下)

在“分类字段”的下拉式列表中选择要进行分类的列标题，在“汇总方式”的下拉式列表中选择行汇总的方式，在资料“电器销售量”中分别选择按“订货单位”和“电器种类”进行分类，选择按“求和”进行汇总，单击“确定”按钮，便得到分类汇总的结果。

3、统计分组

用Excel进行统计分组和编制频数分布表有两种方法，一是函数法；二是利用数据分析中的“直方图”工具。

㈠函数法

在Excel中利用函数进行统计分组和编制频数分布表可利用COUNTIF（）和FREQUENCY（）等函数，但要根据变量值的类型不同而选择不同的函数。当分组标志是品质标志时应使用COUNTIF（）函数；当分组标志是数量标志时应使用FREQUENCY（）函数。

⒈COUNTIF（）函数

COUNTIF（）函数的语法构成是：COUNTIF（区域，条件）。具体使用方法举例如下。

【例1.1】某学院某系某毕业班学生共有30人，他们的毕业就业情况如下表1－1。试编制此调查数据的频数分布表。

表1－1 某毕业班学生毕业就业情况表

首先将数据输入Excel单元格中，观察数据的类型个数，在工作表中的空余位置列出各组名称，如图1－1所示。

图1－1 某毕业班学生毕业就业情况资料操作步骤：

操作步骤：

⑴将上述资料输入Excel工作表；在单元格D2中输入“工作单位性质”，在E2中输入“学生人数”，在D3：D6区域中依次输入国家机关、事业单位、企业、自主创业，表示分组方式，同时这也可以表示分组组限。如图1－1所示。

⑵选择单元格E3至E6区域，在“插入”菜单中单击“函数”选项，打开“粘贴函数”对话框；在“函数分类”列表中选择“常用函数”或者选择“统计”也可以，在“函数名”列表中选择“COUNTIF”。如图1－2所示。

图1－2 粘贴函数对话框

⑶单击“确定”按钮，Excel弹出“函数参数”对话框。在数据区域“Range”中输入单元格B2：B31，在数据接受区间Criteria 中输入单元格D3：D6。如图1－3所示。

图1－3 频数分布对话框

⑷由于频数分布是数组操作，所以，此处不能直接单击“确定”按钮，而应按Ctrl +Shift组合键，同时敲“回车”键，得到频数分布。如图1－4所示。

图1－4 频数分布结果

⒉FREQUENCY（）函数

频数分布函数 FEQUENCY（）：可以对一列垂直数组返回某个区域中数据的频数分布。其语法形式为：FREQUENCY(data_array,bins_array)

其中：Data_array为用来编制频数分布的数据，Bins_array为频数或次数的接收区间。

具体使用方法举例如下。

【例1.2】某班50名学生英语成绩如下表，试编制此调查数据的频数分布表。

表1－2 某班学生英语成绩表

操作步骤：

⑴将上述资料输入Excel工作表；在单元格D2中输入“分组”，在E2中输入“分组组限”，在单元格F2中输入“频数”；在D3：D7区域中依次输入60以下、60—70、70—80、80—90、90—100，表示分组方式，但是这还不能作为频数接收区间；在E3：E7区域中依次输入60、70、80、90、100，表示分组组限，作为频数接收区间，它们分别表明60分以下的人数，60分以上、70 分以下的人数等，这与前列分组方式是一致的。如图1－5所示。

图1－5 学生英语成绩资料

⑵选择单元格F3至F7区域，在“插入”菜单中单击“函数”选项，打开“粘贴函数”对话框；在“函数分类”列表中选择“常用函数”或者选择“统计”也可以，在“函数名”列表中选择“FREQUENCY”。如图1－6所示。

图1－6 粘贴函数对话框

⑶单击“确定”按钮，Excel弹出“频数分布”对话框。在数据区域“Data_array”中输入单元格B2：B51，在数据接受区间Bins－array 中输入单元格E3：E7，在对话窗口中可以看到其相应的频数是6，11，19，12，2。如图1

－7所示。

图1－7 频数分布对话框

⑷由于频数分布是数组操作，所以，此处不能直接单击“确定”按钮，而应

按Ctrl +Shift组合键，同时敲“回车”键，得到频数分布。如图1－8所示。

㈡利用“直方图”工具进行统计分组

直方图分析工具是一个用于确定数据的频数分布、累计频数分布，并提供直方图的分析模块。它在给定工作表中数据单元格区域和接收区间的情况下，计算数据的频数和累积频数。

具体使用方法举例如下。

仍以“表11－2 某班学生英语成绩表”为例，试编制此调查数据的频数分布表。

操作步骤：

⒈在“工具”菜单中，单击“数据分析”选项，弹出“数据分析”对话框，如图1－9所示。

图1－9 数据分析对话框

注意：如果用户在Excel的“工具”菜单中没有找到“数据分析”选项，说明用户安装Excel不完整，必须在Excel 中重新安装“分析工具库”内容。具体安装方法如下。

⑴在“工具”菜单中，单击“加载宏”选项。

⑵选中“分析工具库”和“分析工具库-VBA函数”复选框，单击“确定”按钮，将会引导用户进行安装。如图1－10所示。

图1－10 加载宏对话框

如果用户在安装Excel时选择的是“典型安装”，则需要使用CD—ROM进行安装，如果用户在安装Excel时选择的是“完全安装”，则Excel会从硬盘中直接进行安装。

⑶无论是何种情况，安装完毕后，“数据分析”选项会自动出现在Excel的工具菜单中。

⒉在“分析工具”列表框中，单击“直方图”分析工具，则会弹出“直方图”对话框，如图1－11所示。

图1－11 直方图对话框

⒊选择输入选项：

输入区域：在此输入待分析数据区域的单元格引用；

接收区域：表示分组标志所在的区域，在此输入接收区域的单元格引用，该区域应包含一组可选的用来定义接收区间的边界值，这些值应当按升序排列，如本例中的“分组组限”。关于这一点，与前面所讲的FREQUENCY函数一致。

在“输入区域”中，输入$B$10：$B$59；选好接收区域的内容:$E$2：$E$7。

⒋选择输出选项：

输出选项中可选择输出区域、新工作表或新工作薄。在这里选择输出区域，可以直接选择一个区域，也可以直接输出一个单元格，该单元格代表输出区域的左上角，这里常常只输入一个单元格，如本例中$I11，因为我们往往事先并不知道具体的输出区域有多大。

输出选项中还有以下选项：

柏拉图：选中此复选框，可以在输出表中同时按降序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。

累积百分比：选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略累积百分比。

图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。

本例中，我们选中“累积百分比”和“图表输出”两个复选框。

⒌单击“确定”按钮，可得输出结果。如图1－12所示。

图1－12 频数分布和直方图

注意：在默认的直方图中，柱形彼此分开，如果要将其连接起来，操作步骤如下：

⑴单击某个柱形，单击鼠标右键，在弹出菜单中，选择“数据系列格式”选项，弹出“数据系列格式”对话框，如图1－13所示。

图1－13 数据系列格式对话框

⑵在对话框中选择“选项”标签，将间距宽度从150改成0，点上“依数据点分色”，再单击“确定”按钮，得到直方图如图1－14所示。

图1－14 调整后的直方图

4、数据透视分析

数据透视主要是处理以文字形式表现的资料，如性别、职业、文化程度等。分为单变量表格与双变量表格，前者称为单向表，后者称为交叉表。

单向表

例某移动通讯公司要制定新一年的销售计划，需要向市场了解移动用户对数字移动电话类型的需求。

经过问卷设计，在该地区抽取了1000个样本进行问卷调查，获得关于移动电话用户消费行为的一些数据资料，试根据工作表中的数据分析该地区移动电话用户的电话类型偏好。

○1打开“数据整理.xls”工作簿，选定“移动电话调查”工作表。

②单击“数据”菜单中的“数据透视表和图表报告”选项，Excel弹出数据透视表向导对话框。

○3根据向导要求，第1步是指定“数据源类型”和确定所需创建的“报表类型”。选择“数据清单或数据库”作为数据源，选择“数据透视表”作为报表类型。最后单击“下一步”。

④第2步是确定数据区域，本例中数据所覆盖区域为A1:D1001单元格，单击“下一步”按钮，向导给出第3步操作对话框窗口。

⑤第3步确定数据透视表的位置。选择现有工作表，并利用鼠标将数据透视表的位置确定在E1单元格中。

⑥单击“版式”，Excel弹出对话框窗口，选择右边的“数字移动电话类型”字段，并将它拖到左边的“行” 区，再选择“数字移动电话类型”字段，将其拖放在“数据”区域中，显示为“计数项：数字移动电话类型”，以便进行数据汇总。

⑦双击数据区域中的“计数项：数字移动电话类型”字段，打开“数据透视表字段”对话框，在“汇总方式”列表中选择“计数”。

如果想选择数据显示方式，可单击图右侧的“选项”按钮，则EXCEL 弹出“数据显示方式”。在“数据显示方式”的下拉选项中确定“占同列数据总和的百分比”选项, 单击“确定”按钮，则版式调整完毕。再单击“确定”按钮，回到数据透视表步骤3。

⑧单击数据透视表步骤3中的“完成”按钮。

交叉表例该公司市场部经理希望根据上例中的调查结果，确认不同性别的用户，其移动电话类型偏好是否存在着不同。①打开“数据整理.xls”工作簿，选定“移动电话调查”工作表。

②从“数据”菜单中选择“数据透视表和图表报告”选项，得到“数据透视表对话框”窗口。在第1步之中选择“数据清单或数据库”与“数据透视表”，单击“下一步”按钮。

③在第2步中，确定数据区域A1:D1001，单击“下一步”按钮④在第3步中，确定数据透视表的位置，将其位置调整到“H1”单元格。然后，单击“版式”按钮，打开“版式”对话框。

⑤将对话框右边的“数字移动电话类型”字段拖到左边的“行”区作为分组变量，再将右边的“性别” 字段拖到“列”区，作为交叉分析的变量，最后将右边的“性别” 字段拖放到“数据”区域中，并选择计数项。如果希望结果是以百分比的形式显示，则可双击数据区域中的“计数项，性别”，则会弹出“数据透视表字段”对话框。选择“占同列数据总和的百分比”选项，来表示所出现的频率，最后，单击“确定”按钮，又回到“版式”对话框。

⑥单击“确定”按钮，回到第3步，要注意数据透视表的位置定在单元格H1，最后，单击“完成”按钮，得到交叉表。

5、用Excel绘制统计图

利用Excel绘制统计图比较方便，Excel 提供“图表向导”工具，帮助创建适合于数据信息和分析目的的各种图表，Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图等等，各种图形的绘制方法大同小异。下面先简单描述一下图表操作的基本步骤。

（1）使用“图表向导”工具制作图表之前，选定数据所在的单元格，如果希

望数据的行列标题显示在图表中，则选定区域还应包括含有标题的单元格。

（2）单击“插入”菜单中的“图表”选项，按照“图表向导”4个步骤的指令进行操作。

第1步“图表类型”：每种类型的图表都有两种以上的子类型，选中所需要的图形以及它的子图形，单击“下一步”按钮。

第2步“图表数据源”：选定包含数据和行列标志的工作表单元格，即便工作表包含多个行列标志，也能在图表中显示它们。生成图表时，应将各行列标志包含到选定区域中。若预览图表看去很合适，则表示所选数据区正确。单击“下一步”按钮。

第3步“图表选项”：为选定的图表设置某些标准选项。修改这些设置时，随时查看预览图表可以帮助我们确定设置是否合适。单击“下一步”按钮。

第4步“图表位置”：可以将图表放置在工作表上，或者单独置于一张新的图表工作表上，在这一步中，我们为图表工作表命名，或者选择现有工作表的名称，单击“完成”按钮。

具体使用方法举例如下。

【例1.3】根据浙江省主要市2005年年末人口情况资料，制作饼图。

首先把数据输入工作表中，如图1－15所示。

图1－15 2005年年末浙江省主要市人口情况

操作步骤：

（1）选中某一单元格，单击“插入”菜单，选择“图表”选项，弹出“图表向导”对话框。在图表类型中选择饼图，然后在子图表类型中选择一种类型，这里选用系统默认的方式。如图1－16所示。

图1－16 图表向导对话框

（2）单击“下一步”按钮，打开“源数据”对话框，在源数据对话框中填入数据所在区域，。如图1－17所示。

图1－17 源数据对话框

（3）单击“下一步”按钮，打开“图表选项”，选择“标题”选项，输入标题，如本例中“浙江省人口情况”。如图1－18所示。

图1－18 图表选项对话框

（4）单击“下一步”按钮，打开“图表位置”对话框。如图1－19所示。选好图表位置，单击“完成”，即可得到饼图。如图1－20和1－21所示。

图1－19 图表位置对话框

图1－20 2005年浙江省人口数饼图

图1－21 2005年浙江省人口比重饼图

6、描述性统计

操作步骤如下：

⑴打开“工具”菜单，选择“数据分析”选项，打开“数据分析”对话框，选择“描述统计”分析工具。如图2－21所示。

图2－21 数据分析对话框

⑵单击“确定”按钮，打开“描述统计”对话框。在输入区域中输入：$B$1：$B$51，分组方式选择“逐列”，选中“标志位于第一行”复选框，若分组方式为“逐行”，则为“标志位于第一列”。如果输入区域没有标志项，该复选框被清除。在输出区域中任选一单元格（为输出结果左上角单元格地址，如$G$7），单击“汇总统计”，如不选此项，则Excel省略部分输出结果。如图2－22所示。

图2－22 描述统计对话框

⑶单击“确定”按钮，将产生输出结果。如图2－23所示。

图2－23 描述统计输出结果

在输出结果中：

平均—算术平均数

标准误差—估计标准误差

中值—中位数

模式—众数

标准偏差—样本标准差s

样本方差—s平方

峰值—反映钟形分布峰高的一个指标

偏斜度—反映偏斜程度的一个指标

区域—全距，等于最大值减最小值

计数—单位数

从图2－23的结果可以看出，采用分析工具中的描述统计功能，不必利用统计函数或者公式去求解一个一个的统计量，而能直接将平均数、标准差、众数、中位数、最大值、最小值等等一次全部给出，能大大提高统计效率。

上机实验作业：

⒈某区共有30家企业，他们类型情况如下表，试编制此区企业类型的频数分布表。

表1－3 某地区企业类型情况表

2、打开数据“学生成绩”工作表，要求：根据班级进行分类，对班级的平均成绩进行汇总。

3、打开数据“学生成绩”工作表，要求：按照组距上限分为60、70、80、90、100这5个组，用FREQUENCY函数计算各组的频数；并绘制直方图。

4、打开“学生成绩”表，按班进行分类汇总求英语平均成绩。

5、打开“移动电话调查”工作表，按城乡及性别进行分组，绘制交叉表，统计每组中的

每周平均通话时间，并进行含义解释。

6、打开学生成绩表，试用描述性统计计算英语成绩相关指标。

7、洛伦茨曲线与居民收入差异分析

洛伦茨曲线是本世纪初美国经济统计学家洛伦茨根据意大利经济学家巴雷特提出的收入分配公式绘制成的描述收入和财富分配性质的曲线。曲线横轴是累计人口百分比，纵轴是累计收入或财富的百分比。

当一个国家的收入分配完全按人均分配时，同一累计百分比的人口就一定占有相同的累计收入百分比。此时该国的收入分配程度曲线就与对角线重合。

如果绝大多数人口占有很少的财富和收入，而少部分人占有了绝大部分的收入，则该国的曲线就靠近下横轴和右纵轴。

一般来说，国家的收入分配不会是绝对平均的，也不会是绝对不平均的。将任一国家或地区的收入分配情况绘制成洛伦茨曲线就可以观察分析该国家或地区收入分配的平均程度。例某地区1999年的人口及收入情况见图所示，试绘制该地区的洛伦茨曲线。