Ga naar hoofdinhoud

Excel-tips: cellen tellen/optellen op kleur (achtergrond, lettertype, voorwaardelijke opmaak)

Bij dagelijkse taken is kleurmarkering een populaire methode om cruciale gegevens snel te onderscheiden en te markeren. Maar hoe tellen of tellen we celgegevens op op basis van een specifieke kleur (vulkleur, letterkleur, voorwaardelijke opmaak)? Standaard biedt Excel geen directe functie om op kleur te tellen of op te tellen. Niettemin kunnen we dit met enkele trucjes en indirecte methoden nog steeds bereiken. In dit artikel wordt onderzocht hoe u gegevens op kleur kunt tellen of optellen.

Tel en som cellen op basis van achtergrondkleur

Tel en tel cellen op basis van de letterkleur

Tel en tel cellen op basis van de kleur van de voorwaardelijke opmaak


Video: Cellen tellen en optellen op basis van kleur


Tel en som cellen op basis van achtergrondkleur

Als u bijvoorbeeld een gegevensbereik heeft waarbij de waarden zijn gevuld met verschillende achtergrondkleuren, zoals weergegeven in de onderstaande schermafbeelding. Om de cellen te tellen of op te tellen op basis van een specifieke kleur, biedt Excel geen directe functie om cellen te tellen of op te tellen op basis van hun achtergrondkleur. Met een beetje vindingrijkheid en enkele handige technieken kun je deze taak echter volbrengen. Laten we in dit gedeelte enkele nuttige methoden verkennen.


Tel en tel cellen op achtergrondkleur met de door de gebruiker gedefinieerde functie

Hier laten we u zien hoe u een dergelijke door de gebruiker gedefinieerde functie kunt maken en gebruiken om deze taak in Excel op te lossen. Voer de volgende stappen uit:

Stap 1: Open de VBA-module-editor en kopieer de code

  1. Media Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
  2. Klik in het geopende venster Invoegen > Module om een ​​nieuwe lege module te maken.
  3. Kopieer en plak vervolgens de onderstaande code in de lege module.
    VBA-code: tel en tel cellen op basis van de achtergrondkleur
    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean = False) As Variant
    'Updateby Extendoffice
        Dim rCell As Range
        Dim lCol As Long
        Dim vResult As Double
        lCol = rColor.Interior.ColorIndex
        vResult = 0
        If SUM Then
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + rCell.Value
                End If
            Next rCell
        Else
            For Each rCell In rRange
                If rCell.Interior.ColorIndex = lCol Then
                    vResult = vResult + 1
                End If
            Next rCell
        End If
        ColorFunction = vResult
    End Function
    

Stap 2: Maak formules om cellen te tellen en op te tellen op achtergrondkleur

Nadat u de bovenstaande code hebt geplakt, sluit u het modulevenster en past u vervolgens de volgende formules toe:

  • Tel cellen op basis van de specifieke achtergrondkleur:
    Kopieer of typ de onderstaande formule in de gewenste cel voor het resultaat. Sleep vervolgens de vulgreep naar beneden om andere resultaten te krijgen. Zie schermafbeelding:
    =colorfunction(G2,$B$2:$E$12,FALSE) 
    Note: In deze formule, G2 is de referentiecel met de specifieke achtergrondkleur die u wilt matchen; $B$2:$E$12 is het bereik waar u het aantal cellen van de kleur van G2 wilt tellen; Juist wordt gebruikt om cellen met bijpassende kleur te tellen.
  • Som cellen op basis van de specifieke achtergrondkleur:
    Kopieer of typ de onderstaande formule in de gewenste cel voor het resultaat. Sleep vervolgens de vulgreep naar beneden om andere resultaten te krijgen. Zie schermafbeelding:
    =colorfunction(G2,$B$2:$E$12,TRUE)  
    Note: In deze formule, G2 is de referentiecel met de specifieke achtergrondkleur die u wilt matchen; $B$2:$E$12 is het bereik waar u het aantal cellen van de kleur van G2 wilt tellen; TRUE wordt gebruikt om cellen met bijpassende kleur op te tellen.

Tel en tel cellen op achtergrondkleur met een krachtige functie

Voor degenen die niet bekend zijn met programmeren, kan VBA behoorlijk complex lijken. Hier introduceren we een krachtig hulpmiddel - Kutool voor Excel, het is Tel op kleur Met deze functie kunt u met slechts een paar klikken eenvoudig berekenen (tellen, optellen, gemiddelde, etc.) op basis van de achtergrondkleur. Indrukwekkend, Tel op kleur Deze functie gaat verder dan alleen achtergrondkleuren: het kan ook differentiëren en berekenen op basis van lettertypekleuren en voorwaardelijke opmaak.

Na Kutools voor Excel downloaden en installerenSelecteer eerst het gegevensbereik dat u wilt tellen of cellen wilt optellen op basis van een specifieke achtergrondkleur. Navigeer vervolgens naar Koetools Plus En kies Tel op kleur.

In het Tel op kleur dialoogvenster, specificeer de bewerkingen:

  1. kies Standaardopmaak van het Kleur methode keuzelijst;
  2. Specificeren Achtergrond van het Type telling vervolgkeuzelijst en u kunt een voorbeeld van de statistische resultaten voor elke achtergrondkleur bekijken in het dialoogvenster;
  3. Eindelijk, klik Genereer rapport om de berekende resultaten naar een nieuwe werkmap te exporteren.

Resultaat:

Nu krijg je een nieuw werkboek met de statistieken. Zie screenshot:

Tips:
  1. De Tel op kleur De functie ondersteunt ook het tellen en optellen van cellen op basis van de standaard lettertypekleur, achtergrond of lettertypekleur van voorwaardelijke opmaak, en een combinatie van zowel opvul- als voorwaardelijke opmaakkleuren.
  2. Geïnteresseerd in deze functie, alstublieft klik om te downloaden voor een gratis proefperiode van 30 dagen.

Tel en som cellen op basis van achtergrondkleur met de functie Filter en SUBTOTAAL

Stel dat we een fruitverkooptabel hebben zoals hieronder afgebeeld, en we zullen de gekleurde cellen in de tabel tellen of optellen Hoeveelheid kolom.

Stap 1: Pas de SUBTOTAAL-functie toe

Selecteer lege cellen om de SUBTOTAAL-functie in te voeren.

  • Voer de formule in om alle cellen met dezelfde achtergrondkleur te tellen:
    =SUBTOTAL(102, F2:F16)
  • Voer de formule in om alle cellen met dezelfde achtergrondkleur op te tellen;
    =SUBTOTAL(109, F2:F16)
  • Note: in de bovenstaande formules, 102 vertegenwoordigt het tellen van numerieke waarden in een gefilterde lijst terwijl verborgen cellen worden uitgesloten; 109 vertegenwoordigt het samenvatten van waarden in een gefilterde lijst met uitzondering van verborgen cellen; F2: F16 is het bereik waarover de telling of de som wordt berekend.

Stap 2: Filter cellen op basis van specifieke kleur

  1. Selecteer de koptekst van de tabel en klik Data > FILTER. Zie screenshot:
  2. Klik op de FILTER icon  in de kopcel van het Hoeveelheid kolom en klik op Filter op kleur en de gespecificeerde kleur die u achtereenvolgens telt. Zie screenshot:

Resultaat:

Na het filteren tellen de SUBTOTAAL-formules automatisch de gekleurde cellen in de Hoeveelheid kolom. Zie screenshot:

Note: Deze methode vereist dat de gekleurde cellen die u gaat tellen of optellen zich in dezelfde kolom bevinden.

Tel en tel cellen op basis van de letterkleur

Wilt u cellen tellen of optellen op basis van hun letterkleur in Excel? Laten we zeggen dat je de gegevens hebt, zoals in de gegeven schermafbeelding, met cellen die teksten bevatten in de kleuren rood, blauw, oranje en zwart. Excel maakt dit standaard niet eenvoudig. Maar maak je geen zorgen! In dit gedeelte laten we u enkele eenvoudige trucs zien om precies dat te doen.


Tel en som cellen op basis van de letterkleur met de door de gebruiker gedefinieerde functie

Om cellen met specifieke lettertypekleuren te tellen en op te tellen, kan de volgende door de gebruiker gedefinieerde functie u helpen deze taak op te lossen. Voer de volgende stappen uit:

Stap 1: Open de VBA-module-editor en kopieer de code

  1. Media Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
  2. Klik in het geopende venster Invoegen > Module om een ​​nieuwe lege module te maken.
  3. Kopieer en plak vervolgens de onderstaande code in de lege module.
    VBA-code: tel en tel cellen op basis van de letterkleur
    Function ProcessByFontColor(pRange1 As Range, pRange2 As Range, FunctionType As String) As Double
    'Updateby Extendoffice
        Application.Volatile
        Dim rng As Range
        Dim xTotal As Double
        Dim xCount As Double
        xTotal = 0
        xCount = 0
        For Each rng In pRange1
            If rng.Font.Color = pRange2.Font.Color Then
                If UCase(FunctionType) = "SUM" Then
                    xTotal = xTotal + rng.Value
                ElseIf UCase(FunctionType) = "COUNT" Then
                    xCount = xCount + 1
                End If
            End If
        Next
        If UCase(FunctionType) = "SUM" Then
            ProcessByFontColor = xTotal
        ElseIf UCase(FunctionType) = "COUNT" Then
            ProcessByFontColor = xCount
        Else
            ProcessByFontColor = CVErr(xlErrValue)
        End If
    End Function
    

Stap 2: Maak formules om cellen te tellen en op te tellen op lettertypekleur

Nadat u de bovenstaande code hebt geplakt, sluit u het modulevenster en past u vervolgens de volgende formules toe:

  • Tel cellen op basis van de specifieke lettertypekleur:
    Kopieer of typ de onderstaande formule in de gewenste cel voor het resultaat. Sleep vervolgens de vulgreep naar beneden om andere resultaten te krijgen. Zie schermafbeelding:
    =ProcessByFontColor($B$2:$E$12,G2, "COUNT")
    Note: In deze formule, G2 is de referentiecel met de specifieke letterkleur die u wilt matchen; $B$2:$E$12 is het bereik waar u het aantal cellen van de kleur van G2 wilt tellen.
  • Tel de cellen op op basis van de specifieke lettertypekleur:
    Kopieer of typ de onderstaande formule in de gewenste cel voor het resultaat. Sleep vervolgens de vulgreep naar beneden om andere resultaten te krijgen. Zie schermafbeelding:
    =ProcessByFontColor($B$2:$E$12,G2, "SUM")  
    Note: In deze formule, G2 is de referentiecel met de specifieke letterkleur die u wilt matchen; $B$2:$E$12 is het bereik waar u het aantal cellen van de kleur van G2 wilt tellen.

Tel en tel cellen op basis van de letterkleur met een eenvoudige functie

Wilt u moeiteloos celwaarden in Excel tellen of optellen op basis van de letterkleur? Erin duiken Kutools for Excel's Tel op kleur functie! Met deze slimme tool wordt het tellen en optellen van cellen op basis van een specifieke letterkleur een fluitje van een cent. Ontdek hoe Kutools kan uw Excel-ervaring transformeren.

Na Kutools voor Excel downloaden en installerenSelecteer eerst het gegevensbereik dat u wilt tellen of optellen van cellen op basis van een specifieke lettertypekleur. Dan klikken Koetools Plus > Tel op kleur om de te openen Tel op kleur dialoog venster.

In het Tel op kleur dialoogvenster, specificeer de bewerkingen:

  1. kies Standaardopmaak van het Kleur methode keuzelijst;
  2. Specificeren doopvont van het Type telling vervolgkeuzelijst en u kunt een voorbeeld van de statistische resultaten voor elke lettertypekleur bekijken in het dialoogvenster;
  3. Eindelijk, klik Genereer rapport om de berekende resultaten naar een nieuwe werkmap te exporteren.

Resultaat:

Nu hebt u een nieuwe werkmap waarin de gedetailleerde statistieken worden weergegeven op basis van de kleur van het lettertype. Zie schermafbeelding:

Tips: Geïnteresseerd in deze functie, alstublieft klik om te downloaden voor een gratis proefperiode van 30 dagen.

Tel en tel cellen op basis van de kleur van de voorwaardelijke opmaak

In Excel kunt u doorgaans de Voorwaardelijke opmaak gebruiken om specifieke kleuren toe te passen op cellen die aan bepaalde criteria voldoen, waardoor gegevensvisualisatie intuïtief wordt. Maar wat als u die speciaal opgemaakte cellen moet tellen of optellen? Hoewel Excel hiervoor geen directe manier biedt, zijn hier manieren om deze beperking te omzeilen.


Tel en som voorwaardelijk opgemaakte cellen op met VBA-code

Het tellen en optellen van voorwaardelijk opgemaakte cellen in Excel is niet eenvoudig met behulp van ingebouwde functies. U kunt deze taak echter uitvoeren met VBA-code. Laten we eens kijken hoe u VBA hiervoor kunt gebruiken:

Stap 1: Open de VBA-module-editor en kopieer de code

  1. Media Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
  2. Klik in het geopende venster Invoegen > Module om een ​​nieuwe lege module te maken.
  3. Kopieer en plak vervolgens de onderstaande code in de lege module.
    VBA-code: tel en tel cellen op basis van de kleur van de voorwaardelijke opmaak
    Sub SumCountByConditionalFormat()
    'Updateby Extendoffice
        Dim sampleColor As Range
        Dim selectedRange As Range
        Dim cell As Range
        Dim countByColor As Long
        Dim sumByColor As Double
        Dim refColor As Long
        Set selectedRange = Application.InputBox("Select a range to evaluate:", _
                                                 "Kutools for Excel", _
                                                 Type:=8)
        If selectedRange Is Nothing Then Exit Sub
        Set sampleColor = Application.InputBox("Select a conditional formatting color:", _
                                               "Kutools for Excel", _
                                               Type:=8)
        If Not sampleColor Is Nothing Then
            refColor = sampleColor.Cells(1, 1).DisplayFormat.Interior.color
            For Each cell In selectedRange
                If cell.DisplayFormat.Interior.color = refColor Then
                    countByColor = countByColor + 1
                    sumByColor = sumByColor + cell.Value
                End If
            Next cell
            MsgBox "Count: " & countByColor & vbCrLf & _
                   "Sum: " & sumByColor, _
                   vbInformation, "Results based on Conditional Format Color"
        End If
    End Sub
    

Stap 2: Voer deze VBA-code uit

  1. Druk na het plakken van de code op F5 Om deze code uit te voeren, verschijnt er een promptvenster. Selecteer het gegevensbereik waar u de cellen wilt tellen en som ze op op basis van voorwaardelijke opmaak. Dan klikken OK, Zie screenshot:
  2. Selecteer in een ander promptvenster een specifieke voorwaardelijke opmaakkleur die u wilt tellen en optellen, en klik op OK knop, zie screenshot:

Resultaat:

Nu wordt het resultaat, dat zowel het aantal als de som van cellen met de opgegeven voorwaardelijke opmaakkleur bevat, weergegeven in het uitgevouwen vak. Zie schermafbeelding:


Tel en som voorwaardelijk opgemaakte cellen op met een slimme functie

Als u op zoek bent naar andere snelle en gemakkelijke methoden om voorwaardelijk opgemaakte cellen te tellen en op te tellen, Kutools for Excel is uw go-to-oplossing. Zijn Tel op kleur functie kan deze taak in slechts een paar klikken oplossen. Duik erin en ontdek de efficiëntie en precisie die Kutools in uw workflow kan brengen.

Na Kutools voor Excel downloaden en installerenSelecteer eerst het gegevensbereik dat u wilt tellen of optellen van cellen op basis van een specifieke voorwaardelijke opmaakkleur. Dan klikken Koetools Plus > Tel op kleur om de te openen Tel op kleur dialoog venster.

In het Tel op kleur dialoogvenster, specificeer de bewerkingen:

  1. kies Conditionele opmaak van het Kleur methode keuzelijst;
  2. Specificeren Achtergrond van het Type telling vervolgkeuzelijst, en u kunt een voorbeeld van de statistische resultaten voor elke conditionla-opmaakkleur bekijken in het dialoogvenster;
  3. Eindelijk, klik Genereer rapport om de berekende resultaten naar een nieuwe werkmap te exporteren.

Resultaat:

Nu hebt u een nieuwe werkmap waarin de gedetailleerde statistieken worden weergegeven op basis van de kleur van de voorwaardelijke opmaak. Zie schermafbeelding:

Tips: Geïnteresseerd in deze functie, alstublieft klik om te downloaden voor een gratis proefperiode van 30 dagen.

Gerelateerde artikelen:

  • Filter gegevens op meerdere kleuren
  • Normaal gesproken kunt u in Excel snel rijen met slechts één kleur filteren, maar heeft u er ooit aan gedacht om rijen met meerdere kleuren tegelijkertijd te filteren? In dit artikel zal ik het hebben over een snelle truc om dit probleem op te lossen.
  • Voeg kleur toe aan de vervolgkeuzelijst
  • In Excel kan het maken van een vervolgkeuzelijst u veel helpen, en soms moet u de waarden van de vervolgkeuzelijst een kleurcode geven, afhankelijk van de overeenkomstige geselecteerde. Ik heb bijvoorbeeld een vervolgkeuzelijst met de fruitnamen gemaakt. Als ik Apple selecteer, moet de cel automatisch rood worden gekleurd, en als ik Oranje kies, kan de cel oranje worden gekleurd.
  • Kleur afwisselende rijen voor samengevoegde cellen
  • Het is erg handig om afwisselende rijen met een andere kleur op te maken in grote gegevens, zodat we de gegevens kunnen scannen, maar soms kunnen er enkele samengevoegde cellen in uw gegevens zijn. Om de rijen afwisselend te markeren met een andere kleur voor de samengevoegde cellen, zoals hieronder afgebeeld, hoe zou u dit probleem in Excel kunnen oplossen?