金蝶软件销售订单如何生成销售单,如何用vba制作开单系统表

本节我们学习如何制作周期汇总表，即按年度查询，显示每个月的销售金额，由于报表是按年度查询的，需要切换不同的年度进行查询，所以把查询年度放在第一行作为查询参数。具体的格式可以参考下图：

SQL的查询语句是可以嵌套查询的，即一个查询语句的结果集可以做为另一个查询的数据源，也就是当作一个新表来看待。

字段分析：客户字段在ICSale表中，金额字段在ICSaleEntry表中，两个表中均没有月份字段，但是在ICSale表中有日期字段，我们可以用函数来生成销售月份，即在销售明细表中提取我们所需要的数据作为汇总表的数据源。

SQL语句如下：

Select a.FCustID,MONTH(a.fdate) AS FMonth,SUM(b.famount) AS FAmtFrom ICSale a LEFT JOIN ICSaleEntry b on a.FInterID=b.FInterIDWHERE YEAR(a.fdate)=2022Group By a.FCustID,MONTH(a.fdate)

提取数据时先进行年度过滤，再按客户和月份进行汇总，这样做可以最大限度减少数据处理量，提高查询速度。

在对字段进行函数处理后，由于处理后的字段没有名称，再进行处理时无法调用，所以需要使用AS关键字对处理后的字段进行命名。在SQLServer2008R2的查询分析中我们可以看到结果。

报表格式中有14个字段，即客户 12个月 汇总，我们需要用SQL语句构造出14个字段，即把销售按月份来分列。这里我们使用CASE WHEN 语句来构造新的字段。

CASE WHEN语法为 CASE 字段 WHEN 条件 THEN 条件成立时的值 ELSE 条件不成立的值 END

根据语法我们可以构造出1月的销售额并命名为M1，按同样的方法可以构造出12个月和合计列

SQL语句为：

Select x.FCustID,CASE x.FMonth WHEN 1 THEN x.FAmt ELSE 0 END AS M1,CASE x.FMonth WHEN 2 THEN x.FAmt ELSE 0 END AS M2,CASE x.FMonth WHEN 3 THEN x.FAmt ELSE 0 END AS M3,CASE x.FMonth WHEN 4 THEN x.FAmt ELSE 0 END AS M4,CASE x.FMonth WHEN 5 THEN x.FAmt ELSE 0 END AS M5,CASE x.FMonth WHEN 6 THEN x.FAmt ELSE 0 END AS M6,CASE x.FMonth WHEN 7 THEN x.FAmt ELSE 0 END AS M7,CASE x.FMonth WHEN 8 THEN x.FAmt ELSE 0 END AS M8,CASE x.FMonth WHEN 9 THEN x.FAmt ELSE 0 END AS M9,CASE x.FMonth WHEN 10 THEN x.FAmt ELSE 0 END AS M10,CASE x.FMonth WHEN 11 THEN x.FAmt ELSE 0 END AS M11,CASE x.FMonth WHEN 12 THEN x.FAmt ELSE 0 END AS M12,x.FAmt FROM(Select a.FCustID,MONTH(a.fdate) AS FMonth,SUM(b.famount) AS FAmtFrom ICSale a LEFT JOIN ICSaleEntry b on a.FInterID=b.FInterIDWHERE YEAR(a.fdate)=2022Group By a.FCustID,MONTH(a.fdate)) x

注意使用SQL语句构造数据源后我们需要用括号括起来并重新命名为x,作为一个新表，结果如下：

通过上述方法我们得到了需要的原始数据，只需要对上面的结果再进行一次汇总即可得到我们想要的结果，把上面的结果集命名为y：

结果如下：

VBA代码中我们需要增加合计行公式，这个公式不要使用SUM函数，用Subtotal公式来代替，sum函数是对指定区域进行合计，而subtotal是对指定区域中可以单元格进行合计，这样我们在筛选表格时，合计行会实时显示筛选后的合计数字。

'填充合计栏公式Range("B3:N3").Formula = "=SUBTOTAL(9,B5:B" & Range("A" & Rows.Count).End(xlUp).Row & ")"Range("B3:N3").NumberFormatLocal = "0;;;"

罗马不是一天建成的，学习更是如此。如果要熟练掌握查询语句，必须大量的练习。先模仿，再举一反三，才能融会贯通。在练习过程中如果有不明白的地方可以在评论区留言，我们共同探讨。

附源码：

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 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.FName,SUM(M1),SUM(M2),SUM(M3),SUM(M4),SUM(M5),SUM(M6)" sql = sql & ",SUM(M7),SUM(M8),SUM(M9),SUM(M10),SUM(M11),SUM(M12),SUM(FAmt) " sql = sql & "From (Select x.FCustID," sql = sql & "CASE x.FMonth WHEN 1 THEN x.FAmt ELSE 0 END AS M1," sql = sql & "CASE x.FMonth WHEN 2 THEN x.FAmt ELSE 0 END AS M2," sql = sql & "CASE x.FMonth WHEN 3 THEN x.FAmt ELSE 0 END AS M3," sql = sql & "CASE x.FMonth WHEN 4 THEN x.FAmt ELSE 0 END AS M4," sql = sql & "CASE x.FMonth WHEN 5 THEN x.FAmt ELSE 0 END AS M5," sql = sql & "CASE x.FMonth WHEN 6 THEN x.FAmt ELSE 0 END AS M6," sql = sql & "CASE x.FMonth WHEN 7 THEN x.FAmt ELSE 0 END AS M7," sql = sql & "CASE x.FMonth WHEN 8 THEN x.FAmt ELSE 0 END AS M8," sql = sql & "CASE x.FMonth WHEN 9 THEN x.FAmt ELSE 0 END AS M9," sql = sql & "CASE x.FMonth WHEN 10 THEN x.FAmt ELSE 0 END AS M10," sql = sql & "CASE x.FMonth WHEN 11 THEN x.FAmt ELSE 0 END AS M11," sql = sql & "CASE x.FMonth WHEN 12 THEN x.FAmt ELSE 0 END AS M12,x.FAmt FROM " sql = sql & "(Select a.FCustID,MONTH(a.fdate) AS FMonth,SUM(b.famount) AS FAmt " sql = sql & "From ICSale a LEFT JOIN ICSaleEntry b on a.FInterID=b.FInterID " sql = sql & "WHERE Year(a.fdate) = " & Range("B1") & " " sql = sql & "Group By a.FCustID,MONTH(a.fdate)) x) y " sql = sql & "LEFT JOIN t_Organization c on y.FCustID=c.FItemID " sql = sql & "Group By c.FName Order By SUM(y.FAmt) DESC" Set rst = ado.Execute(sql) If Not rst.EOF Then Range("A5").CopyFromRecordset rst rst.Close Set rst = Nothing Set ado = Nothing '*******************设置报表格式******************* '取消工作表显示网格线 ActiveWindow.DisplayGridlines = False '先设置报表标题 With Range("A3:N4") .Font.Name = "微软雅黑" '字体名称 .Font.Size = 10 '字体大小 .Font.Color = RGB(255, 255, 255) '字体颜色 .Interior.Color = RGB(72, 99, 156) '背景色 .HorizontalAlignment = xlCenter '水平居中 .VerticalAlignment = xlCenter '垂直居中 End With '设置表体格式 With Range("A5:N" & Range("A" & Rows.Count).End(xlUp).Row) .Font.Name = "宋体" '字体名称 .Font.Name = "Calibri" '数字使用的字体名称 .Font.Size = 10 '字体大小 .VerticalAlignment = xlCenter '垂直居中 End With '设置表格 With Range("A3:N" & Range("A" & Rows.Count).End(xlUp).Row) .Borders.LineStyle = 1 '网格线为实线 .Borders.Color = RGB(221, 221, 221) '网格线颜色 End With '设置行高 With Range("3:" & Range("A" & Rows.Count).End(xlUp).Row) .RowHeight = 18 '行间距为18 End With '设置数字格式 With Range("B5:N" & Range("A" & Rows.Count).End(xlUp).Row) .NumberFormatLocal = "0;;;" '数字格式为2位小数，为0时不显示 End With '填充合计栏公式 Range("B3:N3").Formula = "=SUBTOTAL(9,B5:B" & Range("A" & Rows.Count).End(xlUp).Row & ")" Range("B3:N3").NumberFormatLocal = "0;;;" '提取数据后加上自动筛选 Range("A4").Resize(1, Range("A4").End(xlToRight).Column).AutoFilterEnd Sub