Excel Tips: Tellen/sommen van cellen op kleur (achtergrond, lettertype, voorwaardelijke opmaak)

In dagelijkse taken is kleurmarkering een populaire methode om cruciale gegevens snel te onderscheiden en te markeren. Maar hoe tellen of sommeren we celgegevens op basis van een specifieke kleur (vulkleur, lettertypekleur, voorwaardelijke opmaak)? Standaard biedt Excel geen directe functie om te tellen of te sommeren op basis van kleur. Toch kunnen we dit met wat trucs en indirecte methoden nog steeds bereiken. Dit artikel onderzoekt hoe we gegevens kunnen tellen of sommeren op basis van kleur.

Tellen en sommeren van cellen op basis van achtergrondkleur

• Door gebruik te maken van een door de gebruiker gedefinieerde functie
• Door gebruik te maken van een krachtige functie – Kutools voor Excel
• Door gebruik te maken van Filter en SUBTOTAL

Tellen en sommeren van cellen op basis van lettertypekleur

• Door gebruik te maken van een door de gebruiker gedefinieerde functie
• Door gebruik te maken van een eenvoudige functie – Kutools voor Excel

Tellen en sommeren van cellen op basis van voorwaardelijke opmaakkleur

• Door gebruik te maken van VBA-code
• Door gebruik te maken van een slimme functie – Kutools voor Excel

Video: Tellen en sommeren van cellen op basis van kleur

Tellen en sommeren van cellen op basis van achtergrondkleur

Bijvoorbeeld, als je een reeks gegevens hebt waarbij de waarden zijn gevuld met verschillende achtergrondkleuren zoals in de onderstaande schermafbeelding te zien is. Om cellen te tellen of te sommeren op basis van een specifieke kleur, biedt Excel geen directe functie om cellen te tellen of te sommeren op basis van hun achtergrondkleur. Met een beetje creativiteit en handige technieken kun je deze taak echter wel uitvoeren. Laten we enkele nuttige methoden in deze sectie verkennen.

Tellen en sommeren van cellen op basis van achtergrondkleur met een door de gebruiker gedefinieerde functie

Hier laten we je zien hoe je zo'n door de gebruiker gedefinieerde functie kunt maken en gebruiken om deze taak in Excel op te lossen. Volg de volgende stappen:

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

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.
2. Klik in het geopende venster op Invoegen > Module om een nieuwe lege module te maken.
3. Kopieer en plak vervolgens de onderstaande code in de lege module.
   VBA-code: Tellen en sommeren van cellen op basis van 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 te sommeren op basis van achtergrondkleur

Nadat je de bovenstaande code hebt geplakt, sluit je het modulevenster en pas je de volgende formules toe:

• Tel cellen op basis van een 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) 

  Opmerking: In deze formule is G2 de referentiecel met de specifieke achtergrondkleur die je wilt matchen; $B$2:$E$12 is het bereik waarin je het aantal cellen van G2's kleur wilt tellen; ONWAAR wordt gebruikt om cellen met overeenkomende kleur te tellen.
  
• Sommeer cellen op basis van een 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)  

  Opmerking: In deze formule is G2 de referentiecel met de specifieke achtergrondkleur die je wilt matchen; $B$2:$E$12 is het bereik waarin je het aantal cellen van G2's kleur wilt tellen; WAAR wordt gebruikt om cellen met overeenkomende kleur te sommeren.
  

Tellen en sommeren van cellen op basis van achtergrondkleur met een krachtige functie

Voor diegenen die niet bekend zijn met programmeren, kan VBA behoorlijk complex lijken. Hier introduceren we een krachtig hulpmiddel - Kutools voor Excel, waarvan de functie Tellen op Kleur het je mogelijk maakt om gemakkelijk te berekenen (tellen, sommeren, gemiddelde, etc.) op basis van achtergrondkleur in slechts een paar klikken. Indrukwekkend genoeg gaat de functie Tellen op Kleur verder dan alleen achtergrondkleuren – het kan ook onderscheid maken en berekeningen uitvoeren op basis van lettertypekleuren en voorwaardelijke opmaak.

Na het downloaden en installeren van Kutools voor Excel selecteer je eerst het gegevensbereik waarop je cellen wilt tellen of sommeren op basis van een specifieke achtergrondkleur. Ga vervolgens naar Kutools Plus en kies Tellen op Kleur.

Specificeer in het dialoogvenster Tellen op Kleur de bewerkingen:

1. Selecteer Standaardopmaak uit de vervolgkeuzelijst Kleur methode;
2. Specificeer Achtergrond uit de vervolgkeuzelijst Teltype, en je kunt de statistische resultaten voor elke achtergrondkleur in het dialoogvenster bekijken;
3. Klik tot slot op Rapport genereren om de berekende resultaten naar een nieuw werkboek te exporteren.

Resultaat:

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

Tellen en sommeren van cellen op basis van achtergrondkleur met Filter en SUBTOTAL-functie

Stel dat we een fruitverkoopstabel hebben zoals in de onderstaande schermafbeelding te zien is, en we gaan de gekleurde cellen in de Bedrag kolom tellen of sommeren.

Stap 1: Pas de SUBTOTAL-functie toe

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

• Om alle cellen met dezelfde achtergrondkleur te tellen, voer je de volgende formule in:

  =SUBTOTAL(102, F2:F16)

• Om alle cellen met dezelfde achtergrondkleur te sommeren, voer je de volgende formule in:

  =SUBTOTAL(109, F2:F16)

• Opmerking: in de bovenstaande formules vertegenwoordigt 102 het tellen van numerieke waarden in een gefilterde lijst terwijl verborgen cellen worden uitgesloten; 109 vertegenwoordigt het optellen van waarden in een gefilterde lijst terwijl verborgen cellen worden uitgesloten; F2:F16 is het bereik waarover het aantal of de som zal worden berekend.
• 

Stap 2: Filter cellen op basis van een specifieke kleur

1. Selecteer de kop van de tabel en klik op Gegevens > Filter. Zie schermafbeelding:
   
2. Klik op het Filter icoon  in de kopcel van de Bedrag kolom, en klik op Filteren op kleur en de gespecificeerde kleur waarop je achtereenvolgens wilt tellen. Zie schermafbeelding:
   

Resultaat:

Na het filteren tellen en sommeren de SUBTOTAL-formules automatisch de gekleurde cellen in de Bedrag kolom. Zie schermafbeelding:

Tellen en sommeren van cellen op basis van lettertypekleur

Wil je cellen tellen of sommeren op basis van hun lettertypekleur in Excel? Stel dat je de gegevens hebt, zoals in de gegeven schermafbeelding, met cellen die teksten bevatten in rood, blauw, oranje en zwarte kleur. Excel maakt dit standaard niet gemakkelijk. Maar maak je geen zorgen! In deze sectie laten we je enkele eenvoudige trucs zien om dit te doen.

Tellen en sommeren van cellen op basis van lettertypekleur met een door de gebruiker gedefinieerde functie

Om cellen te tellen en te sommeren met specifieke lettertypekleuren, kan de volgende door de gebruiker gedefinieerde functie je helpen bij het oplossen van deze taak. Volg de volgende stappen:

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

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.
2. Klik in het geopende venster op Invoegen > Module om een nieuwe lege module te maken.
3. Kopieer en plak vervolgens de onderstaande code in de lege module.
   VBA-code: Tellen en sommeren van cellen op basis van lettertypekleur

   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 te sommeren op basis van lettertypekleur

Nadat je de bovenstaande code hebt geplakt, sluit je het modulevenster en pas je de volgende formules toe:

• Tel cellen op basis van een 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")

  Opmerking: In deze formule is G2 de referentiecel met de specifieke lettertypekleur die je wilt matchen; $B$2:$E$12 is het bereik waarin je het aantal cellen van G2's kleur wilt tellen.
  
• Sommeer cellen op basis van een 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")  

  Opmerking: In deze formule is G2 de referentiecel met de specifieke lettertypekleur die je wilt matchen; $B$2:$E$12 is het bereik waarin je het aantal cellen van G2's kleur wilt tellen.
  

Tellen en sommeren van cellen op basis van lettertypekleur met een eenvoudige functie

Op zoek naar een manier om moeiteloos celwaarden in Excel te tellen of te sommeren op basis van lettertypekleur? Duik in de Kutools voor Excel's Tellen op Kleur functie! Met dit slimme hulpmiddel wordt het tellen en sommeren van cellen op basis van een specifieke lettertypekleur een makkie. Ontdek hoe Kutools je Excel-ervaring kan transformeren.

Na het downloaden en installeren van Kutools voor Excel selecteer je eerst het gegevensbereik waarop je cellen wilt tellen of sommeren op basis van een specifieke lettertypekleur. Klik vervolgens op Kutools Plus > Tellen op Kleur om het dialoogvenster Tellen op Kleur te openen.

Specificeer in het dialoogvenster Tellen op Kleur de bewerkingen:

1. Selecteer Standaardopmaak uit de vervolgkeuzelijst Kleur methode;
2. Specificeer Lettertype uit de vervolgkeuzelijst Teltype, en je kunt de statistische resultaten voor elke lettertypekleur in het dialoogvenster bekijken;
3. Klik tot slot op Rapport genereren om de berekende resultaten naar een nieuw werkboek te exporteren.

Resultaat:

Nu heb je een nieuw werkboek dat gedetailleerde statistieken toont op basis van lettertypekleur. Zie schermafbeelding:

Tellen en sommeren van cellen op basis van voorwaardelijke opmaakkleur

In Excel gebruik je vaak Voorwaardelijke Opmaak om een specifieke kleur toe te passen op cellen die aan bepaalde criteria voldoen, waardoor gegevensvisualisatie intuïtief wordt. Maar wat als je die speciaal opgemaakte cellen moet tellen of sommeren? Hoewel Excel hier geen directe manier voor biedt, zijn hier manieren om deze beperking te omzeilen.

Tellen en sommeren van voorwaardelijk opgemaakte cellen met VBA-code

Het tellen en sommeren van voorwaardelijk opgemaakte cellen in Excel is niet eenvoudig met ingebouwde functies. Je kunt deze taak echter wel uitvoeren met behulp van VBA-code. Laten we bekijken hoe je VBA hiervoor kunt gebruiken:

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

1. Druk op Alt + F11 om het Microsoft Visual Basic for Applications-venster te openen.
2. Klik in het geopende venster op Invoegen > Module om een nieuwe lege module te maken.
3. Kopieer en plak vervolgens de onderstaande code in de lege module.
   VBA-code: Tellen en sommeren van cellen op basis van voorwaardelijke opmaakkleur

   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. Nadat je de code hebt geplakt, druk je op F5 om deze code uit te voeren. Er verschijnt een promptvenster, selecteer het gegevensbereik waarop je cellen wilt tellen en sommeren op basis van voorwaardelijke opmaak. Klik vervolgens op OK, Zie schermafbeelding:
   
2. In een ander promptvenster selecteer je een specifieke voorwaardelijke opmaakkleur die je wilt tellen en sommeren, en klik je op OK knop, zie schermafbeelding:
   

Resultaat:

Nu wordt het resultaat, inclusief zowel het aantal als de som van cellen met de gespecificeerde voorwaardelijke opmaakkleur, weergegeven in het pop-upvenster. Zie schermafbeelding:

Tellen en sommeren van voorwaardelijk opgemaakte cellen met een slimme functie

Als je op zoek bent naar andere snelle en eenvoudige methoden om voorwaardelijk opgemaakte cellen te tellen en te sommeren, is Kutools voor Excel jouw oplossing. De functie Tellen op Kleur kan deze taak in slechts een paar klikken oplossen. Duik erin om de efficiëntie en precisie te ontdekken die Kutools aan je workflow kan toevoegen.

Na het downloaden en installeren van Kutools voor Excel selecteer je eerst het gegevensbereik waarop je cellen wilt tellen of sommeren op basis van een specifieke voorwaardelijke opmaakkleur. Klik vervolgens op Kutools Plus > Tellen op Kleur om het dialoogvenster Tellen op Kleur te openen.

Specificeer in het dialoogvenster Tellen op Kleur de bewerkingen:

1. Selecteer Voorwaardelijke opmaak uit de vervolgkeuzelijst Kleur methode;
2. Specificeer Achtergrond uit de vervolgkeuzelijst Teltype, en je kunt de statistische resultaten voor elke voorwaardelijke opmaakkleur in het dialoogvenster bekijken;
3. Klik tot slot op Rapport genereren om de berekende resultaten naar een nieuw werkboek te exporteren.

Resultaat:

Nu heb je een nieuw werkboek dat gedetailleerde statistieken toont op basis van de voorwaardelijke opmaakkleur. Zie schermafbeelding:

Gerelateerde artikelen: