全国计算机等级考试一级教程Excel的使用课件

1、Excel 2010 的使用一级等考，全国计算机等级考试一级教程计算机基础及MS Office应用第4章学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONTENTS数据的管理与连接4.7工作表的打印4.8上机指导Excel综合实例4.94.1.1Excel的功能0102030405能够轻松地创建工作簿和工作表，能够方便地使用单元格来输入和编辑数据，且能够按个人需要快速完成对表格数据的格式化操作。能够通过输入各种公式来计算数据，同时提供了大量函数，并支持自定义公式，以完成对数据的各种复杂计算

2、。能够轻松建立和编辑出各种类型的图表，并支持对图表数据和格式进行美化操作。能够高效地处理数据清单，如对数据进行排序、筛选和分类汇总等操作。能够轻松实现数据共享，如支持多用户共同使用一个工作簿，支持创建超链接实现数据的共享使用等。4.1.2Excel的用途Excel 可以创建数据表格，可以利用公式或函数对数据进行各种计算，可以通过表格数据建立各种图表，可以对数据进行管理分析等，在金融、财务、工程预算、市场分析、工资管理、文秘处理和办公自动化等方面被广泛使用。Excel 2010 工作窗口4.1.3Excel 2010的工作窗口4.1.3Excel 2010的工作窗口1编辑栏2编辑区4.1.4Ex

3、cel 2010的基本概念Excel 文件（扩展名为.xlsx），类似于Word 文档的概念，其中可以包含多张工作表。工作簿由若干单元格组成，每个工作表对应一个标签，可在工作簿中实现对工作表的切换。工作表保存数据的基本单位，每个单元格都有一个地址，由行号和列标组成，列标在前，行号在后单元格学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONTENTS数据的管理与连接4.7工作表的打印4.8上机指导Excel综合实例4.94.2.1工作表的基本操作1选择工作表选择单个工作表选择不连续的工作表选

4、择所有工作表选择连续的工作表通过标签滚动按钮选择工作表154324.2.1工作表的基本操作2插入与重命名工作表插入工作表重命名工作表4.2.1工作表的基本操作3移动与复制工作表通过对话框移动或复制工作表通过拖曳鼠标移动或复制工作表4.2.1工作表的基本操作4删除工作表无用的工作表可以删除，选择需删除的工作表，在【开始】【单元格】组中单击“删除”按钮下方的下拉按钮，在弹出的下拉列表中选择“删除工作表”选项。也可直接在选择的工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“删除”命令。5拆分工作表利用拆分条拆分利用“拆分”命令拆分4.2.1工作表的基本操作6冻结工作表冻结拆分窗格冻结首行冻结首列7

5、设置工作表标签颜色在工作表标签上单击鼠标右键，在弹出的快捷菜单中选择“工作表标签颜色”命令，在弹出的子菜单中选择相应的命令选项即可。4.2.2单元格的基本操作1选择单元格选择不连续的单元格选择多个连续的单元格选择整列选择整个工作表的单元格选择单个单元格选择整行4.2.2单元格的基本操作当工作表中存在大量数据时，可通过对单元格或单元格区域进行命名来快速定位到需要的数据。其方法为：选择需命名的单元格或单元格区域，在名称栏中输入需要的名称，按【Enter】键即可。2命名单元格3合并与拆分单元格在编辑表格的过程中，为了使表格结构看起来更美观、层次更清晰，有时需要对某些单元格区域进行合并或拆分操作。插入

6、行插入单元格4.2.2单元格的基本操作Excel 允许随时插入单元格、行和列等对象来增加或调整表格数据，选择多少行或列，便将插入对应数量的行或列。4插入单元格、行和列4.2.2单元格的基本操作选择某个单元格、行或列，单击【开始】【单元格】组中的“删除”按钮即可删除所选对象。5删除单元格、行和列若单击“删除”按钮下方的下拉按钮，在弹出的下拉列表中选择“删除单元格”选项，则可在打开的对话框中设置删除后其他单元格的移动方向或删除的对象。快速调整行高与列宽精确调整行高与列宽4.2.2单元格的基本操作调整单元格的行高和列宽可以改善工作表的美观性和可读性。6调整行高与列宽4.2.2单元格的基本操作为单元格

7、添加批注可以将需要注释的信息存放到表格中，又不影响表格的使用。添加批注的单元格右上角会出现一个三角形标志 ，将鼠标指针移到该标注上停留，便会自动显示批注内容。7添加批注若要编辑或删除批注，则可在单元格上单击鼠标右键，在弹出的快捷菜单中选择“编辑批注”命令或“删除批注”命令即可。4.2.3数据的输入与编辑1输入数据输入普通数字输入分数输入百分数输入日期与时间输入负数输入小数输入文本4.2.3数据的输入与编辑2填充数据拖曳填充柄填充数据利用对话框填充数据自定义填充数据设置输入数据的允许范围4.2.3数据的输入与编辑设置数据有效性可以控制数据的输入内容，如指定某个单元格中只能输入小于10 的整数等，

8、这样可以有效防止手动输入时产生的错误。3检查数据有效性提示输入了非法值4.2.3数据的输入与编辑输入单元格中的数据可根据实际情况随时更改，而且可供选择的方法也较多。4编辑数据在编辑栏中编辑覆盖编辑在单元格中编辑利用快捷键编辑4.2.3数据的输入与编辑移动与复制数据可通过多种方法实现，如利用功能按钮、快捷菜单或快捷键均可进行操作。5移动与复制数据6删除数据删除数据的方法很简单，只需选择需删除的单元格或单元格区域后，按【Delete】键即可。学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONT

9、ENTS数据的管理与连接4.7工作表的打印4.8上机指导Excel综合实例4.94.3.1设置单元格格式选择需设置字体格式的单元格或单元格区域，在【开始】【字体】组中进行设置即可。1设置字体格式4.3.1设置单元格格式选择需设置对齐方式的单元格或单元格区域，在【开始】【对齐方式】组中进行设置即可。2设置对齐方式4.3.1设置单元格格式数据格式是指数据类型，如货币型、日期型和会计专用型等。3设置数据格式4.3.1设置单元格格式4设置边框设置边框的参数4.3.1设置单元格格式5设置填充颜色在“设置单元格格式”对话框中单击“填充”选项卡，选择颜色后单击按钮即可。也可直接在“字体”组中单击“填充颜色”

10、按钮，在弹出的下拉列表中选择颜色选项。4.3.2设置条件格式条件格式可以判断符合条件的单元格并自动为其设置指定的格式，如在成绩表中可以将所有低于60 分的单元格字体颜色设置为红色，这比手动设置的效率要高。4.3.3使用样式与套用格式选择单元格或单元格区域，在【开始】【样式】组的“样式”下拉列表框中选择相应的样式选项即可。1使用单元格样式2套用表格格式选择单元格区域，在【开始】【样式】组中单击“套用表格格式”按钮，打开“套用表格式”对话框，确认所选单元格区域后，单击“确定”按钮即可应用格式。根据模板新建工作簿4.3.4使用模板模板是具有某种数据和样式的工作簿，通过模板来创建工作簿，则可以减少电子

11、表格的制作程序，尽可能利用模板中已有的内容来完善表格。学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONTENTS数据的管理与连接4.7工作表的打印4.8上机指导Excel综合实例4.94.4.1保护工作簿保护工作簿涉及两个方面。工作簿的结构进行权限设置，防止被非法改动。工作簿文件进行加密设置，防止他人随意访问4.4.1保护工作簿保护工作簿窗口和结构加密工作簿4.4.2保护工作表如果只需保护工作簿中的某张工作表，则可在【审阅】【更改】组中单击“保护工作表”按钮，在打开的对话框进行设置，单击

12、“确定”按钮即可。4.4.3保护单元格如果想保证某些单元格中的数据不能被修改，或需要隐藏某些单元格中的公式时，就可以对单元格进行保护设置。学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONTENTS数据的管理与连接4.7工作表的打印4.8上机指导Excel综合实例4.94.5.1使用自动计算功能自动计算功能可以通过判断单元格位置来自动完成求和、求平均值、计数、求最大/ 最小值等操作。当需要完成这类操作且单元格数据符合条件时，即便不会使用Excel 的公式或函数，也能完成计算。4.5.2创建

13、公式在Excel 中，公式遵循一个特定的语法或次序：最前面是等号“=”，后面是参与计算的元素和运算符。1认识公式运算符常量数值或字符串引用单元格括号与数学上的运算符类似，不同的运算符代表着不同的运算数值或任意字符串实际上都是常量的一种类型指定要进行运算的区域。这类对象与常量刚好相反，将根据公式不同的位置发生变化主要用于控制公式中各表达式被处理的先后次序4.5.2创建公式2单元格引用相对引用绝对引用混合引用4.5.2创建公式选择要输入公式的单元格，在编辑框中输入“=”，然后在英文状态下逐次输入公式所需的内容，完成后按【Enter】键或单击编辑框左侧的“输入”按钮即可。3输入公式需要注意的是，如果

14、需要输入的是某个单元格或单元格区域的地址，可直接使用鼠标单击该单元格或拖曳选择该单元格区域。4.5.3编辑公式复制公式选择公式所在的单元格，按【Ctrl+C】组合键，选择目标单元格，按【Ctrl+V】组合键删除公式选择公式所在的单元格，按【Delete】键。修改公式将插入光标定位到编辑框的公式中，按修改数据的方法修改公式内容，完成后按【Enter】键或【Ctrl+Enter】组合键确认修改。移动公式选择公式所在的单元格，拖曳该单元格的边框至目标单元格。移动公式后，公式内容不会发生改变。4.5.4应用函数1认识函数0102030405即不进行计算，不发生改变的值，如数字、文本。用于判断数据真假的

15、值，即TURE（真值）或FALSE（假值）。用于建立可生成多个结果或可在行和列中排列的一组参数进行计算的单个公式。用来表示单元格在工作表中所处位置的坐标集。将函数作为另一个函数的参数使用。常量逻辑值数组单元格引用嵌套函数插入嵌套函数的过程4.5.4应用函数3嵌套函数嵌套函数可通过直接输入的方法创建，但这要求对函数语法格式非常熟悉，如果不是太熟悉，同样可通过插入的方式完成。4.5.4应用函数4常用函数介绍SUM函数AVERAGE函数COUNT函数COUNTIF函数INT函数ROUND函数IF函数MAX/MIN函数SUMIF函数4.5.5错误信息介绍在单元格中输入错误公式或函数后，Excel 会显

16、示错误信息，根据不同的错误原因，Excel显示的错误信息也不相同。#VALUE!#NAME?#N/A#DIV/0!#NULL!#NUM!#REF!学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONTENTS数据的管理与连接4.7工作表的打印4.8上机指导Excel综合实例4.94.6.1图表概述图表包含的组成部分一般包括图表区、图表标题、图例、绘图区、数据系列、数据标签、网格线、坐标轴与标题、背景墙与基底等。选择图表类型4.6.2创建图表Excel提供了各种类型的图表，根据需要可以为工作表

17、的数据创建所需类型的图表。输入图表标题调整图例移动图表4.6.2创建图表更改图表类型4.6.3编辑和修改图表选择图表，在【图表工具 设计】【类型】组中单击“更改图表类型”按钮。在图表上单击鼠标右键，在弹出的快捷菜单中选择“更改图表类型”命令。1调整图表类型4.6.3编辑和修改图表2修改图表数据修改图表数据是指调整图表中数据系列的内容，其方法为：选择图表；在工作表中拖曳表格中的蓝色框线即可。4.6.3编辑和修改图表3删除图表数据删除图表数据的方法为：选择图表中的某个数据系列，按【Delete】键即可。也可删除工作表中对应的图表数据，图表会自动完成更新操作。4.6.4美化图表Excel 提供了一些

18、预设的图表布局，可以快速对图表进行美化操作。1应用图表布局4.6.4美化图表使用预设的图表样式可以快速对图表中的所有文本格式和数据系列等进行美化。2应用图表样式4.6.4美化图表选择图表后，在“图表工具 布局”选项卡中利用“标签”组、“坐标轴”组和“背景”组中的各个参数，可以根据实际需要有针对性地对图表布局进行设置3设置图表布局4.6.4美化图表设置图表格式是指对图表中选择的某一对象的格式进行设置。4设置图表格式学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONTENTS数据的管理与连接4

19、.7工作表的打印4.8上机指导Excel综合实例4.94.7.1数据清单Excel需要在数据清单的环境下，才能更为精确地完成数据管理。虽然利用Excel 编辑好的工作表就是一张二维表格，但只有具备一定结构特点的二维表格才能称为数据清单。4.7.1数据清单数据清单中的列相当于数据库中的字段，列标题则相当于字段名。数据清单应具备以下特点。020103数据清单中含有数据的单元格区域必须是连续的。数据清单中允许出现空值，但不允许出现空行或空列。数据清单以列为项目字段，以行为数据记录。4.7.2数据排序简单排序是指以表格中的某一个项目下的数据为排列依据，对所有记录的顺序进行调整。1简单排序降序排列数据的

20、操作过程4.7.2数据排序简单排序虽然快捷，但一旦出现相同的排序依据时，其整理出的结果就不一定准确，此时便可利用多重排序解决问题，即设置主要关键字和若干次要关键字为排序依据，当主要关键字的排序依据相同时，便按次要关键字作为排序依据。2多重排序4.7.2数据排序自定义排序可以按照自行添加的序列排列数据，并自动按升序和降序的方式生成两种排序选项，这样便可以实现按各种顺序排列数据的目的。3自定义排序4.7.3数据筛选1简单筛选单击“筛选”按钮在弹出的下拉列表中选中或取消选中复选框单击“确定”按钮4.7.3数据筛选自定义条件筛选可以使用Excel 提供的筛选条件或自行设置筛选条件，能够满足对数据筛选的

21、更多需求。“自定义自动筛选方式”对话框中包含4 个下拉列表框和2 个单选项，左侧两个下拉列表框可设置筛选条件，右侧两个下拉列表框可设置条件数据；“与”单选项表示数据必须同时满足上下两侧下拉列表框中设置的条件；“或”单选项表示数据只要满足上下两侧下拉列表框中设置的条件之一即可。2自定义条件筛选4.7.3数据筛选通过高级筛选功能，可以自行组织不同的筛选条件，并筛选出结果，适用于无法利用Excel 已有筛选条件的情况下使用。3高级筛选4.7.4数据分类汇总1创建分类汇总分类汇总的操作过程分类汇总的控制按钮4.7.4数据分类汇总对数据进行分类汇总后，可利用左侧显示的按钮控制汇总数据的显示状态。2查看分

22、类汇总数据数据合并计算的过程4.7.5数据合并数据合并可以将不同工作表中的数据区域进行合并汇总。当需要合并计算的数据存放的位置相同时，便可合并计算它们的数据。指定数据透视表区域和位置4.7.6数据透视表的应用数据透视表能够通过建立交叉列表而实现对数据的汇总分析，同时还可以快速合并和比较分析大量的数据。创建数据透视表创建超链接的过程4.7.7创建超链接超链接可以实现数据共享，通过单击超链接后，便可跳转到相应的单元格或其他工作表中。学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONTENTS数

23、据的管理与连接4.7工作表的打印4.8上机指导Excel综合实例4.94.8.1调整页面布局“页面”选项卡主要用于设置页面方向、缩放比例、纸张大小和打印质量等参数。1“页面”选项卡设置表格在纸张上的显示方向设置缩放的具体数值选择纸张的页面大小设置打印质量设置从哪个页码开始打印4.8.1调整页面布局“页边距”选项卡主要用于设置表格内容与页面边距的距离和对齐方式等参数。2“页边距”选项卡分别设置电子表格距页面四周的距离设置页眉距页面顶端的距离设置页脚距页面底端的距离使电子表格在页面上水平居中和垂直居中4.8.1调整页面布局“页眉/ 页脚”选项卡主要用于为电子表格添加页眉和页脚对象，能够起到辅助显示

24、电子表格制作信息等方面的内容。3“页眉/页脚”选项卡选择预设的页眉内容选择预设的页脚内容通过各种控件按钮设置页眉、页脚内容分别设置奇数页和偶数页的页眉与页脚使首页不显示页眉页脚内容4.8.1调整页面布局“工作表”选项卡主要用于设置打印范围和打印效果。4“工作表”选项卡指定需要打印到纸张上的电子表格区域指定电子表格中的标题区域设置是否打印网格线、批注和行号列标等设置先列后行打印还是先行后列打印4.8.2预览打印效果设置好页面布局后，应当及时预览效果，只有确认无误后，才开始正式打印电子表格。预览打印效果的方法为：在“页面设置”对话框中单击“打印预览”按钮，或单击工作窗口左上角的“文件”选项卡，选择

25、左侧的“打印”选项，即可在右侧界面中预览打印效果。学习导航Excel 2010 概述电子表格的基本操作电子表格的格式化操作保护电子表格数据公式与函数的应用图表的应用4.14.24.34.44.54.6CONTENTS数据的管理与连接4.7工作表的打印4.8上机指导Excel综合实例4.94.9.1计算销售统计表并汇总产品销售情况1案例目标将Sheet1 工作表命名为“销售情况统计表”，然后将工作表的A1:G1 单元格合并为一个单元格，内容水平居中；计算“上月销售额”和“本月销售额”列的内容（销售额= 单价 数量，数值型，保留小数点后0 位）；计算“销售额同比增长”列的内容（同比增长=（本月销售

26、额- 上月销售额）/ 本月销售额，百分比型，保留小数点后1 位）；选取“产品型号”列、“上月销售量”列和“本月销售量”列内容，建立“簇状柱形图”，图表标题为“销售情况统计图”，图例置底部；将图表插入到表的A14:E27 单元格区域内，保存销售统计表.xlsx 文件。（1）在考生文件夹下打开工作簿文件“销售统计表.xlsx”。4.9.1计算销售统计表并汇总产品销售情况 4.9.1计算销售统计表并汇总产品销售情况（2）打开工作簿文件“产品销售情表.xlsx”，对工作表“产品销售情况表”内数据清单的内容按主要关键字“产品名称”的降序次序和次要关键字“分公司”的降序次序进行排序，完成对各产品销售额总和

27、的分类汇总，汇总结果显示在数据下方，工作表名不变，保存产品销售情况表.xlsx 工作簿。4.9.1计算销售统计表并汇总产品销售情况本例首先需要利用不同的公式对销售额和销售额同比增长进行计算，重点在于对公式的正确应用，然后通过指定的数据源创建柱形图并对图表进行编辑，最后对产品销售额进行分类汇总。2制作思路4.9.2创建分配回县/考取散点图并汇总平均销量1案例目标将Sheet1 工作表命名为“回县比率表”，然后将工作表的A1:D1 单元格合并为一个单元格，内容水平居中；计算“分配回县/ 考取比率”列的内容（分配回县/ 考取比率=分配回县人数/ 考取人数，百分比，保留小数点后面两位）；使用条件格式将“分配回县/ 考取比率”列内大于或等于50% 的值设置为红色、加粗。选取“时间”和“分配回县/ 考取比率”两列数据，建立“带平滑线和数据标记的散点图”图表，设置图表样式为“样式4”，图例位置靠上，图表标题为“分配回县/ 考取散点图”，将图表插入到表的A12:D27 单元格区域内。（1）打开工作簿文件“大学升学和分配情况表.xlsx”。4.9.2创建分配回县/考取散点图并汇总