excel - 如何自动过滤表内容并计算结果行数？

Question

我正在尝试使用 VBA 计算一周中的几天和一天中的几个小时(单独绘制图表)的事件实例。日期/时间戳的格式如下:“2/28/2014 20:32”

示例:
AppID = 14329
创建日期 = 2/28/14 20:55
日 = 星期五
时间 = 20:55

我现在要做的是将数据复制到新列并将日期格式设置为“dddd”，以便它显示为“星期日”等。从那里我会自动过滤一周中的每一天并进行计数由此产生的可见细胞。这是使用此代码:

With Range("C2:C" & LR)
                .AutoFilter

                .AutoFilter Field:=3, Criteria1:="Sunday"
                        cCnt = ActiveSheet.AutoFilter.Range.Columns(3) _
                        .SpecialCells(xlCellTypeVisible).Cells.Count
                            For Each cell In Range("C2:C" & cCnt)
                            Sun = Sun + 1
                            Next

                .AutoFilter Field:=3, Criteria1:="Monday"
                        cCnt = ActiveSheet.AutoFilter.Range.Columns(3) _
                        .SpecialCells(xlCellTypeVisible).Cells.Count
                            For Each cell In Range("C2:C" & cCnt)
                            Mon = Mon + 1
                            Next

...等等。

但是，我的问题是按小时计算事件。我试图通过将日期/时间复制到新列并将格式设置为“h:mm;@”并使用以下代码按小时自动过滤来执行类似的操作:

With Range("D2:D" & LR)
            .AutoFilter

            .AutoFilter Field:=4, Criteria1:="10:??"
                        cCnt = ActiveSheet.AutoFilter.Range.Columns(4) _
                        .SpecialCells(xlCellTypeVisible).Cells.Count
                            For Each cell In Range("D2:D" & cCnt)
                            time(10) = time(10) + 1
                            Next

...等等

这不起作用，我不明白为什么。我尝试将 criteria1 更改为各种语法，添加第二个条件参数，但我很困惑为什么当两列都源自相同的原始数据时，此方法适用于第一次循环，但不适用于第二次循环。我还尝试将数据更改为不同的时间格式(只是“hh”等)。

Answer 1

Excel 如何存储日期和时间:

Excel 使用“序列”数字系统来存储日期(第一天是 1900 年 1 月 1 日):

01/01/1900 = 1

01/02/1900 = 2

时间用小数点后的任何数字表示(中午是 0.5，因为它是一天的一半):

01/01/1900 12:00 = 1.5

01/02/1900 15:00 = 2.625

如果您根据值进行过滤，请务必注意这一点。

将此代码包含在您的模块中:

    Public Function GetDay(ByVal inputDate As String) As String

        GetDay = WeekdayName(Weekday(inputDate))

    End Function


    Public Function GetHour(ByVal inputDate As Range) As String
        
        GetHour = Format(inputDate.Value, "Medium Time")

    End Function

现在在您的工作表和自动填充中使用这些功能:

在现有代码中修改以下内容:

    With Range("D2")

        .AutoFilter Field:=4, Criteria1:="10:?? PM"

            'If criteria didn't match anything, cCnt is equal to 1 (because header rows are visible and counted)
            cCnt = ActiveSheet.AutoFilter.Range.Columns(4).SpecialCells(xlCellTypeVisible).Cells.Count

            If cCnt > 1 Then
            
                For Each cell In Range("D2:D" & cCnt)
                    Hour10PM = Hour10PM + 1
                Next
            
            End If

            MsgBox (Hour10PM & " matches for 10:00PM to 10:59PM")
            
            
        .AutoFilter Field:=4, Criteria1:="11:?? PM"

            cCnt = ActiveSheet.AutoFilter.Range.Columns(4).SpecialCells(xlCellTypeVisible).Cells.Count

            If cCnt > 1 Then
            
                For Each cell In Range("D2:D" & cCnt)
                    Hour11PM = Hour11PM + 1
                Next
                
            End If
            
            MsgBox (Hour11PM & " matches for 11:00PM to 11:59PM")
            
    End With

附加说明:

显然我只是给出了一段代码，但想法是你可以分割并记录每个小时。然后绘制图表应该很简单。