给个烦琐的公式,不想去优化了。
E2数组公式下拉:- =69559444&IFERROR(MID(VLOOKUP(IF(B2>"",A2,""),IF({1,0},IF(B$1:B1>"",A$1:A1,""),E$1:E1),2,),9,4),5000+SUM(N(MATCH(IF(B$2:B2="是",A$2:A2,""),IF(B$1:B2>"",A$1:A2,""),)=ROW($2:2))))&MOD(-90-SUM(MID(IFERROR(MID(VLOOKUP(IF(B2>"",A2,""),IF({1,0},IF(B$1:B1>"",A$1:A1,""),E$1:E1),2,),9,4),5000+SUM(N(MATCH(IF(B$2:B2="是",A$2:A2,""),IF(B$1:B2>"",A$1:A2,""),)=ROW($2:2)))),{1,2;3,4},1)*{1,3}),10)
复制代码 |