Excel 基本操作实验.docx
《Excel 基本操作实验.docx》由会员分享，可在线阅读，更多相关《Excel 基本操作实验.docx（19页珍藏版）》请在冰点文库上搜索。
Excel基本操作实验
Excel2003基本操作实验
实验一Excel工作表的创建和编辑
一、实验目的
1.掌握Excel工作簿建立、保存与打开
2.掌握工作表中数据的输入
3.掌握公式和函数的使用
4.掌握数据的编辑修改
5.掌握工作表的插入、复制、移动、删除和重命名
6.掌握工作表数据的自定义格式化和自动格式化
二、实验内容
任务1新建一个“学生成绩.xls”工作簿文件，在它的工作表Sheet1中建立内容如下的“计科1班成绩表”。
（1）新建工作簿Book1。
【提示】启动Excel2003，系统自动新建一个工作簿文件Book1.xls。
（2）将工作簿文件“Book1.xls”更名为“学生成绩.xls”，保存在“E:
\计算机作业”文件夹中。
学生成绩表
计科1班
制表日期：
2008.7.20
学号
姓名
数学
外语
计算机
061401001
王密
88
82
78
061401002
姚静
89
90
94
061401003
陈彬
85
79
72
061401004
李萍
89
71
62
061401005
李广辉
81
70
80
061401006
万彗
78
78
90
061401007
刘道明
80
60
75
061401008
鄢雨青
84
76
90
【提示】选择“文件|保存”或“文件|另存为”命令，打开“另存为”对话框（注：
新建文件首次保存时，也将打开“另存为”对话框），选择好“保存位置”后，在下方的“文件名”编辑框中输入:
学生成绩，在“保存类型”编辑框中选定：
MicrosoftOfficeExcel工作簿（*.xls），并单击“保存”按钮。
（3）在工作簿的第一张工作表Sheet1中，从A1单元格开始照样输入数据。
【提示】输入批量数据时，建议不要采取单击编辑栏后再输入，因为这样效率低，即费时又费力。
可先在A1单元格中输入表标题“学生成绩表”后，压【Enter】键使插入光标移至A2，输入“计科1班”，在A3输入“制表日期：
2006.4.20”，在A4:
E4区域中依次输入表头文字“学号”、“姓名”等，在A5中输入“’*********”（纯数字文本，其余学号的输入可利用填充柄）...输入后请认真观察数据有无变化，不同数据类型的对齐方式是什么。
任务2工作表的格式化
（1）合并单元格并居中表标题。
【提示】选定区域A1:
E1，单击工具栏上的“合并并居中”按钮
；同理操作A2:
E2。
（2）改变数据的字体、字号和颜色。
【提示】选定欲改变字体的表标题单元格，单击“格式|单元格”命令打开“单元格格式”对话框；选择“字体”选项卡；设置“字体”为“华文行楷”，“字形”为“加粗”，“字号”为“18”，“颜色”为“蓝色”。
（3）数据的对齐方式。
合并A3:
E3并设置为水平左对齐；对A4:
F4区域或第三行设置为水平居中；对姓名值各单元格（即B5:
B12）设置为水平分散对齐；各科成绩设置为居中。
【提示】通过“单元格格式”对话框，在其“对齐”选项卡页面选择设置即可。
（4）为附标题“计科1班”所在单元格A2加底纹。
【提示】激活A2单元格，打开“单元格格式”对话框，在其“图案”选项卡页面选择单元格底纹的“颜色”为“浅黄”即可。
（5）为工作表加边框。
经过以上格式化操作后工作表Sheet1结果如下。
学生成绩表
计科1班
制表日期：
2006.10.2
学号
姓名
数学
外语
计算机
061401001
王密
88
82
78
061401002
姚静
89
90
94
061401003
陈彬
85
79
72
061401004
李萍
89
71
62
061401005
李广辉
81
70
80
061401006
万彗
78
78
90
061401007
刘道明
80
60
75
061401008
鄢雨青
84
76
90
任务3工作表的复制与重命名
（1）将工作表Sheet1的数据复制到Sheet2中。
”
【提示】激活Sheet1，选择其数据区域（即A1:
E12）,压【Ctrl+C】键，或单击工具栏上的“复制”按钮，使其被跑动的虚线框住；再激活Sheet2，使用“粘贴”命令即可。
（2）将工作表Sheet1和Sheet2，分别更名为“1班成绩表”和“1班成绩分析表”。
【提示】右击Excel主窗口下方的工作表表名（如Sheet1），从弹出的快捷菜单中选择“重命名”命令，此时表名呈反显，输入新名字即可。
任务4工作表数据的编辑与修改
（1）在工作表中增添新字段。
即对工作表“1班成绩分析表”添加“总分”、“平均分”、“总评”三个字段。
（2）为工作表添加新的行。
即在区域B13:
B15各单元格中依次添加各科成绩“最高分”、“平均分”和“方差”。
效果如下：
学生成绩表
计科1班
制表日期：
2006.10.2
学号
姓名
数学
外语
计算机
总分
平均分
总评
061401001
王密
88
82
78
061401002
姚静
89
90
94
061401003
陈彬
85
79
72
061401004
李萍
89
71
62
061401005
李广辉
81
70
80
061401006
万彗
78
78
90
061401007
刘道明
80
60
75
061401008
鄢雨青
84
76
90
统计分析
最高分
平均分
方差
任务5利用公式和函数进行计算
（1）先计算每个学生的总分和平均分，再求出各科目的最高分、平均分和方差。
【提示】总分、平均分的计算可利用Sum、Average函数，也可直接输入计算公式；最高分、平均分和方差计算使用Max、Average、Stdev函数。
（2）评出优秀学生，总分高于总分平均分的10%者为优秀，在总评栏上填写“优秀”。
【提示】总评使用IF函数，本题对第一个学生的总评公式存放在H4单元格中，其余学生的总评通过填充柄方式实现。
特别提醒：
在使用IF函数计算总分高于总分平均分的10%中，引用存放总分平均分单元格的地址必须是绝对地址引用，否则要使用填充柄对其他学生评价时将显示全部是优秀，为什么?
请思考并分析其原因；在Valueiffalse列表框应按空格键，表示不满足优秀条件的学生评价栏以空白显示，否则将显示False。
任务6工作表的复制、插入、删除与修改
（1）将“1班成绩分析表”中每个学生的姓名、各科成绩及总分（B4:
F12）转置复制到Sheet3中。
【提示】转置是指将表格转90,即行变列、列变行。
实现的方法是选中要复制的表格区域进行复制，然后插入点定位到目标区起始单元格，执行“编辑|选择性粘贴”命令，在其对话框中选中“转置”复选框。
（2）在Sheet3工作表前插入一张工作表Sheet4，将“1班成绩表”复制到Sheet4中A1起始的区域；Sheet3复制到Sheet4中A16起始的区域。
（3）删除Sheet3工作表。
并将Sheet4工作表移动到最前面，成为第一张工作表。
【提示】指向Sheet3表名，击右键，从弹出的快捷菜单中选择“删除”命令；然后指向Sheet4表名，压住鼠标左键拖动到第一张表位置即可。
（4）将工作簿“学生成绩.xls”另存为“学生成绩管理.xls”。
任务7工作表中部分数据的突出显示
（1）利用条件格式，将学生的每门课中最高分以粗体、蓝色字、灰色底纹突出显示。
【提示】选中存放“数学”成绩的8位学生的成绩，利用“格式|条件格式”命令，在如图所示的“条件格式”对话框中，输入条件（C$13表示存放数学最高分的单元格），再单击“格式”按钮对满足条件的单元格进行相关的格式设置；对其他各课程的最高分格式设置，可复制“数学”最高分的格式设置，并通过“选择性粘贴”仅粘贴格式即可。
（2）利用“选择性粘贴”实现转置工作表，并对转置后的工作表中的部分内容自动套用“彩色1”格式，然后将该表格的内框线改为黄色细线。
任务8对“学生成绩管理.xls”工作簿中的“1班成绩分析表”进行如下页面设置，并打印预览。
（1）纸张大小为A4，表格打印设置为水平、垂直居中，上、下页边距为3厘米。
（2）设置页眉为“成绩统计分析汇总表”，格式为楷体字、居中、粗斜体；设置页脚为当前日期，靠右排放。
（3）不打印网格线，但打印工作表的行号和列号。
【提示】表格打印的水平、垂直居中、上、下页边距等格式是通过“文件|页面设置”命令的“页边距”选项卡的有关设置实现的；打印工作表的行号和列号是通过“工作表”选项卡对应的设置来实现的。
（4）保存工作簿文件，并关闭Excel2003窗口。
实验二Excel图表和数据管理
一、实验目的
1.掌握嵌入图表和独立图表的创建
2.掌握图表的整体编辑和对图表中各对象的编辑
3.掌握图表的格式化
4.掌握数据清单的排序、筛选和分类汇总等的应用方法
二、实验内容
任务1嵌入图表的创建与编辑
（1）打开“E:
\计算机作业\学生成绩管理.xls”文件，激活“1班成绩表”，为它创建嵌入的条形图图表，图表标题为“学生成绩表”。
【提示】对数据进行图表化，首先要识图，即对要求已知的图表明确要选中工作表中的哪些数据（本题可选中“1班成绩表”中的B4:
E12区域），图表的类型是什么等。
（2）对上面创建的“学生成绩表”图表进行移动、放大到A15:
G30区域。
（3）将图表中数学的数据系列删除，然后再将计算机与外语的数据系列次序对调。
（4）为图表中“计算机”的数据系列增加以值显示的数据标记。
（5）为图表添加分类轴标题“姓名”。
【提示】对图表进行编辑，首先要搞清楚图表中的各个对象，如“图表标题”、“绘图区”、“数据系列”、“分类轴”、“图例”等；选中所需的对象通过快捷菜单进行相应的操作。
任务2嵌入图表的格式化
（1）对“学生成绩表”图表标题“学生成绩表”设置为黑体、16磅
（2）将图表区的字体大小设置为8磅，并选用最粗的圆角边框
（3）将图例边框改为带阴影边框，并将图例移到图表区的左下角
（4）将数值轴的主要刻度间距改为20
（5）去掉背景区域的图案
（6）调整绘图区的大小
【提示】通过图表中各对象的有关格式快捷菜单命令操作设置即可。
任务3嵌入图表的复制与修改
（1）将建立的嵌入图“学生成绩表”图表复制到A33单元格开始的区域。
（2）将其改为折线图，并对图形区背景、图例等自行进行调整，要求使其美观。
任务4独立图表创建、编辑与格式化
（1）对“计算机”课程的部分学生成绩，创建独立的三维饼图图表“图表1”。
（2）对“图表1”进行格式化，调整图形的大小及进行必要的编辑。
（3）将“学生成绩管理.xls”文件另存为“学生成绩分析.xls”，退出Excel2003。
任务5Excel2003的数据管理
（1）打开“学生成绩分析.xls”文件，激活“1班成绩分析表”，将它复制到刚插入的新表Sheet1中，删除表格上方的表标题、制表日期、右边的“总评”表列和表格最后三行，即仅保留表格区域A4:
G12；并在“姓名”表列后增加“性别”表列；然后将该表更名的“成绩统计表”。
（2）将“成绩统计表”复制二份到新插入的工作表Sheet2中，然后对其中的一份按照一个关键字（如总分）排序，再对另一份按照二个关键字（如总分和学号）排序。
将该表更名为“排序表”，并在两组排序数据前面分别写上排序条件。
【提示】利用“数据|排序”命令，易实现单条件排序和复合条件排序。
（3）将“成绩统计表”复制二份到新插入的工作表Sheet3中，然后对其中的一份按照某条件（如:
数学>=85）进行自动筛选；再对另一份按照至少两个条件构成的复合条件（如：
数学>80的男生）进行高级筛选。
将该表更名为“筛选表”，并在该表的两组筛选数据前面分别写上筛选条件。
【提示】利用“数据|筛选|自动筛选”，易实现单条件筛选；对于高级筛选，可先在高级筛选数据表后建立一个条件区，如在A26:
C28区域中输入如下数据：
条件区:
性别
数学
男
>80
筛选结果:
并在“高级筛选”对话框中，按右图所示选择操作即可。
（4）将“成绩统计表”复制到新插入的工作表Sheet4中，按照某个列（如：
性别）进行分类汇总。
要求在分类汇总表前写清分类汇总的列名（即按照什么分类汇总），并将该表更名为“分类汇总表”。
【提示】在进行分类汇总前，首先应对分类的字段进行排序，然后进行分类汇总，否则分类汇总无意义；其次要搞清楚分类汇总的三要素：
分类字段、汇总方式和汇总字段。
本题分类字段为性别，对三门课程的成绩进行汇总，汇总的方式是求平均值。
任务6嵌套分类汇总及其分级显示
（1）激活上分类汇总表，在其基础上，再汇总出男生和女生的人数。
【提示】在原有分类汇总的基础上再汇总，即嵌套分类汇总。
这时只要在原汇总的基础上，再进行汇总，然后将“替换当前分类汇总”复选框的选中标记取消。
（2）分类汇总的分级显示及汇总数据的编辑。
【提示】“男生平均分”、“女生平均分”、“总计平均分”文字原为“男平均值”、“女平均值”、“总计平均值”等，要进行修改和位置移动。
女生的详细内容不显示。
“分类汇总表”的效果图如下：
（3）存盘退出Excel2003。
Excel综合与提高实验
实验一奖学金等级确定
一、实验目的
通过比较复杂的公式来计算每门功课的学业绩点和平均绩点，供确定奖学金等级使用。
二、实验内容
任务1学业绩点的计算
（1）定义绩点计算公式，计算规定如下：
成绩60分时绩点为1，成绩每增加1分绩点增加0.1；成绩小于60分时绩点为0。
计算公式描述为：
若成绩大于或等于60，则绩点等于（成绩-60）/10+1；若成绩小于60，则绩点等于0
（2）“绩点1”、“绩点2”和“绩点3”，代表三门功课的绩点，计算公式的填充。
将“E2”单元格中的公式横向填充至整个“E2:
G2”区域，进而再将公式纵向填充至整个“E2:
G7”区域，如图1所示，这样，所有绩点都自动计算完毕。
图1全部绩点的自动计算
（3）计算平均绩点。
键入“平均绩点”列标题，然后在“H2”单元格中定义求和公式：
“=AVERAGE（E2:
G2）”，再将公式向下填充，即可求得所有考生的平均绩点，如图2所示。
图2平均绩点的计算
（4）保存文件，名字为“成绩表2.xls”，
任务2奖学金等级的计算
（1）平均绩点在4.0或以上者能获得一等奖学金，3.5或以上者能获得二等奖学金，3.0或以上者能获得三等奖学金,实现多路分支判断.
（2）插入函数公式求出各奖学金等级，如图3所示。
图3最终判定结果
实验二图书销售统计
一、实验目的：
（1）通过一个图书销售统计表，掌握工作表中数据类型及输入。
（2）修改销售表并计算统计数据，掌握单元格、行和列的插入、删除及合并的方法，公式与函数的使用，掌握单元格引用及公式复制的方法。
（3）制作形式美观的表格，掌握工作表中数据格式、字符格式及对齐方式的设置方法，边框和底纹的设置方法，掌握条件格式的设置方法和格式的复制及删除。
（4）用图表显示销售统计数据，掌握创建图表的的方法，图表格式的设置方法。
（5）销售数据的分析与管理，掌握创建数据清单的方法，数据的排序及数据的筛选，数据的分类汇总。
二、实验内容
任务1一个图书销售统计表
用Excel统计某书店部分图书的季度销售量，完成结果如图4所示，保存文件名字为“图书销售统计表.xls”。
图4图书销售统计表
任务2修改销售表并计算统计数据
以实验内容1的销售统计表为基础，添加“单价”一项，修改工作表中的部分数据，并分别计算各种书的季销售金额、季平均销售量、年销售金额和总的销售金额，
把工作簿设计成2000~2004年的销售统计总表，即工作表的名称改为“2000年”、“2001年”、“2002年”，再插入“2003年”、“2004年”两张工作表，并用工作组的方式在其余工作表中填充相同内容。
如图5所示。
图5图书销售统计表
任务3制作形式美观的表格
对实验内容2中的工作表中的“单价”项、“年销售额”及“季销售额”项设置数据格式；设置表格的字符格式及对齐方式；指定销售量小于10和大于100时分别以绿色和橙色自动突出显示；为工作表添加边框、底纹和标题，并打印工作表。
格式化后的工作表如图6所示。
图6图书销售统计表
任务4用图表显示销售统计数据
用数据图表形象地把实验内容3中所示的销售统计结果表现出来，图表的类型为柱型图，图表的分类轴为书目，竖直轴为销量；图表要包涵标题、图例等项目，完成后的图表如图7所示。
图7图书销售统计表
任务5销售数据的分析与管理
（1）根据实验内容2创建书店销售统计表的数据清单；
（2）使用高级筛选。
筛选出第4季度销量小于20或第3季度大于150的书目，如图8所示。
使用“高级筛选”对话框，如图9所示。
高级筛选后的工作表如图10所示。
然后取消筛选；
图8建立条件区域的工作表
图9“高级筛选”对话框
图10高级筛选后的工作表
（3）根据书的类别建立分类汇总表，分级显示汇总数据，取消分类汇总。
实验三、多表格联系处理
任务1学生成绩统计表
新建一个EXCEL文件，将sheet1,sheet2和sheet3分别命名为1班部分学生成绩表，2班部分学生成绩表和3班部分学生成绩表。
分别输入如下数据：
（1）在3班部分学生成绩表之后新建一个表，命名为全部成绩统计表。
（2）在该统计表中统计3个班的的学生中，各门功课的总分。
说明；在“全部成绩统计表”的B2单元格中计算出全部学生的数学总分，首先要计算出全部学生的数学总分。
这需要用到SUM（）函数。
而参数则涉及到前面的3张表格。
从前面的图中可以看出，每张表格的数学成绩均分布在B3：
B5的单元格中。
在这个我们需要加入表格的名称，来区分这三个不同的区域。
一班部分学生成绩表中的B3：
B5则需要表示成“’1班部分学生成绩表’！
B3：
B5”
二班部分学生成绩表中的B3：
B5则需要表示成“’2班部分学生成绩表’！
B3：
B5”
三班部分学生成绩表中的B3：
B5则需要表示成“’3班部分学生成绩表’！
B3：
B5”
从上面的表示我们可以得出结论，“’”+表格名称+“’”+“’”+单元格名称即可。
请注意，双引号中的符号“’”和“!
”是在英文半角下输入的。
升级会员 