以下是一个示例VBA代码,实现了上述功能:
Sub SummarizeByCellColor() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Sheet1") Dim dataRange As Range Set dataRange = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row) Dim colors As Collection Set colors = New Collection Dim cell As Range, color As Variant Dim i As Integer ' 收集颜色 On Error Resume Next For Each cell In dataRange colors.Add cell.Interior.Color, CStr(cell.Interior.Color) Next cell On Error GoTo 0 ' 将颜色种类填充到B列 i = 1 For Each color In colors ws.Cells(i, 2).Interior.Color = color ws.Cells(i, 2).Value = "Color " & i i = i + 1 Next color ' 遍历B列中的每种颜色,并在C列进行汇总 Dim sum As Double For i = 1 To colors.Count sum = 0 For Each cell In dataRange If cell.Interior.Color = ws.Cells(i, 2).Interior.Color Then sum = sum + cell.Value End If Next cell ws.Cells(i, 3).Value = sum Next iEnd Sub代码解释:
首先,创建一个Collection来存储唯一的颜色值。遍历A列,收集每个单元格的颜色。将收集到的颜色填充到B列,同时对每种颜色命名为"Color 1", "Color 2"等。对于B列中的每种颜色,遍历A列并汇总具有相同颜色的单元格的数值到C列。在运行此代码之前,请确保你的Excel工作表名为"Sheet1",并且A列包含你想要分析的数据。此代码可以根据实际情况进行调整和优化。