《Excel VBA+SQL数据管理与应用模板开发》_工作效率UP!_3.2.9 利用合计函数

【摘要】 本书摘自《Excel VBA+SQL数据管理与应用模板开发》一书中第3章，第2节，韩小良著。

3.2.9 利用合计函数 COUNT、MAX、MIN、SUM、AVG进行查询

对数据进行汇总计算并输出结果是数据统计中经常遇到的一个问题。例如，要汇总各个 客户的订单数和销售额合计，找出销售额最大或最小的记录；对产品进行统计分析，统计 每个产品的订单数、销售量和销售总额，哪个产品销售额最大。这时，可以使用合计函数 COUNT、MAX、MIN、AVG和 SUM 等进行查询。

使用合计函数汇总数据的 SQL语句格式如下：

SELECT 函数名(字段名)AS 别名 FROM 数据表 WHERE 条件

这里，要为查询统计结果定义一个别名，以便将计算结果输出。

例如，下面的表达式就是把销售额的合计数 “SUM(销售额)”定义为一个字段名称“销 售总额”。

SUM(销售额)as 销售总额

案例3-10

下面的例子就是从合同信息中按供货商进行汇总查询，统计各个供货商的合同数与合同 总额。

Sub 案例3-100

Dim cnn As New ADODB.Connection

Dim rs As ADODB.Recordset

Dim ws As Worksheet

Dim i As Integer

Set ws=This Workbook.Worksheets("查找结果")

ws.Cells.ClearContents

ws.Range("A₁ :C1")=Array("供货商","合同数","合同总额")

'建立与工作簿的连接

With cnn

.Provider ="microsoft.ace.oledb.12.0"

.ConnectionString="Extended Properties=Excel 12.0;"_

&"Data Source="&ThisWorkbook.FullName

.Open

End With

'先获取不重复的供货商名单

SQL="select distinct 供货商 from[合同信息$]"

Set rs =New ADODB.Recordset

rs.Open SQL,cnn,adOpenKeyset,adLockOptimistic

ws.Range("A2").CopyFromRecordset rs

'循环每个供货商，进行统计汇总

Fori=2 To ws.Range("A1000").End(xIUp).Row

SQL="select count(供货商)as 合同数，sum(合同金额)as 合同总额 from[合同信息S] where 供货商='"'&ws.Range("A"&i)&"""

Set rs =New ADODB.Recordset

rs.Open SQL,cnn,adOpenKeyset,adLockOptimistic

ws.Range("B"&i)=rs!合同数

ws.Range("C"&i)=rs!合同总额

Next i

'关闭查询和连接

rs.Close

cnn.Close

Set rs =Nothing

Set cnn =Nothing

End Sub

在这个程序中，由于要做多次查找，因此在程序顶部做查询数据集变量声明时，声明 语句如下：

Dim rs As ADODB.Recordset

而在程序中，每次查找之前，首先要赋值一个新的查询数据集变量，语句如下：

Set rs =New ADODB.Recordset

利 用 GROUP BY 将查询结果进行分组

仅仅利用函数来制作合计汇总报表还是不太方便的，因为需要先获取不重复供货商名单， 再依次循环查找和统计。

其实，我们可以直接利用GROUP BY 子句将查询结果分成若干组，以字段值相同的记录 为一组，然后根据情况配合合计函数进行统计汇总，这种汇总方式效率更高。其SQL语句的 格式如下：

SELECT 字段1,SUM(字段2)FROM 数据表GROUP BY 字段1

案例3-11

下面的例子就是从合同信息中按供货商进行汇总查询，统计各个供货商的合同数、合同 总额，并按合同总额从大到小进行排序。

Sub 案例3-110

Dim cnn As New ADODB.Connection

Dim rs As ADODB.Recordset

Dim ws As Worksheet

Set ws =ThisWorkbook.Worksheets("查找结果")

ws.Cells.ClearContents

ws.Range("A1:C1")=Array("供货商","合同数","合同总额")

建立与工作簿的连接

With cnn

.Provider ="microsoft.ace.oledb.12.0"

.ConnectionString="Extended Properties=Excel 12.0;"_

Source="&ThisWorkbook.FullName

.Open

End With

'进行统计汇总

SQL="select 供货商，count(供货商)as 合同数，sum (合同金额)as 合同总额from[合同信息 $]group by 供货商 order by sum(合同金额)desc"

Set rs =New ADODB.Recordset

rs.Open SQL,cnn,adOpenKeyset,adLockOptimistic

'复制汇总结果

ws.Range("A2").CopyFromRecordset rs

'关闭查询和连接

rs.Close

cnn.Close

Set rs =Nothing

Set cnn =Nothing

End Sub

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。