查找并计算

数在我心

用函数查找并计算

无关岁月

1. =NPV(-2,SUMIFS(D:D,A:A,"<="&EOMONTH("2024年"&H$1,{-1,0}),B:B,H$2,C:C,G4))

复制代码

ww87725244

=SUMPRODUCT((MONTH($A$2:$A$20)&"月"=$H$1)*($B$2:$B$20=$H$2)*($C$2:$C$20=SUBSTITUTE(G4," ",""))*$D$2:$D$20)

part98

1. =LET(s,FILTER(C2:D20,(TEXT(A2:A20,"m月")=H1)*(B2:B20=H2)),a,TAKE(s,,1),b,TAKE(s,,-1),DROP(REDUCE("",UNIQUE(a),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUM(FILTER(b,a=y)))))),1))

复制代码

hxj119

=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

=LET(x,MONTH(A2:A20)&"月"=H1,
y,B2:B20=H2,
z,FILTER(C2:D20,(y)*(x)),
m,CHOOSECOLS(z,1),
HSTACK(UNIQUE(m),MAP(UNIQUE(m),LAMBDA(k,SUM(FILTER(CHOOSECOLS(z,2),m=k))))))

浮生若梦~~~

=LET(a,UNIQUE(C2:C20),HSTACK(a,MAP(a,LAMBDA(x,SUM((TEXT(A2:A20,"m月")=H1)*(B2:B20=H2)*(C2:C20=x)*D2:D20)))))

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
   yf = Month(arr(i, 1)) & "月"
   If yf = [h1] And arr(i, 2) = [h2] Then
      d(arr(i, 3)) = d(arr(i, 3)) + arr(i, 4)
   End If
Next
[g4].Resize(d.Count, 2) = Application.WorksheetFunction.Transpose(Array(d.keys, d.items))
End Sub

mykitami

=LET(a,FILTER(C2:D20,(B2:B20=H2)*(MONTH(A2:A20)&"月"=H1)),GROUPBY(TAKE(a,,1),TAKE(a,,-1),SUM,,0))

jivy1212

1. =SUM(SUMIFS(D:D,C:C,G4,B:B,$H$2,A:A,$H$1&ROW($1:$31)&"日"))

复制代码