请教如何按月和按年统计次数

529905149

请教如何按月和按年统计次数，谢谢。

看见星光

透视表最简单

xd3210

Sub 按月统计()
    Dim cnn As Object
    Dim rs As Object
    Dim sql As String
    Dim s1 As String
    Dim s2 As String
    Dim arr_ym() As String
    Dim i As Integer
    Dim irs As Integer
   
    Set cnn = CreateObject("adodb.connection")
    Set rs = CreateObject("adodb.recordset")
    rs.cursorlocation = 3
    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes;imex=1';data source=" & ThisWorkbook.FullName
    sql = "select year(时间) & month(时间)  as ym,地点 as dd,count(*) as cs from [sheet1$a1:b] group by year(时间) & month(时间),地点"
    s1 = "select distinct ym from (" & sql & ")"
    s1 = "select ym from (" & s1 & ") order by val(ym)"
    rs.Open s1, cnn, 1, 3
    irs = rs.RecordCount
    ReDim arr_ym(1 To irs)
    i = 1
    Do While Not rs.EOF
        arr_ym(i) = rs.Fields(0).Value
        i = i + 1
        rs.movenext
    Loop
    For i = 1 To irs
        s2 = s2 & " union all select '" & arr_ym(i) & "' as sj,'地点' as dd,'次数' as cs from [sheet1$a1:a2]"
        s2 = s2 & " union all select '' as sj,dd,cs from(" & sql & ") where ym='" & arr_ym(i) & "'"
    Next
    s2 = Mid(s2, 11)
    rs.Close
    rs.Open s2, cnn, 1, 3
    Sheets("月统计").Range("a1").CurrentRegion.Clear
    Sheets("月统计").Range("a1").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

飞飞来啦

利用数据透视表做就好了，分组。也可以用公式做，只是比较麻烦，附件有结果可以看一下

xd3210

Sub 按年统计()
    Dim cnn As Object
    Dim rs As Object
    Dim sql As String
    Dim s1 As String
    Dim s2 As String
    Dim arr_ym() As String
    Dim i As Integer
    Dim irs As Integer
   
    Set cnn = CreateObject("adodb.connection")
    Set rs = CreateObject("adodb.recordset")
    rs.cursorlocation = 3
    cnn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;hdr=yes;imex=1';data source=" & ThisWorkbook.FullName
    sql = "select year(时间) as ym,地点 as dd,count(*) as cs from [sheet1$a1:b] group by year(时间) ,地点"
    s1 = "select distinct ym from (" & sql & ")"
    s1 = "select ym from (" & s1 & ") order by val(ym)"
    rs.Open s1, cnn, 1, 3
    irs = rs.RecordCount
    ReDim arr_ym(1 To irs)
    i = 1
    Do While Not rs.EOF
        arr_ym(i) = rs.Fields(0).Value
        i = i + 1
        rs.movenext
    Loop
    For i = 1 To irs
        s2 = s2 & " union all select '" & arr_ym(i) & "' as sj,'地点' as dd,'次数' as cs from [sheet1$a1:a2]"
        s2 = s2 & " union all select '' as sj,dd,cs from(" & sql & ") where ym='" & arr_ym(i) & "'"
    Next
    s2 = Mid(s2, 11)
    rs.Close
    rs.Open s2, cnn, 1, 3
    Sheets("年统计").Range("a1").CurrentRegion.Clear
    Sheets("年统计").Range("a1").CopyFromRecordset rs
    rs.Close
    Set rs = Nothing
    cnn.Close
    Set cnn = Nothing
End Sub

YZC51

学习啦！请参考
按月和按年统计.rar (53.27 KB, 下载次数: 121)

2018-4-14 17:35 上传

点击文件名下载附件

shi353

能熟悉運用sql實在不簡單，但對初級的人較難理解，不知此例可以用字典子集作出一樣結果嗎? 字典似乎較容易了解

LMY123

Do While Not rs.EOF