当前位置：首页 > article >正文

vba学习系列(8)--指定列单元格时间按时间段计数

article 2024/10/27 19:57:41

系列文章目录

文章目录

系列文章目录
前言
一、背景
二、VBA
总结

前言

一、背景

时间格式：00:00:00
时间段格式：00:00:00 - 01:00:00
计数N列单元格时间位于时间段内的行数

二、VBA

代码如下（示例）：

Sub AssignTimeSegments()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称
    
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, "N").End(xlUp).Row ' 获取N列最后一个非空单元格的行号
    
    Dim i As Long
    For i = 2 To lastRow ' 假设数据从N2开始
        Dim cellTime As Date
        cellTime = ws.Cells(i, "N").Value ' 读取N列的时间
        
        Dim segment As Integer
        Select Case cellTime
        
            Case "9:00:00" To "10:00:00"
                 segment = 1
            Case "10:00:01" To "10:10:00"
                segment = 2
            Case "10:10:01" To "10:20:00"
                segment = 3
            Case "10:20:01" To "10:30:00"
                segment = 4
            Case "10:30:01" To "10:40:00"
                segment = 5
            Case "10:40:01" To "10:50:00"
                segment = 6
            Case "10:50:01" To "11:00:00"
                segment = 7
            Case "11:00:01" To "11:10:00"
                segment = 8
            Case "11:10:01" To "11:20:00"
                segment = 9
            Case "11:20:01" To "11:30:00"
                segment = 10
            Case "11:30:01" To "11:40:00"
                segment = 11
            Case "11:40:01" To "11:50:00"
                segment = 12
            Case "11:50:01" To "12:00:00"
                segment = 13
            Case "12:00:01" To "13:00:00"
                segment = 14
            Case "16:00:00" To "16:30:00"
                segment = 15
            Case "16:30:01" To "16:40:00"
                segment = 16
            Case "16:40:01" To "16:50:00"
                segment = 17
            Case "16:50:01" To "17:00:00"
                segment = 18
            Case "17:00:01" To "17:10:00"
                segment = 19
            Case "17:10:01" To "17:20:00"
                segment = 20
            Case "17:20:01" To "17:30:00"
                segment = 21
            Case "17:30:01" To "17:40:00"
                segment = 22
            Case "17:40:01" To "17:50:00"
                segment = 23
            Case "17:50:01" To "18:00:00"
                segment = 24
            Case "18:00:01" To "18:30:00"
                segment = 25
            Case "22:00:00" To "23:00:00"
                segment = 26
            Case "23:00:01" To "23:10:00"
                segment = 27
            Case "23:10:01" To "23:20:00"
                segment = 28
            Case "23:20:01" To "23:30:00"
                segment = 29
            Case "23:30:01" To "23:40:00"
                segment = 30
            Case "23:40:01" To "23:50:00"
                segment = 31
            Case "23:50:01" To "23:59:59"
                segment = 32
            Case "0:00:00" To "1:00:00"
                  segment = 33
                              
        End Select
        
        ws.Cells(i, "O").Value = segment ' 在O列输出对应的数字
    Next i
End Sub

excel函数思路参考：

=IF(COUNTIF(sheet1!$H:$H,G$2)-COUNTIFS(sheet1!$N:$N,"<"&$D3,sheet1!$H:$H,G$2)-COUNTIFS(sheet1!$N:$N,">"&$E3,sheet1!$H:$H,G$2)<=0,"",COUNTIF(sheet1!$H:$H,G$2)-COUNTIFS(sheet1!$N:$N,"<="&$D3,sheet1!$H:$H,G$2)-COUNTIFS(sheet1!$N:$N,">="&$E3,sheet1!$H:$H,G$2))