#excel中,表格查询无处不在,部门与部门之间会相互用到对方数据,有时候表格会有【合并单元格】、一级分类下面还有二级分类。
数据源表:
待填充的表:
方法一:=Lookup()
lookup是一个比较强大的查询函数,用法也纷繁复杂。
针对几个变量的具体查询,此函数变形为:
=lookup(1,0/(查询条件1=查找值1)*(查询条件2=查找值2),返回值区域)
注意:函数中分子必须小于等于查找值,0是最百搭的。
方法二:用零部件的方式构建大查询
我们熟知的大查询有:=vlookup()、=sumif()、=sumifs()、=index()
以=vlookup为例,=vlookup(查找值,查找区域,第几列,精确查找)
【查找值】在待填充表和数据源表里要一致;
【查找区域】根据年、月不同而变化,但是是有规律的;
【第几列】有规律;
【精确查找】0.
在此篇开头的图片举例中,【查找区域】的规律:
- 年份可以确定查询的结束列;
- 月份可以确定查询区域的起始行和最终行。
需要用到的函数(定位功能):
- =match(要查询的值,查询范围,0)返回的是数字;
- =address(行数,列数,引用类型)(这里需要你百度一下这个函数返回结果)
- =left(),这个跟adress搭配,返回列,也就是A、B、C、D等(思考:如何辨别提取几个字符,A-Z为1个,AA-ZZ为2个,以此类推);
- =countA()与match和adress函数结合,返回【查找范围】最后一列;
- =indirect()重中之重,自己构建的【查找范围】一定要用这个函数引用,不然它们堆叠在一起只是没有特殊的意义文本;
- Tips:文本之间要用&连接,不要忘记":".
这是我的查找函数,做个辅助理解,不理解也没关系,相信你自己能写得出来:
=VLOOKUP($B22,INDIRECT("'预算&指标'!$B"&"$"&MATCH($B$5,'预算&指标'!$A:$A,0)&":"&"$"&LEFT(ADDRESS(1,2+8*COUNTA('预算&指标'!$A$1:INDIRECT(ADDRESS(1,MATCH($B$4,'预算&指标'!$1:$1,0),4))),4),1)&"$"&MATCH($B$5+1,'预算&指标'!$A:$A,0)-1),2+2*COUNTA('预算&指标'!$A$1:INDIRECT(ADDRESS(1,MATCH($B$4,'预算&指标'!$1:$1,0),4),1)),0)
其中‘预算&指标’就是数据源表。