楼主
xmyjk |
Q:如何将需要将系统导出的进货和退货情况的数据，整理汇总到固定格式的汇总报表。
A:问题其实是一个多条件汇总的问题，运用字典来和数组来汇总，并使用IF判断区分是进货还是退货来汇总，代码如下：- Option Explicit
- Sub samesum2()
- Dim i As Long, n As Long, arr, brr(), nm As Long, xh As Long, X As Long
- Dim d
- Set d = CreateObject("Scripting.Dictionary")
- n = Worksheets(2).[a65536].End(xlUp).Row
- arr = Worksheets(2).Range("a2:j" & n)
- nm = 0
- For i = 1 To UBound(arr)
- If Not (d.exists(arr(i, 6) & "|" & arr(i, 5) & "|" & arr(i, 7))) Then
- nm = nm + 1
- ReDim Preserve brr(1 To 11, 1 To nm)
- d(arr(i, 6) & "|" & arr(i, 5) & "|" & arr(i, 7)) = nm
- If arr(i, 2) = "进货单" Then
- brr(1, nm) = arr(i, 6): brr(2, nm) = arr(i, 5): brr(3, nm) = arr(i, 7)
- brr(4, nm) = arr(i, 8): brr(5, nm) = "": brr(6, nm) = arr(i, 10):
- brr(7, nm) = "": brr(8, nm) = "": brr(9, nm) = ""
- brr(10, nm) = 0: brr(11, nm) = 0
- Else
- brr(1, nm) = arr(i, 6): brr(2, nm) = arr(i, 5): brr(3, nm) = arr(i, 7)
- brr(4, nm) = 0: brr(5, nm) = "": brr(6, nm) = 0:
- brr(7, nm) = "": brr(8, nm) = "": brr(9, nm) = ""
- brr(10, nm) = arr(i, 8): brr(11, nm) = arr(i, 10)
- End If
- Else
- xh = d(arr(i, 6) & "|" & arr(i, 5) & "|" & arr(i, 7))
- If arr(i, 2) = "进货单" Then
- brr(4, xh) = brr(4, xh) + arr(i, 8): brr(6, xh) = brr(6, xh) + arr(i, 10)
- Else
- brr(10, xh) = brr(10, xh) + arr(i, 8): brr(11, xh) = brr(11, xh) + arr(i, 10)
- End If
- End If
- Next
- X = Worksheets(1).[b65536].End(3).Row
- Worksheets(1).Range(Cells(7, 2), Cells(X, 12)).ClearContents
- Worksheets(1).[b7].Resize(UBound(brr, 2), UBound(brr, 1)) = Application.Transpose(brr)
- Set d = Nothing '释放对象变量
- Erase arr, brr '释放数组
- End Sub
 汇总.rar
|
