实验4－3　数据管理与分析

一、实验目的

1.掌握数据排序的方法;

2.熟悉数据筛选的方法;

3.掌握数据分类汇总的方法。

二、实验内容

1.建立如图4－22所示表格的数据清单并以“数据管理与分析.xls”存盘;

图4－22　数据清单效果

2.将数据列表复制到Sheet2中，然后按总分降序排序，总分相同的按数学降序排列;

3.将数据列表复制到Sheet3中，然后筛选出“数学”、“语文”及“英语”成绩大于或等于“80”的记录，并将其存放在Sheet4中;

4.将数据列表复制到Sheet5中，然后对全年级的成绩分别按总分、平均分、最高分、最低分等进行分类汇总统计。

三、实验指导

1.建立数据清单

建立数据清单应遵循以下规则:

(1)最好不在一个工作表上建立多个数据清单。

(2)应在数据清单和其他数据间至少留一列或一行空白单元格。

(3)关键数据不要放在数据清单的左右两侧，数据和文本也不要混合放置。

2.数据排序

对一列数据排序:

(1)在数据清单中单击要排序的列字段名或数据单元格。

(2)单击工具栏的升序按钮或降序按钮，即可完成排序。

对多列数据进行排序(本实验选择该排序方法):

(1)单击Sheet2中数据区域的任一单元格即为选中数据清单。

(2)单击【数据】菜单项→选择【排序】命令，出现【排序】对话框(见图4－23)。

(3)在“主要关键字”的下拉列表中选择排序的字段名“总分”，再选择“降序”;在“次要关键字”的下拉列表中选择排序的字段名“数学”，再选择“降序”。

(4)单击【确定】按钮，即可根据清单进行排序。

对一行或多行数据排序:

(1)选择数据清单。

(2)单击【数据】菜单项→选择【排序】命令，出现【排序】对话框(见图4－23)。

图4－23　“排序”对话框

图4－24　“排序选项”对话框

(3)单击【排序】对话框的【选项】按钮，出现【排序选项】对话框(见图4－24)。

(4)在【排序选项】对话框方向中选取“按行排序”。

(5)上步确定完成后回到【排序】对话框，在“主要关键字”和“次要关键字”的下拉列表中选择排序的字段名，再选择“降序”或“升序”。

(6)单击【确定】按钮，即可根据清单进行排序。

3.数据筛选

(1)单击列表中任一单元格，单击【数据】菜单项→【筛选】\【自动筛选】命令，则在工作表的每一个列标题出现一个下拉筛选箭头。

(2)从相应的下拉列表中选择要显示的数据，这里我们选择“数学”下拉列表中的“自定义”(见图4－25)。

图4－25　“数学”下拉列表效果

(3)在【自定义自动筛选方式】对话框中(见图4－26)设置筛选条件，这里我们选择“大于或等于”，单击【确定】，完成数据的筛选操作。

图4－26　“自定义自动筛选方式”对话框

(4)重复步骤(2)(3)，筛选出“语文”及“英语”成绩大于或等于80的记录(见图4－27)。

图4－27　筛选出记录的效果

(5)选取Sheet3中筛选出的数据区域，将其复制粘贴至Sheet4中(见图4－28)。

图4－28　复制粘贴至Sheet4中的效果

(6)若想恢复Sheet3中的数据列表，单击【数据】菜单项→选择【筛选】→将【自动筛选】前的钩号取消，列表恢复原状。

4.分类汇总

成绩处理是学校中经常要做的一项工作。这里介绍一种方法，它除了能对全年级的成绩进行总分、平均分、最高分、最低分等各项目的统计之外，还可同时完成各班各学科的同种项目的统计，这就是Excel中的“分类汇总”。以Sheet1各数据为例，具体步骤如下:

图4－29　“分类汇总”对话框

(1)以“班级”列为“主要关键字”按升序进行排序。

(2)统计各班各学科平均分。单击数据区域中任一单元格，单击【数据】菜单项→选择【分类汇总】命令，出现【分类汇总】对话框(见图4－29)。在“分类字段”下拉列表框中选中“班级”项，在“汇总方式”下拉列表框中选中“平均值”项，在“选定汇总项”下拉列表框中选中“数学”、“语文”、“英语”、“物理”、“化学”等项，单击【确定】按钮，得到各班各学科和全年级各学科的平均分(见图4－30)。

图4－30　得到平均分的效果

(3)显示总体数据。汇总清单可分级显示，这样我们就可以查看其结构。单击分级显示符号、和可创建汇总报表，以隐藏明细数据，而只显示汇总。若只显示全年级的总体数据，单击分级显示符号“1”按钮;若显示各班的总体数据，则单击分级显示符号“2”按钮，就可得到各班各科平均分的直观对比数据(见图4－31);若要还原成详细的汇总数据，也只需单击分级显示符号“3”按钮。

图4－31　显示各班的总体数据效果

(4)若要恢复统计之前的数据显示状态，在【分类汇总】对话框中单击“全部删除”按钮即可。

(5)统计各班各科总分、最大值、最小值，只要在【分类汇总】对话框中的“汇总方式”下拉列表框中分别选中“求和”、“最大值”、“最小值”即可，其他操作步骤同上。

四、实验作业

1.打开实验4－2中完成的“学生学籍管理表.xls”，对“成绩表”中的数据以“总分”为主要关键字按降序排序并填充名次。

2.打开本实验完成的“数据管理与分析.xls”，对Shee1中的数据按各班各科总分、最大值、最小值进行分类汇总。

五、实验报告要求

1.列出实验过程中出现的问题及解决方法。

2.总结本次实验收获并将实验结果保存在磁盘中。