数据统计

Option Explicit
Sub 总计()
'汇总从2018年1月份至今的所有小试流程
    '循环总表
    Dim i, cntcheakY, cntcheakN, cntexam, ws1, str1, str2
    i = 3
    cntcheakY = 0
    cntcheakN = 0
    cntexam = 0
    Set ws1 = Worksheets("总表")
    '获取表中第一个流程的时间
        str1 = Left(ws1.Cells(i, 6), 9)
    While ws1.Cells(i, 1) <> ""
        If ws1.Cells(i, 8) = "Y" Then
            cntcheakY = cntcheakY + 1
            If ws1.Cells(i, 9) = "Y" Then
                cntexam = cntexam + 1
            End If
        ElseIf ws1.Cells(i, 8) = "N" Then
            cntcheakN = cntcheakN + 1
        End If
        i = i + 1
    Wend
    
    '将得到的结果填入到对应的单元格中去，并做运算
    '时间：20180105-最后一个流程
    str2 = Left(ws1.Cells(i - 1, 6), 8)
    Cells(2, 1) = str1 + str2
    '数量
    Cells(2, 2) = i - 2
    '检测合格数及合格率
    Cells(2, 3) = cntcheakY
    Cells(2, 4) = cntcheakY / (cntcheakY + cntcheakN) * 100
    '小试合格数及合格率
    Cells(2, 5) = cntexam
    Cells(2, 6) = cntexam / cntcheakY * 100
    '调试合格数
    '合格供应商数
End Sub

Sub 年度()
    Dim i, cnt, ypoint As Integer, ws1, cntcheakY, cntcheakN, cntexam, year As Integer
    i = 3
    cnt = 0
    cntcheakY = 0
    cntcheakN = 0
    cntexam = 0
    Set ws1 = Worksheets("总表")
    'ypoint用于给时间定位
    ypoint = Left(ws1.Cells(i, 6), 4)
    '获取表中的时间
    While ws1.Cells(i, 1) <> ""
        year = Left(ws1.Cells(i, 6), 4)
        If year = ypoint Then
            cnt = cnt + 1
            If ws1.Cells(i, 8) = "Y" Then
                cntcheakY = cntcheakY + 1
                If ws1.Cells(i, 9) = "Y" Then
                cntexam = cntexam + 1
                End If
            ElseIf ws1.Cells(i, 8) = "N" Then
                cntcheakN = cntcheakN + 1
            End If
        '送样数量
        Cells(2, 2) = cnt
        '检测合格数及合格率
        Cells(2, 3) = cntcheakY
        Cells(2, 4) = cntcheakY / (cntcheakY + cntcheakN) * 100
        '小试合格数及合格率
        Cells(2, 5) = cntexam
        Cells(2, 6) = cntexam / cntcheakY * 100
        '调试合格数
        '合格供应商数
        
        Else
        
                
        i = i + 1
End Sub
Public Sub 写数据()
  
End Sub