融合成一个公式

zzq0117

融合成一个公式

jerrike

1. =LET(a,FILTER(B3:B34,(LEFT(A3:A34,4)="5601")),HSTACK(a,SUMIFS(E:E,B:B,a,M:M,5601,O:O,$S$1:$T$1)))

复制代码

hxj119

1. =REDUCE({"科目名称","1月","2月"},FILTER(B3:B34,(LEFT(A3:A34,4)="5601")),LAMBDA(x,y,VSTACK(x,HSTACK(y,REDUCE("",{"1月","2月"},LAMBDA(m,n,TAKE(HSTACK(m,SUM((E3:E999)*(B3:B999=y)*(M3:M999="5601")*(O3:O999=n))),,-2)))))))

复制代码

天蚕爱学习

=LET(a,FILTER(B3:B34,(LEFT(A3:A34,4)="5601")),m,DROP(REDUCE(0,a,LAMBDA(x,y,VSTACK(x,SUMIFS(E:E,B:B,y,M:M,5601,O:O,$S$1)))),1),n,DROP(REDUCE(0,a,LAMBDA(x,y,VSTACK(x,SUMIFS(E:E,B:B,y,M:M,5601,O:O,$T$1)))),1),HSTACK(a,m,n))

laomu2010

=LET(A,A3:A34,B,B3:B34,E,E3:E34,O,O3:O34,REDUCE({"科目名称","1月","2月"},FILTER(B,(LEFT(A,4)="5601")),LAMBDA(X,Y,VSTACK(X,HSTACK(Y,SUM(E*(LEFT(A,4)="5601")*(B=Y)*(O="1月")),SUM(E*(LEFT(A,4)="5601")*(B=Y)*(O="2月")))))))

天蚕爱学习

=LET(A,UNIQUE(B3:B34),HSTACK(A,DROP(REDUCE(0,S24:T24,LAMBDA(m,n,HSTACK(m,SCAN(0,A,LAMBDA(X,Y,SUMIFS(E:E,B:B,Y,M:M,5601,O:O,n)))))),,1)))

天蚕爱学习

jerrike 发表于 2024-8-15 14:48

=LET(a,FILTER(B3:B34,(LEFT(A3:A34,4)="5601")),HSTACK(a,SUMIFS(E:E,B:B,a,O:O,$S$1:$T$1))),大佬真牛，简洁清晰，5061筛选过了，后面可以少一个条件