搜索
第7章数据的汇总、合并与透视《EXCEL数据处理》电子教案中国水利水电出版社2【本章知识点】利用SUMIF或组合SUM和IF条件汇总利用SUMPRODUCT实现多条件汇总利用DSUM函数进行数据库表格汇总数据库表格的分类汇总操作一些特定情形下的分类汇总操作利用SUM函数进行多工作表数据合并按位置和按分类进行的两种合并计算数据透视表的建立、编辑与显示设置数据透视图和数据透视报告的制作方法《EXCEL数据处理》电子教案中国水利水电出版社3本章目录7.1利用相关函数进行数据汇总7.2数据库表格的分类汇总7.3特定情形下的数据汇总方法7.4数据的多表合并7.5数据的透视分析小结习题《EXCEL数据处理》电子教案中国水利水电出版社47.1利用相关函数进行数据汇总在Excel中进行数据处理时，数据汇总是最频繁的操作之一。通过SUM函数对数据进行无条件汇总是读者最好理解的一种方法，这在前面“函数及其应用”一章已经作过介绍。但是，仅仅只是掌握一个SUM函数，在进行数据汇总时还远远不够。本节介绍其它几个用来进行满足一定条件的数据汇总方式，包括SUMIF函数、SUM函数与IF函数组合应用、SUMPRODUCT函数和DSUM函数。它们各有相应的应用场合，读者应灵活应用。《EXCEL数据处理》电子教案中国水利水电出版社57.1.1利用SUMIF函数实现单条件汇总SUMIF函数的功能是根据指定条件对若干单元格求和，其语法格式如下：SUMIF（条件判断区域，条件，求和区域）《EXCEL数据处理》电子教案中国水利水电出版社67.1.2SUM函数和IF函数联合实现多条件汇总SUMIF函数只能根据一个条件进行求和，如果要实现对两个以上的条件求和，可以联合使用SUM函数和IF函数来实现。《EXCEL数据处理》电子教案中国水利水电出版社77.1.3利用SUMPRODUCT函数实现多条件汇总SUMPRODUCT函数的功能是计算几个数组之间对应元素乘积之和，语法格式如下：SUMPRODUCT（数组１，数组２，数组３，……）可以看出，该函数的数组参数个数不定，但是使用时一定要注意各个数组的维数必须相同，否则SUMPRODUCT函数将返回错误值“#VALUE”。《EXCEL数据处理》电子教案中国水利水电出版社87.1.4利用DSUM函数进行数据库表格多条件汇总Excel中为数据处理提供了12个数据库函数，这些函数都以D开头，也称为D函数。其中DSUM函数的功能就是对数据库表格进行多条件汇总。其语法格式如下：DSUM(database,field,criteria)其中：database为构成数据库的单元格区域；field是database区域中某列数据的列标题，它可以是文本，即两端带引号的标志项（如“使用年数”或“产量”），也可以是代表列表中数据列位置的数字：1表示第一列，2表示第二列……；criteria称为条件区域，它与高级筛选的条件区域的含义一样，关于其构造方法，请读者查看高级筛选相关介绍。DSUM函数应用的具体实例参见下页《EXCEL数据处理》电子教案中国水利水电出版社9《EXCEL数据处理》电子教案中国水利水电出版社107.2数据库表格的分类汇总7.2.1分类汇总的基本知识分类汇总是在Excel数据库表格或者数据清单中快捷地汇总数据的方法，通过分级显示和分类汇总，可以从大量的数据信息中按照某些特殊的需要提出有用的信息。总之，分类汇总为汇总数据提供了非常灵活有用的方式，它主要可以实现以下功能：（1）在数据库表格上显示一组数据的分类汇总及全体总和。（2）在数据库表格上显示多组数据的分类汇总及全体总和。（3）在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）、求总体方差等。《EXCEL数据处理》电子教案中国水利水电出版社117.2.2分类汇总的建立在执行分类汇总命令之前，首先应该对数据库进行排序，将数据库中关键字相同的一些记录集中到一起。当对数据库排序之后，就可以对数据库进行数据分类汇总。运行“数据”／“分类汇总”命令，出现图7-7所示“分类汇总”对话框，然后进行分类汇总的设置操作。《EXCEL数据处理》电子教案中国水利水电出版社127.2.3多重分类汇总的操作上面介绍的分类汇总仅仅只是按照“品名”关键字对“合计金额”进行了一种汇总运算。实际上。在同一分类汇总级上可能不只进行一种汇总运算。为此，Excel还提供了对同一分类进行多重汇总的功能，这就是所谓的多重分类汇总。若要在同一汇总表中显示两个以上的汇总数据时，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行即可。说明：每增加一次分类汇总，汇总结果左侧按钮就会增加一层，如上图已为4层。《EXCEL数据处理》电子教案中国水利水电出版社137.2.4嵌套分类汇总的操作所谓嵌套分类汇总，就是在一个已经按照某一个关键字建立好分类汇总的汇总表中，再按照另一个关键字进行另一种分类汇总，这里要求的是两次的分类汇总关键字不同。建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余的以此类推。在进行嵌套分类汇总时，有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果是操作，其余的以此类推。说明：嵌套分类汇总与多重分类汇总的相同点在于二者都需要进行多次的分类汇总操作；区别在于后者每次的汇总关键字都相同，而前者每次的分类汇总关键字却不一样。《EXCEL数据处理》电子教案中国水利水电出版社147.3特定情形下的数据汇总方法7.3.1数据累加汇总处理将一列数据从上往下（或者自左向右）进行累加汇总，是数据管理中一种常见的操作。该问题解决可以使用SUM函数，但是在单元格区域的引用中第一个单元格应为绝对引用，而第二个单元格地址应为相对引用。《EXCEL数据处理》电子教案中国水利水电出版社157.3.2动态更新区域的数据汇总在实际工作中，有时需要对一个不断更新的数据源进行动态数据汇总，此时可以考虑使用SUMIF函数。《EXCEL数据处理》电子教案中国水利水电出版社167.3.3不连续区域的数据汇总《EXCEL数据处理》电子教案中国水利水电出版社177.3.4数据区域中前若干个最大（小）数值的汇总在数据处理中，有时需要计算数据区域中前若干个最大（小）数值之和。《EXCEL数据处理》电子教案中国水利水电出版社187.3.5对含有错误值的单元格区域进行汇总当利用汇总函数对进行数据汇总时，如果单元格中存在错误值，则函数或者公式的结果将会出现错误。对于这类问题，需要使用错误判断函数IFERROR进行判断，并利用数组公式生成一个不含有错误值的数组，然后才能得到正确的结果。《EXCEL数据处理》电子教案中国水利水电出版社197.4数据的多表合并在实际数据处理中，有时数据被存放到不同的工作表中，这些工作表可在同一个工作簿中，也可来源于不同的工作簿，它们格式基本相同，只是由于所表示的数据因为时间、部门、地点、使用者的不同而进行了分类；但到一定时间，还需要对这些数据表进行合并，将合并结果放到某一个主工作簿的主工作表中。例如，一家企业集团，开始时为了分散管理的方便，分别将各个子公司的销售信息存放到了不同的工作簿中，最后年终可以采用多表合并的方式把各个工作簿中的信息再合并到一个主工作簿中。说明：Excel支持将不多于255个工作表中的信息收集到一个主工作表中。《EXCEL数据处理》电子教案中国水利水电出版社20图7-20表示的就是一个合并计算的例子，注意其中参与合并计算的3个工作表可以分别来源于不同的工作簿，也可以是一个工作簿中的不同的3个工作表。《EXCEL数据处理》电子教案中国水利水电出版社217.4.1利用SUM函数实现多表数据合并进行多表数据合并，最好理解的方法就是通过公式的“三维应用”来实现，也就是同过使用SUM等函数，将来自不同工作表乃至其它不同工作簿的数据进行跨表格引用。《EXCEL数据处理》电子教案中国水利水电出版社227.4.2按位置进行合并计算如果所有需要合并的各个工作表源区域中的数据按同样的顺序和位置排列（例如，数据来自同一模板创建的一系列工作表），则可按位置进行合并计算。注意：按位置进行合并计算时，各工作表中行标题和列标题的位置和顺序必须一致。《EXCEL数据处理》电子教案中国水利水电出版社237.4.3按分类进行合并计算如果需要合并的各个工作表中的数据区域具有相同的行标题或列标题，但是它们是以不同的方式组织的（比如位置不同或者顺序不同），则可按分类进行合并计算。《EXCEL数据处理》电子教案中国水利水电出版社247.5数据的透视分析数据透视分析就是从数据库的特定字段中概括信息，从而方便从各个角度查看、分析数据，并可对数据库中的数据进行汇总统计，它在Excel中的实现工具是数据透视表（图）。数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数据透视图是一种能够根据数据处理需要，查看部分数据的图表对比效果，有些类似前面介绍的动态图表功能，另外，Excel中还可以根据数据透视表制作不同格式的数据透视报告。《EXCEL数据处理》电子教案中国水利水电出版社257.5.1数据透视表的创建选择“数据”／“数据透视表和数据透视图”，然后按操作即可。《EXCEL数据处理》电子教案中国水利水电出版社267.5.2创建数据透视表时的相关操作(1)“布局”对话框的使用《EXCEL数据处理》电子教案中国水利水电出版社277.5.2创建数据透视表时的相关操作(2)汇总方式的修改《EXCEL数据处理》电子教案中国水利水电出版社287.5.2创建数据透视表时的相关操作(3)使用透视表筛选数据《EXCEL数据处理》电子教案中国水利水电出版社297.5.3创建数据透视图创建数据透视图与创建数据透视表一样，也是按上面的向导操作，关键是第一步要选“数据透视图”；如果数据透视表已制作好，要制作与之对应的数据透视图，只要在数据透视表上单击右键，从快捷菜单上单击“数据透视图”即可。《EXCEL数据处理》电子教案中国水利水电出版社307.5.4编辑数据透视表对于制作好的数据透视表，有时还需要进行编辑操作。编辑数据透视表包括对其布局的修改、格式化表中的数据、透视表的复制等。创建数据透视表后，Excel自动打开如图7-42所示的“数据透视表”工具栏以及“数据透视表字段列表”对话框，可通过使用“数据透视表”工具栏，并借助于“数据透视表字段列表”对话框，对数据透视表进行修改。《EXCEL数据处理》电子教案中国水利水电出版社317.5.5修改数据透视表的布局(1)行、列字段互换。(2)在字段内移动数据项(3)隐藏和显示数据(4)添加字段(5)删除字段(6)设置数据透视表选项《EXCEL数据处理》电子教案中国水利水电出版社327.5.6新数据透视表中数据显示形式的修改在数据透视表中，数据的默认显示形式是以数值形式显示，但这不是固定不变的，可以修改数据透视表中数据的显示形式，如显示为小数、百分数或其他需要的形式。《EXCEL数据处理》电子教案中国水利水电出版社337.5.7数据透视表的数据更新数据透视表的数据来源于数据源，不能在透视表中直接修改；即使源数据库中的数据被修改了，透视表中的数据也不会自动更新，必须执行更新数据操作才能刷新，数据透视图也是这样。这一点与Excel中图表制作时候，数据变动会引起对应图表同步改变不同。需要更新数据透视表时，有以下三种方法可用：（1）在“数据”菜单中选择“更新数据”；（2）在数据区域的快捷菜单中选择“更新数据”；（3）在“数据透视表”工具栏上选择“更新数据”按钮。《EXCEL数据处理》电子教案中国水利水电出版社347.5.8删除数据透视表如果不再想要数据透视表，可以将其删除。操作步骤如下：(1)单击要删除的数据透视表中的任意一个单元格。(2)单击“数据透视表”工具栏