jira统计

测试部 > jira统计 > image2023-11-29 16:4:20.png




测试部 > jira统计 > image2023-11-29 16:4:52.png




测试部 > jira统计 > image2023-11-29 16:5:5.png



Sub 汇总统计()
    Dim wsData As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' 设置数据工作表和汇总工作表
    Set wsData = ThisWorkbook.Sheets(1) ' 使用工作簿中的第一个工作表作为数据工作表
    
    ' 检查汇总工作表是否已存在，如果存在则清空该工作表，否则创建一个新的汇总工作表
    On Error Resume Next
    Set wsSummary = ThisWorkbook.Sheets("汇总工作表")
    If Not wsSummary Is Nothing Then
        Application.DisplayAlerts = False ' 关闭警告提示
        wsSummary.Cells.Clear ' 清空汇总工作表中的数据
        Application.DisplayAlerts = True ' 打开警告提示
    Else
        Set wsSummary = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsSummary.Name = "汇总工作表" ' 设置汇总工作表名称
    End If
    
    ' 在汇总工作表中创建标题行
    wsSummary.Cells(1, 1).Value = "问题类型"
    wsSummary.Cells(1, 2).Value = "问题级别"
    wsSummary.Cells(1, 3).Value = "系统名称"
    wsSummary.Cells(1, 4).Value = "数量"
    
    ' 获取数据工作表中的最后一行和最后一列
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
    
    ' 定义变量用于存储问题类型、问题级别和系统名称所在的列号
    Dim issueTypeCol As Long
    Dim issueLevelCol As Long
    Dim systemNameCol As Long
    
    ' 遍历第一行的每一列，查找问题类型、问题级别和系统名称所在的列号
    For i = 1 To lastCol
        If wsData.Cells(1, i).Value = "问题类型" Then
            issueTypeCol = i
        ElseIf wsData.Cells(1, i).Value = "问题级别" Then
            issueLevelCol = i
        ElseIf wsData.Cells(1, i).Value = "系统名称" Then
            systemNameCol = i
        End If
    Next i
    
    ' 如果未找到问题类型、问题级别或系统名称列，则显示错误消息并退出子过程
    If issueTypeCol = 0 Or issueLevelCol = 0 Or systemNameCol = 0 Then
        MsgBox "未找到问题类型、问题级别或系统名称列，请检查标题名称！", vbExclamation
        Exit Sub
    End If
    
    ' 定义变量用于存储汇总结果
    Dim summaryData As Object
    Set summaryData = CreateObject("Scripting.Dictionary")
    
    ' 遍历数据工作表中的每一行
    For i = 2 To lastRow
        ' 获取问题类型、问题级别和系统名称
        Dim issueType As String
        Dim issueLevel As String
        Dim systemName As String
        
        issueType = wsData.Cells(i, issueTypeCol).Value
        issueLevel = wsData.Cells(i, issueLevelCol).Value
        systemName = wsData.Cells(i, systemNameCol).Value
        
        ' 构建唯一键，以问题类型、问题级别和系统名称作为联合键
        Dim key As String
        key = issueType & "|" & issueLevel & "|" & systemName
        
        ' 如果唯一键已存在，则增加数量；否则添加新的唯一键并设置数量为1
        If summaryData.Exists(key) Then
            summaryData(key) = summaryData(key) + 1
        Else
            summaryData.Add key, 1
        End If
    Next i
    
    ' 将汇总结果写入汇总工作表
    Dim rowIndex As Long
    rowIndex = 2 ' 从第2行开始写入数据
    Dim dictKey As Variant
    For Each dictKey In summaryData.keys
        ' 解析唯一键中的问题类型、问题级别和系统名称
        Dim issueTypeInfo As Variant
        issueTypeInfo = Split(dictKey, "|")
        Dim summaryIssueType As String
        Dim summaryIssueLevel As String
        Dim summarySystemName As String
        summaryIssueType = issueTypeInfo(0)
        summaryIssueLevel = issueTypeInfo(1)
        summarySystemName = issueTypeInfo(2)
        
        ' 写入汇总数据
        With wsSummary
            .Cells(rowIndex, 1).Value = summaryIssueType
            .Cells(rowIndex, 2).Value = summaryIssueLevel
            .Cells(rowIndex, 3).Value = summarySystemName
            .Cells(rowIndex, 4).Value = summaryData(dictKey)
        End With
        rowIndex = rowIndex + 1
    Next dictKey
    
    ' 格式化汇总工作表
    wsSummary.Columns("A:D").AutoFit
    wsSummary.Range("A1:D1").Font.Bold = True
    
   ' 对汇总表进行排序
With wsSummary
    ' 获取汇总数据的范围
    Dim summaryRange As Range
    Set summaryRange = .Range("A1").CurrentRegion
    ' 按照系统名称、问题类型、问题级别进行排序
    With summaryRange
        .Sort key1:=.Columns(3), order1:=xlAscending, _
              key2:=.Columns(1), order2:=xlAscending, _
              key3:=.Columns(2), order3:=xlAscending, _
              Header:=xlYes
    End With
End With
    
    MsgBox "汇总统计已完成！", vbInformation
End Sub




Sub 汇总统计()
    Dim wsData As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' 设置数据工作表和汇总工作表
    Set wsData = ThisWorkbook.Sheets(1) ' 使用工作簿中的第一个工作表作为数据工作表
    
    ' 检查汇总工作表是否已存在，如果存在则清空该工作表，否则创建一个新的汇总工作表
    On Error Resume Next
    Set wsSummary = ThisWorkbook.Sheets("汇总工作表")
    If Not wsSummary Is Nothing Then
        Application.DisplayAlerts = False ' 关闭警告提示
        wsSummary.Cells.Clear ' 清空汇总工作表中的数据
        Application.DisplayAlerts = True ' 打开警告提示
    Else
        Set wsSummary = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
        wsSummary.Name = "汇总工作表" ' 设置汇总工作表名称
    End If
    
    ' 在汇总工作表中创建标题行
    wsSummary.Cells(1, 1).Value = "问题类型"
    wsSummary.Cells(1, 2).Value = "状态"
    wsSummary.Cells(1, 3).Value = "系统名称"
    wsSummary.Cells(1, 4).Value = "数量"
    
    ' 获取数据工作表中的最后一行和最后一列
    lastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row
    lastCol = wsData.Cells(1, wsData.Columns.Count).End(xlToLeft).Column
    
    ' 定义变量用于存储问题类型、状态和系统名称所在的列号
    Dim issueTypeCol As Long
    Dim issueLevelCol As Long
    Dim systemNameCol As Long
    
    ' 遍历第一行的每一列，查找问题类型、状态和系统名称所在的列号
    For i = 1 To lastCol
        If wsData.Cells(1, i).Value = "问题类型" Then
            issueTypeCol = i
        ElseIf wsData.Cells(1, i).Value = "状态" Then
            issueLevelCol = i
        ElseIf wsData.Cells(1, i).Value = "系统名称" Then
            systemNameCol = i
        End If
    Next i
    
    ' 如果未找到问题类型、状态或系统名称列，则显示错误消息并退出子过程
    If issueTypeCol = 0 Or issueLevelCol = 0 Or systemNameCol = 0 Then
        MsgBox "未找到问题类型、状态或系统名称列，请检查标题名称！", vbExclamation
        Exit Sub
    End If
    
    ' 定义变量用于存储汇总结果
    Dim summaryData As Object
    Set summaryData = CreateObject("Scripting.Dictionary")
    
    ' 遍历数据工作表中的每一行
    For i = 2 To lastRow
        ' 获取问题类型、状态和系统名称
        Dim issueType As String
        Dim issueLevel As String
        Dim systemName As String
        
        issueType = wsData.Cells(i, issueTypeCol).Value
        issueLevel = wsData.Cells(i, issueLevelCol).Value
        systemName = wsData.Cells(i, systemNameCol).Value
        
        ' 构建唯一键，以问题类型、状态和系统名称作为联合键
        Dim key As String
        key = issueType & "|" & issueLevel & "|" & systemName
        
        ' 如果唯一键已存在，则增加数量；否则添加新的唯一键并设置数量为1
        If summaryData.Exists(key) Then
            summaryData(key) = summaryData(key) + 1
        Else
            summaryData.Add key, 1
        End If
    Next i
    
    ' 将汇总结果写入汇总工作表
    Dim rowIndex As Long
    rowIndex = 2 ' 从第2行开始写入数据
    Dim dictKey As Variant
    For Each dictKey In summaryData.keys
        ' 解析唯一键中的问题类型、状态和系统名称
        Dim issueTypeInfo As Variant
        issueTypeInfo = Split(dictKey, "|")
        Dim summaryIssueType As String
        Dim summaryIssueLevel As String
        Dim summarySystemName As String
        summaryIssueType = issueTypeInfo(0)
        summaryIssueLevel = issueTypeInfo(1)
        summarySystemName = issueTypeInfo(2)
        
        ' 写入汇总数据
        With wsSummary
            .Cells(rowIndex, 1).Value = summaryIssueType
            .Cells(rowIndex, 2).Value = summaryIssueLevel
            .Cells(rowIndex, 3).Value = summarySystemName
            .Cells(rowIndex, 4).Value = summaryData(dictKey)
        End With
        rowIndex = rowIndex + 1
    Next dictKey
    
    ' 格式化汇总工作表
    wsSummary.Columns("A:D").AutoFit
    wsSummary.Range("A1:D1").Font.Bold = True
    
   ' 对汇总表进行排序
With wsSummary
    ' 获取汇总数据的范围
    Dim summaryRange As Range
    Set summaryRange = .Range("A1").CurrentRegion
    ' 按照系统名称、问题类型、状态进行排序
    With summaryRange
        .Sort key1:=.Columns(3), order1:=xlAscending, _
              key2:=.Columns(1), order2:=xlAscending, _
              key3:=.Columns(2), order3:=xlAscending, _
              Header:=xlYes
    End With
End With
    
    MsgBox "汇总统计已完成！", vbInformation
End Sub




测试部 > jira统计 > image2023-11-29 16:16:26.png