Excel对特定区域按颜色求和 、计数

Question

如题所述

Answer 1

对一些特殊的数据标注了颜色，想要快速求出这些区域的和，有两种方法：

Function SumByColor(Ref_color As Range,Sum_range As Range)

Application.Volatile

Dim iCol As Integer

Dim rCell As Range

iCol = Ref_color.Interior.ColorIndex

For Each rCell In Sum_range

If iCol = rCell.Interior.ColorIndex Then

SumByColor = SumByColor + rCell.Value

End If

Next rCell

End Function

Function CountByColor(Ref_color As Range, CountRange As Range)

Application.Volatile

Dim iCol As Integer

Dim rCell As Range

iCol = Ref_color.Interior.ColorIndex

For Each rCell In CountRange

If iCol = rCell.Interior.ColorIndex Then

CountByColor = CountByColor + 1

End If

Next rCell

End Function