excel的操作

第1章 Office Excel 2010高级应用技术

1.1学生成绩管理

一、考查知识点

数据清单、格式设置、公式与函数、排序、筛选、分类汇总和图表等级知识，利用多种

公式和函数对单元格中的数据进行运算；对各种数据可以进行格式设置；分析汇总各单元格

中的数据信息，把相关数据用统计图表的形式表示，运用Excel 2010电子表格强大的数据管

理功能，进行相应的数据处理和分析。

二、知识点解析

1、创建学生信息表，如图1-1所示。

图1-1原始工作表

2、操作内容：

（1） 将首行合并居中，并将标题“学生信息表”设置为“黑体”，“20号”，

将其他数据清单数据设置为“宋体”，“12号”，“水平居中”。

（2） 将数据表中的行高设为“20磅”，列宽设为“最合适的列宽”，数据

表中的单元格垂直和水平对齐方式均设定为“居中”。

（3） 设置各科成绩的数据输入有效性，要求成绩输入的范围为0~100，当

鼠标停留在成绩的单元格上时，提示信息为“成绩范围：0~100”。

（4） 将每门课程中不及格的成绩用红色、加粗字体显示。

（5） 用AVERAGE函数求出每位学生的平均分，并用IF函数根据平均分对

学生做出评价，60分及以上为“合格”，60分以下为“不合格”。

（6） 根据“身份证号码”字段计算每位学生的年龄。

（7） 用统计函数完成对“平均分”中各分数段人数统计，把结果分别填入

相应的单元格，并根据各等级的比例生成三维饼图，放在统计表下。

（8） 插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优秀

学生信息表”；在“学生信息表”中筛选出平均成绩大于等于85分的学生记录，

将其复制到“优秀学生信息表”中，“学生信息表”中的记录还原。

（9） 建立“按组分类汇总”工作表，把“学生信息表”中的成绩表复制到

该表中，按“组别”字段升序排序。

（10） 按A、B两个组进行分类汇总，分别计算出A、B组的“数学”、“英

语”、“物理”、“化学”平均分。

3、操作分析与过程

（1）输入数据表中的原始数据。

（2）选择D3至G23区域，单击“数据”选项卡“数据工具”组中的“数据有效性”

命令，弹出如图1-2所示的对话框，选择“设置”选项卡进行“有效性条件”设置，在“输

入信息”选项卡中进行相关输入信息的设置。

图1-2设置“数据有效性”对话框

（3）选择D3至G23区域，单击“开始”选项卡“样式”组中的“条件格式”按钮，

选择“突出显示单元格规则”→“其他规则”命令，弹出“新建格式规则”对话框，设置单

元格数值为“小于60”，再单击“格式”对话框，设置字体为“红色”、“加粗”，如图1-3

所示，单击“确定”按钮。

图1-3条件格式设置

（4）在单元格H3中输入公式“=AVERAGE(D3:G3)”，计算该学生的平均分，利用Excel

2010的序列填充功能，求出其他学生的平均分，如图1-4所示。

图1-4计算“平均分”

（5）在单元格I3中输入公式“=YEAR(NOW())-MID(C3,7,4)”，计算该学生的年龄，利

用Excel 2010的序列填充功能，求出其他学生年龄，如图1-5所示。

图1-5计算“年龄”

（6）在单元格J3中输入公式“=IF(H3>=60,"合格","不合格")”，计算该学生的等级，利

用Excel 2010的序列填充功能，求出其他学生的等级，如图1-6所示。

图1-6计算“等级”

（7）在单元格D26中输入公式“=COUNTIF(H3:H23,"<60")”，计算“0-59分”的人数；

在单元格E26中输入公式“=COUNTIF(H3:H23,">=60")-

COUNTIF(H3:H23,">=85")”，计算“60-84分”的人数；在单元格F26中输入公式

“=COUNTIF(H3:H23,">=85")”，计算“≥85分”的人数；在单元格D27中输入公式

“=D26/COUNT($H$3:$H$23)”，计算出该等级所占比例，其它依次类推，如图1-7所示。

图1-7统计各分数段人数

（8）选中C25至F27，单击“插入”选项卡“图表”组中的“饼图”按钮，选择“三

维饼图”命令，根据各等级的比例生成如图1-8所示的三维饼图，放在统计表下。

图1-8各分数段人数所占比例图表

（9）插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优秀学生信息表”

（略）；返回原始工作表，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，各字

段旁出现下拉按钮。单击“平均分”字段的下拉按钮，在弹出的下拉菜单中执行“数字筛选”

→“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，如

图1-9所示。筛选出平均成绩大于等于85分的学生记录，将这些记录复制到“优秀学

生信息表”中，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，取消“学生信息

表”中的记录筛选。

图1-9设置“自动筛选”对话框

（10）建立“按组分类汇总”工作表，把“学生信息表”中的成绩表复制到该表中，按

“组别”字段升序排序，单击“数据”选项卡“分级显示”组中的“分类汇总”命令，按“组

别”进行分类汇总，分别计算出A、B组的“数学”、“英语”、“物理”、“化学”平均分，如

图1-10所示，单击“确定”按钮，得到汇总结果。

图1-10设置“分类汇总”对话框

1.2毕业设计成绩管理

一、项目介绍

毕业设计成绩信息记录了毕业设计的基本情况，随着高校办学规模的扩大，各专业学生

数量和毕业设计题目数量都在增加，毕业设计（毕业实习）指导教师在学生毕业设计与毕业

结束后，需要给学生评定成绩，成绩是毕业设计（毕业实习）整个过程的最终体现。通过计

算机采用Excel进行快速评分与计算，并得出相关的统计信息，可节省教师统计与计算的时

间。通过该案例的学习可掌握Excel 2010工作表多表间的数据选择和表示、多表间的公式与

函数的使用、多表间的合并计算、选择性粘贴等功能的综合应用。

二、操作数据

【实战训练】

图1-11基本信息表数据内容

【实战训练】

图1-12基础分表数据内容

三、操作要求

（1） 以“07010101”为起始学号，在“学号”一列为每位同学填充学号；

（2） 从身份证号码中自动提取性别填入“性别”一列，提取方法：若第17位为

奇数，表示男性，若为偶属，表示女性（假设身份证统一为18位）；

（3） 从身份证号码中提取出生日期填入“出生日期”一列，格式为“××××年

××月××日”，提示：18为身份证号码，7~10位为出生年份（4位），11~12位为出生

月份，13~14位为出生日期。（注意：不能用单元格格式设置）；

（4） 在“手机短号”一列填充每位同学的手机短号码，填充方法为每一位同学手

机长号后四位前加上“66”；

（5） 在“基础成绩”一列填充每个同学的基础成绩，基础成绩由“基础成绩表”

中查找不同设计题目所对应的基础成绩；

（6） 在“总成绩”一列计算每位同学的总成绩，算法为：总成绩=基础成绩+设计

成绩*30%+论文成绩*30%+实习成绩*10%，结果保留1位小数；

（7） 如果毕业总成绩在85分以上，则为优秀毕业生，在“是否为优秀毕业生”

一列填充对应的学生为“优秀毕业生”；

（8） 如果某学生毕业设计成绩、毕业论文成绩与毕业实习成绩中有一项缺少，则

在其“备注”一列填充“成绩缺少，不予毕业”；

（9） 分别统计末提交毕业设计与毕业论文、未参加毕业实习的同学个数（该项成

绩为空则为未提交），分别填入E63、E64、E65；

（10） 在不改变现在顺序的前提下，统计总成绩前五名的平均总成绩，结果填入

E73；

（11） 对“基本信息表”按“仓库管理系统、人事管理系统、图书管理系统、旅

游网站、公司企事业网站”的顺序进行排序；

（12） 用“合并计算”分别统计出每个毕业设计题目所有同学的设计成绩、论文

成绩、实习成绩、总成绩的平均分，结果保留1位小数，并将结果保留在sheet3，并

将sheet3重新命名为“不同毕业设计成绩信息”；

（13） 用数据库函数计算女生优秀毕业生个数，填入E67（提示：自己书写条件

区域）；

（14） 用高级筛选功能筛选出设计成绩、论文成绩、实习成绩、总成绩都及格的

学生，放入“各项成绩都及格的学生”工作表；

（15） 根据“基本信息表”建立数据透视表，显示各个毕业设计题目总成绩分段

人数及占总人数的比例，要求：

a、 页字段为：毕业设计题目；

b、 行字段为：总成绩分数段；

c、 数据区为：每个分数段的人数及占该设计题目总人数的百分比；

d、 放入“各题目总成绩分段人数及比例” 工作表；

（16） 在“不同毕业设计成绩信息表”工作表中，以各个毕业设计题目所有同学

的设计成绩、论文成绩、实习成绩、总成绩的平均分制作柱形圆锥图，并加以修饰，结

果如下图1-13所示：

【实战训练】

图1-13平均分柱形圆锥图

（17） 对“基本信息表”进行设置：

a、 设置整个工作表字体为10号，行高为16，列为最适合的列宽，对齐方式为

水平居中与垂直居中，外边框为双线，内边框为单实线；

b、 页面设置为横向，纸张为B4，居中方式为水平居中；

c、 眉中部设置为“07计算机应用技术1班毕业成绩表”，右部为制表日期；页

脚右部插入页码；

d、 设置打印区域为A1:O60，每页都显示标题行；

四、操作分析与过程

操作要求（1）

英文状态下，在A2单元格输入“’07010101”，然后按回车。把鼠标放在A2单元格填

充柄上，如图1-14所示。双击填充柄填充A3-A60单元格数据或当鼠标变成十字形状时，按

着鼠标左键不放，向下填充A3-A60单元格数据。

【实战训练】

图1-14A2单元格填充柄

操作要求（2）

在C2单元格中输入公式“=IF(MOD(MID(D2,17,1),2)=0,"女","男")”，计算该学生的性

别，利用Excel 2010的序列填充功能，求出其他学生年性别。如图1-15所示。

【实战训练】

图1-15计算学生的性别

操作要求（3）

CONCATENATE 函数可将最多 255 个文本字符串联接成一个文本字符串，MID函数可返

回从指定位置开始的特定数目的字符串。在E2单元格中输入公式

“=CONCATENATE(MID(D2,7,4),"年",MID(D2,11,2),"月",MID(D2,13,2),"日")”，计算该学

生的出生日期，利用Excel 2010的序列填充功能，求出其他学出生日期，如图1-16所示。

【实战训练】

图1-16计算学出生日期

也可通过单击菜单“公式”→“插入函数”，插入CONCATENATE函数，在CONCATENATE

函数对话框中，输入如图1-17参数信息，单击确定按钮。

【实战训练】

图1-17CONCATENATE函数参数对话框

操作要求（4）

在M2单元格中输入公式“="66"&RIGHT(L2,4)”，计算该学生的手机短号，利用Excel

2010的序列填充功能，求出其他学手机短号，如图1-18所示。

【实战训练】

图1-18计算学生短号

操作要求（5）

选取单元格“G2”，单击菜单“公式”→“插入函数”，如图1-19所示，系统弹出如图

1-20所示的“插入函数”对话框。

【实战训练】

图1-19“插入函

数”对话框

【实战训练】

图1-20“选择函数”对话

框

在“或选择类别”下拉列表中选择“查找与引用”，在“选择函数”列表中选择“VLOOKUP”，

系统继续弹出如图1-21所示的“函数参数”对话框。在“Lookup_value”（搜索的条件值）

输入框中输入“F2”，即“毕业设计题目”，在“Table_array”（被搜索的表区域）输入框中

输入“基础分表!$A$1:$B$6”，在“Col_index_num”（返回值所在的列号）输入框中输入“2”，

即“姓名”列，在“Range_lookup”输入框中输入“FALSE”或缺省，单击“确定”按钮，

此时的编辑栏显示“=VLOOKUP(F2,基础分表!$A$1:$B$6,2,FALSE)”，在单元格“G2”出现“30”，

计算该学生的毕业设计基础分，利用Excel 2010的序列填充功能，求出其他学生的毕业设

计基础分。

【实战训练】

图1-21VLOOKUP函数参数对话框

操作要求（6）

在K2单元格中输入公式“=G2+H2*0.3+I2*0.3+J2*0.1”，计算该学生的总成绩，利用

Excel 2010的序列填充功能，求出其他学生的总成绩，如图1-22所示。

【实战训练】

图1-22计算学生总成绩

操作要求（7）

在N2单元格中输入公式“=IF(K2>=85,"优秀毕业生","")”，计算该学生是否为优秀毕

业生，利用Excel 2010的序列填充功能，判断其他学生是否为优秀毕业生，如图1-23所示。

【实战训练】

图1-23计算是否为优秀毕业生

操作要求（8）

可在O2单元格中输入公式“=IF(OR(H2="",I2="",J2=""),"成绩缺少，不予毕业","")”，

如图1-24所示，计算该学生是否有成绩缺少，给出相应提示信息。也可以通过插入公式方

式来操作。选取单元格“O2”，单击菜单“公式”→“插入函数”，插入IF函数，在IF函数

对话框中，输入如图1-25参数信息。光标放在Logical_test对应区域，单击，在函

数列表中单击其他函数，如图1-26所示，出现插入函数对话框，选择OR函数，如图1-27，

在OR函数对话框中，输入如图1-28参数信息，按确定。利用Excel 2010的序列填充功能，

判断其他学生是否有成绩缺少，给出相应提示信息。

【实战训练】

图1-24计算是否不予毕业

【实战训练】

图1-25IF函数参数对话框

图1-26 图1-27“选择函数”对话框

函数列表

【实战训练】

图1-28OR函数参数对话框

操作要求（9）

COUNTBLANK函数用于计算指定单元格区域中空白单元格的个数，分别在E63、E64、E65

单元格中输入公式“=COUNTBLANK(H2:H60)”、“=COUNTBLANK(I1:I60)”、

“=COUNTBLANK(J2:J60)”，统计末提交毕业设计与毕业论文、未参加毕业实习的同学个数，

其中H2:H60区域为学生的设计成绩区域，I1:I60区域为学生的论文成绩区域，J2:J60学生

的实习成绩区域，如图1-29。

【实战训练】

图1-29计算空白单元格的个数

操作要求（10）

选取单元格“E73”，单击菜单“公式”→“插入函数”，插入AVERAGE函数，在AVERAGE

函数对话框中，输入如图1-30参数信息，单击确定按钮。

【实战训练】

图1-30AVERAGE函数参数对话框

也可以利用数组公式，在单元格“E73”中，输入公式

“=AVERAGE(LARGE(K2:K60,{1,2,3,4,5}))”，求出统计总成绩前五名的平均总成绩，如图

1-31所示。

【实战训练】

图1-31计算前五名的平均总成绩

操作要求（11）

把光标放在A1：O60任意单元格上，单击“开始”--“排序和刷选”，选择“自定义排

序”，如图1-32，在排序对话框中主关键字选择“毕业设计题目”，次序选择“自定义序列”，

在自定义对话框中，输入序列仓库管理系统、人事管理系统、图书管理系统、旅游网站、公

司企事业网站，如图1-33，点添加按钮，把该序列添加到自定义序列中，按确定，再在排

序对话框中按确定按钮即可，如图1-34所示。

【实战训练】

图1-32

排序对话框

【实战训练】

图1-33

自定义序列对话框

【实战训练】

图1-34

排序对话框

操作要求（12）

选中“不同毕业设计成绩信息”表中A1单元格，单击“数据”---“合并计算”，在合

并计算对话框中选择函数为“平均值”，单击引用位置右边按钮，选择“基本信息表”

中F1:K60区域数据，单击添加按钮，再单击确定按钮即可，如图1-35。

【实战训练】

图1-35“合并计算”对话框

选择“C2：F6”区域，单击右键，选择“设置单元格格式”，选择“数字”---“数值”，

设置小数位数为1位，如图1-36，完成后效果如图1-37。

【实战训练】

图1-36“设置单元格格式”对话框

【实战训练】

图1-37完成效果

操作要求（13）

DCOUNTA函数可返回列表或数据库中满足指定条件的记录字段（列）中的非空单元格的

个数。先在G67:H68区域中输入女优秀毕业生条件，如图1-38设置女优秀毕业生条件所示，

然后选取单元格“E67”，单击菜单“公式”→“插入函数”，插入DCOUNTA函数，在DCOUNTA

函数对话框中，输入DCOUNTA函数参数对话框中的参数信息，如图1-39，单击确定按钮。

也可直接在单元格“E67”中，输入公式“=DCOUNTA(B1:O60,C1,G67:H68)”，求出统计总成

绩前五名的平均总成绩。

【实战训练】

图1-38设置女优秀毕业生条件

【实战训练】

图1-39DCOUNTA函数参数对话框

操作要求（14）

设置条件区域，复制“基本信息表”中的如图1-40所示的“H1：K1”区域的标题信息，

粘贴到“各项成绩都及格的学生”工作表中的“A1：D1” 区域，设置及格条件，如

图1-41设置各项成绩及格条件所示。

图1-40“H1：K1”标题信息 图1-41设置各项成绩及格条件

选中“各项成绩都及格的学生”工作表中的“A4” 单元格，单击菜单“数据”—“高

级”，打开高级筛选对话框，如图1-42高级筛选对话框（1），先对条件区域与复制位置进行

设置。把光标放在列表区域对应位置，在单击右边的，选择“基本信息表”中“A1：O60”

完成筛选。

区域数据，列表区域设置完成效果如图1-43高级筛选对话框（2）所示。单击“确定”按钮，

【实战训练】 【实战训练】

图1-42高级筛选对话图1-43高级筛选对话

框（1） 框（2）

操作要求（15）

选中“各题目总成绩分段人数及比例”表中A2单元格，单击“插入”---“数据透视表”中

“数据透视表”。在“创建数据透视表”对话框中选择表区域，如。单击确定。

图1-44

【实战训练】

图1-44“创建数据透视表”对话框

把“毕业设计题目”拖放到A1单元格。将总成绩拖放到左边行字段处，如图1-45所

示。

【实战训练】

图1-45“毕业设计题目”拖放位置

把“毕业设计题目”拖放到A1单元格。将总成绩拖放到左边行字段处，如图1-46所

示。

【实战训练】

图1-46总成绩拖放位置（1）

再将总成绩拖放到值字段处，如图1-47所示。

【实战训练】

图1-47总成绩拖放位置（2）

再将总成绩拖放到求和项：总成绩处。效果如图1-48所示。

【实战训练】

图1-48总成绩求和项

在右侧下方处，点击汇总的第一种方式“求各项：总成绩”右侧下拉菜单，

如图1-49，在弹出的快捷菜单中选择“值字段设置（N）”，如图1-50，在“值字段设置”对

话框中“值汇总方式”选项卡中选择计算类型为“计数”，如图1-51确定，出现如图1-52

效果。

【实战训练】

图1-49求和项修改

【实战训练】 【实战训练】

图1-50值字段设置 图1-51值汇总方式修

改

【实战训练】

图1-52修改值汇总方式后效果

在右侧下方处，点击汇总的第二种方式“求各项：总成绩”右侧下拉菜单，

如图1-52，在弹出的快捷菜单中选择“值字段设置（N）”，如图1-53，在“值字段设置”对

话框中“值显示方式”选项卡中选择“值显示方式（A）”为“全部汇总百分比”，确定，出

现如图1-54效果。

【实战训练】

图1-53修改值显示方式

【实战训练】

图1-54修改值显示方式后效果

选中总成绩中60分以下的成绩，单击右键，选择“创建组”，如图1-55，同理创建60-70

分创建组，70-80分创建组,80分以上创建组。效果如图1-56所示。

【实战训练】

图1-55创建组

【实战训练】

图1-56创建组后效果

右击总成绩列某个单元格，选择“删除“总成绩”，如图1-57所示。删除后完成效果如

图1-58所示。

【实战训练】

图1-57删除总成绩字段

【实战训练】

图1-58删除总成绩字段后效果

修改“总成绩2”列中各分组名称，同时修改图中“数据”列中“计数项：总成绩”为

“人数”，“求和项：总成绩2”为“占总人数比例”，效果如图1-59所示。

【实战训练】

图1-59修改字段信息

操作要求（16）

选择“不同毕业设计成绩信息”表中“A1：F6”数据区域，单击“插入”---“柱形图”，

单击柱形图下方三角箭头，选择“圆锥图”中的簇状柱形图，如图1-60所示，出现如

图1-61所示的圆锥图。

【实战训练】

图1-60

“柱形图”下拉列表

【实战训练】

图1-61圆锥图

选中图1-61中圆锥图的右侧“垂直（值）铀”，双击，弹出如图1-62所示“设置坐标

铀格式”对话框，设置最大值为100，最小值为0，主要刻度单位为20，确定。

选中图中圆锥图的左侧“图例”，双击，弹出如图1-63所示“设置图例格式”对话框，

调整图例位置为底部。

【实战训练】

图1-62设置坐标轴格

式

【实战训练】

图1-63设置图例格式

设置坐标轴值与图例后，效果如图1-64所示。

【实战训练】

图1-64修改坐标轴与图例格式后效果

选中该图表，选择“设计”---“图表布局”中“布局3”，如图1-65，输入图表标题“不

同毕业设计各类成绩平均分”，并设置合适的字体大小，图表完成后最终效果如图1-66。

【实战训练】

图1-65图表布局

【实战训练】

图1-66图表最终效果

操作要求（17）

选择“基本信息表”中“A1：O60”区域，单击鼠标右键，弹出“设置单元格格式”对

话框，在弹出的对话框中，单击边框，在样式中选择单线样式，点击预置中“内部”，如图

1-67设置单元格格式对话框（一）所示，然后在样式中选择双线样式，点击预置中“外边

框”，如图1-68设置单元格格式对话框（二）所示。设置好内部与外部边框后，单击“确定”

按钮。

【实战训练】 【实战训练】

图1-67设置单元格格图1-68设置单元格格

式对话框（一） 式对话框（二）

单击“对齐”，分别设置水平对齐、垂直对齐方式为“居中”。如图1-69所示。

【实战训练】

图1-69设置对齐方式

选中整个数据表，单击“格式”中的“行高”，如图1-70所示，设置单元格大小中的行

高为16，如图1-71所示，列宽为“自动调整列宽”，如图1-72所示。

【实战训练】 【实战训练】 【实战训练】

图图图1-72

1-70选择行高 1-71行高设置 自动调整列宽

在“页面布局”菜单中“页面设置”功能区中：单击“纸张方向”按钮，选择“横向”，

如图1-73所示；单击“纸张大小”按钮，设置纸张大小为B4，如图1-74所示；单击“打

印区域”按钮，设置打印区域，如图1-75所示；单击“页面设置”右侧小斜拉箭头，如图

1-76，自定义页眉和页脚，如图1-77和图1-78所示，同时，也可在页面设置对话框“工作

表”选项卡中设置打印区域与每页都显示标题行，如图1-79所示。

【实战训练】 【实战训练】

图1-73纸张横向设置 图1-74设置纸张大小

【实战训练】 【实战训练】

图1-75设置打印区域 图1-76选择页面设置

【实战训练】

图1-77页眉页脚设置

【实战训练】

图1-78自定义页眉

【实战训练】

图1-79打印区域与顶端标题设置

【项目小结】

本项目利用Excel 2010对毕业设计与实习各项成绩进行管理，节省教师统计与计算的时

间。通过该案例的学习可掌握Excel 2010工作表多表间的数据选择和表示、多表间的公式与

函数的使用、多表间的合并计算、选择性粘贴等功能的综合应用。

【实战训练】

1、假设你是一位毕业设计与实习指导教师，请你结合自己实际情况，制作学生的毕业设计

与实习成绩管理的电子表。

2、如果你是一位专业教研室主任，请根据自己本专业学生毕业设计与毕业实习情况，制作

关于本专业学生的毕业设计与实习成绩管理的电子表。