求解：统计不重复数值

zhangcheng6688 · 发表于 2024-4-10 12:43

学习一下字典，打扰
Sub test()
arr = Range("h4:h5")
brr = [b3].CurrentRegion
Set d = CreateObject("scripting.dictionary")
ReDim crr(1 To UBound(arr), 1 To 1)
For i = 1 To UBound(arr)
d(arr(i, 1)) = 0
Next
For j = 2 To UBound(brr)
s = brr(j, 1)
s1 = brr(j, 2)
If Not d.exists(s & "|" & s1) Then
   d(s1) = d(s1) + 1
   d(s & "|" & s1) = ""
End If
Next
For Each k In d.keys
If InStr(k, "|") = 0 Then
   m = m + 1
   crr(m, 1) = d(k)
End If
Next
[i4].Resize(m, 1) = crr
End Sub

无关岁月 · 发表于 2024-4-10 13:14

zhangcheng6688 发表于 2024-4-10 12:43
学习一下字典，打扰
Sub test()
arr = Range("h4:h5")

查询的话，可以这样

zhangcheng6688 · 发表于 2024-5-26 08:16

无关岁月发表于 2024-4-10 13:14
查询的话，可以这样

借个楼问个问题
你好老师，如果item是数组的话，查询时怎么提取更好一点，谢谢
https://club.excelhome.net/forum ... ;page=2#pid11506287

mengrs · 发表于 2024-5-26 08:39

=SUMPRODUCT((C$4:C$18=H4)/COUNTIFS(B$4:B$18,B$4:B$18,C$4:C$18,C$4:C$18))

无关岁月 · 发表于 2024-5-26 08:40

本帖最后由无关岁月于 2024-5-26 08:42 编辑

zhangcheng6688 发表于 2024-5-26 08:16
借个楼问个问题
你好老师，如果item是数组的话，查询时怎么提取更好一点，谢谢
https://club.excelhome ...

查询的话就这样写，通常数组的话，只需要记录它的行号就行了
Sub 按钮2_Click()
Set d = CreateObject("scripting.dictionary")
arr = [a2].CurrentRegion
For i = 2 To UBound(arr)
s = arr(i, 1)
If d(s) = 0 Then
      d(s) = i
Else
      j = d(s)
      If arr(j, 3) < arr(i, 3) Then
         d(s) = i
      End If
End If
Next
brr = [h2].CurrentRegion
For i = 2 To UBound(brr)
s = brr(i, 1)
j = d(s)
If Len(d(s)) Then
      brr(i, 2) = arr(j, 2)
      brr(i, 3) = arr(j, 3)
End If
Next
[h2].CurrentRegion = brr
End Sub

boyuanoffice · 发表于 2024-5-26 08:42

=ROWS(UNIQUE(FILTER(B:B,C:C=H4)))

复制代码

我的城市你来过 · 发表于 2024-5-26 12:17

=COUNTA(UNIQUE(FILTER($A$1:$A$16,MAP($A$1:$A$16&$B$1:$B$16,LAMBDA(x,REGEXP(x,$D2,1))))))

laomu2010 · 发表于 2024-5-26 22:26

本帖最后由 laomu2010 于 2024-5-26 22:27 编辑

=SUM((FREQUENCY(IF(C4:C18=H4,ROW(C4:C18)),IF(C4:C18<>H4,ROW(C4:C18)))))

		自动登录	找回密码
密码			免费注册

[求助] 求解：统计不重复数值

评分

评分

评分