去重复多条件求个数问题

jylai

涂黄颜色是想要的结果

jerrike

1. G2=COUNT(UNIQUE(FILTER($A$2:$A$100,(EOMONTH($A$2:$A$100,)=EOMONTH(G$1,))*($B$2:$B$100=$F2))))

复制代码

jylai

jerrike 发表于 2024-4-29 08:19

感谢老师，您提供公式我G2的0，实际为1

魚。。

G2：

1. =LET(魚,COUNT(UNIQUE(FILTER($A$2:$A$100,($B$2:$B$100=$F2)*(EOMONTH(--$A$2:$A$100,-1)+1=G$1)))),IF(魚=0,"",魚))

复制代码

jylai

魚。。 发表于 2024-4-29 08:42
G2：

感谢各层老师，今天没有花了

袁勇

1. =TEXT(SUMPRODUCT(($B$2:$B$100=$F2)*(TEXT($A$2:$A$100,"emm")=TEXT(G$1,"emm"))/COUNTIFS($A:$A,$A$2:$A$100,$B:$B,$B$2:$B$100)),"0;;;")

复制代码

jylai

袁勇 发表于 2024-4-29 09:03

感谢老师，今天没有花了

chenxuefu

=PIVOTBY(CHOOSECOLS(UNIQUE(A2:D100),2),CHOOSECOLS(UNIQUE(A2:D100),4),CHOOSECOLS(UNIQUE(A2:D100),3),LAMBDA(x,y,COUNT(x)))

无关岁月

1. =LET(_a,A2:A100,_b,B2:B100,PIVOTBY(_b,--(YEAR(_a)&"-"&MONTH(_a)),N(MATCH(_a&_b,_a&_b,)=ROW(1:99)),SUM,,0,,0))

复制代码

zhangcheng6688

学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
lastr = Cells(Rows.Count, "F").End(xlUp).Row
brr = Range("f1:p" & lastr)
For i = 2 To UBound(arr)
   strkey = arr(i, 1) & arr(i, 2)
   d(strkey) = ""
Next
For j = 2 To UBound(brr)
   For k = 2 To UBound(brr, 2)
      If brr(1, k) <> "" Then
         y = Year(brr(1, k))
         m = Month(brr(1, k))
         s = y & "/" & m
         For Each vkey In d.keys
            If InStr(vkey, s) > 0 And InStr(vkey, brr(j, 1)) Then brr(j, k) = brr(j, k) + 1
         Next
      End If
   Next
Next
[F1].Resize(UBound(brr), UBound(brr, 2)) = brr
End Sub