办公软件【第九章：工作表的数据操作】.ppt

董成杰 ,Office办公软件 第9章工作表的数据操作,本章学习内容,9.1 图表的实用技能 9.2 数据操作 9.3 高级应用（规划求解）,图表具有较好的视觉效果，可方便用户查看数据的差异、图案和预测趋势。通过图表，可以将工作表数据转换成图形。,9.1.1 认识图表,9.1.2 建立图表,在Excel中，可以用两种方式建立图表：一种是将图表和工作表数据放在同一工作表中，称为嵌入式图表；另一种是将图表放在工作簿的图表工作表中，使工作表与图表分开。 嵌入图表和图表工作表都与建立它们的工作表数据相链接，当改变了工作表中的数据时，这两种图表都会随之更新。,9.1.3 创建图表,1. 选择要创建图表所需的数据和标记 选择数据的区域可以是连续的，也可以是不连续的。选择不连续的区域，可以按住Ctrl键逐一选择各区域。 2. 选择图表类型 可单击常用工具栏上的“图表向导”按钮 或选择菜单命令【插入】【图表】打开 “图表向导”对话框，可在“标准类型”选项卡上选择所需的“图表类型”和“子图表类型”。 3. 确定图表源数据 单击“下一步”按钮，在该对话框中可以对事先选定的数据区域进行修改，只要在“数据区域”框中输入新的单元格区域即可。 4. 图表选项 5. 图表位置,9.1.3 图表类型,Excel提供了14种图表类型，每个基本类型下面还有子类型可供选择。我们需要分析不同图表类型的特点，选择合适的图表类型，使数据更易理解。比较常用的图表类型有以下几种： 1. 柱形图 柱形图是默认的图表类型，用长条显示数据点的值。柱形图适用于大多数要对数据进行比较的情况。 2. 条形图 条形图类似于柱形图，仅是将柱形图旋转90，横轴为数值， 纵轴为分类，条形图的应用也类似于柱形图。,9.1.3 图表类型,3. 折线图 折线图是将同一系列的数据在图中表示成点，再用直线连接起来。折线图适用于显示某段期间内数据的变动情形及趋势。 4. 饼图 饼图适合于显示一组数值内部的比较，以及显示各个组成部分在总体中所占的比例。 5. 面积图 面积图是将每一系列数据用直线段连接起来，并将每条线以下的区域 用不同颜色填充，面积图强调的是变化量，而不是时间和变化率。,9.1.4 编辑图表,当创建了一个图表之后，很多情况下还需要对图表进行编辑和修改。如更改图表的类型、增加或删除系列等。 当创建了一个图表之后，在屏幕上出现“图表”工具栏。如果用户的屏幕上没有显示“图表”工具栏，可以选择“视图”菜单的“工具栏”命令，打开“图表工具栏”。,9.1.5 改变图表类型,可以利用下述方法改变图表类型： 选中图表，这时菜单栏上出现“图表”菜单项。选择菜单【图表】【图表类型】命令，或单击常用工具栏中的图表向导按扭，打开“图片类型”对话框，选择新的图表类型。 利用“图表”工具栏中的按钮可以快速修改图表类型。用鼠标单击“图表类型”按钮右侧的下拉按钮，出现如右图所示的下拉列表。单击下拉列表中的一个小图标，创建的图表就会立即转换成该类型。,9.1.6 修改图表对象属性,可以修改任何“图表对象”（如标题、图例、网格线、数据标志、数据表，以及文本批注）或者编排其格式，以此来增强图表的外观并提高它的整体效果。“图表标题”用来帮助识别图表的主要用途，而“轴标题”则阐明了构成的数据。标题可以占据多行，并且可以像编排其他工作表文本格式那样编排其格式。, 选定图表对象,首先选中图表，单击 “图表”工具栏中的“图表对象”下拉按钮选择下拉列表中的图表对象。如分类轴、绘图区、数值轴等。 单击图表中的图表对象，当图表对象中出现控制点时，表明此对象被选中。, 编辑图表对象,在选定图表对象后，可以通过以下方法之一打开与对象相对应格式对话框，完成图表对象的格式设置。 选择“格式”菜单，在其下拉菜单的第一项显示相应的图表对象命令。 单击鼠标右键，在快捷菜单的第一项显示相应的格式设置命令。 单击图表工具栏中的“坐标轴格式”按钮 。 双击图表对象。,9.1.7 添加和删除数据系列,1. 快速添加数据系列 选定包含要添加到图表中的数据系列的区域。 将该区域拖到现有的图表中，所添加的数据系列便可显示在图表中。 2. 删除数据系列 选定包含要删除的数据系列的图表。单击该数据系列中的任意数据点，数据系列将被选中。 按键盘中的Delete键，Excel删除数据系列并显示修改后的图表。,9.2 工作表的数据库操作,工作表的数据库操作是Excel的重要功能之一，在数据管理方面提供了排序、检索、数据筛选、分类汇总等数据库管理功能。 数据表与通常使用的由若干行和列组成的二维表格相似，表中各列的列标题称为“字段名”，字段名以下该列的数据称为该字段的“值”，每一行称为数据库的一条“记录”。,9.2.1 记录单,输入数据表的记录数据时，可以直接逐行输入记录数据；也可用“记录单”命令输入记录数据。下面以“电视机销售情况表”为例具体讲解记录单的使用方法。, 使用记录单输入数据,操作步骤： 单击数据表中的任一单元格。 选择菜单命令【数据】【记录单】，打开“记录单”对话框。在对话框中单击“新建”按钮。 在各字段框中输入新记录的字段值，用Tab键或用鼠标单击的方法转到下一字段，各字段值输入完毕可按Enter键转下一记录。重复本步骤，直到全部记录输入完毕。, 编辑记录,记录数据由于种种原因，总存在要修改、插入、删除和追加的需求。为了对记录数据进行增、删、改操作，首先要找到待编辑的记录，即记录定位。记录定位有两种方法： (1) 在记录单对话框中，单击“上一条”或“下一条”或利用滚动条定位到目标记录。 (2) 条件定位：根据目标记录满足的条件，查找目标记录。 单击对话框中的“条件”按钮，出现“条件”对话框，在与条件相关的字段输入条件值。如：“品牌”字段输入“长虹”，单击“下一条”按钮，就可以定位在满足条件的第一条记录上。再单击“下一条”按钮，可以定位在满足条件的第二条记录上，依此类推。,9.2.2 排序,排序是我们在进行数据分析和处理时，经常进行的一项操作。通过排序，我们可以根据某特定列的内容来重新排列数据清单中的行。在Excel中，可以按照字母、汉字或数字从大到小或从小到大的顺序进行排列。对于汉字的排序即可按照汉语拼音的顺序排序，也可按照笔划多少的顺序排序。, 单关键字排序,在常用工具栏上有两个用于排序的按钮，分别是“升序排序” 和“降序排序” 。 方法：用鼠标点击需要排序的关键字段的任一单元格位置，再单击常用工具栏中的排序按钮。 例：将电视机销售收入表，按销售收入对数据表进行降序排序。 用鼠标点击数据表内“销售收入”列的任意单元格，以确定关键字段。 用鼠标点击常用工具栏的“降序排序”按钮进行排序。,降序排序,升序排序, 使用对话框排序,使用“排序”对话框对选定的单元格区域进行排序Excel中可以同时最多对三列数据进行排序。方法如下： 将光标停在数据清单中的任一单元格中。 选择菜单【数据】【排序】命令。 在“排序”对话框选择“主关键字”、“次关键字”、“第三关键字”以及排序的趋势。 点击选项按钮可以打开排序选项对话框。 在排序选项对话框中可以设置排序的方向、方法或采用在自定义排序次序。,9.2.3 数据筛选,筛选是在数据库中查找满足条件的记录。当筛选一个数据清单时，Excel只显示那些符合条件的记录，而将其它记录从视图中隐藏起来。筛选分为两种：自动筛选和高级筛选。, 自动筛选,自动筛选是按照简单的比较条件来快速地选取数据，将不满足条件的数据暂时隐藏起来，将满足条件的数据显示在表格中。 方法：将光标停在数据表的任一单元格中，选择菜单【数据】【筛选】【自动筛选】命令。这时在清单中每个字段名右边出现下拉式列表按钮，用鼠标点击该按钮便可选择筛选条件。,例：自动筛选的应用,要求： (1)在“筛选1”工作表中显示品牌为“TCL”的记录。 (2)在“筛选2”工作表中显示销售收入为前三名的记录。 (3)在“筛选3”工作表中显示销售利润在10000元以上的康佳牌电视机的记录。,任务分析,(1)新建一个工作簿文件，将工作表sheet1 改名为“筛选1”并完成工作表数据的录入和格式设置。再将第一个工作表的内容复制到其它两个工作表，并进行重命名。 (2)将当前单元格置于数据表中的任意位置，选择菜单命令【数据】【筛选】【自动筛选】。下拉按钮出现在第一行的位置，很显然这并不是我们所需要的。（第一行采用的是标题“跨列居中”） 原因：数据表上方通常不要出现连续的两行以上的表格标题内容，而此数据表上方有两行的表格标题（行号为1，2的两行）。 解决方法：在第2行处添加一个空行或先选中数据表中的全部数据再进行筛选操作。 我们现在采用添加空行的方法，在表格标题的下方插入一个空行再进行筛选操作。,实施步骤,(1)将当前单元格置于数据表中的任意位置，选择菜单命令【数据】【筛选】【自动筛选】。单击字段名“品牌”右侧的下拉按钮，在列表中选择“长虹”。 (2)在工作表“筛选2”中显示销售收入为前三名的记录。单击工作表标签“筛选2”使它成为当前工作表。将当前单元格置于数据表中的任意位置，选择菜单命令【数据】【筛选】【自动筛选】。然后按下列步骤操作： 用鼠标点击字段名“销售收入”右侧的下拉按钮，选择（前10个）； 在打开的对话框中选择“最大”、“3”、“项”。 点击“确定”按钮完成筛选。,(3)在工作表“筛选3”中显示销售利润在10000元以上的康佳牌电视机 单击工作表标签“筛选3”使它成为当前工作表。将当前单元格置于数据表中的任意位置，选择菜单命令【数据】【筛选】【自动筛选】。然后按下列步骤操作： 单击字段名品牌右侧的下拉按钮，选择“康佳”，此时在数据表中只显“康佳”品牌的记录内容。 单击字段名销售利润右侧的下拉按钮，选择“自定义”，此时会弹出“自定义自动筛选方式”对话框。 在对话框的关系运算下拉列表中选择“大于”。 在对话框的比较运算值列表框中输入10000。 单击“确定”按钮完成筛选，此时在工作表中显示的是销售利润在10000元以上的康佳牌电视机的销售情况。, 高级筛选,“高级筛选”命令可像“自动筛选”命令一样筛选区域，但不显示列的下拉列表，而是在单独的条件区域中键入筛选条件。条件区域必须具有列标签。请确保在条件区域与列表区域之间至少留了一个空白行。 应用高级筛选, 分类汇总,分类汇总可以对数据表的某个字段提供诸如“求和”、“求平均值”、“计数”、“求最大值”等汇总函数，实现对分类汇总值的计算。并且可以将计算结果分级显示出来。使用分类汇总之前，必须先按分类字段对数据表进行排序。,9.2.4 数据透视表,数据透视表是交互式报表，可快速合并和比较大量数据。并可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。,例：统计销售人员订单金额,任务分析,使用数据透视表可以快速统计出每一个销售人员的“订单金额”，及在不同地区的每一个销售人员的“订单金额”。若要创建数据透视表，可运行“数据透视表和数据透视图向导”。在向导中，从工作表列表或外部数据库选择源数据。向导提供报表的工作表区域和可用字段的列表。当将字段从列表窗口拖到分级显示区域时，Excel 自动汇总并计算报表。,步骤,(1)打开订单统计工作表，将光标定位在数据表中的任意单元格。例如，单击单元格 A4。在“数据”菜单上，单击“数据透视表和数据透视图”，将启动向导。 (2)在向导的步骤 1 中指定待分析数据的数据源类型和选择所需创建的报表类型。在这里我们只取默认值。用鼠标单击下一步。 (3)在“数据透视表和数据透视图向导3步骤之2”对话框中确定数据源区域，我们仍取默认值，单击下一步按钮。 (4)在“数据透视表和数据透视图向导3步骤之3”对话框中确定数据源显示位置。取默认值，“新建工作表”单击完成按钮。,步骤,(5) 新工作表“Sheet4”插入到工作簿中。新工作表包括三个项目：“数据透视表字段列表”、报表的布局区域（其中包括各个用于放置拖放字段的带外边框的拖放区域）和“数据透视表”工具栏。假如不能看到类似于如下所示的字段列表： 单击带外边框的区域。如果还是不能看到字段列表，则在“数据透视表”工具栏上（该工具栏浮动在工作表上，或者固定在一边。），单击“显示字段列表”按钮。 提示：如果“数据透视表”工具栏不可见，在“视图”菜单上，指向“工具栏”，然后单击“数据透视表”。,步骤,(6)设置报表布局 现在将设置报表布局，以算出每位销售人员的销量。将字段放置到布局中时，报表布局的外观将发生更改。从“数据透视表字段列表”中，将“销售人员”字段拖动到标签为“将行字段拖至此处”的拖放区域。 可以通过选择字段名称或字段名称前面的按钮拖动字段。如果将字段拖放到了错误的拖放区域，只需把它拖放到正确的拖放区域即可。 拖放字段名称后，它们仍保留在列表上，但是更改为黑体。,步骤,(7)从“数据透视表字段列表”中，将“订单金额”字段拖动到标签为“请将数据项拖至此处”的拖放区域。 将字段拖放到数据项的拖放区域中时，彩色外框将消失，并将显示报表，其中显示有每位销售人员的销售总额。 要查看该报表，且工作表顶部不显示空白的页字段拖放区域： 单击布局区域外的任意位置。例如，在单元格 C4 内单击，页字段区域消失，“数据透视表字段列表”也消失。,步骤,(8)创建页视图 现在可以进行新的尝试：创建页视图。在本示例中，将显示销售人员的订单金额，不同地区的数量显示在页面上。将“地区”字段拖放到标签为“将页字段拖至此处”的拖放区域。 将“国家/地区”字段从“数据透视表字段列表”拖放到“将页字段拖至此处”区域。,9.3 规划求解,Excel高级用户有时会希望完成这样的操作，通过调整某些单元格或者单元格区域的值，求得工作表上某个单元格（被称为目标单元格）中公式的最优解。Excel提供“规划求解”功能帮助用户完成这样的操作。“规划求解”将对直接或间接与目标单元格中公式相关联的一组单元格中的数值进行调整，最终在目标单元格公式中求得期望的结果。“规划求解”通过调整所指定的可更改的单元格（可变单元格）中的值，从目标单元格公式中求得所需的结果。在创建模型过程中，可以对“规划求解”模型中的可变单元格数值应用约束条件，而且约束条件可以引用其他影响目标单元格公式的单元格。,例：规划求解,下面通过求解长度为20的绳子围成的矩形的最大面积，介绍如何使用规划求解功能。具体操作步骤如下： 1. 向单元格A2和B2输入任意正整数，A2和B2分别存放矩形的两条边的长度。向单元格A1输入公式“=A2*B2”，A1存放矩形的面积。向单元格A3输入公式“=SUM(A2:B2)*2”，A3存放矩形的周长。如图所示。,例：