第5章海量数据分析的利器初识查找引用函数

Excel201012大类，除了所谓不需要学习都会的逻辑函数外，最常用的就是查找与函数，几乎每个用户在工作中都接触过此类函数。里仅就一些常见的函数及基础的应用做一些讲解，带领读者初识查找函数。认识函数中的大众查找与函数的普及和其中的一个函数有关。谈到函数公式，绝大多数的Excel用户会感到很头这个函数就是人见人爱的函数领域中的大众——VLOOKUP一个典型的单条某公司月底结账时需要编制进销存报表，进销存报表中D列“本期销售”需要从H列“销售汇总”中取数，匹配的原则为A列“货号”与G列“货号”相符，如图5.1所示。5.1 5.2VLOOKUP VLOOKUP（要查找的值，查找的区域，查找区域中返回的列，查找模式 5.1VLOOKUP函数420等价于FALSE，精确匹配，如果为1或者TRUE，模糊匹D2单元格返回的值来看一下函数公式的运算原演示如图5.3所示。5.310多行，实际工作中的数据源可能有成百上千行，一样只要用一个公式向下就能解决问题。学会这个函数后在实际工作中遇到类似问题可以大幅度提高工作效率。VLOOKUP函数的局限具有优势，但也有其局限性。使用VLOOKUP函数最常见的困惑有如下几种：查找与函数。正如一个人的能力是有极限的，一个函数的能力也是有限的，运用函数组合能够解决本节示例文件：《5.1认识函数领域的大众.xlsx突破 函数反向查找的限一个反向查找的如图5.4所示，学生的学号与是一一匹配的，如已知学号要查找，可以用VLOOKUP函数解决，F2单元格公式如下： 但反过来已知要查找学号，直接用VLOOKUP函数就问题分

图5.4已知查询学这里直接用VLOOKUP不行，原因在于在查找区域中需要返回的“学号”在“”的左边，也就是不符合需要查找项目“”在查找区域A2:B10的第一列的条件。知道了问题的所在，解决就容易了。如果在“学号”的左侧有一列“”，这个问题就迎刃而解了。一个笨方法是在A列“学号”之前插入一列，原B列“”的内容，这样就可以直接使用VLOOKUP函数了。助列，重构数据源的效果。在F5单元格录入如下公式： 公式解整个公式外层还是VLOOKUP函数，关于这个函数的用法不再赘述公式中使用了一个IF函数作为VLOOKUP函数的第2参数，在编辑栏中选取这一段函数“IF({1,0},B2:B10,A2:A10)”，按F9键，返回结果为：{"管莺菲","A001";"石永绍","A002";"越俊","A003";"崔亨","A004";"于成中","A005";"狄影淑","A006";"糜彩青","A007";"秋彩伊","A008";"裘健栋5.5IFIF函数，返回了一个内存数组，相当于重构了一个区域，而这个重构的区域正好满足使用VLOOKUP查找时查找值位于被查找区域第一列的要求。函数套路——IF{1,0}解IF函数第一参数不能写成{1;0}，不要小看仅一个符号的差异，逗号表示横向，分号表示纵向，使用{1;0}IF2*1的数组9*1F9测试的结果是{"管莺菲";"A002";#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}915.6所5.6IF{1,0}如果能够理解IF{1,0}的返回结果，读者可以自试以下公式IF{1,0}构建内存数组的方法很经典，但相对理解有一点难度。查找的问题并非一定要通过VLOOKUP函数来解决，使用其他函数也能轻松搞定。在F5单元格录入如下公式： 公式解5.2MATCH30回结果如图5.7所示。5.7INDEX+MATCHINDEX+MATCH函数组合相当于把原来VLOOKUP一个函数的工作分配给了两个函数来完成。虽VLOOKUPVLOOKUP函数要多很多，至少 “销售汇总”区域$H$1:$H$13返回货号对应的销售额。IF{1,0}INDEX+MATCH方法比VLOOKUP函数示例是不需要使用数组公式结束的，因为IF函数的第二和第三参数仅是了工作表中的单元格区域，并没有计算，如在的同时还有计算，就需要使用数组公式结束。本节示例文件：《5.2VLOOKUP函数反向查找的限制.xlsx轻松应对多条件相对于单条件查找，多条件查找肯定不能简单应用VLOOKUP函数。多条件查找的2种解决A:C列，E2单元格中设置了数据有效性，取值为产单元格取值变动而变动，如图5.8所示。问题分

5.8IF{1,0}VLOOKUPG2单元格中输入如下公式，并按 公式解也使用连接符&将两个数据区域连接。将IF函数的返回结果放入单元格区域中，如图5.9所示。5.9IF{1,0}中使用连接符&2个条件，将多条件查找变成了单条件查找，后续的问题就简单了，INDEX+MATCH函数组合也可以解决，在G2单元格中录入如下公式，并按三键结束。 公式解特殊的多条件双向查的条件分别在第1行和第E列，要求在F2单元格中写一个公式，向下向右完成。

5.10 公式后的E列是不变的，的行会改变，所以对于E2单元格，需要使用行相对列混合方式$E2；对于的F1单元格判断依此类推。多列查入一个公式，向右后能在A9单元格变动的情况下返回对应的、和职务。5.11找到这些操作之间的内在规律。先写出单独解决B9、C9、4。如果有一个函数在B9单元格中返回2，公式向右时能变成3、4，这个问题就解决了。 这个公式中的关键是COLUMN函数。COLUMN函数返回参数指定单元格的列号，在省略参数的情况下返回COLUMN函数所在单元格的列号。5.12COLUMN5.12所示，列出了在省略参数的情况下函数公式“=COLUMN()B、C、D列的返回值分别是2、3、4，也就是第9行中VLOOKUP函数第3个参数的取值。如果这个公式不是放在B:D列，写法就要改变一下，COLUMN函数要加上参数，公式如下 5.13COLUMN点与ROW函数的用法类似。INDEX+MATCH函数组合。在B9单元中录入如下公式，并向右完成 这里使用了两次MATCH函数，分别定位两个条件（行方向的，列方向的要查找项目在 轻松应对多条件查找.xlsx查找满足条件的最后一什么情况呢？在精确查找模式下，不管是单条件还是多条件查找，不论是用VLOOKUP函数、VLOOKUP{1,0}或者INDEX+MATCH函数组合，返回结果都仅是满足条件的第一个值。LOOKUP函数应用于模糊查问题分VLOOKUP公式 解决方

5.14VLOOKUP查找，在F2单元格输入如下公式，向下，如图5.15所示。 公式解

5.15LOOKUPLOOKUP函数，LOOKUP函数不同于VLOOKUP函数的地方是，其本身就是一个模糊查找的函数，不像VLOOKUP函数通过第4参数来控制查找模式。很麻烦吗？答案是否定的，这里使用了LOOKUP(1,0)的函数套路。函数套路——LOOKUP（1，0）解=LOOKUP（1，0/(条件区域=条件)，返回值区域 0除以判断的结果TRUE或者FALSE，得到的值为0或者#DIV/0!。LOOKUP函数第2参数部分，即 F9后得到结果为{0;#DIV/0!;0;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!}。把这一段的计算结5.16BE2单元取值“A0，其他行返回值为“#DIV/0!”。这一段公式的实质是构建了一个辅助列，作为LOOKUP2个参5.162200值，肯定找不到“1”，这时模糊查找发挥作用了。Excel0值后会认为下面还有更大的值，继续查找，直到定位到最后一个0值为止。这样查找就能返回E2单元格中货号A对应的最后一笔成交单87。对于货号B和C的查找，读者可以按照上述方法自试一下，加深对于LOOKUP(1,0)函数套路用模糊查找做精确查找的条件，也不管是不是所谓的反向查找都可以完成，但其前提是要用LOOKUP（1,0）的函数套路。INDEX+MATCH函数组合同等的效果。 5.17LOOKUPLOOKUP（1,0）函数套路可以起到精确查找的效果。 查找满足条件的最后一个值.xlsx巧妙利用函数的模糊LOOKUP函数，VLOOKUPMATCH函数也可运用于模糊查找，MATCH函数还有2种模糊查找模式。典型的区间查找个人所得税计之前介绍过，VLOOKUP41或者TRUE缴纳社保等其他事项的影响，如图5.18所示。

5.18=*-如图5.19所示，这是一个网上的税率表，其中B列的“分段”数字是自行添加的，读者可以观察一下B列“分段”数字与A列“含税级距”之间的关系。5.19做好准备工作后，可以在“个人所得税计算表”的D2单元格输入如下公式并向下=C2*VLOOKUP(C2,税率,2,1)/100-VLOOKUP(C2,税率 这个公式使2VLOOKUP函数，第1个函数取得“税率”，第2个函数返回“速算扣除数”。如对于计算结果不理解的话，可以用F9键来分析一下公式的分段运算结果。VLOOKUPF91,005A，应税额21,500对应的税率为25%，速算扣除数是1,005。果？这就是VLOOKUP函数的模糊查找在起作用。21,5009,0009,000所在行对应的税率和速算扣除数。如图5.20。5.20 =C2*LOOKUP(C2,税率表!$B$2:$B$8,税率表!$C$2:$C$8)/100-LOOKUP(C2,税率 3个参数，第二个LOOKUP2这里分别运用了LOOKUP函数的向量形式和数组形式向量形式：LOOKUP(C2,税率表!$B$2:$B$8,税率表 数组形式：LOOKUP(C2,税率 在分段模糊查找时可以用LOOKUP代替VLOOKUP，且比VLOOKUP更加灵活学生成绩分如图5.21所示，第4章中学生成绩分级的例子，在F列增加一个成绩“分段”值，比较F列例图5.19中也能得到验证。这里使用VLOOKUP函数和LOOKUP函数都能解决，公式 5.21比VLOOKUP运用更加灵活。模糊查找规律的总使用模糊查找机制时，VLOOKUP函数查找区域（LOOKUP数组形式）的第一列或者 巧妙利用函数的模糊查找.xlsx处理跨工作表都是在一张工作表中，如涉及在多个工作表中查询，就需要使用其他查找与函数。5 一个跨工作表查询的实某企业在各地有若干家，统计各家的销售量，每个月一张工作表，表结构一致，A2B2单元格设置了数据有效性下拉列表，要求C2单元格数字随A2和B2单元格取值变化而变化，如图5.22所示。问题分

5.22=VLOOKUP(B2,一月 VLOOKUP2参数中的区域“A:B”之前加上了这个区域所在工作表名称“一月”及“!”，即“一月!A:B”。如果把“一月”当成一个变量，用单元格A2来代替，是否就可以实现VLOOKUP函数第2个参数的查找区域随着变量月份（A2单元格）的变动而变动？答案是否定的，使用公式“=VLOOKUP(B2,A2&"!A2:B9",2,)”结果会报错“#VALUE!”，对第2但就是不能返回正确的结果，如图5.23所示。解决方

图5.23错误的公“查询”表的C2单元格中输入如下公式： 公式解这个公式外层是最常用的VLOOKUP函数，用了精确查找；内层使用了INDIRECTF9INDIRECT函数中参数的运算结果，发现和之前错误的公式中一样，都是“"一月!A2:B9"”，如图5.24。5.24INDIRECTExcelINDIRECT函数的说明中有一句话很值得玩味“如果需要更改公式中对单元格的，而不更改公式本身，请使用函数INDIRECT”。改变对单元格的而不更改公式本身，可以理解为对于公式中了变量（单元格），实现了内容与变量（单元格）的联动。5.3INDIRECT2TRUE或省略为A1样式的，FALSE为R1C1样式后把内容放到INDIRECT函数中。需要注意的是的单元格，需和的文本用连接符&连接，的文本本身外面要加上一对半角英文状态下的双引号。这个例子已可以使用INDIRECT的R1C1方式，公式如下 比较这两个公式，使用R1C1时，INDIRECT函数不能省略第2参数。这里“R2C1:R9C2”相当于A1下的“A2:B9”。 工作表名的规 差异仅在的工作表外面加上了一对半角英文状态下的单引号“’”。如果不能确定何时需要这INDIRECT函数时一直加上，多加Excel是不会报错的。当然也可以在单元格中测试一下跨工作表的，如“3月”工作表的A:B列，如图5.25所示。5.25在单元格中测试跨工作表的 处理跨工作表查找.xlsx查找返回指定查找返回横向请看如下示例，已知各个业务员1-6月份的销售额，A10单元格中为业务员，B10单元格中为使用一个公式计算某个业务员若干月份的销售额合计，如图5.26所示。

5.26数组的技巧，略有点麻烦。而使用函数OFFSET仅是一个最基础的应用。 OFFSETSUM函数求和。OFFSET函数5个参数2个参数使返回一个1行若干列（B10单元格的取值）的区域。整个过程如图5.27所示。5.27OFFSET5Excel中参数最多的函数之一，也是超级难解的函数之一。本章仅讲解最基础的应用，先从函数的5个参数开始理解。5.4OFFSET5始的上方）1始的左边） 初次接触OFFSET函数会比较难理解，这里将以几个示意图来帮助理解省略4、5参

5.28OFFSET图OFFSET1A1235参数5.29OFFSET5.29A12单元格返回值出错，原因在于函数返回的是一个单元格区域而不是一个单元格，把为其他函数的参数，如再外套SUM函数等。5.30OFFSET通过以上三个演示，可以得出关于OFFSET函数的以下几个规律谈谈查找函数和函和OFFSET属于函数，同属查找与函数大类，其实并不完全一样。5.31这个问题可以使用VLOOKUP、INDEX、OFFSET和INDIRECT四种函数组合，公式分别如下=VLOOKUP(A11,A2:G7,MATCH(B11&"月=INDEX(B2:G7,MATCH(A11,A2:A7,),MATCH(B11&"月=OFFSET(A1,MATCH(A11,A2:A7,),MATCH(B11&"月=INDIRECT("R"&MATCH(A11,A1:A7,)&"C"&MATCH(B11&"月INDIRECT函数R1C1样式外，其他的函数组合并不陌生，返回结=VLOOKUP(A11,A2:G7,MATCH(B11&"月=INDEX(B2:G7,MATCH(A11,A2:A7,),MATCH(B11&"月=OFFSET(A1,MATCH(A11,A2:A7,),MATCH(B11&"月=INDIRECT("R"&MATCH(A11,A1:A7,)&"C"&MATCH(B11&"月=SUM(B5:INDEX(B2:G7,MATCH(A11,A2:A7,),MATCH(B11&"月=SUM(B5:OFFSET(A1,MATCH(A11,A2:A7,),MATCH(B11&"月=SUM(B5:INDIRECT("R"&MATCH(A11,A1:A7,)&"C"&MATCH(B11&"月以B5单元格使用区域运算符（冒号）连接上述公=SUM(B5:INDEX(B2:G7,MATCH(A11,A2:A7,),MATCH(B11&"月=SUM(B5:OFFSET(A1,MATCH(A11,A2:A7,),MATCH(B11&"月=SUM(B5:INDIRECT("R"&MATCH(A11,A1:A7,)&"C"&MATCH(B11&"月查找结果报错的IFERROR函数消5.32所示的例子，查找结果中出现了“#N/A”，原因在于某些货号在本期没有销售，在“销5.32VLOOKUP从2007版开始，Excel提供了一个全新的消错函数IFERROR，使用这个函数可以快速函数计值，也不会影响到E IFERROR2个参数，第一个参数是需要消错的公式，第二个参数是第一参数计算结果出错2003版中的消错再外套IF函数处理，上述公式需更改为如下： 其中错误的公式如VLOOKUP函数要出现2次，明显不如使用IFERROR函数消错简 查找结果报错的处理.xlsx查找函数之实战应计算分类百分问题分

5.33依次为C列的单元格，从C2单元格到C13单元格，但除数不一样，如图5.33中F列分析。除数的单元格区域是分段变化的，第一段是C5、第二段是C10、最后一段是除数单元格在A列的文字都有“小计”两个字对于返回单元格分段变化，可以考虑改变公式中的单元格类型，在公式向下时不解决方=C2/VLOOKUP("*小计 公式解P第2A21下，域会依次变为“A3C$3”、“A4$3”、“A5C13”、…“1$3”、“A1C$3”，实现查找查找客户首次还款月如图5.34，需要在J列填列各个客户当年第一笔还款的月份问题分

5.34MATCHINDEX函数；也可以在解决方在J2单元格录入如下公式，按三键结束，向下完成=MATCH(,0/B2:I2,)&"月 公式解J2单元格，在编辑栏中选取这一段公式“0/B2:I2”，按F9后返回结果为：“{#DIV/0!,#DIV/0!,0,#DIV/0!,#DIV/0!,#DIV/0!,0,#DIV/0!} 5.35这里使用的MATCH0，即返回第一个0所在的位置3，然后用连接符&连接上“月”，即得到首次还款所在月份。 格区域中返回MATCH函数查找得到位置的月份。使用B$1:I$1的写法是考虑到公式要向下，需要另类的多条件世界之大真可谓无奇不有！有些用户做的表格非常奇怪，Excel中很多技巧就是来源于不规范的表5.36所示，A：D1,044行，G3：I13单元格中是各个供应商的进货数量，在J列中对于有进货的供应商查找对应物料代码的进货价格。问题分

5.365.37要查找的表格虽然不符合制表规则，但还是有规律可循的。如果能在J3单元格公式向下时引用的供应商名称会随之变动，还将回到多条件查找的套。再次一下函数帮助文件中的经典语句——“如果需要更改公式中对单元格的，而不更改公解决方在J3单元格中录入如下公式，按三键结束，向下完成 公式解函数的R1C1。公式的部分是“SUM((G3:I3<>"")*{7,8,9})”，这一段的含义是判断G3：I3单元格区域是否非的列号，SUM求和的结果就是需要查找的供应商名称所在单元格的列号。在J3单元格中进入编辑栏，选取该段公式，按F9的返回结果是7，INDIRECT函数的为“"R2C7"”，即供应商名称所在的G2单元格。这个会随着公式向下而变化，比如在J4单元格中，