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

第5章 Excel 2010电子表格数据处理 本章主要内容  创建和管理工作簿及工作表。  数据输入与编辑。  设置工作表格式。  使用公式和函数。  数据分析与管理。  使用图表。  打印工作表。 1 5.1 Excel 2010的基本知识 5.1.1 启动和退出Excel 2010 启动Excel 2010的方法主要有如下几种。 （1）选择“开始”→“所有程序”→Microsoft Office→Microsoft Excel 2010命令。 （2）双击桌面上Excel 2010的快捷方式图标。 通过以上的方法，可以打开一个空白的Excel 2010文档。 退出Excel 2010有如下方法。 （1）单击Excel 2010窗口右上角的 按钮。 （2）单击“文件”菜单，在弹出的菜单中选择“退出”选项。 （3）使用Alt＋F4快捷键。 （4）双击窗口左上角的Excel图标。 2 5.1.2 Excel 2010窗口的组成 3 5.1.3 工作簿的创建和管理 一个Excel文档就是一个工作簿，工作簿由若干工作表组成。工作表是在Excel中用于存储和管理数据的主要文档，它存在于工作簿中。如图5-1所示，新建“工作簿1”默认由Sheet1、Sheet2、Sheet3三张工作表组成。工作簿与工作表的关系，就像账簿与账页的关系。 4 5.1.3 工作簿的创建和管理 1．创建空白工作簿 2．基于现有工作簿创建工作簿 3．使用模板快速创建工作簿 5 5.1.3 工作簿的创建和管理 4．保存工作簿 保存工作簿分为两种情况。 （1）保存新建工作簿时，需要为工作簿指定一个保存的位置和名称；保存被修改的工作簿，也就是覆盖原工作簿。保存方法有如下3种。  选择“文件”菜单中的“保存”选项。  按Ctrl＋S快捷键。  单击“快速访问工具栏”上的“保存”按钮 。 （2）另存工作簿即另外重新保存工作簿，也就是将已有的工作簿以其他文件格式、其他文件名或选择其他位置等方式进行再次保存。操作方法如下：选择“文件”菜单→“另存为”命令，在弹出的对话框中选择保存位置，输入文件名，然后单击“保存”按钮即可。 6 5.1.3 工作簿的创建和管理 5．隐藏和显示工作簿 打开需要隐藏的工作簿，在“视图”选项卡的“窗口”选项组中单击“隐藏”按钮，当前窗口即被隐藏起来。 在“视图”选项卡的“窗口”选项组中，单击“取消隐藏”，则隐藏的工作簿可以重新显示。 7 5.1.4 工作表的创建和管理 1．新建工作表 新建的工作簿默认包含了3张独立的工作表，用户可以根据需要增加工作表的数目，最多可达255张。新建工作表的操作方法有如下3种。 （1）打开工作簿，选择“开始”选项卡→“单元格”选项组→“插入”→“插入工作表”选项，即可添加新工作表。 （2）打开工作簿，用鼠标右键单击工作表标签，在弹出的快捷菜单中选择“插入”选项。在弹出的“插入”对话框中，选择“常用”→“工作表”选项，单击“确定”后即插入了新工作表。 （3）直接单击工作表标签上的 “插入工作表”按钮。 8 5.1.4 工作表的创建和管理 2．删除工作表 单击工作表标签，选定要删除的工作表，然后选择“开始”选项卡→“单元格”选项组→“删除”→“删除工作表”选项，即可删除该工作表。也可以用鼠标右键单击选定的工作表标签，选择快捷菜单中的“删除”选项。 3．选择工作表 默认状态下，当前工作表为Sheet1。 用鼠标选择Excel表格是最常用、最快速的方法。只需在Excel表格下方的要选择的工作表标签上单击，即可选择该工作表为当前活动工作表。 按下Shift键，同时依次单击第1个和最后1个需要选择的工作表标签，即可选择连续的Excel工作表。 要选择不连续的Excel工作表，只需按下Ctrl键的同时选择相应的Excel工作表标签即可。 9 5.1.4 工作表的创建和管理 4．移动/复制工作表 （1）移动工作表 单击要移动的工作表标签，按下鼠标的同时，沿着标签行拖放至目标位置即可移动工作表。也可以右键单击选定的工作表标签，选择快捷菜单中的“移动或复制”选项，在弹出的“移动或复制工作表”对话框中设置。 （2）复制工作表 复制工作表与移动工作表类似。在用鼠标按下工作表标签拖动时，同时按下Ctrl键，出现一个“＋”则表示复制工作表。 5．重命名工作表 双击要重命名的工作表标签，标签以反白显示，在其中输入新的名称并确认。也可以右键单击选定的工作表标签，选择快捷菜单中的“重命名”选项进行操作。 10 5.2 数据输入与编辑 5.2.1 选择单元格和区域 将鼠标光标指向需选定的单元格并单击，或利用键盘的上、下、左、右方向键，均可选定不同的单元格。 如果要用鼠标选定一个连续的单元格区域，先用鼠标单击区域左上角的单元格，按下鼠标左键并拖动至区域右下角的单元格，然后释放鼠标即可。 选择多个不连续单元格区域时，先单击或拖动鼠标选定第一个区域，然后按下Ctrl键再去选定其他区域。 单击某一列单元格的列标，可以选定整个列；单击某一行单元格的行号，可以选定整个行。同样，单击列标或行号，并按下鼠标左键拖动，则可以选定连续的多列或多行。 11 5.2.2 在单元格中输入数据 （1）在单元格中输入文本 在Excel中，文本可以是数字、空格和非数字字符的组合。所有文本在单元格中默认为左对齐显示。 （2）在单元格中输入数字 在Excel中，数字一般是0～9、＋、（）、/、E、e等符号的组合，可以是整数、小数、分数，也可以是用科学计数法表示的数字。所有数字在单元格中默认为右对齐显示。 （3）在单元格中输入日期和时间 在Excel单元格中输入日期有两种方式：一种是用“/”分隔的年月日，如2017/1/23；另一种是用“-”分隔的年月日，如2017-1-23。两种方式输入后均显示为后一种格式。 在Excel单元格中输入时间是需要带上冒号的，如12:34，11:22:33。显示的格式以第一次输入的格式为准。 日期和时间在单元格中默认为右对齐显示。 12 5.2.3 在单元格中自动填充数据 当选中一个单元格时，该单元格的右下角会有一个黑色的小方块，这个小方块称为填充柄。当鼠标指向填充柄时，鼠标形状会由一个空心“十”字变成一个黑色实心“十”字。按下填充柄横向或纵向拖动，即可在相邻的单元格中完成数据序列的填充。 例如，从工作表的B5单元格开始，沿B列向下依次填入5、10、15、20、25这样一组数据，可以采用以下3种方法。 方法一：利用鼠标拖动填充柄填充数据。 方法二：利用“序列”对话框填充数据。 方法三：利用鼠标右键填充数据。 13 5.2.4 移动与复制单元格数据 选中需要操作的数据单元格或区域，执行“开始”选项卡→“剪贴板”选项组→“剪切”或“复制”选项，然后选中需要放置该数据的目标单元格，执行“粘贴”选项，即可移动或复制数据。 5.2.5 清除与删除单元格 清除单元格会删除单元格中的内容、格式、批注或全部，但是空白单元格仍然保留在工作表中。删除单元格，则会从工作表中移除掉这些单元格，并调整周围的单元格以填补删除后的空缺。 清除单元格内容时，可以直接按Delete键，或执行“开始”选项卡→“编辑”选项组→“清除”→“清除内容”选项，或单击右键单击后选择“清除内容”。 删除单元格时，选中需要删除的单元格，执行“开始”选项卡→“单元格”选项组→“删除”→“删除单元格”选项，在弹出的“删除”对话框中，根据需要选择左移或上移单元格。 14 5.2.6 查找与替换数据 1．查找单元格数据 单击任意单元格，执行“开始”选项卡→“编辑”选项组→“查找和选择”→“查找”选项，在弹出的对话框中选择“查找”选项卡。在“查找内容”框中输入要查找的目标内容，单击“查找全部”或“查找下一个”按钮即可完成简单的查找。 2．替换单元格数据 单击任意单元格，执行“开始”选项卡→“编辑”选项组→“查找和选择”→“替换”选项，在弹出的对话框中选择“替换”选项卡。在“查找内容”框中输入要被替换的内容，在“替换为”框中输入新的内容，单击“替换”或“全部替换”按钮即可完成简单的替换。 15 1．合并单元格 选中需要合并的相邻单元格区域，执行“开始”选项卡→“对齐方式”选项组→“合并后居中”→“合并单元格”选项。 2．拆分单元格 选中合并后的需要拆分的单元格，执行“开始”选项卡→“对齐方式”选项组→“合并后居中”→“取消单元格合并”选项。 5.2.7 合并与拆分单元格 16 5.3.1 设置单元格格式 对于简单的单元格格式设置，可以直接通过“开始”选项卡中的不同选项功能来实现，如设置字体、对齐方式、数字格式等。对于比较复杂的格式操作，选择一个单元格或单元格区域后，执行“开始”选项卡→“单元格”选项组→“格式”→“设置单元格格式”选项，或者单击鼠标右键选择“设置单元格格式”选项，即可弹出含有多个选项卡的“设置单元格格式”对话框。 5.3 工作表的格式设置 17 5.3.1 设置单元格格式 18 1．插入行和列 2．删除行和列 3．隐藏行和列 4．调整行高和列宽 方法一：用鼠标手动调整。 方法二：精确设置行高和列宽。 设置行高时，选择“开始”选项卡→“单元格”选项组→“格式”→“行高”。行高的单位是“磅”。 设置列宽时，选择”开始”选项卡→“单元格”选项组→“格式”→“列宽”。列宽的单位是“字符”。 5.3.2 设置行和列 19 样式就是字体、字号和缩进等格式设置的组合，Excel将这一组合作为集合加以命名和存储。 使用Excel 2010的内置单元格样式，可以先选中需要设置样式的单元格或单元格区域，然后选择“开始”选项卡→“样式”选项组→“单元格样式”，再单击对应选项来直接套用内置的样式。 5.3.3 套用单元格样式 如果要删除某个不再需要的单元格样式，可以单击任意已经设置样式的单元格，在“单元格样式”选项中右键单击已突出显示的样式，然后在弹出的快捷菜单中选择“删除”选项。也可以通过执行“开始”选项卡→“编辑”选项组→“清除”→“清除格式”选项来完成。 20 执行“开始”选项卡→“样式”选项组→“套用表格格式”选项，弹出工作表样式选项，选择需要的内置表格样式，在弹出的对话框中设置使用的目标区域后单击“确定”按钮，如图5-16所示。 设置完成后，在Excel功能区中，会增加一个“设计”选项卡。用户可以选择其中的选项功能，完成相应的表格设计。 在Excel 2010中，图标 称为“折叠按钮”，主要用于单元格区域的选择。通过单击对话框中的“折叠按钮”，能够将当前对话框缩小显示，用户通过拖动鼠标来选择目标区域。选定后，再次单击该按钮，还原并返回原始对话框。 5.3.4 套用表格格式 21 条件格式功能可以根据指定的公式或数值来确定搜索条件，如果满足指定的条件，Excel自动将预置格式应用于单元格。这些格式可以是字体格式、图案、边框和颜色等。 选定需要设置格式的单元格区域，执行“开始”选项卡→“样式”选项组→“条件格式”选项，选择“突出显示单元格规则”中的某个条件来建立一个条件格式规则。 5.3.5 条件格式 22 所谓批注，就是帮助理解的批语或注解，一般可以用简短的提示性文字来描述。在Excel 2010中，用户可以为某个单元格或单元格区域添加批注。 选中需要添加批注的单元格或单元格区域，执行“审阅”选项卡→“批注”选项组→“新建批注”选项，在弹出的批注文本框中输入批注内容。 当鼠标指向单元格右上角红色标记时，会显示出已添加的批注内容。 5.3.6 添加批注 23 公式是单元格中的一系列值、单元格引用、名称和运算符的组合，用户可以利用公式计算得到新的值。函数是Excel预定义的内置公式。每个函数都有其自身特定的功能、格式及参数，可以完成数学、文本、逻辑的运算或查找工作表的信息等功能。 5.4 公式和函数 24 1．相对地址引用 若公式所在单元格的位置发生改变，则公式中引用的单元格的位置也将随之发生变化。C6就是一个相对引用的地址格式。在使用公式时，默认情况下一般使用相对地址来引用单元格的位置。 2．绝对地址引用 无论公式所在单元格的位置发生什么变化，公式中引用的单元格的位置都不会发生改变。绝对地址引用格式是在行和列前面分别加上一个“$”。$C$6就是一个绝对引用的地址格式。 3．混合地址引用 混合地址引用是指引用单元格地址的行和列之中一个是相对的，一个是绝对的。在复制公式时，只需行或只需列保持不变时，就需要使用混合地址引用。$C6就是一个混合引用的地址格式。 5.4.1 引用单元格 25 公式遵循一个特定的语法和次序：最前面是等号“=”，后面是参与计算的数据对象和运算符。数据对象可以是常数、单元格地址或引用的单元格区域等。运算符用来连接要运算的数据对象，并说明进行了哪种公式运算。 5.4.2 使用公式 26 Excel将具有特定功能的一组公式组合在一起以形成函数。Excel中的函数都是内置的，每个函数都有其自身特定的功能和语法格式。函数一般包含3个组成部分：等号、函数名和参数。例如，“=SUM（C1:G10）”，该公式表示对C1:G10区域内所有单元格中的数据求和。其中，SUM是内置求和函数的名称，C1:G10表示C1至G10为起止的单元格区域。 Excel 2010中包括11种类型的数百个函数，每个函数的应用各不相同。常用的几种函数包括求和、平均值、计数、最大值、最小值、条件统计等。 5.4.3 使用函数 27 要在工作表中使用函数，首先要插入函数。选中需要存放函数结果的单元格后，直接单击编辑栏中的fx按钮，或者执行“公式”选项卡→“函数库”选项组→“插入函数”选项，打开“插入函数”对话框。在“插入函数”对话框中，可通过“或选择类别”后的下拉列表框来选择函数的分类，在“选择函数”列表框中选择正确的函数名称，并且可以在列表框下方查看到该函数的简单说明。 5.4.3 使用函数 28 1．AVERAGE函数 2．MAX函数和MIN函数 3．IF函数 4．COUNT函数和COUNTIF函数 5.4.3 使用函数 29 5.5.1 排序 1．按单列内容排序 先选中要排序的列中任意一个单元格，然后执行”开始”选项卡→“编辑”选项组→“排序和筛选”选项，选择“升序”或“降序”选项，也可以通过“数据”选项卡→“排序和筛选”选项组来完成选择。 2．按多列内容排序 先选中要排序的多列数据中的任意一个单元格，然后执行“数据”选项卡→“排序和筛选”选项组→“排序”选项。在弹出的“排序”对话框中，“添加条件”和“删除条件”按钮分别用来添加和删除排序条件。 设置“主要关键字”和两个“次要关键字”为排序条件，各个条件按照前后顺序依次优先执行。 5.5 数据分析与管理 30 经过筛选后的数据表只显示标题行及满足指定条件的数据行，以供用户浏览、分析。 1．自动筛选 执行“数据”选项卡→“排序和筛选”选项组→“筛选”选项，标题行中的各个字段名称将变成一个带有下拉列表按钮的框名。单击任意一个字段名后的下三角按钮，将显示该列中所有的数据筛选清单。 单击已选中的“筛选”按钮，可以退出自动筛选状态，显示出数据表中所有的记录。 5.5.2 筛选 31 2．高级筛选 高级筛选是指以指定区域为条件的筛选操作。使用高级筛选功能的步骤如下。 （1）建立一个用于实现筛选的条件区域，用来指定数据所要满足的筛选条件。条件区域的第一行是所有筛选条件的字段名，这些字段名与原始数据表中的字段名必须完全一致。条件区域的第二行是指定的条件值。 （2）执行“数据”选项卡→“排序和筛选”选项组→“高级”选项，弹出“高级筛选”对话框，“列表区域”用来选择原数据表中需要进行筛选的单元格区域。选择好“列表区域”和“条件区域”后，如图5-37所示，单击“确定”按钮即可完成筛选操作。 5.5.2 筛选 32 分类汇总对数据表中指定的字段进行分类，然后对同一类记录的有关信息进行汇总、分析。汇总的方式可以由用户指定。 对数据表进行分类汇总，首先要求数据表中的每一列都要有列标题。同时，要求汇总前数据表必须先对分类字段进行排序。 选中数据区域中的任意单元格。执行“数据”选项卡→“分级显示”选项组→“分类汇总”选项。 在含有分类汇总结果的数据区域中，单击任意一个单元格，执行“数据”选项卡→“分级显示”选项组→“分类汇总”选项，在打开的“分类汇总”对话框中单击“全部删除”按钮即可退出“分类汇总”结果。 5.5.3 分类汇总 33 数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式Excel报表。数据透视表不仅可以转换行和列以查看源数据的不同汇总结果，也可以显示不同页面以筛选数据，还可以根据需要显示区域中的细节数据。源数据可以来自Excel数据区域、外部数据库或多维数据集，或者另一张数据透视表。 5.5.4 数据透视表 34 （1）选中存放结果区域中的任意单元格。 （2）选择数据来源。执行“插入”选项卡→“表格”选项组→“数据透视表”→“数据透视表”选项，弹出“创建数据透视表”对话框。 （3）设置数据透视表的布局。 5.5.4 数据透视表 35 5.6.1 创建图表 （1）单击空白数据区域的任意单元格。选择“插入”选项卡，在“图表”选项组中选择图表的类型，单击某种类型下方的下三角按钮，选择具体的图表样式。 （2）执行“设计”选项卡→“数据”选项组→“选择数据”选项，弹出“选择数据源”对话框。在“图表数据区域”中选择需要生成图表的数据列。 （3）单击“确定”按钮。 5.6 图表 36 “设计”选项卡 “布局”选项卡 “格式”选项卡 5.6.2 图表工具 37 5.7.1 页面设置 “页面设置” 对话框——在对话框中可以完成页面、页边距、页眉页脚及工作表等相关选项的设置。 设置打印区域——按住鼠标左键，拖动选中需要打印的目标数据区域。然后执行“页面布局”选项组→“页面设置”选项组→“打印区域”→“设置打印区域”选项。 网格线——默认情况下打印出来的工作表或工作簿不显示网格线。在“页面布局”选项卡上的“工作表选项”组中，选中“网格线”下的“打印”复选框，则可以在打印时打印出网格线。 5.7 打印工作表 38 5.7.2 打印预览与打印 39 1、2、3 4（3、5、8、10） 作业 40 $$