Excel对特定区域按颜色求和 、计数
2016-02-01 本文已影响388人
麦地蓝天
对一些特殊的数据标注了颜色，想要快速求出这些区域的和，有两种方法：
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
Excel对特定区域按颜色求和 、计数
