测试部 > 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
01-30
1394
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
08-26
05-06
9655
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
09-22
5236
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
06-02