Har du set hvor smart det kan være?

Microsoft Office, Word skabeloner, Excel regnearkSmart Office Word skabelon system

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 Office Freeware Smart Data Management
Compare 2 Columns
Excel Super- Subscript
Teachers Excel Tools
         
Smart Office - Word og Excel specialist