怎么按月统计数量

2ws3ed

怎么按月统计数量，新增数据能自动更新

hxj119

1. =VSTACK(HSTACK("月份",TRANSPOSE(C3:C12)),GROUPBY(TRANSPOSE(TEXT(E1:S1,"m月")),TRANSPOSE(E3:S12),SUM))

复制代码

hxj119

1. =TRANSPOSE(VSTACK(HSTACK({"编号";"名称"},TRANSPOSE(B3:C12)),GROUPBY(TRANSPOSE(TEXT(E1:S1,"m月")),TRANSPOSE(E3:S12),SUM)))

复制代码

mykitami

新建工作表
=LET(_m,MONTH(Sheet1!E1:ZZ1),_a,DROP(REDUCE("",UNIQUE(_m,1),LAMBDA(x,y,HSTACK(x,BYROW(FILTER(Sheet1!E3:ZZ999,_m=y),SUM)))),,1),VSTACK(HSTACK(Sheet1!A1:D1,UNIQUE(_m,1)&"月"),GROUPBY(Sheet1!A3:D999,_a,SUM,0,0,,Sheet1!B3:B999<>"")))

wyslw2004

=LET(a,HSTACK("名称",TRANSPOSE(C3:C12)),b,GROUPBY(TRANSPOSE(TEXT(E1:S1,"m月")),TRANSPOSE(E3:S12),SUM),TRANSPOSE(VSTACK(a,b)))

mjm04

高版本

=LET(x,GROUPBY(B3:D12,E3:S12,CHOOSE({1,2},TOCOL(MONTH(E1:S1)&"月"),SINGLE),0,0),PIVOTBY(CHOOSECOLS(x,1,2,3),CHOOSECOLS(x,4),CHOOSECOLS(x,5),SUM,,,,0))

你是那块小饼干

=HSTACK(C2:C12,DROP(REDUCE("",UNIQUE(MONTH(E1:S1),TRUE),LAMBDA(X,Y,HSTACK(X,VSTACK(Y&"月",BYROW(FILTER(E3:S12,MONTH(E1:S1)=Y,""),SUM))))),,1))

xtmyd5156

hxj119

更新公式：

1. =LET(a,TRANSPOSE(VSTACK(TEXT(E1:S1,"m月"),E3:S12)),HSTACK(VSTACK(A1:D1,A3:D12),TRANSPOSE(GROUPBY(TAKE(a,,1),DROP(a,,1),SUM))))

复制代码