计算机应用基础课程excel综合实训材料.docx

计算机应用基础课程excel综合实训材料.docx

《计算机应用基础课程excel综合实训材料.docx》由会员分享，可在线阅读，更多相关《计算机应用基础课程excel综合实训材料.docx（32页珍藏版）》请在冰豆网上搜索。

计算机应用基础课程excel综合实训材料

第1章OfficeExcel2010高级应用技术

1.1学生成绩管理

一、考查知识点

数据清单、格式设置、公式与函数、排序、筛选、分类汇总和图表等级知识，利用多种公式和函数对单元格中的数据进行运算；对各种数据可以进行格式设置；分析汇总各单元格中的数据信息，把相关数据用统计图表的形式表示，运用Excel2010电子表格强大的数据管理功能，进行相应的数据处理和分析。

二、知识点解析

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

图71原始工作表

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区域，单击“数据”选项卡“数据工具”组中的“数据有效性”命令，弹出如图72所示的对话框，选择“设置”选项卡进行“有效性条件”设置，在“输入信息”选项卡中进行相关输入信息的设置。

图72设置“数据有效性”对话框

（3）选择D3至G23区域，单击“开始”选项卡“样式”组中的“条件格式”按钮，选择“突出显示单元格规则”→“其他规则”命令，弹出“新建格式规则”对话框，设置单元格数值为“小于60”，再单击“格式”对话框，设置字体为“红色”、“加粗”，如图73所示，单击“确定”按钮。

图73条件格式设置

（4）在单元格H3中输入公式“=AVERAGE（D3:

G3）”，计算该学生的平均分，利用Excel2010的序列填充功能，求出其他学生的平均分，如图74所示。

图74计算“平均分”

（5）在单元格I3中输入公式“=YEAR（NOW（））-MID（C3,7,4）”，计算该学生的年龄，利用Excel2010的序列填充功能，求出其他学生年龄，如图75所示。

图75计算“年龄”

（6）在单元格J3中输入公式“=IF（H3>=60,"合格","不合格"）”，计算该学生的等级，利用Excel2010的序列填充功能，求出其他学生的等级，如图76所示。

图76计算“等级”

（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）”，计算出该等级所占比例，其它依次类推，如图77所示。

图77统计各分数段人数

（8）选中C25至F27，单击“插入”选项卡“图表”组中的“饼图”按钮，选择“三维饼图”命令，根据各等级的比例生成如图78所示的三维饼图，放在统计表下。

图78各分数段人数所占比例图表

（9）插入一张工作表，工作表标签颜色设成红色，工作表重命名为“优秀学生信息表”（略）；返回原始工作表，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，各字段旁出现下拉按钮。

单击“平均分”字段的下拉按钮，在弹出的下拉菜单中执行“数字筛选”→“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，如图79所示。

筛选出平均成绩大于等于85分的学生记录，将这些记录复制到“优秀学生信息表”中，单击“数据”选项卡“排序和筛选”组中的“筛选”命令，取消“学生信息表”中的记录筛选。

图79设置“自动筛选”对话框

（10）建立“按组分类汇总”工作表，把“学生信息表”中的成绩表复制到该表中，按“组别”字段升序排序，单击“数据”选项卡“分级显示”组中的“分类汇总”命令，按“组别”进行分类汇总，分别计算出A、B组的“数学”、“英语”、“物理”、“化学”平均分，如图710所示，单击“确定”按钮，得到汇总结果。

图710设置“分类汇总”对话框

1.2毕业设计成绩管理

一、项目介绍

毕业设计成绩信息记录了毕业设计的基本情况，随着高校办学规模的扩大，各专业学生数量和毕业设计题目数量都在增加，毕业设计（毕业实习）指导教师在学生毕业设计与毕业结束后，需要给学生评定成绩，成绩是毕业设计（毕业实习）整个过程的最终体现。

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

通过该案例的学习可掌握Excel2010工作表多表间的数据选择和表示、多表间的公式与函数的使用、多表间的合并计算、选择性粘贴等功能的综合应用。

二、操作数据

图711基本信息表数据内容

图712基础分表数据内容

三、操作要求

（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）在“不同毕业设计成绩信息表”工作表中，以各个毕业设计题目所有同学的设计成绩、论文成绩、实习成绩、总成绩的平均分制作柱形圆锥图，并加以修饰，结果如下图713所示：

【实战训练】图713平均分柱形圆锥图

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

a、设置整个工作表字体为10号，行高为16，列为最适合的列宽，对齐方式为水平居中与垂直居中，外边框为双线，内边框为单实线；

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

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

d、设置打印区域为A1:

O60，每页都显示标题行；

四、操作分析与过程

操作要求

（1）

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

把鼠标放在A2单元格填充柄上，如图714所示。

双击填充柄填充A3-A60单元格数据或当鼠标变成十字形状时，按着鼠标左键不放，向下填充A3-A60单元格数据。

图714A2单元格填充柄

操作要求

（2）

在C2单元格中输入公式“=IF（MOD（MID（D2,17,1）,2）=0,"女","男"）”，计算该学生的性别，利用Excel2010的序列填充功能，求出其他学生年性别。

如图715所示。

【实战训练】图715计算学生的性别

操作要求（3）

CONCATENATE函数可将最多255个文本字符串联接成一个文本字符串，MID函数可返回从指定位置开始的特定数目的字符串。

在E2单元格中输入公式“=CONCATENATE（MID（D2,7,4）,"年",MID（D2,11,2）,"月",MID（D2,13,2）,"日"）”，计算该学生的出生日期，利用Excel2010的序列填充功能，求出其他学出生日期，如图716所示。

【实战训练】图716计算学出生日期

也可通过单击菜单“公式”→“插入函数”，插入CONCATENATE函数，在CONCATENATE函数对话框中，输入如图717参数信息，单击确定按钮。

图717CONCATENATE函数参数对话框

操作要求（4）

在M2单元格中输入公式“="66"&RIGHT（L2,4）”，计算该学生的手机短号，利用Excel2010的序列填充功能，求出其他学手机短号，如图718所示。

图718计算学生短号

操作要求（5）

选取单元格“G2”，单击菜单“公式”→“插入函数”，如图719所示，系统弹出如图720所示的“插入函数”对话框。

【实战训练】图719“插入函数”对话框

【实战训练】图720“选择函数”对话框

在“或选择类别”下拉列表中选择“查找与引用”，在“选择函数”列表中选择“VLOOKUP”，系统继续弹出如图721所示的“函数参数”对话框。

在“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”，计算该学生的毕业设计基础分，利用Excel2010的序列填充功能，求出其他学生的毕业设计基础分。

【实战训练】图721VLOOKUP函数参数对话框

操作要求（6）

在K2单元格中输入公式“=G2+H2*0.3+I2*0.3+J2*0.1”，计算该学生的总成绩，利用Excel2010的序列填充功能，求出其他学生的总成绩，如图722所示。

【实战训练】图722计算学生总成绩

操作要求（7）

在N2单元格中输入公式“=IF（K2>=85,"优秀毕业生",""）”，计算该学生是否为优秀毕业生，利用Excel2010的序列填充功能，判断其他学生是否为优秀毕业生，如图723所示。

【实战训练】图723计算是否为优秀毕业生

操作要求（8）

可在O2单元格中输入公式“=IF（OR（H2="",I2="",J2=""）,"成绩缺少，不予毕业",""）”，如图724所示，计算该学生是否有成绩缺少，给出相应提示信息。

也可以通过插入公式方式来操作。

选取单元格“O2”，单击菜单“公式”→“插入函数”，插入IF函数，在IF函数对话框中，输入如图725参数信息。

光标放在Logical_test对应区域，单击

，在函数列表中单击其他函数，如图726所示，出现插入函数对话框，选择OR函数，如图727，在OR函数对话框中，输入如图728参数信息，按确定。

利用Excel2010的序列填充功能，判断其他学生是否有成绩缺少，给出相应提示信息。

图724计算是否不予毕业

【实战训练】图725IF函数参数对话框

图726函数列表

图727“选择函数”对话框

【实战训练】图728OR函数参数对话框

操作要求（9）

COUNTBLANK函数用于计算指定单元格区域中空白单元格的个数，分别在E63、E64、E65单元格中输入公式“=COUNTBLANK（H2:

H60）”、“=COUNTBLANK（I1:

I60）”、“=COUNTBLANK（J2:

J60）”，统计末提交毕业设计与毕业论文、未参加毕业实习的同学个数，其中H2:

H60区域为学生的设计成绩区域，I1:

I60区域为学生的论文成绩区域，J2:

J60学生的实习成绩区域，如图729。

【实战训练】图729计算空白单元格的个数

操作要求（10）

选取单元格“E73”，单击菜单“公式”→“插入函数”，插入AVERAGE函数，在AVERAGE函数对话框中，输入如图730参数信息，单击确定按钮。

【实战训练】图730AVERAGE函数参数对话框

也可以利用数组公式，在单元格“E73”中，输入公式“=AVERAGE（LARGE（K2:

K60,{1,2,3,4,5}））”，求出统计总成绩前五名的平均总成绩，如图731所示。

【实战训练】图731计算前五名的平均总成绩

操作要求（11）

把光标放在A1：

O60任意单元格上，单击“开始”--“排序和刷选”，选择“自定义排序”，如图732，在排序对话框中主关键字选择“毕业设计题目”，次序选择“自定义序列”，在自定义对话框中，输入序列仓库管理系统、人事管理系统、图书管理系统、旅游网站、公司企事业网站，如图733，点添加按钮，把该序列添加到自定义序列中，按确定，再在排序对话框中按确定按钮即可，如图734所示。

【实战训练】图732排序对话框

【实战训练】图733自定义序列对话框

【实战训练】图734排序对话框

操作要求（12）

选中“不同毕业设计成绩信息”表中A1单元格，单击“数据”---“合并计算”，在合并计算对话框中选择函数为“平均值”，单击引用位置右边

按钮，选择“基本信息表”中F1:

K60区域数据，单击添加按钮，再单击确定按钮即可，如图735。

【实战训练】图735“合并计算”对话框

选择“C2：

F6”区域，单击右键，选择“设置单元格格式”，选择“数字”---“数值”，设置小数位数为1位，如图736，完成后效果如图737。

【实战训练】图736“设置单元格格式”对话框

【实战训练】图737完成效果

操作要求（13）

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

先在G67:

H68区域中输入女优秀毕业生条件，如图738设置女优秀毕业生条件所示，然后选取单元格“E67”，单击菜单“公式”→“插入函数”，插入DCOUNTA函数，在DCOUNTA函数对话框中，输入DCOUNTA函数参数对话框中的参数信息，如图739，单击确定按钮。

也可直接在单元格“E67”中，输入公式“=DCOUNTA（B1:

O60,C1,G67:

H68）”，求出统计总成绩前五名的平均总成绩。

【实战训练】图738设置女优秀毕业生条件

图739DCOUNTA函数参数对话框

操作要求（14）

设置条件区域，复制“基本信息表”中的如图740所示的“H1：

K1”区域的标题信息，粘贴到“各项成绩都及格的学生”工作表中的“A1：

D1”区域，设置及格条件，如图741设置各项成绩及格条件所示。

图740“H1：

K1”标题信息

图741设置各项成绩及格条件

【实战训练】图742高级筛选对话框

（1）

图743高级筛选对话框

（2）

选中“各项成绩都及格的学生”工作表中的“A4”单元格，单击菜单“数据”—“高级”，打开高级筛选对话框，如图742高级筛选对话框

（1），先对条件区域与复制位置进行设置。

把光标放在列表区域对应位置，在单击右边的

，选择“基本信息表”中“A1：

O60”区域数据，列表区域设置完成效果如图743高级筛选对话框

（2）所示。

单击“确定”按钮，完成筛选。

操作要求（15）

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

在“创建数据透视表”对话框中选择表区域，如图744。

单击确定。

【实战训练】图744“创建数据透视表”对话框

把“毕业设计题目”拖放到A1单元格。

将总成绩拖放到左边行字段处，如图745所示。

【实战训练】图745“毕业设计题目”拖放位置

把“毕业设计题目”拖放到A1单元格。

将总成绩拖放到左边行字段处，如图746所示。

【实战训练】图746总成绩拖放位置

（1）

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

【实战训练】图747总成绩拖放位置

（2）

再将总成绩拖放到求和项：

总成绩处。

效果如图748所示。

【实战训练】图748总成绩求和项

在右侧下方

处，点击汇总的第一种方式“求各项：

总成绩”右侧下拉菜单，如图749，在弹出的快捷菜单中选择“值字段设置（N）”，如图750，在“值字段设置”对话框中“值汇总方式”选项卡中选择计算类型为“计数”，如图751确定，出现如图752效果。

【实战训练】图749求和项修改

【实战训练】图750值字段设置

【实战训练】图751值汇总方式修改

【实战训练】图752修改值汇总方式后效果

在右侧下方

处，点击汇总的第二种方式“求各项：

总成绩”右侧下拉菜单，如图752，在弹出的快捷菜单中选择“值字段设置（N）”，如图753，在“值字段设置”对话框中“值显示方式”选项卡中选择“值显示方式（A）”为“全部汇总百分比”，确定，出现如图754效果。

【实战训练】图753修改值显示方式

【实战训练】图754修改值显示方式后效果

选中总成绩中60分以下的成绩，单击右键，选择“创建组”，如图755，同理创建60-70分创建组，70-80分创建组,80分以上创建组。

效果如图756所示。

【实战训练】图755创建组

【实战训练】图756创建组后效果

右击总成绩列某个单元格，选择“删除“总成绩”，如图757所示。

删除后完成效果如图758所示。

【实战训练】图757删除总成绩字段

【实战训练】图758删除总成绩字段后效果

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

总成绩”为“人数”，“求和项：

总成绩2”为“占总人数比例”，效果如图759所示。

【实战训练】图759修改字段信息

操作要求（16）

选择“不同毕业设计成绩信息”表中“A1：

F6”数据区域，单击“插入”---“柱形图”，单击柱形图下方

三角箭头，选择“圆锥图”中的簇状柱形图，如图760所示，出现如图761所示的圆锥图。

【实战训练】图760“柱形图”下拉列表

【实战训练】图761圆锥图

选中图761中圆锥图的右侧“垂直（值）铀”，双击，弹出如图762所示“设置坐标铀格式”对话框，设置最大值为100，最小值为0，主要刻度单位为20，确定。

选中图中圆锥图的左侧“图例”，双击，弹出如图763所示“设置图例格式”对话框，调整图例位置为底部。

【实战训练】图762设置坐标轴格式

【实战训练】图763设置图例格式

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

【实战训练】图764修改坐标轴与图例格式后效果

选中该图表，选择“设计”---“图表布局”中“布局3”，如图765，输入图表标题“不同毕业设计各类成绩平均分”，并设置合适的字体大小，图表完成后最终效果如图766。

【实战训练】图765图表布局

【实战训练】图766图表最终效果

操作要求（17）

选择“基本信息表”中“A1：

O60”区域，单击鼠标右键，弹出“设置单元格格式”对话框，在弹出的对话框中，单击边框，在样式中选择单线样式，点击预置中“内部”，如图767设置单元格格式对话框

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

（二）所示。

设置好内部与外部边框后，单击“确定”按钮。

【实战训练】图767设置单元格格式对话框

（一）

【实战训练】图768设置单元格格式对话框

（二）

单击“对齐”，分别设置水平对齐、垂直对齐方式为“居中”。

如图769所示。

【实战训练】图769设置对齐方式

选中整个数据表，单击“格式”中的“行高”，如图770所示，设置单元格大小中的行高为16，如图771所示，列宽为“自动调整列宽”，如图772所示。

【实战训练】图770选择行高

【实战训练】图771行高设置

【实战训练】图772自动调整列宽

在“页面布局”菜单中“页面设置”功能区中：

单击“纸张方向”按钮，选择“横向”，如图773所示；单击“纸张大小”按钮，设置纸张大小为B4，如图774所示；单击“打印区域”按钮，设置打印区域，如图775所示；单击“页面设置”右侧小斜拉箭头，如图776，自定义页眉和页脚，如图777和图778所示，同时，也可在页面设置对话框“工作表”选项卡中设置打印区域与每页都显示标题行，如图779所示。

【实战训练】图773纸张横向设置

【实战训练】图774设置纸张大小

【实战训练】图775设置打印区域

【实战训练】图776选择页面设置

【实战训练】图777页眉页脚设置

【实战训练】图778自定义页眉

【实战训练】图779打印区域与顶端标题设置

【项目小结】

本项目利用Excel2010对毕业设计与实习各项成绩进行管理，节