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

系列文章目录

前言

一、背景

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

二、VBA

代码如下（示例）：

Sub AssignTimeSegments()Dim ws As WorksheetSet ws = ThisWorkbook.Sheets("Sheet1") ' 修改为你的工作表名称Dim lastRow As LonglastRow = ws.Cells(ws.Rows.Count, "N").End(xlUp).Row ' 获取N列最后一个非空单元格的行号Dim i As LongFor i = 2 To lastRow ' 假设数据从N2开始Dim cellTime As DatecellTime = ws.Cells(i, "N").Value ' 读取N列的时间Dim segment As IntegerSelect Case cellTimeCase "9:00:00" To "10:00:00"segment = 1Case "10:00:01" To "10:10:00"segment = 2Case "10:10:01" To "10:20:00"segment = 3Case "10:20:01" To "10:30:00"segment = 4Case "10:30:01" To "10:40:00"segment = 5Case "10:40:01" To "10:50:00"segment = 6Case "10:50:01" To "11:00:00"segment = 7Case "11:00:01" To "11:10:00"segment = 8Case "11:10:01" To "11:20:00"segment = 9Case "11:20:01" To "11:30:00"segment = 10Case "11:30:01" To "11:40:00"segment = 11Case "11:40:01" To "11:50:00"segment = 12Case "11:50:01" To "12:00:00"segment = 13Case "12:00:01" To "13:00:00"segment = 14Case "16:00:00" To "16:30:00"segment = 15Case "16:30:01" To "16:40:00"segment = 16Case "16:40:01" To "16:50:00"segment = 17Case "16:50:01" To "17:00:00"segment = 18Case "17:00:01" To "17:10:00"segment = 19Case "17:10:01" To "17:20:00"segment = 20Case "17:20:01" To "17:30:00"segment = 21Case "17:30:01" To "17:40:00"segment = 22Case "17:40:01" To "17:50:00"segment = 23Case "17:50:01" To "18:00:00"segment = 24Case "18:00:01" To "18:30:00"segment = 25Case "22:00:00" To "23:00:00"segment = 26Case "23:00:01" To "23:10:00"segment = 27Case "23:10:01" To "23:20:00"segment = 28Case "23:20:01" To "23:30:00"segment = 29Case "23:30:01" To "23:40:00"segment = 30Case "23:40:01" To "23:50:00"segment = 31Case "23:50:01" To "23:59:59"segment = 32Case "0:00:00" To "1:00:00"segment = 33End Selectws.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))

---

总结

分享：
我们的痛苦不是问题本身带来的，而是我们对这些问题的看法产生的，我们要学会解脱，而解脱的最好方式就是面对不同的情况，用不同的思路，多角度的分析问题；