求助如何按指定的列进行数据汇总？

lovelfg

740688321 发表于 2019-6-12 17:44
给个意思，完全可以自己去手嘛

我不懂数据库语句

boy8199

= SUMIFS(B2:B10,A2:A10,A20,H2:H10,"main")
===========
一个公式解决的问题,非要 整 vba

属于 过度.

740688321

lovelfg 发表于 2019-6-12 17:17
厉害！这次对了

Sub MySum1()
    Dim cnn As Object, sql$, i%
    Set cnn = CreateObject("ADODB.Connection")
    If Application.Version < 12 Then
        cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
    Else
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;Data Source=" & ThisWorkbook.FullName
    End If
    '''求和列
    sql = "select 标题1,sum(标题2) from [Sheet1$a1:H10] group by 标题1"
    Cells(Rows.Count, "A").End(xlUp).Offset(1).CopyFromRecordset cnn.Execute(sql)

    sql = "select 标题3,标题4,标题5,标题6,标题7,标题8 from [Sheet1$a1:H10] where 标题8='main'"
    Cells(Rows.Count, "C").End(xlUp).Offset(1).CopyFromRecordset cnn.Execute(sql)

    cnn.Close
    Set cnn = Nothing
End Sub