Excel, voorwaardelijke opmaak en percentages

[Eagle Creek]

Hi,

Voorop gesteld:

Excel 2010
Ik zit al enige tijd te zoeken naar de oplossing, snap an sich ook de functionaliteiten wel maar wat ik mis is de logische beredenering achter wat ik wil bereiken. Ik hoop dat iemand die wat ervaring heeft mij dit uit kan leggen.

Cellen A1 t/m A5 bevatten namen van medewerkers.
Cellen B1 t/m B5 bevatten aantal geplande uren.

5x 36 = 180 uur

In theorie kun je dus 180 uur in een week plannen.
Ik zou graag een voorwaardelijke opmaak toepassen in een cel die de totalen van B1 t/m B5 optelt, dat deelt tot het maximaal aantal werkbare uren en op basis daarvan een schaal toont (dan wel pictogrammen dan wel kleurtjes).

Nu kun je bij de voorwaardelijke opmaak percentages de kleur laten bepalen maar wat ik niet begrijp is een percentage waarvan.

Ik heb op dit moment:
C1
=(SOM(B1:B5))/C2
(C2 bevat de fixed value 180).

De celeigenschappen zijn opgegeven als "percentage".

Echter bereik ik hier niet wat ik wil. En ik kom ook niet achter de logica die ik precies zoek.

Hoe vertel ik de voorwaardelijke opmaak dat hij moet kijken naar de SOM als percentage van het maximum en daarop de opmaak toepast?

[mhoogendam]

Kan je dit niet instellen via een custom voorwaardelijke opmaak?
Highlight patterns and trends with conditional formatting

[Eagle Creek]

Bij wat je daar aanhaalt wordt de opmaak toegepast over de hele groep waarden. Daarmee staan de waarden in verhouding ten opzichte van elkaar. Maar dat is niet wat ik zoek.

[ajakkes]

Percentages bij voorwaardelijke opmaak zijn waarde van de formule ten opzichte van som van bereik.

Dus in jouw geval:

Zorgen dat B1 t/m B6 180 uur is. En dan de voorwaardelijke opmaak over die range.

B6 =( aantal(b1:b5)*36)-som(b1:b5)

Je kan ook de range van voorwaardelijke opmaak zetten over b1:b5;c6 volgens mij maar dat weet ik niet zeker.

Edit:
Volgens mij moet je eerst bekijken op basis van welk percentage je die uren nu eigenlijk wil kleuren want ik denk dat daar je denkfout zit:

werknemer gepland perc van totaal perc. van pers. cap.
alex 24 13% 67%
bennie 20 11% 56%
clemens 10 6% 28%
don 36 20% 100%
evert 5 3% 14%

gepland 95
aantal p 5
uur pp 36
beschikbare uren 180
ongepland 85

Wat jij aangeeft is dat je bij Don omdat hij 36uur ingepland is de cel voor 20% wil kleuren. Immers hij vult met zijn uren maar 20% van de totale capaciteit van 180uur.
Maar het zou logischer zijn om Don voor 100% te kleuren. Immers hij kan er niks meer bij hebben.

[ Voor 47% gewijzigd door ajakkes op 06-01-2016 10:40 ]

[Eagle Creek]

Hi,

Zorgen dat B1 t/m B6 180 uur is.

Maar dat zal niet altijd het geval zijn. Er kan ook 140 uur gepland zijn. Dan wil ik de cel met het totaal aantal uren anders gekleurd zien.

B6 =( aantal(b1:b5)*36)-som(b1:b5)

Hiermee toon ik het aantal uren dat ik overhoud. Wat kan ik hiermee dan?

Wat jij aangeeft is dat je bij Don omdat hij 36uur ingepland is de cel voor 20% wil kleuren. Immers hij vult met zijn uren maar 20% van de totale capaciteit van 180uur.

Nee, de cellen van de medewerkers hoeven niet gekleurd te worden. Alleen de totaalcel (die de teambelasting weergeeft).

Het totale team kan voor 180 uur gepland worden. Cel C1 geeft de som weer van alle geplande uren. Cel C1 moet qua opmaak tonen hoe de waarde zich verhoudt tot de totale planbare180 uur.

[ Voor 16% gewijzigd door Eagle Creek op 06-01-2016 10:50 ]

[Lustucru]

Je wilt op C1 voorwaardelijke opmaak toepassen?

Kies voorwaardelijke opmaak, kleurenschaal (naar keuze 2 of 3 kleuren), kies meer regels en geef de grenswaarden aan als type 'getal'. Je minimumwaarde is 0, je maximum waarde is 1. Dat het getal wordt weergegeven als percentage doet er niet zoveel toe.

[Eagle Creek]

Lustucru schreef op woensdag 06 januari 2016 @ 10:50:
Je wilt op C1 voorwaardelijke opmaak toepassen?

Kies voorwaardelijke opmaak, kleurenschaal (naar keuze 2 of 3 kleuren), kies meer regels en geef de grenswaarden aan als type 'getal'. Je minimumwaarde is 0, je maximum waarde is 1. Dat het getal wordt weergegeven als percentage doet er niet zoveel toe.

Aha! Ik kom nu al een heel stuk in de goede richting.
Wat is de reden dat de waarden 0 en 1 worden gekozen door je?

[CappieL]

0% = 0 en 100% = 1

De waarde van cel C1 ligt tussen 0 en 1 (90 uur / 180 uur = 0.5 = 50%)

[ Voor 68% gewijzigd door CappieL op 06-01-2016 10:58 ]

[F_J_K]

Reden: 0 = 0/100 = 0%, 1 = 100/100 = 100%.

Maar ik zou dus twee formules met eigen voorwaardelijke opmaak verwachten:
C1: B1/36
B6: (som(B1:B5)/180).

Dan de opmaak cf. Lustucru's methode of als het ook moet werken met oudere Excel met bijv drie losse regels: als <0,5 dan rood, tussen 0,5 en 0,8 geel, als >= 0,8 dan groen. Of zoiets (let op de grensgevallen en/of gebruik 'stop if true' bij beheer van de regels).

[Eagle Creek]

[b][message=45545556,noline]

Maar ik zou dus twee formules met eigen voorwaardelijke opmaak verwachten:
C1: B1/36
B6: (som(B1:B5)/180).

Vanwaar? Dan werk je toch niet met een fixed value? Ik snap je redenatie niet?

[ Voor 16% gewijzigd door Eagle Creek op 06-01-2016 11:08 ]

[F_J_K]

De 36 en 180 waren even om het eenvoudig leesbaar te houden, je wilt inderdaad "natuurlijk nooit" werken met absolute getallen, altijd met verwijzing naar cel of soms handiger: gedefinieerde namen ("parameters" die verwijzen naar een cel of bereik).

Zoals ik het las wilde je twee dingen weten: bezetting% per persoon en per team. In C1, C2, etc. per persoon, in B6 voor het hele team. Dat het team 80% is gevuld zegt niets: persoon 1 kan thuisblijven als de rest 100% vol zit. Of moet juist 100% werken als de rest 'maar' 75% vol zit.

[Eagle Creek]

Hi,

Het absolute getal komt voort uit 't feit dat er 5 medewerkers zijn met een 36-uurs contract. De enige wijziging op deze fixed value is dus wanneer het team groeit of krimpt.

Wanneer een medewerker thuis is, is "vakantie" of "ziek" een planbaar item. Het streven is elke week naar de 36 uur te plannen, ongeacht welke activiteit het is.

Dank!!

[ Voor 6% gewijzigd door Eagle Creek op 06-01-2016 11:26 ]

[F_J_K]

Laat maar, ik begrijp dat je er met cru's tip al bent