[Excel] Gekleurde cellen automatisch tellen

[PcDealer]

Microsoft Excel voor Office 365 MSO 16.x 64-bits versie 1911


Ik heb een spreadsheet met per regel een occassion. Van die occassions heb ik kolommen waarin ik de kenmerken van die auto vermeld. Daat zijn o.a. model, bouwjaar, kilometerstand, soort aandrijving, kleur, opties als verwarmbare stoelen of schuif-/kanteldak.Alle cellen in de kolommen krijgen een waardeoordeel via een kleur. Voor het gemak gebruik ik er nu 3: groen, oranje en rood, maar in werkelijkheid wil ik er 5.

Elke waarde visualiseer ik met Conditional formatting (Voorwaardelijke opmaak). Elke kleur krijgt een waarde als getal:
- groen = +5
- oranje = 0
- rood = -5

Ik wil het aantal kleuren tellen, vermenigvuldigen met de waarde en dat per kleur per regel optellen. Die vormen een totaal. Autofilter er op en filteren van hoog naar laag op de kolom Totaal. De hoogste score is de beste kandidaat om te kopen.

Ik heb een voorbeeld van een eerdere zoektocht toen ik de kleur in de bovenste regel heb gedefinieerd en handmatig het aantal kleuren telde en via een formule tot de totalen kwam:


Nu ik sinds kort Conditional formatting ken, bedacht ik me dat het vast mogelijk is om alle cellen met een bepaalde kleur op te tellen. Die functie blijkt er niet in te zitten. Toen stuitte ik op Excel VBA script Count Cell Color werkt niet optimaal.

ZIe ook AANTAL.ALS, functie -> Count the number of cells with a specific cell color using VBA.

Dat blijkt inderdaad te werken, maar als ik het aantal cellen met dezelfde kleur aanpas (direct de kleur in de cel, zonder gebruik te maken van Conditional formatting), dan wordt de uitkomst niet automatisch gewijzigd. Automatisch berekenen staat wel (standaard) aan. Als ik de formule cel in ga en enter druk, dan past het wel aan. Niet met F9.

Hoe kan ik het automatisch laten berekenen?

Edit: ook na verwijderen en opnieuw toevoegen van de vba-code werkt alleen groen de rest niet

[ Voor 12% gewijzigd door PcDealer op 11-12-2019 01:47 ]

[iamcj]

Kleuren gebruiken om te rekenen is naar mijn mening niet zo'n goed idee.

De kleuren zijn gebaseerd op data: Tel de data voor bijvoorbeeld jaartal:

=aantal.als(range;">=2009")*5
=aantallen.als(range;"<2009;range">2002")*0
=aantal.als(range;"<=2002")*-5

Horizontaal wordt het wat ingewikkelder, dan kun je beter hulpkolommen of een schaduwblad gebruiken. Op die kolommen kun je ook je voorwaardelijke opmaak instellen zodat de logica op 1 plek blijft.

Ook kun je dan de grenswaarden lostrekken en zo je sheet finetunen, bijvoorbeeld weer voor jaartal waarbij de jaartallen in kolom A staan vanaf rij 3:

Dit zijn je grenswaarden:
B1 =2009
B2 =2002

Hier beginnen de rijen met data met deze formules in je hulpkolom B.
B3 = als(A3>=$B$2;5;als(A3<=$B$2;-5;0))
B4 = als(A4>=$B$2;5;als(A4<=$B$2;-5;0))

Volgend jaar hoef je dan allen even je jaartallen in B1 en B2 aan te passen en je sheet is weer up to date.

Ook dat kun je nog weer generieker maken:
B1=Jaar(NU()-10)
B2=B1-7

Wil je op de huidige voet verder voeg dan deze code toe aan je blad:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.CalculateFull
End Sub

of in deze:
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

[ Voor 11% gewijzigd door iamcj op 14-12-2019 17:03 ]

[Jean luc Picard]

Het filter dat je al op je sheet hebt zitten, kan zelf op kleur filteren. Gewoon de kleur die je wilt zien selecteren. Nu houd je het gewenste resultaat over.

[PcDealer]

Jean luc Picard schreef op zondag 15 december 2019 @ 09:49:
Het filter dat je al op je sheet hebt zitten, kan zelf op kleur filteren.

Dat is mij inderdaad bekend. Ik wil echter het aantal kleuren tellen en die vermenigvuldigen met een waarde waardoor ik op het totaal kan sorteren.

[biomass]

Ik snap wel dat je het met kleuren wil doen, als je voor elke kolom en regel een waarderingscel vlak bij de data zelf moet neerzetten word het erg onoverzichtelijk.

Je kunt een macro maken die al je cellen langsloopt en op een aparte sheet of range de waardering neerzet?
Daar pas je je berekening toe en het resultaat kopieer je terug naar je brongegevens zodat je daarop kan sorteren? Dat is dus wel een actie die je moet triggeren met een knopje of een OnChange event, want je moet de kleurgegevens inlezen/interpreteren met VBA.

[The_Vice]

PcDealer schreef op dinsdag 10 december 2019 @ 23:10:
...
Edit: ook na verwijderen en opnieuw toevoegen van de vba-code werkt alleen groen de rest niet

Die vba code kijkt naar de colorindex, dan is het beter om uit het standaard pallet (figure 2) de kleuren te nemen.

[Lustucru]

Omdat het veranderen van een kleur voor Excel geen trigger is om een herberekening te doen, moet je de functie merken met 'application.volatile'. Dit zorgt ervoor dat die functie bij élke herberekening wordt 'meegenomen'. Zie verder bij:

Lustucru in "[Excel] Waardes van gekleurde tekst in cellen optellen"

Of om helemaal naar het begin te gaan: de oplossing is 15 jaar oud.

Lustucru in "excel cellen op kleur sorteren/optellen"

[ Voor 18% gewijzigd door Lustucru op 15-12-2019 20:07 ]