excel狼道办公技巧教学(Excel VBA应用-12：计算当期应收账款回款率)

我们在评价业务员销售业绩时，不仅仅考虑业务员的销售情况，也需要考虑回款情况，如果业务员销售业绩很高，但是回款情况却很差，只能说明业务员的催收能力不足。另外，在评价客户质量时也会用到回款率的数据，回款率高的客户才能给企业带来足够的现金流。

关于回款率的计算公式有很多种，各位根据自己的需要来设定计算公式即可，这里采用的计算公式是

回款率=当月到期的应收款对应的收款金额/当月到期的应收款总额

这个算法是比较宽泛的算法，比如当月10号到期，而收款时间是在30号，那么也认为该款项被收回，如果再计算的严格一些，比如超期5天内回款的才能计算为回款，那么就需要再增加条件。

做统计表之前先列一下格式，明确我们需要显示的内容：

根据上面的格式，我们需要知道数据库中存放上述内容的表。

销售发票表（ICSale）：发票主表，提供客户信息，应收日期，业务员信息；

销售发票明细表（ICSaleEntry）：发票明细表，提供应收金额；

收款单表（t_RP_NewReceiveBill）：收款日期以及对应的发票内码；

收款单明细表（t_rp_ARBillOfSH）：收款明细表，提供已收金额；

销售发票字段（ICSale）：

FInterID：发票内码；

FCustID：客户ID；

FEmpID：业务员ID；

FSettleDate：应收日期；

销售发票明细字段（ICSaleEntry）：

FInterID：发票内码；

FDetailID：发票明细ID；

FAmountincludetax：应收金额；

收款单表（t_RP_NewReceiveBill）：

FBillID：收款单ID；

FYear：收款日期所属年度；

FPeriod：收款日期所属月份；

收款单明细表（t_rp_ARBillOfSH）：收款明细表，提供应收金额，未收金额；

FBillID：收款单ID；

FEntryID_SRC：发票明细ID，对应的是ICSaleEntry表中的FDetailID；

FID_SRC：发票ID，对应的是ICSale表中的FInterID；

FReceiveAmount：收款金额；

先关联销售发票主表与明细表：

from ICSaleEntry a left join ICSale b on a.FInterID=b.FInterID

关联客户表

left join t_Organization c on b.FCustID=c.fitemid

关联职员表

left join t_item d on c.Femployee=d.fitemid

关联收款明细表

left join t_rp_ARBillOfSH f on f.FEntryID_SRC =a.FDetailID and f.FID_SRC =a.FInterID

限定条件：

发票结算日期在查询的月份中：

where b.FCancellation=0 and Year(b.fsettledate) = 2022 And Month(b.fsettledate) = 20

收款单日期也在当期：

and f.fbillid in (select fbillid from t_RP_NewReceiveBill where fyear=2022 and fperiod=20)

确认显示的字段：

select c.fnumber,c.fname,d.fname,SUM(a.FAmountincludetax) as famt,SUM(f.FReceiveAmount) as fhk

按业务员和客户代码排序：

group by d.fname,c.fnumber,c.fname

完整的SQL语句如下：

select c.fnumber,c.fname,d.fname,SUM(a.FAmountincludetax) as famt,SUM(f.FReceiveAmount) as fhkfrom ICSaleEntry a left join ICSale b on a.FInterID=b.FInterIDleft join t_Organization c on b.FCustID=c.fitemidleft join t_item d on c.Femployee=d.fitemidleft join t_rp_ARBillOfSH f on f.FEntryID_SRC =a.FDetailID and f.FID_SRC =a.FInterIDwhere b.FCancellation=0 and Year(b.fsettledate) = 2022 And Month(b.fsettledate) = 10and f.fbillid in (select fbillid from t_RP_NewReceiveBill where fyear=2022 and fperiod=10)group by d.fname,c.fnumber

最后结果如下：

附源码：

Option ExplicitPrivate Sub CommandButton1_Click()Dim ado As ObjectDim rst As ObjectDim str As StringDim sql As StringDim dbIP As StringDim dbsa As StringDim dbpwd As StringDim dbname As StringDim rs As Integer'清屏Range("5:" & Rows.Count).Clear'如果没有录入查询年度和月份，退出If Val(Range("B1")) = 0 Or Val(Range("D1")) = 0 Then Exit Sub'如果有自动筛选，先取消自动筛选If ActiveSheet.AutoFilterMode Then Range("A4").AutoFilter'设置数据库连接字符串dbIP = "(local)" '安装数据库的电脑IP地址，(local)代表本机dbsa = "sa" 'SQLServer数据库的登录用户名dbpwd = "123456" 'SQLServer数据库的登录密码dbname = "AIS20210318095953" '需要提取数据的金蝶数据库名str = "Provider=SQLOLEDB.1;"str = str & "Data Source=" & dbIP & ";"str = str & "Persist Security Info=True;"str = str & "User ID=" & dbsa & ";"str = str & "Password=" & dbpwd & ";"str = str & "Initial Catalog=" & dbname & ";"'建立数据库连接Set ado = CreateObject("ADODB.Connection")ado.Open str'构造提取数据的SQL语句sql = "select c.fnumber,c.fname,d.fname,SUM(a.FAmountincludetax),SUM(f.FReceiveAmount),SUM(f.FReceiveAmount)/SUM(a.FAmountincludetax) "sql = sql & "from ICSaleEntry a left join ICSale b on a.FInterID=b.FInterID "sql = sql & "left join t_Organization c on b.FCustID=c.fitemid "sql = sql & "left join t_item d on c.Femployee=d.fitemid "sql = sql & "left join t_rp_ARBillOfSH f on f.FEntryID_SRC =a.FDetailID and f.FID_SRC =a.FInterID "sql = sql & "where b.FCancellation=0 and Year(b.fsettledate) = " & Range("B1") & " And Month(b.fsettledate) = " & Range("D1") & " "sql = sql & "and f.fbillid in (select fbillid from t_RP_NewReceiveBill where fyear=" & Range("B1") & " and fperiod=" & Range("D1") & ") "sql = sql & "group by d.fname,c.fnumber,c.fname"Set rst = ado.Execute(sql)If Not rst.EOF Then Range("A5").CopyFromRecordset rstrst.CloseSet rst = NothingSet ado = Nothing'*******************设置报表格式*******************'取消工作表显示网格线ActiveWindow.DisplayGridlines = False'先设置报表标题With Range("A4:F4").Font.Name = "微软雅黑" '字体名称.Font.Size = 11 '字体大小.Font.Color = RGB(255, 255, 255) '字体颜色.Interior.Color = RGB(72, 99, 156) '背景色.HorizontalAlignment = xlCenter '水平居中.VerticalAlignment = xlCenter '垂直居中End With'设置表体格式With Range("A5:F" & Range("A" & Rows.Count).End(xlUp).Row).Font.Name = "宋体" '字体名称.Font.Name = "Calibri" '数字使用的字体名称.Font.Size = 11 '字体大小.VerticalAlignment = xlCenter '垂直居中End With'设置表格With Range("A4:F" & Range("A" & Rows.Count).End(xlUp).Row).Borders.LineStyle = 1 '网格线为实线.Borders.Color = RGB(221, 221, 221) '网格线颜色End With'设置行高With Range("4:" & Range("A" & Rows.Count).End(xlUp).Row).RowHeight = 18 '行间距为18End With'设置数字格式With Range("D5:E" & Range("A" & Rows.Count).End(xlUp).Row).NumberFormatLocal = "0.00;;;" '数字格式为2位小数，为0时不显示End WithWith Range("F5:F" & Range("A" & Rows.Count).End(xlUp).Row).NumberFormatLocal = "0.00%;;;" '数字格式为2位小数，为0时不显示End With'提取数据后加上自动筛选Range("A4").Resize(1, Range("A4").End(xlToRight).Column).AutoFilterEnd Sub