Getallen in gekleurde cellen optellen
|
Excel heeft geen ingebouwde functie om alleen getallen op te tellen in cellen die een bepaalde kleur hebben. Maar je kunt die functie wel zelf programmeren. Dat wordt een UDF genoemd, een User Defined Function.
We noemen deze functie SOMZELFDEKLEUR. Deze functie telt de waarden op in cellen die een bepaalde opvulkleur hebben. We geven aan één bepaalde cel buiten de tabel een opvulkleur, geven getallen in de tabel dezelfde kleur en de getallen met dezelfde kleur worden opgeteld. Je programmeert deze functie als volgt. Open de Visual Basic Editor, dubbelklik in de Projectverkenner op de werkmap waarin je deze functie wilt plaatsen. Klik op Invoegen en op Module; er verschijnt een blanco codeblad. Neem daarin de volgende code over. Deze kun je van hieruit kopiëren en plakken. Function SOMZELFDEKLEUR(Gebied As Range, Cel As Range) As Double Dim Kleur As Integer Application.Volatile Kleur = Cel.Interior.ColorIndex For Each Cel In Gebied.Cells If Cel.Interior.ColorIndex = Kleur And IsNumeric(Cel.Value) Then SOMZELFDEKLEUR = SOMZELFDEKLEUR + Cel.Value End If Next Cel End Function Tussen de haakjes staat eerst het gebied met de getallen, dan volgt de cel die de betreffende kleur heeft. De code begint met Application.Volatile Dat zorgt ervoor dat de formule automatisch reageert als je naderhand celkleuren verandert. Je hoeft dan niet in de cel met de formule op de functietoets F2 te drukken en op de Enter-toets om de formule opnieuw in te voeren en weer te laten berekenen. De functie haalt het kleurnummer van die ene cel op met: Kleur = Cel.Interior.ColorIndex De volgende code is een lus: For Each Cel In Gebied.Cells tot en met Next Cel Deze gaat alle cellen in het opgegeven gebied langs en controleert of hun kleurnummer overeenkomt met het kleurnummer van de ene cel die als criterium dient, met: If Cel.Interior.ColorIndex = Kleurnummer Bovendien moet de waarde in die cel een getal zijn, dat checkt: And IsNumeric(Cel.Value) De variabele SOMZELFDEKLEUR telt de waarden in deze cellen op. Deze is in het begin nul. Als een cel een getal bevat met het betreffende kleurnummer, wordt de waarde van die cel bij de variabele opgeteld in SOMZELFDEKLEUR = SOMZELFDEKLEUR + Cel.Value Vervolgens wordt de waarde van de volgende cel die aan de voorwaarden voldoet, daarbij opgeteld enzovoort. De lus doorloopt het hele gebied en telt zo alle getallen op in de cellen die dat kleurnummer hebben. |
Meer eigen functies programmeren?Deze voorbeelden komen uit het boek:
Excel VBA voor professionals, Derde editie 
|
Formule met SOMZELFDEKLEUR opstellen
|
Vervolgens gebruik je deze zelfgeschreven functie in een formule. Deze formule heeft de volgende opbouw.
=SOMZELFDEKLEUR(gebied;cel met een kleur) Je geeft tussen haken het gebied op dat wordt doorzocht; na een puntkomma verwijs je naar één cel die de betreffende kleur heeft. Resultaat: het totaal van de getallen in dat gebied in de cellen met dezelfde kleur als die ene cel. Je past deze functie bijvoorbeeld toe op de reeks B1 tot en met E10. Cel A1 is bijvoorbeeld oranje. Je wilt van B1 tot en met E10 de getallen optellen, die ook in een oranje cel staan (die dezelfde kleur hebben als A1). Je formule is: =SOMZELFDEKLEUR(B1:E10;A1) Alle getallen worden opgeteld in de cellen die dezelfde kleur als A1 hebben. |
De getallen worden opgeteld in de cellen die dezelfde kleur als A1 hebben.
|
|
De cel waar je de kleur vandaan haalt, mag ook de cel zijn waarin deze formule zelf staat (dat levert geen kringverwijzing op).
Geef een aantal cellen in B1 tot en met E10 een gele opvulkleur. Kleur bijvoorbeeld cel F6 ook geel en plaats in deze cel de formule: =SOMZELFDEKLEUR(B1:E10;F6) Nu heeft de cel met de uitkomst dezelfde kleur als de cellen waarvan de getallen worden opgeteld. * Verwijs niet naar een hele kolom of hele rij, want dan zal het werkblad trager werken, omdat deze functie duizenden cellen moet controleren. Houd de verwijzing naar het gebied zo beperkt mogelijk. |
Tel de getallen op in de cellen met dezelfde kleur als waarin de formule staat (in F5).
Download gratis het voorbeeld-bestand
Je mag het Excel-bestand met deze code erin gratis downloaden. Je mag dit bestand voor jezelf gebruiken. Doorverkopen is op grond van het auteursrecht verboden. |
Tellen hoeveel cellen dezelfde kleur hebben
Je kunt ook tellen hoeveel cellen een bepaalde keur hebben. Ook die functie kun je zelf programmeren.
Dit is een variant op bovenstaande functie, we noemen deze AANTALZELFDEKLEUR.
We geven aan één bepaalde cel buiten de tabel een opvulkleur, en de formule telt hoeveel cellen in die tabel dezelfde kleur hebben.
Neem onderstaande code over (die mag in dezelfde module komen).
Function AANTALZELFDEKLEUR(Gebied As Range, Cel As Range) As Double
Dim Kleur As Integer
Application.Volatile
Kleur = Cel.Interior.ColorIndex
For Each Cel In Gebied.Cells
If Cel.Interior.ColorIndex = Kleur Then
AANTALZELFDEKLEUR = AANTALZELFDEKLEUR + 1
End If
Next Cel
End Function
Vervolgens gebruik je deze zelfgeschreven functie in een formule, die de volgende opbouw heeft.
=AANTALZELFDEKLEUR(gebied;cel met een kleur)
Je geeft tussen haken het gebied op dat wordt doorzocht; na een puntkomma verwijs je naar één cel die de betreffende kleur heeft.
Resultaat: het aantal cellen in dat gebied dat dezelfde kleur heeft als die ene cel.
Je past deze functie bijvoorbeeld toe op de reeks B1 tot en met E10. Cel A1 is bijvoorbeeld oranje. Je wilt weten, hoeveel cellen in B1 tot en met E10 ook oranje zijn (die dezelfde kleur hebben als A1). Je formule is:
=AANTALZELFDEKLEUR(B1:E10;A1)
Alle cellen worden geteld die dezelfde kleur als A1 hebben.
De cel waar je de kleur vandaan haalt, mag ook de cel zijn waarin deze formule zelf staat. Geef een aantal cellen in B1 tot en met E10 een gele opvulkleur. Kleur bijvoorbeeld cel F6 ook geel en plaats in deze cel de formule:
=AANTALZELFDEKLEUR(B1:E10;F6)
Nu heeft de cel met de uitkomst dezelfde kleur als de cellen die worden geteld.
Dit is een variant op bovenstaande functie, we noemen deze AANTALZELFDEKLEUR.
We geven aan één bepaalde cel buiten de tabel een opvulkleur, en de formule telt hoeveel cellen in die tabel dezelfde kleur hebben.
Neem onderstaande code over (die mag in dezelfde module komen).
Function AANTALZELFDEKLEUR(Gebied As Range, Cel As Range) As Double
Dim Kleur As Integer
Application.Volatile
Kleur = Cel.Interior.ColorIndex
For Each Cel In Gebied.Cells
If Cel.Interior.ColorIndex = Kleur Then
AANTALZELFDEKLEUR = AANTALZELFDEKLEUR + 1
End If
Next Cel
End Function
Vervolgens gebruik je deze zelfgeschreven functie in een formule, die de volgende opbouw heeft.
=AANTALZELFDEKLEUR(gebied;cel met een kleur)
Je geeft tussen haken het gebied op dat wordt doorzocht; na een puntkomma verwijs je naar één cel die de betreffende kleur heeft.
Resultaat: het aantal cellen in dat gebied dat dezelfde kleur heeft als die ene cel.
Je past deze functie bijvoorbeeld toe op de reeks B1 tot en met E10. Cel A1 is bijvoorbeeld oranje. Je wilt weten, hoeveel cellen in B1 tot en met E10 ook oranje zijn (die dezelfde kleur hebben als A1). Je formule is:
=AANTALZELFDEKLEUR(B1:E10;A1)
Alle cellen worden geteld die dezelfde kleur als A1 hebben.
De cel waar je de kleur vandaan haalt, mag ook de cel zijn waarin deze formule zelf staat. Geef een aantal cellen in B1 tot en met E10 een gele opvulkleur. Kleur bijvoorbeeld cel F6 ook geel en plaats in deze cel de formule:
=AANTALZELFDEKLEUR(B1:E10;F6)
Nu heeft de cel met de uitkomst dezelfde kleur als de cellen die worden geteld.