第四章Excel 电子表格实验.docx
《第四章Excel 电子表格实验.docx》由会员分享，可在线阅读，更多相关《第四章Excel 电子表格实验.docx（15页珍藏版）》请在冰豆网上搜索。
第四章Excel电子表格实验
第四章Excel2003电子表格实验
实验一工作表的建立、编辑和格式化
一、实验目的
1．掌握Excel2003的启动与退出。
2．熟练掌握文本、数值和日期型数据的输入，包括序列填充等自动输入的方法。
3．熟练掌握公式和常用函数的使用。
4．掌握对工作簿、工作表的基本操作。
5．掌握工作表中数据的编辑方法。
6．熟练掌握工作表的插入、复制、移动、删除和重命名。
二、实验内容
1.启动Excel2003
试用不同方式启动Excel2003，建立工作簿文件“数据处理表格.xls”。
2.熟悉Excel2003的窗口。
3.工作表的创建
（1）单个工作表的创建：
单击工作表的标签。
（2）连续多个工作表的创建：
单击首个工作表，Shift+单击最后一个工作表。
（3）不连续多个工作表的创建：
Ctrl+单击所需的工作表。
1.单元格的选定
（1）选定单个单元格。
用鼠标左键单击所需的单元格。
（2）选定多个连续单元格（选定区域）。
方法1：
单击首单元格→Shift＋单击末单元格。
方法2：
单击首单元格→按F8键→光标移动→按F8键结束。
（3）选定多个不连续单元格。
方法1：
Ctrl+单击单元格或：
Ctrl+鼠标拖动。
方法2：
Shift+F8键→光标移动→按Shift+F8键结束。
（4）选取整行、整列。
单击行头或列头。
（5）选取全部单元格。
方法1：
单击工作表左上方“全选”按钮。
方法2：
“编辑”／“全部选中”。
（6）清除选定的单元格。
工作表任意处单击或按光标移动键。
4.数据的输入
（1）单元格数据的输入。
方法1：
单击单元格→直接在单元格内输入数据。
方法2：
双击单元格→在编辑栏内输入数据。
在输入过程中，按Tab、Shift+Tab、←、↑、→、↓进行各单元格的移动。
（2）按区域输入数据。
选定区域→输入数据。
在Sheet1中输入如图4-1-1所示数据。
图4-1-1成绩表
5.数据处理
将Sheet1的标签修改为“成绩表”。
对成绩表进行操作：
使用SUM、Max、Min、Average函数或直接输入公式，计算每个同学的总成绩、平均成绩（保留1位小数），求出各科成绩的最高分和最低分
提示：
在F3单元格输入=SUM（C3:
E3）后回车，然后鼠标移动到F3单元格右下角的填充柄，拖动填充柄，将F3单元格的公式复制到F4。
。
。
F10。
同理，在C11单元格输入=MAX（C3:
C10）
在C11单元格输入=MIN（C3:
C10）
效果如图4-1-4所示。
图4-1-4操作示例图
6.序号的快速填充
自动填充柄的使用。
可以输入一批有规律的数据。
例：
一，二，三，……；第一季，第二季，……；等等。
使用方法：
输入前两个数→选中该两个单元格→指针指向第二个单元格右下角的自动填充柄→拖动填充柄到结束。
在如图4-1-4成绩表中“姓名”和“性别”间插入“班级”学号列，输入同学的学号。
学号列字符要在前面加入前导符“’”（注意：
输入的单引号是英文状态下的）。
输入第1个学号“’0401001”，然后用填充柄拖曳复制的方法来实现，如图4-1-8所示。
将文件另存为x1-1.xls。
图4-1-8输入数值型字符数据示例
6．单元格的操作
（1）修改单元格的数据双击目标单元格，然后在编辑栏中修改。
（2）单元格的复制、单元格内容的移动和选择性粘贴
复制。
可用拖动填充柄、命令或鼠标拖动等方法。
拖动填充柄方法：
选定区域→拖动填充柄至目标；
命令方法：
选定区域→[编辑]|[复制]→单击目标单元格→[编辑]|[粘贴]；
拖动方法：
选定区域→指针指向区域边框→按Ctrl键+鼠标拖动到目标处。
（3）移动。
可用命令或鼠标拖动等方法。
命令方法：
选定区域→[编辑]|[剪切]→单击目标单元格→[编辑]|[粘贴]。
拖动方法：
选定区域→指针指向区域边框→按鼠标拖动到目标处。
（4）移动和插入。
上面使用的移动方法是在数据移到新的地点后，会将新地点处的数据覆盖
掉。
若希望新地点的数据不被覆盖掉，可用下列方法：
选定区域→[编辑]|[剪切]→单击目标单元格→[插入]|[剪切单元格]。
（4）单元格、行、列的插入和删除
插入单元格：
选定插入的位置→[插入]／[单元格]。
插入行：
选定插入的位置→[插入]|[行]。
插入列：
选定插入的位置→[插入]|[列]。
删除：
选定删除的对象→[编辑]|[删除]，
删除单元格、行或列（包括内容和单元格）。
（5）清除：
选定清除的对象→[编辑]|[清除]，
清除内容，单元格还存在。
（6）.查找与替换
Excel的查找与替换与Word操作相同：
（1）查找：
[编辑]|[查找]→输入查找内容→查找下一个。
（2）替换的两种方法：
[编辑]|[替换]→输入查找替换内容→[替换]；
[编辑]|[替换]→输入查找替换内容→[全部替换]。
7．工作簿间的表格操作
新建工作簿文件“格式设计表格.xls”。
（1）在Sheet1工作表前插入一张新工作表Sheet4，将Sheet4工作表改名为“期末成绩表”，再将其复制到Sheet3工作表前，将复制的“期末成绩表
（2）”移动到最后一张工作表的后面。
（2）将“数据处理表格.xls”文件中“成绩表”的内容复制到当前工作簿的Sheet1工作表中，再将复制到Sheet1的内容复制到“期末成绩表”从B2单元格开始的区域。
8．格式设计
（1）调整列宽。
方法1：
选定需改变列宽的区域→指针指向文本右边的垂直线→向左或向右拖动鼠标。
方法2：
选定需改变列宽的区域→格式／列／列宽→输入列宽的字符数。
（2）调整行高。
方法1：
选定需改变行高的区域→指针指向数字下的横线→向上或向下拖动鼠标。
方法2：
选定需改变行高的区域→格式／行／行高→输入行高数。
（3）隐藏和恢复数据。
选中要隐藏的行或列→“格式”／“行”（列）→“隐藏”（或“取消隐藏”）。
（4）设置字体、数字格式与对齐方式
设置字体：
选定区域→“格式”／“单元格”／“字体”。
数字格式：
包括正数格式、负数格式、零的格式、文本格式四部分。
方法是：
选定区域→“格式”／“单元格”／“数字”。
（5）设置边框线与图案
设置边框线。
方法1：
选定区域→“格式”／“单元格”／“字体”。
方法2：
选定区域→边框工具→选边框格式。
设置图案。
图案有十八种图案供选择，颜色指图案背景颜色。
方法：
选定区域→“格式”／“单元格”／“图案”。
对“期末成绩表”工作表进行格式化。
在表格标题和表格间插入一空白行，选中B2：
K2区域，单击工具栏合并居中按钮
，或者在[格式]｜[单元格]命令“对齐”选项卡中完成合并单元格和水平、垂直居中的操作。
将表格标题设置成华文行楷、24磅、蓝色字。
将表格列宽设为最合适的列宽；列标题行高设置为24磅，其余行高设为17磅；标题行设置为黑体、12磅、水平和垂直居中、25%灰色底纹；姓名列设为粗体、加25%灰色底纹；表格中其它内容也水平和垂直居中。
表格右边增加“计算机1班期末成绩表”文字；文字设置为方正准圆简体、12磅。
在K4单元格输入文字，选中K4：
K14区域进行单元格的合并居中；在[格式]｜[单元格]命令“对齐”选项卡中选择文字方向为垂直方向，然后再进行字形、字体的设置。
给表格设置如样张所示的边框。
在I15单元格输入当前日期，设置为楷体、倾斜。
三．进一步练习
1．条件格式
选中存放“经济学”学生的成绩，执行[格式]｜[条件格式]命令，在如下图所示的对话框中输入条件和格式设置。
在这里要注意的是“=E$14”表示存放经济学最低分的单元格，不能引用具体的数据，否则所设条件格式不会随表中数据的变化而变化；另外单元格的引用也不能使用绝对引用，只能混合引用，以便对其它成绩操作时可以利用格式复制功能。
“格式”中将其设为红色。
对其它课程的操作可先将“经济学”格式复制，然后进行格式的“选择性粘贴”或利用工具栏的格式刷操作。
图4-1-9条件格式设置
7.选择性粘贴
选定区域→[编辑]|[复制]→单击目标单元格→[编辑]|[选择性粘贴]。
“选择性粘贴”对话框内容：
全部、公式、数值、格式、批注、有效数据、边框除外、跳过空单元等。
（1）表格行列转置
将“成绩表”中男同学的数据行列转置后复制到Sheet2的A11：
E18区域。
操作方法：
在成绩表中选中不连续的区域A2：
H5和A8：
H8进行复制；然后将插入点定位到目标区起始单元格Sheet2中的A11，执行[编辑]｜[选择性粘贴]命令，在选择性粘贴对话框中选择“转置”复选框，单击[确定]按钮，效果如图4-1-6所示。
图4-1-6表格行列转置操作结果图
（2）对选中的元素数值进行计算
在复制的表中给每位男同学的计算机成绩加5分。
在Sheet2中任意一个空白单元格中输入5，并进行复制；
选择“计算机”成绩行；
执行[编辑]｜[选择性粘贴]，在选择性粘贴对话框中选择“加”选项，单出“确定”按钮，完成操作。
3．自定义序列
建立一个自定义序列，序列内容为“姓名”列的8个名字。
操作步骤：
选择“工具｜选项”命令，在选项对话框中选择“自定义序列”选项卡，如图4-1-2所示的对话框中输入8个姓名，按“确定”键完成。
在以后要输入姓名的地方只需要输入第一个姓名，然后用鼠标拖曳填充柄自动输入。
4．使用Excel的自动套用格式，快速格式化电子表格。
5．工作表窗口的拆分
（1）水平拆分：
选取拆分线的行→“窗口”/“拆分窗口”。
（2）垂直拆分：
选取拆分线的列→“窗口”/“拆分窗口”。
（3）水平与垂直拆分：
选取拆分线的单元格→“窗口”/“拆分窗口”。
（4）撤消拆分：
“窗口”/“撤消拆分窗口”。
样张
四．思考与练习
1．单击I15单元格，按【Ctrl】+【；】组合键，在单元格中会显示什么？
2．Excel中文件、工作簿、工作表、单元格有何关系？
3．在Excel中鼠标在不同区域的形状和功能有何区别？
4．如何表示单元格的地址？
5．如何输入有规律的数据？
试举例说明。
6．用什么方法可以进行快速求和？
7．在某个单元中输入公式后，其相邻单元也要进行同类运算，应该怎样做？
实验二数据图表化、数据管理及页面设计
一、实验目的
1.熟练掌握创建嵌入图表和独立图表的方法。
2.掌握图表的整体编辑和对图表中各对象的编辑。
3.掌握图表的格式化方法。
4.了解Excel2003的数据处理功能。
5.掌握对数据列表的排序、筛选方法。
6.了解数据的分类汇总和数据透视表的操作。
7.掌握打印页面设置方法。
二、实验步骤
1、创建表
新建工作簿“图表格式设计.xls”，将“数据处理表格.xls”中成绩表中内容复制到“图表格式设计.xls”的Sheet1中，将多余的数据删除，只留下如下图4-2-1所示数据。
利用Sheet1中数据，创建如图4-2-2所示的图表。
图4-2-1图4-2-2
选中A1：
E9区域，单击工具栏图表向导按钮
，在对话框中根据向导进行操作，图表样式选择“三维簇状柱形图”，图表位置选择“作为其中的对象插入”。
2．图表编辑
（1）修改图表类型：
选中图表，单击图表向导按钮
，图表类型选择“条型图”，子图表类型选择“三维簇状条型图”。
（2）删除图表中的总成绩、经济学系列，将计算机与外语系列次序对调。
操作方法：
选中系列，按Del键可删除系列；双击某一系列，在对话框中选择系列次序，然后上移或下移。
（3）移动、放大图表到A12：
H29区域。
增加图表标题“学生期末成绩表”、分类轴标题“姓名”、数值轴标题“成绩”。
操作方法：
选中图表，使用[图表]｜[图表选项]命令。
（4）显示“英语”系列的值。
操作方法：
选中“英语”系列，再双击“英语”系列，在数据系列格式对话框中的“数据标志”选项卡中选择“显示值”。
3．图表格式化
（1）图表区边框选用最粗的圆角、阴影边框，边框颜色设为红色，背景选择纹理“花束”，图表区的字体大小设为10磅。
（2）图表标题设置为隶书。
（3）将数值轴的主要该度间距改为10，分类轴的文字45o。
4．改变图表类型
将图例设置成如样张4-2-1所示，加“最高分”标注。
将Sheet1中的图表复制到Sheet2中B3单元格开始位置；将图改为如图4-2-4所示的图形。
此图表是自定义类型中的线-柱图。
图表区背景设置：
在过渡选项卡中，“颜色”选择双色、“颜色1”选为青绿、“颜色2”选为白色、底纹式样选为“中心幅射”，最后选择“变形”右边的式样。
取消“绘图区”背景和边框。
样张4-2-1
5．筛选数据
新建工作簿“数据管理.xls”，将“格式设计表格.xls”中Sheet1工作表的A2：
H10复制到当前工作簿的Sheet1工作表中，删除班级学号列。
（1）排序
按总成绩降序排列，若总成绩相同按英语成绩升序排列。
执行[数据]｜[排序]命令，在排序对话框中“主关键字”选择总成绩，递减；“次要关键字”选择英语，递增。
（2）筛选数据
将Sheet1的内容复制到Sheet2工作表，并将Sheet2工作表标签改为“筛选数据”。
筛选出“英语”成绩超过85分的记录。
筛选出“英语”成绩在80～90之间的男同学。
恢复为全部显示；恢复为普通显示。
6．分类汇总
将Sheet1工作表内容复制到Sheet3，将工作表标签改为“分类汇总”。
按性别分类，对“平均成绩”求平均值，对“英语”列求最大值和最小值。
对表格作简单的格式化，结果如图4-2-6所示。
图4-2-6
首先对性别排序，执行[数据]｜[分类汇总]命令，在对话框中分类字段选择“性别”；汇总方式选择“平均值”；汇总项选择“平均成绩”。
在前面的基础上再做两次分类汇总，与前面不同的是“替换当前分类汇总”复选框不选。
图4-2-6所示结果是通过分级显示按钮隐藏了明细数据。
删除分类汇总，恢复原来显示。
7．数据透视表
在Sheet1工作表中“姓名”列前增加一列，列标题为“系别”，为该列输入数据，如“工商”、“会计”、“计算机”等系列，然后进行如下操作：
（1）建立一个嵌入式二维数据透视表，计算各系男女生的总平均成绩。
（2）以“系别”为页字段建立三维数据透视表，使透视表的每一页为一个系的男女生各科成绩的平均值。
8．打印格式设置
对“格式设计表格.xls”中的成绩表进行页面设置：
（1）纸张设为A4，表格水平、垂直居中；
（2）设置页眉为“期末成绩表”、右对齐、仿宋体、10磅；
（3）设置页脚为当前日期，居中，打印工作表的行号和列号。
（4）通过打印预览观察效果。
三、进一步练习
1、股市表与总成交额图表的制作
股市表数据如表4-2-1所示，其中：
涨跌=（收盘价-开盘价）/开盘价，总成交额=总成交量*收盘价，百分比=总成交额/总成交额的总和，总和=总成交额的总和，平均成交额=总成交额的总和/6。
表4-2-1股市表
代码
名称
开盘价
收盘价
总成交量
涨跌
总成交额
百分比
800060
锦绣大地
￥11.98
￥12.58
102.90
800076
创兴科技
￥16.12
￥15.80
114.95
800100
山东铝业
￥21.40
￥22.50
165.14
800624
海南航空
￥7.83
￥7.50
108.01
800637
广电股份
￥8.56
￥8.20
58.62
800640
北京城建
￥14.60
￥14.85
44.72
总和
平均成交量
要求如下：
⑴参照上表建立工作表文档文件；
⑵根据要求利用公式计算工作表中的各项内容；
⑶建立股市“总成交额”图表，图表样式自定；
⑷以股市表.xls为文件名保存。
2、学生成绩图表的制作
（1）启动excel，在空白工作表中输入以下数据，并以“学生成绩.XLS”为文件名保存。
中文2班3组成绩表
制表日期:
2007-07-15
姓名
高等数学
大学英语
计算机基础
总分
总评
王大伟
78
80
90
李 博
89
86
80
程小霞
79
75
86
马宏军
90
92
88
李 梅
96
95
97
丁一平
69
74
79
张珊珊
60
68
75
柳亚萍
72
79
80
最高分
平均分
（2）计算每个学生的总分，并求出各科目的最高分和平均分,再利用IF函数总评出优秀学生（总分>=270分），最后求出优秀率（优秀率＝优秀人数／总人数）。
（3）将表格的标题改为“中文２班３组部分科目成绩表”。
（4）先将每个学生的各科成绩及总分（A3:
F11）转置复制到A17起始的区域，形成第二个表格。
（5）在第二个表格中只保留总评为“优秀”的学生数据。
（6）在第一个表格的“姓名”后面插入１列“性别”。
（7）将Sheet1工作表改名为“成绩表”，再将其复制到Sheet2工作表前，然后将复制的“成绩表
（2）”移动到最后一张工作表的后面。
（8）将“成绩表”工作表中第二个表格移动到Sheet2工作表中，然后按样张对“成绩表”工作表中的表格进行如下格式化：
✓使表格标题与表格空一行，然后将表格标题设置成蓝色、粗楷体、16磅大小、加下划双线，并采用合并及居中对齐方式。
✓将制表日期移到表格的右边，并设置成隶书、斜体。
✓将表格中各栏标题设置成粗体、居中；再将表格中的其它内容居中，平均分保留小数１位。
✓使“优秀率”这行与上面的数据空一行，然后将“优秀率”设置为45度方向，并移动到其数值的前面，其值用百分比式样表示并垂直居中。
✓设置表格，不包括“优秀率”这行与上面的空行的边框线：
外框为最粗的单线，内框为最细的单线，“最高分”这行的上框线与各栏标题的下框线为双线。
✓设置单元格填充色：
对各栏标题、最高分、平均分及优秀率设置25％的灰色。
✓对学生的总分设置条件格式；总分>=270，用宝石蓝图案；240=<总分<270，采用蓝色、加粗斜体。
✓将高等数学、大学英语及计算机基础各列宽度设置为“最适合的列宽”。
✓将表格栏标题的行高设置为25磅，并将该栏的文字垂直居中。
（9）对复制到Sheet2工作表中的表格自动套用“三维效果２”格式，然后将该表格的填充色改为淡紫色，字体改为深蓝色。
四．思考与练习
1．长嵌入式图表，除了选择[插入]|[图表]命令外，还有什么方法？
2．如何添加和删除图表中数据系列？
3．分类汇总的功能是什么？
4．怎样区分分类汇总字段和汇总项？
5．数据透视表的作用是什么？
6．怎样设置打印区域和打印标题？
升级会员 