Zo werkt Excel

Naar aanleiding van mijn artikel over de werking van de SOMPRODUCT functie (Getallen optellen op basis van meerdere voorwaarden), ontving ik van iemand de vraag of het mogelijk was om met behulp van deze functie getallen met dezelfde kleur bij elkaar op te tellen.

Met behulp van de standaard functies van Excel is dit niet mogelijk. Er bestaat geen functie die informatie geeft over de kleur van het lettertype in een cel. Echter het is wel relatief eenvoudig om zelf zo’n functie te bouwen. Laten we eens kijken naar het volgende voorbeeld.

Als het mogelijk is om met een functie de kleur van de cijfers in kolom E te achterhalen, dan kunnen we het resultaat van die functie gebruiken om de som van bijvoorbeeld de rode cijfers te bepalen. Om dit te kunnen doen, moeten we zelf een functie gaan maken. Zorg dat je in de werkmap staat waarin de cellen met gekleurde cijfers staan en ga met de toetsencombinatie [Alt] + [F11] naar de VBA (visual basic for applications) editor.

Selecteer het project dat hoort bij de werkmap waarin de cijfers staan die je wilt optellen. In mijn voorbeeld is dit zowerktexcel1. Klik op de rechtermuisknop en kies voor invoegen -> module.

Je ziet vervolgens een leeg scherm. Vul daar de bovenstaande code in en sluit de VBA editor (met behulp van [Alt] + [Q]) af.
We gaan de formule nu invoeren in cel F3. Kies in het menu: Invoegen -> Functie. Selecteer in het onderstaande dialoogscherm de categorie ‘Door gebruiker gedefinieerd’ en als het goed is zie je de functie TEKSTKLEUR staan.

Als we deze functie nu selecteren en hem laten verwijzen naar cel E3 en deze formule kopiëren van cel F3 naar F10, krijgen we het volgende resultaat:

De functie geeft in kolom F de kleurenindex van het lettertype in kolom E. Als de kleur niet is aangepast, geeft de functie de constante -4105 terug. Het kan gebeuren dat als je de kleur van het lettertype wijzigt, de waarde in kolom F niet meteen wordt aangepast (Excel gaat niet opnieuw rekenen omdat er geen getallen veranderen). Je kunt dit forceren met de toetsencombinatie [Ctrl] + [Alt] + [F9]. Met behulp van de kleurcodes in kolom F kunnen we met SOMPRODUCT de ‘rode’ omzet in Q2 optellen. Een gedetailleerde uitleg over hoe je dit kunt doen vind je in het artikel: Getallen optellen op basis van meerdere voorwaarden. Wil je de functie zelf bekijken en er wat mee experimenteren dan kun je hier het voorbeeldbestand downloaden.