查找并计算

inrever

1. =LET(a,FILTER(C2:D20,(B2:B20=H2)*MONTH(A2:A20)&"月"=H1),b,TAKE(a,,1),c,TAKE(a,,-1),HSTACK(UNIQUE(b),MAP(UNIQUE(b),LAMBDA(n,SUM(--(b=n)*c)))))

复制代码

大道无形5613

=TEXTSPLIT(SUM(FILTER(D2:D20,(MONTH(A2:A20*1)&"月"&B2:B20=H1&H2)*($C$2:$C$20=SUBSTITUTE(G4," ",""))))&","&SUM(FILTER(D2:D20,(MONTH(A2:A20*1)&"月"&B2:B20=H1&H2)*($C$2:$C$20=SUBSTITUTE(G5," ","")))),,",")

数在我心

part98 发表于 2024-7-1 22:52

感谢大神,是这个效果.公式我还得慢慢消化一下

数在我心

hxj119 发表于 2024-7-1 22:55
=SUMPRODUCT((TEXT($A$2:$A$20,"m月")=$H$1)*($B$2:$B$20=$H$2)*($C$2:$C$20=G5)*($D$2:$D$20))

谢谢,,需要绿色部分全部用公式查找计算

数在我心

leepoison 发表于 2024-7-1 22:58
=LET(x,MONTH(A2:A20)&"月"=H1,
y,B2:B20=H2,
z,FILTER(C2:D20,(y)*(x)),

感谢大佬帮助解疑,完美解决

数在我心

mykitami 发表于 2024-7-2 08:57
=LET(a,FILTER(C2:D20,(B2:B20=H2)*(MONTH(A2:A20)&"月"=H1)),GROUPBY(TAKE(a,,1),TAKE(a,,-1),SUM,,0))

感谢帮助,我用的WPS,好像没有GROUPBY

数在我心

jivy1212 发表于 2024-7-2 09:00

感谢帮助!需要绿色部分全部用公式生成

数在我心

zhangcheng6688 发表于 2024-7-2 06:52
学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion

感谢大神,可是我不会用,也看不明白

数在我心

浮生若梦~~~ 发表于 2024-7-1 23:38
=LET(a,UNIQUE(C2:C20),HSTACK(a,MAP(a,LAMBDA(x,SUM((TEXT(A2:A20,"m月")=H1)*(B2:B20=H2)*(C2:C20=x)*D2: ...

感谢,这个最短最简单

浮生若梦~~~

数在我心 发表于 2024-7-2 20:25
感谢,这个最短最简单

也好理解吧   去重 条件求和  然后组合在一起