用VB代码批量汇总到表格在不同的sheet中

下面是用 VB 代码批量汇总到表格在不同的 sheet 中的示例：

Sub SummarizeData()
    '获取源数据表格
    Dim srcSheet As Worksheet
    Set srcSheet = ThisWorkbook.Worksheets("Sheet1")
    
    '获取目标数据表格
    Dim destSheet As Worksheet
    Set destSheet = ThisWorkbook.Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
    destSheet.Name = "Summary"
    
    '设置目标表格的表头
    destSheet.Cells(1, 1).Value = "姓名"
    destSheet.Cells(1, 2).Value = "科目"
    destSheet.Cells(1, 3).Value = "成绩"
    
    '汇总数据
    Dim lastRow As Long
    lastRow = srcSheet.Cells(srcSheet.Rows.Count, 1).End(xlUp).Row
    
    Dim name As String
    Dim subject As String
    Dim score As Double
    Dim row As Long
    row = 2
    
    For i = 2 To lastRow
        name = srcSheet.Cells(i, 1).Value
        subject = srcSheet.Cells(i, 2).Value
        score = srcSheet.Cells(i, 3).Value
        
        '在目标表格中查找是否已经存在该姓名和科目的记录
        Dim found As Boolean
        found = False
        
        Dim j As Long
        For j = 2 To row - 1
            If destSheet.Cells(j, 1).Value = name And destSheet.Cells(j, 2).Value = subject Then
                found = True
                Exit For
            End If
        Next j
        
        '如果已经存在该姓名和科目的记录，则累计成绩
        If found Then
            destSheet.Cells(j, 3).Value = destSheet.Cells(j, 3).Value + score
        Else
            '否则新增一条记录
            destSheet.Cells(row, 1).Value = name
            destSheet.Cells(row, 2).Value = subject
            destSheet.Cells(row, 3).Value = score
            row = row + 1
        End If
    Next i
End Sub

上述代码将源数据表格中的数据汇总到一个新的表格中，该表格的名称为“Summary”，表格中包含三列，分别是“姓名”、“科目”和“成绩”。如果目标表格中已经存在某个姓名和科目的记录，则将该记录的成绩累加，否则新增一条记录。你可以根据自己的需求修改代码。