[中学教育]Excel 基础应用实验报告

实验四Excel电子表格实验报告

姓名（学号）：__________________ 年级（专业）：_____ ________成绩：________

实验时间：_______________________ _实验地点：__________________________ _

实验4-1：

一、实验目的：

掌握使用Excel进行电子表格处理的基本操作，编辑与格式化工作表和单元格。

二、实验内容：

1．在Excel中新建一个空白工作簿，文件名为“学号.xls”，保存在文件夹“实验4”中；2．将工作表“Sheet1”重命名为“成绩1”，输入下列内容：

学号姓名性别班级平时成绩期中成绩期末成绩

男一班85 72 80

女一班86 68 72

男二班76 80 84

女二班89 82 80

女一班92 94 91

女二班98 96 92

男二班60 59 60

女一班90 82 88

男一班71 74 70

男二班65 58 54 3．在工作表“成绩1”的最右侧插入两列，标题依次为“总评成绩”、“等级”；

4．调整“学号”、“姓名”、“性别”、“班级”、“等级”列的列宽为80个像素，调整“平时成绩”、“期中成绩”、“期末成绩”、“总评成绩”列的列宽为85个像素；

5．设置“平时成绩”只能为[60-100]的整数，“期中成绩”和“期末成绩”只能为[0-100]的整数；

6．设置标题行：行高为20磅，水平居中，垂直居中，字体为新宋体，字形为加粗，字号为12磅，底纹图案为25％灰色，颜色为浅橙色；

7．在工作表“成绩1”顶部插入一行，输入“学生成绩表”，字体为黑体，字号为20磅，颜色为蓝色，跨列居中（使它位于所制作表格的上方中央位置）。

8．为表格添加表格框线，内部为单实线，外部为双实线。

9．在“学号”列从上到下依次填充文本数据“2009001”至“2009010”，要求水平居中。10．在“姓名”列从上到下依次填充文本数据“某一”至“某十”，要求水平居中。11．要求“期末成绩”列能自动识别高于90分的成绩，并将其显示成蓝色粗体。

12．保存修改后的文件。

三、实验步骤：（学生自己填写）

1．桌面——右键——新建——文件夹——改名为“实验4”——Excel空白工作簿——改名为“20091060257”;

2.在“Sheet1”上右键——重命名为“成绩1”——在“成绩1”中输入指定内容;

3.在“成绩1”中的最右侧输入字段“总评成绩”和“等级”；

4.选中“学号”，“姓名”，“性别”，“班级”，“等级”列——将鼠标放在任意一列上——左右

移动鼠标调整列宽为80像素——对成绩列进行同样的操作，调整其列宽为85像素；

5.选中“平时成绩”一列——格式——单元格——数字——数值——小数位数设置为0位——确定——格式——条件格式——设置为要求的数值范围和格式——对“期中成绩”和“期末成绩”进行同样的操作，使其满足题目要求；

6.选中标题行——格式——单元格——设置其“字体”，“对齐”方式和“图案”为指定格式；

7.选中“成绩1”中最顶部的一行——插入——行——输入指定文字——并设置其为指定格式——选中表格的所有列——按“合并及居中”按钮；

8.选中表格——格式——单元格——边框——设置其为指定格式；

9.在“学号”列的第一个单元格输入“2009001”——在第二个单元格中输入“2009002”——选中这两个单元格——将鼠标放在该单元格的右下角——鼠标变为实心的十字架时，按住鼠标左键向下拖动，直到“2009010”——选中该列——格式——单元格——对齐——水平居中；

10.工具——选项——自定义序列——填充“某一，某二……某十”序列——在“姓名”一列的第一个单元格中输入“某一”——选中该单元格——将鼠标放在该单元格的右下角——当鼠标变为实心的十字架时拖动鼠标直到“某十”——选中该列——格式——单元格——对齐——水平居中；

11.选中“期末成绩”列——格式——条件格式——大于等于90——格式——设置为指定格式——确定；

12.点击“保存”按钮；

实验4-2：

一、实验目的：

掌握Excel中公式和函数的使用。

二、实验内容：

1．在工作表“成绩1”中，按照“总评成绩=平时成绩×10％+期中成绩×20％+期末成绩×70％”计算“总评成绩”，要求四舍五入成整数，并且如果不及格则显示成红色粗体；2．在“学生成绩表”右下角插入当前日期；

3．根据下面的“分数等级转换表”，计算“总评成绩”的等级，并记录在“等级”列中；

分数等级转换表优（>=）良（>=）中（>=）差（<）

89.5 74.5 59.5 59.5

4．在工作表“成绩1”中空白处输入下列内容：

成绩统计表

平均分

最高分

最低分

分数段90～100 80～89 70～79 60～69 0～59

人数

5．根据“总评成绩”计算得到“成绩统计表”中的“平均分”、“最高分”、“最低分”及各个“分数段”的“人数”；

6．保存修改后的文件。

三、实验步骤：（学生自己填写）

1.选中“总评成绩”列的第一个单元格——输入“=E3*%10+F3*20%+G3*70%”回车——选中该单元格——将鼠标放在该单元格的右下角——当鼠标变为实心的十字架时向下拖动鼠标——选中该列——格式——单元格——数字——数值——小数位数设置为0位——格式——条件格式——小于59.5——格式设置为指定格式；

2.选中右下角的单元格——插入——函数——“日期与时间”——TODAY——确定；

3.选中“等级”列的第一个单元格——输入“=IF(H3>=89.5,“优”，IF(H3>=7

4.5,“良”，IF(H3>=59.5,“中”，“差”)))——回车——选中该单元格——将鼠标放在该单元格的右下角，当鼠标变为实心的十字架时向下拖动鼠标；

4.在“成绩1”的空白处输入指定内容——选中第一行——合并及居中——对其余个单元格进行相应的操作，使其符合要求；

5.选中平均分所对应的单元格——插入——函数——选择“average”函数——选择区域为总评成绩——确定——用同样的操作分别使用“统计”类型函数中的“max”“min”函数求出最高分和最低分；

6.选中“人数”所对应的第一个单元格——插入——函数——统计——COUNTIF_确定——选择区域为“总评成绩”，条件为〉=90——确定——选中第二个单元格——输入“=COUNTIF(H3:H12,">=80")-COUNTIF(H3:H12,">=90")”——依次算出其余各段的人数；

7.点击“保存”按钮；

实验4-3：

一、实验目的：

掌握Excel中数据处理功能——排序、筛选、分类汇总与数据透视。

二、实验内容：

1．打开文件“学号.xls”，新建工作表“成绩2”，并将工作表“sheet2”和“sheet3”分别重命名为“成绩3”和“成绩4”；

2．将工作表“成绩1”中的“学生成绩表”中的主体内容（不含“学生成绩表”行和“当前日期”行）复制到工作表“成绩2”中，要求：

a)仅仅复制数值；

b)筛选出“班级”为二班，且“总评成绩”大于等于80分的学生，在工作表“成绩

2”中另辟一块区域显示筛选结果；

3．将工作表“成绩1”中的“学生成绩表”中的主体内容（不含“学生成绩表”行和“当前日期”行）复制到工作表“成绩3”中，要求：

a)仅仅复制数值；

b)根据“班级”分类汇总“平时成绩”、“期中成绩”、“期末成绩”、“总评成绩”的平

均值；

4．将工作表“成绩1”中的“学生成绩表”中的“性别”、“班级”、“总评成绩”的标题及内容复制到工作表“成绩4”中，要求：

a)仅仅复制数值；

b)制作数据透视表，行字段为“班级”，列字段为“性别”，数据项为“总评成绩”的

平均值；

5．保存修改后的文件。

三、实验步骤：（学生自己填写）

1.点击“工作表””栏——右键——插入——工作表——重命名为“成绩2”——分别选中“Sheet2”,“Sheet3”——右键——重命名为“成绩3”，“成绩4”；

2.选中“成绩1”中的主体内容——Ctrl+C_——打开“成绩2”——编辑——选择性粘贴——数值——将该主题内容复制到“成绩2”中的另一区域——对选择任意一个非空白的单元格——数据——筛选——自动筛选——分别对“班级”和“总评成绩”字段进行筛选；

3.同第二步复制数据——选中任意一个非空白单元格——数据——排序——班级——确定——数据——分类汇总——分类字段为“班级”——汇总方式为“平均值”——汇总项为

“平时成绩”，“期中成绩”，“期末成绩”，“总评成绩”；

4.选中指定列——右键——复制——打开“成绩4”——编辑——选择性粘贴——数值——确定——数据——数据透视表和数据透视图——按要求和系统提示制作数据透视表；

5.点击“保存”按钮；

实验4-4：

一、实验目的：

掌握Excel中图表的制作。

二、实验内容：

1．在工作簿“学号.xls”中新建工作表“成绩5”，将工作表“成绩1”中的“学生成绩表”

中的“学号”、“姓名”及各种成绩的标题及内容复制到工作表“成绩5”中，要求：

a)仅仅复制数值；

b)在工作表“成绩5”中制作“数据点折线”图表，系列有“平时成绩”、“期中成绩”、

“期末成绩”和“总评成绩”，分类轴（标题）为“学号”，数值轴（标题）为“成

绩”，靠右显示图例；

c)设置系列格式为下图所示形式：

2．保存修改后的文件

3．将“实验四Excel电子表格”的实验报告保存在文件夹“实验4”中；

4．将文件夹“实验4”提交到“e-learning”的相应课程中。

三、实验步骤：（学生自己填写）

1.将鼠标放在左下角的工作表名上——右键——插入——工作表——重命名为“成绩5”——选中指定列——按实验4-3中的操作步骤完成数据的复制——选中任意一个单元格——插入——图表——标准类型——数据点折线——设置其系列为题目所要求的项目——设置图表的其它格式使其符合题目要求；

2.点击图例——右键——图例格式——修改其格式为指定格式；

3.点击“保存”按钮；

4.文件——另存为——实验4；

实验思考题及解答：

1．如何实现单元格中的文本自动换行？

2．如何使用“选择性粘贴”命令？它与直接使用“粘贴”命令有什么不同？

3．说明相对引用、绝对引用和混合引用3种不同引用方法在公式复制时的变化规则。4．高级筛选的条件设置中是否可以使用通配符？如果可以使用，请自己设计一个例子说明，并显示结果。

答：1.中该单元格——格式——单元格——对齐——选择“自动换行”；

2.在原地点击复制，到目的地后“编辑”——选择性粘贴——选择要粘贴的项目;

直接使用“粘贴”命令会把原文本原样粘贴（包括格式、数据等），而使用“选择性粘贴”命令可以选择要粘贴的项目，取消不需要的项目；

3.“相对引用”会随着复制位置的变化而作相应的变化，“绝对引用”则在公式复制的

任何时候都不会发生变化；“混合引用”中“相对引用”不符合“绝对引用”不符

合其独立使用时的变化规则一样。

4.可以。

要筛选出“表格一”中每个班的2号，则筛选条件为表格二，筛选的结果为表格三；学生成绩

一班1号82

一班2号85

一班3号86

二班1号84

二班2号79

二班3号85

三班1号78

三班2号79 三班3号81

表格一

学生成绩

一班2号85 二班2号79 三班2号79

表格三学生*2号

表格二