编按: 介绍3种含合并单元格在内的数据查找:第一种合并单元格是查找值;第二种合并单元格是返回值;第三种合并单元格是多条件查找的条件之一。 小窝分享3种含合并单元格的数据查找。 第一种:合并单元格是查找值 譬如下方,查找各合并单元格对应的金额。 直接用Vlookup查找,得不到完整的结果: 之所以如此,是因为合并单元中的数值只保存在第一个单元格中,其他都是空的: 这时适合用坐字法查找: =VLOOKUP(LOOKUP("坐",$B$1:B2),$E$2:$F$6,2,) 说明: ①LOOKUP("坐",$B$1:B2),这是简写公式,完整的是LOOKUP("坐",$B$1:B2, $B$1:B2) 。“坐”在文本中是一个极大值,根据二分法原理,会返回区域$B$1:B2中最后一个不为错误值的文本,即B2的“销售一室”。当公式拖动到C4单元格,返回区域变成$B$1:B4,返回的最后一个文本仍然是B2中的“销售一室”;公式应用到C6单元格,返回区域变成$B$1:B6,返回的最后一个文本就是B5中的“销售二室”。依次类推。 ②VLOOKUP(①,$E$2:$F$6,2,),VLOOKUP精确查找。 第二种:合并单元格是返回值 譬如下方,查人员所在部门,而需要返回的部门位于合并单元格中。 如果用VLOOKUP直接查找“杨过”,对应的B4单元空是空值,得不到需要的“销售一室”: 同样可以使用坐字法查找: =LOOKUP("坐",INDIRECT("b1:b"&MATCH(E9,A1:A15,))) 说明: 查找“杨过”在A列中的位置数,然后将其作为部门查找范围的终点位置。 第三种:合并单元格是多条件查找的一个条件 譬如,查找合并部门的某产品的销售金额。 很显然,不可能按普通的多条件查找来做。 有两个解决思路: 思路1,查找合并单元格的位置作为第二个条件的查找范围起点位置。 思路2,在内存中将合并单元格拆解填充出来后进行多条件匹配。 思路1: 下面两个公式都可以。 =VLOOKUP(O2,INDIRECT("k"&MATCH(N2,$J$1:$J$15,)&":l15"),2,) =VLOOKUP(O2,OFFSET($K$1:$L$1,MATCH(N2,$J$1:$J$15,)-1,,2,15),2,) 思路2: =SUMPRODUCT((LOOKUP(ROW(2:15),ROW(2:15)/(J2:J15<>""),J2:J15)=N2)*(K2:K15=O2)*L2:L15) 公式中LOOKUP(ROW(2:15),ROW(2:15)/(J2:J15<>""),J2:J15)可以获得合并单元格拆分填充效果: |
|