前言在EXCEL的基础数据表中,输入一定要规整,同一字段的数值类型必须统一,不要有合并单元格,否则会给统计分析带来困难。而在报表中则可以进行合并、拆分、采取不同的方式力求达到需要的视觉效果和美观。 然而对于大多数用户而言,报表和基础数据表不分,如下图所示:同一基地的项目有多个,因此将基地进行了合并,看上去更加直观清晰,但是,如果我们要查找某个项目在哪个基地,怎呢查找呢? 1常规查找首先假设我们的基础数据比较规范是这个样子的: 在E2中输入=VLOOKUP(D2,$A$2:$B$13,2,0),下拉填充公式即可,这是VLOOKUP的标准用法。 2反向查找如果EXCEL的基础数据表是这个样子的: 这是一个反向查找: 可以在E2中输入=VLOOKUP(D2,IF({1,0},$B$2:$B$13,$A$2:$A$13),2,0) 或者使用INDEX,MATCH组合函数,=INDEX($A$2:$A$13,MATCH(D2,$B$2:$B$13,0)) 3合并单元格下的反向查找再回到开篇位置的例子,这是一个合并单元格下的反向查找:直接上公式: E2=LOOKUP("座",OFFSET($A$2,,,MATCH(D2,$B$2:$B$13,0),1)) 这是一个嵌套函数: 从外到内以此为: 1、LOOKUP(“座”,查找区域) 2、查找区域=OFFSET($A$2,,,行数,1) 3、行数=MATCH(D2,$B$2:$B$13,0) 分别解释一下,从内到外 1、行数=MATCH(D2,$B$2:$B$13,0) 查找D2在区域B2:B13中的位置,H1297在$B$2:$B$13区域的位置为第4个 2、查找区域=OFFSET($A$2,0,0,4,1) 意思是返回一个由A2偏移后的新的区域为A2:A5,即A2偏移0行,0列,新的区域的行数为4,列数为1。 3、LOOKUP(“座”,A2:A5) 在A2:A5区域查找”座”,如查找不到,则返回最后一个文本,A2的值为”烟台”。 如果要查找最后一个数字,需用LOOKUP(9E+307,区域) 9E+307为9乘以10的307次方,表示一个很大的数;“座”可以认为文本的最后一个字符,当LOOKUP查不到这两个数时,则返回区域的最后一个数值或文本。 总结各种情况下的LOOKUP使用方法,一定要活学活用 一定要规范基础数据的输入,将报表和基础数据分开 嵌套函数要先写括号,保证每个函数的完整性! 求最后一个文本和数值的方法 END |
|