一个有趣的统计 后续

版权声明：本文为博主原创文章，转载请在显著位置标明本文出处以及作者网名，未经作者允许不得用于商业目的。

上一篇统计的并非是真正的节假日的情况。

于是我又采用了一种方式，将所有假日标识出来，然后再统计，excel本身不能获得那些日子是节假日，只有通过手工填写标识。

还是先程序标识出所有的周末，这样修改起来更方便些：

Sub signWeekEnd()
'
    Dim i As Integer
    Dim visitdatestr As String
    Dim visitdate As Date
    Dim weekindex As Integer
    
    For i = 2 To 185
        visitdatestr = Range("A" & i).Value
        visitdate = DateValue(Left(visitdatestr, 4) & "-" & Mid(visitdatestr, 5, 2) & "-" & Right(visitdatestr, 2))
        weekindex = Weekday(visitdate, vbMonday)
        
        Select Case weekindex
            Case 6, 7
                Range("C" & i).Value = "Y"
            Case Else
                Range("C" & i).Value = "N"
        End Select
    Next
    
End Sub

 

然后手工再把放假的日期修改为Y，把调班的日期修改为N。接着就可以继续用代码了：

Sub statistic2()
'
    Dim i As Integer
    Dim holiday As String
    
    Dim visitCount As Integer
    Dim visitWeekendCount As Integer
    Dim visitNoWCount As Integer
    
    Dim visitPersonCount As Long
    Dim visitPersonWCount As Long
    Dim visitPersonNoWcount As Long
    
    visitCount = 0
    visitWeekendCount = 0
    visitPersonCount = 0
    visitPersonWCount = 0
    visitNoWCount = 0
    visitPersonNoWcount = 0
    
    Dim visitPersonCountPerDay As Integer
    
    
    For i = 3 To 185
        holiday = Range("C" & i).Value
        visitPersonCountPerDay = Range("B" & i).Value - Range("B" & (i - 1)).Value
        
        visitCount = visitCount + 1
        
        visitPersonCount = visitPersonCount + visitPersonCountPerDay
        
        
        Select Case holiday
            Case "Y"
                Range("A" & i).Interior.ColorIndex = 43
                visitWeekendCount = visitWeekendCount + 1
                visitPersonWCount = visitPersonWCount + visitPersonCountPerDay
            Case Else
                visitNoWCount = visitNoWCount + 1
                visitPersonNoWcount = visitPersonNoWcount + visitPersonCountPerDay
                
        End Select
    Next
    
    MsgBox ("记录总日期：" & visitCount & " 天，假日：" & visitWeekendCount & " 天，工作日：" & visitNoWCount & "天" & vbCrLf & _
    "平均访问人次：" & (visitPersonCount / visitCount) & vbCrLf & _
    "假日平均访问人次：" & (visitPersonWCount / visitWeekendCount) & vbCrLf & _
    "工作日平均访问人次：" & (visitPersonNoWcount / visitNoWCount))
    
End Sub

运行如下：

和之前的计算相差不是太大。

 

学习更多vb.net知识，请参看vb.net 教程 目录