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

西贝木一

grf1973 发表于 2015-12-3 09:42
折腾了半天弄出来了。和你的正确答案有一点不同，如果期初为负，下期回款为正，则不作处理。所有效果都是把 ...

grf1973老师,"如果期初为负，下期回款为正，则不作处理。"这样处理也可以,但下表这个有点不对,您再帮忙看看.

对账结账日	客户编码	客户名称	业务员/跟单员	期初余额	销售对账金额	回款对账金额	期末对账单余额	说明	正确数据
2015年1月	2.002	华为	李好	2000.00			1100.00		0
2015年2月	2.002	华为	李好	-1200.00			-1200.00		0
2015年3月	2.002	华为	李好		-800	100	0.00		-100

grf1973

上期的期初还要和下面的期初作比较？

西贝木一

grf1973 发表于 2015-12-3 10:15
上期的期初还要和下面的期初作比较？

就是您说的那样,"据期初余额和以后的销售及回款，如果销售及回款作用的效果是接近0，则作用之，反之就不作用".如果期初数与下面最近的期初数或销售冲抵为零,那先冲抵,"如果期初为负，下期回款为正，则不作处理。所有效果都是把期初+销售-回款向0靠近。",就象下表1月期初2000,与2月期初和3月销售-1200+-800刚好冲抵为0.

对账结账日	客户编码	客户名称	业务员/跟单员	期初余额	销售对账金额	回款对账金额	期末对账单余额	说明	正确数据
2015年1月	2.002	华为	李好	2000.00			1100.00		0
2015年2月	2.002	华为	李好	-1200.00			-1200.00		0
2015年3月	2.002	华为	李好		-800	100	0.00		-100

西贝木一

非常感谢grf1973老师!如果太麻烦太耗您时间就算了.感谢!

西贝木一

grf1973老师真想加您为好友,可我权限不够.

grf1973

再改也简单的，再加一个比较就是了。

grf1973

把代码作了一些简化，结果是一样的，但没原代码直观。

1. Sub 计算New1()       '目的：让每一期的 “期初+销售-回款”尽量向0值靠近
   
2.     arr = Range("a4:z" & [a65536].End(3).Row)
   
3.     Set d = CreateObject("scripting.dictionary")
   
4.     x = 0
   
5.     For i = 1 To UBound(arr)
   
6.         bm = arr(i, 2)    '以编码为key
   
7.         d(arr(i, 2)) = d(arr(i, 2)) & "," & i
   
8.     Next
   
9.     For Each bm In d.keys
   
10.         hsrr = Split(d(bm), ","): r = UBound(hsrr)
    
11.         xsum = 0
    
12.         If r > 1 Then
    
13.             For i = 1 To r - 1
    
14.                 k = Val(hsrr(i))
    
15.                 a = arr(k, 5 + x): b = arr(k, 6 + x): c = arr(k, 7 + x)
    
16.                 For j = i + 1 To r
    
17.                     k1 = Val(hsrr(j))
    
18.                     For p = 5 To 7
    
19.                         xp = IIf(p < 7, arr(k1, p + x), -arr(k1, p + x))
    
20.                         If (a + b - c) * xp < 0 Then '“期初+销售-回款”分别与下面的期初、销售、回款（取负）对比
    
21.                             If Abs(a + b - c) > Abs(xp) Then
    
22.                                 a = a + xp: xp = 0
    
23.                             Else
    
24.                                  xp = xp + (a + b - c): a = c - b
    
25.                             End If
    
26.                         End If
    
27.                         arr(k1, p + x) = IIf(p < 7, xp, -xp)
    
28.                     Next
    
29.                 Next
    
30.                 arr(k, 5 + x) = a: arr(k, 6 + x) = b: arr(k, 7 + x) = c
    
31.             Next
    
32.         End If
    
33.         For i = 1 To r
    
34.             k = Val(hsrr(i))
    
35.             arr(k, 8 + x) = arr(k, 5 + x) + arr(k, 6 + x) - arr(k, 7 + x)
    
36.         Next
    
37.     Next
    
38.     Range("h4:h" & [a65536].End(3).Row) = Application.Index(arr, , 8 + x)
    
39. End Sub

复制代码

西贝木一

grf1973 发表于 2015-12-3 11:12
把代码作了一些简化，结果是一样的，但没原代码直观。

非常感谢您,grf1973老师!感谢您的无私帮助.

西贝木一

OK了,帮了我大忙了,再次感谢grf1973老师!
也感谢这个论坛,这个论坛高人真的挺厉害.

西贝木一

grf1973 发表于 2015-12-3 11:04
再改也简单的，再加一个比较就是了。

grf1973老师,您好!不好意思,还要麻烦您一下.如果不考虑"上期的期初和下面的期初作比较",但要同时考虑以下4种情况，代码怎么弄:
1、期初余额和销售是负数要从下往上依次冲抵，不与回款冲抵，
2、回款是负数要从下往上依次冲抵，不与期初和销售冲抵，
3、回款是正数要从上往下依次冲抵，要与期初和销售冲抵，
4、期初余额和销售是正数则按公式计算：期初余额+销售-回款=期末余额。
计算效果如下表：
一、回款、期初余额和销售是负数

对账结账日	客户编码	客户名称	业务员/跟单员	期初余额	销售对账金额	回款对账金额	期末对账单余额	说明	正确数据
2015年9月	2.012	HTC	刘佳	3000.00			0.00		500.00
2015年10月	2.012	HTC	刘佳	1000.00			0.00		0.00
2015年11月	2.012	HTC	刘佳	-1500.00			0.00		0.00
2015年11月	2.012	HTC	刘佳		1500.00	2000.00	0.00		1000.00
2015年12月	2.012	HTC	刘佳		-3500.00	-3000.00	1500.00		0.00

二、回款是正数

对账结账日	客户编码	客户名称	业务员/跟单员	期初余额	销售对账金额	回款对账金额	期末对账单余额	说明	正确数据
2015年9月	2.012	HTC	刘佳	2000.00			0.00		0.00
2015年10月	2.012	HTC	刘佳		-1800		0.00		0.00
2015年11月	2.012	HTC	刘佳		-200.00		0.00		0.00
2015年11月	2.012	HTC	刘佳		2000.00		0.00		0.00
2015年12月	2.012	HTC	刘佳		5000.00	3000.00	4000.00		4000.00