基于多条件的Excel VBA生成器步骤计数器开发需求

统计规则明确 #

• 仅Hochzeit有时间戳、Setup无时间戳：生成器当前处于Setup步骤
• Hochzeit和Setup均有时间戳：
  • 若Dauertest无时间戳 → 处于Dauertest步骤
  • 若Dauertest有时间戳、Endkontrolle无时间戳 → 处于Endkontrolle步骤
• 未完成Hochzeit的生成器：不计入任何步骤统计

改进后的VBA代码 #

Sub CountGeneratorSteps()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim countSetup As Long, countDauertest As Long, countEndkontrolle As Long
    
    ' 指定目标工作表（根据实际表名修改）
    Set ws = ThisWorkbook.Worksheets("生成器列表")
    
    ' 初始化计数器
    countSetup = 0
    countDauertest = 0
    countEndkontrolle = 0
    
    ' 获取数据最后一行（假设Hochzeit在B列，Setup在C列，Dauertest在D列，Endkontrolle在E列）
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    
    ' 从第2行遍历（第1行为表头）
    For i = 2 To lastRow
        ' 校验各步骤时间戳有效性
        Dim hasHochzeit As Boolean, hasSetup As Boolean
        Dim hasDauertest As Boolean, hasEndkontrolle As Boolean
        
        hasHochzeit = Not IsEmpty(ws.Cells(i, "B").Value) And IsDate(ws.Cells(i, "B").Value)
        hasSetup = Not IsEmpty(ws.Cells(i, "C").Value) And IsDate(ws.Cells(i, "C").Value)
        hasDauertest = Not IsEmpty(ws.Cells(i, "D").Value) And IsDate(ws.Cells(i, "D").Value)
        hasEndkontrolle = Not IsEmpty(ws.Cells(i, "E").Value) And IsDate(ws.Cells(i, "E").Value)
        
        ' 按规则分类计数
        Select Case True
            Case hasHochzeit And Not hasSetup
                countSetup = countSetup + 1
            Case hasHochzeit And hasSetup And Not hasDauertest
                countDauertest = countDauertest + 1
            Case hasHochzeit And hasSetup And hasDauertest And Not hasEndkontrolle
                countEndkontrolle = countEndkontrolle + 1
        End Select
    Next i
    
    ' 输出统计结果（示例输出到G、H列）
    ws.Range("G2:G4") = Application.Transpose(Array("Setup中", "Dauertest中", "Endkontrolle中"))
    ws.Range("H2:H4") = Application.Transpose(Array(countSetup, countDauertest, countEndkontrolle))
    ' 补充统计未启动/未完成Hochzeit的数量
    ws.Range("G5").Value = "未启动/未完成Hochzeit"
    ws.Range("H5").Value = lastRow - 1 - (countSetup + countDauertest + countEndkontrolle)
    
    MsgBox "统计完成！", vbInformation
End Sub

关键改进点 #

1. 前置步骤校验：不再单独统计空单元格，而是结合前序步骤的完成状态判断当前步骤
2. 时间有效性验证：用IsDate确保单元格内容是合法时间戳，避免空文本、无效值干扰统计
3. 灵活适配：代码中列标识可根据实际表格结构修改，输出位置也可自行调整

内容的提问来源于stack exchange，提问作者Gaz