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 Integer Dim s As String DBOpen '先删除原目标数据 ado.Execute ("Delete a_EmpSale WHERE FYear=" & Range("B1")) '获取最大行 rs = Range("A" & Rows.Count).End(xlUp).Row '从第5行开始循环判断是否有数据，如果有保存到数据库中 For r = 5 To rs If Val(Range("N" & r)) <> 0 Then '获取员工ID ID = 0 Set rst = ado.Execute("Select FItemID From t_Emp WHERE FName='" & Cells(r, 1) & "'") If Not rst.EOF Then ID = rst(0) rst.Close If ID > 0 Then s = "Insert Into a_EmpSale Values(" & Range("B1") & "," & ID For c = 2 To 14 s = s & "," & Val(Cells(r, c)) Next s = s & ")" ado.Execute (s) End If End If Next DBClose MsgBox "保存成功！", 64, "提示" Call CommandButton1_Click End 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"

运行后的结果如下图：