Hoe cellen tellen of optellen op basis van celkleur in een Google Sheet?
In dagelijkse spreadsheetwerkzaamheden kun je situaties tegenkomen waarin je celwaarden moet tellen of optellen volgens een specifieke achtergrondkleur, zoals geïllustreerd in de onderstaande schermafbeelding. Bijvoorbeeld, je wilt misschien alleen die cellen tellen of optellen die gemarkeerd zijn in een bepaalde kleur om snel gegevens te analyseren op categorie of status. Deze gids legt uit hoe je dit kunt doen, niet alleen in Google Sheets, dat geen ingebouwde ondersteuning biedt voor dergelijke kleurgebaseerde berekeningen, maar ook in Microsoft Excel, dat verschillende benaderingen biedt, van ingebouwde functies tot geavanceerde hulpmiddelen.
Het begrijpen hoe je kleurgebaseerde data-analyse kunt hanteren, kan je werk efficiënter maken, vooral wanneer kleuren worden gebruikt om statussen, prioriteiten of categorieën aan te geven. We bespreken ook verschillende oplossingen, vergelijken hun gebruiksscenario's en bieden praktische operationele tips en foutmeldingen om ervoor te zorgen dat je taken soepel verlopen.
- Tel celwaarden op basis van celkleur met een script in Google Sheet
- Som celwaarden op basis van celkleur met een script in Google Sheet
- Tel of som celwaarden op celkleur met Kutools voor Excel in Microsoft Excel
Tel celwaarden op basis van celkleur met een script in Google Sheet
Google Sheets biedt geen directe optie om cellen te tellen op basis van achtergrondkleur. Dit kun je echter bereiken met een aangepast Apps Script. Dit script fungeert als een door de gebruiker gedefinieerde functie, waardoor je het kunt gebruiken als een formule. Hier is hoe je het script instelt en gebruikt:
1. Klik op Tools > Script editor om de scriptomgeving te openen. Zie schermafbeelding:
2. Selecteer in het projectvenster File > New > Script file om een nieuwe codemodule te openen, zoals weergegeven:
3. Voer bij de prompt een naam in voor je nieuwe scriptcode en bevestig. Noem het script zinvol om later het doel te kunnen identificeren.
4. Klik op OK, kopieer en plak vervolgens de volgende code om elke voorbeeldcode in de module te vervangen. Zorg ervoor dat je deze exact zo plakt als deze wordt geleverd.
function countColoredCells(countRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var countCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countCells = countCells + 1;
return countCells;
};
5. Sla dit scriptbestand op, ga terug naar je sheet en gebruik de nieuwe functie net zoals elke andere Google Sheets-formule. Voer in: =countcoloredcells(A1:E11,A1) in een lege cel om cellen in het bereik A1:E11 te tellen die overeenkomen met de kleur van A1. Druk op Enter om het resultaat te verkrijgen. Als er om toestemming wordt gevraagd, autoriseer dan het script om in je sheet te draaien.
Opmerking: A1:E11 is je databereik; A1 is de referentiecel van de kleur om te tellen. Zorg ervoor dat referentiecellen exact dezelfde kleur hebben en vermijd samengevoegde cellen voor de beste betrouwbaarheid.
6. Om andere kleuren te tellen, herhaal dan de formule met een andere kleurreferentiecel indien nodig. Als je bereik verandert, pas dan het bereik in de formule dienovereenkomstig aan.
Als je een foutmelding of een onverwacht resultaat ontvangt, controleer dan of het script is opgeslagen en of je de juiste kleurreferentie hebt gebruikt. Functies op basis van Apps Script worden alleen herberekend als de functie of de argumenten veranderen - als je later je cellen opnieuw kleurt, voer dan de formule opnieuw in of druk op Enter om te vernieuwen.
Som celwaarden op basis van celkleur met een script in Google Sheet
Het optellen van celwaarden op basis van een gedefinieerde celkleur in Google Sheets vereist een vergelijkbare aanpak met behulp van een Apps Script. Dit is vooral handig voor financiële sheets, statuslogs of elk scenario waarin kleuren categorieën vertegenwoordigen met numerieke gegevens eronder.
1. Open in Google Sheets de Script editor via Tools > Script editor. Selecteer in het projectvenster File > New > Script file om een nieuwe codemodule toe te voegen. Geef een unieke naam in de prompt om het doel te kunnen traceren, zoals "SumColoredCells." Bevestig om de module te creëren.
2. Klik op OK en vervang in het venster van de nieuwe codemodule elke standaardcode door het plakken van het gegeven script voor het optellen van gekleurde cellen. Zorg er zorgvuldig voor dat alle code is gekopieerd, omdat ontbrekende tekens kunnen leiden tot syntaxfouten.
function sumColoredCells(sumRange,colorRef) {
var activeRg = SpreadsheetApp.getActiveRange();
var activeSht = SpreadsheetApp.getActiveSheet();
var activeformula = activeRg.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
var sumValues = activeSht.getRange(countRangeAddress).getValues();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSht.getRange(colorRefAddress).getBackground();
var totalValue = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
if ((typeof sumValues[i][k]) == 'number')
totalValue = totalValue + (sumValues[i][k]);
return totalValue;
};
3. Na het opslaan van het script, ga terug naar je sheet en voer de formule =sumcoloredcells(A1:E11,A1) in een lege cel in, en druk op Enter. Deze formule telt waarden op in A1:E11 waarvan de achtergrondkleur overeenkomt met A1. Zorg ervoor dat alle doelsomcellen numeriek zijn; niet-numerieke waarden worden genegeerd.
Opmerking: A1:E11 vertegenwoordigt je databereik en A1 biedt de kleurreferentie. De formule zal alleen zichtbare numerieke waarden optellen - zorg ervoor dat samengevoegde cellen of fouten binnen het bereik je totalen niet beïnvloeden.
4. Je kunt het bovenstaande proces repliceren om waarden voor verschillende kleurcategorieën op te tellen door de kleurreferentiecel in je formule te wijzigen. Als je gegevens worden bijgewerkt of je verandert achtergrondkleuren, vergeet dan niet de formule te vernieuwen voor bijgewerkte resultaten.
Als de som nul of een foutwaarde retourneert, controleer dan of het bereik getallen bevat en of de kleurmatch exact is. Ook de herberekening is niet automatisch als alleen de celkleur verandert - bewerk de formulecel om een update af te dwingen.
Tel of som celwaarden op celkleur met Kutools voor Excel in Microsoft Excel
Bij het werken in Microsoft Excel is het vaak nodig om cellen te tellen of optellen op kleur, vooral binnen projectmanagement, inventarisatie of kwaliteitscontrole rapportages. Kutools voor Excel biedt een toegewijd Tellen op Kleur hulpmiddel, dat het mogelijk maakt om tellingen en totalen te verkrijgen op basis van achtergrond- of lettertypekleur - dit is vooral nuttig voor grotere databereiken en wanneer je snel herbruikbare resultaten nodig hebt.
Nadat Kutools voor Excel is geïnstalleerd, volg dan deze stappen:
1. Markeer het bereik waar je op kleur wilt tellen of optellen, klik dan op Kutools Plus > Tellen op Kleur. Raadpleeg de onderstaande schermafbeelding voor richtlijnen:
2. Het dialoogvenster Tellen op Kleur verschijnt. Stel Standaardopmaak in onder Kleurmodus, en kies Achtergrond voor Teltype. Controleer de voorvertoning en opties zorgvuldig:
3. Klik op Rapport genereren om een nieuw werkblad te maken dat een uitsplitsing bevat van tellingen en sommen voor elke kleur in je bereik. Dit rapport bevat zowel het aantal als de som van gekleurde cellen, wat makkelijke naslag of verdere analyse mogelijk maakt.
Opmerking: Deze functie kan ook waarden berekenen op basis van voorwaardelijke opmaak of lettertypekleur. Gebruik regels voor voorwaardelijke opmaak voor dynamische analyses; anders werkt het hulpmiddel het best voor statische kleurvullingen. Eventuele wijzigingen in de broncelkleuren vereisen het opnieuw uitvoeren van het hulpmiddel Tellen op Kleur voor bijgewerkte resultaten. Als je problemen ervaart, bevestig dan dat Kutools actief is en up-to-date.
Klik hier om Kutools voor Excel nu te downloaden en gratis te proberen!
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen