按年度分月份统计每个客户的应收款

jaxpqh

　　我需要按年度分月份统计每个客户的应收款（如下图），我是用函数公式实现的，由于客户名称是个变量，会不断地增加，我用函数公式就太麻烦了。我想用VBA代码的字典和数组功能来实现自己的需求，搞了好久没成功。我的需求是，运行VBA代码时弹出对话框，手动输入要统计的年份（不是该年份的不进行统计，时间对应的列为B列即“送货时间”），默认为当年，通过运行VBA把相关结果赋值到《统计表》对应的单元格，并在最后一行和最后一列进行合计。恳请哪位大师给予帮助，先谢谢了！

scggs

建议楼主可以使用数据透视表试试，对年份加个筛选
把原始的底表设置为智能表，就可以自动刷新了

jaxpqh

scggs 发表于 2024-5-16 10:50
建议楼主可以使用数据透视表试试，对年份加个筛选
把原始的底表设置为智能表，就可以自动刷新了

　　数据透视表没有研究过，不会弄。老师能帮忙弄一下吗？

longwin

jaxpqh 发表于 2024-5-16 10:54
　　数据透视表没有研究过，不会弄。老师能帮忙弄一下吗？

数据透视表见附件。

jaxpqh

longwin 发表于 2024-5-16 11:02
数据透视表见附件。

　　我试了一下，如果我增加４月、５月的数据，重新选择年份，并不会更新统计４月、５月的数据。

baofa2

1. Sub test11()  '
   
2.   Dim results(), data, dict As Object, strKey As String, i As Long, j As Long
   
3.   Dim rowSize As Long, colSize As Long, posRow As Long, posCol As Long, yyyy As Long
   
4.   
   
5.   yyyy = Val(Application.InputBox("年？", "T", Year(Date)))
   
6.   If yyyy = 0 Then Exit Sub
   
7.   
   
8.   Application.ScreenUpdating = False
   
9.   rowSize = 1
   
10.   colSize = 1
    
11.   Set dict = CreateObject("Scripting.Dictionary")
    
12.   
    
13.   data = Worksheets("客户").Range("A1").CurrentRegion.Value
    
14.   ReDim results(1 To UBound(data), 1 To 14)
    
15.   results(rowSize, colSize) = data(1, 4)
    
16.   For j = 1 To 12
    
17.     colSize = colSize + 1
    
18.     results(rowSize, colSize) = j & "月"
    
19.   Next
    
20.   For i = 2 To UBound(data)
    
21.     If Year(data(i, 2)) = yyyy Then
    
22.       strKey = Trim(data(i, 4))
    
23.       If Not dict.Exists(strKey) Then
    
24.         rowSize = rowSize + 1
    
25.         results(rowSize, 1) = strKey
    
26.         dict.Add strKey, rowSize
    
27.       End If
    
28.       posRow = dict(strKey)
    
29.       posCol = Month(data(i, 2)) + 1
    
30.       results(posRow, posCol) = results(posRow, posCol) + Val(data(i, 11))
    
31.     End If
    
32.   Next
    
33.   rowSize = rowSize + 1
    
34.   colSize = colSize + 1
    
35.   results(rowSize, 1) = "合计"
    
36.   results(1, colSize) = "合计"
    
37.   For j = 2 To colSize - 1
    
38.     For i = 2 To rowSize - 1
    
39.       results(rowSize, j) = results(rowSize, j) + results(i, j)
    
40.       results(i, colSize) = results(i, colSize) + results(i, j)
    
41.     Next
    
42.     results(i, colSize) = results(i, colSize) + results(i, j)
    
43.   Next
    
44.   With Worksheets("统计表").Range("A2")
    
45.     .CurrentRegion.Offset(1).Clear
    
46.     With .Resize(rowSize, colSize)
    
47.       .HorizontalAlignment = xlCenter
    
48.       .Borders.LineStyle = xlContinuous
    
49.       .Rows(1).Font.Bold = True
    
50.       .Value = results
    
51.       .Resize(rowSize - 1).Sort .Item(1), xlAscending, , , , , , xlYes
    
52.     End With
    
53.   End With
    
54.   
    
55.   Set dict = Nothing
    
56.   Application.ScreenUpdating = True
    
57.   Beep
    
58. End Sub

复制代码

fanyoulin

jaxpqh 发表于 2024-5-16 11:14
　　我试了一下，如果我增加４月、５月的数据，重新选择年份，并不会更新统计４月、５月的数据。

数据透视需要手动更新，把鼠标点到数据透视的任何区域，菜单上就会有增加一个“分析”的子菜单，里面有个快捷菜单“刷新”，还有更改数据源之类的，所有与数据透视有关联的更改大部分都在这里；

chxw68

参与一下。

lcluck2002

订单汇总0516.zip

LIUZHU

参与下，这个效果，做了个模版，每次提取数据，则复制模版生成新的数据