第9章Excel数据管理与分析ppt课件.pptx

在此输入您的封面副标题第9章Excel数据管理与分析数据排序数据筛选分类汇总数据透视表合并计算模拟分析231、按关键字排序单一关键字根据单个关键字对数据进行排序的具体操作方法：首先单击参与排序的列的某个单元格，然后单击“数据”选项卡“排序和筛选”组“升序”按钮 或“降序”按钮 ，即可快速地按所选列数值升序或降序排序。多关键字首先单击参与排序的数据区域中的任意单元格，单击“数据”选项卡“排序和筛选”组“排序”按钮，弹出“排序”对话框，然后选择“主关键字”，在“排序的依据”、“次序”下拉列表中进行相应设置即可。如果一个关键字无法解决排序中的先后问题，可以根据需要添加“次要关键字”作为排序的第二个、第三个、依据，每个次要关键字根据需要设置“排序的依据”和“次序”项。42、按自定义序列排序在Excel中，当按照关键字的升序或降序排列无法满足用户需求时，用户还可以按照自定义的序列顺序进行数据排序。具体操作方法：首先创建一个自定义序列，然后选择参与排序的数据，接着打开“排序”对话框，在“次序”下拉列表中选择“自定义序列”，打开“自定义序列”对话框，从中选择已经创建的自定义序列，即可按照用户自定义的序列顺序进行排序。例：在如图数据中费用类别按交通费、通讯费、餐饮费、住宿费、其他的顺序排列。51、自动筛选首先单击数据清单中的任意单元格，然后单击“数据”选项卡“排序和筛选”组“筛选”按钮 ，当前数据清单中的每个字段名（即列标题）右侧会出现一个筛选箭头 ，最后单击筛选箭头，在弹出的筛选下拉列表根据需要进行设置即可。其中，筛选下拉列表显示的内容与当前列的数据格式有关。当数据格式为文本时，筛选下拉列表将显示“文本筛选”命令，如图所示，当数据格式为数值时，筛选下拉列表将显示“数字筛选”命令。61、自动筛选此外，如果需要设定筛选条件，那么可以单击“数字筛选”命令或“文本筛选”命令，在其子菜单中选择“自定义筛选”命令，弹出“自定义自动筛选方式”对话框，如图所示，在其中设置筛选条件即可。72、高级筛选当筛选条件比较复杂，自动筛选无法实现用户的筛选要求时，可以通过高级筛选来指定筛选条件以实现复杂条件的数据筛选。具体操作方法：首先构建筛选条件，然后单击数据清单中的任意单元格，接着单击“数据”选项卡“排序和筛选”组“高级”按钮，弹出“高级筛选”对话框，如右上图所示，进行相应设置即可。筛选条件的构建规则：条件区域的第一行必须是字段名，且与原数据清单的字段名保持一致；需要同时满足的条件写在条件区域的同一行，不需要同时满足的条件写在不同行，如右下图所示，左图表示不需要同时满足的条件，平时成绩大于90分或期中成绩大于90分，右图则表示需要同时满足的条件，平时成绩大于90分并且期中成绩大于90分。81、建立分类汇总建立分类汇总具体操作方法：首先选择需要进行分类汇总的数据区域，按照分类字段进行排序，然后单击“数据”选项卡“分组显示”组“分类汇总”按钮，弹出“分类汇总”对话框，如图所示，分别对“分类字段”、“汇总方式”、“选定汇总项”等进行相应设置，最后单击“确定”按钮。注意：建立分类汇总前，应该先以分类字段作为主关键字对数据清单中的数据进行排序。2、删除分类汇总删除分类汇总具体操作方法：首先单击已建立分类汇总的数据区域任意单元格，然后单击“数据”选项卡“分组显示”组“分类汇总”按钮，在弹出的“分类汇总”对话框中单击“全部删除”按钮。9例：在如图所示的原数据中汇总出各费用类别的差旅费用金额总额。103、数据分级显示自行创建分级显示在Excel中分类汇总后的数据可以自动形成分级显示，普通数据也可以通过分级显示功能创建数据分级显示。普通数据创建分级显示的具体操作方法：首先单击需要建立分级显示的数据清单任意单元格，按照分组依据字段为主关键字进行排序，然后选择创建组的行或列，单击“数据”选项卡“分组显示”组“创建组”按钮，弹出“创建组”对话框，如图所示，接着单击“确定”按钮，即可按照所选行或列创建组。以同样的方式依次创建其他组，数据可实现分级显示。分类汇总或创建分级显示后的工作表最左侧会出现分级显示符号 、显示明细数据按钮 和隐藏明细数据按钮 。113、数据分级显示显示或隐藏明细数据已创建分级显示的数据可以单击工作表最左侧的显示明细数据按钮和隐藏明细数据按钮，显示或隐藏明细数据。单击分级显示符号中某一级别的编号，处于较低级别的明细数据将被隐藏，单击最低级别的分级显示符号，所有明细数据都会显示。清除分级显示清除分级显示的具体操作方法：首先单击已建立分级显示的数据区域任意单元格，然后单击“数据”选项卡“分组显示”组“取消组合”下拉列表中的“清除分级显示”命令。121、创建数据透视表创建数据透视表的具体操作步骤：步骤1：首先单击需要创建数据透视表的数据区域任意单元格，然后单击“插入”选项卡“表格”组“数据透视表”按钮，弹出“创建数据透视表”对话框，如图所示，接着在“选择一个表或区域”中指定数据源区域、在“选择放置数据透视表的位置”中选择数据透视表生成新工作表还是在现有工作表中插入。13步骤2：单击“确定”按钮，即可在指定位置插入空的数据透视表，如图所示。步骤3：为空白数据透视表添加字段，可以直接将字段列表中的字段拖动到布局区域的指定区域（筛选器、列、行、值），如图所示；也可以在字段列表的字段名上单击右键，在弹出的快捷菜单中选择相应的命令。14步骤4：删除已添加的字段，只需要单击取消已添加字段的字段名前的复选框选择即可，如图所示。步骤5：对已添加的字段进行筛选，单击已添加字段右侧的筛选箭头，对当前字段的数据进行筛选。152、数据透视表工具更改数据源如果数据透视表的数据源不完全正确，可以通过更改数据源功能来更改数据区域，具体操作方法：首先单击数据透视表中任意单元格，然后单击“数据透视表工具|分析”选项卡“数据”组“更改数据源”按钮，弹出“更改数据透视表数据源”对话框，如图所示，接着重新选择或输入新的数据区域即可。16更新数据透视表数据透视表创建后，如果数据源中的数据进行了更改，可以通过刷新功能更新数据透视表的显示，具体操作方法：首先单击数据透视表中任意单元格，然后单击“数据透视表工具|分析”选项卡“数据”组“刷新”按钮即可。设置活动字段数据透视表创建后，可以对活动字段进行相应设置。方法一：首先单击数据透视表中任意单元格，然后单击“数据透视表工具|分析”选项卡“活动字段”组“字段设置”按钮，弹出“值字段设置”对话框，如图所示。17设置活动字段方法二：双击数据透视表中的活动字段即可弹出“值字段设置”对话框。（1）修改活动字段名称：“值字段设置”对话框中，在“自定义名称”文本框输入新的字段名即可。（2）修改值汇总方式：“值字段设置”对话框中，在“值汇总方式”选项卡可以选择值汇总的计算类型为求和、计数、求平均值、最大值、最小值等。（3）修改值显示方式：“值字段设置”对话框中，在“值显示方式”选项卡可以设置字段的值显示方式为总计的百分比、列汇总的百分比、行汇总的百分比、百分比、父行汇总的百分比等，如图所示。18数据透视表选项数据透视表创建后，可以对其名称、布局、汇总和筛选等选项进行修改。具体操作方法：单击“数据透视表工具|分析”选项卡“数据透视表”组“选项”按钮，弹出“数据透视表选项”对话框，可以对数据透视表名称、布局、格式、汇总和筛选等进行设置。设置数据透视表格式数据透视表创建后，可以对其报表布局、数据透视表样式等外观格式进行设置。具体操作方法：单击“数据透视表工具|设计”选项卡，选择相应的格式应用到数据透视表即可。193、数据透视图数据透视图是通过图形形式对数据透视表中的汇总数据进行描述，与Excel图表类似，数据透视图是数据透视表数据更为直观形象的呈现。创建数据透视图的具体操作方法：首先单击数据透视表中任意单元格，然后单击“数据透视表工具|分析”选项卡“工具”组“数据透视图”按钮，弹出“插入图表”对话框，接着选择需要的图表类型，单击“确定”按钮即可插入数据透视图。204、删除数据透视表和数据透视图删除数据透视表的具体操作方法：首先单击数据透视表，然后单击“数据透视表工具|分析”选项卡“操作”组“选择”下拉列表中的“整个数据透视表”命令，最后按Delete键。数据透视表删除后，与之关联的数据透视图将变为普通图表。删除数据透视图的具体操作方法：首先单击数据透视图，然后按Delete键。21Excel中的合并计算可以将多张工作表的数据汇总到指定工作表中，进行合并的工作表与合并后的目标工作表可以在同一工作簿，也可以在不同工作簿。合并计算的具体操作步骤：（1）首先单击合并后工作表中的开始单元格，然后单击“数据”选项卡“数据工具”组“合并计算”按钮，弹出“合并计算”对话框，如图所示。 （2）在“函数”下拉列表框中选择合并计算的方式：求和、计数、平均值、最大值、最小值等。（3）在“引用位置”选择需要合并的区域，然后单击“添加”按钮，合并区域就会添加到“所有引用位置”列表框中，如此依次添加多个需要合并的区域，如图所示。（4）在“标签位置”组中根据需要对标签所在位置“首行”、“最左列”和“创建指向源数据的链接”进行选择。（5）单击“确定”按钮，完成数据合并。22Excel模拟分析是通过更改单元格中的值来了解这些更改会如何影响引用这些单元格的公式结果的过程。1、单变量求解单变量求解的模拟分析是利用公式的计算结果去分析确定可能产生此结果的公式中某变量的输入值。单变量求解的具体操作方法：首先单击公式所在的目标单元格，然后单击“数据”选项卡“预测”组“模拟分析”下拉列表的“单变量求解”命令，弹出“单变量求解”对话框，如图所示，设置“目标单元格”（显示目标值的单元格）、“目标值”（期望达到的值）和“可变单元格”（能够达到目标值的可变量所在单元格）的值，接着单击“确定”按钮，弹出“单变量求解状态”对话框，最后单击“单变量求解状态”对话框中的“确定”按钮。232、模拟运算表单变量模拟运算表单变量模拟运算表的具体操作方法：首先输入数据与公式，然后选择需要创建模拟运算表的数据区域，然后单击“数据”选项卡“预测”组“模拟分析”下拉列表的“模拟运算表”命令，弹出“模拟运算表”对话框，接着选择变量所在单元格，如果变量在一行，则在“输入引用行的单元格”中选择行变量公式所在单元格，如果变量在一列，则在“输入引用列的单元格”中选择列变量公式所在单元格，最后单击“确定”按钮，即可在选定区域生成模拟运算表。双变量模拟运算表具体操作步骤：（1）首先输入数据与公式，然后输入变量，在公式所在行输入一个变量的系列值，公式所在列输入另一个变量的系列值。（2）首先选择需要创建模拟运算表的数据区域，然后单击“数据”选项卡“预测”组“模拟分析”下拉列表的“模拟运算表”命令，弹出“模拟运算表”对话框。（3）选择变量所在单元格，在“输入引用行的单元格”中选择行变量公式所在单元格，在“输入引用列的单元格”中选择列变量公式所在单元格，最后单击“确定”按钮，即可在选定区域生成模拟运算表。243、方案管理器模拟运算表最多只能进行两个变量的模拟运算，如果需要对两个以上的变量进行模拟运算，那么应该使用方案管理器。创建方案显示方案编辑方案删除方案