实验9 EXCEL操作[1]
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
5、对“学生信息表”中按以下的要求来设置表的格式： （1）所有数据都设置对齐方式为水平、垂直居中，字体为 12 磅蓝色隶书。 （2）将表的外边框设定为蓝色双线，内部为红色单线。表格的底纹为淡黄色，“补助”一列的 底纹为“浅绿色”。 6、对“学生成绩表”设置“表格套用格式”为“古典Ⅱ”类型。选择表中各科成绩所在的数据 区域，利用条件格式化命令，将所有成绩小于 60 分的单元格底纹设置为浅黄色，并带有 6.25 的灰 色小点图案；所有成绩介于 60 分到 70 分之间的单元格底纹设置为浅蓝色。（试着改变个别单元格的 数据，观察其底纹是否按条件设置自动变化。）
图 9-4
11、将工作表“信息”中的 A2:H9 区域中的基本数据复制到工作表“信息”中的 A13:H20 区域 中。对 A13:H20 区域中的数据按“班级”笔画升序及“学号”降序排序。(请记录你在下面操作中所 用到的公式及主要步骤)
①编辑公式计算每位学生的折合分（折合分=语文×0.4+数学×0.3+体育×0.3）。[例如：王小军 的折合分为：=B3*0.4+C3*0.3+F3*0.3]
男
300 1987-4-6
男
280 1988-10-2
女
260 1987-11-1
女
260 1988-6-25
学号
表 9-2
7、利用“学生成绩表”的基本数据，建
立柱形图表（如图 9-1），图表位于 A17:H26
区域。 8、在工作表“学生成绩表”中，插入前 4
位学生的“语文、英语和体育成绩图表”的柱 形图，如图 9-2 所示，将图表的填充背景设置 为“白色大理石”效果，边框为“圆角”，字 体为深蓝色隶书 10 号, 图表
学生成绩图表
分数
100
语文
50
数学
0
物理
王小军 李正月 张宏明 赵大生 张大明 王芳 李青 英语
姓名
体育
图 9-1
网络信息与计算中心
-2-
大学计算机基础实验指导 【实验九】
位于 A27:E36 区域。
语文、英语、体育成绩图表
分数
制表人：XXX
100
50
0
王小军
李正月
张宏明
姓名
赵大生
语文 英语 体育
【实验内容】
1、在磁盘 D 下你的个人文件夹中建立一个 Excel 工作簿文件，主文件名为你的学号，并按照下 列要求完成所有操作。
2、将工作表 Sheet1 更名为“学生成绩表”，在 A1:H15 区域建立如表 9-1 所示“学生成绩表”， 最后一行填写制表人信息。将 A1:H1 区域合并，并输入标题，标题的格式设置为 14 磅、蓝色、华 文行楷、居中、黄色底纹。
④用 COUNTIF 函数计算良好人数（总分在 350～399 之间）。用 COUNTIF 函数和 COUNT 函数 计算优秀比率（总分在 400 及以上的人数比率，采用百分比格式）。
12、在工作表“信息”中，进行下列操作： ①计算每位同学的年龄（保留整数部分）。（要求掌握 2 种以上计算年龄的方法,注意要给计算结 果选择合适的数字格式。） [例如：王小军的出生日期在 F3 单元格，则他的年龄为： =YEAR(TODAY())-YEAR(F3) 或者 =(TODAY()-F3)/365 ] ②在“年龄”右侧插入“说明”列，利用 IF 函数为本月过生日的同学做提示，即在相应单元格 内显示“生日快乐！”，其余单元格不显示信息。 [ 例 如 ： 如 图 9-5 所 示 ， 王 小 军 的 出 生 日 期 在 F3 单 元 格 ， 则 H3 单 元 格 的 公 式 为 ： =IF(MONTH(F3)=MONTH(TODAY()),"生日快乐！","") ]
分级显示按钮
网络信息与计算中心
图 9-8
图 3-6
-5-
大学计算机基础实验指导 【实验九】
③用“自动筛选”命令选出语文成绩>=80 且体育成绩>=90 的数据（分两步进行），将结果截图
放在当前工作表的右侧。
④取消“自动筛选”，实验“高级筛选”功能（要求筛选条件位于 B11:D12 区域，筛选结果数据
网络信息与计算中心
-1-
大学计算机基础实验指导 【实验九】
学生成绩表
姓名 王小军 李正月 张宏明 赵大生 张大明 王芳 李青 课程平均 最高分 最低分
语文 80 86 86 73 92 78 59
数学 62 93 95 76 67 72 87
物理 78 87 47 80 71 81 67
英语 56 66 81 78 91 65 77
体育 90 84 70 92 90 89 87
折合分
总分
良好人数 优秀比率
制表人
学号
表 9-1
学生信息表
班级 学号 姓名 性别 补助 出生日期 年龄 补助等级
三班
王小军
一班
李正月
二班
张宏明
二班
赵大生
一班
张大明
一班
王芳
三班
李青
平均补助
制表人
男
360 1985-5-20
女
340 1986-3-3
男
320 1988-9-30
3、将工作表 Sheet2 更名为“学生信息表”，并在 A1:H11 区域中建立如表 9-2 所示“学生信息 表”。将 A1：H1 区域中合并居中并输入标题文字，标题的格式设置为 16 磅、蓝色、华文新魏。表 格最后一行填写你的个人信息。
4、将“学生信息表”中的 “学号”列的数据从 2010001 开始，按步长为 2 的等差数列由上至 下填充。“补助”一列的数据格式设置为（￥#,###.##）格式。“出生日期”列的数据格式设置为“1988 年 8 月 8 日”样式。
[例如：如图 9-6 所示，王小军在 J3 单元格的信息为： =IF(成绩!I3<=3,成绩!I3,"") ]
图 9-6 网络信息与计算中心
-4-
大学计算机基础实验指导 【实验九】
13、请思考上面题目中，哪些操作采用了相对引用、绝对引用、混合引用或三维引用。 14、将工作表“信息”中的 A2:H9 区域中的基本数据复制到工作表“信息”中的 A13:H20 区域 中。对 A13:H20 区域中的数据按“班级”笔画升序及“学号”降序排序。 15、将工作表“Sheet3”更名为“分类汇总”。将工作表“信息”中的 A2:E9 区域中的基本数据 复制到“分类汇总”中 A1:E8 区域中，在工作表“分类汇总”中，利用分类汇总命令，按“班级” 分别汇总出补助的平均值。分别单击各“分级显示”按钮，观察其相应级别的分类汇总结果。 提示：①进行分类汇总前，先要按分类字段“班级”排序。 ②单击“数据”→“分类汇总”，在分类汇总对话框内（如图 9-7）按要求进行设置。汇总结果 如图 9-8 所示。
自 A21 单元格开始显示），筛选条件为：
语文 >=70
数学 >=70
物理 >=70
思考：若上述三条件为“或”关系，条件区域应该如何表示。
17、 对工作表“成绩”进行页面设置如下：
①纸张大小为 A4，方向为“纵向”，页边距上下设为 2cm，左右设为 1.5cm，内容在水平和垂直
方向均居中。
②页眉：左边插入文件名及工作表标签；中部为“你的班级”，右侧为你的姓名。页脚：左侧插
入打印日期和时间，中部插入页码，右侧插入总页数。
③打印预览工作表“成绩”页面，然后适当调节表格宽度，图形大小及位置等，使页面布局美
观、清晰。
源自文库
网络信息与计算中心
-6-
②用函数分别计算每位学生的总分和每门课程的课程平均分、最高分、最低分。（计算数据保留 1 位小数）
③在“总分”右侧追加一列，标题为“名次”，用 RANK 函数计算每位同学的名次（按总分由 大到小）。[例如：王小军的名次为：=RANK(H3,H$3:H$9)]
网络信息与计算中心
-3-
大学计算机基础实验指导 【实验九】
图 9-2
9、在工作表“学生成绩表”中，插入前 4 位同学的“语文成绩”圆柱图表，如图 9-3 所示，图
表位于 A37:E47 区域。
语文成绩
语文
65 70 75 80 85 90 分数
赵大生 张宏明 李正月 王小军
图 9-3
10、在工作表“学生成绩表”中，利用饼形图表示“李正月的物理成绩对总分的贡献”，如图9-4 所示，要求显示出类别名称、值和百分比信息，图表位于A48:H58区域。
图 9-7
16、在工作表“分类汇总”右侧插入工作表“筛选”，将工作表“成绩”中的“学生成绩表”的 基本数据（A2:F9 区域）复制到工作表“筛选”的 A1:F8 区域中。在工作表“筛选”中，完成如下 练习（要求将每完成一个自动筛选结果后，用截屏的方法将有效数据区保存为图片至本工作表中）：
①用“自动筛选”命令从全体学生中筛选出体育成绩位于前 3 名的学生的数据； ②用“自动筛选”命令从全体学生中筛选出姓“王”或 姓“张”的学生的数据；
图 9-5
③在“补助等级”列，利用 IF 函数显示信息：补助大于等于“平均补助”者为“A”，补助小于 “平均补助”者为“B”。
[例如：如图 9-6 所示，王小军的补助在 E3 单元格，平均补助值在 E10 单元格，则 I3 单元格的 公式为： =IF(E3-$E$10>=0,"A","B") ]
④在“补助等级”列右侧插入一列，标题为“名次”，利用 IF 函数和工作表“成绩”中的“名 次”列数据，显示名次在前 3 名同学的名次信息，其余单元格不显示信息。（试将“成绩”工作表中 李正月的语文成绩减少 20 分，观察“成绩”和“信息”工作表中“名次”列信息的变化情况，然后 还原之。）
大学计算机基础实验指导 【实验九】
实验九 Excel 操作
【实验目的】
1、掌握 Excel 电子工作簿的创建方法。 2、掌握 Excel 中不同类型数据的输入方法。 3、掌握 Excel 中有规律数据的自动填充方法。 4、掌握编辑工作表和格式化工作表的方法。 5、掌握 Excel 中图表创建、编辑和格式化等操作。 6、掌握 Excel 中使用公式、函数计算数据的方法。 7、掌握 Excel 中相对引用、绝对引用、混合引用和三维引用。 8、掌握 Excel 中数据的排序、分类汇总、筛选等数据管理的基本方法。 9、掌握 Excel 打印工作表内容的页面设置操作。
图 9-4
11、将工作表“信息”中的 A2:H9 区域中的基本数据复制到工作表“信息”中的 A13:H20 区域 中。对 A13:H20 区域中的数据按“班级”笔画升序及“学号”降序排序。(请记录你在下面操作中所 用到的公式及主要步骤)
①编辑公式计算每位学生的折合分（折合分=语文×0.4+数学×0.3+体育×0.3）。[例如：王小军 的折合分为：=B3*0.4+C3*0.3+F3*0.3]
男
300 1987-4-6
男
280 1988-10-2
女
260 1987-11-1
女
260 1988-6-25
学号
表 9-2
7、利用“学生成绩表”的基本数据，建
立柱形图表（如图 9-1），图表位于 A17:H26
区域。 8、在工作表“学生成绩表”中，插入前 4
位学生的“语文、英语和体育成绩图表”的柱 形图，如图 9-2 所示，将图表的填充背景设置 为“白色大理石”效果，边框为“圆角”，字 体为深蓝色隶书 10 号, 图表
学生成绩图表
分数
100
语文
50
数学
0
物理
王小军 李正月 张宏明 赵大生 张大明 王芳 李青 英语
姓名
体育
图 9-1
网络信息与计算中心
-2-
大学计算机基础实验指导 【实验九】
位于 A27:E36 区域。
语文、英语、体育成绩图表
分数
制表人：XXX
100
50
0
王小军
李正月
张宏明
姓名
赵大生
语文 英语 体育
【实验内容】
1、在磁盘 D 下你的个人文件夹中建立一个 Excel 工作簿文件，主文件名为你的学号，并按照下 列要求完成所有操作。
2、将工作表 Sheet1 更名为“学生成绩表”，在 A1:H15 区域建立如表 9-1 所示“学生成绩表”， 最后一行填写制表人信息。将 A1:H1 区域合并，并输入标题，标题的格式设置为 14 磅、蓝色、华 文行楷、居中、黄色底纹。
④用 COUNTIF 函数计算良好人数（总分在 350～399 之间）。用 COUNTIF 函数和 COUNT 函数 计算优秀比率（总分在 400 及以上的人数比率，采用百分比格式）。
12、在工作表“信息”中，进行下列操作： ①计算每位同学的年龄（保留整数部分）。（要求掌握 2 种以上计算年龄的方法,注意要给计算结 果选择合适的数字格式。） [例如：王小军的出生日期在 F3 单元格，则他的年龄为： =YEAR(TODAY())-YEAR(F3) 或者 =(TODAY()-F3)/365 ] ②在“年龄”右侧插入“说明”列，利用 IF 函数为本月过生日的同学做提示，即在相应单元格 内显示“生日快乐！”，其余单元格不显示信息。 [ 例 如 ： 如 图 9-5 所 示 ， 王 小 军 的 出 生 日 期 在 F3 单 元 格 ， 则 H3 单 元 格 的 公 式 为 ： =IF(MONTH(F3)=MONTH(TODAY()),"生日快乐！","") ]
分级显示按钮
网络信息与计算中心
图 9-8
图 3-6
-5-
大学计算机基础实验指导 【实验九】
③用“自动筛选”命令选出语文成绩>=80 且体育成绩>=90 的数据（分两步进行），将结果截图
放在当前工作表的右侧。
④取消“自动筛选”，实验“高级筛选”功能（要求筛选条件位于 B11:D12 区域，筛选结果数据
网络信息与计算中心
-1-
大学计算机基础实验指导 【实验九】
学生成绩表
姓名 王小军 李正月 张宏明 赵大生 张大明 王芳 李青 课程平均 最高分 最低分
语文 80 86 86 73 92 78 59
数学 62 93 95 76 67 72 87
物理 78 87 47 80 71 81 67
英语 56 66 81 78 91 65 77
体育 90 84 70 92 90 89 87
折合分
总分
良好人数 优秀比率
制表人
学号
表 9-1
学生信息表
班级 学号 姓名 性别 补助 出生日期 年龄 补助等级
三班
王小军
一班
李正月
二班
张宏明
二班
赵大生
一班
张大明
一班
王芳
三班
李青
平均补助
制表人
男
360 1985-5-20
女
340 1986-3-3
男
320 1988-9-30
3、将工作表 Sheet2 更名为“学生信息表”，并在 A1:H11 区域中建立如表 9-2 所示“学生信息 表”。将 A1：H1 区域中合并居中并输入标题文字，标题的格式设置为 16 磅、蓝色、华文新魏。表 格最后一行填写你的个人信息。
4、将“学生信息表”中的 “学号”列的数据从 2010001 开始，按步长为 2 的等差数列由上至 下填充。“补助”一列的数据格式设置为（￥#,###.##）格式。“出生日期”列的数据格式设置为“1988 年 8 月 8 日”样式。
[例如：如图 9-6 所示，王小军在 J3 单元格的信息为： =IF(成绩!I3<=3,成绩!I3,"") ]
图 9-6 网络信息与计算中心
-4-
大学计算机基础实验指导 【实验九】
13、请思考上面题目中，哪些操作采用了相对引用、绝对引用、混合引用或三维引用。 14、将工作表“信息”中的 A2:H9 区域中的基本数据复制到工作表“信息”中的 A13:H20 区域 中。对 A13:H20 区域中的数据按“班级”笔画升序及“学号”降序排序。 15、将工作表“Sheet3”更名为“分类汇总”。将工作表“信息”中的 A2:E9 区域中的基本数据 复制到“分类汇总”中 A1:E8 区域中，在工作表“分类汇总”中，利用分类汇总命令，按“班级” 分别汇总出补助的平均值。分别单击各“分级显示”按钮，观察其相应级别的分类汇总结果。 提示：①进行分类汇总前，先要按分类字段“班级”排序。 ②单击“数据”→“分类汇总”，在分类汇总对话框内（如图 9-7）按要求进行设置。汇总结果 如图 9-8 所示。
自 A21 单元格开始显示），筛选条件为：
语文 >=70
数学 >=70
物理 >=70
思考：若上述三条件为“或”关系，条件区域应该如何表示。
17、 对工作表“成绩”进行页面设置如下：
①纸张大小为 A4，方向为“纵向”，页边距上下设为 2cm，左右设为 1.5cm，内容在水平和垂直
方向均居中。
②页眉：左边插入文件名及工作表标签；中部为“你的班级”，右侧为你的姓名。页脚：左侧插
入打印日期和时间，中部插入页码，右侧插入总页数。
③打印预览工作表“成绩”页面，然后适当调节表格宽度，图形大小及位置等，使页面布局美
观、清晰。
源自文库
网络信息与计算中心
-6-
②用函数分别计算每位学生的总分和每门课程的课程平均分、最高分、最低分。（计算数据保留 1 位小数）
③在“总分”右侧追加一列，标题为“名次”，用 RANK 函数计算每位同学的名次（按总分由 大到小）。[例如：王小军的名次为：=RANK(H3,H$3:H$9)]
网络信息与计算中心
-3-
大学计算机基础实验指导 【实验九】
图 9-2
9、在工作表“学生成绩表”中，插入前 4 位同学的“语文成绩”圆柱图表，如图 9-3 所示，图
表位于 A37:E47 区域。
语文成绩
语文
65 70 75 80 85 90 分数
赵大生 张宏明 李正月 王小军
图 9-3
10、在工作表“学生成绩表”中，利用饼形图表示“李正月的物理成绩对总分的贡献”，如图9-4 所示，要求显示出类别名称、值和百分比信息，图表位于A48:H58区域。
图 9-7
16、在工作表“分类汇总”右侧插入工作表“筛选”，将工作表“成绩”中的“学生成绩表”的 基本数据（A2:F9 区域）复制到工作表“筛选”的 A1:F8 区域中。在工作表“筛选”中，完成如下 练习（要求将每完成一个自动筛选结果后，用截屏的方法将有效数据区保存为图片至本工作表中）：
①用“自动筛选”命令从全体学生中筛选出体育成绩位于前 3 名的学生的数据； ②用“自动筛选”命令从全体学生中筛选出姓“王”或 姓“张”的学生的数据；
图 9-5
③在“补助等级”列，利用 IF 函数显示信息：补助大于等于“平均补助”者为“A”，补助小于 “平均补助”者为“B”。
[例如：如图 9-6 所示，王小军的补助在 E3 单元格，平均补助值在 E10 单元格，则 I3 单元格的 公式为： =IF(E3-$E$10>=0,"A","B") ]
④在“补助等级”列右侧插入一列，标题为“名次”，利用 IF 函数和工作表“成绩”中的“名 次”列数据，显示名次在前 3 名同学的名次信息，其余单元格不显示信息。（试将“成绩”工作表中 李正月的语文成绩减少 20 分，观察“成绩”和“信息”工作表中“名次”列信息的变化情况，然后 还原之。）
大学计算机基础实验指导 【实验九】
实验九 Excel 操作
【实验目的】
1、掌握 Excel 电子工作簿的创建方法。 2、掌握 Excel 中不同类型数据的输入方法。 3、掌握 Excel 中有规律数据的自动填充方法。 4、掌握编辑工作表和格式化工作表的方法。 5、掌握 Excel 中图表创建、编辑和格式化等操作。 6、掌握 Excel 中使用公式、函数计算数据的方法。 7、掌握 Excel 中相对引用、绝对引用、混合引用和三维引用。 8、掌握 Excel 中数据的排序、分类汇总、筛选等数据管理的基本方法。 9、掌握 Excel 打印工作表内容的页面设置操作。