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