|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
给个更快的,少了两重排序。- Sub 分组后对冲()
- Set d = CreateObject("Scripting.Dictionary")
- arr = [a1].CurrentRegion 'arr为源数组
- ra = UBound(arr)
- For i = 2 To ra '以账号为key,以账号所在行数为item(字符串累加)
- d(arr(i, 2)) = d(arr(i, 2)) & "," & i
- Next
-
- dk = d.keys: dt = d.items
- p = 1
- For i = 0 To UBound(dk)
- x = Split(dt(i), ",") '行数分列
- s = UBound(x)
- For j = 1 To s - 1 '在账号对应的各行之间对冲,如果对冲,把对应行的金额置0
- h1 = Val(x(j)): a1 = arr(h1, 4) '表示行h1对应的金额
- For k = j + 1 To s
- h2 = Val(x(k)): a2 = arr(h2, 4) '表示行h2对应的金额
- If a1 + a2 = 0 Then
- arr(h1, 4) = 0 '对应行的金额置0
- arr(h2, 4) = 0
- Exit For
- End If
- Next
- Next
- Next
-
- ReDim brr(1 To UBound(arr), 1 To 4) 'brr为显示数组
- brr(1, 1) = arr(1, 1): brr(1, 2) = arr(1, 2)
- brr(1, 3) = arr(1, 3): brr(1, 4) = arr(1, 4)
- p = 1
- For i = 2 To UBound(arr)
- If arr(i, 4) <> 0 Then '如果金额不为0(表示未经过对冲),则存入Brr用于最终显示
- p = p + 1
- brr(p, 1) = arr(i, 1): brr(p, 2) = arr(i, 2)
- brr(p, 3) = arr(i, 3): brr(p, 4) = arr(i, 4)
- End If
- Next
-
- [f1].Resize(p, 4) = brr
- End Sub
复制代码 |
|