Office高级应用课件：Excel数据分析与处理

第3章

通过

Excel创建

并处理电子表格第3章通过Excel创建并处理电子表格3.1Excel制表基础3.2工作簿与多工作表操作3.3Excel公式和函数3.4在

Excel中创建图表3.5Excel数据分析与处理3.6Excel与其他程序的协同与共享23.5Excel数据分析与处理本节案例背景：Excel提供了丰富的数据处理功能。小蒋是一位中学教师，在教务处做后勤管理工作，主要负责初一年级学生的成绩管理以及学生的校外活动辅导工作，小蒋老师通过Excel来统计学生成绩及各项学生活动情况。本节将通过对学生成绩单、学生勤工俭学情况统计表、本量利分析表的统计、分析、处理，帮助小蒋老师实现以下工作目标：学会如何对分处不同工作表的数据进行合并。对数据进行各类排序操作。对数据进行自动筛选、设计条件实现高级筛选。通过分类汇总功能，对数据进行分级显示及统计。利用数据透视表和数据透视图对数据进行汇总、分析、浏览。对数据进行模拟运算与分析。第3章通过Excel创建并处理电子表格33.5Excel数据分析与处理准备工作：数据表的构建规则本节的功能全部是在正确的数据列表基础上实现的，需要重点强调一下数据列表的构建规则。数据列表一般是一个矩形区域，应与周围的非数据列表内容用空白行列分隔开，也就是说一组数据列表中没有空白的行或列。数据列表要有一个标题行，作为每列数据的标志，标题应便于理解数据的含义。标题一般不能使用纯数值，不能重复，也不能分置于两行中。数据列表中不能包括合并单元格，标题行单元格中不能插入斜线表头。每一列中的数据格式一般应该统一。第3章通过Excel创建并处理电子表格43.5.1合并计算若要汇总和报告多个单独工作表中数据的结果，可以将每个单独工作表中的数据合并到一个主工作表。所合并的工作表可以与主工作表位于同一工作簿中，也可以位于其他工作簿中。注意：参与合并计算的数据区域应满足数据列表的条件且应位于单独的工作表中，不要放置在合并后的主工作表中，同时确保参与合并计算的数据区域都具有相同的布局。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格53.5.1合并计算打开准备放置合并数据的主工作表“数据”选项卡→“数据工具”组→“合并计算”按钮在“函数”下拉框中，选择一个汇总函数在“引用位置”框中设定合并区域单击“添加”按钮，增加引用位置。可添加多个合并数据区域在“标签位置”组中指定标签所在位置3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格63.5.1合并计算提示：当所有合并计算数据所在的源区域具有完全相同的行列标签时，无需选中“标签位置”。当一个源区域中的标签与其他区域都不相同时，将会导致合并计算中出现单独的行或列。操作：打开案例工作簿文档“3.5分班级学生成绩单”，利用合并计算功能将工作表“初一（1）班”、“初一（2）班”、“初一（3）班”中数据进行“求和”合并，其中“标签位置”指定为“首行”和“最左列”。合并后的数据存放在工作表“全年级”中。可对合并后的工作表进行修改完善。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格73.5.2对数据进行排序对数据进行排序有助于快速直观地组织并查找所需数据，可以对一列或多列中的数据文本、数值、日期和时间按升序或降序的方式进行排序。还可以按自定义序列、格式（包括单元格颜色、字体颜色等）进行排序。1.快速简单排序通常情况下，参与排序的数据列表需要有标题行、且为一个连续区域，很少只单独对某一列进行排序。选择排序的数据区域→“数据”选项卡→“排序和筛选”组→“升序”或“降序”按钮。操作：打开案例工作簿文档“3.5全年级学生成绩单”，对工作表“期末成绩”中的数据按“总分”由高到低进行排序。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格83.5.2对数据进行排序2.复杂多条件排序选择要排序的数据区域→“数据”选项卡→“排序和筛选”组→“排序”按钮，打开“排序”对话框→设置“主要关键字”及“次序”→“添加条件”按钮，设置第二、第三……排序依据→“选项”按钮，可进一步设置排序条件。操作：打开案例工作簿文档“3.5全年级学生成绩单”，对工作表“期末成绩”中的数据先按“总分”由高到低进行排序，总分相同的再按语文成绩由高到低进行排序。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格93.5.2对数据进行排序3.按自定义列表进行排序只能基于数据（文本、数值以及日期或时间）创建自定义列表，而不能基于格式（单元格颜色、字体颜色等）创建自定义列表。首先，通过“文件”选项卡→“选项”→“高级”→“常规”→“编辑自定义列表”按钮创建一个自定义序列。“数据”选项卡→“排序和筛选”组→“排序”按钮→在排序条件的“次序”列表中，选择“自定义序列”。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格103.5.3从数据中筛选通过筛选功能，可以快速从数据列表查找符合条件数据或者排除不符合条件的数据。筛选条件可以是数值或文本，可以是单元格颜色，还可以根据需要构建复杂条件实现高级筛选。数据列表中的数据经过筛选后，将仅显示那些满足指定条件的行，并隐藏那些不希望显示的行。对于筛选结果可以直接复制、查找、编辑、设置格式、制作图表和打印。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格113.5.3从数据中筛选1.自动筛选使用自动筛选来筛选数据，可以快速而又方便地查找和使用数据列表中数据的子集。定位要筛选的数据列表。在“数据”选项卡→“排序和筛选”组→“筛选”按钮，进入到自动筛选状态。单击列标题中的筛选箭头，打开一个筛选器选择列表。指定筛选条件。其中选择“数字筛选”或“文本筛选”命令→“自定义筛选”命令，可自行设定筛选条件。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格123.5.3从数据中筛选操作：打开案例工作簿“3.5全年级学生成绩单”，对工作表“期末成绩”中的数据进行双重嵌套筛选：先筛选全年级总分前8名；然后筛出总分前8名中的1班学生，结果如图3.88所示。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格13图3.88自动筛选出总分前8名的1班学生共4名3.5.3从数据中筛选2.高级筛选通过构建复杂条件可以实现高级筛选。所构建的复杂条件需要放置在单独的区域中，可以为该条件区域命名以便引用。用于高级筛选的复杂条件中可以使用下列运算符比较两个值：=（等号） >（大于号）<（小于号） >=（大于等于号）<=（小于等于号） <>（不等号）3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格143.5.3从数据中筛选1）创建复杂筛选条件构建复杂条件的原则：条件区域中必须有列标题、且与包含在数据列表中的列标题一致；表示“与（and）”的多个条件应位于同一行中，意味着只有这些条件同时满足的数据才会被筛选出来；表示“或（or）”的多个条件应位于不同的行中，意味着只要满足其中的一个条件就会被筛选出来。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格153.5.3从数据中筛选为案例工作簿文档“3.5全年级学生成绩单”，构建复杂筛选条件，要求：查找1班中数学高于100分且总分高于650分的学生，以及3班中语文高于95分且总分高于620分的学生。所构建的条件如表3.5中所列3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格16表3.5

构建高级筛选条件注意：在“班级”条件列中，由于希望在单元格中显示的条件本身为“=1班”这个文本串，为了与公式输入相区别，要求在构建筛选条件时以类似“="=1班"”的方式作为字符串表达式的条件，以免产生意外的筛选结果。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格17

ABCD1班级数学语文总分2="=1班">100

>6503="=3班"

>95>6203.5.3从数据中筛选2）依据复杂条件进行高级筛选方法：在独立区域创建一组筛选条件→“数据”选项卡→“排序和筛选”组→“高级”按钮→设定筛选条件。操作：打开案例工作簿文档“3.5全年级学生成绩单（1）”，按照“筛选条件”工作表中已经事先创建好的筛选条件对工作表“期末成绩”中的数据列表进行筛选，筛选结果与数据列表存放在同一工作表中。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格183.5.3从数据中筛选3.清除筛选清除某列的筛选条件：在自动筛选列表中选择“从“××”中清除筛选”命令。清除工作表中的所有筛选条件并重新显示所有行：“数据”选项卡→“排序和筛选”组→“清除”按钮。退出自动筛选状态：在自动筛选状态下，选择“数据”选项卡→“排序和筛选”组→“筛选”按钮。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格193.5.4分类汇总与分级显示分类汇总是将数据列表中的数据先依据一定的标准分组，然后对同组数据应用分类汇总函数得到相应的统计或计算结果。分类汇总的结果可以按分组明细进行分级显示，以便于显示或隐藏每个分类汇总的明细行。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格203.5.4分类汇总与分级显示1.插入分类汇总3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格21选择数据区域对分组依据列进行排序“数据”选项卡→“分级显示”组→“分类汇总”按钮在“分类汇总”对话框中设置汇总条件：选择“分类字段”设定“汇总方式”“选定汇总项”设置汇总数据显示位置3.5.4分类汇总与分级显示操作：打开案例工作簿文档“3.5全年级学生成绩单”，利用分类汇总功能计算工作表“期末成绩”中每个班各科的平均成绩。分类汇总结果参见案例工作簿文档“3.5全年级学生成绩单（3）”。2.删除分类汇总定位分类汇总的数据区域→在“数据”选项卡上→“分级显示”组→“分类汇总”→“全部删除”按钮3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格223.5.4分类汇总与分级显示3.分级显示使用分级显示可以快速显示摘要行或摘要列，或者显示每组的明细数据。分类汇总的结果可以形成分级显示。可以为数据列表自行创建分级显示，最多可分8级。经过了分类汇总后，在数据区域的右侧出现了分级显示符号，如图3.91所示，其中数字表示分级的级数及级别，数字越大级别越小；加号表示可展开下级明细，减号表示可收缩下级明细。单击不同的分级显示符号将显示不同的级别。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格233.5.4分类汇总与分级显示图3.91工作表中的分级显示符号3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格243.5.4分类汇总与分级显示1）显示或隐藏组的明细数据2）展开或折叠特定级别的分级显示3）自行创建分级显示定位数据列表。对作为分组依据的数据列进行排序。在每组明细行的紧下方或紧上方插入带公式的汇总行，输入摘要说明和汇总公式。选择同组中的明细行或列（不包括汇总行）→“数据”选项卡→“分级显示”组→“创建组”按钮。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格253.5.4分类汇总与分级显示4）复制分级显示的数据当在分级显示的数据列表收缩部分明细后，如果只希望复制显示的内容，可进行下列设置：使用分级显示符号隐藏不需要复制的明细数据。选择要复制的数据区域。“开始”选项卡→“编辑”组→“查找和选择”按钮→“定位条件”命令→选中“可见单元格”单选项。通过“复制”→“粘贴”命令将选定的分级数据复制到其他位置，被隐藏的明细数据将不会被复制。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格263.5.4分类汇总与分级显示5）删除分级显示3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格27“数据”选项卡“分级显示”组“取消组合”按钮下方的箭头“清除分级显示”命令。3.5.5通过数据透视表分析数据数据透视表是一种可以从源数据列表中快速提取并汇总大量数据的交互式表格。用于汇总、分析、浏览数据以及呈现汇总数据，以便从不同的角度查看数据，并对相似数据进行比较。数据透视表是根据源数据列表生成的，源数据列表中每一列都成为汇总多行信息的数据透视表字段，列名称为数据透视表的字段名。该源数据区域必须具有列标题，并且该区域中没有空行。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格283.5.5通过数据透视表分析数据1.创建一个数据透视表创建数据透视表所依据的源数据列表。定位数据源区域。“插入”选项卡→“表”组→“数据透视表”按钮，打开“创建数据透视表”对话框。指定数据来源以及数据透视表存放的位置。向数据透视表中添加字段。在数据透视表中筛选字段。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格293.5.5通过数据透视表分析数据图3.95在新工作表中插入空白的透视表并显示数据透视表字段列表窗口3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格30数据透视表区字段列表区布局区域3.5.5通过数据透视表分析数据操作：打开案例工作簿文档“3.5学生勤工俭学情况统计”，为工作表“初一销售统计”中的统计数据创建一个数据透视表，透视表中只汇总各个班级每个时间段的电子产品的销售额，并放置在新工作表。提示：在字段列表中依次选中“班级”和“销售额”两个字段，再将“商品名称”字段拖动到“报表筛选”区中，将“时间”字段拖动到“列标签”区中，最后从报表筛选字段“商品名称”中选择“电子产品”。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格313.5.5通过数据透视表分析数据2.对数据透视表进行更新和维护在数据透视表中的任意单元格中单击，功能区中将会出现“数据透视表工具”的“选项”和“设计”两个选项卡，在如图3.97所示的“选项”选项卡下可以对数据透视表中进行多项操作。1）刷新数据透视表2）更改数据源图3.97“数据透视表工具”的“选项”选项卡3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格323.5.5通过数据透视表分析数据3.设置数据透视表的格式可以像对普通表格那样对数据透视表进行格式设置；还可通过如图3.99所示“数据透视表工具”的“设计”选项卡为数据透视表快速指定预置样式。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格33图3.99“数据透视表工具”的“设计”选项卡3.5.5通过数据透视表分析数据4.创建一个数据透视图数据透视图以图形形式呈现数据透视表中的汇总数据，其作用与普通图表一样，可以更为形象化地对数据进行比较、反映趋势。为数据透视图提供源数据的是相关联的数据透视表，在透视表中所做的更改，会立即反映在数据透视图中。除了数据源来自数据透视表以外，数据透视图与标准图表组成元素基本相同，与普通图表的区别在于，当创建数据透视图时，数据透视图的图表区中将显示字段筛选器，以便对基本数据进行排序和筛选。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格343.5.5通过数据透视表分析数据①在数据透视表中单击。②“数据透视表工具”的“选项”选项卡→“工具”组→“数据透视图”按钮→“插入图表”对话框。③选择相应的图表类型和图表子类型。④通过图表区中的字段筛选器，可更改图表中显示的数据。⑤在数据透视图中单击，功能区出现“数据透视图工具”中的“设计”、“布局”、“格式”和“分析”四个选项卡。通过这四个选项卡，可以对透视图进行修饰和设置，3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格353.5.5通过数据透视表分析数据提示：在数据透视图中，可以使用除XY散点图、气泡图或股价图以外的任意图表类型。操作：打开案例工作簿文档“3.5学生勤工俭学情况统计（1）”，为工作表“数据透视”中的数据表区域创建一个柱形图中的“堆积柱形图。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格363.5.5通过数据透视表分析数据5.删除数据透视表或数据透视图删除数据透视表定位数据透视表→“数据透视表工具”的“选项”选项卡→“操作”组→“选择”按钮→“整个数据透视表”命令→按Delete键。删除数据透视图在要删除的数据透视图中的任意空白位置单击，然后按Delete键。删除数据透视图不会删除相关联的数据透视表。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格373.5.6模拟分析和运算模拟分析是指通过更改某个单元格中数值来查看这些更改对工作表中引用该单元格的公式结果的影响的过程。通过使用模拟分析工具，可以在一个或多个公式中试用不同的几组值来分析所有不同的结果。Excel附带了三种模拟分析工具：方案管理器、模拟运算表和单变量求解。方案管理器和模拟运算表可获取一组输入值并确定可能的结果。单变量求解则是针对希望获取结果的确定生成该结果的可能的各项值。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格383.5.6模拟分析和运算1.单变量求解单变量求解用来解决以下问题：先假定一个公式的计算结果是某个固定值，当其中引用的变量所在单元格应取值为多少时该结果才成立。输入基础数据→“数据”选项卡→“数据工具”组→“模拟分析”按钮→“单变量求解”命令→设置用于单变量求解的各项参数。参数含义：公式所在的单元格为“目标单元格”，其所引用的变量所在单元格为“可变单元格”，希望达到的计算结果为“目标值”。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格393.5.6模拟分析和运算操作：打开案例工作簿文档“3.5本量利分析”，在“单变量求解”工作表中输入如图3.101中所示的基础数据，其中C7单元格输入的是利润的求解公式：“利润=（单价-成本）×销量”。进行单变量求解：试测算当利润为15000时，销量是多少。其中含有利润公式的C7为“目标单元格”，含有变量销量值的C6作为“可变单元格”。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格403.5.6模拟分析和运算图3.101输入单变量求解的基础数据3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格413.5.6模拟分析和运算2.模拟运算表的创建和应用模拟运算表的结果显示在一个单元格区域中，它可以测算将某个公式中一个或两个变量替换成不同值时对公式计算结果的影响。模拟运算表最多可以处理两个变量，但可以获取与这些变量相关的众多不同的值。模拟运算表依据处理变量个数的不同，分为单变量模拟运算表和双变量模拟运算表两种类型。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格423.5.6模拟分析和运算1)单变量模拟运算表若要测试公式中一个变量的不同取值如何改变这些公式的结果，可使用单变量模拟运算表。在单列或单行中输入变量值后，不同的计算结果便会在公式所在的列或行中显示。输入基础数据与公式→选择创建模拟运算表的单元格区域→“数据”选项卡→“数据工具”组→“模拟分析”按钮→“模拟运算表”命令，→指定变量值所在的单元格→在模拟运算表中输入不同的变量值进行结果测试。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格433.5.6模拟分析和运算操作：打开案例工作簿文档“3.5本量利分析”，在“模拟运算”工作表中输入如图3.104所示的基础数据与公式，其中D4单元格中输入的是利润的求解公式：“利润=（单价-成本）×销量”。对本量利进行单变量模拟运算，目的是测算不同单价下利润值的变化情况。其中A4单元格中的单价为变量值，不同的单价将会在A列中输入。D4单元格是引用了该变量的利润计算公式，3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格443.5.6模拟分析和运算图3.104输入单变量模拟运算表的基础数据3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格453.5.6模拟分析和运算2）双变量模拟运算表若要测试公式中两个变量的不同取值如何改变这些公式的结果，可使用双变量模拟运算表。在单列和单行中分别输入两个变量值后，计算结果便会在公式所在区域中显示。输入基础数据与公式→输入变量值→选择创建模拟运算表的单元格区域→“数据”选项卡→“数据工具”组→“模拟分析”按钮→“模拟运算表”命令→指定变量值所在的单元格→在模拟运算表中更改单价或销量，测算对应的利润值。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格463.5.6模拟分析和运算操作：打开案例工作簿文档“3.5本量利分析”，在“模拟运算”工作表中输入如图3.106所示的基础数据与公式，其中G6单元格中输入的是利润的求解公式：“利润=（单价-成本）×销量”，G3单元格中为单价变量、G5单元格中为销量变量。对本量利进行双变量模拟运算，目的是测算不同单价、不同销量下利润值的变化情况：从H6单元格开始沿行输入销量值；从G7单元格开始沿列输入单价值。G6单元格是引用了两个变量的利润计算公式。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格473.5.6模拟分析和运算图3.106输入双变量模拟运算表3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格483.5.6模拟分析和运算3.使用方案管理器如果要分析两个以上的变量，应使用方案管理器。一个方案最多获取32个不同的值，但是却可以创建任意数量的方案。方案管理器作为一种分析工具，每个方案允许建立一组假设条件、自动产生多种结果，并可以直观地看到每个结果的显示过程，还可以将多种结果存放到一个工作表中进行比较。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格493.5.6模拟分析和运算1）建立分析方案由于电子产品的成本上涨了10%，导致利润下降，为了抵消成本上涨带来的影响，小蒋老师拟采取两种措施：第一种，提高单价5%，因此导致销量减少10%；另一种，是降低单价3%，这使得销量增加15%。表3.6中显示了上述三种不同的方案情况。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格50表3.6建立三种不同的方案3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格51项

目方案1方案2方案3单价增长率0.00%8.00%-3.00%成本增长率10.00%10.00%10.00%销量增长率0.00%-5.00%20.00%3.5.6模拟分析和运算目标：通过方案管理器来建立分析方案，帮助小蒋老师分析一下价量不变、提价、降价这三种方案对利润额的影响。方法：输入基础数据与公式→“数据”选项卡→“数据工具”组→“模拟分析”→“方案管理器”命令→“添加”按钮→在“添加方案”对话框中输入方案名、设置可变单元格区域→在“方案变量值”对话框中输入方案的变量值→继续添加其他方案。3.5Excel数据分析与处理第3章通过Excel创建并处理电子表格523.5.6模拟分析和运算操作：打开案例工作簿“3.5本量利分析”，在“方案管理”工作表中输入如图3.108所示的基础数据与公式。说明：D5、D6、D7三个