跨月到账，怎么根据营收日期和到账日期，取到账的汇总金额分别放入对应单元格

amigoel

因为有跨月到账，所以要根据对账表的店仓号，取上月月底本月到账的金额，本月营收本月到账的金额，和本月营收下月到账的金额（在本月还没有到所以到账日期为空白）

我用的方法是先弹出消息框让用户输入对账月份，然后再去筛选店仓、营收日期和到账日期，感觉有点慢，有没有更简单的方法


对账表


到款明细表

3190496160

Sub 汇总()
Application.ScreenUpdating = False
Dim ar As Variant
Dim d As Object
Set d = CreateObject("scripting.dictionary")
With Sheets("到款明细")
    r = .Cells(Rows.Count, 1).End(xlUp).Row
    If r < 2 Then MsgBox "到款明细为空！": End
    ar = .Range("a1:g" & r)
End With
Dim br()
ReDim br(1 To UBound(ar), 1 To 5)
w = InputBox("请输入月份", "月份", "")
If w = "" Then End
For i = 2 To UBound(ar)
    If ar(i, 4) <> "" Then
        If IsDate(ar(i, 4)) Then
            yf = Month(ar(i, 4))
            If Val(yf) = Val(w) Then
                If Trim(ar(i, 1)) <> "" Then
                    t = d(ar(i, 1))
                    If t = "" Then
                        k = k + 1
                        d(ar(i, 1)) = k
                        t = k
                        br(k, 1) = ar(i, 1)
                        br(k, 2) = ar(i, 2)
                    End If
                    If Trim(ar(i, 3)) = "" Then
                        br(t, 5) = br(t, 5) + ar(i, 7)
                    Else
                        rq = CDate(Replace(ar(i, 3), "到账", ""))
                        yff = Month(rq)
                        If Val(yff) > Val(yf) Then
                            br(t, 5) = br(t, 5) + ar(i, 7)
                        ElseIf Val(yff) < Val(yf) Then
                            br(t, 3) = br(t, 3) + ar(i, 7)
                        ElseIf Val(yff) = Val(yf) Then
                            br(t, 4) = br(t, 4) + ar(i, 7)
                        End If
                    End If
                End If
            End If
        End If
    End If
Next i
If k = "" Then MsgBox "没有所选月份的数据！": End
With Sheets("对账")
    .[a1].CurrentRegion.Offset(1) = Empty
    .[a2].Resize(k, UBound(br, 2)) = br
End With
Application.ScreenUpdating = True
MsgBox "ok!"
End Sub

3190496160

猜呢，仅供参考
到款对账.rar (21.57 KB, 下载次数: 3)

2024-1-8 17:45 上传

点击文件名下载附件

amigoel

1. Sub 对账()
   
2. Dim by1!, sy1!, xy1!
   
3. Dim frr()
   
4. '先列出取数的月份
   
5. by = InputBox("请输入对账月份（例如：2023-08）")
   
6. sy = Format(DateAdd("m", -1, by), "YYYY-MM")
   
7. xy = Format(DateAdd("m", 1, by), "YYYY-MM")
   
8. 
   
9. Sheets("对账").Select
   
10. arr = Range("a2", Cells(Rows.Count, 1).End(xlUp))
    
11. Sheets("到款明细").Select
    
12. brr = Range("a1").CurrentRegion
    
13. 
    
14. ReDim brr1(1 To UBound(brr))
    
15. For j = 1 To UBound(brr)
    
16.     '把对账表格的店仓号、到账日期、营收日期、应到账金额、已到账金额用\连接在一起，方便筛选
    
17.     brr1(j) = Join(Array(brr(j, 1), brr(j, 3), brr(j, 4), brr(j, 5), brr(j, 6)), "")
    
18. Next j
    
19. 
    
20. On Error Resume Next
    
21. For i = 1 To UBound(arr)
    
22.     crr = Filter(brr1, arr(i, 1)) '按照店仓筛选符合条件的数据
    
23.     ReDim Preserve frr(1 To UBound(crr) + 1)
    
24.     For y = 0 To UBound(crr)
    
25.         frr(y + 1) = Split(crr(y), "") '数组分割，方便筛选日期，和累加金额
    
26.     Next y
    
27.     For Z = 1 To UBound(frr)
    
28.         '营收日期是上月，到账日期是本月则则把“已到账金额”加到sy1
    
29.         If InStr(frr(Z)(2), sy) > 0 And InStr(frr(Z)(1), by) > 0 Then sy1 = sy1 + frr(Z)(4)
    
30.         If InStr(frr(Z)(2), by) > 0 Then
    
31.             If InStr(frr(Z)(1), by) > 0 Then '营收日期是本月，到账日期是本月则把“已到账金额”加到by1
    
32.                 by1 = by1 + frr(Z)(4)
    
33.             Else '营收日期是本月，到账不是本月则把“应到账金额”加到xy1
    
34.                 xy1 = xy1 + frr(Z)(3)
    
35.             End If
    
36.         End If
    
37.     Next Z
    
38.     Sheets("对账").Cells(i + 1, "c") = Format(sy1, "0.00") '上月累计的金额写入对账表的c列
    
39.     Sheets("对账").Cells(i + 1, "d") = Format(by1, "0.00")
    
40.     Sheets("对账").Cells(i + 1, "e") = Format(xy1, "0.00")
    
41.     sy1 = 0
    
42.     by1 = 0
    
43.     xy1 = 0
    
44.     Erase frr '清空frr
    
45. Next i
    
46. Sheets("对账").Select
    
47. End Sub

复制代码

amigoel

我自己重新写了一下，思路是这样的
by1代表本月到账累计金额，sy1代表上月营收本月到账累计金额，xy1代表本月营收下月到账累计金额
1、把门店代码写入数组arr，把到款明细所有数据写入brr
2、把到款明细需要用到的列：店仓号、到账日期、营收日期、应到账金额、已到账金额用\连接在一起，组成新的数组brr1，方便后面筛选
3、进入arr的循环，根据店仓号筛选brr1中符合条件的数据，然后分列成数组frr
4、判断frr中的营收日期和到账日期的月份，分别把金额加到by1, sy1, xy1，最后写入对账表格中的位置

amigoel

3190496160 发表于 2024-1-8 17:45
猜呢，仅供参考

谢谢提供思路，我没有用字典，用数组写了一个新的