统计时间点出现的次数（去除重复值）

1786024734 · 发表于 2024-9-13 16:11

本帖最后由 1786024734 于 2024-9-13 16:20 编辑

=INDEX(FREQUENCY(UNIQUE($B:$B),TEXTSPLIT(E1,"-")-{1,0}%%%),2)

复制代码

或

=INDEX(FREQUENCY(UNIQUE($B:$B),MID(E1,{1,7},5)-{1,0}%%%),2)

复制代码

jerrike · 发表于 2024-9-13 16:12

=COUNT(UNIQUE(FILTER($B$2:$B$772,BYROW($B$2:$B$772,LAMBDA(a,PRODUCT(a-TEXTSPLIT(E1,"-")/1)<=0)))))

复制代码

一杯绿茶来了 · 发表于 2024-9-13 22:05

=INDEX(FREQUENCY(UNIQUE($B$2:$B$772),TEXTSPLIT(E$1,,"-")*1),2)

忙到飞起 · 发表于 2024-9-13 23:05

=TOROW(DROP(FREQUENCY(UNIQUE(B2:B772),TEXT({"9:00","12:30","17:30"},"G/通用格式")+(1/24/60*10)),-1))

zhangcheng6688 · 发表于 2024-9-14 09:34

学习一下字典，打扰了
Sub test()
arr = [a1].CurrentRegion
Set d = CreateObject("scripting.dictionary")
For i = 2 To UBound(arr)
d(arr(i, 2)) = ""
Next
brr = [d1].CurrentRegion
For i = 2 To UBound(brr)
For j = 2 To UBound(brr, 2)
   sj = Split(brr(1, j), "-")
   For Each kk In d.keys
      If CDate(sj(0)) <= kk And kk <= CDate(sj(1)) Then
         brr(i, j) = brr(i, j) + 1
      End If
   Next
Next
Next
[d1].CurrentRegion = brr
End Sub

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

[已解决] 统计时间点出现的次数（去除重复值）

评分