Как автоматически фильтровать содержимое таблицы и подсчитывать полученные строки?

Я пытаюсь подсчитать количество случаев события как для дней недели, так и для часов дня (для графического отображения отдельно) с помощью VBA. Отметка даты/времени имеет следующий формат: «28.02.2014 20:32».

Пример:
AppID = 14329
DateCreated = 28.02.14 20:55
День = Пятница
Час = 20:55

Сейчас я копирую данные в новый столбец и устанавливаю формат даты «дддд», чтобы он отображался как «воскресенье» и т. д. Оттуда я выполняю автофильтрацию для каждого дня недели и подсчитываю полученные видимые клетки. Это работает с этим кодом:

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

...так далее.

Однако моя проблема заключается в подсчете событий по часам. Я пытаюсь сделать это аналогичным образом, копируя дату/время в новый столбец и устанавливая формат «ч: мм; @» и выполняя автофильтрацию по часам с помощью этого кода:

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

...так далее.

Это не работает, и я не могу понять, почему. Я попытался изменить критерии1 на другой синтаксис, добавив второй параметр критериев, и я в тупике, почему этот метод будет работать для первого обхода, но не для второго, когда оба столбца получены из одних и тех же исходных данных. Я также попытался изменить данные на другой формат времени (просто «чч» и т. д.).


excel vba autofilter visible
person user3220810    schedule 11.03.2014    source источник
comment
Просто выстрел в темноте, попробуйте изменить 10:?? на 10:*   -  person Automate This    schedule 12.03.2014
comment
Спасибо, Портленд, я пробовал и это, и 10*. Результаты немного другие, но все же не правильные, к сожалению.   -  person user3220810    schedule 12.03.2014

Ответы (1)


arrow_upward
1
arrow_downward

Как Excel хранит даты и время:

Excel использует «порядковую» систему счисления для хранения дат (первый день — 1 января 1900 г.):

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

Теперь используйте эти функции на листе и автозаполнение:

Таблица необработанных данныхТаблица, шаг 1 Таблица, шаг 2Финальная таблица

В существующем коде измените следующее:

    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

Дополнительные примечания:

Очевидно, я просто дал фрагмент кода, но идея заключалась бы в том, что вы могли бы разделить и вести счет каждый час. Тогда составить график должно быть просто.

person Brock Gion    schedule 12.03.2014
comment
Идеально, работал как шарм. Большое спасибо за все подробности, Бобби! - person user3220810; 12.03.2014