人次- =LET(a,SCAN(,$B$1:$DE$3,LAMBDA(x,y,IF(y="",x,y))),b,FILTER($B4:$DE4,ISNUMBER(FIND(SUBSTITUTE(CHOOSEROWS(a,2),"月",""),TEXTJOIN(",",,SEQUENCE(,$DM$2-$DL$2+1))))),SUM(--CHOOSECOLS(b,SEQUENCE(,COUNTA(b)/3,1,3))))
复制代码
总金额
- =LET(a,SCAN(,$B$1:$DE$3,LAMBDA(x,y,IF(y="",x,y))),b,FILTER($B4:$DE4,ISNUMBER(FIND(SUBSTITUTE(CHOOSEROWS(a,2),"月",""),TEXTJOIN(",",,SEQUENCE(,$DM$2-$DL$2+1))))),SUM(--CHOOSECOLS(b,SEQUENCE(,COUNTA(b)/3,2,3))))
复制代码
基金支出
- =LET(a,SCAN(,$B$1:$DE$3,LAMBDA(x,y,IF(y="",x,y))),b,FILTER($B4:$DE4,ISNUMBER(FIND(SUBSTITUTE(CHOOSEROWS(a,2),"月",""),TEXTJOIN(",",,SEQUENCE(,$DM$2-$DL$2+1))))),SUM(--CHOOSECOLS(b,SEQUENCE(,COUNTA(b)/3,3,3))))
复制代码
分项目的
- =LET(a,SCAN(,$B$1:$DE$3,LAMBDA(x,y,IF(y="",x,y))),b,FILTER($B4:$DE4,ISNUMBER(FIND(TEXTBEFORE(TEXTAFTER(DI$3,"("),")"),TAKE(a,1)))*ISNUMBER(FIND(SUBSTITUTE(CHOOSEROWS(a,2),"月",""),TEXTJOIN(",",,SEQUENCE(,$DM$2-$DL$2+1))))),SUM(--CHOOSECOLS(b,SEQUENCE(,COUNTA(b)/3,3,3))))
复制代码
|