在EXCEL的基础数据表中,输入一定要规整,同一字段的数值类型必须统一,不要有合并单元格,否则会给统计分析带来困难。而在报表中则可以进行合并、拆分、采取不同的方式力求达到需要的视觉效果和美观。
然而对于大多数用户而言,报表和基础数据表不分,如下图所示:同一基地的项目有多个,因此将基地进行了合并,看上去更加直观清晰,但是,如果我们要查找某个项目在哪个基地,怎呢查找呢?
首先假设我们的基础数据比较规范是这个样子的:
在E2中输入=VLOOKUP(D2,$A$2:$B$13,2,0),下拉填充公式即可,这是VLOOKUP的标准用法。
如果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))
再回到开篇位置的例子,这是一个合并单元格下的反向查找:直接上公式:
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
联系客服