Question

请帮助您根据不同颜色的指定计算单元格值。

在一张工作表中,有些单元格填满了“强度”颜色

这就是我曾经尝试过的:

Function CountByColor(InputRange As Range, ColorRange As Range) As Long

    Dim cl As Range
    TempCount As Long
    ColorIndex As Integer

    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex TempCount = 0
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex
        Then
        TempCount = TempCount + 1
        End If
    Next cl
    Set cl = Nothing CountByColor = TempCount
End Function

Answer 1

您的函数按预期工作 :

Function CountByColor(InputRange As Range, ColorRange As Range) As Long
    Dim cl As Range, TempCount As Long, ColorIndex As Integer
    ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
    TempCount = 0
    For Each cl In InputRange.Cells
        If cl.Interior.ColorIndex = ColorIndex Then
            TempCount = TempCount + 1
        End If
    Next cl
    Set cl = Nothing
    CountByColor = TempCount
End Function

Formula:

=CountByColor(A:A,A2)

这里 A2 单元格 填入 green,绿色指数为 14

Result:

为了我的床单我拿到了结果

基本上您需要执行此公式三次才能取得三次结果

=CountByColor(A:A,A2)   // A2 filled with Green
=CountByColor(A:A,A6)   // A6 filled with Red
=CountByColor(A:A,A9)   // A9 filled with Blue

Formula:

Result:

友情链接