工作表数据的处理

【实训目的】

（1）熟练掌握自动筛选和高级筛选的方法。

（2）熟练掌握分类汇总操作。

（3）了解创建数据透视表的方法。

【实训内容】

（1）对工作表的内容用自动筛选的方法，筛选出总分大于等于500分及小于430分的女生。

（2）对工作表的内容用高级筛选的方法，筛选出男生思想道德修养与法律基础成绩在85分以上（含85分）、大学英语成绩在80分以上（不含80分）的记录。

（3）对工作表中的数据进行分类汇总，按性别汇总学生的大学英语、体育、计算机基础、总分的平均分。

（4）在原工作表中创建数据透视表。

【实训要求】

在“学生成绩表”工作簿中完成如下操作：

（1）在“原始成绩”工作表中找出总分大于等于500分及小于430分的女生数据。

（2）在“原始成绩”工作表中找出男生思想道德修养与法律基础成绩在85分以上（含85分）、大学英语成绩在80分以上（不含80分）的数据。

（3）在“原始成绩”工作表中分类汇总学生的大学英语、体育、计算机基础、总分的平均分。

（4）在“原始成绩”工作表中创建数据透视表。

【实训操作步骤】

4.4.1 自动筛选

（1）打开“学生成绩表”工作簿，选中“原始成绩”工作表，在J1单元格输入“总分”，利用公式，计算出每个学生的总分。将其另存为“成绩分析”工作簿，保存于D盘。

（2）选择需要进行筛选的单元格列标题D1：J1，单击“数据”选项卡的“排序和筛选”组功能区中的“筛选”按钮，便在D1：J1单元格右下角出现自动筛选下拉菜单“”按钮。

（3）单击下拉菜单“”按钮，在弹出的窗口中选择“数字筛选”｜“大于或等于”选项，然后输入“500”，选中“或”，在第二行下拉菜单中选择“小于”选项，输入“430”，单击“确定”按钮，即筛选出总分大于500分及小于430分的学生，如图4-32和图4-33所示。

图4-32 “自动筛选”下拉菜单

图4-33 “自定义自动筛选方式”窗口

（4）如图4-18所示，“姓名”列后插入一列“性别”，输入数据后，保存到D盘，用自动筛选方式，单击单元格D2的下拉菜单按钮，在弹出的窗口中，只在“女”前面的小方框打钩，单击“确定”按钮完成自动筛选。

【知识小贴士】

在输入数据时，有些数据是有范围限制的，比如以百分制的考试成绩必须是0～100的某个数据，除此之外的数据就是无效数据，要从巨大的数据源中找到无效数据使用人工审核的方式是一件麻烦的事，可以通过Excel2010的数据有效性，快速找出表格中的无效数据。例如：

（1）打开“学生成绩表”工作簿中的“原始成绩”工作表，选中需要审核的区域D2：I23，在“数据”选项卡的“数据工具”组功能区中单击“数据有效性”按钮，弹出“数据有效性”窗口，切换到“设置”选项卡，在“允许”下拉框中选择“小数”，在打开的“数据”下拉框中选择“介于”，将最小值设为“0”，将最大值设为“100”，单击“确定”按钮，如图4-34所示。

（2）设置好数据有效性规则后，在“数据”选项卡的“数据工具”组功能区中打开“数据有效性”按钮右侧的下拉菜单，选择“圈释无效数据”，表格中所有无效数据被一个红色的椭圆形圈出来，错误数据一目了然。由于此例中并无无效数据，可以在设置数据有效性时更改一个数据则可看到效果，如图4-35所示。

图4-34 设置“数据有效性”

图4-35 设置数据有效性后的效果

（3）如果设置了数据有效性后再次输入数据数据，则会弹出错误窗口，提示输入非法，如图4-36所示。

图4-36 非法输入提示窗口

4.4.2 高级筛选

（1）打开D盘的“成绩分析”工作簿，选中“原始成绩”工作表。

（2）复制D2、E2、G2单元格到D27、E27、F27单元格，在D28单元格录入“男”，在E28单元格录入“＞＝85”，在F28单元格录入“＞80”，完成条件区域的设置，如图4-37所示。

图4-37 “条件区域”设置

（3）单击“数据”选项卡的“排序和筛选”组功能区中的“高级”按钮“”，在弹出的窗口中选择“将筛选结果复制到其他位置”，单击“列表区域”右侧的“拾取”按钮“”，选取A3：K24单元格区域，空白处自动填入“原始成绩！＄A＄2：＄K＄24”；单击“条件区域”右侧的“拾取”按钮“”，选取D28：F29单元格区域，空白处自动填入“原始成绩！＄D＄28：＄F＄29”；在“复制到”右侧输入“A31”，然后选择“确定”按钮，筛选后的结果如图4-38所示。

图4-38 高级筛选的结果

4.4.3 数据分类汇总

（1）打开D盘的“成绩分析”工作簿，选中“原始成绩”工作表，在第一行前插入一行输入标题“学生成绩表”并设置好跨列居中。

（2）选择B2：K24单元格区域，单击“数据”选项卡的“排序和筛选”组功能区中的“排序”按钮“”，在弹出的窗口中选择主要关键字为“性别”，排序依据“数值”，次序为“升序”，如图4-39所示。

图4-39 “排序”设置

（3）选择B2：K24单元格区域，单击“数据”选项卡的“分组显示”组功能区中的“分类汇总”按钮“”，在弹出的窗口中“分类字段”选择“性别”，“汇总方式”选择“平均值”，“选定汇总项”复选“体育”“大学英语”“计算机基础”“总分”4项，并选中“替换当前分类汇总”和“汇总结果显示在数据下方”复选框。单击“确定”按钮，结果如图4-40所示。

图4-40 分类汇总的结果

4.4.4 数据透视表

（1）打开“数据透视表数据”工作簿，选中“原始成绩”工作表。选择A2：E22单元格区域，在“插入”选项卡的“表格”组功能区“数据透视表”下拉菜单中选择“数据透视表”，在弹出的“创建数据透视表”窗口中，选择“表／区域”中默认的内容，在“选择放置数据透视表的位置”中选择“现有工作表”，输入“G1”，单击“确定”按钮，结果如图4-41所示。

（2）在右边“数据透视字段列表”中，选择需要添加的字段拖动到“列标签”和“行标签”处，再将要汇总的内容拖动到“数值”处，结果如图4-42所示。

图4-41 “数据透视表”设置

图4-42 “数据透视表字段列表”窗口

（3）可以根据对数据的使用需要，选择“列标签”和“行标签”右下角的下拉菜单进行选择，得到需要的结果。

【巩固与提高练习】

（1）打开素材文件夹中的“分类汇总”工作簿，选择“Sheet1”工作表，将A1：I16单元格的内容复制到“Sheet2”工作表中A1开始的区域中，并根据“职称”字段统计不同职称的基本工资的总额。

（2）打开素材文件夹中的“分类汇总”工作簿，选择“Sheet1”工作表，将A1：I16单元格的内容复制到“Sheet3”工作表中A1开始的区域中，并根据“性别”字段统计不同性别的基本工资、奖金的平均数。

（3）打开素材文件夹中的“分类汇总”工作簿，选择“Sheet1”工作表，将A1：I16单元格的内容复制到“Sheet4”工作表中A1开始的区域中，并根据“性别”字段统计不同性别的职工的人数。