如何在Excel另一工作表中转置并分组汇总表格数据？

一、公式实现方案（优先推荐） #

假设原始数据存放在名为Sheet1的工作表中，表头从A1开始（A列：First_Name，B列：Last_Name，C列：Company，D列：Number，E列：Done），汇总结果放在Sheet2中：

1. 提取唯一公司名称
   在Sheet2的A2单元格输入公式，自动生成所有不重复的公司名称：

   =UNIQUE(Sheet1!C:C)
   

   注：如果使用的是Excel 2019及更早版本（无UNIQUE函数），可通过「数据」选项卡的「高级筛选」提取唯一值，或使用数组公式（需按Ctrl+Shift+Enter确认）：

   =INDEX(Sheet1!C:C,MIN(IF(COUNTIF($A$1:A1,Sheet1!C:C)=0,ROW(Sheet1!C:C),99999)))
   

   下拉公式直到出现空值即可获取所有唯一公司。

2. 统计公司人数
   在Sheet2的B2单元格输入公式，下拉填充：

   =COUNTIF(Sheet1!C:C,A2)
   

3. 统计完成情况
   在Sheet2的C2单元格输入公式，下拉填充，格式为「完成数 on 总人数」：

   =COUNTIFS(Sheet1!C:C,A2,Sheet1!E:E,1)&" on "&B2
   

4. 计算数字总和
   在Sheet2的D2单元格输入公式，下拉填充：

   =SUMIF(Sheet1!C:C,A2,Sheet1!D:D)
   

   最后选中D列，设置单元格格式为「数值」并保留两位小数，匹配示例中的22,00格式。

二、VBA代码方案 #

如果需要自动化批量处理，可使用以下VBA宏，执行后会自动生成名为Summary的汇总工作表：

Sub CompanySummary()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long, i As Long
    Dim companyDict As Object
    Dim companyName As String, doneCount As Integer, totalCount As Integer, sumNum As Double
    
    ' 指定源数据工作表
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    
    ' 创建或获取汇总工作表
    On Error Resume Next
    Set wsTarget = ThisWorkbook.Sheets("Summary")
    On Error GoTo 0
    If wsTarget Is Nothing Then
        Set wsTarget = ThisWorkbook.Sheets.Add(After:=wsSource)
        wsTarget.Name = "Summary"
    End If
    
    ' 清空汇总表并设置表头
    wsTarget.Cells.Clear
    wsTarget.Range("A1:D1") = Array("Company", "Number of names", "Done", "Sum of Numbers")
    
    ' 用字典存储分组统计数据
    Set companyDict = CreateObject("Scripting.Dictionary")
    
    ' 获取源数据最后一行
    lastRow = wsSource.Cells(wsSource.Rows.Count, "C").End(xlUp).Row
    
    ' 遍历源数据进行分组统计
    For i = 2 To lastRow
        companyName = wsSource.Cells(i, "C").Value
        doneCount = wsSource.Cells(i, "E").Value
        sumNum = wsSource.Cells(i, "D").Value
        
        If companyDict.Exists(companyName) Then
            ' 更新已有公司的统计值
            companyDict(companyName)(0) = companyDict(companyName)(0) + 1
            companyDict(companyName)(1) = companyDict(companyName)(1) + doneCount
            companyDict(companyName)(2) = companyDict(companyName)(2) + sumNum
        Else
            ' 新增公司统计记录
            companyDict.Add companyName, Array(1, doneCount, sumNum)
        End If
    Next i
    
    ' 将统计结果写入汇总表
    Dim key As Variant
    Dim targetRow As Long
    targetRow = 2
    For Each key In companyDict.Keys
        wsTarget.Cells(targetRow, "A") = key
        wsTarget.Cells(targetRow, "B") = companyDict(key)(0)
        wsTarget.Cells(targetRow, "C") = companyDict(key)(1) & " on " & companyDict(key)(0)
        wsTarget.Cells(targetRow, "D") = companyDict(key)(2)
        targetRow = targetRow + 1
    Next key
    
    ' 设置汇总表格式
    wsTarget.Range("A1:D1").Font.Bold = True
    wsTarget.Columns("A:D").AutoFit
    wsTarget.Columns("D").NumberFormat = "#,##0.00"
    
    MsgBox "统计完成！", vbInformation
End Sub

使用方法：按Alt+F11打开VBA编辑器，插入模块后粘贴代码，运行宏即可。

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