通过指定的不同颜色计算单元格值 [英] Count the cells value by different color specified

问题描述

在按指定的不同颜色计算单元格值时，需要您的帮助.

Please need your help on count the cells value by different color specified.

在一张纸中，一些单元格用红色填充，一些单元格用蓝色填充，有些单元格用绿色填充.输出应分别为红色的单元格数，蓝色的单元格数和绿色的单元格数.

In one sheet, some cells filled with red color, some cells filled with blue color, some cells filled with green color. The output should be cells count red color, cells count of blue color and cells count of green color separately.

这是我尝试过的:

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

推荐答案

您的函数按预期工作:-

Your function works as expected:-

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

公式:

=CountByColor(A:A,A2)

此处A2 Cell用Green填充，绿色索引为14

Here A2 Cell is filled with Green and Green color index is 14

对于我的工作表，我得到的结果为

For my sheet, I got the result as

3

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

Basically you need to execute this formula for three times to get three results

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

这篇关于通过指定的不同颜色计算单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！