本帖最后由 我走我流 于 2019-10-20 14:37 编辑
仅针对附件
=SUM(IFERROR(VLOOKUP(T(IF({1},"*"&T(OFFSET($D$13,(ROW(A1)-1)*4+{0;1;2},COLUMN(A:B)))&"*")),IF({1,0},SUBSTITUTE(A:A,"市","",2),B:B),2,),))
精简下
=SUM(IFERROR(VLOOKUP(T(IF({1},"*"&OFFSET($E$13,MAX(ROW(A1)-1)*4,,3,2)&"*")),IF({1,0},SUBSTITUTE(A:A,"市","",2),B:B),2,),))
换个思路
=SUM(IFERROR(INDEX($B$2:$B$13,N(IF({1},MATCH("*"&OFFSET($E$13,MAX(ROW(A1)-1)*4,,3,2),MID($A$2:$A$13,1,FIND("市",$A$2:$A$13)),)))),))
精简下
=SUM(IFNA(N(OFFSET($B$1,MATCH("*"&OFFSET($E$13,MAX(ROW(A1)-1)*4,,3,2),MID($A$2:$A$13,1,FIND("市",$A$2:$A$13)),),)),))
|