计算机应用基础课程excel综合实训材料.docx
《计算机应用基础课程excel综合实训材料.docx》由会员分享,可在线阅读,更多相关《计算机应用基础课程excel综合实训材料.docx(17页珍藏版)》请在冰豆网上搜索。
计算机应用基础课程excel综合实训材料
第 1 章Office Excel 2010 高级应用技术
1.1 学生成绩管理
一、考查知识点
数据清单、格式设置、公式与函数、排序、筛选、分类汇总和图表等级知识,利用多
种公式和函数对单元格中的数据进行运算;对各种数据可以进行格式设置;分析汇总各单
元格中的数据信息,把相关数据用统计图表的形式表示,运用 Excel 2010 电子表格强大的
数据管理功能,进行相应的数据处理和分析。
二、知识点解析
1、创建学生信息表,如图 7-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 区域,单击“数据”选项卡“数据工具”组中的“数据有效性”
命令,弹出如图7-2 所示的对话框,选择“设置”选项卡进行“有效性条件”设置,在
“输入信息”选项卡中进行相关输入信息的设置。
图 1-2 设置“数据有效性”对话框
(3)选择 D3 至 G23 区域,单击“开始”选项卡“样式”组中的“条件格式”按钮,
选择“突出显示单元格规则”→“其他规则”命令,弹出“新建格式规则”对话框,设置
单元格数值为“小于 60”,再单击“格式”对话框,设置字体为“红色”、“加粗”,如图
7-3 所示,单击“确定”按钮。
图 1-3 条件格式设置
(4)在单元格 H3 中输入公式“=AVERAGE(D3:
G3)”,计算该学生的平均分,利用
Excel 2010 的序列填充功能,求出其他学生的平均分,如图 7-4 所示。
图 1-4 计算“平均分”
(5)在单元格 I3 中输入公式“=YEAR(NOW())-MID(C3,7,4)”,计算该学生的年龄,
利用 Excel 2010 的序列填充功能,求出其他学生年龄,如图 7-5 所示。
图 1-5 计算“年龄”
(6)在单元格 J3 中输入公式“=IF(H3>=60,"合格","不合格")”,计算该学生的等级,
利用 Excel 2010 的序列填充功能,求出其他学生的等级,如图 7-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)”,计算出该等级所占比例,其它依次类推,如图 7-7 所示。
图 1-7 统计各分数段人数
(8)选中 C25 至 F27,单击“插入”选项卡“图表”组中的“饼图”按钮,选择“三
维饼图”命令,根据各等级的比例生成如图 7-8 所示的三维饼图,放在统计表下。
图 1-8 各分数段人数所占比例图表
(9)插入一张工作表,工作表标签颜色设成红色,工作表重命名为“优秀学生信息表”
(略);返回原始工作表,单击“数据”选项卡“排序和筛选”组中的“筛选”命令,各字
段旁出现下拉按钮。
单击“平均分”字段的下拉按钮,在弹出的下拉菜单中执行“数字筛
选”→“自定义筛选”命令,弹出“自定义自动筛选方式”对话框,如
图7-9 所示。
筛选出平均成绩大于等于 85 分的学生记录,将这些记录复制到“优秀
学生信息表”中,单击“数据”选项卡“排序和筛选”组中的“筛选”命令,取消“学生
信息表”中的记录筛选。
图 1-9 设置“自动筛选”对话框
(10)建立“按组分类汇总”工作表,把“学生信息表”中的成绩表复制到该表中,
按“组别”字段升序排序,单击“数据”选项卡“分级显示”组中的“分类汇总”命令,
按“组别”进行分类汇总,分别计算出 A、B 组的“数学”、“英语”、“物理”、“化学”平
均分,如图 7-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) 在“不同毕业设计成绩信息表”工作表中,以各个毕业设计题目所有同学
的设计成绩、论文成绩、实习成绩、总成绩的平均分制作柱形圆锥图,并加以修饰,
结果如下图 7-13 所示:
【实战训练】图 1-13 平均分柱形圆锥图
(17) 对“基本信息表”进行设置:
a、 设置整个工作表字体为 10 号,行高为 16,列为最适合的列宽,对齐方式
为水平居中与垂直居中,外边框为双线,内边框为单实线;
b、 页面设置为横向,纸张为 B4,居中方式为水平居中;
c、眉中部设置为“07 计算机应用技术 1 班毕业成绩表”,右部为制表日期;
页脚右部插入页码;
d、 设置打印区域为 A1:
O60,每页都显示标题行;
四、操作分析与过程
操作要求
(1)
英文状态下,在 A2 单元格输入“’07010101”,然后按回车。
把鼠标放在 A2 单元格填
充柄上,如图7-14 所示。
双击填充柄填充 A3-A60 单元格数据或当鼠标变成十字形状时,
按着鼠标左键不放,向下填充 A3-A60 单元格数据。
【实战训练】图 1-14 A2 单元格填充柄
操作要求
(2)
在 C2 单元格中输入公式“=IF(MOD(MID(D2,17,1),2)=0,"女","男")”,计算该学生的
性别,利用 Excel 2010 的序列填充功能,求出其他学生年性别。
如图 7-15 所示。
【实战训练】图 1-15 计算学生的性别
操作要求(3)
CONCATENATE 函数可将最多 255 个文本字符串联接成一个文本字符串,MID 函数可返
回从指定位置开始的特定数目的字符串。
在 E2 单元格中输入公式
“=CONCATENATE(MID(D2,7,4),"年",MID(D2,11,2),"月",MID(D2,13,2),"日")”,计算该学
生的出生日期,利用 Excel 2010 的序列填充功能,求出其他学出生日期,如图 7-16 所示。
【实战训练】图 1-16 计算学出生日期
也可通过单击菜单“公式”→“插入函数”,插入 CONCATENATE 函数,在 CONCATENATE
函数对话框中,输入如图 7-17 参数信息,单击确定按钮。
【实战训练】图 1-17CONCATENATE 函数参数对话框
操作要求(4)
在 M2 单元格中输入公式“="66"&RIGHT(L2,4)”,计算该学生的手机短号,利用 Excel
2010 的序列填充功能,求出其他学手机短号,如图 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,基础分表!
$A$1:
$B$6,2,FALSE)”
,在单元格“G2”出现“30”,计算该学生的毕业设计基础分,利用 Excel 2010 的序列填
充功能,求出其他学生的毕业设计基础分。
【实战训练】图 1-21 VLOOKUP 函数参数对话框
操作要求(6)
在 K2 单元格中输入公式“=G2+H2*0.3+I2*0.3+J2*0.1”,计算该学生的总成绩,利用
Excel 2010 的序列填充功能,求出其他学生的总成绩,如图 7-22 所示。
【实战训练】图 1-22 计算学生总成绩
操作要求(7)
在 N2 单元格中输入公式“=IF(K2>=85,"优秀毕业生","")”,计算该学生是否为优秀毕
业生,利用 Excel 2010 的序列填充功能,判断其他学生是否为优秀毕业生,如图 7-23 所
示。
【实战训练】图 1-23 计算是否为优秀毕业生
操作要求(8)
可在 O2 单元格中输入公式“=IF(OR(H2="",I2="",J2=""),"成绩缺少,不予毕业","")”
,如图7-24 所示,计算该学生是否有成绩缺少,给出相应提示信息。
也可以通过插入公
式方式来操作。
选取单元格“O2”,单击菜单“公式”→“插入函数”,插入 IF 函数,在
IF 函数对话框中,输入如图7-25 参数信息。
光标放在 Logical_test 对应区域,单击
,在函数列表中单击其他函数,如图 7-26 所示,出现插入函数对话框,选择 OR 函数,
如图 7-27,在 OR 函数对话框中,输入如图 7-28 参数信息,按确定。
利用 Excel 2010 的
序列填充功能,判断其他学生是否有成绩缺少,给出相应提示信息。
【实战训练】图 1-24 计算是否不予毕业
【实战训练】图 1-25 IF 函数参数对话框
图 1-26 函数列表图 1-27“选择函数”对话框
【实战训练】图 1-28 OR 函数参数对话框
操作要求(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”,单击菜单“公式”→“插入函数”,插入 AVERAGE 函数,在
AVERAGE 函数对话框中,输入如图 7-30 参数信息,单击确定按钮。
【实战训练】图 1-30 AVERAGE 函数参数对话框
也可以利用数组公式,在单元格“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”区域,单击右键,选择“设置单元格格式”,选择“数字”---“数值”,
设置小数位数为 1 位,如图 7-36,完成后效果如图 7-37。
【实战训练】图 1-36“设置单元格格式”对话框
【实战训练】图 1-37 完成效果
操作要求(13)
DCOUNTA 函数可返回列表或数据库中满足指定条件的记录字段(列)中的非空单元格
的个数。
先在 G67:
H68 区域中输入女优秀毕业生条件,如图7-38 设置女优秀毕业生条件
所示,然后选取单元格“E67”,单击菜单“公式”→“插入函数”,插入 DCOUNTA 函数,在
DCOUNTA 函数对话框中,输入 DCOUNTA 函数参数对话框中的参数信息,如图7-39,单击确
定按钮。
也可直接在单元格“E67”中,输入公式“=DCOUNTA(B1:
O60,C1,G67:
H68)”,求出
统计总成绩前五名的平均总成绩。
【实战训练】图 1-38 设置女优秀毕业生条件
【实战训练】图 1-39 DCOUNTA 函数参数对话框
操作要求(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-42 高级筛
选对话框
(1)
【实战训练】 图 1-43 高级筛
选对话框
(2)
操作要求(15)
选中“各题目总成绩分段人数及比例”表中 A2 单元格,单击“插入”---“数据透视表”
中“数据透视表”。
在“创建数据透视表”对话框中选择表区域,如图 7-44。
单击确定。
【实战训练】图 1-44“创建数据透视表”对话框
把“毕业设计题目”拖放到 A1 单元格。
将总成绩拖放到左边行字段处,如图 7-45 所
示。
【实战训练】图 1-45“毕业设计题目”拖放位置
把“毕业设计题目”拖放到 A1 单元格。
将总成绩拖放到左边行字段处,如图 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 圆锥图
选中图 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 设