计算机应用基础课程excel综合实训材料Word格式文档下载.docx
《计算机应用基础课程excel综合实训材料Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《计算机应用基础课程excel综合实训材料Word格式文档下载.docx（17页珍藏版）》请在冰豆网上搜索。
两个组进行分类汇总，分别计算出
组的“数学”、“英
语”、“物理”、“化学”平均分。
3、操作分析与过程
（1）输入数据表中的原始数据。
（2）选择
D3
至
G23
区域，单击“数据”选项卡“数据工具”组中的“数据有效性”
命令，弹出如图7-2
所示的对话框，选择“设置”选项卡进行“有效性条件”设置，在
“输入信息”选项卡中进行相关输入信息的设置。
1-2
设置“数据有效性”对话框
（3）选择
区域，单击“开始”选项卡“样式”组中的“条件格式”按钮，
选择“突出显示单元格规则”→“其他规则”命令，弹出“新建格式规则”对话框，设置
单元格数值为“小于
60”，再单击“格式”对话框，设置字体为“红色”、“加粗”，如图
7-3
所示，单击“确定”按钮。
1-3
条件格式设置
（4）在单元格
H3
中输入公式“=AVERAGE（D3:
G3）”，计算该学生的平均分，利用
的序列填充功能，求出其他学生的平均分，如图
7-4
1-4
计算“平均分”
（5）在单元格
I3
中输入公式“=YEAR（NOW（））-MID（C3,7,4）”，计算该学生的年龄，
利用
的序列填充功能，求出其他学生年龄，如图
7-5
1-5
计算“年龄”
（6）在单元格
J3
中输入公式“=IF（H3>
=60,"
合格"
"
不合格"
）”，计算该学生的等级，
的序列填充功能，求出其他学生的等级，如图
7-6
1-6
计算“等级”
（7）在单元格
D26
中输入公式“=COUNTIF（H3:
H23,"
<
60"
）”，计算“0-59
分”的人
数；
在单元格
E26
>
=60"
）-
COUNTIF（H3:
=85"
）”，计算“60-84
分”的人数；
F26
中输入公式
“=COUNTIF（H3:
）”，计算“≥85
D27
“=D26/COUNT（$H$3:
$H$23）”，计算出该等级所占比例，其它依次类推，如图
7-7
1-7
统计各分数段人数
（8）选中
C25
F27，单击“插入”选项卡“图表”组中的“饼图”按钮，选择“三
维饼图”命令，根据各等级的比例生成如图
7-8
所示的三维饼图，放在统计表下。
1-8
各分数段人数所占比例图表
（9）插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优秀学生信息表”
（略）；
返回原始工作表，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，各字
段旁出现下拉按钮。
单击“平均分”字段的下拉按钮，在弹出的下拉菜单中执行“数字筛
选”→“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，如
图7-9
筛选出平均成绩大于等于
分的学生记录，将这些记录复制到“优秀
学生信息表”中，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，取消“学生
信息表”中的记录筛选。
1-9
设置“自动筛选”对话框
（10）建立“按组分类汇总”工作表，把“学生信息表”中的成绩表复制到该表中，
按“组别”字段升序排序，单击“数据”选项卡“分级显示”组中的“分类汇总”命令，
按“组别”进行分类汇总，分别计算出
组的“数学”、“英语”、“物理”、“化学”平
均分，如图
7-10
所示，单击“确定”按钮，得到汇总结果。
1-10
设置“分类汇总”对话框
1.2
毕业设计成绩管理
一、项目介绍
毕业设计成绩信息记录了毕业设计的基本情况，随着高校办学规模的扩大，各专业学
生数量和毕业设计题目数量都在增加，毕业设计（毕业实习）指导教师在学生毕业设计与
毕业结束后，需要给学生评定成绩，成绩是毕业设计（毕业实习）整个过程的最终体现。
通过计算机采用
进行快速评分与计算，并得出相关的统计信息，可节省教师统计与
计算的时间。
通过该案例的学习可掌握
工作表多表间的数据选择和表示、多表
间的公式与函数的使用、多表间的合并计算、选择性粘贴等功能的综合应用。
二、操作数据
【实战训练】图
1-11
基本信息表数据内容
1-12
基础分表数据内容
三、操作要求
以“07010101”为起始学号，在“学号”一列为每位同学填充学号；
从身份证号码中自动提取性别填入“性别”一列，提取方法：
若第
17
位为
奇数，表示男性，若为偶属，表示女性（假设身份证统一为
18
位）；
从身份证号码中提取出生日期填入“出生日期”一列，格式为“×
×
年×
月×
日”，提示：
为身份证号码，7~10
位为出生年份（4
位），11~12
出生月份，13~14
位为出生日期。
（注意：
不能用单元格格式设置）；
在“手机短号”一列填充每位同学的手机短号码，填充方法为每一位同学
手机长号后四位前加上“66”；
在“基础成绩”一列填充每个同学的基础成绩，基础成绩由“基础成绩表”
中查找不同设计题目所对应的基础成绩；
在“总成绩”一列计算每位同学的总成绩，算法为：
总成绩=基础成绩+设
计成绩*30%+论文成绩*30%+实习成绩*10%，结果保留
位小数；
如果毕业总成绩在
分以上，则为优秀毕业生，在“是否为优秀毕业生”
一列填充对应的学生为“优秀毕业生”；
如果某学生毕业设计成绩、毕业论文成绩与毕业实习成绩中有一项缺少，
则在其“备注”一列填充“成绩缺少，不予毕业”；
分别统计末提交毕业设计与毕业论文、未参加毕业实习的同学个数（该项
成绩为空则为未提交），分别填入
E63、E64、E65；
在不改变现在顺序的前提下，统计总成绩前五名的平均总成绩，结果填入
E73；
（11）
对“基本信息表”按“仓库管理系统、人事管理系统、图书管理系统、旅
游网站、公司企事业网站”的顺序进行排序；
（12）
用“合并计算”分别统计出每个毕业设计题目所有同学的设计成绩、论文
成绩、实习成绩、总成绩的平均分，结果保留
位小数，并将结果保留在
sheet3，并
将
sheet3
重新命名为“不同毕业设计成绩信息”；
（13）
用数据库函数计算女生优秀毕业生个数，填入
E67（提示：
自己书写条件
区域）；
（14）
用高级筛选功能筛选出设计成绩、论文成绩、实习成绩、总成绩都及格的
学生，放入“各项成绩都及格的学生”工作表；
（15）
根据“基本信息表”建立数据透视表，显示各个毕业设计题目总成绩分段
人数及占总人数的比例，要求：
a、
页字段为：
毕业设计题目；
b、
行字段为：
总成绩分数段；
c、
数据区为：
每个分数段的人数及占该设计题目总人数的百分比；
d、
放入“各题目总成绩分段人数及比例”
工作表；
（16）
在“不同毕业设计成绩信息表”工作表中，以各个毕业设计题目所有同学
的设计成绩、论文成绩、实习成绩、总成绩的平均分制作柱形圆锥图，并加以修饰，
结果如下图
7-13
所示：
【实战训练】图
1-13
平均分柱形圆锥图
（17）
对“基本信息表”进行设置：
设置整个工作表字体为
10
号，行高为
16，列为最适合的列宽，对齐方式
为水平居中与垂直居中，外边框为双线，内边框为单实线；
页面设置为横向，纸张为
B4，居中方式为水平居中；
c、眉中部设置为“07
计算机应用技术
班毕业成绩表”，右部为制表日期；
页脚右部插入页码；
设置打印区域为
A1:
O60，每页都显示标题行；
四、操作分析与过程
操作要求
（1）
英文状态下，在
A2
单元格输入“’07010101”，然后按回车。
把鼠标放在
单元格填
充柄上，如图7-14
双击填充柄填充
A3-A60
单元格数据或当鼠标变成十字形状时，
按着鼠标左键不放，向下填充
单元格数据。
1-14
单元格填充柄
操作要求
（2）
在
C2
单元格中输入公式“=IF（MOD（MID（D2,17,1）,2）=0,"
女"
男"
）”，计算该学生的
性别，利用
的序列填充功能，求出其他学生年性别。
如图
7-15
1-15
计算学生的性别
操作要求（3）
CONCATENATE
函数可将最多
255
个文本字符串联接成一个文本字符串，MID
函数可返
回从指定位置开始的特定数目的字符串。
E2
单元格中输入公式
“=CONCATENATE（MID（D2,7,4）,"
年"
MID（D2,11,2）,"
月"
MID（D2,13,2）,"
日"
）”，计算该学
生的出生日期，利用
的序列填充功能，求出其他学出生日期，如图
7-16
1-16
计算学出生日期
也可通过单击菜单“公式”→“插入函数”，插入
函数，在
CONCATENATE
函数对话框中，输入如图
7-17
参数信息，单击确定按钮。
1-17CONCATENATE
函数参数对话框
操作要求（4）
M2
单元格中输入公式“="
66"
&
RIGHT（L2,4）”，计算该学生的手机短号，利用
Excel
的序列填充功能，求出其他学手机短号，如图
7-18
1-18
计算学生短号
操作要求（5）
选取单元格“G2”，单击菜单“公式”→“插入函数”，如图7-19
所示，系统弹出如
7-20
所示的“插入函数”对话框。
1-19“插
入函数”对话框
【实战训练】
1-20“选择函数”
对话框
在“或选择类别”下拉列表中选择“查找与引用”，在“选择函数”列表中选择
“VLOOKUP”，系统继续弹出如图7-21
所示的“函数参数”对话框。
在“Lookup_value”
（搜索的条件值）输入框中输入“F2”，即“毕业设计题目”，在“Table_array”（被搜索
的表区域）输入框中输入“基础分表!
$A$1:
$B$6”，在“Col_index_num”（返回值所在的列
号）输入框中输入“2”，即“姓名”列，在“Range_lookup”输入框中输入“FALSE”或缺
省，单击“确定”按钮，此时的编辑栏显示“=VLOOKUP（F2,基础分表!
$B$6,2,FALSE）”
，在单元格“G2”出现“30”，计算该学生的毕业设计基础分，利用
的序列填
充功能，求出其他学生的毕业设计基础分。
1-21
VLOOKUP
操作要求（6）
K2
单元格中输入公式“=G2+H2*0.3+I2*0.3+J2*0.1”，计算该学生的总成绩，利用
的序列填充功能，求出其他学生的总成绩，如图
7-22
1-22
计算学生总成绩
操作要求（7）
N2
单元格中输入公式“=IF（K2>
=85,"
优秀毕业生"
"
）”，计算该学生是否为优秀毕
业生，利用
的序列填充功能，判断其他学生是否为优秀毕业生，如图
7-23
所
示。
1-23
计算是否为优秀毕业生
操作要求（8）
可在
O2
单元格中输入公式“=IF（OR（H2="
I2="
J2="
）,"
成绩缺少，不予毕业"
）”
，如图7-24
所示，计算该学生是否有成绩缺少，给出相应提示信息。
也可以通过插入公
式方式来操作。
选取单元格“O2”，单击菜单“公式”→“插入函数”，插入
函数，在
函数对话框中，输入如图7-25
参数信息。
光标放在
Logical_test
对应区域，单击
，在函数列表中单击其他函数，如图
7-26
所示，出现插入函数对话框，选择
OR
函数，
7-27，在
7-28
参数信息，按确定。
的
序列填充功能，判断其他学生是否有成绩缺少，给出相应提示信息。
1-24
计算是否不予毕业
1-25
1-26
函数列表图
1-27“选择函数”对话框
1-28
操作要求（9）
COUNTBLANK
函数用于计算指定单元格区域中空白单元格的个数，分别在
E63、E64、E65
单元格中输入公式“=COUNTBLANK（H2:
H60）”、“=COUNTBLANK（I1:
I60）”、
“=COUNTBLANK（J2:
J60）”，统计末提交毕业设计与毕业论文、未参加毕业实习的同学个数，
其中
H2:
H60
区域为学生的设计成绩区域，I1:
I60
区域为学生的论文成绩区域，J2:
J60
学
生的实习成绩区域，如图
7-29。
1-29
计算空白单元格的个数
操作要求（10）
选取单元格“E73”，单击菜单“公式”→“插入函数”，插入
7-30
1-30
也可以利用数组公式，在单元格“E73”中，输入公式
“=AVERAGE（LARGE（K2:
K60,{1,2,3,4,5}））”，求出统计总成绩前五名的平均总成绩，如图
7-31
1-31
计算前五名的平均总成绩
操作要求（11）
把光标放在
A1：
O60
任意单元格上，单击“开始”--“排序和刷选”，选择“自定义排
序”，如图
7-32，在排序对话框中主关键字选择“毕业设计题目”，次序选择“自定义序列”
，在自定义对话框中，输入序列仓库管理系统、人事管理系统、图书管理系统、旅游网站、
公司企事业网站，如图
7-33，点添加按钮，把该序列添加到自定义序列中，按确定，再在
排序对话框中按确定按钮即可，如图
7-34
1-32
排序对话框
1-33
自定义序列对话框
1-34
操作要求（12）
选中“不同毕业设计成绩信息”表中
A1
单元格，单击“数据”---“合并计算”，在合
并计算对话框中选择函数为“平均值”，单击引用位置右边按钮，选择“基本信息表”
中
F1:
K60
区域数据，单击添加按钮，再单击确定按钮即可，如图
7-35。
1-35“合并计算”对话框
选择“C2：
F6”区域，单击右键，选择“设置单元格格式”，选择“数字”---“数值”，
设置小数位数为
位，如图
7-36，完成后效果如图
7-37。
1-36“设置单元格格式”对话框
1-37
完成效果
操作要求（13）
DCOUNTA
函数可返回列表或数据库中满足指定条件的记录字段（列）中的非空单元格
的个数。
先在
G67:
H68
区域中输入女优秀毕业生条件，如图7-38
设置女优秀毕业生条件
所示，然后选取单元格“E67”，单击菜单“公式”→“插入函数”，插入
函数对话框中，输入
函数参数对话框中的参数信息，如图7-39，单击确
定按钮。
也可直接在单元格“E67”中，输入公式“=DCOUNTA（B1:
O60,C1,G67:
H68）”，求出
统计总成绩前五名的平均总成绩。
1-38
1-39
操作要求（14）
设置条件区域，复制“基本信息表”中的如图7-40
所示的“H1：
K1”区域的标题信
息，粘贴到“各项成绩都及格的学生”工作表中的“A1：
D1”区域，设置及格条件，如
7-41
设置各项成绩及格条件所示。
1-40“H1：
K1”标题信息图
1-41
设置各项成绩及格条件
选中“各项成绩都及格的学生”工作表中的“A4”
单元格，单击菜单“数据”—“高
级”，打开高级筛选对话框，如图
1-42
高级筛选对话框
（1），先对条件区域与复制位置进
行设置。
把光标放在列表区域对应位置，在单击右边的，选择“基本信息表”中
“A1：
O60”区域数据，列表区域设置完成效果如图
1-43
高级筛选对话框
（2）所示。
单
击“确定”按钮，完成筛选。
高级筛
选对话框
（1）
选对话框
（2）
操作要求（15）
选中“各题目总成绩分段人数及比例”表中
单元格，单击“插入”---“数据透视表”
中“数据透视表”。
在“创建数据透视表”对话框中选择表区域，如图
7-44。
单击确定。
1-44“创建数据透视表”对话框
把“毕业设计题目”拖放到
单元格。
将总成绩拖放到左边行字段处，如图
7-45
1-45“毕业设计题目”拖放位置
7-46
1-46
总成绩拖放位置
（1）
再将总成绩拖放到值字段处，如图
7-47
1-47
总成绩拖放位置
（2）
再将总成绩拖放到求和项：
总成绩处。
效果如图
7-48
1-48
总成绩求和项
”
在右侧下方处，点击汇总的第一种方式“求各项：
总成绩”右侧下拉菜单，
7-49，在弹出的快捷菜单中选择“值字段设置（N），如图
7-50，在“值字段设置”
对话框中“值汇总方式”选项卡中选择计算类型为“计数”，如图
7-51
确定，出现如图
7-52
效果。
1-49
求和项修改
1-50
值字段
设置
1-51
值汇总
方式修改
1-52
修改值汇总方式后效果
在右侧下方处，点击汇总的第二种方式“求各项：
1-52，在弹出的快捷菜单中选择“值字段设置（N）”，如图
7-53，在“值字段设置”
对话框中“值显示方式”选项卡中选择“值显示方式（A）”为“全部汇总百分比”，确定，
出现如图
7-54
1-53
修改值显示方式
1-54
修改值显示方式后效果
选中总成绩中
60
分以下的成绩，单击右键，选择“创建组”，如图
7-55，同理创建
60-70
分创建组，70-80
分创建组,80
分以上创建组。
7-56
1-55
创建组
1-56
创建组后效果
右击总成绩列某个单元格，选择“删除“总成绩”，如图
7-57
删除后完成效果
7-58
1-57
删除总成绩字段
1-58
删除总成绩字段后效果
修改“总成绩
2”列中各分组名称，同时修改图中“数据”列中“计数项：
总成绩”
为“人数”，“求和项：
总成绩
2”为“占总人数比例”，效果如图
7-59
1-59
修改字段信息
操作要求（16）
选择“不同毕业设计成绩信息”表中“A1：
F6”数据区域，单击“插入”---“柱形图”
，单击柱形图下方三角箭头，选择“圆锥图”中的簇状柱形图，如图
7-60
所示，出现
7-61
所示的圆锥图。
【实战训练】图
1-60“柱形图”下拉列表【实战训练】
1-61
圆锥图
选中图
中圆锥图的右侧“垂直（值）铀”，双击，弹出如图
7-62
所示“设置坐
标铀格式”对话框，设置最大值为
100，最小值为
0，主要刻度单位为
20，确定。
选中图中圆锥图的左侧“图例”，双击，弹出如图
7-63
所示“设置图例格式”对话框，
调整图例位置为底部。
1-62
设置坐
标轴格式
设置坐标轴值与图例后，效果如图
7-64
1-63
设置图例
格式
1-64
修改坐标轴与图例格式后效果
选中该图表，选择“设计”---“图表布局”中“布局
3”，如图
7-65，输入图表标题
“不同毕业设计各类成绩平均分”，并设置合适的字体大小，图表完成后最终效果如图
7-66。
1-65
图表布局
1-66
图表最终效果
操作要求（17）
选择“基本信息表”中“A1：
O60”区域，单击鼠标右键，弹出“设置单元格格式”
对话框，在弹出的对话框中，单击边框，在样式中选择单线样式，点击预置中“内部”，如
7-67
设
升级会员 