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

第11章动态报表与数据查找本章学习目标1、理解表与结构化引用2、掌握动态报表的构造和数据分析方法3、掌握查询大工作表数据的方法4、用lookup/Vlookup函数查询数据的方法5、用indirect和名称相结合查询数据的方法6、index和match相结合查询数据的方法7、用D函数查询数据的方法8、字符匹配函数与文本查询的方法9、用ADDRESS和OFFSET进行定位查询10、用choose查询数据的方法11、工作表数据提取与报表结构转换11.1表与动态报表1、工作表的缺限日常工作中，类似于这样的“日志”工作表很常见，每天都向此工作表添加数据，由于数据行的不确定性，为统计工作带来了一定的困难！Excel2010的表格可以解决这一难题！11.1.1表格1、表格的概念表格也称表，是一系列包含相关数据的行和列，这些行和列与工作表中其他行和列中的数据分开管理。表是Excel2010中的特殊对象（其实早就有了，对应于Excel2003中的列表，只是它比列表具有更多的功能），包含有格式化功能以外的许多特性。表中包括的主要内容有表区域、表数据区域、汇总行、标题行、列标题、调整大小控制点等，如图11.2所示。表具有动态特性。当表中的数据行发生变化之后，针对于表的各种计算公式会以表中的最新数据为依据进行自动调整，重新计算出正确的结果。11.1.1表格2、表的建立表与普通工作表区域可以随时转换，可以将普通工作表转换成表，也可以将表转换成普通工作表。1、建立普通工作表，每列有标题2、单击“插入”|“表”按钮3、弹出“创建表”对话框，单击“确定”按钮就会建立该数据区域对应的表，如下页所示11.1.1表表名称标题行控制点汇总行表区域数据区域计算列11.1.1表2、表的结构表：是指包括标题和汇总行在内的整个区域。表名称：每个表都有一个名称，应用表名称可以引用表中的数据。标题行：表区域的第一行，常用描述性文字表示。在默认情况下，表中每一列都在标题行中启用了筛选功能，利用此功能可以快速筛选表中的数据或对表进行排序。数据区域：数据区域是指除开表标题和汇总行之外的区域，是表存放数据的单元格区域。11.1.1表2、表的结构表：是指包括标题和汇总行在内的整个区域。表名称：每个表都有一个名称，应用表名称可以引用表中的数据。标题行：表区域的第一行，常用描述性文字表示。在默认情况下，表中每一列都在标题行中启用了筛选功能，利用此功能可以快速筛选表中的数据或对表进行排序。数据区域：数据区域是指除开表标题和汇总行之外的区域，是表存放数据的单元格区域。11.1.1表2、表的结构汇总行：汇总行位于表的最下方，在最后一个数据行下面。在默认情况下，汇总行是不显示在汇总行中，可以对相应列中的表数据进行各种类型的汇总计算，如计数、求平均数、求总和等。大小调整控制点：在表的右下角，用鼠标上下左右拖动它，可以可以扩大或缩小表所对应的区域。排序和筛选：Excel会自动将筛选器下拉列表添加在表的标题行中，通过它可以实现工作表数据筛选。显示和计算表数据总计：可以快速地对表中的数据进行汇总，方法为：在表的末尾显示一个总计行，然后使用在每个总计行单元格的下拉列表中提供的函数11.1.1表2、表的结构使用计算列：要使用一个适用于表中每一行的公式，可以创建计算列。计算列会自动扩展以包含其他行，从而使公式可以立即扩展到这些行。动态扩展：表中的数据区域具有动态特性，可以灵活地向表中添加或删除数据行。当在表下边相邻的空行或表右边相邻的空列中输入数据时，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.1.2结构化引用和动态报表3、计算每们职工的销售总额K列公中应用了对表3的结构化引用，无论A：H列的表中是否会增加或删除数据，K列的计算公式都会根据表中的最新数据进行计算，得到正确的结果。这些计算公式因为应用了对表的结构化引用而具有动态计算的能力。2、常用结核化引用案例3、计算每们职工的销售总额：在K3中输入数组公式：=SUM(IF(表3[销售员姓名]=$J3,IF(表3[[#数据],[产品名称]]=K$2,表3[数量])))其中第一个IF条件首先判断销售员姓名列数据即（C列）是否为J3（即劳得诺），如果是再通过第2个条件语句判断D列产品名称是否为K2（创为），如果两个IF条件都要成立，就返回同一行数量列（G列）数据。向下复制此公式可计算出各员工销售创为的数量，向右复制此公式，可计算各员工销售其它产品的总数量3、通过结构化引用删除冗余数据行在Excel的普通工作表中，要删除其中的重复数据行并不容易。将普通工作表转换成表，然后利用表提供的“删除重复项”功能，就能轻松地得到数据行不重复的报表。11.1.2结构化引用和动态报表1、包括重复数据的区域2、转换成表3、选择删除重复项4、删除重复项后的表11.1.2结构化引用和动态报表4、通过数组公式引用表表的名称、列标题、各种特殊项（如#全部、汇总等）事实相当于对应区域的名称，可以通过数组公式在不同的工作表中引用它们。11.1.2结构化引用和动态报表5、在不同工作表中对表进行结构化引用【例11.3】某超市从多家供应商处进购各种食品，进购的情况如图（a）所示。计算超市应付给各供应商的总订货费，如图（b）所示1、将源数据区域转换成表2、复制源数据区域的供应商，将它转换成表表，并通过表删除其中的重复数据行3、在B2中输入公式：=SUM(IF(进货单[供应商]=表5[[#此行],[供应商]],进货单[单价]*(进货单[订购量]+进货单[再订购量])))按Ctrl+shift+Enter,就会自动生成B列的计算公式,此公式的意思是：如果进货单表中供应商列数据与表5中同行供应商相同，就计算出单价同进货单表中订购量和再订购量的总和的乘积11.1.2结构化引用和动态报表6、表的应用和普通工作表区域的转换表能够方便地构造日常工作中的动态报表，在各种不同的公式中通过对表的结构化引用，不仅能够使公式含义清楚，而且能够扩展公式的计算能力，实现对动态报表的各类计算。在日常工作中，应该大量用表来保存各种业务数据，制作工作报表。单击表中任一单元格单击“转换为区域”，可将表转换为普通区域11.2D函数与动态报表1、关于D函数Excel将每个数据列都有标题的数据表称为数据库，并提供了大约12个专用函数来简化这种数据表的数据统计和数据查找工作，这些函数都以D开头，所以也称为D函数。D函数有相同的调用形式，其语法形式如下：Dname(database,field,criteria)

其中，Dname是函数名；database是一个单元格区域，要求该区域中的每列数据都必须有标题；field是database区域中某列数据的标题（称为字段，出现在字符串中）；criteria称为条件区域，它与高级筛选条件区域的含义和构造方法完全相同。11.2.1

D函数函数名功能DAVERAGE返回所选数据库列的平均值DCOUNT计算数据库中包含数字的单元格的数量DCOUNTA计算数据库中非空单元格的数量DGET从数据库中提取符合指定条件的一行数据DMAX返回所选数据库列中的最大值DMIN返回所选数据库列的最小值DPRODUCT将数据库中符合条件的数据行值相乘DSTDEV基于选择的数据库列的样本估算标准偏差DSTDEVP基于所选数据库列的样本总体计算标准偏差DSUM对数据库中符合条件的数据行的数字求和DVAR基于所选数据库列的样本估算方差DVARP基于所选数据库列的样本总体计算方差11.2.1

D函数简介条件区域数据库区域D函数的调用11.2.2D函数与表结合构造动态数据分析报表D函数具有动态计算能力用D函数对数据表进行条件统计非常方便，同时D函数还具有动态计算的能力。在数据库中应用D函时，其动态计算能力依赖于第一个参数的范围设置【例11.4】某商店在工作表中保存库存和进货记录，如图11.9中A:J列所示。由于随时可能会添加进货记录，因此工作表中的数据行是不确定的。计算表中各种商品的总库存量、第一次订购量和再订购量的总和，以及每类产品的总平均费用。11.2.2D函数与表结合构造动态数据分析报表应用D函数和普通数据库字段计算出的汇总数据在D函数应用表的结构化引用计算出的汇总数据。无论表区域的数据如何扩展，这些统计数据都会实时计算，自动更新，具有完全的动态数据分析能力各单元格的公式设置请看下页，详见教材277页的介绍。11.2.2D函数与表结合构造动态数据分析报表纯数据库，非表格方式输入下列公式，并右复制：N3：=DSUM($A$1:$J$83,"库存量",N1:N2)N4：=DSUM($A$1:$J$83,"订购量",N1:N2)N5：=DSUM($A$1:$J$850,"再订购量",N1:N2)N6：=DAVERAGE($A$1:$J$850,$J1,N1:N2)D函数与表结合使用方式输入下列公式，并右复制：N11=DSUM(表3[#全部],表3[[#标题],[库存量]],N9:N10)N12=DSUM(表3[#全部],表3[[#标题],[订购量]],N9:N10)N13=DSUM(表3[#全部],表3[[#标题],[再订购量]],N9:N10)N14=DAVERAGE(表3[#全部],表3[[#标题],[订货费]],N9:N10)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位数字；将公式复制到D5时，将变成“=MID($C4,COLUMN(B1),1)”，因COLUMN(B1)结果为2，公式将提取C4中的第2位数字……11.4.1用行、列号函数定位与提取数据【例11.6】用行列号计算函数row，rows，column，columns以及文本函数mid，left，right，text进行数字的按位提取与转换。如图11.12所示。(4)在A8中输入并向下复制下面的公式，生成A8:A13中的数据公式：="第"&ROW(A1)&"期"公式利用Row函数分别生成了数字1，2，3，4。(5)在N8中输入公式=LEFT(RIGHT(TEXT($C8*100,"￥000;;"),COLUMNS(N:$N)))将此公式向下复制到N9，再将N8:N9中的公式向左复制到D8:D9即可生成D8:N9区域中的数字。11.4.1用行、列号函数定位与提取数据【例11.6】用行列号计算函数row，rows，column，columns以及文本函数mid，left，right，text进行数字的按位提取与转换。如图11.12所示。(6)在D10中输入下面公式，并将此公式向下复制到D11，向右复到到N10:N11。=MID(TEXT($C10*100,REPT("",10-LEN($C10*100))&"￥000"),COLUMNS($A:A),1)D10:N10区域共11个单元格，LEN($C10*100)计算出数字本身要占据其中的单元格个数，还有11-LEN($C10*100)个单元格没有数字填，其中一个单元格填写“￥”符号，因此还有10-LEN($C10*100)个单元格需要填写空白字符“REPT("",10-LEN($C10*100))”就重复生成需要填在数字前面的空白字符。“TEXT($C10*100,REPT("",10-LEN($C10*100))&"￥000")”创建了恰好够填满D10:N10区域的11个字符，Mid函数则应用COLUMNS函数计算出每次提出到相应单元格中的起始位置，提取恰当的字符并填写到对应单元格中。11.4.1用行、列号函数定位与提取数据【例11.6】(7)在N12中输入公式：=LEFT(RIGHT(TEXT($C12*100,"[dbnum2]￥000;;"),COLUMNS(N:$N)))将此公式向左复制到D12，即可生成D12:N12区域中的数字。本公式与前面的数字提取公式含义相同，只是在提取前首先用自定义格式“[dbnum2]”将数字转换为中文大写，然后再提取。11.4.1用行、列号函数定位与提取数据【例11.6】(8)在N13中输入以下公式，再将其向左复制到D13，即可计算出D13:N13的数据：=LEFT(RIGHT(TEXT($C13*100,"[dbnum1]￥000;;"),COLUMNS(N:$N)))公式中的“[dbnum1]“将数字设置为中文小写11.4.1用行、列号函数定位与提取数据【例11.6】(9)在O1并向下填充复制下面的输入公式，生成O列的编号“=ROWS($A$1:A1)”本公式利用Rows函数计算参数单元格区域中的行数，由于区域中结束单元格位置采用了相对应用，因此将它向下复制时会自动扩展，产生连续的数字编号。其特点是：如果删除表区域中的数据行，编号会自动更新。11.4.2用INDIRECT函数和名称查询其他工作表中的数据Indirect概述Indirect函数返回由文字串指定的引用。该函数能够对引用进行计算，并显示引用的内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用该函数。格式Indirect(ref_text,A1)其中ref_text为对单元格的引用，它可以是单元格的名称、引用或字符串。A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果A1为TRUE或省略，ref_text被解释为A1样式的引用。否则ref_text被解释为R1C1样式的引用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$6ADDRESS(2,3,1,false,"[Book1]Sheet1")

=[Book1]Sheet1!R2C3，ADDRESS(2,3,1,true,"[Book1]Sheet1")

=[Book1]Sheet1!$C$2。11.4.3用ADDRESS和OFFSET函数进行定位查找与数据提取应用情况在实际工作中，许多时间要根据工作表，单元格行、列位置动态引用单元格，在这种情况下应用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(E8,-4,-2)，E8上4行左2列，为C4，OFFSET(A8:D12,4,5)，A8下4行右5列与A8:D12大小相同的区域，结果为

F12:I16OFFSET(A8:D12,-4,5)的结果为F4:I8，OFFSET(E8:F11,-4,-2)结果为C4:D7，OFFSET(A1,,)等效于OFFSET(A1,0,0)，结果为A111.4.3用ADDRESS和OFFSET函数进行定位查找与数据提取【例11.9】某单位进行职工技能测试大赛，分为四组，每组6人，每人进行两次测试，测试达标通过率情况如图11.15中A1:H13所示。计算每组的平均通过率，如图中B15:C18区域所示；对数据进行规范化处理，如图中K1:Q13区域所示；提取每组的参赛职工名单，如图中K15:Q18区域所示。1．用Count和OFFSET函数在合并单元格生成连续序号如果需要在大小不同的合并单元格中生成连续的序号，可以使用OFFSET函数计算单元格位置，再用Count函数对偏移的单元格进行计数，则可生成连续的编号本例中选中A2:A13区域；输入公式=1+COUNT(OFFSET($A$1,,,ROW()-1,))然后按CTRL+ENTER，即可生成A列的编号注意：如果要编号的合并单元格大小不一，如有的由两个单元格合并而成，有的由三个或四个合并而成，则需要严格按上述两步骤操作。如果每个合并单元格大小相同，也可以在第一个合并单元格中输入上述公式后，再将其填充复制到其它单元格，生成需要的序号。2．用OFFSET从合并单元格提取数据在B15中输入公式=OFFSET($B$1,ROW(B1)*3-2,)向下复制到B18，即可将B2:B13区域中的组别提取到B15:B18中。OFFSET函数B2开始，通过ROW(B1)*3-2中相对引用单元格的变化，每间隔3行提取B列对应单元格的数据，即B2、B5、B8、B11，正好将各个分组提取完毕。3．用OFFSET提取和计算动态数据区域在C15中输入公式=TEXT(AVERAGE(OFFSET($C$1,ROW(C1)*3-1,0,2,6)),"0.00%")其中“OFFSET($C$1,ROW(C1)*3-1,0,2,6)”计算的结果为C1向下偏移2行右移0列，取2行高6列宽的区域，即C3:H4Average计算C3:H4区域的平均数，其结果为小数，再用Text函数将此小数格式化为小数点后面保留2位小数的百分数。将C15中的公式向下复到C18，在复制过程中，ROW中的C1引用分别变成C2、C3、C4，即可计算出每组的测试平均数4．用OFFSET重复生成合并单元格中的数据在K2中输入公式=IF(B2<>"",B2,OFFSET(K2,-1,))将其向下复制到K13单元格，即可生成K2:K13区域中的组别。注意：合并单元格的数据在左上角单元格中。公式首先判B2若非空，其计算结果即为B2，K2由此变为“第一组”，当将此公式复制到B3时，即变为“=IF(B3<>"",B3,OFFSET(K3,-1,))”，由于B3为空，公式的结果为OFFSET(K3,-1,)，即K2单元格中的值。将K2:K13区域中的公式向右复制到Q2:Q13即可生成K2:Q13区域的全部数据。5．用OFFSET提取工作表中固定间隔行的数据在K15中输入公式=OFFSET($B$1,ROW(B1)*3-2,COLUMN(B2)-2)向右复制此公式到Q15，再将K15:Q15中的公式向下复制到K18:Q18即可生成每组的测试职工名单公式ROW(B1)*3-2的结果为1，COLUMN(B2)-2为0，因此原公式的结果相当于“=OFFSET($B$1,1,0)”结果为B2单元格；复制到L15时变成“=OFFSET($B$1,ROW(C1)*3-2,COLUMN(C2)-2)”，相当于“=OFFSET($B$1,1,1)”，结果为C2……11.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函数提供了比lookup（或Vlookup、Hlookup）函数更多的灵活性，它可以在工作表的一行（或一列）中进行数据查找，并返回数据在行（或列）中的位置。如果需要找出数据在某行（或某列）的位置，就应该使用Match函数而不是Lookup函数在多数情况下，Match函数的结果并不是所需要的最终答案，而是作为lookup（Vlookup，Hlookup）的第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.5用match和index函数构造灵活的查询4.用INDEX和MATCH进行模糊查询【例11.12】某单位的职工收入表如图11.18的D～F列所示，现要计算每个职工应纳的个人所得税税率。为了便于数据对比，将各种收入所对应的个人所得税税率列于图11.18的A2:B10单元格区域内（若这些数据处于另一个独立的工作表中，查找方法完全相同）。

在G3单元格中下向下复制下面公式，模糊查找出G列数据=INDEX($A$3:$B$10,MATCH($F3,$A$3:$A$10,1),2)MATCH($F3,$A$3:$A$10,1)在A3:A10中找到最大的那个小于等于F3的单元格。F3的内容为6582，而A3:A10中最大的那个小于6582的数是5000，它位于A3:A10区域内的第8个位置，所以MATCH($F3,$A$3:$A$10,1)结果是8。因此，原公式就相当于INDEX($A$3:$B$10,8,2)，则该函数将返回A3:B10区域内的第8行、第2列所对应的单元格引用，即B10单元格。B10的税率为25%，这正好是工资6825所对应的个人所得税税率。11.4.5用match和index函数构造灵活的查询5.用INDEX和MATCH进行重复数据判断应用INDEX、MATCH和其它统计函数，可以查找或标识出工作表中的重复数据。例如，在图11.18中，要判断D列哪些姓名重复了，重复最多的是哪个姓名？（1）标识重复出现的姓名在H3中输入公式，将此公式向下复制到H12，标识出重复的姓名。=IF(MATCH(D3,D$3:D$12,0)=ROW(A1),"","重复")”原理Row(A1)=1，如果D3中的姓名在D3:D12区域中只出现一次，MATCH(D3,D$3:D$12,0)的结果为1，IF函数的结果为空白串""，在H3中就不会有显示；如果D3中的姓名在D3:D12中重复了，MATCH函数将返回该姓名最后一次出现的位置，这个位置一定不等于Row(A1)，IF条件不成立，函数结果为“重复”11.4.5用match和index函数构造灵活的查询5.用INDEX和MATCH进行重复数据判断应用INDEX、MATCH和其它统计函数，可以查找或标识出工作表中的重复数据。例如，在图11.18中，要判断D列哪些姓名重复了，重复最多的是哪个姓名？（2）找出重复出现次数最多的姓名在I3中输入公式即可找出D3:D12区域中出现次数最多的姓名=INDEX(D3:D12,MODE(MATCH(D3:D12,D3:D12,0)))原理MATCH(D3:D12,D3:D12,0)逐个查找D3:D12区域中每个姓名在D3:D12区域中出现的位置，重复最多的姓名，MATCH函数返回它的位置就最多。MODE是取众数的函数，它返回一组数中出现次数最多的数字，它会返回出现重复次数最多的姓名在D3:D12第一次出现的位置，INDEX再据此位置查询得取重复最多的姓名。11.4.6用Lookup函数进行表查找Lookup函数Lookup函数在一个大表中找出特定数据，并在其它工作表中引用查找结果，在工作中应用较广。功能从给定的向量（单行或单列单元格区域）或数组中查询出需要的数值。格式Lookup(x,r1,r2)其中：x是要查找的内容，它可以是数字、文本、逻辑值或包含数值的名称或引用。r1、r2都是只包含一行或一列的单元格区域，其值可以是文本、数字或逻辑值。r2的大小必须与r1相同。Lookup函数在r1所在的行或列中查找值为x的单元格，找到后返回r2中与r1同行或同列的单元格中的值。1、用lookup函数在普通工作表中查找数据【例11.13】某蔬菜供应商在一个工作表中保存蔬菜的单价和出产地，如图（a）所示。在另一工作表中保存销售记录，如图（b）所示。在图（b）中，蔬菜名和数量是实际输入的数据，产地和单价需要根据产品名从图（a）所示的蔬菜单价表中查询输入11.4.6用Lookup函数进行表查找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函数进行表查找案例解决方法（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函数进行表查找注意：①r1中的内容必须按升序排序，查找的字符文本不区分大小写。否则，Lookup函数不能返回正确的结果。②如果Lookup函数找不到x，则查找r1中小于或等于x的最大数值。如果x小于r1中的最小值，Lookup函数返回错误值“#N/A”。11.4.6用Lookup函数进行表查找2、在lookup函数通过对表的结构化引用查找数据对于例11.13中的数据查找问题，较好的方法是将蔬菜单价表保存在表中，然后在LOOKUP函数中通过表的结构化引用查找蔬菜单价，这样的优点是在表中添加新的蔬菜品种时，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.2用Vlookup函数进行表查找案例解决方法2）处理Vlookup错误如果没有找到相应的数据，Vlookup函数将返回#N/A错误信息，如果不处理这些错误数据，还会导致其它计算错误。例如，图11.23中G列通过Sum函数计算D、E、C三列数据的总和，从中可以看出F列的错误也导致了G列的#N/A错误，无法算出对应职工的总收入。在H2中输入公式：=if(isna(vlookup($a2,indirect(f$1&"!$a$1:$d$500"),4,0)),0,

vlookup($a2,indirect(f$1&"!$a$1:$d$500"),4,0))其意思是如果Vlookup查询有错误，if函数的值为0，否则IF函数就取Vlookup查询的结果。即用0代替了Vlookup的错误值11.4.2用Vlookup函数进行表查找（3）Vlookup和Indirect相结合构造通用查询由于基本工资、奖金和课时费三张数据表的结构完全相同（见图11.22(b)(c)(d)），这些表的第四列数据是要查询提取的数据。此外，图11.22(a)中D1:F1对应列的标题名称与其数据所在的工作表名称对应相同，可用Indirect函数将此区域中的列标题取出构造出要查询出的工作表名称和区域，创建通用的数据表查询公式。因此，图11.23中基本工资、奖金和课时费的查询和提取，可用下面的方法实现。在D2中输入公式，向右复制此公式到F2，向下复制此公式到最后一个职工的数据行，即可查询出所有职工的基本工资、奖金和课时费=VLOOKUP($A2,INDIRECT(D$1&"!$A$1:$D$500"),4,0)公式的巧妙在于用INDIRECT函数生成了工作表名称和数据区域：INDIRECT(D$1&"!$A$1:$D$500")”的计算结果为“基本工资!$A$1:$D$500”11.4.2用Vlookup函数进行表查找（4）Vlookup、Match结合构造动态列序的查询许多时间需要从原数据表中查询提取多列数据，形成报表或构造综合信息查询。如果查询结果数据的次序与原始数据表中的顺序相同，则可以通过查询公式的复制快速完成；如果次序不同，则需要对复制后的查询公式进行修改。在Vlookup函数中应用Match函数动态变通数据表行列位置，可以构造满足这一需求的动态序列查询。【例11.16】某学校职工收入明细数据如图11.24中A11:G289区域所示。由于人数太多，需要按照职工编号查询该职工的各项收入明细。如图中A2:G4区域所示。要求在C2中输入职工的编号后，就查询显示出该职工的姓名、单位、资金、基本工资等数据。原始数表在C2中输入职工编号，就会从原数据表中查询到该职工的各项数据在B8、C8中分别输入职工姓名、单位，从原数据表中查询到该职工的各项数据在C4中输入下面的公式，将它从C4向右复制到G4，即可查询出C2中职工编号对应的单位、奖金、基本工资、课时费和收入总计。=VLOOKUP($C2,$B$11:$G$289,MATCH(C3,$B$11:$G$11,0),0)公式含义不难理解：在B11:G289区域中查询C2中的职工编号，但找到后的数据列由MATCH(C3,$B$11:$G$11,0)决定，C3中为奖金，它位于B11:G11区域的第4列，所以结果为4，因此VLOOLUP函数将返回B11:G289区域中找到职工号同行第4列的数据，即该职工的奖金；当将C4中的公式复制到D4后，MATCH中的相对引用会改变，变成MATCH(D3,$B$11:$G$11,0)，其结果为3……Vlookup、Match结合构造动态列序查询职工的奖金、基本工资、课时费、收入总计问题：通过观察可知，查询报表头B3:G3和原数据表头B11:G11的次序不同，但是B3:G3区域中的标题都在源数据表中职工编号的右边，而职工编号是从原数据表中查询数据的依据。像这样的查询可以用在Vlookup函数中用Match定位要查询的数据列，实现动态查询。但是姓名列数据在原数据表中位于查询依据列（职工编号）左边，就不能用这种方式查询。因为VLOOKUP函数要求要查询的数据必须位于原表中查询依据列的右边。（5）Vlookup和Choose结合构造向左的列查询问题：如何根据职工编号查询职工姓名呢？VLOOUP函数只能在查询数据区域的第1列（最左列）查找指定的数据，但在原数据A11:G289区域中，姓名位于职工编号的左边，不能用Vlookup函数直接查询。对于这种情况，可以用Choose函数通过数组的方式，对原数据表的列进行交换，构造出一个符合Vlookup函数查询需求的内存数组，再用Vlookup函数从此数组中查询。

在B4中输入下面的公式，即可根据C2中的职工编号查询出对应的职工姓名。=VLOOKUP(C2,CHOOSE({1,2},B11:B289,A11:A289),2,0)公式将先执行Choose函数，先用数组{1,2}中的第一个元素1选取了B11:B289数据区域，再用数组元素2选取A11:A289，构造了一个B列在前，A列在后的内存数组。最后VLOOKUP从该内存数组中查询（5）Vlookup和Choose结合构造向左的列查询从根据职工编号查找姓名的事例可知，通过CHOOSE函数可以交换原数据表的列次序，形成具有新列次序的内存二维数组。由此可得到启示：通过Choose函数，可将原表职工编号交换到第一列，并按查询表数据列的先后次序将原始数据表交换次序形成内存数组，然后可以通过VLOOKUP函数一次性动态查询出职工的姓名、单位、奖金、基本工资等全部数据。在B6中输入下面的公式，将其向右复到到G6，可查询得到第6行全部数据，请思考：此公式是如何查询提取数据的呢？=VLOOKUP($C$2,CHOOSE({1,2,3,4,5,6,7},$B11:$B400,$A11:$A400,$C11:$C400,$E11:$E400,$D11:$D400,$F11:$F400,$G11:$G400),COLUMN(B1),0)CHOOSE函数构造了数据列序如下的内存数组，VLOOKUP函数在这个内存数组中查询数据，找到职工编号后，只需要依次提取数据即可，因此用了COLUMN函数提取数据列B列A列C列E列D列F列G列职工编号姓名单位奖金基本工资课时费收入总计

（6）用数组公式构造多列组合查询此查询需求有两个问题：其一是查询关键字由两个字段组成；其二是查询列不在数据区域的第一列。应用Choose函数进行数据列交换的技术和数组公式，能够轻易完成查询要求。在D9中输入下面的公式:=vlookup($B9&$C9,choose({1,2,3},$A11:$A400&$C11:$C400,$B11:B400,$E11:$E400),column(B2),0)输入完成后按Ctrl+Shift+Enter组合键，然后将此公式复制到E9，即可查询出职工编号和奖金。公式的利用了CHOOSE函数构造内存数组，数组第1列是由原数据A列和C列组合成的，请同学们结合前面的分析思考此公式的查询原理。

需要根据多个组合字段查询其它信息。例如，要根据职工的姓名和单位信息才能查询职工编号和奖金（不同单位有同名职工，不能据姓名查编号）。

要求，在B9输入职工姓名，C9输入职工的单位后，在D9中就显示出该职工的编号，在E9中显示出他的奖金。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.4.7用Vlookup函数进行表查找说明在Excel中还有一个常用的查找函数Hlookup，其用法与Vlookup函数完全相同。它按行方式进行数据查找，而Vlookup函数按列方式进行数据查找。因时间所限，这里不作介绍，同学们可从Excel的帮助信息中查找该函数的用法。11.5用数据库函数进行查找统计1、概述在Excel中，数据库是指每列数据都有标题的数据表。Excel提供大约12个专用数据库函数来简化这种数据表的数据统计和数据查找工作，这些函数都以D开头，所以也称为D函数。D函数有相同的调用形式，相同参数表，格式如下。Dname(database,field,criteria)其中的Dname是函数名，它可以是Dsum、Daverage、Dget、Dcount、Dcounta、Dmax、Dmin等。各函数的功能如其名字所示，Dsum求总和，Daverage求平均数，Dget查找数据，Dcount统计数字个数，Dcounta统计文本和数据的个数，Dmax求最大数，Dmin求最小数。11.5用数据库函数进行查找统计案例【例11.17】某校某专业共有224名学生，某次期末考试的“数据库系统应用”课程的成绩表如图所示。现在要查找每位学生的成绩，希望输入学号后，就能够得到该生的各种详细数据，如图的J1:M8区域所示。此外，还希望对各班的考试情况进行简单的统计分析，能够随时查看各班的考试人数，最高成绩，高低成绩，及缺考人数等，如图的J10:N17区域所示。11.5用数据库函数进行查找统计11.5用数据库函数进行查找统计案例解决方法（1）在K13中输入计算上机平均成绩的公式：=DAVERAGE(A4:H227,"上机成绩",J12:J13)（2）在M13输入计算综合平均成绩的公式：=DAVERAGE(A4:H227,"综合成绩",J12:J13)（3）在K15输入计算缺考人数的公式：=DCOUNTA(A4:H227,"期末考试成绩",J12:J13)-DCOUNT(A4:H227,"期末考试成绩",J12:J13)（4）在M15输入计算最高成绩的公式：=DMAX(A4:H227,"期末考试成绩",J12:J13)（5）在K17输入计算最低成绩的公式：=DMIN(A4:H227,"期末考试成绩",J12:J13)（6）在M17输入计算考试人数的公式：=DCOUNT(A4:H227,"淘汰率为4%下的成绩",J12:J13)11.6用数组公式进行查找统计数组公式在进行数据的汇总、查询方面有着较强的实用效能，能够解决实际工作中的许多问题。案例【例11.18】某电脑配件经销商将每天的销售数据记录在Excel的工作表中，如图的A1:E18区域所示。该经销商希望随时查看各配件的累计汇总数据，以便为进购做出调整。累计汇总数据表如图中的G1:I10所示，希望在I3中输入月份的数字时，就累计出从1月到该月各种电脑配件的销售总数量和销售总金额。

11.6用数组公式进行查找统计将数据区域转换成表:表1当然,也可以不用表而用普通数据区域查找统计.但表具有动态扩展特性,更能满足本表的统计需求.公式中对表进行了结构化引用11.6用数组公式进行查找统计案例解决方法（1）为了简化输入，可建立I3单元格的“有效性”列表输入，从列表中选中月份。同时,将A:E的数据区域转换成表:表1（2）在H5中输入累计CPU销售数量的数组公式。即公式输入完后，按Ctrl+shift+Enter=SUM(IF(MONTH(表1[日期])<=$I$3,IF(表1[商品名称]=G5,表1[销售数量])))（3）将H5中的公