Har du set hvor smart det kan være?

SmartOffice - ColoredCellsCount

Disse brugerdefinerede funktioner kan tælle antallet af celler i et området, som har en specifik baggrundsfarve, mønster eller mønster med farve.

Eksempel 1

Public Function ColoredCellsCount(rCountArea As Range, rCountColor As Range) As Double
'Flemming Vadet, Januar 2002, fv@smartoffice.dk
    Application.Volatile
    Dim dRetVal As Double
    Dim rCell As Range
    Dim iColor As Integer
    For Each rCell In rCountArea
        If rCell.Interior.ColorIndex = rCountColor.Interior.ColorIndex Then
            dRetVal = dRetVal + 1
        End If
    Next rCell
    ColoredCellsCount = dRetVal
    ' Clean up
    Set rCell = Nothing
End Function

Public Function PatternedCellsCount(rCountArea As Range, rCountColor As Range) As Double
'Flemming Vadet, April 2010, fv@smartoffice.dk
    Application.Volatile
    Dim dRetVal As Double
    Dim rCell As Range
    Dim iColor As Integer
    For Each rCell In rCountArea
        If rCell.Interior.Pattern = rCountColor.Interior.Pattern Then
            dRetVal = dRetVal + 1
        End If
    Next rCell
    PatternedCellsCount = dRetVal
    ' Clean up
    Set rCell = Nothing
End Function

Public Function PatternColoredCellsCount(rCountArea As Range, rCountColor As Range) As Double
'Flemming Vadet, April 2010, fv@smartoffice.dk
    Application.Volatile
    Dim dRetVal As Double
    Dim rCell As Range
    Dim iColor As Integer
    For Each rCell In rCountArea
        If rCell.Interior.PatternColorIndex = rCountColor.Interior.PatternColorIndex Then
            dRetVal = dRetVal + 1
        End If
    Next rCell
    PatternColoredCellsCount = dRetVal
    ' Clean up
    Set rCell = Nothing
End Function

I en celle skriver du nedenstående formel, hvor "F1:F20) er det område der skal undersøges og tælles for celler med samme baggrundsfarve som celle "B1" har.

Test 1

=ColoredCellsCount(F1:F20;B1)

Test 2

=PatternedCellsCount(F1:F20;B1)

Test 3

=PatternColoredCellsCount(F1:F20;B1)

Download eksempel

Smart Data Management

Compare 2 Columns

Excel Super- Subscript

Teachers Excel Tools