多表格汇总求助

gwgha

hugeinfo 发表于 2024-12-25 16:37
=LET(c,DROP(REDUCE("",SHEETSNAME(,1,1),LAMBDA(x,y,VSTACK(x,GROUPBY(SCAN(,INDIRECT(y&"!A3:A200"),LAMB ...

谢谢大神，解决了我的大问题。公式能否再优化一下，把结果中的0不显示。

hugeinfo

gwgha 发表于 2024-12-25 17:16
谢谢大神，解决了我的大问题。公式能否再优化一下，把结果中的0不显示。

=LET(c,DROP(REDUCE("",SHEETSNAME(,1,1),LAMBDA(x,y,VSTACK(x,GROUPBY(SCAN(,INDIRECT(y&"!A3:A200"),LAMBDA(x_,y_,IF(y_<>"",y_,x_))),INDIRECT(y&"!C3:j200"),CHOOSE({1,2},SUM,TOCOL(INDIRECT(y&"!C1:j1"))),,0)))),1),d,FILTER(c,CHOOSECOLS(c,2)<>0),PIVOTBY(TAKE(d,,1),CHOOSECOLS(d,2),TAKE(d,,-1),LAMBDA(a,IF(ISNUMBER(0/SUM(a)),SUM(a),"")),,0,,0))

edwin11891

hugeinfo 发表于 2024-12-25 17:28
=LET(c,DROP(REDUCE("",SHEETSNAME(,1,1),LAMBDA(x,y,VSTACK(x,GROUPBY(SCAN(,INDIRECT(y&"!A3:A200"),LA ...

借用一下公式，将行日期补齐：

1. =LET(c,DROP(REDUCE("",SHEETSNAME(,1,1),LAMBDA(x,y,VSTACK(x,GROUPBY(SCAN(,INDIRECT(y&"!A3:A200"),LAMBDA(m,n,IF(n<>"",n,m))),INDIRECT(y&"!C3:J200"),CHOOSE({1,2},SUM,TOCOL(INDIRECT(y&"!C1:J1"))),,0)))),1),d,FILTER(c,CHOOSECOLS(c,2)<>0),s,CHOOSECOLS(d,2),n,MAX(s)-MIN(s)+1,e,VSTACK(d,HSTACK(REPTARRAY(TAKE(d,1,1),n),SEQUENCE(n,,MIN(s)),REPTARRAY(0,n))),St,PIVOTBY(TAKE(e,,1),CHOOSECOLS(e,2),TAKE(e,,-1),LAMBDA(x,IF(ISNUMBER(0/SUM(x)),SUM(x),"")),,0,,0),St)

复制代码

mykitami

已经预留了下方及右方的扩展数据，如果不够用，自己更改一下


=LET(fx,LAMBDA(x,y,TOCOL(IF(x>0,y,1/0),2)),d,DROP(REDUCE("","Sheet"&{1,2}&"!",LAMBDA(x,y,LET(s,INDIRECT(y&"C3:N9999"),VSTACK(x,HSTACK(fx(s,SCAN(,INDIRECT(y&"A3:A9999"),LAMBDA(m,n,IF(n="",m,n)))),fx(s,INDIRECT(y&"C1:Az1")),fx(s,s)))))),1),PIVOTBY(TAKE(d,,1),DAY(INDEX(d,,2)),TAKE(d,,-1),SUM,0,0,,0,))

mykitami

如果需要将本月的所有日期都补充完整
=LET(fx,LAMBDA(x,y,TOCOL(IF(x>0,y,1/0),2)),d,DROP(REDUCE("","Sheet"&{1,2}&"!",LAMBDA(x,y,LET(s,INDIRECT(y&"C3:N9999"),VSTACK(x,HSTACK(fx(s,SCAN(,INDIRECT(y&"A3:A9999"),LAMBDA(m,n,IF(n="",m,n)))),fx(s,INDIRECT(y&"C1:Az1")),fx(s,s)))))),1),rq,INDEX(d,,2),_d,VSTACK(d,IFNA(HSTACK("",SEQUENCE(DAY(EOMONTH(MIN(rq),0))),""),"")),jg,PIVOTBY(TAKE(_d,,1),DAY(INDEX(_d,,2)),TAKE(_d,,-1),SUM,0,0,,0,),VSTACK(TAKE(jg,1),DROP(jg,2)))