《CL合并计算实例.ppt》由会员分享，可在线阅读，更多相关《CL合并计算实例.ppt（37页珍藏版）》请在金锄头文库上搜索。
1、第第 7 7 章章数据的汇总、合并与透视数据的汇总、合并与透视【本章知识点】n n利用利用利用利用SUMIFSUMIF或组合或组合或组合或组合SUMSUM和和和和IFIF条件汇总条件汇总条件汇总条件汇总n n利用利用利用利用SUMPRODUCTSUMPRODUCT实现多条件汇总实现多条件汇总实现多条件汇总实现多条件汇总n n利用利用利用利用DSUMDSUM函数进行数据库表格汇总函数进行数据库表格汇总函数进行数据库表格汇总函数进行数据库表格汇总n n数据库表格的分类汇总操作数据库表格的分类汇总操作数据库表格的分类汇总操作数据库表格的分类汇总操作n n一些特定情形下的分类汇总操作一些特定情形下的分
2、类汇总操作一些特定情形下的分类汇总操作一些特定情形下的分类汇总操作n n利用利用利用利用SUMSUM函数进行多工作表数据合并函数进行多工作表数据合并函数进行多工作表数据合并函数进行多工作表数据合并n n按位置和按分类进行的两种合并计算按位置和按分类进行的两种合并计算按位置和按分类进行的两种合并计算按位置和按分类进行的两种合并计算n n数据透视表的建立、编辑与显示设置数据透视表的建立、编辑与显示设置数据透视表的建立、编辑与显示设置数据透视表的建立、编辑与显示设置n n数据透视图和数据透视报告的制作方法数据透视图和数据透视报告的制作方法数据透视图和数据透视报告的制作方法数据透视图和数据透视报告的制
3、作方法EXCEL数据处理电子教案2中国水利水电出版社本章目录7.1利用相关函数进行数据汇总7.2数据库表格的分类汇总7.3特定情形下的数据汇总方法7.4数据的多表合并7.5 数据的透视分析小结习题EXCEL数据处理电子教案3中国水利水电出版社7.1利用相关函数进行数据汇总n在Excel中进行数据处理时，数据汇总是最频繁的操作之一。通过SUM函数对数据进行无条件汇总是读者最好理解的一种方法，这在前面“函数及其应用”一章已经作过介绍。但是，仅仅只是掌握一个SUM函数，在进行数据汇总时 还远远不够。n本节介绍其它几个用来进行满足一定条件的数据汇总方式，包括SUMIF函数、SUM函数与IF函数组合应用
4、、SUMPRODUCT函数和DSUM函数。它们各有相应的应用场合，读者应灵活应用。EXCEL数据处理电子教案4中国水利水电出版社7.1.1利用SUMIF函数实现单条件汇总SUMIF函数的功能是根据指定条件对若干单元格求和，其语法格式如下：SUMIF（条件判断区域，条件，求和区域）（条件判断区域，条件，求和区域）EXCEL数据处理电子教案5中国水利水电出版社7.1.2 SUM函数和IF函数联合实现多条件汇总nSUMIF函数只能根据一个条件进行求和，如果要实现对两个以上的条件求和，可以联合使用SUM函数和IF函数来实现。EXCEL数据处理电子教案6中国水利水电出版社7.1.3 利用SUMPRODU
5、CT函数实现多条件汇总nSUMPRODUCT函数的功能是计算几个数组之间对应元素乘积之和，语法格式如下：nSUMPRODUCT（数组，（数组，数组，数组，数组，数组，）n可以看出，该函数的数组参数个数不定，但是使用时一定要注意各个数组的维数必须相同，否则SUMPRODUCT函数将返回错误值“#VALUE”。EXCEL数据处理电子教案7中国水利水电出版社7.1.4 利用DSUM函数进行数据库表格多条件汇总nExcel中为数据处理提供了12个数据库函数，这些函数都以D开头，也称为D函数。其中DSUM函数的功能就是对数据库表格进行多条件汇总。其语法格式如下：DSUM(database,field,c
6、riteria)n其中： database 为构成数据库的单元格区域； field 是database区域中某列数据的列标题，它可以是文本，即两端带引号的标志项（如“使用年数”或“产量”），也可以是代表列表中数据列位置的数字：1 表示第一列，2 表示第二列；criteria称为条件区域，它与高级筛选的条件区域的含义一样，关于其构造方法，请读者查看高级筛选相关介绍。nDSUM函数应用的具体实例参见下页EXCEL数据处理电子教案8中国水利水电出版社EXCEL数据处理电子教案9中国水利水电出版社7.2数据库表格的分类汇总7.2.17.2.1分类汇总的基本知识分类汇总的基本知识分类汇总的基本知识分类汇
7、总的基本知识n分类汇总是在Excel数据库表格或者数据清单中快捷地汇总数据的方法，通过分级显示和分类汇总，可以从大量的数据信息中按照某些特殊的需要提出有用的信息。n总之，分类汇总为汇总数据提供了非常灵活有用的方式，它主要可以实现以下功能：（1）在数据库表格上显示一组数据的分类汇总及全体总和。（2）在数据库表格上显示多组数据的分类汇总及全体总和。（3）在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）、求总体方差等。EXCEL数据处理电子教案10中国水利水电出版社7.2.2分类汇总的建立n在执行分类汇总命令之前，首先应该对数据库进行排序，将数据库中关键字相同的一些记录集中到
8、一起。当对数据库排序之后，就可以对数据库进行数据分类汇总。n运行“数据” “分类汇总”命令，出现图7-7所示 “分类汇总”对话框，然后进行分类汇总的设置操作。 EXCEL数据处理电子教案11中国水利水电出版社7.2.3多重分类汇总的操作n上面介绍的分类汇总仅仅只是按照“品名”关键字对“合计金额”进行了一种汇总运算。实际上。在同一分类汇总级上可能不只进行一种汇总运算。n为此，Excel还提供了对同一分类进行多重汇总的功能，这就是所谓的多重分类汇总。n若要在同一汇总表中显示两个以上的汇总数据时，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行即可。n说明：说明：每增
9、加一次分类汇总，汇总结果左侧按钮就会增加一层，如上图已为4层。EXCEL数据处理电子教案12中国水利水电出版社7.2.4嵌套分类汇总的操作n所谓嵌套分类汇总，就是在一个已经按照某一个关键字建立好分类汇总的汇总表中，再按照另一个关键字进行另一种分类汇总，这里要求的是两次的分类汇总关键字不同。n建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余的以此类推。n在进行嵌套分类汇总时，有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果是操作，其余的以此类推。说明：说明：嵌
10、套分类汇总与多重分类汇总的相同点在于二者都需要进行多次的分类汇总操作；区别在于后者每次的汇总关键字都相同，而前者每次的分类汇总关键字却不一样。EXCEL数据处理电子教案13中国水利水电出版社7.3特定情形下的数据汇总方法7.3.1 数据累加汇总处理n将一列数据从上往下（或者自左向右）进行累加汇总，是数据管理中一种常见的操作。n该问题解决可以使用SUM函数，但是在单元格区域的引用中第一个单元格应为绝对引用，而第二个单元格地址应为相对引用。EXCEL数据处理电子教案14中国水利水电出版社7.3.2动态更新区域的数据汇总n在实际工作中，有时需要对一个不断更新的数据源进行动态数据汇总，此时可以考虑使用
11、SUMIF函数。EXCEL数据处理电子教案15中国水利水电出版社7.3.3不连续区域的数据汇总EXCEL数据处理电子教案16中国水利水电出版社7.3.4 数据区域中前若干个最大（小）数值的汇总n在数据处理中，有时需要计算数据区域中前若干个最大（小）数值之和。EXCEL数据处理电子教案17中国水利水电出版社7.3.5对含有错误值的单元格区域进行汇总n当利用汇总函数对进行数据汇总时，如果单元格中存在错误值，则函数或者公式的结果将会出现错误。对于这类问题，需要使用错误判断函数IFERROR进行判断，并利用数组公式生成一个不含有错误值的数组，然后才能得到正确的结果。 EXCEL数据处理电子教案18中国
12、水利水电出版社7.4数据的多表合并n在实际数据处理中，有时数据被存放到不同的工作表中，这些工作表可在同一个工作簿中，也可来源于不同的工作簿，它们格式基本相同，只是由于所表示的数据因为时间、部门、地点、使用者的不同而进行了分类；但到一定时间，还需要对这些数据表进行合并，将合并结果放到某一个主工作簿的主工作表中。n例如，一家企业集团，开始时为了分散管理的方便，分别将各个子公司的销售信息存放到了不同的工作簿中，最后年终可以采用多表合并的方式把各个工作簿中的信息再合并到一个主工作簿中。n说明：说明：Excel支持将不多于255个工作表中的信息收集到一个主工作表中。EXCEL数据处理电子教案19中国水利
13、水电出版社图7-20表示的就是一个合并计算的例子，注意其中参与合并计算的3个工作表可以分别来源于不同的工作簿，也可以是一个工作簿中的不同的3个工作表。EXCEL数据处理电子教案20中国水利水电出版社7.4.1利用SUM函数实现多表数据合并n进行多表数据合并，最好理解的方法就是通过公式的“三维应用”来实现，也就是同过使用SUM等函数，将来自不同工作表乃至其它不同工作簿的数据进行跨表格引用。EXCEL数据处理电子教案21中国水利水电出版社7.4.2按位置进行合并计算n如果所有需要合并的各个工作表源区域中的数据按同样的顺序和位置排列（例如，数据来自同一模板创建的一系列工作表），则可按位置进行合并计算
14、。n注意：按位置进行合并计算时，各工作表中行标题和列标题的位置和顺序必须一致。EXCEL数据处理电子教案22中国水利水电出版社7.4.3按分类进行合并计算n如果需要合并的各个工作表中的数据区域具有相同的行标题或列标题，但是它们是以不同的方式组织的（比如位置不同或者顺序不同），则可按分类进行合并计算。EXCEL数据处理电子教案23中国水利水电出版社7.5 数据的透视分析n数据透视分析就是从数据库的特定字段中概括信息，从而方便从各个角度查看、分析数据，并可对数据库中的数据进行汇总统计，它在Excel中的实现工具是数据透视表（图）。n数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数
15、据透视图是一种能够根据数据处理需要，查看部分数据的图表对比效果，有些类似前面介绍的动态图表功能，另外，Excel中还可以根据数据透视表制作不同格式的数据透视报告。EXCEL数据处理电子教案24中国水利水电出版社7.5.1数据透视表的创建n选择“数据”“数据透视表和数据透视图” ，然后按操作即可。 EXCEL数据处理电子教案25中国水利水电出版社7.5.2创建数据透视表时的相关操作 (1)“布局”对话框的使用EXCEL数据处理电子教案26中国水利水电出版社7.5.2创建数据透视表时的相关操作(2)汇总方式的修改EXCEL数据处理电子教案27中国水利水电出版社7.5.2创建数据透视表时的相关操作(
16、3) 使用透视表筛选数据EXCEL数据处理电子教案28中国水利水电出版社7.5.3创建数据透视图n创建数据透视图与创建数据透视表一样，也是按上面的向导操作，关键是第一步要选 “数据透视图”；如果数据透视表已制作好，要制作与之对应的数据透视图，只要在数据透视表上单击右键，从快捷菜单上单击“数据透视图” 即可。 EXCEL数据处理电子教案29中国水利水电出版社7.5.4编辑数据透视表n对于制作好的数据透视表，有时还需要进行编辑操作。编辑数据透视表包括对其布局的修改、格式化表中的数据、透视表的复制等。创建数据透视表后，Excel自动打开如图7-42所示的“数据透视表”工具栏以及“数据透视表字段列表”
17、对话框，可通过使用“数据透视表”工具栏，并借助于“数据透视表字段列表”对话框，对数据透视表进行修改。EXCEL数据处理电子教案30中国水利水电出版社7.5.5修改数据透视表的布局(1) 行、列字段互换。(2) 在字段内移动数据项(3) 隐藏和显示数据 (4) 添加字段 (5) 删除字段 (6) 设置数据透视表选项EXCEL数据处理电子教案31中国水利水电出版社7.5.6新数据透视表中数据显示形式的修改n在数据透视表中，数据的默认显示形式是以数值形式显示，但这不是固定不变的，可以修改数据透视表中数据的显示形式，如显示为小数、百分数或其他需要的形式。EXCEL数据处理电子教案32中国水利水电出版社
18、7.5.7 数据透视表的数据更新n数据透视表的数据来源于数据源，不能在透视表中直接修改；即使源数据库中的数据被修改了，透视表中的数据也不会自动更新，必须执行更新数据操作才能刷新，数据透视图也是这样。这一点与Excel中图表制作时候，数据变动会引起对应图表同步改变不同。n需要更新数据透视表时，有以下三种方法可用：（1）在“数据”菜单中选择“更新数据”；（2）在数据区域的快捷菜单中选择“更新数据”；（3） 在“数据透视表”工具栏上选择“更新数据”按钮。EXCEL数据处理电子教案33中国水利水电出版社7.5.8删除数据透视表n如果不再想要数据透视表，可以将其删除。n操作步骤如下：(1) 单击要删除的
19、数据透视表中的任意一个单元格。(2) 单击“数据透视表”工具栏中的“数据透视表”按钮，或者右键单击数据透视表，弹出快捷菜单。(3) 从中选“选定”，打开子菜单并从中选“整张表格”子选项。(4) 打开“编辑”菜单，选择“清除”选项，并从其子菜单中选择“全部” 子选项，即可删除数据透视表。EXCEL数据处理电子教案34中国水利水电出版社7.5.9制作数据透视报告n根据需要，可以为数据设置数据透视报告格式 EXCEL数据处理电子教案35中国水利水电出版社小小 结结n对数据进行汇总可以通过有关汇总函数进行，其中SUM函数可以进行无条件汇总求和；但是当要进行一些满足指定条件的数据汇总时，就需要依靠SUM
20、IF、SUMPRODUCT、DSUM或者联合SUM函数和IF函数。n分类汇总功能可以方便地实现数据库表格按照指定的关键字进行分类汇总，根据需要还可以进行多重分类汇总和嵌套分类汇总。另外，对于管理中特定场合下的数据汇总，读者也要熟练操作，例如累加汇总、动态区域汇总、若干个最大（小）数值汇总、不连续区域汇总以及当数据区域中存在错误时如何进行汇总等。n数据的多表合并可以通过三维引用，利用SUM函数来实现；另外，Excel还专门提供了“合并计算”功能，根据所用数据区域不同，它有按位置合并和按分类合并两种方案。n数据透视表是一种对大量数据快速汇总和建立交叉列表的动态工作表，而数据透视图是形象生动的图表，
21、还可以制作不同格式的数据透视报告。n通过本章学习，读者应能熟练地进行数据汇总，多表合并和数据透视。EXCEL数据处理电子教案36中国水利水电出版社习习 题题1请比较SUMIF、SUMPRODUCT和DSUM三个汇总函数在使用上不同之处。2什么是多重分类汇总？什么是嵌套汇总？二者有什么相同点和不同点？ 3在Excel中，多个数据表之间的“合并计算”包括按位置合并和按分类合两种方案，请说明这两种方案的不同应用场合以及操作时候需要注意的问题。4数据透视表具有什么用途？ 如何对数据透视表进行更新？5上机自行将本章的所有实例操作一遍68 上机练习习题 -具体要求详见教材和本书素材EXCEL数据处理电子教案37中国水利水电出版社
