|
统计结果和模拟结果略有不同。
- Sub 统计()
- Dim wb As Workbook
- Set d = CreateObject("scripting.dictionary")
- Set wb = Workbooks.Open(ThisWorkbook.Path & "\工作量 .xlsx")
- arr = wb.Sheets(1).Range("c1:f" & wb.Sheets(1).[e65536].End(3).Row)
- wb.Close False
- For i = 2 To UBound(arr)
- yh = arr(i, 3) '用户
- lh = Trim(arr(i, 4)) '移动类型
- x = yh & lh & "笔数": d(x) = d(x) + 1
- x = yh & lh & "数量": d(x) = d(x) + Abs(arr(i, 1))
- Next
-
- [d4:iv1000] = ""
- cmax = [iv3].End(xlToLeft).Column '当前表最大列
- rmax = [c65536].End(3).Row 'C列最大行+2
- arr = [a1].Resize(rmax, cmax)
- For j = 8 To cmax
- If arr(1, j) = "" Then arr(1, j) = arr(1, j - 1)
- If arr(2, j) = "" Then arr(2, j) = arr(2, j - 1)
- lh = Trim(arr(2, j)) '移动类型
- For i = 4 To rmax
- yh = arr(i, 3) '用户
- x = yh & lh & arr(3, j) '用户+移动类型+笔数(数量)
- arr(i, j) = d(x)
- x1 = yh & arr(1, j) & arr(3, j) '用户+出库(入库)+笔数(数量)
- d(x1) = d(x1) + arr(i, j)
- Next
- Next
-
- For i = 4 To rmax - 2
- yh = arr(i, 3) '用户
- arr(i, 4) = d(yh & "入库" & "笔数")
- arr(i, 5) = d(yh & "入库" & "数量")
- arr(i, 6) = d(yh & "出库" & "笔数")
- arr(i, 7) = d(yh & "出库" & "数量")
- Next
-
- [a1].Resize(rmax, cmax) = arr
-
- End Sub
复制代码 |
|