第4章 电子表格Excel 2010应用（课件）-《计算机应用基础》同步教学（电子工业出版社）

第4章 电子表格Excel 2010应用 项目任务 知识目标 学习目标 掌握Excel2010的基本操作 掌握工作表的格式设置 学会用公式和函数进行数据的计算和统计 学会对工作表中的数据进行排序、筛选、分类汇总等操作 文档的基础编辑操作 字体与段落的设置 表格制作与编辑 图片、图形与文字内容的综合排版 任务分析 任务完成 任务描述 Part 2 Part 3 Part 1 4.1 电子表格Excel 2010基础操作 4.1.1 Excel2010的用户界面 4.1.2 工作簿的新建、打开、保存、平铺 （1）启动Excel 2010，新建一个工作簿，命名为“学生成绩表”。 （2）在一个工作簿第一个工作表中（默认第一个工作表为 Sheet1为当前工作表）并按下图录入数据。 工作簿的平铺显示 依次打开多个工作簿，在“视图”功能区的“窗口”组中单击“全部重排”按钮 在弹出的“重排窗口”对话框中选择排列方式（平铺、水平并排、垂直并排和层叠），单击“确定”按钮 4.1.3 工作表数据的输入和选择 工作表中数据的输入 选定要输入的单元格，使其变为活动单元格，即可在单元格中输入数据。 文本输入、数字的输入 工作表中数据的选择 选择单个单元格、一个单元格区域、不相邻的两个或两个以上的单元格区域、一行或一列、全部单元格等 4.1.4 工作表内容的编辑 工作表中内容的编辑 在编辑栏中编辑单元格的内容。选择活动单元格，该单元格的内容出现在编辑栏中。如果单元格内容是公式，则编辑栏显示相应的公式 用“选择性粘贴”复制单元格数据 可对数据进行复制和粘贴，需要选中内容，再进行复制粘贴 工作表内容的拖动、查找、替换 工作表内容的拖动 用拖动鼠标的方法复制和移动单元格的内容 查找与替换 单击“编辑”功能区中的“查找”按钮A可对工作表进行查找、替换、定位等操作 案例1 学生成绩表的制作 建立某班级的学生成绩表 (1)启动 Excel 2010,新建一个工作簿，命名为“学生成绩表”。 (2)在工作簿的第一个工作表（默认第一个工作表Sheetl为当前工作表）中按图4-1所示录入数据。 (3)输入表格的列标题：按照表格所需要的内容逐个输入数据 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 任务分析 任务完成 任务描述 Part 2 Part 3 Part 1 4.2 工作表的格式化 4.2.1 工作表数据的格式化 1. 设置工作表数据的格式 （1）设置工作表的列宽。 （2）设置工作表行高。 （3）单元格内容的对齐。 2. 修饰工作表的文字 （1）设置单元格的文字格式 （2）设置单元格的颜色和图案 3.设置单元格数字的格式 4.设置单元格的背景和边框 4.2.2 条件格式的设置 1.条件格式的设置与删除 2.更改条件格式 3.使用格式刷 案例2 学生成绩表的格式化 （1）学生成绩表刚创建时，输入的数据是默认格式。现要求改变工作表的格式，使其达到图4-6所示的效果。 （2）修饰工作表的文字，要求标题文字为“华文细黑”字体、字号16点、蓝色；第二行数据项目名称文字为“隶书”字体、字号14点、黑色；其余单元格中的文字为“楷体＿GB2312”字体、字号12点、红色。 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 任务分析 任务完成 任务描述 Part 2 Part 3 Part 1 4.3 公式与函数的应用 4.3.1 公式的应用 公式中的运算符 Excel中的运算符一般有算术运算符、比较运算符、文本运算符和引用运算符 运算符的优先顺序 Excel按照从左到右的顺序进行公式运算，当公式中使用多个运算符时，Excel将根据各个运算符的优先级进行运算 4.3.1 公式的应用 1. 设置工作表数据的格式 （1）设置工作表的列宽。 （2）设置工作表行高。 （3）单元格内容的对齐。 2. 修饰工作表的文字 （1）设置单元格的文字格式 （2）设置单元格的颜色和图案 3.设置单元格数字的格式 4.设置单元格的背景和边框 案例3 工资表计算 制作一张某公司的工资表（如图4-12所示），并在G3:G12单元格区域求出各人员的应发工资并计算出实发工资。应发工资＝基本工资＋职务津贴＋补贴，实发工资＝应发工资－扣款。 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 4.3.2 函数的应用 用输入函数的方法计算:学生成绩表中的“总分”和“平均分” 函数是 Excel 内部已经定义的公式，对指定的值区域执行运算。Excel提供的函数包括数学与三角、时间与日期、财务、统计、查找和引用、数据库、文本、逻辑、信息和工程等，为数据运算和分析带来极大的方便。 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 4.3.3 Excel主要函数简介 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 数学和三角函数 Excel函数常用的有共有11类，分别是数学和三角函数、统计函数、数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、文本函数以及用户自定义函数。以下仅列出常用的几类函数并给出简单使用例子，详细的说明请参考Excel的在线说明文档。 4.3.3 Excel主要函数简介 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 统计函数 统计工作表函数用于对数据区域进行统计分析。例如，统计工作表函数可以提供由一组给定值绘制出的直线的相关信息，如直线的斜率和 y 轴截距，或构成直线的实际点数值 4.3.3 Excel主要函数简介 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 逻辑函数 使用逻辑函数可以进行真假值判断，或者进行复合检验。例如，可以使用 IF 函数确定条件为真还是假，并由此返回不同的数值。 4.3.3 Excel主要函数简介 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 日期与时间函数 通过日期与时间函数，可以在公式中分析和处理日期值和时间值 4.3.3 Excel主要函数简介 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 文本函数 通过文本函数，可以在公式中处理文字串。例如，可以改变大小写或确定文字串的长度。可以将日期插入文字串或连接在文字串上。 4.3.3 Excel主要函数简介 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 查询和引用函数 当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用 VLOOKUP 工作表函数。如果需要确定数据清单中数值的位置，可以使用 MATCH 工作表函数。 4.3.3 Excel主要函数简介 学号 姓名 英语 计算机 高数 写作 总分 平均分 20050101 陈海威 70 92 77 68 307 77 20050102 李庭康 90 90 68 69 317 79 20050103 郭锦伟 69 69 90 68 296 74 20050104 张健栋 68 68 69 69 274 69 20050105 范嘉荣 77 77 77 92 323 81 20050106 梁思珞 90 68 90 90 338 85 20050107 郑志宏 70 90 69 68 297 74 20050108 许俪贤 92 69 68 90 319 80 20050109 许丹纯 90 92 92 69 343 86 20050110 赵家伟 69 90 90 68 317 79 20050111 龙晓思 70 92 70 92 324 81 查询和引用函数 当需要在数据清单或表格中查找特定数值，或者需要查找某一单元格的引用时，可以使用查询和引用工作表函数。例如，如果需要在表格中查找与第一列中的值相匹配的数值，可以使用 VLOOKUP 工作表函数。如果需要确定数据清单中数值的位置，可以使用 MATCH 工作表函数。 任务分析 任务完成 任务描述 Part 2 Part 3 Part 1 4.4 数据的管理 4.4.1数据的排序 （1）选择成绩单数据表中的任一单元格。 （2）在“开始”选项卡的“编辑”功能组中单击“排序和筛选”按钮，在下拉列表中选择“自定义排序”选项，弹出“排序”对话框 （3）指定排序的主要关键字、排序依据和次序，如果需要增加排序条件（如次要关键字），则单击“添加条件”按钮。本例在“主要关键字”下拉列表框中选择G列（平均分），单击“确定”按钮，即可在屏幕上看到排序结果 4.4.2 数据的筛选 (1)将单元格光标移动到表头行的任意位置，在“开始”选项卡的“编辑”功能区中单击“排序和筛选”按钮，在下拉列表中选择“筛选”选项，系统自动在每列表头（字段名）上显示筛选箭头。 (2)单击表头“销售价（元／平米）”右边的筛选箭头，打开下拉列表。列表中有“升序”“降序”“按颜色排序”“数字筛选”等选项。本例选择“数字筛选”→“大于”，弹出“自定义自动筛选方式”对话框 4.4.2 数据的筛选 (3)按照步骤（2）的操作方式筛选出“总户数”少于800的数据。 (4)在“项目名称”区域内单击下拉列表框左边的箭头，弹出选择框。在其中勾选相应项目的记录，然后单击“确定”按钮，满足指定条件的记录自动筛选出来。 4.4.3 数据的分类汇总 进行分类汇总时，如果选择分类汇总区域不明确，或只是指定一个单元格，没有指定区域，系统将无法确定将哪一列作为关键字段来汇总。这时，系统提问是否用当前单元格区域的第一列作为关键字。 确认后，弹出“分类汇总”对话框，可以在其中指定分类汇总的关键字。 分类汇总后，在工作表左端自动产生分级显示控制符。其中1、2、3为分级编号，＋、－为分级分组标记。单击分级编号或分级分组标记可以选择分级显示。 1.Excel中数据有效性的设置。 对工作表进行如下设置： （1）当用户选中“间隔”列的第3~13行中的某一行时，在其右侧显示一个下拉列表框箭头，并显示“2房1厅、3房1厅、3房2厅”选择项供用户选择。 （2）当用户选中“租价”列的第3~13行中的某一行时，在其右侧显示一个输入信息“介于1000与5000之间的整数”，标题为“请输入租价”，如果输入的值不是介于1000与5000之间的整数，会有出错警告，错误信息为“不是介于1000与5000之间的整数”，标题为“请重新输入” 4.4.4 数据工具的应用 4.4.4 数据工具的应用 2 数据的合并计算 对工作表进行如下设置： （1）当用户选中“间隔”列的第3~13行中的某一行时，在其右侧显示一个下拉列表框箭头，并显示“2房1厅、3房1厅、3房2厅”选择项供用户选择。 （2）当用户选中“租价”列的第3~13行中的某一行时，在其右侧显示一个输入信息“介于1000与5000之间的整数”，标题为“请输入租价”，如果输入的值不是介于1000与5000之间的整数，会有出错警告，错误信息为“不是介于1000与5000之间的整数”，标题为“请重新输入” 任务分析 任务完成 任务描述 Part 2 Part 3 Part 1 4.5 数据的图形表示 4.5.1  图表的创建与编辑 将单元格中的数据以各种图表的形式显示，可使繁杂的数据更加生动、易懂，可以直观、清晰地显示不同数据间的差异。当工作表中的数据发生变化时，图表中对应项的数据也自动更新。此外，Excel还可以将数据创建为数据图，可以插入或描绘各种图形，使工作表中的数据、文字、图形并茂。 案例6 学生成绩表创建簇状柱形图 本案例学习Excel 中图表的建立与编辑方法。 根据右图所示的学生成绩表创建簇状柱形图图表。 案例6 学生成绩表创建簇状柱形图 本例采用图表向导来创建图表。图表向导引导用户根据工作表的数据建立图表或修改现有图表的设置。 （1）选择用来生成图表的数据区域（本例为＄B$3:$F$12）,如果图表中要包含这些数据标题，则应将标题包含在所选区域内。 （2）在“插入”选项卡的“图表”功能组中单击“柱形图”按钮，在下拉列表中选择“二维图”中的“簇状柱形图”按钮。 $$