Public Sub aa() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim cnstr As String, rsstr As String Dim sql As String Dim ws As WorksheetOn Error Resume Next Set cn = New ADODB.Connection cn.Open "Provider=microsoft.jet.oledb.4.0;" _ & "Extended Properties=Excel 8.0;" _ & "Data Source=" & ThisWorkbook.FullName sql = "select a,sum(b)as b from [sheet1$] group by a" Set rs = New ADODB.Recordset Set rs = cn.Execute(sql, adCmdText) Range("a10").CopyFromRecordset rsrs.Close cn.Close Set rs = Nothing Set cn = Nothing End Sub我以經測試過了,其實就是sql 中的分類匯總
=SUMIF(A:A,A2,B:B)然后填充C列、固化C列(复制、选择性粘贴、数值)删除重复的行
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cnstr As String, rsstr As String
Dim sql As String
Dim ws As WorksheetOn Error Resume Next
Set cn = New ADODB.Connection
cn.Open "Provider=microsoft.jet.oledb.4.0;" _
& "Extended Properties=Excel 8.0;" _
& "Data Source=" & ThisWorkbook.FullName
sql = "select a,sum(b)as b from [sheet1$] group by a"
Set rs = New ADODB.Recordset
Set rs = cn.Execute(sql, adCmdText)
Range("a10").CopyFromRecordset rsrs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub我以經測試過了,其實就是sql 中的分類匯總
'原始数据区: A1:B5
'计算结果放在: D1
'下面要改为你的文件的真实位置以及具体表名称 !!!
Range("D1").Select
Selection.Consolidate Sources:="'C:\[BOOK1.xls]Sheet2'!R1C1:R5C2", Function _
:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub运行结果如下:A B C D E
aa 1 aa 3
aa 2 bb 7
bb 3 cc 5
bb 4
cc 5