实验项目4数据处理操作

【实验目的】

➢掌握图表制作的操作方法。

➢掌握筛选的操作方法。

➢掌握数据排序、分类汇总的操作方法。

➢掌握数据透视表的操作方法。

➢掌握数据分析和管理的方法。

【实验技术要点】

（1）图表制作和编辑：①Excel 2010中常用图表的建立方法；②图表的设计、布局、格式化方法，了解图表与数据源的关系；③图表类型的修改方法；④图表管理和分析数据的应用。

（2）数据筛选：①自动筛选的建立、编辑方法；②高级筛选的建立、编辑方法；③数据筛选管理和分析数据的应用。

（3）数据排序：①单关键字排序的建立、编辑方法；②多关键字排序的建立、编辑方法；③排序管理和分析数据的应用。

（4）分类汇总：①分类汇总的建立、编辑方法；②分类汇总管理和分析数据的应用。

（5）数据透视表：①数据透视表的建立、编辑方法；②数据透视表管理和分析数据的应用。

【实验内容】

一、任务描述

（1）在工作簿“成绩管理”中创建工作表“成绩汇总表”和“成绩筛选表”，如图1.135和图1.136所示。

（2）在“成绩汇总表”工作表中，制作统计图表。

①创建四门课程分数段人数统计表——柱形图表。

②创建高数课程分数段人数统计表——饼形图表。

（3）在“成绩筛选表”工作表中，完成下列筛选操作。

①使用自动筛选功能，筛选出英语成绩在90分及以上的学生名单。

②使用自动筛选功能，筛选出英语、高数、平均分均在90分以上的优秀学生名单。

③使用高级筛选功能，筛选出英语、高数、政治、计算机均在90分以上的优秀学生名单。

④使用高级筛选功能，筛选出补考学生名单。

图1.135　成绩汇总表

图1.136　成绩筛选表

（4）在“成绩筛选表”工作表中，完成下列排序、分类汇总操作。

①用排序功能对工作表中的数据按性别进行升序排序。

②按性别汇总出男生和女生的人数。

（5）在“成绩筛选表”工作表中，使用数据透视表完成各种数据分析要求。

二、任务实施

（1）在工作簿“成绩管理”中创建工作表“成绩汇总表”和“成绩筛选表”。

在工作簿“成绩管理”中，将工作表“成绩统计表”复制到“Sheet4”并重命名为“成绩汇总表”，按照如图1.135所示输入“成绩汇总表”的内容并保存；再将“成绩汇总表”的内容复制到工作表“Sheet5”中，按照如图1.136所示编辑工作表内容并保存，并将工作表“Sheet5”重命名为“成绩筛选表”。

（2）在“成绩汇总表”工作表中，制作成绩统计图表。

①创建四门课程分数段人数统计表——柱形图表。

a.在“成绩汇总表”工作表中，选择制作图表的数据区域B16：F21。

b.单击【插入】选项卡，再在【图表】组中单击要使用的图表类型【柱形图】，然后再在其子类型【二维柱形图】中单击【簇状柱形图】，即可插入柱形图表。

c.移动图表、调整大小等操作同Word中的图片对象的操作。

d.使用【图表工具】更改图表的设计、布局和格式。选定图表，功能区显示【图表工具】中的三个选项卡：【设计】【布局】【格式】。

在【布局】选项卡的【标签】组中，单击【图表标题】【坐标轴标题】设置图表的标题、坐标轴的标题。

在【布局】选项卡的【标签】组中，单击【绘图区】设置图表绘图区的背景。

设置后的分数段人数统计图表——柱形图如图1.137所示。

图1.137　分数段人数统计图表——柱形图

②创建高数课程分数段人数统计表——饼形图表。

a.在“成绩汇总表”工作表中，选择制作图表的数据区域D4：D21和B16：B21（分数段数据区域和高数成绩数据区域）。（选择不连续区域按住【Ctrl】键）

b.在【插入】选项卡的【图表】组中，单击要使用的图表类型【饼图】，然后再在其子类型【二维饼图】中单击【饼图】，插入的饼形图表如图1.138所示。

图1.138　高数课程分数段人数统计图表——饼形图

（3）在“成绩筛选表”工作表中，完成下列筛选操作。

①使用自动筛选功能，筛选出英语成绩在90分及以上的学生名单。

a.将光标置于数据区域A2：L2中或选择筛选区域A2：L2。

b.在【数据】选项卡的【排序和筛选】组中，单击【筛选】按钮，在数据清单的首行列标题中即出现筛选按钮。

c.单击列标题【英语】中的筛选按钮，显示一个筛选器选择列表，选择【数字筛选】|【大于或等于】命令，操作如图1.139所示。

图1.139　自动筛选操作

d.此时显示【自定义自动筛选方式】对话框，如图1.140所示，进行选项设置。

图1.140　【自定义自动筛选方式】对话框

e.单击【确定】按钮，在数据区即可隐藏英语成绩小于90分的整行数据。

②使用自动筛选功能，筛选出英语、高数、平均分均在90分以上的优秀学生名单。

a.经过上述的筛选，已筛选出英语在90分及以上的优秀学生名单。

b.在英语筛选结果的基础上，再单击列标题【高数】中的筛选按钮，使用和筛选英语同样的方法筛选出英语和高数同时在90分以上的优秀学生名单。

c.在英语和高数筛选结果的基础上，再单击列标题【平均分】中的筛选按钮，使用和筛选英语同样的方法筛选出英语、高数、平均分同时在90分以上的优秀学生名单。

上述操作完成后，筛选结果如图1.141所示。

图1.141　使用“自动筛选”筛选出的优秀学生名单

③使用高级筛选功能，筛选出英语、高数、政治、计算机均在90分以上的优秀学生名单。

a.在“成绩筛选表”中清除自动筛选。

b.在“成绩筛选表”中建立条件区域。

说明：英语、高数、政治、计算机均在90分以上的条件应为“与”运算，故要输入在同一行中，同时要注意，条件区域应和筛选的数据区域隔开。建立的条件区域如图1.144中的B14：E15区域所示。

c.选择要筛选的数据区域A2：L12。

d.在【数据】选项卡的【排序和筛选】组中，单击【高级】按钮，打开【高级筛选】对话框，参数设置如图1.142所示。在此界面上，做如下操作：

图1.142　【高级筛选】对话框一

选中【将筛选结果复制到其他位置】。

在【列表区域】中已自动填入了筛选的数据区域（也可以单击右边的按钮重新选择数据区域）。

单击【条件区域】右边的按钮，在工作表中选择已设置好的条件区域B14：E15，然后再单击右边的按钮返回。

单击【复制到】右边的按钮，在工作表中选择要存放结果的区域A21：L23或起始位置A21，再单击右边的按钮。

e.单击【确定】按钮，即可看到筛选的结果，即如图1.144所示的A21：L23区域。

④使用高级筛选功能，筛选出补考学生名单。

a.在“成绩筛选表”中建立条件区域。

说明：四门课中有不及格的条件应为“或”运算，故要输入在不同行中，同时要注意，条件区域应和筛选的数据区域隔开。建立的条件区域如图1.144中的G14：J18区域。

b.选择要筛选的数据区域A2：L12。

c.在【数据】选项卡的【排序和筛选】组中，单击【高级】按钮，打开【高级筛选】对话框，参数设置如图1.143所示。在此界面上，做如下操作：

选中【将筛选结果复制到其他位置】。

在【列表区域】中已自动填入了筛选的数据区域（也可以单击右边的按钮重新选择数据区域）。(www.chuimin.cn)

单击【条件区域】右边的按钮，在工作表中选择已设置好的条件区域G14：J18，然后再单击右边的按钮返回。

单击【复制到】右边的按钮，在工作表中选择要存放结果的区域A26：L29或起始位置A26，再单击右边的按钮。

d.单击【确定】按钮，即可看到筛选的结果，即如图1.144所示的A26：L29区域。

图1.143　【高级筛选】对话框二

（4）在“成绩筛选表”工作表中，完成下列排序、分类汇总操作。

①用排序功能对工作表中的数据按性别进行升序排序。

方法一：使用【升序】按钮。

a.在“成绩筛选表”中，单击数据区域中要排序的列中某一个单元格，如选择【性别】列C3单元格。

图1.144　高级筛选操作结果效果图

b.在【数据】选项卡的【排序和筛选】组中，单击【排序】中的【升序】按钮，整个数据区即按性别升序排序。

方法二：使用【排序】对话框。

a.在“成绩筛选表”中，选择【性别】列C3单元格。

b.在【数据】选项卡的【排序和筛选】组中，单击【排序】按钮，打开【排序】对话框，如图1.145所示。在【主要关键字】列表框中选择要排序的字段名【性别】，在【排序依据】列表框中选择排序依据【数值】，在【次序】列表框中选择排序次序【升序】。

图1.145　【排序】对话框

c.单击【确定】按钮。

说明：如果需要多关键字排序，就在【排序】对话框中，单击【添加条件】按钮，可添加排序条件。

②按性别汇总出男生和女生的人数。

a.首先要对分类字段【性别】进行排序（升序或降序均可），这个操作前面已完成。

b.选定数据区域中的任意一个单元格或选定整个数据区域A2：L12。

c.在【数据】选项卡的【分级显示】组中，单击【分类汇总】按钮，打开【分类汇总】对话框，设置分类汇总选项，如图1.146所示。

在【分类字段】列表框中，选择分类的字段【性别】。

在【汇总方式】列表框中，选择汇总的计算方式【计数】。

在【选定汇总项】列表框中，选择要汇总的数据列【性别】。

勾选【汇总结果显示在数据下方】。

d.单击【确定】按钮，分类汇总的结果如图1.147所示。

图1.146　【分类汇总】对话框

e.单击图1.147左边的分级显示按键“-”或“+”，可折叠或展开分类汇总项目。

（5）在“成绩筛选表”工作表中，使用数据透视表完成各种数据分析要求。

图1.147　分类汇总结果示意图

①单击数据区域中的任意一个单元格，选择【插入】选项卡，在【表格】选项组中单击【数据透视表】按钮，打开【创建数据透视表】对话框，如图1.148所示。

图1.148　【创建数据透视表】对话框

②单击【确定】按钮，即可创建一个空白的数据透视表，并在窗口的右侧显示【数据透视表字段列表】窗格，在其中选择需要的字段，并在左侧的数据透视表中显示出来，效果如图1.149所示。

图1.149　数据透视表操作界面

③在图1.149中，选择B15单元格，在【活动字段】选项组中单击【字段设置】按钮，打开【值字段设置】对话框，选择【值汇总方式】选项卡，在其列表框中选择【平均值】选项，如图1.150所示。

图1.150　【值字段设置】对话框

④单击【确定】按钮，此时所有学生的高数成绩以及高数成绩的平均分显示出来。

⑤在数据透视表操作界面（见图1.149）中，用户可根据对数据分析的需要，把表格的各字段名拖曳到【报表筛选】【列标签】【行标签】【数值】选项中，并可以对【数值】选项中的字段进行各种运算。

【实验练习】

（1）在Excel中制作表格“学生竞赛成绩统计表”，内容如图1.151所示。按照题目要求完成后，用Excel的保存功能直接存盘。

要求：

①打开“学生竞赛成绩统计表”表格，将Sheet1工作表的A1：F1单元格合并为一个单元格，内容水平居中；按表中第2行中各成绩占总成绩的比例计算“总成绩”列的内容（数值型，保留小数点后1位），按总成绩的降序次序计算“成绩排名”列的内容。

图1.151　学生竞赛成绩统计表

②选取“学号”列（A2：A10）和“总成绩”列（E2：E10）数据区域的内容建立“簇状棱锥图”，图表标题为“成绩统计图”，不显示图例，设置数据系列格式为纯色填充（紫色，强调文字颜色4，深色25%），将图插入到表的A12：D27单元格区域内。将工作表命名为“成绩统计表”并保存。

（2）在Excel中制作表格“新书采购表”，内容如图1.152所示。按照题目要求完成后，用Excel的保存功能直接存盘。

图1.152　新书采购表

要求：

①修改Sheet1工作表的名称为“新书采购表”，将A1：H12的单元格的行高设置为20，列宽设置为10，把单元格A1：H1进行合并居中；

②在“新书采购表”中利用函数分别求出合计本数、金额；

③在“新书采购表”中，利用“新书采购表”中的数据插入图表，要求以书名为x轴，上下半年采购的本数为y轴，图表类型为折线图，图表标题为“新书采购图”；

④将“新书采购表”中的数据复制到Sheet2、Sheet3工作表中，并在Sheet2工作表中筛选出上半年本数和下半年本数都在［80，130］的记录；

⑤在Sheet3工作表中，通过分类汇总，分别统计出理科和文科中合计本数和金额的最大值。

（3）在Excel中制作表格“产品销售情况统计表”，内容如图1.153所示。按照题目要求完成后，用Excel的保存功能直接存盘。

要求：

①将工作表命名为“销售情况统计表”；

②将A1：F1区域合并单元格，字体设置为楷体，字号为22，字体颜色为“黑色，文字1，淡色50%”，水平对齐方式为“居中”，垂直对齐方式为“靠下”；

图1.153　产品销售情况统计表

③利用公式计算“上月销售额”列和“本月销售额”列，结果保留0位小数；

④选取“产品型号”列、“上月销售量”列和“本月销售量”列内容，建立“簇状柱形图”，图表标题为“销售情况统计图”，图例置于底部，将图表插入到表的A14：E27区域。

（4）在Excel中制作表格“成绩表”，内容如图1.154所示。按照题目要求完成后，用Excel的保存功能直接存盘。

图1.154　成绩表

要求：

①将Sheet1工作表中的标题文字“成绩表”设置为黑体，将A1：G1单元格区域合并居中，学号从201401到201409以步长值为1的规律填完整，要求自动填充；

②在Sheet1工作表中利用SUM函数求出总分，利用AVERAGE函数求出各科平均分，平均分结果保留一位小数；

③将Sheet1工作表中的数据按“总分”降序排序，对C3：F11区域中的数据设置条件格式，要求各科成绩大于80的单元格用粗体、蓝色突出显示；

④在Sheet1工作表中，以“姓名”列（B2：B11）为x轴，以“总分”列（G2：G11）为y轴绘制二维簇状柱形图，要求图表标题为“学生成绩总分”，将图表放置到该工作表C16：H32单元格区域；

⑤将Sheet1工作表中的A1：G12单元格中的数据复制到Sheet2工作表的A1：G12单元格中，在Sheet2工作表中，利用自动筛选，筛选出数学和英语都大于80分的学生记录。

（5）在Excel中制作表格“员工工资表”，内容如图1.155所示。按照题目要求完成后，用Excel的保存功能直接存盘。

要求：

①将Sheet1工作表中的标题文字“员工工资表”设置为黑体、20号、蓝色，将A1：I1单元格区域合并居中；

图1.155　员工工资表

②在Sheet1工作表中，利用公式计算每位员工的扣除工资（公式：扣除工资=水电费+公积金）和实发工资（公式：实发工资=职务工资+津贴-水电费-公积金），要求计算出的扣除工资和实发工资数据保留一位小数；

③为Sheet1工作表中的A1：I15区域添加边框，要求外边框为蓝色、粗线条，内框线为蓝色、细线条，标题行底纹为黄色；

④将Sheet1工作表中的A1：I15单元格中的数据复制到Sheet2、Sheet3工作表中A1：I15单元格中，在Sheet2工作表内统计各部门的实发工资总额（提示：分类汇总前先按部门字段升序排序），分类字段为“部门”，汇总方式为“求和”，汇总项为“实发工资”；

⑤在Sheet3工作表中，选取姓名列（A2：A15）和实发工资列（I2：I15）绘制各员工实发工资的二维簇状柱形图，横坐标标题为“姓名”，纵坐标竖排标题为“实发工资”，图表标题为“各员工工资”，将图表放置到该工作表B19：I37单元格区域。