(3.8)-8.动态报表与数据查找

第11章动态报表与数据查找本章学习目标1、

了解动态报表、表及其结构化引用2、掌握动态报表的数据处理与分析方法3、掌握用查找引用类函数查询提取数据的方法11.1表与动态报表1、工作表的缺限日常工作中，类似于这样的“日志”工作表很常见，每天都向此工作表添加数据，由于数据行的不确定性，为统计工作带来了一定的困难！Excel2010的表格可以解决这一难题！11.1.1表格1、表格的概念表格(Table)也称表（本章后面统称表），是一系列包含相关数据的行和列，这些行和列与工作表中其他行和列中的数据分开管理。表具有动态特性。当表中的数据行发生变化之后，针对于表的各种计算公式会以表中的最新数据为依据进行自动调整，重新计算出正确的结果。11.1.1表格2、表的建立表与普通工作表区域可以随时转换，可以将普通工作表转换成表，也可以将表转换成普通工作表。1、建立普通工作表，每列有标题2、单击“插入”|“表”按钮3、弹出“创建表”对话框，单击“确定”按钮就会建立该数据区域对应的表，如下页所示11.1.1表表名称标题行控制点汇总行表区域数据区域计算列11.1.1表3、表的结构表：是指包括标题和汇总行在内的整个区域。表名称：每个表都有一个名称，应用表名称可以引用表中的数据。标题行：表区域的第一行，常用描述性文字表示。在默认情况下，表中每一列都在标题行中启用了筛选功能，利用此功能可以快速筛选表中的数据或对表进行排序。数据区域：数据区域是指除开表标题和汇总行之外的区域，是表存放数据的单元格区域。汇总行：汇总行位于表的最下方，在最后一个数据行下面。在默认情况下，汇总行是不显示在汇总行中，可以对相应列中的表数据进行各种类型的汇总计算，如计数、求平均数、求总和等。大小调整控制点：在表的右下角，用鼠标上下左右拖动它，可以可以扩大或缩小表所对应的区域。11.1.1表4、表的功能排序和筛选：Excel会自动将筛选器下拉列表添加在表的标题行中，通过它可以实现工作表数据筛选。格式化：通过表样式可以快速对数据表进行格式化。显示和计算表数据总计：可以快速地对表中的数据进行汇总，方法为：在表的末尾显示一个总计行，然后使用在每个总计行单元格的下拉列表中提供的函数。使用计算列：要使用一个适用于表中每一行的公式，可以创建计算列。计算列会自动扩展以包含其他行，从而使公式可以立即扩展到这些行。动态扩展：表中的数据区域具有动态特性，当在表下边相邻的空行或表右边相邻的空列中输入数据时，Excel就会自动对表进行扩展。删除重复数据：快速地删除重复数据行。11.1.1表计算列和动态扩展H列是计列：在H2中输入公式：=F2*H2回车后，Excel就会自动填充H列的计算公式在I2中输入21，表会动态扩展将I列包括到表中，但I列不是计算列，计算列应包括公式。在J2中输入“=2”，这是一个公式，J2是计算列，Excel会将此公式填充J列在第8行任一单元格输入数据，Excel就会自动扩展表区域，将第8行包括到表中11.1.2结构化引用和动态报表1、结构化引用表是一个自包含对象，是一个完整的结构，表区域、数据区域、汇总行、标题行、列标题、数据行和数据列等都是表结构的组成部分。在对表进行计算的公式中，可以引用表中的单元格，也可以直接引用行、列、数据区域、汇总行或标题行等表结构，称为结构化引用。结构化引用最大的优点是对于动态报表的自动识别，无论表的数据区域怎样变化，结构化引用的单元格区域都能够随之进行自动调整。这样便在最大程度上减少了在表中添加和删除行或列时重写公式的需要。11.1.2结构化引用和动态报表【例11.2】在例11.1的销售工作表中建立统计报表，计算以下数据：每位职工销售各种品牌电视机的总数量、总的销售记录数、所有电视机的平均销售价格、所有产品的销售总数量以及商店工作人员的名单。表K3单元格中的公式，其中包括结构化引用通过表，删除冗余数据得到的不重复名单11.1.2结构化引用和动态报表1、结构化引用语法结合Page271理解公式中①②③④⑤的含义，弄清楚结构化引用的真实用法！说明如下：常用结构化引用标志①表名称表名称实际上相当于表数据区域名称（若有标题行和汇总行，将不包括它们）。例如，在前面的例子中，表3是A2:H10区域的名称②列说明符从列标题演化而来，由括号“[]”括起，并引用列数据（若有列标题和汇总行，将不包括它们），相当于列的名称例如公式中的“[销售员姓名]”、“[产品名称]”和“[数量]”都是列说明符，分别代表C2:C10、D2:D10和G2:G10区域中的数据。常用结构化引用标志③特殊项目说明符是引用表中的特定部分（如汇总行）的方法。

[#数据]是指表的整个数据区域，即A2:H10。“[[#数据],[产品名称]]”表示引用数据区域中产品名称列所对应的数据（即D2:D10）。“#全部”表示表对应的全部单元格区域（A1:H11）“#标题”表示表的标题行（A1:H1）“#汇总”表示汇总行（A11:H11，处于隐藏状态）。“@-此行”与结构化引用公式所在单元格的行相对应。用法有二：其一，是直接用“[@]”表示表中的一行数据。例如，若在I2中输入公式“=sum(表3[@])”，则其中的@代表A2:H2区域；第二种用法，是“[@列标题]”，表示由指定标题列中与公式所在行交叉处的单元格。例如，若在I2中输入公式“=表3[@单价]”，表示表3中第2行单价列的数据，即F2，其值为3080。2、常用结构化引用案例【例11.2】在例11.1的销售工作表中建立统计报表，计算以下数据：每位职工销售各种品牌电视机的总数量、总的销售记录数、所有电视机的平均销售价格、所有产品的销售总数量以及商店工作人员的名单。11.1.2结构化引用和动态报表2、H列是计算列，公中应用对表3的结构化引用，计算每天的销售总价：其中的[#此行]表示公式所在数据行，它可用@代替，[单价]和[数量]都是表中的数据列的结构化引用。例如H2中公式的：“[[#此行]，[单价]]”代表F2，也可表示为“[@单价]”“[[#此行]，[数量]]”代表G2，也可表示为“[@数量]”即H2中的总价计算公式也为表示为：=[@单价]*[@数量]1、原始数据区域，将其转换成表11.3查找大工作表的特定数据行1、概述当工作表数据行较多时，要查看其中的某行数据并非易事。利用Excel提供的查找功能就能够很快定位到特定数据行，实现高效的查找。本节案例【例11.5】某单位有600多名职工，其医疗档案表如所示，现要从中查看李大友的医疗费用情况。11.5查找大工作表的特定数据行1、精确数据查找11.3查找大工作表的特定数据行1、单击“开始”选项卡2、单击“查找和选择”中的“查找”命令3、在“查找内容”中输入查找内容4、单击“查找全部”可以将光标定位到找到的数据行上2、模糊数据查找如果对要查找的内容不太清楚，或者需要查找含有相近但并不相同的文本的记录，就可使用通配符查找。通配符查找是指用一个记号代替不能确定的符号进行查找。Excel使用的通配符记号有“*”和“？”，*代表任意多个任意符号，？代表一个任意符号。例如，bd?cd可以是bdacd，bdecd，bd3cd，bc/cd等，bd*cd则可以是bdee2cd，bdacd，bd9088cd等。11.3查找大工作表的特定数据行1、单击“开始”选项卡2、单击“查找和选择”中的“查找”命令3、在“查找内容”中输入查找内容，*代表任意符号4、单击“查找全部”可以将光标定位到找到的数据行上11.4查找及引用函数1、概述查找引用函数能通过单元格引用地址、行、列对工作表的单元格进行访问，还能够从单元格的引用地址中求出单元格所在的行或列，进而查获更多的信息。当需要从一个工作表查询特定的值、单元格内容、格式或选择单元格区域时，这类函数特别有用。在大数据表、不同工作薄或工作表之间查询数据，将查询结果用于公式计算时，这类函数很有用。11.4.1用行、列号函数定位与提取数据1、行列号函数的作用行、列号函数能够生成有规律的自然数，在Excel公式中经常利用它们来确定单元格在工作表中的引用位置，构造灵活多样的数据查询。2、行号/行数计算函数ROW([reference])ROWS(array)Row计算参数引用的行编号,如果省略参数则计算公式所在单元格的行号Rows计算参数引用中包含的行数。例如，Row(A1)=1，Row(B8)=8，Rows(A1:E1)=1，Rows(A1:E5)=5；11.4.1用行、列号函数定位与提取数据3、列号/列数计算函数COLUMN([reference])COLUMNS(array)Column计算出指定单元格引用位置所在的列号，如果省略参数则计算公式所在单元格的列号；Columns计算指定单元格区域中的列数。例如Column(C10)=3，Column(E1)=5Columns(A1:E1)=5Columns(A1:E5)=5Colunms(A1:A100)=1。11.4.1用行、列号函数定位与提取数据【例11.6】用行列号计算函数row，rows，column，columns以及文本函数mid，left，right，text进行数字的按位提取与转换。如图11.12所示。1、在N2输入公式，并向左复制可生成D2:N2区域的数据“=RIGHT(TEXT($C2*100,"￥000;;"),COLUMNS(N:$N))”公式中的$C2*100将C2中的数据转换成整数1235397，再用Text函数将它转换成文本形式的金额，注意在￥的左边有空白符一个，再得用Right函数依次从右边截取1、2、3、4、5……位字符并存入N2、M2、L2、K2……公式利用COLUMNS函数生成了需要的自然数1、2、3、……。当公式从N2复制到M2、L2、K2……时，计算列数的函数将分别变成COLUMNS(N:$N))，COLUMNS(M:$N))，COLUMNS(L:$N))……结果依次为1、2、3……11.4.1用行、列号函数定位与提取数据【例11.6】2、N3输入的公式为=LEFT(RIGHT(TEXT($C3*100,"￥000;;"),COLUMNS(N:$N)))本公式内部的RIGHT函数与上面分析的N2中的公式相同，可知本公式首先用RIGHT提取文本数字中的右子串，再用Left函数提取该子串最左边的一位数字。当从右至左提取到“￥”位置时，提取到F3、E3、D3中的是空白字符。11.4.1用行、列号函数定位与提取数据【例11.6】(3)在D4中输入并向右复制下面的公式：

=MID($C4,COLUMN(A1),1)其中的COLUMN(A1)结果为1，MID将中C1的第1位开始提取1位数字；将公式复制到E4时，将变成“=MID($C4,COLUMN(B1),1)”，因COLUMN(B1)结果为2，公式将提取C4中的第2位数字……11.4.2用INDIRECT函数和名称查询其他工作表中的数据Indirect概述Indirect函数返回由文字串指定的引用。该函数能够对引用进行计算，并显示引用的内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用该函数。格式Indirect(ref_text)其中ref_text为对单元格的引用，它可以是单元格的名称、引用或字符串。11.4.2用INDIRECT函数和名称查询其他工作表中的数据例若单元格A1包含文本“B2”，且单元格B2包含数值1.333，则INDIRECT($A$1)=1.333；如果将单元格A1中的文本改为“C5”，而单元格C5中包含数值45，则INDIRECT($A$1)=45；如果B3包含文本“George”，而名字为George的单元格包含数值10，则INDIRECT($B$3)=10。Indirect与名称的妙用用名字作它的参数，可以构造非常灵活而高效的查询，还能使问题简化。【例11.7】某单位的职称工资表如图11.13（a）所示，职工基本档案表如图11.13（b）所示。假设图11.13（b）中除了E列的职称工资外，其他数据都建立完毕，现在要输入每位职工的职称工资。11.4.2用INDIRECT函数和名称查询其他工作表中的数据（1）指定图（a）中A2:B11区域的最左列为名字。（2）在E2单元格中输入下述公式=Indirect(D2)向下复制此公式，就能求出所有职工的职称工资。11.4.3用ADDRESS和OFFSET函数进行定位查找与数据提取1．ADDRESS用指定的行列编号，生成文本形式单元格引用地址，用INDIRECT可将其转换成可用的引用。用法如下。ADDRESS(r,c,abs_num,a1,sheet_text)r是在单元格引用中使用的行号；c列号；abs_num指明引用类型。1（或省略）——绝对引用；2——绝对行号，相对列号；3——相对行号，绝对列号；4相对引用例：ADDRESS(6,3)=$C$6，ADDRESS(6,1,2)=A$6在实际工作中，许多时间要根据工作表，单元格行、列位置动态引用单元格，在这种情况下应用Address和行列计算函数制作这类报表是比较方便的。11.4.3用ADDRESS和OFFSET函数进行定位查找与数据提取【例11.8】有银行存折信息如图11.14所示，其中A列是存款或取款的日期，B列是存款信息，C列是取款信息，D列是每发生一笔存取款信息后存折上的余额。P282存折余额的计算公式为：存款余额=本期存款–本期取款+前期余额。D2中的公式为：=SUM(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),-INDIRECT(ADDRESS(ROW(),COLUMN()-1)),INDIRECT(ADDRESS(ROW()-1,COLUMN())))ADDRESS(ROW(),COLUMN()-2)其中，ROW的结果为2，COLUMN()-2的结果为4-2；则ADDRESS（2，2）结果为文本形式的：$B$2；INDIRECT（$B$2）将它转换成可用的引用并求取B2中的值11.4.3用ADDRESS和OFFSET函数进行定位查找与数据提取2．OFFSETOFFSET以指定的引用位置为参照点，再根据指定的偏移量计算出新的引用位置。函数的结果是单元格引用或区域。用法：OFFSET(reference,rows,cols,[height],[width])参数说明Reference是参照点单元格或区域。Rows和Cols用于确定新引用位置偏离Reference单元格（若Reference为单元格区域，则指该区域左上角单元格）的行数和列数。Rows为正数时表示新位置在Reference下方的第Rows行，为负数时表示新位置在Reference上方的第Rows行Cols为正数时表示新位置在Reference右边的第cols列，为负数时表示在Reference左边的第cols列。OFFSET(reference,rows,cols,[height],[width])Offset参数说明Height用于指定所要返回的引用区域的行数Width用于指定所要返回的引用区域的列数。Height和Width必须为正数。如果省略height或width，则假设其高度或宽度与reference相同。如果行数和列数偏移量超出工作表边缘，函数OFFSET返回错误值#REF!。注意OFFSET实际上并不移动任何单元格或更改选定区域，它只是返回一个引用，可用于任何需要将引用作为参数的函数。OFFSET(reference,rows,cols,[height],[width])Offset简单实例SUM(OFFSET(C2,1,2,3,1))

OFFSET(C2,1,2,3,1)确定单元格C2靠下1行右2列的3行1列的区域，即E3:E5，原公式相当于“=SUM（E3:E5）”OFFSET(A8,4,5)，A8下4行右5列，即

F12，OFFSET(A8,-4,5)，A8上4行右5列，为F4，OFFSET(A8:D12,4,5)，A8下4行右5列与A8:D12大小相同的区域，结果为

F12:I16OFFSET(A1,,)等效于OFFSET(A1,0,0)，结果为A111.4.4Choose函数进行值查询Choose函数格式Choose(n,v1,v2,...v254)其中n是一个整数值，用以指明待选参数的序号。n必须为1~254之间的数字或者是包含数字1~254的公式或单元格引用。如果n为1，函数的值就为v1；如果为2，函数返回v2，以此类推v1，v2，…为1~254个数值参数，可以是数字、单元格引用，区域，或者已定义的名称、公式、函数或文本。功能利用索引从参数清单中选择需要的数值。11.4.4Choose函数进行值查询案例【例11.10】某学校为了提高教学质量，让学生对教师的授课情况进行评价。评价采用百分制，如图的E列所示。现要将学生评价转换成等级制。转换规则是：0~60为不及格，60~70为及格，70~80为中，80~90为良，90~100为优。用Choose函数进行转换的方法是，在F2单元格输入公式：=CHOOSE(IF(E2<60,1,INT((E2-50)/10)+1),"不及格","及格","中","良","优")11.4.5用match和index函数构造灵活的查询1、概述Match函数在工作表的一行（或一列）中进行数据查找，并返回数据在行（或列）中的位置。在多数情况下，Match函数的结果并不是所需要的最终答案，而是作为Vlookup的第3个参数，或作为Index函数的参数。11.4.5用match和index函数构造灵活的查询Match格式Match(x，r，f)其中x是要查找的数值，r可以是一个数组常量，或某列（或行）连续的单元格区域，其中可能包含有要查找的x。f用于指定match的查找方式，它可以是-1，0或1，表11-3给出了这几个取值的含义。功能Match(x，r，f)表示的意思是：在数组或连续的单元格区域r中查找x，并返回x在r中的位置编号。当f为0是，match进行精确查找，当f为1（或-1）时，match进行模糊查找。11.4.5用match和index函数构造灵活的查询取值函数功能-1r必须按降序排列，查找大于或等于x的最小数值0r不必排序，查找等于x的第一个数值1r必须按升序排列，查找小于或等于x的最大数值表11-3Match查找的方式11.4.5用match和index函数构造灵活的查询2、Index函数格式Index(Area，r，c，n)其中，Area是1个或多个单元格区域；r是某行的行序号，c是某列的列序号，该函数返回指定的行与列交叉处的单元格引用。如果r等于0，则返回整行单元格引用，如果c等于0，则返回整列单元格引用。当Area包括多个单元格区域时，n=1就表示结果来自于Area中的第1个区域，n=2表示结果来源于第2个单元格区域……。如果省略n表示结果来源于第1个单元格区域。功能Index（Area，r，c，n）的功能是返回Area中第n个单元格区域中的r行，c列交叉处的单元格引用。11.4.5用match和index函数构造灵活的查询3.用INDEX和MATCH进行查找【例11.11】某地域中各县的蔬菜销售单价表如图的A4:J18区域所示，希望能够快捷地查找到某地某蔬菜的单价。最好是输入地名和蔬菜名，就能看到对应的蔬菜单价，如图B1:D3区域所示。2在D3单元格中输入公式：=INDEX(A5:J18,MATCH(B3,A5:A18,0),MATCH(C3,A5:J5,0))11.4.6用Lookup函数进行表查找Lookup函数Lookup函数在一个大表中找出特定数据，并在其它工作表中引用查找结果，在工作中应用较广。功能从给定的向量（单行或单列单元格区域）或数组中查询出需要的数值。格式Lookup(x,r1,r2)其中：x是要查找的内容，它可以是数字、文本、逻辑值或包含数值的名称或引用。r1、r2都是只包含一行或一列的单元格区域，其值可以是文本、数字或逻辑值。r2的大小必须与r1相同。Lookup函数在r1所在的行或列中查找值为x的单元格，找到后返回r2中与r1同行或同列的单元格中的值。11.4.6用Lookup函数进行表查找注意：①r1中的内容必须按升序排序，查找的字符文本不区分大小写。否则，Lookup函数不能返回正确的结果。②如果Lookup函数找不到x，则查找r1中小于或等于x的最大数值。如果x小于r1中的最小值，Lookup函数返回错误值“#N/A”。11.4.6用Lookup函数进行表查找1、用lookup函数在普通工作表中查找数据【例8.6】某蔬菜供应商在一个工作表中保存蔬菜的单价和出产地，如图（a）所示。在另一工作表中保存销售记录，如图（b）所示。在图（b）中，蔬菜名和数量是实际输入的数据，产地和单价需要根据产品名从图（a）所示的蔬菜单价表中查询输入D3中输入下述公式：=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$B$2:$B$11)向下复制此公式，查出蔬菜的单价在B3中输入下述查找公式：=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$C$2:$C$11)向下复制此公式，查出蔬菜的产地11.4.6用Lookup函数进行表查找案例解决方法（1）建立图（a）所示的蔬菜单价表，并按升序对该工作表进行排序，排序主关键字为“蔬菜”。（2）输入图（b）的A列数据，和第1、2行的标题。在B3中输入下述查找公式，然后向下填充复制该公式，就可找出各蔬菜的产地。=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$C$2:$C$11)查找蔬菜单价的方法与此完全类似，只需要在图（b）的D3单元格中输入下述公式，然后向下复制该公式就行了。=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$B$2:$B$11)11.4.6用Lookup函数进行表查找2、在lookup函数通过对表的结构化引用查找数据11.4.7用Vlookup函数进行表查找Vlookup函数功能Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据，它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容。格式Vlookup(x,table,n,f)其中，x是要查找的值；table是一个单元格区域；n中table区域中要返回的数据所在列的序号。n=1时，返回table第1列中的数值；n=2时，返回table第2列中的数值；以此类推。f是一个逻辑值，表示查找的方式。当其为true（或1）时，表示模糊查找；当它为false（或0）时，表示精确查找。11.4.7用Vlookup函数进行表查找说明：Vlookup函数在table区域的第1列中查找值为x的数值，如果找到，就返回与找到数据同行第n列单元格中的数据。当f为true时，table的第1列数据必须按升序排列，否则找不到正确的结果；当f为false时，table的第1列数据不需要排序。注意①如果Vlookup函数找不到x，且f=true，则返回小于等于x的最大值。②如果x小于table第1列中的最小值，Vlookup函数返回错误值“#N/A”。③如果Vlookup函数找不到x且f=FALSE，Vlookup函数返回错误值“#N/A”。11.4.7用Vlookup函数进行表查找1、用Vlookup进行模糊查找模糊查找也就是常说的近似查找，常用于数据转换分组或数据对照表中的数据查找。案例【例11.14】假设所得税的税率如图的A1:B10区域所示。其中的含义是：0~500的税率为0%，500~1000的税率为1%，1000~1500的税率为3%……，4000以上的税率为20%。某公司的职工收入数据如图的D1:J11所示，现在计算每位职工应缴的所得税。11.4.7用Vlookup函数进行表查找I列的所得税率的计算方法如下。在I3单元格中输入下述公式，然后向下复制此公式，就能够计算出每位职工的所得税率。=Vlookup(H3,$A$3:$B$10,2,1)公式的含义是：在A3:B10区域中的第1列数据中（即A3:A10），查找与单元格H3内容（即11454）最接近的单元格，然后返回A3:B10区域第2列（即B列）与找到单元格（即A10）同行单元格的内容（即B10）。11.4.7用Vlookup函数进行表查找2、用Vlookup进行精确查找概述精确查找就是指查找数据完全匹配的查找，Vlookup函数具有此项功能。在大表中查找特定数据，或查找不同工作表中的数据，特别是工作表数据较多，Vlookup函数显得非常有效。11.4.7用Vlookup函数进行表查找案例【例11.15】某学校职工收入由基本工资、奖金、课时费等部分组成，这些金额分别保存在同名的基本工资、奖金和课时费表中，如图11.22(b)(c)(d)所示。现在要形成图11.22(a)所示的汇总表，并计算职工的总收入。在四张数据表中，只有职工收入表和基本工资表的数据包括了全部职工，有些职工没有奖金，有些职工没有课时费，所以奖金表和课时费表中只有部分职工信息11.4.2用Vlookup函数进行表查找案例解决方法（1）用Vlookup精确查找基本工资、奖金和课时费在图11.22(a)单位列的C2单元格输入下面的公式，然后向下填充复制此公式，即可查询出每位职工的单位。=VLOOKUP(A2,基本工资!$A$1:$D$279,4,0)在11.22(a)的E2中输入查找奖金的公式：=VLOOKUP(A2,奖金!$A$1:$D$279,4,0)在11.22(a)的F2中输入查找课时费的公式：=VLOOKUP(A2,课时费!$A$1:$D$279,4,0)11.4.用Vlookup函数进行表查找应用案例2（补充内容）【例11.16-OLD】某电话公司的电话收费系统进行了系统升级，下页图（a）是系统升级前的电话号码和收费账号对照表，图（b）的是升级后的收费表。升级后系统新加了一些号码，新加的号码要重新编制账号，但原有号码的账号则需要从旧系统中查询。也就是说，图（b）中的绝大部分号码的账号（B列数据）要从图（a）的B列查询。11.4.7用Vlookup函数进行表查找在图的B3单元格输入下述查找公式，然后向下复制此公式，就能查找到所有旧号码的账号。

=VLOOKUP(A3,旧账号!A$2:$B$6884,2,0)11.9数据提取与表格结构调整1、数据提取和表格结构调整的应用现状数据提取和表格结构调整是日常办公中的常事，常与表格数据打交道的办公人员：办公室主任、账务处理人员、薪酬管理者、大学辅导员……，但有不少人不得其法，常用复制、粘贴等方法取数制表，低效费时。例如，对表格进行列转换，将一列数据分离为多列数据，间隔一定数据行提取或汇总数据，在间隔固定行数的数据区域之间插入空行以生成打印报表，如此等等。2、数据提取和表格结构调整的方法应用查找引用类函数，并结合row和column等行列计算函数生成的自然数，往往可以灵活地解决这类问题中的大多数，减少数据重新输入或重制报表的麻烦。11.9.2提取间隔数据行问题【例11.23】某专业期末考试成绩表如图11.38的A1:H225区域所示。放暑假了，为了让每位同学知道自己的各科成绩，辅导员要给每位同学寄成绩单，成绩单如图中J:Q列所示。11.9.2提取间隔数据行问题问题分析对比表格结构可知：每位同学的成绩单包括3行数据：第1行固定为原表的A2:H2标题行，第2行为该同学在原表中的实际数据行，第3行为空白。即，原表中的每行数据都变成新表中的3行数据。解题思路由于数据都是从原表中提取出来的，可以根据提取数据的特征，以原表起始单元格A2为参考点，用求余函数mod和行列号计算函数row和colums计算单元格距离参考点单元格的行列偏移量，再用OFFSET函数提取指定偏移位置的单元格数据，即可完成新表数据的提取。11.9.2提取间隔数据行问题解题方法在J2单元格输入下面的公式：=OFFSET($A$2,CHOOSE(MOD(ROW(A1)-1,3)+1,0,(ROW(A2)-1)/3+1,10000),COLUMN(A2)-1)&""向右向下复制即可生成J:Q区域的学生成绩单。11.9.2提取间隔数据行问题原理分析1．提取A2:H2中的标题行=OFFSET($A$2,CHOOSE(MOD(ROW(A1)-1,3)+1,0,(ROW(A2)-1)/3+1,10000),COLUMN(A2)-1)&""OFFSET函数以A2提取数据的起始参照点，其中ROW和COLUMN函数中的A1、A2没有任何特别意义，它作为这两个函数的参数，是为了生成需要的自然数。求出公式中有有关函数的结果后，J2中的公式为，=OFFSET($A$2,CHOOSE(1,0,2,10000),0)&""”计算出CHOOSE的结果后为“=OFFSET($A$2,0,0)&""””，则结果为A2单元格。11.9.2提取间隔数据行问题将J2的公式向右复制到K2时，因相对引用变化，K2中的公式为：=OFFSET($A$2,CHOOSE(MOD(ROW(B1)-1,3)+1,0,(ROW(B2)-1)/3+1,10000),COLUMN(B2)-1)&""计算在其中row，column和mod函数的值后，公式为，=OFFSET($A$2,CHOOSE(1,0,1,10000),1)&""计算出CHOOSE的结果后为“=OFFSET($A$2,0,1)&""””，则结果为B2单元格。由上分析可知，向右复制J2中的公式时，OFFSET偏离A2单元格的行号不变，列号由COLUMN函数的计算而相对递增，将依次提取B2、C2、D2……11.9.2提取间隔数据行问题2．提取成绩行将J2的公式向下复制到J3时，因相对引用变化，J3中的公式为：=OFFSET($A$2,CHOOSE(MOD(ROW(A2)-1,3)+1,0,(ROW(A3)-1)/3+1,10000),COLUMN(A3)-1)&""计算出其中Row，Column函数的值后，公式变为：=OFFSET($A$2,CHOOSE(2,0,1,10000),0)&""计算出CHOOSE的值后，公式即为“=OFFSET($A$2,1,0)&""”，结果为A3单元格。由“提取标题行”的分析可知：向右复制J3中的公式，将依次提取B3、D3……，即第一个学生的成绩单。11.9.2提取间隔数据行问题3．提取空白行将J3中的公式复制到J4时，因相对应用的变化，J4中的公式为：=OFFSET($A$2,CHOOSE(MOD(ROW(A3)-1,3)+1,0,(ROW(A4)-1)/3+1,10000),COLUMN(A4)-1)&""计算出其中MOD，ROW和COLUMN函数的值后，公式如下：=OFFSET($A$2,CHOOSE(3,0,2,10000),0)&""Choose执行后，公式变成“=OFFSET($A$2,10000,0)&""”，结果为偏离A2单元格10000行列0的单元格，即A10002，那里的单元格没有任何内容。由此可知，最被输入在J2公式中的10000实际上可以改写为其它数字，只要它对应工作表中不会有数据的空白行就行了。在实际应用中，常常用工作表最后的数据行编号来表示。因为实际工作表基本上不可能将数据保存到最后数据行。11.9.2提取间隔数据行问题4．MOD函数确定数据转换的行数将J4中的公式复制到J5后，因相对引用变化，公式将变成：=OFFSET($A$2,CHOOSE(MOD(ROW(A4)-1,3)+1,0,(ROW(A5)-1)/3+1,10000),COLUMN(A5)-1)&""求出公式中有有关函数的结果后，J5中的公式等效为，=OFFSET($A$2,CHOOSE(1,0,2,10000),0)&""可以发现这个公式与J2中的公式相同。原因是只要Row函数计算出的行号减1为3的倍数，就表示一个学生的成绩单生成了，Mod函数将OFFSET中的行偏移量设置为0。由此可知，每个学生的成绩单如果需要4行，则Mod应模除4，有5行则模除5……11.9.2提取间隔数据行问题5、提取间隔数据行的灵活应用上例介绍了从数据表中提取数据，将原数据表拆分成许多小数据表的方法。反过来看，也有许多时候需要从原数据表中提取被分离的数据，将数据重新集中在一起。【例11.24】有学生成绩单如图11.39中A1:F23所示，现需要将其中的数据整理成一份完整的学生成绩表，以便进行各科成绩的统计和分析，如下图中I1:N8区域所示。合并数据从G列的行编号可以知道，要提取的数据行为3、7、11，即间隔4行提取。如果以A1作为OFFSET函数的参照点提取数据，可以用ROW（A1）*4-2作为OFFSET偏离A1的行数，再用Column计算偏离的列数，可以方便地实现数据的提取操作。在I3中输入下面的公式：=OFFSET($A$1,ROW(A1)*4-2,COLUMN(A$1)-1,1,1)将此公式向右、向下复制，即可提取各同学的资料和成绩数据，如图中I3:N8区域所示。11.9.3数据表行列转换问题1、行列转换的应用情况和手段在不同应用场合，以及不同应用软件之间传递数据时，常常需要进行表格的格式转换，表格的行列变换就是其中的常见问题之一。2、进行表格行列转换的手段用Index、Offset、Match等查找引用类函数，结合Row，Column等行列计数函数，可以方便实现表格的行列转换。3、将表格行转换为列3、将表格行转换为列【例11.25】某班某次考试的成绩如下图11.40的A1:F7区域所示，现需要将此表中的数据转换成I:L列区域中的格式，以便将其导入学生成绩管理数据库中。每位同学有4科成绩，则转换之后每个同学的数据将从原表的1行转换成4行。其中姓名和学号由原表的1行重复为4行，用Index或Offset函数都容易实现这样的转换，本例采用Index函数完成它们的转换。