Hoe tel of sommeer ik cellen op basis van hun kleur in een Google-blad?
Tijdens uw dagelijkse werk met spreadsheets komt u mogelijk situaties tegen waarin u celwaarden moet tellen of optellen op basis van een specifieke celachtergrondkleur, zoals te zien in de onderstaande schermafbeelding. Stel, u wilt bijvoorbeeld alleen cellen tellen of optellen die zijn gemarkeerd met een bepaalde kleur, zodat u snel gegevens kunt analyseren per categorie of status. Deze handleiding laat u zien hoe u dat doet – zowel in Google Sheets, dat geen ingebouwde ondersteuning biedt voor dergelijke kleurbasede berekeningen, als in Microsoft Excel, dat meerdere aanpakken kent, variërend van ingebouwde functies tot geavanceerde hulpmiddelen.
Begrijpen hoe u omgaat met kleurbasede data-analyse maakt uw werk efficiënter, vooral wanneer kleuren worden ingezet om statussen, prioriteiten of categorieën weer te geven. We bespreken diverse oplossingen, vergelijken hun toepassingsscenario’s en delen praktische gebruikstips plus veelvoorkomende foutmeldingen, zodat uw taken soepel verlopen.
- Tel celwaarden op basis van celkleur met een script in het Google-blad
- Sommeer celwaarden op basis van celkleur met een script in het Google-blad
- Tel of sommeer celwaarden op basis van celkleur met Kutools voor Excel in Microsoft Excel
Tel celwaarden op basis van celkleur met een script in het Google-blad
Google Sheets biedt geen ingebouwde optie om cellen te tellen op basis van hun achtergrondkleur, maar u kunt dit eenvoudig realiseren met een aangepast Apps Script. Dit script werkt als een door u gedefinieerde functie, zodat u deze net als elke andere formule in uw werkblad kunt gebruiken. Zo stelt u het script in en past u het toe:
1. Klik op Extra > Scripteditor om de scriptomgeving te openen. Zie de schermafbeelding:
2. Selecteer in het projectvenster Bestand > Nieuw > Scriptbestand om een nieuwe codemodule te openen, zoals hieronder wordt getoond:
3. Geef uw nieuwe scriptcode een naam in de prompt en bevestig deze. Kies een duidelijke, betekenisvolle naam, zodat u het doel ervan later meteen herkent.
4. Klik op OK en kopieer vervolgens de volgende code om eventuele voorbeeldcode in de module te vervangen. Plak de code exact zoals verstrekt.
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, keer terug naar uw werkblad en gebruik de nieuwe functie net als elke andere Google Sheets-formule. Typ in een lege cel het volgende:=countcoloredcells(A1:E11;A1) om cellen in het bereik A1:E11 te tellen die dezelfde kleur hebben als A1. Druk op Enter om het resultaat te krijgen. Als u om machtigingen wordt gevraagd, geeft u het script dan toestemming om in uw werkblad uit te voeren.
Opmerking: A1:E11 is uw gegevensbereik; A1 is de referentiecel voor de te tellen kleur. Zorg ervoor dat de referentiecellen exact dezelfde kleur hebben en vermijd samenvoegen voor optimale betrouwbaarheid.
6. Om andere kleuren te tellen, herhaalt u de formule met een andere referentiecel voor de desbetreffende kleur indien nodig. Wanneer uw bereik verandert, past u het bereik in de formule dienovereenkomstig aan.
Als u een foutmelding of een onverwacht resultaat krijgt, controleert u of het script is opgeslagen en of u de juiste kleurreferentie hebt gebruikt. Op Apps Script gebaseerde functies worden alleen opnieuw berekend wanneer de functie zelf of de argumenten ervan wijzigen – als u later de kleur van uw cellen aanpast, typt u de formule opnieuw in of drukt u op Enter om de gegevens te vernieuwen.
Sommeer celwaarden op basis van celkleur met een script in het Google-blad
Het sommeren van celwaarden op basis van een specifieke celkleur in Google Sheets vereist een vergelijkbare aanpak via Apps Script. Dit is vooral handig voor financiële overzichten, statuslogboeken of elk scenario waarin kleuren categorieën aanduiden met daaronder numerieke gegevens.
1. Open in Google Sheets de Scripteditor via Extra > Scripteditor. Selecteer in het projectvenster Bestand > Nieuw > Scriptbestand om een nieuwe codemodule toe te voegen. Geef in het dialoogvenster een unieke naam op om het doel ervan bij te houden, zoals „SumColoredCells”. Bevestig om de module aan te maken.
2. Klik op OK en vervang in het nieuwe codemodulevenster eventuele standaardcode door het verstrekte script voor het sommeren van gekleurde cellen. Zorg er zorgvuldig voor dat je de volledige code kopieert, want ontbrekende tekens kunnen leiden tot syntaxisfouten.
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. Nadat u het script hebt opgeslagen, keert u terug naar uw werkblad en voert u de formule =sumcoloredcells(A1:E11;A1) in een lege cel in, waarna u op Enter drukt. Deze formule telt de waarden op in A1:E11 waarvan de achtergrondkleur overeenkomt met die van A1. Zorg er bij gebruik van deze functie voor dat alle doelcellen numeriek zijn; niet-numerieke waarden worden genegeerd.
Opmerking: A1:E11 vertegenwoordigt uw gegevensbereik en A1 geeft de kleurreferentie aan. De formule telt alleen zichtbare numerieke waarden op – zorg ervoor dat samengevoegde cellen of fouten binnen het bereik uw totalen niet beïnvloeden.
4. U kunt het bovenstaande proces herhalen om waarden op te tellen voor verschillende kleurcategorieën door de referentiecel voor de kleur in uw formule aan te passen. Als uw gegevens worden bijgewerkt of u de achtergrondkleur wijzigt, vergeet dan niet de formule te vernieuwen voor een actueel resultaat.
Als de som nul of een foutwaarde oplevert, controleer dan of het bereik daadwerkelijk cijfers bevat en of de kleurovereenkomst exact is. Houd er bovendien rekening mee dat herberekening niet automatisch plaatsvindt wanneer alleen de celkleur verandert – bewerk de formulecel om een update af te dwingen.
Tel of sommeer celwaarden op basis van celkleur met Kutools voor Excel in Microsoft Excel
Bij het werken in Microsoft Excel is het tellen of optellen van cellen op basis van kleur een veelvoorkomende eis, vooral in projectmanagement, inventarisaties of kwaliteitscontroleverslagen. Kutools voor Excel biedt een speciaal Tellen op kleur-hulpmiddel waarmee u direct tellingen en totalen kunt verkrijgen op basis van achtergrond- of letterkleur – ideaal voor grotere bereiken en wanneer u snelle, herhaalbare resultaten nodig heeft.
Zodra Kutools voor Excelis geïnstalleerd, volgt u deze stappen:
1. Markeer het bereik waarin u op kleur wilt tellen of optellen en klik vervolgens op KUTOOLS PLUS > Tellen op kleur. Raadpleeg de onderstaande schermafbeelding als hulp:
2. Het dialoogvenster Tellen op kleur verschijnt. Stel Standaard opmaak in onder Kleurmodus en kies Achtergrond voor Statistiektype. Controleer het voorbeeld en de opties zorgvuldig:
3. Klik op Rapport genereren om een nieuw werkblad te maken met een overzicht van tellingen en totalen voor elke kleur in uw bereik. Dit rapport bevat zowel het aantal als de som van gekleurde cellen, waardoor verdere analyse of naslag een stuk eenvoudiger wordt.
Opmerking: Deze functie kan waarden ook berekenen op basis van Voorwaardelijke opmaak gebruiken of Letterkleur. Gebruik regels voor Voorwaardelijke opmaak gebruiken voor dynamische analyses; voor statische kleurvullingen werkt het hulpmiddel het beste. Als de kleuren van brondcellen wijzigen, moet u het Tellen op kleur-hulpmiddel opnieuw uitvoeren om bijgewerkte resultaten te krijgen. Mocht u problemen ondervinden, controleer dan of Kutools actief en up-to-date is.
Klik op Download en gratis proefversie Kutools voor Excel Nu!
Beste Office-productiviteitshulpmiddelen
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie zoals nooit tevoren.Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en Tijd besparen.Klik hier om de functie te krijgen die u het meest nodig heeft...
Office Tab brengt een tabbladinterface naar Office en maakt uw werk veel eenvoudiger
- Schakel tabbladbewerking en -lezen in voor Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten aan in nieuwe tabbladen binnen hetzelfde venster, in plaats van in afzonderlijke vensters.
- Verhoogt uw productiviteit met 50 % en bespaart u dagelijks honderden muisklikken!
Alle Kutools-add-ins in één installatieprogramma.
Kutools for Office bundelt add-ins voor Excel, Word, Outlook en PowerPoint, plus Office Tab Pro—ideaal voor teams die met meerdere Office-apps werken.
- Alles-in-één suite— add-ins voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro
- Één installatieprogramma, één licentie— binnen enkele minuten klaar (MSI-geschikt)
- Werkt beter samen— gestroomlijnde productiviteit in alle Office-apps
- 30 dagen volledig functionele proefversie— geen registratie, geen creditcard
- Beste prijs-kwaliteitverhouding— bespaar ten opzichte van het afzonderlijk kopen van add-ins