excel数据处理与分析-第11章动态报表与数据查找.pptx

第11章 动态报表与数据查找本章学习目标1、理解表与结构化引用2、掌握动态报表的构造和数据分析方法3、掌握查询大工作表数据的方法4、用lookup/Vlookup函数查询数据的方法5、用indirect和名称相结合查询数据的方法6、index和match相结合查询数据的方法7、用D函数查询数据的方法8、字符匹配函数与文本查询的方法9、用ADDRESS和OFFSET进行定位查询10、用choose查询数据的方法11、工作表数据提取与报表结构转换11.1 表与动态报表1、工作表的缺限日常工作中，类似于这样的“日志”工作表很常见，每天都向此工作表添加数据，由于数据行的不确定性，为统计工作带来了一定的困难！Excel 2010的表格可以解决这一难题！11.1.1 表格1、表格的概念表格也称表，是一系列包含相关数据的行和列，这些行和列与工作表中其他行和列中的数据分开管理。表是Excel2010中的特殊对象（其实早就有了，对应于Excel 2003中的列表，只是它比列表具有更多的功能），包含有格式化功能以外的许多特性。表中包括的主要内容有表区域、表数据区域、汇总行、标题行、列标题、调整大小控制点等，如图11.2所示。表具有动态特性。当表中的数据行发生变化之后，针对于表的各种计算公式会以表中的最新数据为依据进行自动调整，重新计算出正确的结果。 11.1.1 表格3、弹出“创建表”对话框，单击“确定”按钮就会建立该数据区域对应的表，如下页所示2、表的建立表与普通工作表区域可以随时转换，可以将普通工作表转换成表，也可以将表转换成普通工作表。2、单击“插入”|“表”按钮1、建立普通工作表，每列有标题11.1.1 表表名称表区域计算列标题行数据区域控制点汇总行11.1.1 表2、表的结构表：是指包括标题和汇总行在内的整个区域 。表名称：每个表都有一个名称，应用表名称可以引用表中的数据。标题行：表区域的第一行，常用描述性文字表示。在默认情况下，表中每一列都在标题行中启用了筛选功能，利用此功能可以快速筛选表中的数据或对表进行排序。数据区域：数据区域是指除开表标题和汇总行之外的区域，是表存放数据的单元格区域。11.1.1 表2、表的结构表：是指包括标题和汇总行在内的整个区域 。表名称：每个表都有一个名称，应用表名称可以引用表中的数据。标题行：表区域的第一行，常用描述性文字表示。在默认情况下，表中每一列都在标题行中启用了筛选功能，利用此功能可以快速筛选表中的数据或对表进行排序。数据区域：数据区域是指除开表标题和汇总行之外的区域，是表存放数据的单元格区域。11.1.1 表2、表的结构汇总行：汇总行位于表的最下方，在最后一个数据行下面。在默认情况下，汇总行是不显示在汇总行中，可以对相应列中的表数据进行各种类型的汇总计算，如计数、求平均数、求总和等。大小调整控制点：在表的右下角，用鼠标上下左右拖动它，可以可以扩大或缩小表所对应的区域。排序和筛选：Excel会自动将筛选器下拉列表添加在表的标题行中，通过它可以实现工作表数据筛选。 显示和计算表数据总计：可以快速地对表中的数据进行汇总，方法为：在表的末尾显示一个总计行，然后使用在每个总计行单元格的下拉列表中提供的函数 11.1.1 表2、表的结构使用计算列：要使用一个适用于表中每一行的公式，可以创建计算列。计算列会自动扩展以包含其他行，从而使公式可以立即扩展到这些行。动态扩展：表中的数据区域具有动态特性，可以灵活地向表中添加或删除数据行。当在表下边相邻的空行或表右边相邻的空列中输入数据时，Excel就会自动对表进行扩展，将输入了数据的相邻行或列添加到表中。拖动表的大小控制点，让它包括相邻的工作表行或工作表列，这些被包括的行或列就会被添加到表中；在表中任意位置插入行或列，插入的行和列就会成为表的有效组成部分。 11.1.1 表在I2中输入21，表会动态扩展将I列包括到表中，但I列不是计算列，计算列应包括公式。在J2中输入“=2”，这是一个公式，J2是计算列，Excel会将此公式填充J列计算列和动态扩展H列是计列：在H2中输入公式：=F2*H2回车后，Excel就会自动填充H列的计算公式在第8行任一单元格输入数据，Excel就会自动扩展表区域，将第8行包括到表中11.1.2 结构化引用和动态报表1、结构化引用表是一个自包含对象，是一个完整的结构，表区域、数据区域、汇总行、标题行、列标题、数据行和数据列等都是表结构的组成部分。在对表进行计算的公式中，可以引用表中的单元格，也可以直接引用行、列、数据区域、汇总行或标题行等表结构，称为结构化引用。 结构化引用最大的优点是对于动态报表的自动识别，无论表的数据区域怎样变化，结构化引用的单元格区域都能够随之进行自动调整。这样便在最大程度上减少了在表中添加和删除行或列时重写公式的需要