excel狼道办公技巧教学(Excel VBA应用-13：统计业务员业绩，目标完成率分析表)

在评价业务员销售业绩时，往往会给业务员设定销售目标，根据实际业务计算业务员的目标完成率。

报表格式如下图：

要计算目标完成率，首先要有销售目标的数据，可以在Excel表中建立一个销售目标表，这种方式的好处是简单，报表直接套用公式就可以使用，缺点是别人无法共享数据，一旦修改可能造成数据不同步。另一种方式是直接在金蝶数据库中建立新表，保存销售目标数据。对于ERP系统，如果对数据库不熟悉，一定不要去修改已有的内容，可能会造成系统无法运行，但是可以建立新表，不会影响系统的正常运行。

新建一张工作表，用来管理业务员销售目标，功能是可以查询任意年度的销售目标，录入并保存目标数据。格式如下图：

两个按钮，一个按钮完成查询功能，一个按钮完成保存功能；

在数据库中创建一个销售目标表a_EmpSale，创建表的SQL语句是：

CREATE TABLE a_EmpSale(FYear int,FEmpID int,F1 numeric(18,6),F2 numeric(18,6),F3 numeric(18,6),F4 numeric(18,6),F5 numeric(18,6),F6 numeric(18,6),F7 numeric(18,6),F8 numeric(18,6),F9 numeric(18,6),F10 numeric(18,6),F11 numeric(18,6),F12 numeric(18,6),FSum numeric(18,6))

FYear：年度；

FEmpID：业务员ID，从系统的职员表（t_Emp）获取；

F1—F12：12个月的目标；

FAmt：年度目标合计；

录入数据有两种方法，一种是录入业务员，一种是直接显示所有的业务员，这里我们采用第2种，直接列出系统所有的职员，如果有职员资料中职员类型，可以直接筛选出类型为业务员的职员。

关于格式设计和连接数据库前面多期已有介绍，这里就不再赘述，直接列出SQL语句。

刷新数据的SQL语句：

sql = "Select a.FName,F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,FSum "sql = SQL & "From t_Emp a LEFT JOIN "sql = sql & "(Select * From a_EmpSale WHERE FYear=" & Range("B1") & ") b ON a.FItemID =b.FEmpID "sql = sql & "Order By FName"

刷新结果如上图，在B列到M列录入目标数据，注意N列是公式，不要修改。

保存数据时我们以N列是否有数值来判断是否需要保存该行数据。

再来看保存按钮的功能代码：

Private Sub CommandButton2_Click()Dim r As Integer, rs As Integer, ID As Long, c As IntegerDim s As StringDBOpen'先删除原目标数据ado.Execute ("Delete a_EmpSale WHERE FYear=" & Range("B1"))'获取最大行rs = Range("A" & Rows.Count).End(xlUp).Row'从第5行开始循环判断是否有数据，如果有保存到数据库中For r = 5 To rsIf Val(Range("N" & r)) <> 0 Then'获取员工IDID = 0Set rst = ado.Execute("Select FItemID From t_Emp WHERE FName='" & Cells(r, 1) & "'")If Not rst.EOF Then ID = rst(0)rst.CloseIf ID > 0 Thens = "Insert Into a_EmpSale Values(" & Range("B1") & "," & IDFor c = 2 To 14s = s & "," & Val(Cells(r, c))Nexts = s & ")"ado.Execute (s)End IfEnd IfNextDBCloseMsgBox "保存成功！", 64, "提示"Call CommandButton1_ClickEnd Sub

这里要注意的是两个判断，一个是循环时该行员工的ID是否存在，防止由于误操作修改员工姓名，一个是以合计列是否有数值来判断该行是否录入数据。

还要注意的是我们把连接数据库和关闭数据库的代码放到模块中，以方便调用，不用每次都写重复的代码。

有了目标数据，我们就可以很方便的制作目标完成率报表了。这里只介绍SQL语句即可，其他的都是相同的代码。

目标完成率报表的SQL语句如下：

'构造提取数据的SQL语句

sql = "Select c.FName,S1,F1,S1/F1,S2,F2,S2/F2,S3,F3,S3/F3,S4,F4,S4/F4"sql = sql & ",S5,F5,S5/F5,S6,F6,S6/F6,S7,F7,S7/F7,S8,F8,S8/F8"sql = sql & ",S9,F9,S9/F9,S10,F10,S10/F10,S11,F11,S11/F11,S12,F12,S12/F12,SSum,FSum,SSum/FSum "sql = sql & "From (Select FEmpID,SUM(S1) AS S1,SUM(S2) AS S2,SUM(S3) AS S3"sql = sql & ",SUM(S4) AS S4,SUM(S5) AS S5,SUM(S6) AS S6,SUM(S7) AS S7"sql = sql & ",SUM(S8) AS S8,SUM(S9) AS S9,SUM(S10) AS S10,SUM(S11) AS S11"sql = sql & ",SUM(S12) AS S12,SUM(SSum) AS SSum From "sql = sql & "(Select b.FEmpID,"sql = sql & "CASE FPeriod WHEN 1 THEN FAmountincludetax ELSE 0 END AS S1,"sql = sql & "CASE FPeriod WHEN 2 THEN FAmountincludetax ELSE 0 END AS S2,"sql = sql & "CASE FPeriod WHEN 3 THEN FAmountincludetax ELSE 0 END AS S3,"sql = sql & "CASE FPeriod WHEN 4 THEN FAmountincludetax ELSE 0 END AS S4,"sql = sql & "CASE FPeriod WHEN 5 THEN FAmountincludetax ELSE 0 END AS S5,"sql = sql & "CASE FPeriod WHEN 6 THEN FAmountincludetax ELSE 0 END AS S6,"sql = sql & "CASE FPeriod WHEN 7 THEN FAmountincludetax ELSE 0 END AS S7,"sql = sql & "CASE FPeriod WHEN 8 THEN FAmountincludetax ELSE 0 END AS S8,"sql = sql & "CASE FPeriod WHEN 9 THEN FAmountincludetax ELSE 0 END AS S9,"sql = sql & "CASE FPeriod WHEN 10 THEN FAmountincludetax ELSE 0 END AS S10,"sql = sql & "CASE FPeriod WHEN 11 THEN FAmountincludetax ELSE 0 END AS S11,"sql = sql & "CASE FPeriod WHEN 12 THEN FAmountincludetax ELSE 0 END AS S12,"sql = sql & "FAmountincludetax As SSum "sql = sql & "From ICSaleEntry a LEFT JOIN ICSale b ON a.FInterID =b.FInterID "sql = sql & "WHERE FYear=2022) X Group By FEmpID) Y LEFT JOIN "sql = sql & "(Select * From a_EmpSale WHERE FYear=2022) Z ON Y.FEmpID =Z.FEmpID "sql = sql & "LEFT JOIN t_Emp c ON y.FEmpID=c.FItemID "sql = sql & "Order By FName"

运行后的结果如下图：

后面的操作就是把年度目标按月度分解后录入数据库，直接就能得到指定年度的目标完成率报表，方便使用。

关于使用Excel VBA制作金蝶数据库报表的系列告一段落，如果大家还有什么想了解的，可以在下面留言。

制作报表的要求就是了解自己的需求，了解数据库，套用固定格式就可以了，只要多多练习其实是很容易的。多花点时间学习，在生成报表时就可以节约大量的时间，提高工作效率。