EXCEL VBA 根据年月日统计sheet2A列符合年月日下的时分秒对应单元格数

在Excel VBA中，你可以编写一段宏来统计Sheet2的A列中特定日期范围内每个小时、分钟和秒的单元格数量。以下是简单的步骤和示例代码：

1. 打开VBA编辑器（可通过菜单栏的"开发者"->"Visual Basic"打开）。

2. 在新的模块中，创建一个新的Sub过程，例如`CountCellsByTime`。

Sub CountCellsByTime()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet2") '指定你要操作的工作表
    
    Dim startDate As Date
    Dim endDate As Date
    '假设你已经有了起始和结束日期，如果没有，需要先输入或获取
    startDate = #2023/1/1# '这里以2023年1月1日为例
    endDate = #2023/12/31# '一年的结束日期

    Dim ACell As Range
    Dim currentDate As Date
    Dim hourCounter(24) As Integer '用于存储每个小时的计数，范围从0到23
    Dim minuteCounter(60) As Integer '类似，范围从0到59
    Dim secondCounter(59) As Integer '范围从0到58

    For Each ACell In ws.Range("A:A")
        If ACell.Value >= startDate And ACell.Value <= endDate Then
            currentDate = ACell.Value
            hourCounter Hour(currentDate) = hourCounter Hour(currentDate) + 1
            minuteCounter Minute(currentDate) = minuteCounter Minute(currentDate) + 1
            secondCounter Second(currentDate) = secondCounter Second(currentDate) + 1
        End If
    Next ACell

    '在这里打印或更新一个表格显示结果
    Dim tbl As ListObject
    On Error Resume Next
    Set tbl = ws.ListObjects.Add(xlSrcRange, ws.Range("B1:E1"), , xlYes)
    On Error GoTo 0
    tbl.Name = "TimeCounts"

    With tbl.ListRows.Add
        .Range(1, 1).Value = Array("小时", "分钟", "秒", "计数")
        For i = 0 To 23
            .Range(i + 2, 1).Value = i + 1
            .Range(i + 2, 2).Value = hourCounter(i)
        Next i
        For i = 0 To 59
            .Range(i + 2, 3).Value = i + 1
            .Range(i + 2, 4).Value = minuteCounter(i)
        Next i
        For i = 0 To 58
            .Range(i + 2, 5).Value = i + 1
            .Range(i + 2, 5).Value = secondCounter(i)
        Next i
    End With
End Sub

运行这个宏后，它会在Sheet2的某处生成一个列表，列出每天从早到晚每个小时、分钟和秒的计数。