任务情景

小李是他们班的学习委员,每学期都需要对本班学生的成绩进行分析、汇总,以前他都是用手工完成这些任务,计算量大、任务重,每次都需要好几天的时间。自从学习了Excel后,觉得Excel功能强大,尤其是在对数据的处理方面等。为了减轻自己的负担,他认真向老师请教,掌握了Excel在数据管理和分析上的相关知识。

任务目标及效果

分析、汇总班级学生成绩单,对全班学生进行总分排名,找出挂科学生,通知学生本人准备补考,找出成绩优异的学生进行表扬,并对全班学生成绩进行综合分析,观察男女生的学生成绩之间的差异。如图4-42、图4-43、图4-44所示。

图4-42 学生成绩汇总表效果图

图4-43 学生成绩排名表效果图

图4-44 学生成绩男女差异表效果图

任务分析

1.计算全班学生的平均成绩和总分,按照总分由高到低的顺序对全班学生进行排名。

2.使用筛选功能,筛选出平均分低于80分的学生名单。

3.使用高级筛选功能,筛选出单科成绩不合格(低于60分)的学生名单,通知他们补考。

4.利用分类汇总功能将全班学生按照性别分类汇总,分析性别对各学科成绩的影响。

5.分析男女生各科成绩,生成数据透视表。

知识准备

Excel2010具有强大的排序与筛选功能,排序是将工作表中的数据按照一定的规律进行显示,而筛选则只在工作表中显示符合一个或多个条件的数据。通过排序和筛选,可以直观地显示工作表中的数据。

1.排序数据

(1)简单排序。

在工作表中选择需要进行排序的单元格区域,执行“数据”选项标签|“排序和筛选”功能区|“升序”或“降序”命令。

(2)自定义排序。

用户根据需要进行自定义排序,选择数据区域,执行“数据”选项标签|“排序和筛选”功能区|“自定义排序”命令,在弹出的对话框中设置排序关键字,如图4-45所示。

图4-45 “排序”对话框

2.筛选数据

筛选数据是从无序且庞大的数据清单中找出符合指定条件的数据,并且剔除无用的数据,从而帮助用户快速、准确地查找与显示有用数据。

(1)自动筛选。

执行“数据”选项标签|“排序和筛选”功能区|“筛选”命令,即可在所选单元格中显示“筛选”按钮,用户可以单击该按钮,在下拉列表中选择“筛选”选项。

图4-46 “自定义自动筛选方式”对话框

(2)自定义自动筛选。

点击“筛选”按钮,在打开的下拉菜单中执行“文本筛选”|“自定义筛选”命令,系统会自动弹出“自定义筛选”对话框,如图4-46所示。

在该对话框中最多可以设置两个筛选条件,用户可以自定义等于、不等于、大于、小于等12种筛选条件。

特别提示

☉与 同时需要满足两个条件。

☉或 需要满足两个条件中的一个条件。

同时,用户还可以通过下列两种通配符实现模糊查找。

☉? (问号)任何单字符。

☉∗ (星号)任何数量字。

(3)高级筛选。

①制作筛选条件。

在工作表第一行前插入三行空白行,在第一行中输入字段名称,该字段名称与需要筛选区域中数据的字段名称一致。在第二行中,根据字段名称设置不同的筛选条件,如图7-47所示。

图4-47 制作筛选条件

特别提示

在制作条件格式时,如果在同一行中输入多个筛选条件,则筛选的结果必须同时满足多个条件,如果在不同行中输入多个筛选条件,则筛选结果只需要满足其中任意一个条件。

②制作筛选参数。

执行“数据”选项标签|“排序和筛选”功能区|“高级”命令,在弹出的“高级筛选”对话框中设置筛选参数,如图4-48所示。

图4-48 设置筛选参数

该对话框中主要包括下列选项。

①在原有区域显示筛选结果:表示筛选结果显示在原有数据清单位置,原有数据区域被覆盖。

②将筛选结果复制到其他位置:表示筛选后的结果将显示在指定的单元格区域中,与原表单并存。

③列表区域:设置筛选数据区域。

图4-49 “分类汇总”对话框

④条件区域:设置筛选条件区域,即新制作的筛选条件区域。

⑤复制到:设置筛选结果的存放位置。

⑥选项不重复的记录:选中该复选框,表示在筛选结果中将不显示重复的数据。

⑦最后,单击“确定”按钮,即可获得筛选结果。

3.分类汇总数据

(1)创建分类汇总。

在创建分类汇总之前,需要对数据进行排序,以便将数据中关键字相同的数据集中在一起。选择数据区域中任意单元格,执行“数据”选项标签|“分级显示”功能区|“分类汇总”命令,在弹出的“分类汇总”对话框中设置各项选项即可,如图4-49所示。

该对话框主要包括下列几种选项。

①分类字段:用来设置分类汇总的字段依据,包含数据区域中的所有字段。

②汇总方式:用来设置汇总函数,包含求和、平均值、最大值等11种函数。

③选定汇总项:设置汇总数据列。

④替换当前分类汇总:表示在进行多次汇总操作时,选中该复选框可以清除前一次汇总结果,按本次分类要求进行汇总。

⑤每组数据分页:选中该复选框,表示在打印工作表时,将每一类分别打印。

⑥汇总结果显示在数据下方:选中该复选框,可以将分类汇总结果显示在本类最后一行(系统默认是放在本类的第一行)。

(2)嵌套分类汇总。

嵌套汇总是对某项指标汇总,然后将汇总后的数据再汇总,以便做进一步的细化。首先将数据区域进行排序,执行“数据”选项标签|“分级显示”功能区|“分类汇总”命令,在弹出的“分类汇总”对话框中设置各项选项,单击“确定”按钮即可。

4.创建与编辑图标

创建图表时将单元格区域中的数据以图表的形式进行显示,从而可以更直观地分析表格数据。

(1)图表的组成。

图表由许多部分组成,每一部分就是一个图标项。如图表区、绘图区、标题、坐标轴、数据系列等,其中图表区表示整个图标区域,绘图区位于图表区域的中心,图表的数据系列、网络线等位于该区域中,如图4-50所示。

图4-50 图表组成

图4-51 图表的类型

(2)图表类型。

Excel2010支持各种类型的图表,如柱形图、折线图、饼图、条形图、面积图、散点图等,从而帮助用户以多种方式表示工作中的数据。一般可以用柱形图比较数据间的多少关系,用折线图反映数据的变化趋势,用饼状图表现数据间的比例分配关系,图表的类型如图4-51所示。

对于大多数图表,如柱形图和条形图,可以将工作表的行或列中排列的数据绘制在图表中,而有些图表类型(如饼图),则需要特定的数据排列方式。

(3)创建图表。

在Excel2010中创建图表的一般流程如下。

①选定要创建为图表的数据并插入某些类型的图表。

②根据需要编辑的图表,如更改图表类型、切换行列、移动图表和为图表快速应用系统内置的样式等。

③根据需要设置图表布局,如添加或取消图表的标题、坐标轴和网格线等。

④根据需要分别对图表的图表区、绘图区、分类(X)轴、数值(Y)轴和图例项等组成元素进行格式化,从而美化图表。

Excel2010中的图表分为两种类型,嵌入式图表和独立图表。

嵌入式图表是指与数据源位于同一个工作表的图表。要创建独立图表,可先创建嵌入式图表,然后执行“图表工具”|“设计”选项标签|“位置”功能区|“移动图表”命令,打开“移动图表”会话框,选中“新工作表”按钮,再单击“确定”按钮,即可在原工作表的前面插入一个“Chart＋数字”命名的工作表以放置创建的图表,如图4-52、图4-53所示。

图4-52 “设计”选项标签

图4-53 移动图表

(4)编辑图表。

创建图表后,在工作表的其他位置单击可取消图表的选择,单击图表区任意位置可选中图表。选中图表后,“图表工具”选项标签变为可用,用户可使用其中的“设计”子选项标签编辑图表,如更改图表类型,向图表中添加或删除数据,将图表的行、列数据互换,快速更改图表布局和应用图表样式等,如图4-54所示。

图4-54 图表设计

(5)设置图表布局和美化图表。

创建图表后,除了可以利用“图表工具”选项标签中的“设计”子选项标签对图表进行编辑和应用系统内置的布局和样式外,还可利用“布局”子选项标签设置图表布局,以及利用“格式”子选项标签设置图表各元素的格式以美化图表,如设置图表区、绘图区和坐标轴等的格式。

①设置图表布局。在Excel2010中,可利用“图表工具”下的“布局”选项标签添加或取消图表的标题、坐标轴、网格线和图例等组成元素,以及设置这些组成元素在图表中的位置等,从而更改图表布局,如图4-55所示。

②美化图表。创建好图表后,还可以利用“图表工具”下的“格式”选项标签分别对图表的图表区、绘图区、标题、坐标轴、图例项、数据系列等组成元素进行格式设置,如设置填充颜色、边框颜色和字体等,从而美化图表,如图4-56所示。

图4-55 图表布局

图4-56 表格式

任务实施

步骤一 排序

打开学生成绩表工作簿,利用求和函数和平均值函数计算出总分和平均值,单击鼠标左键拖动鼠标选定全班学生的成绩,执行“数据”|“排序”命令打开排序对话框,如图4-57所示。(www.daowen.com)

图4-57 “排序”对话框

在排序对话框中“列”选项中选择“总分”如图4-58所示,在“排序依据”选项中选择“数值”,在“次序”选项中选择“降序”,完成所有设置后,单击“确定”按钮完成排序任务,如图4-59所示。

图4-58 设定排序参数

图4-59 排序后的效果

步骤二 筛选低于80的学生名单

在学生成绩表区域内任意单击一个单元格,执行“数据”选项标签|“筛选”命令,此时成绩表的表头文字每一个单元格后面出现一个倒三角按钮,如图4-60所示。

图4-60 筛选

单击“平均分”所在单元格的倒三角按钮,在下拉菜单中执行“数字筛选”|“大于”命令,如图4-61所示,弹出“自定义自动筛选方式”对话框,如图4-62所示。

图4-61 执行筛选

图4-62 “自定义自动筛选方式”对话框

在“自定义自动筛选方式”对话框大于后面的文字框中输入80,如图4-62所示,单击“确定”按钮完成筛选任务,如图4-63所示。

图4-63 筛选后的效果

步骤三 筛选出需补考的学生名单

再次执行“数据”选项标签|“筛选”命令,取消筛选状态,恢复原有状态。

绘制“条件区域”,如图4-64所示,条件为小于60,条件关系为“或”关系。

图4-64 绘制“条件区域”

执行“数据”选项标签|“高级筛选”命令打开“高级筛选”对话框,单击“列表区域”后区域选择按钮,拖动鼠标选定条件区域(整个成绩表,不包含“学生成绩表”单元格),单击“条件区域”选择按钮,拖动鼠标选择条件区域。单击选择“将筛选结果复制到其他位置”并选择结果粘贴区域,如图4-65 所示。完成所有设置后,单击“确定”按钮,完成高级筛选,如图4-66所示。

图4-65 “高级筛选”对话框

图4-66 高级筛选结果

步骤四 按性别分类汇总

将全班学生按照性别进行排序。

执行“数据”选项标签|“分类汇总”命令,弹出“分类汇总”对话框,如图4-67所示。

图4-67 “分类汇总”对话框

在“分类汇总”对话框中分别设置“分类字段”为性别,“汇总方式”为平均值,“选定汇总项”为各科目成绩即数学、语文、英语、历史、化学、生物,“替换当前分类汇总”和“汇总结果显示在数据下方”两项,然后单击“确定”按钮,完成汇总,如图4-68所示。

图4-68 分类汇总

步骤五 将数据转换为图表

在分类汇总的基础上,单击A 列标签左边数字为“2”的按钮,如图4-69所示,将所有平均值保留1位小数,单击A2单元格,拖动鼠标至L19,选定该三行单元格区域。

图4-69

执行“插入”选项标签|“图标”功能区|“柱形图”选项|“圆柱图”样式|“簇状圆柱图”命令,生成如图4-70所示的图表。

图4-70 柱状图

为图表添加标题。选定图标,执行“布局”选项标签|“标签”功能区|“图表标题”|“图表上方”命令,如图4-71所示。此时图标上方就会多出一个文本框“图表标题”,如图4-72所示,将“图表标题”更改为“男女生科目成绩分析”,完成图表标题的添加。

图4-71 设置图表标题

图4-72 图表标题

选定图表,执行“格式”选项标签|“当前所选内容”功能区|“设定所选内容格式”命令,弹出“设置数据系列格式”对话框,如图4-73所示。

在“设置数据系列格式”对话框中单击“填充”选项,打开“填充”选项标签选择“图片或纹理填充”,在选项标签下面的“纹理”选项中选择“水滴”,如图4-74所示。

图4-73 “设置数据系列格式”对话框

图4-74 设置图表填充方案

在“设置数据系列格式”对话框中单击“三维格式”选项,打开“三维格式”选项标签,设置三维格式棱台顶端为“角度”,如图4-75所示。

图4-75 设置图表三维格式

单击“关闭”按钮完成图表区格式设置,图表的最后效果如图4-76所示。

图4-76 图表最终效果

实战演练

一、选择题

1.用Excel2010创建一个学生成绩表,如果要按照班级统计某门课程的平均分,则需要使用的方式是(　)。

A.数据筛选　B.排序　C.合并计算　D.分类汇总

2.在Excel2010中,下面关于分类汇总的叙述错误的是(　)。

A.分类汇总前必须按关键字排序数据

B.汇总方式只能是求和

C.分类汇总的关键字段只能是一个字段

D.分类汇总可以被删除,但删除汇总后排序操作不能撤销

3.在Excel中,关于“筛选”的正确叙述是(　)。

A.自动筛选和高级筛选都可以将结果筛选至另外的区域中

B.不同字段之间进行“或”运算的条件必须使用高级筛选

C.自动筛选的条件只能是一个,高级筛选的条件可以是多个

D.如果所选条件出现在多列中,并且条件间有“与”的关系,必须使用高级筛选

4.在Excel中,若要使用工作表Sheet2 中的区域A1:B2 作为条件区域在工作表Sheet1中进行数据筛选,则指定的条件区域应该是(　)。

A.Sheet2aI:B2　B.Sheet2! A1:B2

C.Sheet2＃A1:B2　D.A1:B2

5.在Excel中,用筛选条件“数学＞65”与“总分＞250”对成绩数据进行筛选,在筛选结果中都是(　)的记录。

A.数学＞65　B.数学＞65并且总分＞250

C.总分＞250　D.数学＞65或总分＞250

6.在Excel中,数据排序可以按(　)来排序。

A.时间顺序　B.数值大小　C.字母顺序　D.以上均可

7.在Excel中,数据清单的每一列的数据类型必须(　)才能进行分类汇总。

A.不同　B.完全相同　C.部分相同　D.数值结果相同

8.在Excel中,下列关于排序操作的叙述中,正确的是(　)。

A.用于排序的字段称为“关键字”,排列时只能有一个“关键字”

B.只能按字段值的升序或降序方式进行排列

C.只能对数值字段进行排序,不能对字符型字段进行排列

D.一旦排序后就不能恢复原来的记录序列

二、简答题

1.若用自动筛选,怎样筛选出“姓名”列中含有姓“张”的所有记录? 如何操作?

2.对数据清单进行高级筛选时,筛选条件是什么样的表达式? 筛选条件必须包括什么?

三、操作题

在Excel电子表格中录入如图4-77所示数据,按要求操作。

图4-77

①设置工作表行、列。

标题行行高30,其余行高为20。

②设置单元格。

a.标题格式:字体楷书,字号20,字体颜色为红色,跨列居中,底纹黄色。

b.将成绩右对齐,其他各单元格内容居中。

③设置表格边框:外边框为双线,深蓝色;内边框为细实心框,黑色。

④重命名工作表:将sheet1工作表重命名为“学生成绩表”。

⑤复制工作表:将“学生成绩表”工作表复制到sheet2中。

⑥将姓名和总成绩建立图表并将图表命名。

⑦计算学生总成绩、平均成绩、最高成绩。

⑧按总成绩递增排序。

⑨数据筛选:筛选“数学”字段选择“＞90分”。