遇到合并单元格的查询和隔行填充问题,这样玩!

发布时间:2020-01-07 来源:网络


表格中如果存在合并单元格,除了经常会遇到序号填充、统计、排序和筛选等问题外,还会遇到合并单元格查询和隔行填充问题。


1、合并单元格数据查询引用


如下表所示,表格中的销售区域列含有合并单元格,我们如何根据产品查询引用对应的销售区域和销售额?


首先查询对应销售区域,在F2单元格中输入公式:

=INDEX(A2:A13,LOOKUP(MATCH(E2,B2:B13,),ROW(1:12)/(A2:A13<>"")))



公式解析:


  • (A2:A13<>""):判断A2:A13是不是空值,如果是返回TRUE,否则返回FALSE;

  • ROW(1:12)/(A2:A13<>""):1到12的数值除以逻辑值数组,结果为

{1;#DIV/0!;#DIV/0!;4;#DIV/0!;#DIV/0!;#DIV/0!;8;#DIV/0!;10;#DIV/0!;#DIV/0!}


  • MATCH(E2,B2:B13,):返回指定产品在B2:B13区域中的行数;

  • LOOKUP(MATCH(E2,B2:B13,),ROW(1:12)/(A2:A13<>"")):在数组中查找指定产品对应的数值,因为LOOKUP查找时忽略错误值#DIV/0!,返回等于4或小于4且最接近的值,结果为4;

  • INDEX(A2:A13,LOOKUP(MATCH(E2,B2:B13,),ROW(1:12)/(A2:A13<>""))):返回A2:A13中第4行的值。


接下来查询对应的销售额,在G2单元格中输入公式:

=IFERROR(VLOOKUP(E2,B2:C13,2,0),"")



公式解析:在B2:C10区域,查找指定产品对应的销售额。IFERROR容错处理,如果查询不到结果,显示空值。


2、合并单元格隔行填充颜色


对合并单元格进行隔行填充颜色,我们可以利用条件格式设置来完成。


选中A2:C13单元格区域--条件格式--新建规则--使用公式确定要设置格式的单元格--输入公式:=MOD(COUNTA($A$2:$A2),2)--格式--填充--选择颜色--确定。




说明:在合并单元格中,默认仅显示合并单元格区域左上角第一个单元格内容。根据这一特点,先用COUNTA函数计算非空单元格个数,然后再用MOD函数判断奇偶,奇数结果填充颜色。