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
- Door de door de gebruiker gedefinieerde functie te gebruiken
- Door een krachtige functie te gebruiken – Kutools voor Excel
- Door Filter en SUBTOTAAL te gebruiken
Tel en tel cellen op basis van de letterkleur
- Door de door de gebruiker gedefinieerde functie te gebruiken
- Door een eenvoudige functie te gebruiken – Kutools voor Excel
Tel en tel cellen op basis van de kleur van de voorwaardelijke opmaak
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
- Media Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
- Klik in het geopende venster Invoegen > Module om een nieuwe lege module te maken.
- Kopieer en plak vervolgens de onderstaande code in de lege module.
VBA-code: tel en tel cellen op basis van de achtergrondkleurFunction 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:
- kies Standaardopmaak van het Kleur methode keuzelijst;
- Specificeren Achtergrond van het Type telling vervolgkeuzelijst en u kunt een voorbeeld van de statistische resultaten voor elke achtergrondkleur bekijken in het dialoogvenster;
- 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:
- 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.
- 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
- Selecteer de koptekst van de tabel en klik Data > FILTER. Zie screenshot:
- 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:
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
- Media Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
- Klik in het geopende venster Invoegen > Module om een nieuwe lege module te maken.
- Kopieer en plak vervolgens de onderstaande code in de lege module.
VBA-code: tel en tel cellen op basis van de letterkleurFunction 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:
- kies Standaardopmaak van het Kleur methode keuzelijst;
- Specificeren doopvont van het Type telling vervolgkeuzelijst en u kunt een voorbeeld van de statistische resultaten voor elke lettertypekleur bekijken in het dialoogvenster;
- 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:
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
- Media Alt + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.
- Klik in het geopende venster Invoegen > Module om een nieuwe lege module te maken.
- 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 opmaakSub 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
- 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:
- 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:
- kies Conditionele opmaak van het Kleur methode keuzelijst;
- Specificeren Achtergrond van het Type telling vervolgkeuzelijst, en u kunt een voorbeeld van de statistische resultaten voor elke conditionla-opmaakkleur bekijken in het dialoogvenster;
- 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:
Gerelateerde artikelen:
- Als de kleur van het lettertype rood is, retourneer dan een specifieke tekst
- Hoe zou je een specifieke tekst kunnen retourneren als de letterkleur rood is in een andere cel, zoals hieronder afgebeeld? In dit artikel zal ik enkele trucs introduceren voor het uitvoeren van sommige bewerkingen op basis van de rode lettertypetekst in Excel.
- 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?
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!
Inhoudsopgave
- Video
- Tel en som cellen op basis van achtergrondkleur
- Door de door de gebruiker gedefinieerde functie te gebruiken
- Door een krachtige functie te gebruiken – Kutools voor Excel
- Door Filter en SUBTOTAAL te gebruiken
- Tel en tel cellen op basis van de letterkleur
- Door de door de gebruiker gedefinieerde functie te gebruiken
- Door een eenvoudige functie te gebruiken – Kutools voor Excel
- Tel en tel cellen op basis van de kleur van de voorwaardelijke opmaak
- Door gebruik te maken van VBA-code
- Door een slimme functie te gebruiken – Kutools voor Excel
- Gerelateerde artikelen
- De beste tools voor kantoorproductiviteit
- Heb je vragen? Stel ze hier.