[已解决]应收账款余额计算函数搞不定，能不能用自定义函数或VBA

西贝木一

大家好！现有一应收账款余额计算问题弄了好久，函数无法解决，能不能用自定义函数或VBA啊？请大家帮忙看看，谢谢！主要是应收账款余额（橙色部分）计算出错，如果期初余额和当月销售回款都是正数，则应收账款余额计算无误，如果有负数（比如退货），则计算出错。具体见附件



计算方法是:期初余额+当月销售-当月回款=期末余额,比如下表中9月期初余额是2000,10月份销售1100,回款2500元,那先冲减9月期初余额2000,减至为零,回款还剩余2500-2000=500,这500元再冲减10月销售500元,还剩1100-500=600元.

对账结账日	客户编码	客户名称	业务员/跟单员	期初余额	销售对账金额	回款对账金额	期末对账单余额
2015年9月	2.012	HTC	刘佳	2000.00			0.00
2015年10月	2.012	HTC	刘佳		1100	2500	600.00

但也有些特殊的情况不好处理,比如下表中期初余额、销售金额和回款金额为负数时,回款为负数等于是退款，5月期初余额为-1000，6月销售（退货）-500，回款-100，则5月期末余额为-1000--100=-900，6月期末余额应为-500。如果6月回款为100（正数），则5月期末余额为-1000-100=-1100,6月末余额为-500.

对账结账日	客户编码	客户名称	业务员/跟单员	期初余额	销售对账金额	回款对账金额	期末对账单余额
2015年5月	2.001	步步高	张倩	-1000.00			-900
2015年6月	2.001	步步高	张倩		-500	-100	-500

最佳答案

月排行榜 / 总排行榜

grf1973

2015-11-27 15:15

1. Sub 计算()
   
2.     arr = Range("a4:h" & [a65536].End(3).Row)
   
3.     For i = 1 To UBound(arr) - 1
   
4.         For j = i + 1 To UBound(arr)
   
5.             If arr(j, 2) <> arr(i, 2) Then Exit For
   
6.         Next
   
7.         s = i: e = j - 1         '相同客户的起止行
   
8.         For k = s To e
   
9.             arr(k, 8) = arr(k, 5) + arr(k, 6) - arr(k, 7)
   
10.         Next
    
11.         If e > s Then
    
12.             xsum = arr(s, 5) + arr(s, 6) - arr(s, 7) + arr(e, 5) + arr(e, 6) - arr(e, 7)
    
13.             p = arr(s, 5) - arr(e, 7)
    
14.             arr(s, 8) = p
    
15.             If arr(s, 5) > 0 And p <= 0 Then arr(s, 8) = 0
    
16.             arr(e, 8) = xsum - arr(s, 8)
    
17.         End If
    
18.         i = e
    
19.     Next
    
20.     Range("h4:h" & [a65536].End(3).Row) = Application.Index(arr, , 8)
    
21. End Sub

复制代码

跳转到最佳答案楼层

西贝木一

怎么没有高手关注啊

grf1973

1. Sub 计算()
   
2.     arr = Range("a4:h" & [a65536].End(3).Row)
   
3.     For i = 1 To UBound(arr) - 1
   
4.         For j = i + 1 To UBound(arr)
   
5.             If arr(j, 2) <> arr(i, 2) Then Exit For
   
6.         Next
   
7.         s = i: e = j - 1         '相同客户的起止行
   
8.         For k = s To e
   
9.             arr(k, 8) = arr(k, 5) + arr(k, 6) - arr(k, 7)
   
10.         Next
    
11.         If e > s Then
    
12.             xsum = arr(s, 5) + arr(s, 6) - arr(s, 7) + arr(e, 5) + arr(e, 6) - arr(e, 7)
    
13.             p = arr(s, 5) - arr(e, 7)
    
14.             arr(s, 8) = p
    
15.             If arr(s, 5) > 0 And p <= 0 Then arr(s, 8) = 0
    
16.             arr(e, 8) = xsum - arr(s, 8)
    
17.         End If
    
18.         i = e
    
19.     Next
    
20.     Range("h4:h" & [a65536].End(3).Row) = Application.Index(arr, , 8)
    
21. End Sub

复制代码

西贝木一

grf1973 发表于 2015-11-27 15:15

非常感谢grf1973老师

西贝木一

再次感谢grf1973老师的无私帮助，验算了一下，数据计算无误，但在筛选状态下计算出错

西贝木一

grf1973 发表于 2015-11-27 15:15

grf1973老师，您好！如果在E列前面插入列，代码应怎样修改？每行代码能不能简单解释一下，本人VBA是菜鸟，非常感谢！


在H列前面插入列后改了一下arr = Range("a4:j" & [a65536].End(3).Row)和Range("j4:j" & [a65536].End(3).Row) = Application.Index(arr, , 8)计算无误，但在B：E列前面增加插入列修改了上面，计算却出错。

grf1973

1、arr = Range("a4:h" & [a65536].End(3).Row) 范围改大一点，比如
arr = Range("a4:z" & [a65536].End(3).Row)
2、代码中凡是出现  arr(*,*)的，全部换成arr(*,*+x)，x为插入的列数
3、Range("h4:h" & [a65536].End(3).Row) = Application.Index(arr, , 8) 把其中的“h"改为显示结果所在的列，Application.Index(arr, , 8) 改成 Application.Index(arr, , 8+x)

西贝木一

grf1973 发表于 2015-11-27 17:07
1、arr = Range("a4:h" & [a65536].End(3).Row) 范围改大一点，比如
arr = Range("a4:z" & [a65536].End(3 ...

grf1973老师,非常感谢!

西贝木一

grf1973 发表于 2015-11-27 15:15

grf1973老师,您好!碰到特殊情况,应收账款余额计算还是会出错(附件黄色部分),请抽空再帮忙看看,感谢!

grf1973

你得讲清为什么。你把错的几行的具体计算过程讲清楚。我实在看不出为什么是这个结果。。。。。