Microsoft Word - 新正文（大学）.doc

Size: px

Start display at page:

Download "Microsoft Word - 新正文（大学）.doc"

怀布
5 years ago
Views:

1 第 4 章 Excel 2003 应用实验一 Excel 2003 基本操作一实验目的 1. 掌握 Excel 工作簿的新建保存与打开 2. 掌握工作表中数据的输入和编辑修改 3. 掌握公式和函数的使用 4. 掌握工作表的插入复制移动删除和重命名 5. 掌握工作表数据的自定义格式化和自动格式化二实验内容 1. 新建一个学生成绩管理.xls 工作簿, 在 Sheet1 中输入数据 2. 工作表的格式化 3. 工作表的复制与重命名 4. 工作表数据的编辑与修改 5. 利用公式和函数进行计算 6. 工作表的复制插入删除与修改 7. 工作表中部分数据的突出显示 8. 对工作表进行页面设置, 并打印预览三实验步骤 1. 新建一个学生成绩管理.xls 工作簿文件, 在 Sheet1 中输入数据 (1) 新建工作簿 Book1 启动 Excel 2003, 系统自动新建一个工作簿文件 Book1.xls (2) 将工作簿文件另存为学生成绩管理.xls, 保存在自己的文件夹中 (3) 在工作表 Sheet1 中, 从 A1 单元格开始, 按照图 4-1 所示的学生成绩表输入数据提示 : 学号是以数字存储的文本, 输入时前面加单引号, 如 , 其他学号可利用填充柄进行复制填充 2. 工作表的格式化 (1) 合并及居中主标题和副标题行选定区域 A1:E1, 单击工具栏上的合并及居中按钮, 同理操作 A2:E2 (2) 设置主标题学生成绩表的单元格格式为楷体加粗 18 号蓝色

大学计算机基础实践教程图 4-1 学生成绩表 (3) 为副标题电信 1 班所在的单元格加浅黄底纹选中 A2 单元格, 单击格式单元格命令, 在弹出对话框中单击图案选项卡, 选择底纹的颜色为浅黄 (4) 合并 A3:E3 并设置为水平左对齐, 对 A4:E4 区域设置为水平居中, 对姓名列 ( 即 B5:B12) 设置为左对齐, 各科成绩设置为居中 (5) 为 A1:E12

2 大学计算机基础实践教程图 4-1 学生成绩表 (3) 为副标题电信 1 班所在的单元格加浅黄底纹选中 A2 单元格, 单击格式单元格命令, 在弹出对话框中单击图案选项卡, 选择底纹的颜色为浅黄 (4) 合并 A3:E3 并设置为水平左对齐, 对 A4:E4 区域设置为水平居中, 对姓名列 ( 即 B5:B12) 设置为左对齐, 各科成绩设置为居中 (5) 为 A1:E12 区域加细实线内边框和双线外边框学生成绩表格式化结果如图 4-2 所示 3. 工作表数据的复制与工作表重命名 (1) 将工作表 Sheet1 的数据复制到 Sheet2 中操作步骤 : 1 选择 Sheet1, 选中 A1:E12 数据区域, 按 Ctrl+C 键或单击工具栏上的复制按钮 2 选择 Sheet2 工作表的 A1 单元格, 按 Ctrl+V 键进行粘贴 (2) 将工作表 Sheet1 和 Sheet2 分别更名为 1 班成绩表和 1 班成绩分析表右击 Excel 主窗口下方的工作表表名, 从弹出的快捷菜单中选择重命名选项, 此时表名呈反显, 输入新名字即可 4. 工作表数据的编辑与修改要求 : 对工作表 1 班成绩分析表添加总分平均分总评三列, 添加最高分平均分和方差三行, 如图 4-2 所示 5. 利用公式和函数进行计算 (1) 先计算每个学生的总分和平均分 ( 保留两位小数 ), 再求出最高分平均分和方差操作提示 : 总分平均分的计算可以利用 Sum Average 函数, 最高分平均分和方差计算使用 Max Average Stdev 函数 (2) 评出优秀学生, 总分高于总分平均分 10% 者为优秀, 在总评栏上填写优秀操作步骤 : 1 将鼠标定位到 H5 单元格, 单击插入函数命令, 弹出如图 4-3 所示的函数参数对话框 2 参照图 4-3 在对话框中输入相应的参数注意, 使用的标点符号必须为英文标点符号其余学生的总评通过填充柄复制产生

第 4 章 Excel 2003 应用图 4-2 1 班成绩分析表图 4-3 函数参数对话框 6.

转置是指将表格转 90 度, 即行变列列变行 ) 操作步骤 : 1 在 1 班成绩表中选中 B4:E12 数据区域并右击, 在弹出的快捷菜单中选择

3 第 4 章 Excel 2003 应用图班成绩分析表图 4-3 函数参数对话框 6. 工作表的转置要求 : 将 1 班成绩表中学生的成绩 (B4:E12) 转置复制到 Sheet3 中, 工作表重命名为转置 ( 注 : 转置是指将表格转 90 度, 即行变列列变行 ) 操作步骤 : 1 在 1 班成绩表中选中 B4:E12 数据区域并右击, 在弹出的快捷菜单中选择复制选项 2 在 Sheet3 中选中 A1 单元格, 单击编辑选择性粘贴命令, 弹出如图 4-4 所示的选择性粘贴对话框, 在其中勾选转置复选框 3 将工作表 Sheet3 重命名为转置

对话框并输入条件 ( 参考图 4-5 所示 ), 其中 $C$13 表示存放数学最高分的单元格 3 单击格式按钮, 为最高分设置粗体蓝色灰色底纹单元格格式图 4-5 条件格式对话框 8. 对学生成绩管理.

4 大学计算机基础实践教程图 4-4 选择性粘贴对话框 7. 使用条件格式对工作表中的部分数据设置突出显示要求 : 在工作表 1 班成绩分析表中利用条件格式将学生的数学最高分设置为粗体蓝色灰色底纹以突出显示操作步骤 : 1 选中存放数学成绩的单元格区域 C5:C12 2 单击格式条件格式命令, 弹出条件格式对话框并输入条件 ( 参考图 4-5 所示 ), 其中 $C$13 表示存放数学最高分的单元格 3 单击格式按钮, 为最高分设置粗体蓝色灰色底纹单元格格式图 4-5 条件格式对话框 8. 对学生成绩管理.xls 工作簿中的 1 班成绩分析表进行页面设置并打印预览 (1) 纸张大小为 A4, 表格打印设置为水平垂直居中, 上下页边距为 3cm (2) 设置页眉为成绩统计分析汇总表, 格式为楷体居中粗斜体 ; 设置页脚为当前日期, 靠右摆放 (3) 不打印网格线, 但打印工作表的行号和列号操作提示 : 选择文件页面设置命令, 弹出页面设置对话框进行设置 ; 打印工作表的行号和列号是通过工作表选项卡中对应的设置来实现的 (4) 保存学生成绩管理.xls 工作簿, 关闭文件

学生成绩分析 (1) 计算总分最高分平均分 ( 使用函数 Sum Max Average) (2) 计算总评, 其中总分大于或等于 270 分的学生为优秀 ( 使用 IF 函数 ) (3)H13 单元格输入文字优秀率, 将单元格底纹设置为灰色,

5 第 4 章 Excel 2003 应用四上机练习 1. 新建 Excel 工作簿, 在工作表 Sheet1 中输入如图 4-6 所示的学生成绩表, 将文件保存在自己的文件夹中, 文件名为 E1.xls 图 4-6 学生成绩表 2. 按照图 4-7 所示添加列数据, 插入学号列和捐款列, 注意纯数字文本的输入 ( 001) 图 4-7 添加列数据 3. 学生成绩分析 (1) 计算总分最高分平均分 ( 使用函数 Sum Max Average) (2) 计算总评, 其中总分大于或等于 270 分的学生为优秀 ( 使用 IF 函数 ) (3)H13 单元格输入文字优秀率, 将单元格底纹设置为灰色, 使用函数 CountIf CountA 计算出优秀率 ( 计算方法 : 优秀的人数 / 总人数 ), 公式为 =COUNTIF(I4:I11," 优秀 ")/COUNTA(I4:I11) (4) 将总分小于 180 的单元格字体颜色设置为红色 ( 使用条件格式 ) 3. 工作表格式化 (1) 在第 1 行前面插入 2 行, 第 1 行输入标题计算机 1 班成绩表, 设置为蓝色粗

大学计算机基础实践教程楷体 16 磅双下划线, 合并及居中 ; 第 2 行输入制表日期 :2011-3-1, 设置为隶书倾斜, 合并后右对齐 ; 将第 3 行标题行字体设置为粗体居中 (2) 表格外框为最粗单实线, 内框为最细单实线, 设置效果如图 4-8 所示图 4-8 格式化学生成绩表 (3) 将 Sheet1 重命名为学生成绩表, 操作完成后保存为 E1.

6 大学计算机基础实践教程楷体 16 磅双下划线, 合并及居中 ; 第 2 行输入制表日期 : , 设置为隶书倾斜, 合并后右对齐 ; 将第 3 行标题行字体设置为粗体居中 (2) 表格外框为最粗单实线, 内框为最细单实线, 设置效果如图 4-8 所示图 4-8 格式化学生成绩表 (3) 将 Sheet1 重命名为学生成绩表, 操作完成后保存为 E1.xls 实验二 Excel 2003 高级应用一实验目的 1. 掌握嵌入式图表和独立式图表的创建 2. 掌握图表的整体编辑和对图表中各对象的编辑 3. 掌握图表的格式化 4. 掌握数据清单的排序筛选和分类汇总等的操作方法二实验内容 1. 图表的创建与编辑 2. 图表的格式化 3. 图表的复制与修改 4.Excel 2003 的数据管理三实验步骤 1. 图表的创建与编辑 (1) 打开实验一完成的学生成绩管理.xls 文件, 首先将文件另存为学生成绩分析.xls, 然后为工作表 1 班成绩表中的 B4:E12 区域创建嵌入的簇状条形图图表, 图表标题为学

第 4 章 Excel 2003 应用生成绩表 (2) 将刚创建的学生成绩表图表进行移动放大到 A15:G30 区域 (3) 将图表中数学的数据系列删除, 然后再将计算机与外语的数据系列次序对调 (4) 为图表中计算机的数据系列增加以值显示的数据标记 (5) 为图表添加分类轴标题姓名操作提示 : 对图表进行编辑, 首先需要弄清楚图表中的各个对象, 如图表标题绘图区数据系列

7 第 4 章 Excel 2003 应用生成绩表 (2) 将刚创建的学生成绩表图表进行移动放大到 A15:G30 区域 (3) 将图表中数学的数据系列删除, 然后再将计算机与外语的数据系列次序对调 (4) 为图表中计算机的数据系列增加以值显示的数据标记 (5) 为图表添加分类轴标题姓名操作提示 : 对图表进行编辑, 首先需要弄清楚图表中的各个对象, 如图表标题绘图区数据系列分类轴图例等 ; 然后选中对象并右击, 在弹出的快捷菜单中选择所需要的菜单项, 在弹出的对话框中进行设置 2. 图表的格式化 (1) 将图表标题学生成绩表设置为黑体 16 磅 (2) 将图表区选用最粗的圆角边框, 两坐标轴字体大小均设置为 10 磅 (3) 将图例边框改为带阴影边框, 并将图例移到图表区的左下角 (4) 将数值轴的主要刻度单位改为 10 操作提示 : 双击所需的对象, 在弹出的对话框中进行相应的设置, 最终效果如图 4-9 所示图 4-9 格式化图表 3. 图表的复制与修改 (1) 将学生成绩表图表复制到 A33 单元格开始的区域 (2) 将其改为折线图, 并对图形区背景图例等自行进行调整, 要求使其美观 4.Excel 2003 的数据管理 (1) 插入新的工作表 Sheet1, 选择工作表 1 班成绩分析表, 将 A4:G12 数据区域复制到 Sheet1 中的 A1 单元格中在姓名列后增加性别列, 然后将该工作表重命名为成绩统计表, 如图 4-10 所示 (2) 插入新的工作表, 将成绩统计表复制到新插入的工作表中, 按照总分升序排序, 总分相同再按学号升序排序, 将该表更名为排序表

对话框 (4) 插入新的工作表, 将成绩统计表复制到新插入的工作表中, 使用高级筛选筛选出数学和外语都高于 80 分的男生, 工作表重命名为高级筛选表先在空白处建立条件区域, 单击数据筛选高级筛选

8 大学计算机基础实践教程图 4-10 添加性别列利用数据排序命令实现复合条件排序 (3) 插入新的工作表, 将成绩统计表复制到新插入的工作表中, 然后进行自动筛选数学大于等于 85 分的学生信息, 将该表更名为自动筛选表操作步骤 : 1 单击数据筛选自动筛选命令 2 在数学下拉列表框中选择自定义, 弹出如图 4-11 所示的自定义自动筛选方式对话框进行设置图 4-11 自定义自动筛选方式对话框 (4) 插入新的工作表, 将成绩统计表复制到新插入的工作表中, 使用高级筛选筛选出数学和外语都高于 80 分的男生, 工作表重命名为高级筛选表先在空白处建立条件区域, 单击数据筛选高级筛选命令, 弹出如图 4-12 所示的高级筛选对话框进行设置 (5) 插入新的工作表, 将成绩统计表复制到新插入的工作表中按照性别对三门课程的成绩进行分类汇总, 汇总的方式是求平均值, 并将该表更名为分类汇总表

所示图 4-13 分类汇总对话框 (6) 保存学生成绩分析.xls 文件, 退出 Excel 2003 四上机练习 1.

9 第 4 章 Excel 2003 应用图 4-12 高级筛选对话框操作步骤 : 1 鼠标定位在数据区域, 单击数据排序命令, 按性别进行排序 2 鼠标定位在数据区域, 单击数据分类汇总命令, 弹出如图 4-13 所示的分类汇总对话框进行设置后确定分类汇总结果如图 4-14 所示图 4-13 分类汇总对话框 (6) 保存学生成绩分析.xls 文件, 退出 Excel 2003 四上机练习 1. 图表制作 (1) 打开实验一上机练习中完成的 E1.xls 文件, 插入新工作表并更名为图表制作, 将学生成绩表中的 A1:I11 数据区域复制到新插入的工作表中

15, 设定计算机基础系列显示数据值, 数据标志的字体大小为 10 号, 结果如图 4-15 所示图 4-15 学生成绩表图表 2.

10 大学计算机基础实践教程图 4-14 分类汇总结果 (2) 对学生的三门课程成绩创建嵌入式簇状柱形图图表, 图表标题为学生成绩表, 添加分类轴标题姓名, 数值轴标题分数 (3) 设置图表标题字体为隶书加粗 14 号单下划线, 坐标轴字体为 10 号, 设定图表边框为黑色粗实线圆角, 背景墙颜色为灰色填充效果 (4) 将数值轴的主要刻度改为 15, 设定计算机基础系列显示数据值, 数据标志的字体大小为 10 号, 结果如图 4-15 所示图 4-15 学生成绩表图表 2. 数据处理 (1) 插入新工作表并更名为自动筛选, 将学生成绩表中的 A1:I11 数据区域复制到新插入的工作表中, 用自动筛选筛选出总分小于 240 或大于 270 的女生记录, 自动筛选结果如图 4-16 所示

分类汇总结果五 Excel 2003 综合设计综合练习一 : 图书销售统计 1. 创建图书销售统计表用 Excel 统计某书店部分图书的季度销售量, 完成结果如图 4-18 所示, 保存文件名为图书销售统计表.xls 图 4-18 图书销售统计表 2.

11 第 4 章 Excel 2003 应用图 4-16 自动筛选 (2) 插入新工作表并更名为排序, 将学生成绩表中的 A1:I11 数据区域复制到新插入的工作表中按性别升序排序, 性别相同的按总分降序排序 (3) 插入新工作表并更名为分类汇总, 将学生成绩表中的 A1:I11 数据区域复制到新插入的工作表中按性别分类汇总, 在性别列统计人数 ; 再次按性别分类汇总, 统计各科成绩及总分的平均分利用分级显示按钮分级显示汇总结果, 分类汇总结果如图 4-17 所示图 4-17 分类汇总结果五 Excel 2003 综合设计综合练习一 : 图书销售统计 1. 创建图书销售统计表用 Excel 统计某书店部分图书的季度销售量, 完成结果如图 4-18 所示, 保存文件名为图书销售统计表.xls 图 4-18 图书销售统计表 2. 修改销售表并统计数据在 Sheet1 中以图书销售统计表为基础, 插入单价 ( 元 ) 列, 计算图书季平均销售量年销售量年销售额 ( 年销售额 = 年销售量 * 单价 ); 计算每个季度图书销售总数量, 如图 4-19 所示统计图书销售

) 季平均销量年销售额 ( 元 ) 项设置数据格式 ( 采用货币格式, 保留两位小数 ); 设置表格的数据居中对齐, 加细实线边框 ; 除标题外其余各行行高设置为

12 大学计算机基础实践教程图 4-19 统计图书销售 3. 格式化表格 (1) 复制工作表 Sheet1 中的数据到工作表 Sheet2 中, 为工作表添加标题 :16 号黑体合并居中黄色底纹 (2) 对单价 ( 元 ) 季平均销量年销售额 ( 元 ) 项设置数据格式 ( 采用货币格式, 保留两位小数 ); 设置表格的数据居中对齐, 加细实线边框 ; 除标题外其余各行行高设置为 20 (3) 使用条件格式设置图书销售量小于 10 和大于 50 时分别以绿色和橙色突出显示格式化后的工作表如图 4-20 所示图 4-20 格式化图书销售统计表 4. 用图表显示销售统计数据用数据图表表示 Sheet2 表中的各个季度销量, 步骤如下 :

销售数据的分析与管理 (1) 将 Sheet1 中的 A1:K11 数据区域复制到 Sheet3 中使用分类汇总, 汇总每个类别的图书在各个季度的销量分类汇总后的工作表如图 4-22

13 第 4 章 Excel 2003 应用 (1) 图表的类型为簇状柱型图, 图表标题为 2011 图书销量统计表, 图表的分类轴为书目, 数值轴为销量, 独立式图表 (2) 图表标题为 20 号, 分类轴标题为 16 号, 绘图区图案为雨后初晴的渐变填充效果, 网格线格式为虚线完成后的图表如图 4-21 所示图 4-21 图书销售统计表图表 5. 销售数据的分析与管理 (1) 将 Sheet1 中的 A1:K11 数据区域复制到 Sheet3 中使用分类汇总, 汇总每个类别的图书在各个季度的销量分类汇总后的工作表如图 4-22 所示图 4-22 分类汇总图书销售统计表 (2) 在 Sheet1 工作表中使用高级筛选筛选出 : 第三季度销量小于 10 或者第四季度销量大于 50 的图书完成高级筛选后的工作表如图 4-23 所示

14 大学计算机基础实践教程图 4-23 高级筛选后的工作表综合练习二 : 多表格联系处理 1. 创建学生成绩统计表新建一个名为学生成绩统计表.xls 的 Excel 文件将 Sheet1 Sheet2 和 Sheet3 分别命名为一班部分学生成绩表二班部分学生成绩表和三班部分学生成绩表, 然后分别输入如图 4-24 所示表中的数据 (a) (b) 三 (c) 图 4-24 学生成绩统计表 2. 在三班部分学生成绩表之后插入一个工作表, 更名为年级成绩分析, 在该统

同样的方法依次计算出年级各科的最高分最低分统计分析年级成绩如图 4-26 所示图 4-26 统计分析年级成绩所示 3.

15 第 4 章 Excel 2003 应用计表中统计年级各门课程的平均分最高分最低分例如, 在 B2 单元格中插入 Average() 函数, 选择每个工作表中的数学成绩区域, 如图 4-25 所示图 4-25 函数参数对话框注意 : 在函数参数对话框中最好用鼠标去选择数据区域作为参数, 如果手动输入要注意使用英文半角状态下的标点符号同样的方法依次计算出年级各科的最高分最低分统计分析年级成绩如图 4-26 所示图 4-26 统计分析年级成绩所示 3. 为年级成绩分析创建嵌入式数据点折线图图表, 标题内容和图表格式如图成绩分析分数数学语文英语计算机课程平均分最高分最低分图 4-27 成绩分析图表 4. 保存文件退出 Excel

60 新编计算机应用基础实训指导 (2) 单元格显示格式设置选中 (A1:I1) 单元格区域, 在格式工具栏中选择加粗按钮, 将字体设置为加粗显示 ; 选择居中显示按钮, 将字体设置为居中显示 ; 选择底纹设置按钮, 将单元格底纹设置为黄色, 显示结果如图 3 3 所示图 3

60 新编计算机应用基础实训指导 (2) 单元格显示格式设置选中 (A1:I1) 单元格区域, 在格式工具栏中选择加粗按钮, 将字体设置为加粗显示 ; 选择居中显示按钮, 将字体设置为居中显示 ; 选择底纹设置按钮, 将单元格底纹设置为黄色, 显示结果如图 3 3 所示图 3 模块三电子表格软件 Excel 训练项目 1 教师人事档案管理表的录入与编辑训练目标掌握 Excel 2003 工作簿的建立保存与打开方法掌握工作表中各种不同类型数据的输入方法掌握单元格格式的设置方法掌握表格边框和底纹的设置方法掌握特殊符号的输入方法训练内容制作教师人事档案管理表, 最终显示效果如图 3 1 所示图 3 1 教师人事档案管理表样本实训步骤 (1) 启动 Excel