使用VBA实现数据统计

工作中经常需要用到excel，有时候会统计和计算大量数据，如果用人工来做会耗时耗力，而且容易出错。如果使用excel的公式，稍微复杂一点的需求就没办法实现。

excel支持VBA编程，所以用程序来实现一些复杂的需求，看起来是个不错的选择。

这里以一个具体的需求来讲解VBA编程

有一个原始数据，里面记录了每一个问题由谁处理的，耗时多少。这些问题，有一些是直接由某人处理，有一些是多个人处理过。要求是统计每个人直接处理了多少问题，参与处理了多少问题，花费了多少时间，并计算平均处理一个问题需要多少时间

先在打开的excel界面，按ALT+F11，呼出VBA编辑器。按照下图方式插入模块，在模块上双击，便可以开始编辑代码

首先要定义一个函数，所有的逻辑都在函数里面实现

Sub test()
....
End Sub

如上，定义了函数test，所有的逻辑就在Sub和End Sub之间实现

接着要定义一些变量

Dim MantisRow As Integer
Dim Name As String
Dim ResolvedMantisNum As Integer
Dim ModifyMantisNum As Integer
Dim ResolvedMantisTime As Single
Dim ModifyMantisTime As Single
Dim ResolvedMantis As Boolean
Dim SummaryRow As Integer

MantisRow表示原始数据的一行

Resolve开头的变量，表示直接解决的问题

Modify开头的变量，表示参与解决的问题

接着，我要sheet2中原有的数据清除，sheet2就是用于填充统计和计算后的数据

Sheets("Sheet2").Range("B2:G11").ClearContents

下面这整个一大段，就是逻辑实现了

' 从sheet2中拿出人名与sheet1中比对
For SummaryRow = 2 To Sheets("Sheet2").Cells(65536, 1).End(xlUp).Row
    Name = Sheets("Sheet2").Cells(SummaryRow, 1)
    ResolvedMantisNum = 0
    ModifyMantisNum = 0
    ResolvedMantisTime = 0
    ModifyMantisTime = 0
    
    ' Sheets("Sheet1").Cells(65536, "G").End(xlUp).Row 通过G列来判断总行数，因为第一列会有空行的情况导致数据不准
    For MantisRow = 2 To Sheets("Sheet1").Cells(65536, "G").End(xlUp).Row
        If Sheets("Sheet1").Cells(MantisRow, 1) <> "" Then ' 如果第一列取出来的是mantisID，接着对人名，如果匹配，那么这个mantis就是被他resolved的
            ResolvedMantis = False
            If Sheets("Sheet1").Cells(MantisRow, "C") = Name Then
                ResolvedMantisNum = ResolvedMantisNum + 1
                ResolvedMantis = True
            End If
        Else ' 如果第一列取出来的是空值，那根据ResolvedMantis判断此问题是否被该人resolved的，如果不是就看看修改记录里面有没此人，如果有，那么就要记录到Modify中
            If Sheets("Sheet1").Cells(MantisRow, "G") = Name Then
                If ResolvedMantis Then
                    ResolvedMantisTime = ResolvedMantisTime + Sheets("Sheet1").Cells(MantisRow, "H")
                Else
                    ModifyMantisTime = ModifyMantisTime + Sheets("Sheet1").Cells(MantisRow, "H")
                    ModifyMantisNum = ModifyMantisNum + 1
                End If
            End If
        End If
    Next
    Sheets("Sheet2").Cells(SummaryRow, "B") = ResolvedMantisNum ' resolved掉的mantis数量
    Sheets("Sheet2").Cells(SummaryRow, "C") = ModifyMantisNum ' 修改过的mantis数量
    Sheets("Sheet2").Cells(SummaryRow, "D") = ResolvedMantisTime ' resolved mantis的总时间
    Sheets("Sheet2").Cells(SummaryRow, "E") = ModifyMantisTime ' 修改mantis的总时间
    Sheets("Sheet2").Cells(SummaryRow, "F") = ModifyMantisTime + ResolvedMantisTime ' 处理mantis使用的总时间
    If (ResolvedMantisNum + ModifyMantisNum) = 0 Then ' 处理没有mantis的情况
        Sheets("Sheet2").Cells(SummaryRow, "G") = 0
    Else
        ' 平均处理一条mantis耗费的时间
        Sheets("Sheet2").Cells(SummaryRow, "G") = (ModifyMantisTime + ResolvedMantisTime) / (ResolvedMantisNum + ModifyMantisNum)
    End If
Next

大致讲解一下，Sheets("Sheet2").Cells(65536, 1).End(xlUp).Row就表示统计表格的最大行数，最外面的for循环，就是要在sheet2中，按照每行，将计算好的数据填进去。

sheet1就是原始数据表格，里面这个for循环就是统计每个人的数据

最后，下面这段，就是将统计和计算好的数据，填入到sheet2中相应的位置中

    Sheets("Sheet2").Cells(SummaryRow, "B") = ResolvedMantisNum ' resolved掉的mantis数量
    Sheets("Sheet2").Cells(SummaryRow, "C") = ModifyMantisNum ' 修改过的mantis数量
    Sheets("Sheet2").Cells(SummaryRow, "D") = ResolvedMantisTime ' resolved mantis的总时间
    Sheets("Sheet2").Cells(SummaryRow, "E") = ModifyMantisTime ' 修改mantis的总时间
    Sheets("Sheet2").Cells(SummaryRow, "F") = ModifyMantisTime + ResolvedMantisTime ' 处理mantis使用的总时间
    If (ResolvedMantisNum + ModifyMantisNum) = 0 Then ' 处理没有mantis的情况
        Sheets("Sheet2").Cells(SummaryRow, "G") = 0
    Else
        ' 平均处理一条mantis耗费的时间
        Sheets("Sheet2").Cells(SummaryRow, "G") = (ModifyMantisTime + ResolvedMantisTime) / (ResolvedMantisNum + ModifyMantisNum)

至此，程序完成，如果要运行的话，选择运行菜单中的运行子过程，或者直接按F5就可以了