=SUMIF(门店基础资料货架包汇总!Z:Z,匹配数据逻辑!P3,IF(INDIRECT()=Q3,INDIRECT(V$1)))
因为每隔4行就是一个风格,连这个条件判断,IF(INDIRECT()=Q3都不需要了
CONCATENATE和&连接字符
功能差不多
获取列名:
SUBSTITUTE(ADDRESS(1,21,4),1,"")
向上取整,获取循环数:
ROUNDUP(ROW(1:1)/4,0)
连接符号:
"AA"&"$"&"11"
引用获取单元格:
INDIRECT()
INDIRECT(SUBSTITUTE(ADDRESS(1,22,4),1,"")&"$"&ROUNDUP(ROW(1:1)/4,0))
=SUMIF(门店基础资料货架包汇总!Z:Z,匹配数据逻辑!P3,INDIRECT(SUBSTITUTE(ADDRESS(1,22,4),1,"")&"$"&ROUNDUP(ROW(1:1)/4,0)))
生成循环数:
(ROUNDUP(ROW(1:1)/4,0)-1)*9
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),1,)
取指定偏移量的单元格:
OFFSET(AG1,0,9,1,1)
把9换成下面的循环数:
OFFSET(AG1,0,(ROUNDUP(ROW(1:1)/4,0)-1)*9,1,1)
=SUMIF(门店基础资料货架包汇总!Z:Z,匹配数据逻辑!P3,INDIRECT("门店基础资料货架包汇总!"&SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(AG1,0,(ROUNDUP(ROW(1:1)/4,0)-1)*9,1,1)),4),1,)&":"&SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(AG1,0,(ROUNDUP(ROW(1:1)/4,0)-1)*9,1,1)),4),1,)))
=SUMIF(门店基础资料货架包汇总!Z:Z,匹配数据逻辑!P3,INDIRECT("门店基础资料货架包汇总!"&SUBSTITUTE(ADDRESS(1,COLUMN(AG1),4),1,)&":"&SUBSTITUTE(ADDRESS(1,COLUMN(AG1),4),1,)))
=SUMIF(门店基础资料货架包汇总!Z:Z,匹配数据逻辑!P3,INDIRECT("门店基础资料货架包汇总!"&SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(AF1,0,(ROUNDUP(ROW(1:1)/4,0)-1)*9,1,1)),4),1,)&":"&SUBSTITUTE(ADDRESS(1,COLUMN(OFFSET(AF1,0,(ROUNDUP(ROW(1:1)/4,0)-1)*9,1,1)),4),1,)))