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