《计算机应用基础2》课件第03章.ppt

1、实习1 Excel 2003基本操作训练实习2 Excel 2003的图表应用实习3 Excel 2003公式与函数应用第3章 Excel 2003电子表格处理 实习1 Excel 2003基本操作训练1实习目的(1)学习Excel 2003电子表格的基本制作过程。(2)掌握表格制作的基本方法。(3)学会使用简单公式计算，学会工作表的基本操作，工作表的插入、删除、重命名、保存等。2实习任务任务1 完成如图3.1所示的“第一季度个人财政预算”电子表格。图3.1 “第一季度个人财政预算”电子表格 步骤1：建立文件 启动Excel 2003，进入Excel 2003工作窗口。步骤2：表格标题的输入

2、(1)单击选中A1单元格。(2)按下Ctrl+Shift组合键选项，切换到一种常用的中文输入法，比如“智能ABC输入法”。(3)输入表格标题“第一季度个人财政预算”，注意观察此时的编辑栏，如图3.2所示。(4)按下Enter键，或用鼠标单击“”确认按钮，中文“第一季度个人财政预算”即输入到A1单元格中。图3.2 输入表格标题步骤3：原始数据的输入仿照步骤2，完成余下的文字和数字的输入，如图3.3所示。(1)在A2单元格中输入“(食品开销除外)”。(2)在B3单元格中输入“每月净收入”。(3)在D3单元格中输人数值“1475”。(4)在A6至A12区域中，仿照图3.3示例完成各项开支名称的输入。

3、(5)在B6至D12区域中，仿照图3.3示例完成具体支出数据的输入。(6)E5单元格输入“季度总和”，A14单元格中输入“每月支出”，A16单元格输入“节余”。图3.3 输入原始数据 步骤4 自动“填充”有规律的数据 (1)在B5单元格中输入“一月”。(2)将鼠标指向该单元格的右下角填充柄处，鼠标形状变为“”黑色实心加号。(3)按住鼠标左键并拖动鼠标至D5单元格，释放鼠标，Excel便会自动输入“二月”和“三月”。如图3.4所示。图3.4 自动填充数据说明：数据的“规律”体现在两个方面：数据内容的规律。数据存放位置的相邻性。只有满足了这两点才能借助“+”黑色实心加号的拖动实现数据的自动填充。步

4、骤5 利用绘图工具“输入”特殊内容(1)选取“绘图”工具栏中“”箭头工具。(2)把鼠标指向C3单元格，同时按住Shift键，拖动鼠标，在C3单元格中绘制一条带箭头的指示线，如图3.5所示。图3.5 绘制箭头符号小结：我们可根据实际情况在工作表中的不同单元格中输入不同类犁的数据，比如普通文本的表格标题或表中的数值数据等，这些信息被称为常量信息，除非重新输入或人为修改这些信息，否则，它们不会发生变化。任务2 使用公式进行计算步骤1：计算房租的季度总支出单击E6单元格，输入公式“=600+600+600”，按下Enter键或单击“”确定按钮，公式结果值1800出现在E6单元格中，如图3.6所示。图3

5、.6 计算总和 步骤2：观察等式的工作特点(1)单击B6单元格，重新输入一月房租的数值“675”，B6单元格中的数据便从原来的600更改为675。(2)观察此时的E6单元格中的结果值，我们发现E6的值并未发生变化，仍是1800(如图3.7所示)。图3.7 更改B6内容 步骤3：删除单元格中的公式 (1)用鼠标选中E6单元格。(2)单击鼠标右键，在弹出的快捷菜单中选择“删除”，删除E6单元格中的公式。步骤4：比较公式的工作特点重新输入E6单元格中的公式，采用另一种形式表示“=B6+C6+D6”，该算式的结果值为1875，如图3.8所示。图3.8 重新输入E6中的公式 步骤5：观察公式自动重算特点

6、将一月的房租重新修改为600，此时E6单元格中的结果值就会通过公式的自动重算功能，给出最新变化的结果值1800，如图3.9所示。图3.9 自动计算结果 步骤6：使用函数创建公式 (1)选中E6单元格，按Delete键，可直接删除该单元格中的内容。(2)单击“常用”工具栏上的“”自动求和按钮，Excel将自动为E6单元格创建一个求和公式“=SUM(B6:D6)”，如图3.10所示，虚线框为Excel自动识别的数据范围。(3)按下Enter键或单击“”确定按钮，完成求和公式的创建。思考：这两个等式“=SUM(B6：D6)”与“=B6+C6+D6”所实现的功能是否相同?图3.10 自动求和小结：公式

7、是利用单元格的地址对存放在其中的数值数据进行计算的等式。算式“=600+600+600”和“=B6+C6+D6”的本质区别在于：前者是具体的数值参与运算，后者则是由这些数值的具体存放单元格参与运算，所以一旦修改某一单元格中的数值，公式中对应的单元格所代表的具体值也就发生变化，算式中的等号“=”自动实现重算，以保持等式运算结果的正确性。任务3 工作表的基本操作 步骤1：重命名工作表 (1)用鼠标右键单击工作表标签Sheet1，从弹出的快捷菜单中选择“重命名”选项。(2)此时，工作表默认名 Sheet1被选中，直接输入“个人收支情况表”。(3)按下Enter键，完成重新命名操作，“个人收支情况表”

8、成为当前工作表的标签名称，如图3.11所示。图3.11 重命名结果步骤2：删除工作表(1)用鼠标右键单击工作表标签Sheet1，从弹出的快捷菜单中选择“删除”选项，即可将Sheet1工作表删除。(2)用同样的方法将Sheet3工作表删除，最终仅保留“个人收支情况表”工作表。图3.12 删除空工作表后的结果实习2 Excel 2003的图表应用1实习目的(1)熟悉用图表描述电子表格中的数据的方法。(2)掌握用Excel 2003将电子表格中的数据转换成各种类型的统计图表的方法。2实习任务本实习主要介绍Excel 2003对于图表的编辑功能。任务背景：“分配表”显示了某大学10年来本科生毕业分配流

9、向，单纯通过数字很难形象地看出分配趋势的变化，通过创建“柱形图”图表能够直观地反映毕业分配的趋势。并通过10年中的头一年和末一年“饼图”的对比可以清楚地了解10年来本科毕业生在选择上最大的改变所在。任务1 创建“数据1”工作表单 步骤1：启动Excel 2003启动Excel 2003，自动创建“Book1.xls”工作簿文件。步骤2：输入表格标题(1)单击D1单元格，选中该单元格，输入“皓天大学本科毕业生分配表”。(2)单击“编辑栏”中的“”确认按钮或按下Enter键，完成表格标题的输入。步骤3：输入原始数据按照图3.13，在B2:K8区域输入相应文字和数据。图3.13 输入原始数据步骤4：

10、修改工作表单名称 双击Sheet1标签，Sheet1呈反显状态，直接输入文字“分配表”，单击其他单元格或按Enter键进行确认。步骤5：工作簿的保存选择“文件”菜单中的“另存为”选项，注意保存路径，文件名为“图表.xls”。任务2 修饰工作表单 步骤1：跨列居中工作表标题选中D1:K1区域，单击“常用”工具栏上的“”合并及居中按钮，使工作表标题在此区域中居中显示。步骤2：标题格式的设置设置标题格式为“黑体”、粗体、24磅，如图3.14所示。图3.14 标题格式设置步骤3：设置正文内容格式(1)选中正文区域B2:K8，设置为“宋体”、字体大小“12”号字、“居中对齐”方式。(2)同时选中行标题(

11、B3:B8)和列标题(C2:K2)设为“粗体”字，并将字体颜色改为深蓝色。步骤4：为工作表添加边框和底纹。选中B2:K8区域，通过“格式”工具栏上的“”边框和“”填充颜色按钮，为工作表的正文内容部分加上内外边框和淡紫色的底纹，如图3.15所示。图3.15 为表添加边框和底纹保存文件操作小结：工作表单的修饰是以使工作表单看起来醒目，容易分辨和色彩谐调为目的的。任务3 创建柱形图 步骤1：选择需要绘制“柱形图”的数据拖动鼠标选择B2:B8区域，按住Ctrl键拖动鼠标选择D2:F8区域和I2:J8区域，此时，国有企业、外资企业、合资企业、出国留学和国内深造等五项数据同时被选中，如图3.16所示。技巧

12、：不连续区域的选择要借助Ctrl键。图3.16 选择需绘制柱形图的数据步骤2：选择图表类型在“插入”菜单中选择“图表”选项，打开“图表向导-4步骤之1-图表类型”对话框，从中选择图表类型“柱形图”，同时在对话框右侧的7个子图表类型中作出选择，呈反显的是被我们选中的簇状柱形图，单击“下一步”按钮。步骤3：输入正确的数据区域(1)在图3.17所示的“图表向导-4步骤之2-图表源数据”对话框的“数据区域”选项卡中，“数据区域”栏的文本框中显示的是默认的数据区域，这是步骤1(在进行图表创建之前)所选择的区域，如果需要更改，可直接在框中输入新的数据区域的地址，或通过单击框右侧的“”选取按钮，重新进行选取

13、。(2)“行”、“列”选项是确定数据系列的组成形式，这里我们选择“列”，单击“下一步”按钮。注意：请观察选择“行”或“列”时预览区中的图表示例，注意图例与类型(X)轴上的类别名称的变化。图3.17 选取数据区域 步骤4：设置图表选项(1)在图3.18所示的“图表向导-4步骤之3-图表选项”对话框中，单击打开“标题”选项卡，在“图表标题”栏的文本框中输入“毕业生分配柱形图”，在“分类(X)轴”栏的文本框中输入“年份”，在“数值(Y)轴”栏的文本框中输入“人数”，单击“确定”按钮。(2)单击“网格线”选项卡。(3)如图3.19所示，选中“分类轴”和“数值轴”中的“主要网格线”，请在预览区中，观察主

14、要、次要网格线选中与不选中的各种效果，单击“下一步”按钮。图3.18 设置图表标题 图3.19 设置图表网格线说明：我们可以根据需要，对图表中的一些组成选项进行设置。例如，命名图表标题，以及是否显示刻度线或图例，甚至图例在图表中的放置位置等等。步骤5：设定图表的摆放位置(1)在图3.20所示的“图表向导-4步骤之4-图表位置”对话框中选择“作为其中的对象插入”选项，单击“完成”按钮，所创建的图表将出现在当前工作表中。图3.20 设置图表摆放位置(2)此时观察到嵌入的图表四周出现8个黑色的小方块，用鼠标指向某个小方块并按住鼠标左键进行拖动，可调整图表的大小；将鼠标指向图表(小方块除外)，按住鼠标

15、左键拖动，可调整图表整体的位置，该步骤将图表放置在B10:K22区域位置处，如图3.21所示。说明：“作为新工作表插入”选项使所创建图表独立出现在一个新的工作表中，“作为其中的对象插入”选项使图表和数据存放在同一张工作表单中。图3.21 调整图表位置步骤6：保存文件操作 单击“常用”工具栏上的“”按钮，保存文件。小结：柱形图用来显示一段时期内数据的变化或者描述各项之间的比较，能有效地显示随时间变化的数量关系，从左到右的顺序表示时间的变化，柱形图的高度表示每个时期内的数值。任务4 将2003年和2005年两个数据系列制成饼图 步骤1：选择2003年饼图所需的图表数据源 选中标题行B2:K2区域和

16、2003年所有数据B6:K6区域。步骤2：选择图表类型选择“插入”菜单中的“图表”命令，弹出“图表向导-4步骤之1-图表类型”对话框，从中选择图表类型“饼图”，子图表类型选择“三维饼图”，单击“下一步”按钮。步骤3：观察图表源数据此时，在步骤1所选择的数据区域已被直接映射到“图表向导-4步骤之2-图表源数据”对话框的“数据区域”编辑框中，如图3.22所示，单击“下一步”按钮。步骤4：为“饼图”设置标题和数据标志(1)在“图表向导-4步骤之3-图表选项”对话框的“标题”选项卡中，设置饼图标题为“2003年毕业生分配比例图”，如图3.23所示。(2)在“数据标志”选项卡中选择数据标记为“百分比”，

17、如图3.24所示，使饼图上能够标出每一项占总体的百分比。图3.22 “数据区域”编辑框图3.23 设置图表标题 图3.24 设置数据标志 步骤5：将“饼图”放入新的工作表(1)在“图表向导-4步骤之4-图表位置”对话框中选择“作为新工作表插入”单选按钮，并为新工作表命名“图表2”。(2)单击“完成”按钮，所创建的图表将单独出现在名为“图表2”的图表表单中。步骤6：编辑和修饰“饼图”(1)选中“饼图”标题，设置字体“隶书”、大小20磅字，并拖动标题到合适的位置。(2)选中图例(图例周围出现小方块)，通过拖动小方块调整图例的大小，也可拖动图例(小方块除外)调整整体位置。(3)选中图表区，通过“常用

18、”工具栏中的“填充颜色”按钮，选择“水蓝”色，为“饼图”加上底纹，如图3.25所示。图3.25 编辑和修饰饼图步骤7 制作2005年毕业生分配比例图 (1)制作方法同上。(2)选择数据区域D2:K2和D8:K8。(3)插入方式为“作为新工作表插入”，名称为“图表3”。(4)标题为“2005年毕业生分配比例图”，隶书、20磅。(5)为该图表加上水蓝色底纹，最终效果如图3.26所示。图3.26 2005年毕业生分配比例图步骤8：保存文件 小结：饼图是将某个数据系列视为一个整体，其中每一项数据标记用扇形图表示该数值占整个系列数值总和的比例，直观地显示出整体与局部的比例关系。它一般只显示一个数据系列，

19、在需要突出某个重要数据项时十分有用。实习3 Excel 2003 公式与函数应用1实习目的(1)了解Excel 2003利用公式进行更新数据自动重算的电子报表特点。(2)掌握利用Excel 2003自带的函数库，根据实际需要，创建不同用途的公式，打造不同用途的表格的方法与技巧。2实习任务任务背景：本实习将用到一份计算机基础成绩统计表，如图3.27所示。该表格体现了课程的考核要求，并根据各项考核指标，实现自动计算每一位同学的成绩。课程考核指标(总分100分)分别为：3次作业满分80分(包括两道必做题作业“文字处理软件Word”满分25分，网页制作满分35分和一道任选题作业满分20分)，两次平时测

20、验(凡参加者每次记5分)和讨论参与(满分10分，以0、5、10三档记分)，凡作业次数未交齐者均视为不通过。图3.27 打开数据工作表任务分析：(1)保证参与求和计算的作业成绩符合考核要求，即任选作业“Excel”、“PowerPoint”、“Flash”以及“Photoshop”只有一项记入成绩。(2)对于提交多个任选作业的同学，首先将找出其中成绩最高的一次作业，反映在“任选作业”一栏中，并参加总成绩计算。(3)防止“任选作业”一栏的成绩由人工输入。(4)为醒目标识出某同学未交齐作业，需要将“Word”、“任选作业”以及“网页”三栏的背景色在没有成绩时以红色背景显示。(5)“参与程度”一栏应根

21、据上述评分标准，提供以“0、5、10”为三档的输入列表，供教师选择输入。(6)为减少输入错误，为“Word”、“任选作业”以及“网页”的输入设置有效范围，比如，凡在“Word”一栏中，输入大于25或小于0的数值时，均视为无效作业成绩。(7)最后总评成绩根据分数，自动设置三档计分：高于85分为“优秀”，60.85分为“通过”，60分以下的为“不通过”。(8)根据“总评”数据，计算出该课程的“课程通过率”和“课程优秀率”。任务1 设置数据的有效范围 步骤1：准备工作启动Excel 2003，依据图3.27所示表格创建“计算机基础成绩总汇.xls”文件。步骤2：选取设置对象 选中C4：C13区域(“

22、Word”一栏)。步骤3：设置有效范围 (1)选择“数据”菜单“有效性”选项，在弹出的“数据有效性”对话框中选择“设置”选项卡，具体设置如图3.28所示。单击“允许”项右边的下拉箭头按钮，在打开的列表中选择“小数”。在“数据”项中选择“介于”。“最小值”为“0”。“最大值”为“25”。(2)打开“输入信息”选项卡，参照图3.29进行填写。在“标题”栏中输入“Word作业成绩”。在“输入信息”中输入“满分25”。图3.28 “设置”选项卡 图3.29 “输入信息”选项卡(3)完成设置后，单击“确定”按钮，关闭对话框。(4)观察以上操作效果发现：当选择“Word”成绩所在列相应单元格时，会出现我们

23、所输入的提示信息，以引起注意，如图3.30所示。图3.30 出现提示信息 步骤4：设置出错警告方式(1)选择“数据”菜单“有效性”选项，在弹出的“数据有效性”对话框中选择“出错警告”选项卡，具体设置见图3.31。出错提示采用“终止”样式，观察其下方的终止图标。出错“标题”项填写“无效”。“出错信息”项填写“数据错误”。(2)完成设置后，单击“确定”按钮，关闭对话框。(3)观察以上操作效果；当在“Word成绩列相应单元格中输入的数据大于25或小于0或非数值数据时，将出现我们所设置的出错警告。图3.31 “出错警告”选项卡训练：仿照上述操作完成其他区域数据有效范围的设置，其中：D4:D13区域(任

24、选作业成绩)，有效条件设为“介于020”。提示信息为“任选作业成绩满分20”，出错提示“数据错误”。I4:I13区域(网页作业成绩)，有效条件设为“介于035”。提示信息为“网页作业成绩满分35”，出错提示“数据错误”。J4:J13区域(平时讨论参与成绩)，有效条件设为“介于010”。提示信息为“平时讨论参与成绩满分10”，出错提示“数据错误”。步骤5：设置平时测试区域有效范围 (1)首先在F15，G15，H15三个单元格中分别输入0，5，10。这三个数据是平时测验成绩来源所在，也可以把它放在工作表的任何单元格中。(2)选取“平时测试”区域K4:K13。(3)选择“数据”菜单“有效性”选项，在

25、弹出的“数据有效性”对话框中选择“设置”选项卡。单击“允许”项右边的下拉箭头按钮，在打开的列表中选择“序列”，如图3.32所示。选择“序列”来源。单击“来源”编辑框右侧的“”按钮，将“数据有效性”对话框缩小，用鼠标选定“序列”所在位置F15:H15，单击“来源”编辑框右侧“”按钮，可以观察到所选范围已填入“来源”编辑框中，如图3.33所示。打开“输入信息”选项卡，在图3.34中填写提示信息。在“标题”栏中输入“平时测试成绩”。在“输入信息”栏中输入“单击右下角下拉按钮，选择具体成绩”。图3.32 “设置”选项卡 图3.33 “来源”编辑框 图3.34 设置提示信息(4)完成设置后，单击“确定”

26、按钮，关闭对话框。(5)观察以上操作效果发现：当我们选择平时测试区域(K4:K13)的单元格时，会出现所设置的提示信息，如图3.35所示，同时在单元格的右下角出现下拉按钮，通过下拉按钮我们可以在打开的序列中选择0，5，10。图3.35 出现下拉按钮与提示信息 任务2 插入函数从任选作业中选择出最高分 步骤1：创建公式(1)首先选定单元格D4，然后单击“编辑栏”中的“”插入函数按钮，打开如图3.36所示“插入函数”对话框。(2)在“或选择类别”栏的下拉列表中选择“常用函数”，在“选择函数”列表中选择 MAX，单击“确定”按钮，弹出如图3.37所示的“函数参数”编辑对话框。图3.36 “插入函数”

27、选择对话框图3.37 “函数参数”编辑对话框(3)单击“Number1”编辑栏右侧的“”按钮，将“函数参数”编辑框缩小，用鼠标选定参加计算的一组数据E4:H4之后(该区域存放任选作业的成绩)，再单击编辑框右端的“”按钮，恢复“函数参数”对话框。(4)单击“确定”按钮，关闭“函数参数”对话框，按下Enter键完成公式输入。观察上述操作的结果，如图3.38所示，单元格D4中显示公式的运算结果，编辑栏中显示具体公式。图3.38 运算结果步骤2：复制公式 (1)将鼠标指向D4单元格的右下角填充柄处，鼠标形状变为“”黑色实心加号，按住鼠标左键拖动鼠标至第13行，释放鼠标完成公式的复制。(2)选择被复制了

28、公式的单元格，观察编辑栏的变化。思考：为什么D4:D13区域中每个单元格中均出现数据0?训练：参照以上步骤，在“分数”列(L4:L13区域)中创建求和公式，计算“计算机基础课”的几部分分数总和。任务3 创建嵌套公式，计算课程总评成绩 步骤1：分析任务 根据“分数”栏中的结果值进行判断。总评成绩是根据每一位同学的成绩决定的，所以计算公式中需要利用逻辑函数IF进行条件的判断。(1)分数在60分以下的为“不通过”；(2)分数在6085分为“通过”；(3)分数高于85分为“优秀”。步骤2：创建逻辑判断公式 (1)选择单元格M4，然后单击“编辑栏”中的“”插入函数按钮，打开如图3.36所示的“插入函数”

29、对话框。创建学生“甲”总评成绩的计算公式。(2)在“或选择类别”栏的下拉列表中选择“常用函数”，在“选择函数”列表中选择IF，单击“确定”按钮。弹出如图3.39所示的“函数参数”编辑对话框。图3.39 “函数参数”编辑对话框(3)在“Logical_test”编辑框中输入“优秀”的条件：单击“Logical_test”编辑框右侧的“”按钮，将“函数参数”对话框缩小，用鼠标选定单元格L4(该单元格存放学生“甲”的分数)，接着从键盘输入运算符大于和等于号(“=”)，然后输入数字“85”，再单击编辑框右侧的按钮，恢复“函数参数”对话框。(4)用鼠标单击“Value_if_true”编辑框。注意“函数

30、参数”对话框底部对该参数的描述，该参数负责给出满足条件的结果值。我们在此输入“优秀”，注意编辑栏的变化，见图3.40。图3.40 设置“优秀”条件(5)单击“Value_if_false”编辑框。注意“函数参数”对话框底部对该参数的描述，该参数负责给出不满足条件的结果值。至于这个结果值是“及格”或“不及格”，还需要作进一步的判断，具体实现可按下面步骤3操作。步骤3：函数嵌套，实现多重判断(1)单击“Value if_false”编辑框，从键盘输入“if(L4=60,”及格“，if(L40,”不及格“,”没有成绩“)”，注意观察“公式编辑栏”中的内容，见图3.41。(2)单击“确定”按钮，M4单

31、元格中出现“没有成绩”字样，在M5:M13区域进行公式复制，完成“总评”成绩公式的设置，如图3.42所示。图3.41 判断条件输入完成图3.42 总评成绩训练：请读者完成及格率(F17单元格)和优秀率(F18单元格)的计算。我们可以采用直接在“公式编辑栏”中输入的方式，也可以通过“公式选项板”创建公式。及格率公式：“=COUNTIF($L$4:$L$13，”=60“)/COUNT($L$4:$L$13)”；优秀率公式：“=COUNTIF($L$4:$L$13，”=85“)/COUNT($L$4:$L$13)”。公式创建完毕后，将F17和F18单元格设置为百分比样式%。任务4 使用条件格式，醒目

32、显示重要数据 将“Word”、“任选作业”以及“网页”3栏的背景色在没有成绩时以红色显示。(1)选择区域C4:C13、D4:D13、I4:I13。(2)选择“格式”菜单“条件格式”选项，打开“条件格式”对话框。(3)单击“条件格式”列表框按钮(默认选择为“介于”)，选择“等于”，然后在右边的编辑框中输入0，完成条件的设定操作，如图3.43所示。图3.43 “条件格式”对话框(4)单击此对话框右边的“格式”按钮，弹出“单元格格式”对话框，选择“图案”选项卡，选择红色为背景色，如图3.44所示。单击“确定”按钮，返回“条件格式”对话框。(5)单击“确定”按钮，关闭“条件格式”对话框，返回工作表。设置完成后，工作表的Word、任选作业、网页3列为红色显示，如图3.45所示。最后，我们在设计好的表格中添入数据，其最终效果如图3.46所示。图3.44 “单元格格式”对话框图3.45 突出显示列图3.46 表格最终效果