|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
- Sub qs()
- Dim arr, i, dic
- Set dic = CreateObject("scripting.dictionary")
- arr = Sheet1.Range("a1").CurrentRegion.Value
- ReDim brr(1 To UBound(arr), 1 To 20)
- brr(1, 1) = "时间段"
- m = 1: c = 1
- For i = 2 To UBound(arr)
- s = Format(arr(i, 6), "hh")
- s = s & ":00:00-" & s & ":59:59"
- If Not dic.exists(s) Then
- m = m + 1
- dic(s) = m
- End If
- s2 = arr(i, 7)
- If Not dic.exists(s2) Then
- c = c + 1
- dic(s2) = c
- End If
- r = dic(s): cl = dic(s2)
- brr(r, 1) = s: brr(1, cl) = s2: brr(r, cl) = brr(r, cl) + Val(arr(i, 3))
- Next
- Sheet1.Range("k4").Resize(m, c) = brr
- Sheet1.Range("k4").Sort key1:=Range("k4"), Order1:=2, Header:=xlYes
- End Sub
复制代码 |
|